Sunroom Logger Part 5

Data Structures

I spent some time with MySQL Workbench setting up a data structure that should allow an unlimited number of sensors to be logged to a generic structure. This database structure was deployed to a local server on my windows development machine, the Pi (running MariaDB, which appears to work with MySQL Workbench with only minor complaints), and also to my webhost. This is similar to a structure I use at work. Each ‘device’ can then have a local DB that is always available. Then some (so far unwritten) task can copy the data up to the webhost, which is used as a warehouse for the data. If for some reason the pi loses its network connection, it still can record locally with no data loss.

There is a stored procedure on the DB that the program can use to determine what kind of device it is (not hard coded!), and then another SP to retrieve the IDs for the sensors available. This makes the database into the “Single source of truth”. The last SP then allows the program to record data against a deviceID/sensorID pair that is unique to each sensor. A single device can have multiple sensors. I could technically get away with not storing the deviceID with the data, since I can look in the sensors table to get it, but I think it doesn’t hurt for now.

The entire data structure is mirrored in C#. The first revision of the program can detect if it is on Windows (simulated test environment) or Linux (actually on the Pi). it loads the database configuration from a local json file stored outside of the program structure (so it doesn’t end up in source control!)

Wiring it up

I also wired an OSEPP DHT11 sensor module to the Pi for the first time. I made a splitter cable from parts that I got at Pololu (wire, connectors) and some other random bin cables (and a breadboard for future work on the moisture sensor part of the sensor module).

Deploying the app to the Pi

Build the app in Visual Studio

Copy the compiled output directory onto the shared folder on the NAS

SSH into the Pi

copy the config.json and the app directory from the NAS onto the SD card