CuPID Remote : Read, Database, Logging and UI

The Project

Building on our previous post, where we laid out the fundamentals for our Mote communication in a temperature Mote, and built up a modular Mote sketch that allows us to reprogram parameters via serial and radio, this time we’ll build up the processing back end that will get our data into our CuPID gateway and our User Interface – a web browser.

So we’ll set out to do the following:

  • Process IO data on gateway end into remote table
  • Process IO data into control and display algorithms alongside local data in the gateway

The Basics

We talked briefly about getting data into our CuPID from our gateway Moteino in another post, but we’ll rehash the basics here just to cover the bases.

We’ve got our gateway Moteino talking to our remotes, and passing on the data over serial to our Pi/CuPID, which is listening on the serial port /dev/ttyAMA0. It parses out the data between message demarcations and ends up with a nice json-formatted message, which it drops into a dictionary. We end up with something like this:

{'iovalue': '0', 'RX_RSSI': '-67', 'iomode': '01', 'iopin' : '04', 'nodeid': '2'}

This message comes from node 2, which is telling us that pin 4 is in mode 01 (digital output), and currently has a value of 0 (off). Cool. Let’s get it into tables, where it will be useful!

Tabling the Data

We are going to have a table of values brought in from the remotes, aptly called ‘remotes’, so we’ll throw this in there. Before we do so, however, let’s think about the structure of the table. We want enough fields to be present so that we can quickly identify whether a record should be inserted or replaced. To do this, we’ll set up a few auxiliary variables:

  • msgtype – this is metadata that will tell our scripts how to process
  • keyvalue – a unique value for the data to be reported. will vary with msgtype
  • keyvaluename – an identifier for the keyvalue, for handy use

Based on the keys present in the data, we’ll classify the message, and do a smart replace in the remote table.

So, for example, for two common message types:

runquery = False
        nodeid = datadict['nodeid']
        querylist = []
        if 'iovalue' in datadict:
            # iovalue type message
            try:
                msgtype = 'iovalue'
                keyvalue = datadict['iopin']
                keyvaluename = 'iopin'
            except:
                print('oops')
            else:
                runquery = True

        elif 'owdev' in datadict:
            # 1Wire message
            try:
                msgtype = 'owdev'
                keyvalue = datadict['owrom'][2:]
                keyvaluename = 'owrom'
            except:
                print('oops')
            else:
                runquery = True
        if runquery:
            deletequery = pilib.makedeletesinglevaluequery('remotes',{'conditionnames':['nodeid', 'keyvalue','keyvaluename'],'conditionvalues':[nodeid,keyvalue,keyvaluename]})
            insertquery = pilib.makesqliteinsert('remotes',  [nodeid, msgtype, keyvaluename, keyvalue, stringmessage, pilib.gettimestring()], ['nodeid', 'msgtype', 'keyvaluename', 'keyvalue', 'data', 'time'])
            querylist.append(deletequery)
            querylist.append(insertquery)
            pilib.sqlitemultquery(pilib.controldatabase, querylist)

So here, for each message type, we create a key with a value and a name. For io values, this is ‘iopin’ and then the number of the pin. This uniquely identifies a table entry. If an entry exists with the same nodeid, key name, and key value, we will replace it. We do this by constructing a query with our auxiliary function, makedeletesinglevaluequery, which takes a list of conditions and values and constructs a query. We then create an insert query with our data, which consists of the above, a data field that contains the raw message, and a timestamp. We can do most of the above with foreign key constraints on the database, but we keep it in our script for now. Running our serial monitor with this processing sequence, we get what we expect in our remotes table:

Our basic motes table, viewed here in phpliteadmin
Our basic motes table, viewed here in phpliteadmin

We’ll need to add in a hygiene function to remove old entries, but this will work for now. We’re now ready to do some postprocessing on our table, but first let’s create a basic page to display our mote table. Some copy pasta of existing pages and a little jQuery later, and we have this:

Our basic Mote table.
Our basic Mote table.

Great! Now let’s get it into our IO tables, so we can view, log and process it just like everything else.

Integrate the IO

To get our remote data into our IO tables, we’ll add the mote as an interface into our interfaces table. This way, we can selectively enable and disable processing of individual motes, and we can add options for poll period, how stale data needs to be before we ignore it, and other good stuff.  For now, we create a basic interface entry:

Our interfaces table entry for our Mote will allow selectively setting options and enable/disable. It will be processed just like our other interfaces.
Our interfaces table entry for our Mote will allow selectively setting options and enable/disable. It will be processed just like our other interfaces.

Alright, so we just need to add a case for our updateio script to process the entries. This takes a little py-fu, but it’s not too bad. We’ll need to process each message type separately, and we cheat on the name of each input for the moment, as we don’t feel like setting up metadata entries in our ioinfo table just yet. We are pleasantly greeted:

Our mote entries, all wrapped up and inserted into our IO table. We're now ready to process them as if they were local inputs!
Our mote entries, all wrapped up and inserted into our IO table. We’re now ready to process them as if they were local inputs!

We can now use our remote inputs as if they were local! Let’s take a view in our dataviewer. We scroll down to find our io automatically logging (default of 100 points), and select the onewire sensor. We’re done!

Our imported data in our dataviewer. We're now transparently reading remote data as if it were local.
Our imported data in our dataviewer. We’re now transparently reading remote data as if it were local.

Icing

Let’s revisit the idea of metadata for these channels. We hardcoded colloquial names for our mote inputs based on their unique input IDs, but it would be ideal if we again treat them like our other IO and give them metadata entries. This will also allow us to add other options, such as scaling. So we add a text column to our ioinfo table, and create entries for our remote devices. We can do this simply by editing the text in the IO table and pressing “update”:

Updating metadata for our remote inputs.

Next, we’d like to add a scaling factor. Our analog input being reported here, iopin 20, is hooked up to a voltage divider that will monitor battery input. So the value, 512, is 513 / 1024 * Vcc, where Vcc is 3.3V. We’re reading about 1.65V. Our voltage divider is a 470k resistor and a 1M resistor, with the 470k on the ground side. In other words, if we are reading 1.65V, the voltage at the high side is 1.65V * (1000 + 470)/470 = 5.2V. This is about right, within error on the resistors. We probably shouldn’t be using 5V power for our 1Wire sensor which will put data at 5V where Vcc is 3.3V, but oh well. Seems ok. So this gives us a total scaling factor of (1470/470)/1024*3.3 =0.0101 . We should technically add 1 to the input value, as it’s zero-indexed, but we’ll ignore that for now. Later, we’ll parse formulas in python, but leave that for another day.

So back to how we’ll make this happen. We’ll put a json-encoded option of ‘scale’ into the options field of the ioinfo metadata table. Then, when we process our io, we’ll see if this field exists, and scale it as we process it into the io table. A little more py-fu and this happens. We make the entry manually using phpliteadmin, since we don’t have an edit page set up for this options field yet:

Adding our options field in manually in phpliteadmin. We add scale to our battery monitor channel to scale it properly.
Adding our options field in manually in phpliteadmin. We add scale to our battery monitor channel to scale it properly.

Now let’s flip back to our inputs page. Just like magic, we’re all scaled! Now we have an accurate battery monitor that will log!

Our Battery monitor is updated and scaled, as we specified in our metadata table.
Our Battery monitor is updated and scaled, as we specified in our metadata table.

 Next time:

  • Programming our Motes to send channel status data to our gateway
  • Setting up our gateway to reprogram our Motes when they come online, using a table of set values, and

Reference

The above makes use of the open source libraries available on github here:

Explanation and installation here:

 

One thought on “CuPID Remote : Read, Database, Logging and UI”

Leave a Reply

Your email address will not be published. Required fields are marked *