Home Automation Laboratory (HAL) - Part 2 - Data Persistence

Introduction

This is part 2 of our 4 part series documenting our home automation system we lovingly call HAL. Feel free to cruise on over to Part 1 - Overview if you want some background information.

• Part 1 - Overview. We lay out the main components of our home automation system and install the core software component, OpenHAB

• Part 2 - Data Persistence. We install a MySQL database server and tools to manage it. We then integrate it with OpenHAB for the purposes of storing our home automation data indefinitely.

• Part 3 - Messaging. We install a lightweight messaging system (MQTT broker), Mosquitto, and integrate it with OpenHAB to exchange information with custom, home-grown IoT sensors and controls.

• Part 4 - Custom Interfaces. We install and configure an Apache web server and briefly discuss integrating custom web pages with OpenHAB. This will provide a platform for building custom user interfaces for our home automation system.

In this write-up, we will install a MySQL database server and a tool to help us manage it. Then we will create a database and configure OpenHAB to use that database to store various home automation data.

Prerequisite

This write-up assumes you have already installed and configured Raspbian on a Raspberry Pi. For details, please see Part 1 - Overview.

Construction

MySQL

Installing MySQL is fairly easy. Here's a web page we used. The MySQL install packages are already known to apt, so it's a simple matter to open up a console/command-line interface to your Pi and type:

sudo apt-get install mysql-server

You'll be prompted for a root password. And the install will run for a few minutes. And soon enough, you'll have a database server up and running on your Pi.

There is a MySQL client for the Pi, but it is a command line interface, so not the most user-friendly option. Nonetheless, that's how some people like to roll, if you fall into that camp, here's the command for installing it:

sudo apt-get install mysql-client

Not too tough.

For those of you that prefer a more graphical interface, there are 2 popular options:

• PHPMyAdmin. This is a web-based interface. You can install it on the same Pi as your DB server. However, it requires PHP, and a web server (typically Apache). Not terribly onerous, but a few extra bits and pieces. It works fairly well, although it can be a little slow. The benefit is, you can access it from any machine that has a web browser. Details for installing PHPMyAdmin can be found on the same page linked above for installing the MySQL server.

• MySQL Workbench. This is a full-blown piece of client software that installs on a Windows, Mac, or Unix/Linux machine. This is our preferred choice for managing our MySQL server.

MySQL Workbench

Installing MySQL Workbench is fairly straightforward. Download the installer for your chosen platform, execute the installer, and you're there.

Once MySQL Workbench is installed and launched, you will need to create a connection to the MySQL Server that you installed. You will need to know the IP address of the Pi you installed the server on as well as the root password you specified during the server install (you remember the password, right?)

Configure MySQL for integration with OpenHAB

Now that your MySQL server is up and running and you've established a connection between your server and your MySQL Workbench, it's time to connect your database server to your OpenHAB server. The first step is to create a new schema (or database) in your MySQL server:

Next, you will need to create a database user that will have access to your new schema. OpenHAB will use this user to connect to the new schema.

The final step on the MySQL side of things is to grant your new user account access to your new schema. OpenHAB will automatically create the tables it needs and populate them with data. Therefore, you'll need to grant this user fairly broad access permissions.

Configure OpenHAB for database persistence

First, we need to install JDBC Persistence MySQL. This is easily done by pointing a browser at your OpenHAB server and specifying the Paper UI:

http://<your_openhab_ip>:8080/paperui

Once the JDBC Persistence MySQL is installed, it needs to be configured so that it can connect to the server and schema that you've set-up:

Almost there. As you can imagine, a fairly robust, comprehensive home automation system can collect vast amounts of data over time. So our final step will be to tell OpenHAB which data to persist/store and how often. To this, we need to edit one of OpenHAB's configuration files.

Fire up a console/SSH session to your OpenHAB Pi. Enter the following:

$ cd /etc/openhab2/persistence
$ sudo nano jdbc.persist

This will open the jdbc.persist file in the nano editor. Modify to match the following:

// persistence strategies have a name and a definition and are referred to in the "Items" section 
Strategies { 
    // if no strategy is specified for an item entry below, 
    // the default list will be used 
       everyMinute     : "0 * * * * ?" 
       every5Minutes : "0 */5 * * * ?" 
       everyHour   : "0 0 * * * ?" 
       everyDay    : "0 0 0 * * ?" 
       default = everyChange 
} 
/* 
* Each line in this section defines for which item(s) which strategy(ies) should be applied. 
* You can list single items, use "*" for all items or "groupitem*" for all members of a group 
* item (excl. the group item itself). 
*/ 
Items { 
   // persist all items once a day and on every change and restore them from the db at startup 
   * : strategy = everyChange, everyDay, restoreOnStartup 
   // additionally, persist all temperature and weather values every hour 
      gTemperatur* : strategy = every5Minutes, restoreOnStartup 

Once your edits are complete, hit control-X to exit the nano editor. Nano will prompt you to save your changes. Enter Y to do so. Nano will then prompt you for a file name. Hit [Enter] to save your changes to the same file name.

A quick run-down on what all this means: The first section defines several "Strategies" or schedules. "every minute", "every5Minutes", "everyHour", etc. These can be used later in the file to tell OpenHAB how often to save data to persistent storage.

The second section tells OpenHAB what data to store and how often. There are 2 entries in our file:

• The first, starting with "*", tells OpenHAB to store every value it knows about into the persistent data store, every time a value changes, and once per day (using the "everyDay" strategy/schedule) whether it changes or not. It also says to read all the values from the persistent data store every time OpenHAB starts up (restoreOnStartup). This helps insure that everything is restored to its last known state if OpenHAB gets restarted.

• The second entry tells OpenHAB to store the value of any item whose name starts with "gTemperatur" into the persistent data store every 5 minutes. We have placed all of our temperature measuring devices into groups whose names start with "gTemperatur". So this entry will allow us to track temperature trends over time.

Finally

That's it! You may need to restart OpenHAB before it'll start recording data. If so, that's easily done. Open a console/command line to your OpenHAB Pi and enter:

sudo systemctl restart openhab2.service

This command will stop and restart your OpenHAB server. After a few minutes, it should be fully up and operational.

If everything is working correctly, OpenHAB will create a number of tables in your MySQL schema and start writing data to them. You can use MySQL Workbench to query these tables and see what OpenHAB is up to. Fun!

Conclusion

We now have our main home automation component writing data to persistent storage. Our jdbc.persist file allows for a ton of data to be recorded, so as we start adding sensors and devices to our system, we may need to tweak it so it's no so verbose.

Be sure to catch Part 3 - Messaging