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.

Raspberry PI – Network access


One of the benefits of having a machine like the RPI is that you can set it up on your home network and access it without having to worry about a keyboard, mouse or monitor.

Combined with the RPIs small size, and the ability to place it in a custom case with a wall mount, these features are what make it one of the most exciting tools for home embedded/smart systems.

The steps below are an introduction on getting your RPI to boot up with ssh access, and allow you to connect to it from another machine on your home network.


So what is ssh? In brief ssh (secure shell) is a method that allows us to log into other machines over the network, most commonly through a command line tool (for those of you with a Mac you can do this via the Terminal). Once logged in, depending on our permissions we can then perform actions on the machine we are connected to.

For a deeper more complex technical evaluation of ssh you can read the wiki article here.

For the purposes of configuring our RPI (and later using it as a controller) we are going to be setting up ssh access so we can work on the machine remotely.

In my specific case, this is born from the fact that my router is nowhere near my TV set so trying to configure the RPI with a monitor, but without network access is going to be impossible in some cases i.e. installing HTSQL.

Steps to set up 

Fist of all connect your RPI up to its power supply, monitor and keyboard. Then log in.

Raspberry PI login

Raspberry PI login

At this point you may want to set the date of your RPI if you haven’t done so already.

By default the keyboard is set up to be UK format, so the @ is actually the ”

To set the date:

sudo date –set “23 APR 2012 16:01”

Next you will need to set the RPI to start ssh when it boots. To do this follow the next steps:

  1. cd /boot/
  2. sudo cp bootenable_ssh.rc boot.rc

The above copies bootenable_ssh.rc and creates a second version called boot.rc. The boot.rc version is then loaded when you start your RPI up.

Now follow the next steps:

  1. cd /etc/network
  2. vi interfaces

By default vim isn’t installed on the RPI, so if you haven’t taken the step to install it, you’ll need to use vi to edit the interfaces file.

You’ll need to edit the settings in the interfaces file as follows:

  1. Add eth0 to this line: insert eth0 so : auto lo eth0
  2. Change iface eth0 inet dhcp  to  iface th0 inet static
  3. Now add:

Finally save the file  – press  : then type x and hit enter to save and exit
The above steps have now added configuration to tell the RPI to run on a static IP address on your local network.

Testing the above worked

You can now log out of your RPI and power it down.

Next, boot the PI back up with your network jack attached. Your PI should now boot with ssh enabled.

Raspberry PI hooked up

Raspberry PI hooked up

I plugged my PI directly into my Cisco E4200 router. I then logged into it via in my web browser (type into the address bar) – note this may be different on your home network depending on how you have configured it.

If you have a Windows machine for example and your router is plugged into that, you can run:


Now look for Default Gateway, this will give you the IP address that the router should be running on.

Once your PI is powered up and booted (you should also see a yellow light, indicating you are on the network) you can shell (ssh) into your server.


The fixed IP address you assigned may not work. To test if it did try:

 ssh pi@

If this does not work, log into your router and check the DHCP Client table. Most of the devices I run on my network have a client name e.g. kindle-xxxxxxxxxx.

You may now see a device appear in the list with no name. To confirm which device is your RPI, you can try disconnecting it, refreshing the DHCP client table, reconnecting it and then refreshing again. The new device added to the list is probably your RPI.

Next, take the new IP address from the table and shell into the machine (via Terminal, console, Cygwin or whatever you are using):

ssh pi@

This gave me the authenticity message which I accepted. I then entered the default password for the PI, and presto! I am now logged in.


So I need to investigate how to ensure the static IP is always picked up, I’ll blog a separate post on that for those of you having problems.

For the moment we now have an easy way to get access to the PI without a monitor and can go ahead and install whatever packages we need.

Raspberry PI case printing


In my previous post I hd mentioned that having got the 3D printer ready to go again, I planned to print the case for my Raspberry PI. After viewing some of the cases on Thingiverse I located one that had been designed with four wall mounts.

In my initial attempt to export the image to the SD card, everything had seemed to work correctly, however the MakerBot was unable to view it. So during my lunch hour today I removed the file from the SD card and started over.

Exporting the case to SD card

My first step was to load the top portion of the case’s .stl file into ReplicatorG. Using the 3D rendering of the image I was able to align the case top so that it was positioned in the middle of the axis. Once this was complete I then exported the file to Gcode. Finally I edited the Gcode so the heat of the base plate would be 120 degrees F.

I then followed the steps above for the bottom portion of the case.

The images were now ready to export to SD format. Using the option on the menu I exported directly to my SD card, and also shortened the rather long names of the files in case this contributed to the MakerBot not being able to view them.

Ejecting the SD card I was now ready to give printing another try.

Printing the case

Loading the SD card into the MakerBot I could now view the files on the menu and so set the top to print. I left the printer to do its thing and checked back in on it a couple of hours later. As you can see in the picture below, the printing was successful:

Raspberry PI case

Raspberry PI case

Raspberry PI top

Raspberry PI top

Raspberry PI bottom section

Raspberry PI case

With the two pieces of the case complete my next job was to clean the lattice off the objects and try the Raspberry PI out to see if it fit.

Here you can see the case clipped together:

Completed case

Completed case

I would recommend for the next steps having some kind of tray to put the case on, an X-acto knife and some sand-paper or a tool to smooth off the plastic (the later being something I didn’t have at hand at home, so I’ll need a trip to Home Depot).

Most objects are printed onto a lattice base, so your first job will be to remove this.

As you can see below, the lattice that the objects are printed on comes off pretty easily, you can snap this off with your hand right after you take the object out of the MakerBot.



As a note: One thing I have noticed about the quality of the print – in general – is that the objects made on the MakerBot have a lot of rough portions that need to be cut or smoothed off. This takes a little while so with my RPI case I set some  time aside this evening to work on cleaning it up.

Here you can see the bottom portion of the RPI case:

Case bottom

Case bottom

Outside of the RPI case

Outside of the RPI case

Using an X-acto knife I gently removed the top layer on the case to reveal the better resolution layer below. However this is a tedious process and even with the X-acto knife didn’t give me the result I wanted. It is also messy so having a tray to catch the off-cuts was handy. At this point some sandpaper would have been useful. Since I didn’t have this at home, doing the final sanding off  had to wait. Once I do this I also plan to spray paint the case, which will be the subject of another post.

Having done a best of a job as possible cleaning things up I now tried placing the RPI inside the bottom section. The RPI fitted in here no problem. I now tried to place the top onto the RPI and close the case. At this point I noticed that one of the plastic lips inside the case was catching on the RPI near the SD card port. Taking the X-acto knife I cut this portion off and now the case closed perfectly.

You can see the final result here:

RPI in the case

RPI in the case

So as you can see above, we have our first attempt at a printed Raspberry PI case complete. Once I finish off the aesthetics of the case, I will then be able to attach it to the wall and wire it up to my home network. I still have some configuration to complete on the RPI in the area of HTSQL installation which I will be blogging about soon.

All in all the process has been a great learning experience and given me a number of avenues to investigate, including how to ensure a better resolution and quality of print on future 3D objects.

It’s also been the first project that has been successfully completed on our co-op’s MakerBot leading to us now feeling comfortable with everyone jumping in and printing their own creations.

With regards to cases, for the Arduino thermostat, I plan to design my own housing unit in Blender, which will also be the subject of a future blog posting.

Finally, if anyone has any suggestions of improving the print quality, please feel free to comment below!

Raspberry PI case, soldering irons and other such matters


This post will be a round up of what has been happening on the intelligent heating project.

In my last post I detailed how we had encountered a problem printing an object on the MakerBot and how this had resulted in some slight damage to the build plate.

Thankfully the damage wasn’t critical. This week we were able to re-cover the build plate with the film (which is a real awkward task and takes a couple of attempts to get right) and start printing object again!

The picture below shows the MakerBot back in action:



Raspberry PI (RPI) case

Now that the printer is back in action I did some research online for Raspberry PI cases. The thingiverse website has a selection of RPI cases however I found one in particular that matched my requirements, as it came with a mounting bracket for screwing the case to the wall.

Since my RPI will be sitting inside a small closet where our electrical box is located, this seemed perfect.

You can see the case here:

I downloaded the STL files and then loaded them into ReplicatorG. First of all I built the Gcode for the base section of the case, and then exported to flash card format.

I now encountered a small problem, the case wasn’t visible on the flash card via the MakerBot menu. I’m not immediately sure what happened here, so I’ll be trying a fresh export on Monday and will try reloading it. Providing this works, then the first version of the case will be printed next week.

I’ll be photoing and uploading the results to the blog.

Soldering Iron

At the end of the month I plan on ordering some components for the Arduino from Adafruit, this will consist of a temperature sensor and a touch screen for the thermostat. I’ve not settled on which products I’m going with yet, but once this is decided I’ll be adding a post to the Thermostat thread details the specs and costs.

Of course in order to attach the components to the Arduino it helps to have a soldering iron. So today I picked one up from Home Depot. It cost around $15 and some extra electrical solder was around $9:

Soldering Iron

Soldering Iron




So I am now all set to start connecting up the components for my Arduino thermostat.

Raspberry PI and TV

In my earlier blog I had mentioned my plan to install HTSQL on the RPI. I have still not gotten around to doing this as a result of the equipment I am missing. I will need a cable to connect the video out up to a spare monitor  I have ready. My other option is of course to buy another monitor with a HDMI output on it. However I have had some problems with HDMI jack not working on the RPI so I will need to investigate this further.


So the above details where the project is currently at. Expect further updates over the week.

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.

Raspberry PI setup

A quick guide to RPI setup

Below is a quick guide to how I set up my Raspberry PI.

As an introductory note. It took several attempts to get the Raspberry PI (RPI) to work, the steps below are the ones that ended up working for me, but may not work for you. These steps were performed on a MacBook pro running Lion and using a SanDisk Ultra II 4 GB SD card.

1.) I downloaded the Debian Squeeze version of Linux from here:

2.) I grabbed a copy of RasPiWrite for Mac from here:

3.) Next I put my SD card into my MacBook and removed the existing partition.

SD Card

SD Card

4.) I ran the RasPiWrite script. This requires python to be installed on your machine.  You may want to follow along with the instructions on the website as well.

5.) Once done I plugged in the spare keyboard I had into the Raspberry PI and using a USB to wall power supply, plugged this into the RPI.

6.) I plugged the HDMI cable from my TV into the Raspberry PI. Note – this did not work, I got a flickering green + red light.

7.) I switched over from the HDMI  cable to the yellow comp cable – this worked.

The RPI booted up and presented me with the login screen. The login credentials can be found on the downloads page of the RPI site, next to the OS version you have selected to use.

My RPI is now ready for me to setup network support and install an HTSQL server on it.

I’ll be going into more details on HTSQL in my next post.

Raspberry PI update

I’ve just had confirmation from Newark/element 14 that my Raspberry PI will be delivered in the batch being shipped out June 29th.

The email also contained the following information:

As you may have heard, all the Raspberry Pi units require a CE (compliance tested) mark prior to distribution. We are pleased to report that this testing has been successful, following an accelerated application, jointly conducted by the Raspberry Pi Foundation and Newark element14. So once again, we thank you for your patience as you’ve waited for the delivery of your Raspberry Pi. We’ll be sure to keep you up to date as more news becomes available.

So that is great news, and means July will be spent working on the controller for the heating system. This leaves me with May and June to build the thermostats out.

MakerBot – 3D printer on the way

I’m very fortunate in that, the company I work for has a number of electronic enthusiasts, folks interested in 3D printers, and the plain adventurous. After several chats over coffee we came up with the idea of starting a 3D printing co-operative at our work place. The idea is each employee interested buys in, and with the funds we then purchase a 3D printer and material that the co-op members can use.

After doing some research we decided to go with the MakerBot Replicator™ and have it shipped built.  The Replicator™ is a 3D printer that allows you to print in two colors at once and clocks in at around $2000 with the dual extruder.

You can see a picture of it here from the MakerBot website:


Makerbot 3D printer


Around nine of us got together and raised the funds for the printer, and our company also chipped in extra $500 to help us out. They have also graciously lent us a room to set it up in so we have a place to use it.

There is around an 8 week lead time on delivery, so the printer should arrive around the same time as the Raspberry PI is delivered. With access to the MakerBot I’ll be able to print a case for the Raspberry PI as well as cases for the Arduino Thermostats.

One of my work colleagues is a dab hand at the 3D design on Blender, so we are going to have some training session for the co-op members which should be cool.

Once the printer arrives and I get the opportunity to design some cases on Blender, I’ll post the details on this blog – including links to the 3D OBJ files so those readers interested can re-use them for their own projects.