Tag Archives: Imperihome databases and Node-Red

Imperihome Database

I’ve been asked to go into a little more detail about the database side of Node-Red with Imperihome

So for example, let’s say you have a temperature sensor. My last blog on the subject shows you how to put the sensor up and show the current temperature. However, if you want to store readings and be able to click on the icon and see a graph, you have to make the data available to Imperihome in a form it understands.

Some time ago I explained my reasoning for using SQLITE on the Raspberry Pi and other SD based systems and so this explanation applies to SQLite 3 and the PHPLITEADMIN tool as implemented in my script.  I use this setup along with Mosquitto (with websockets) in all my installations.

So before we get into this – let’s define tables in a database. You can use any database you have handy or a new one – but you’ll need 3 tables. One defines the device, another defines an attribute of that device (so you might have a device which is a thermometer and the attribute might be the temperature – but there are combination devices with more than one attribute such as the combined thermometer/hygrometer so there are 2 attributes – temperature and humidity – and finally you need a third table – the data. This will contain a timestamp, the actual value, the device and an attribute so that Imperihome can request data – and it might do this for the last day, last week, last month etc.  Clearly you don’t want to send thousands of values out so for longer periods you want a reasonable number of averaged values.

Here are the three tables.

Device;

CREATE TABLE 'Device' ('DeviceID' INTEGER PRIMARY KEY,'DeviceName' TEXT NOT NULL)

DeviceData

CREATE TABLE 'DeviceData' ( 'DataID' INTEGER PRIMARY KEY, DeviceID INTEGER,'DataName' TEXT, FOREIGN KEY(DeviceID ) REFERENCES Device(DeviceID ) )

Data

CREATE TABLE 'Data' ( SequenceID INTEGER PRIMARY KEY, 'DeviceID' INTEGER NOT NULL, 'DataID' INTEGER NOT NULL, 'DataValue' NUMERIC NOT NULL, 'epoch' NUMERIC NOT NULL, 'timestamp' DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , FOREIGN KEY(DataID, DeviceID ) REFERENCES DeviceData(DAtaID, DeviceID ) )

Imperihome uses epoch but as humans have trouble with that we also include a human-readable timestamp.

Typical devices – deviceID is created automatically as you add devices – yes I could have used way more meaningful device names.

tmp5C8D

DeviceData

tmp6F46

As you can see above – one device (number 3) with two values (temp and hygro) – the rest are just called value (don’t blame me – that’s what Imperihome is asking for).

And finally some typical data – the lower values are temperature in degrees C and the higher ones are percentage humidity.

tmp33AC

All simple stuff. You need a flow which will work without modification for all data

tmp1EE7

The incoming html GET on the left is this

tmpD9BC

Then the function which says above “Request History from SQL Lite”

// This will handle any device and any attribute as long as it is in the DB
var Month = 5184000000 ; //60 Days
var Week  = 1209600000 ; //14 Days
var Day   =  172800000 ; // 2 Days
var Hour  =    3600000 ; // used to aggregate data by the hour
var Minute  =   600000 ; // used to agrgegate data by the 10 Minute
var period = msg.req.params.enddate - msg.req.params.startdate;
var groupby = Day ; // used for group by clause below

// The select will provide the starting Date for the period and the average value for the period
//The History Ui on ImperiHome expects the current period and last, that’s why double the rang than expected
if       (period <= Day)  {groupby =Minute;}// Ask for a Day and get by the minute
else if  (period <= Week) {groupby =Hour;}// Ask for a week and get it by the hour
else if  (period <= Month){groupby =Day/2;}// Ask for a Month and get it by the Day
else                      {groupby =Week/2;}// Ask for a Year and get it by the Week

msg.topic =
"SELECT MIN(epoch) as date, printf('%.2f', AVG(DataValue)) as value FROM Data" +
" WHERE DeviceID = (SELECT DeviceID from Device WHERE DeviceName = '" + msg.req.params.deviceID + "')" +
" AND   DataID = (SELECT DataID from DeviceData WHERE DataName ='" + msg.req.params.paramKey + "'" +
" AND DeviceID = (SELECT DeviceID from Device WHERE DeviceName = '" + msg.req.params.deviceID + "'))"  +
" AND   epoch >= " + msg.req.params.startdate +
" AND   epoch <= " + msg.req.params.enddate +
" GROUP BY epoch/" + groupby

return msg;

A friend of mine Peter Oakes wrote this as I’m crap at databases. It works.

The orange History Archive is a SQLITE node – just point it to your chosen database remembering that a database in SQLITE is just a file – I keep mine in a folder under /user/pi

The next function could not be simpler..

//Really complex conversion so be careful if you change this 🙂
var results = msg.payload ;
msg.payload = {"values": results };
return msg;

and you feed that to the outgoing html node.

That’s it. Imperihome will take care of the rest – all you have to do now is make sure that whenever you take, say, a temperature reading, you fire it into the database – example below.

tmp6D4F

So I save my values in global variables and here, every 5 minutes I inject the values into the database – you might do it another way.

So each of the 3 functions above (temperature, humidity, office temperature) looks like this:

msg.deviceName="dev05";
msg.dataName="value";
msg.payload=context.global.thermostat.display_temperature;
return msg;

and the function that shoves it into the database is simply….

// Set the next two variables to suit your device
// make sure there in the DB as well

var d = new Date();
var epoch = d.getTime();
msg.topic = "INSERT INTO Data(epoch, DeviceID, DataID, DataValue)" +
            "Values(" +
            epoch + "," +
            "(SELECT DeviceID from Device WHERE DeviceName = '" + msg.deviceName + "')," +
            "(SELECT DataID from DeviceData WHERE DataName ='"  + msg.dataName   + "' AND" +
            " DeviceID =(SELECT DeviceID from Device WHERE DeviceName = '" + msg.deviceName + "'))," +
            msg.payload +
            ")"
return msg;

That’s pretty much it.

Facebooktwittergoogle_pluspinterestlinkedin