Imperihome Database

Peter Scargillimperihome37 Comments on 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.

37 thoughts on “Imperihome Database”

  1. Hi Pete,
    Why the column DeviceID in the Data table?
    If I understood correctly this field is already in the DeviceData table and DataID is enough to find the DeviceID.

  2. Hi Pete,

    I just started working with Node-Red to get my LoRa sensors working and I was looking for a quick way to demonstrate this as part of a home control system.
    Thanks to your great articles and video. and the other contributors, I managed to get the whole thing working within the hour.

    I know – it will take me days to get all the details figured out but it’s a nice beginning.

    Thanks

  3. Hi Pete, managed to get everything in, it all seem to have gone in OK, the Apache server is working.

    how do I get to the PHPLITEADMIN admin page?, everything is setup as per your script.

  4. Hi Pete,
    great video on Imperihome with Node-Red, Could you tell me how you installed PHPLITEADMIN on your rpi
    I have spent the weekend trying all different ways from blogs on google but to no avail, I know from your other comments you don’t want to go into data base’s, but any help with just whats required to install it would be much appreciated.

  5. Looking for a little help…

    I already have a mysql database and have been storing sensor data for a couple years. Inspired by Pete’s example I have a query that returns the following to an imperihome history request. All I get is “Warning, No data for specified timeframe”.

    { “values”: [ { “value”: 52.4, “date”: 1460347636000 }, { “value”: 52, “date”: 1460351009000 }, { “value”: 51.3, “date”: 1460357753000 }, { “value”: 51.1, “date”: 1460358415000 }, { “value”: 50.6, “date”: 1460362811000 }, { “value”: 50.4, “date”: 1460366063000 }…

    Mysql dosen’t like the 13 digit unix timestamp so I divide by 1000 to get a compatible 10 digit version. Likewise I multiply the mysql unix timestamp by 1000 for Imperihome.
    Here’s my test mysql query:
    SELECT Value/10 as value, unix_timestamp(timestamp)*1000 as date FROM Readings where idNames= ‘1’ and timestamp between from_unixtime(1460088000) and from_unixtime(1460486578) group by hour(timestamp);

    Is there a limit to the number of values returned? I’m only returning 1 value/hour in this test. Also the “last 24 hours” seems to return more like 48 hours.

    Any suggestions?

    1. Doesn’t look that powerful to me… I use PHPLITEADMIN which is quite good and free. I think the point here surely is that SQLITE is meant more for local data – you would not normally access it externally? Oh, if anyone has a more powerful manager than PHPLITEADMIN – do let me know but not something you have to sit at the Raspberry Pi to operate….

      1. Hi Pete, it should be good enough for your needs – especially if you want to access the sqlite database tables from another pc which doesn’t have PHP and a MAMP installed.
        However, if you want to do as you say, then I’m afraid you really should consider the security implications of what you’re opening yourself up to.

  7. I have it all working now on MYSQL (thanks for your help Steven). I have also added a flow that autocreates the db data (devices/devicedata) if it does not already exist.

      1. My table creation code for MYSQL (with help for Steven)
        1) Create device, I have specified a 20 char device name that must be unique. This is needed by my autodevice population nodes but may not be an issue for most people.

        CREATE TABLE Device (DeviceID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,DeviceName VARCHAR(20) NOT NULL UNIQUE)

        2) DeviceData
        CREATE TABLE DeviceData (DataID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, DeviceID INTEGER NOT NULL,DataName TEXT, FOREIGN KEY(DeviceID ) REFERENCES Device(DeviceID ) )

        3) Data: Needed Auto Increment and BIGINT settings. Note that the Foreign Key parameters are changed as mysql is more rigid than sqllite
        CREATE TABLE Data (SequenceID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,DeviceID INTEGER,DataID INTEGER,DataValue NUMERIC NOT NULL, epoch BIGINT NOT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(DeviceID,DataID) REFERENCES DeviceData(DeviceID,DataID))

        4) change to History Function node: the sql in the function node needs to be slightly amended
        msg.topic =
        “SELECT MIN(epoch) as date, 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

        Hope that helps

  9. Hi Pete,

    Why are you storing epoch and timestamp in the database? That seems to me like redundant data.

    If you’re storing epoch, then you can use node-red to convert it to a timestamp if you want to display it later. Or vice versa (storing the timestamp and converting to epoch when you need to send it to ImperiHome).

    1. Hi Steven – this was never intended as an example of database programming – both are stored so that there’s a human readable version available when using PHPLITEADMIN as I’ve not found a better web-based SQLITE editor that can sit on the PI.

      🙂

  10. Ive managed to create the databases in mysql using:

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

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

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

    However I am having problems with the adding data to the DBs – has anyone tried this with MYSQL?

    The sql statement created is:
    INSERT INTO Data(epoch, DeviceID, DataID, DataValue)Values(1458756676951,(SELECT DeviceID from Device WHERE DeviceName = ‘BedTemp’),(SELECT DataID from DeviceData WHERE DataName =’value’ AND DeviceID =(SELECT DeviceID from Device WHERE DeviceName = ‘BedTemp’)),22)

    and error:
    Error: ER_DUP_ENTRY: Duplicate entry ‘0’ for key ‘PRIMARY’

    It isnt creating the Devices in the database….

      1. I understand, I am trying to translate it into mysql. I have modified the CREATE TABLE commands into mysql code above.

        1. If you’re using mysql, you need the primary keep to be auto-increamented.

          What’s happening is that everytime you add an entry to the DB, “SeqyenceID” is the same: 0

          Try changing

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

          To

          CREATE TABLE Data (SequenceID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,DeviceID INTEGER,DataID INTEGER,DataValue NUMERIC NOT NULL, epoch NUMERIC NOT NULL, timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(DeviceID,DataID) REFERENCES DeviceData(DeviceID,DataID))

          Not the AUTO_INCREMENT

          I think…

          2. thanks Steven – it’s moving in the right direction, although I’m not getting correct values for epoch (999999999) and nothing in Device or DeviceData

          5. Also, if DeviceData and Device tables are currently empty, then you won’t get anything from them when doing your INSERT code.

          6. Thanks Steven – I tried it with DECIMAL(14) which worked, and have now switched to BIGINT.
            Regarding the population of DeviceData and Device, I was expecting thes to be populated automatically as per Petes article – do I need to create these Devices manually or is there a flow missing?

    1. It is but it still has some glaring items missing:

      A simple button
      A simple LED
      A simple slider

  12. Hi Peter
    I’m starting to setup a system motivated from lot’s of you posts.
    The idea is that sensor are sending theyr meassurements to an mqtt. But for a while i was unsure of how to deal with hostorical data. Now i have the idea that i write a node who listens on the mqtt and put that data into a database similar as per your description. But what i dislike about this approach on the display side of things i’ll have two connections one to mqtt for current data and another on to the db for historical data. Is this how it’s done nowadays or is there a better approach?

    1. Not sure what you mean by “is that how it’s done nowadays”.. it’s how I did it…. I could have taken the last item off the database – but that gets refreshed when the APP is on – so it seems a little excessive when a global var does the job 🙂 And it works…

Comments are closed.