Month: June 2012

Settings up HTSQL on Mac Lion

IOTHome 1 Comment

Introduction

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  –  http://developer.apple.com/opensource/tools/x11.html

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:

http://guide.macports.org/

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

https://distfiles.macports.org/MacPorts/

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 htsql.org for the latest bitbucket path)

hg clone https://bitbucket.org/prometheus/htsql

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

e.g.

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

Conclusion

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

IOTHome 1 Comment

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

IOTHome Leave a comment

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.