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:

http://myheatingserver:8080/temperature/:json

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!

Conclusion

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: http://www.raspberrypi.org/downloads

2.) I grabbed a copy of RasPiWrite for Mac from here: http://exaviorn.com/raspiwrite/

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.