Book update

Just a quick update on the book. It is currently going through the pre-final proof reading stage where hopefully we will weed out any typos!

Once I have a confirmed release date I will include those details here – we are looking at around the end of February for it hitting the stores I would guess though.

For anyone that buys it and has comments or questions you can use this blog as a medium to contact me.

Temperature Database Prototyping – Hardware, Postgresql and HTSQL


In this post I am going to detail prototyping a test Postgresql database server, installing Postgres support on the Raspberry PI (for HTSQL) and then serving up the contents of the DB server via HTSQL over http.

The purpose of this task is to familiarise ourselves with each of the components in the system before we design how the actual database server will be setup and built out.

Throw away test machine

I like to keep a couple of old Windows machine on hand for building out quick test servers that I can use to prototype a system and then wipe out ready for my next project. I find this saves a lot of cost of going out and buying hardware, only to discover your original plan has changed for some technical/design reason and then having to purchase more equipment.

For this task I am using an old Pentium 4 machine running XP with about 60 Gigs of HDD space. I’ve hooked it up with a monitor and keyboard and plugged it into my router via a network cable. At this point it is worth grabbing the IP address of the machine you have just plugged in. This should be available via your routers DHCP table.

Consult your router documentation on how to access this information if you are not familiar with the process. Note: I checked my DHCP table after installing Ubuntu – since this is the only Ubuntu machine on my network currently, it appeared as “ubuntu” in the table

Now my first step is going to be to set it up as a dual-boot machine and load Ubuntu desktop edition onto it.

Ubuntu setup

Depending on the type of machine you use for prototyping the instructions below will obviously be different, for those of you with an old XP machine at hand, the steps below should work for you.

  1. Downloaded Ubuntu’s latest version windows installed here.
  2. Run the installer and follow the on-screen instructions – Note: select your OS type as Ubuntu.
  3. Once your machine has rebooted into Windows follow the next steps:
  4.  Right click My computer and select Properties
  5. Select the Advanced tab from the pop-up box
  6. Under Start-up and Recovery select Settings
  7. Select Time to display list of operating systems checkbox – change the drop-down to 10 seconds
  8. Select the Default operating system drop-down and change this to “Ubuntu”
  9. Save your changes and re-boot your machine into Ubuntu.

Setting up Postgresql

Our Ubuntu machine is going to act as a database server, a Postgresql one in fact. For those of you not familiar with Postresql, it is a powerful, free, open source relational database and has been around for 15+ years. You can read more about it here.

Your first step will be to install the necessary software on Ubuntu.

Follow the instructions below to set up Postgresql

1. Open Terminal in Ubuntu and type the following command:

sudo apt-get install postgresql postgresql-client postgresql-contrib

2. The above command once it has completed running will have installed all the necessary components to start a Postgresql server on your Ubuntu machine.

3. Navigate via the command line to the following directory:


Note: depending on the version of Postgres you have installed the directory 9.1 may be different. As of August 26, 2012 9.1 is the latest version installed via the apt-get command above.

4. You will now need to edit the postgresql.conf in order to tell Postgresql to listen on the IP address assigned to your new dev machine via DHCP. Using vi type:

sudo vi postgresql.conf

Once you have the file open, edit the following line (you may have to un-comment it, and it will probably be set to localhost):

listen_addresses = 'ip address from dhcp table'

Save the file and exit.

5. We can now login to the “template1” DB that comes setup by default with pgsql

sudo -u postgres psql template1

From here for example we can change the password of the postgres account:

ALTER USER postgres with encrypted password 'my_new_password';

6. We now want to create a test database to serve via HTSQL, to do this we type the following:


7. With our new database created we can add a dummy table and a dummy row of data:

CREATE TABLE temperature (code char(5) );
INSERT INTO temperature (code) VALUES ('12345');

Feel free to add some more test rows.

8. Now we can quit postgresql and edit the configuration in order to allow remote access to the DB. In order to do this, you will need to edit the pg_hba.conf file

Change the configuration to include the following (you can use Vi):

local   all         postgres                          md5
host   all         postgres              samenet            md5

Then save and exit

9. We now need to restart our postgresql instance to pick up the changes, this can be done by:

sudo /etc/init.d/postgresql restart

Once the instance has restarted, we are now ready to make some configuration changes on the Raspberry PI.

Raspberry PI configuration

In order to connect our HTSQL server to our newly built Postgresql DB we need to install some extra packages on the Raspberry PI. First of all you will need ssh into the RPI.

Once logged in, follow the steps below:

1. Install python developer packages and the HTSQL postgres package as follows:

sudo apt-get install libpq-dev python-dev
pip install HTSQL-PGSQL

Once these have successfully been installed you should now see:

Successfully installed HTSQL-PGSQL psycopg2

Now we can connect to the postgresql instance.

2. We can test our connection to the testtemp database we created as follows:

htsql-ctl shell -p pgsql://postgres@host:port/database

The host above should be replaced with the IP address of the Ubuntu server you setup with postgresql installed on it. Also remember to include the port number after the IP address, by default postgresql runs on 5432. The final option is the database we created – temptest.

Once you can log in via the HTSQL shell to the DB instances then you are good to go with actually running a server.

3. Exit out of the HTSQL shell and then create a HTSQL server as follows:

htsql-ctl server 'engine://username:password@host:port/database'

engine should be replaced with pgsql, username and password are those that you set on the DB server, host and port are the DB servers IP address and postgresql port (as mentioned above this will probably be 5432) and finally database is our temptest database.

Once you have started the server you will see:

Starting an HTSQL server on raspberrypi:8080 over temptest

4. We can now check that everything is running as expected. Load up your web browser and in the URL bar type:  http://<ip of raspberry pi>:8080

If everything is working as expected you will see

Welcome to HTSQL!

Please enter a query in the address bar.

5. You can then hit the test table we created as follows:

http://<ip of raspberry pi>:8080/temperature

And see some test data (note I have added a few extra rows to my temperature table of random test data)

1 12345
2 1245
3 56789

6. And that’s it, you now have HTSQL running on the Raspberry PI, pointing to a database server.


The above demonstrates the process of setting up a database server and using HTSQL on the Raspberry PI to communicate with it.  However this is only the beginning of what is required in order to build a system that can record our thermostat temperature data.

Using a prototype is useful for the purposes of ensuring we are building a modular system, where each component can be swapped out as necessary. Now we have demonstrated that we can plug a database into the system, we can concentrate on sourcing the right hardware that will replace the prototype.

Of course whilst our Raspberry PI now runs HTSQL, there is still a lot of work to be done on that end of things. We need to configure the RPI so that it is secure and also build out our controller software – which will use the data stored in the Postgres DB to tune our heating system.

The next steps then are going to be to complete the setup of the Raspberry PI and use our prototype DB server to test this configuration against. Once this is complete we will then concentrate on designing a relational database for the heating data, and building out the server hardware.

Installing HTSQL on RPI


As I have mentioned in earlier posts I am planning on using HTSQL as part of my heating system. It will primarily provide me with a way to query data stored from the controller and thermostats over HTTP.

Eventually it will be pointing to a PostgreSQL DB which will act as my area for data storage.

Below are instructions on how to install HTSQL on your Raspberry PI.

Installation instructions

Note: The instructions presented are for installing HTSQL on a RPI running Debian squeeze. If you are looking for the Mac instructions they are located here. For other flavours of Linux available on the RPI the instructions should work, I make no promises though!

As always, you can check for up-to-date information on operating systems supported and instructions.

Anyway on with the installation process:

The Raspberry PI runs on an ARM based architecture. On last checking the htsql website, there is no support for this via apt-get.

You can check the architecture of your chipset (which of course will be ARM if you are following the steps for your RPI) as follows:

apt-config dump

You will see in the returned information:

APT::Architecture "armel"

If you attempt to follow the standard apt-get procedure as presented on the htsql website for Debian, you will run into the following error message:

E: Unable to locate package htsql

This is because there is currently no armel distro.

So in order to install HTSQL, you will need to install via source.

Install pip

In order to run pip (a python package installer) you will need to add it to your RPI.

Run the following command :

sudo apt-get install python-pip

Once this is complete you can then run:

pip install HTSQL

Once this is complete you can now type:

htsql-ctl version

You should now see:

HTSQL 2.3.1 (or whatever the latest version is you downloaded).

HTSQL is now ready to go!


Out of the box HTSQL works with sqlite, if you want to run it against other databases you can follow the instructions here.

Follow up posts to this will detail setting up a PostgreSQL DB server and then connecting to it via HTSQL.

Quick update – number 2

With summer being here, things have been a little slower on the development/soldering front. However as we approach fall this will change.

Here is an update of what is happening.

3D printing co-operative

This evening the first meeting of our 3D printing co-operative will take place. Now we have ironed out the issues with the MakerBot, the rest of the co-operative is ready to start using it. I’ll be posting the occasional picture up here of any cool objects my colleagues make.

Instructional Videos

As part of the inauguration of the 3D printing co-op we will doing some training sessions on the Replicator. I will try to film some of the important aspects of this (changing material spools, changing the base plate heat etc.) and adding links to these on the blog.

Ordering Materials

I still have some outstanding components to order for my Thermostats, I’ll be sorting these out next week. Once I have them all, expect a post listing each piece, what it does and how it fits together as part of the overall system.

Raspberry PI remote access/ssh

Expect two separate posts, one on setting up the Raspberry PI to be accessed over your home network (so you don’t need a TV/Monitor) and the second on installing the latest version of HTSQL. (Update: these can be found here)

Settings up HTSQL on Mac Lion


In order to use HTSQL on my Raspberry PI I decided to install a local copy first so I could experiment with it.

The steps below are for HTSQL setup on a Mac Book Pro running Lion.

Setup steps for installing HTSQL on Mac Lion

Step 1: Setup machine ready for HTSQL install

Ensure that all required packages are installed. You will need the following:

X11  –

X code –  With Lion you can install X code directly from the App store.
You will need to use your ITunes login, or create a new Apple ID if you do not have one.
When attempting to download from the App store you will be prompted for the above and given the opportunity to create an account if you don’t have one.

Once downloaded, the App store will show you Xcode as being installed. However this is misleading. You will need to navigate to your Applications folder and run the Install Xcode installer.

Macports – Follow the link below for a guide to Macports:

The latest Lion dmg can be downloaded for the link below:

Mercurial – Once the above packages have been installed, you can now install Mercurial which will allow you to clone the HTSQL repository to your local machine.

Load up terminal and run the following command:

sudo port install mercurial

This will install a number of packages onto your machine.

Step 2 – Install HTSQL

On your Mac you can now clone the HTSQL repository to your local machine (this will clone to Users/username for example):

Load Terminal and from the command line run the following command (note: check for the latest bitbucket path)

hg clone

Once this is complete you should be able to navigate to the HTSQL directory:

cd htsql

To install HTSQL run the following commands:

make build

and then

sudo make install

You can test your installation by running the following from the command line:

run htsql-ctl help

You should now see HTSQL help running.

Using the HTSQL documentation you can now connect to a database on your machine.

HTSQL works with a variety of databases, the easiest to get started with is probably SQlite

These above steps for example were tested against a sqlite DB using the server command and running on localhost:8080


htsql-ctl server sqlite:../../../Applications/XAMPP/xamppfiles/htdocs/mydatabase/test.db

Starting an HTSQL server on User-MacBook-Pro.local:8080 over ../../../Applications/XAMPP/xamppfiles/htdocs/mydatabase/test.db


The process above is fairly simple although does require on the Mac a number of packages in order to work. If you have a Linux machine the process is far simpler.  Installing HTSQL on the RPI for example should be a fairly quick process

Further instructions on installation and compilation of HTSQL can be found here.

HTSQL – an HTTP based query language

An introduction to HTSQL

I’ll quickly give you an over-view of what HTSQL is, before I go into how I plan to use it for my thermostat/heating project.

HTSQL is a query language, developed by Clark Evans and Kirill Simonov of Prometheus Research, for relational databases that can be used over http. The language contains many of the features available in SQL but with the benefit that the user can plug queries directly into their browser, rather than having to embed code into server-side scripts, or connect to a database directly via a command line.

A variety of relational databases are supported by HTSQL including (but not limited to): MySQL, PostgreSQL, MsSQL and Oracle.

Once you have installed HTSQL you can point your instance at an existing database and begin to query the data contained within it. For example, if I wished to return a JSON object containing a list of temperatures from a temperature table, the query could look something like this:


I could post the above URL into my web browser and it will query and return the data for me.  This of course opens up a lot of possibilities for a heating system dashboard, web-based monitoring and secure remote access of data.

There is a comprehensive list of examples of the HTSQL language here. The language is also complimented by a JavaScript based toolkit (HTRAF) that allows users to embed queries directly into widgets in their HTML page, and generate HTML elements dynamically.

Applications of HTSQL in my heating system

As I alluded to above, HTSQL provides some great opportunities for building dash boards and similar. I plan to use the technology in a number of ways, some of these are briefly outlined below:

1.) Install HTSQL on the Raspberry PI. Once installed on the RPI, I can point it at my local database server (more on this later). If I wish to switch the server out at any point in the future, I can update the settings on the RPI with little problem.

2.) Use my Arduino thermostats to post data to my DB server via HTSQL. The Arduino thermostats will be able to construct HTTP requests that will post temperature data to the RPI running the HTSQL instance. HTSQL will then take care of the rest.

3.) Modify heat setting in various rooms via the RPI. The algorithm that will guide changing the temperature in various rooms in my house will be located on the RPI. Via the HTSQL server it will be able to access past temperature history and use this for optimization.

4.) Build a dashboard to show temperature history and a host of other data. On a separate web-server I’ll be setting up (probably an Apache web-server on Ubuntu) I can build a graphical dashboard using HTRAF widgets to present the data I have collected in graphs, tables and other interesting HTML elements.

No-doubt other uses will arise as I start to build out my system and I’ll post these uses as I go. As I hope you can see from the above, the language provides some very interesting tools!


HTSQL provides a great method for interacting with and querying my heating system. My next step is going to be installing HTSQL on my RPI running Debian Squeeze. I’ll provide a guide to this as I go, as well as a guide to installing HTSQL on my MacBook for testing purposes.