Previous posts for this project:

 

 

Introduction

 

Another week, another blog post. This time I've been learning to work with persistence of data and visualising the history of certain sensors.

 

As always, you can find links to previous parts of this project at the top of this page.

 

Persistence

 

OpenHAB offers different ways to persist data: databases, log files, etc ... https://github.com/openhab/openhab/wiki/Persistence

 

I decided to go for the MySQL option for no specific reason other than the fact that I've used MySQL before and I know how to use it.

 

MySQL on Pi

 

 

Install MySQL


The first step is to install the MySQL server and client applications:


pi@webserver ~ $ sudo apt-get install mysql-client mysql-server


Verify the installation by connecting to the MySQL server:


pi@webserver ~ $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.37-0+wheezy1 (Debian)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>























 

Hooray, MySQL server is up and running!

 

Create database

 

To demonstrate the manual creation of a database, I listed the databases, created a new one and listed them again for verification.

 

List the databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.09 sec)























 

Create a new, empty database:

mysql> create database openhab;
Query OK, 1 row affected (0.01 sec)























 

Verify the new database is there by listing them again:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| openhab            |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)























 

 

Create user

 

After creating the database, the next step is to create a user to access that database.

 

Using the same command line, I created user "openhab" with password "openhab" which has to connect via localhost.

 

mysql> CREATE USER 'openhab'@'localhost' IDENTIFIED BY 'openhab';
Query OK, 0 rows affected (0.02 sec)























 

Assign privileges

 

The newly created user "openhab" does not have any permissions on the database we created. To change this, the "GRANT" statement is used.

 

With following command, I will give the "openhab" user full permission on the "openhab" database:

 

mysql> GRANT ALL PRIVILEGES ON openhab.* TO 'openhab'@'localhost';
Query OK, 0 rows affected (0.40 sec)






















 

Verify and test

 

With all commands executed, it is time to verify all works as expected.

 

A first sanity check is to verify by checking the database info:

 

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed





















 

mysql> select * from db \G;
*************************** 1. row ***************************
                 Host: localhost
                   Db: openhab
                 User: openhab
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)





















 

In the above output, we can see that user "openhab" has all privileges (except the "GRANT" privilege) on the openhab database. This looks good.

 

A final check is to connect to the database as user "openhab".

 

mysql> quit
Bye





















 

pi@webserver ~ $ mysql -u openhab -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.37-0+wheezy1 (Debian)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>





















 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| openhab            |
+--------------------+
2 rows in set (0.00 sec)





















 

mysql> use openhab;
Database changed





















 

mysql> show tables;
Empty set (0.01 sec)





















 

 

Configure OpenHAB

 

With the MySQL server set up with database and user, it was time to configure OpenHAB to use it by following the steps documented here: https://github.com/openhab/openhab/wiki/MySQL-Persistence

 

Add-on

 

First step is to enable the MySQL persistence add-on by ensuring it is available in the add-ons folder:

 

pi@webserver ~ $ cd /opt/openhab/addons

pi@webserver /opt/openhab/addons $ ls -l *mysql*
-rw-r--r-- 1 root root 832433 Jun 16 02:29 org.openhab.persistence.mysql-1.5.0.jar



















 

If it's not there, you can download the add-ons as described in my first post: [CaTS] ForgetMeNot - Week 1: EnOceanPi and Sensors

 

Configuration

 

By creating a persistence configuration file, we are able to specify the persistence behaviour:

  • when are values persisted ?
  • which values are persisted ?

 

To do this, a configuration file needs to be created.

 

pi@webserver ~ $ sudo touch /opt/openhab/configurations/persistence/mysql.persist


















 

I edited the configuration file using the syntax from the documentation. To start, I kept it simple and decided to persist ALL values, only when they CHANGE:

 

Strategies {
    default = everyChange
}

Items {
    * : strategy = default, restoreOnStartup
}

















 

Service

 

The persistence service needs to be configured and pointed to the database that was created earlier.

 

pi@webserver ~ $ sudo nano /opt/openhab/configurations/openhab.cfg
















 

In the "SQL Persistence Service" section, enter the correct url, username and password of the database:

 

############################ SQL Persistence Service ##################################
# the database url like 'jdbc:mysql://<host>:<port>/<user>'
mysql:url=jdbc:mysql://127.0.0.1:3306/openhab

# the database user
mysql:user=openhab

# the database password
mysql:password=openhab

# the reconnection counter
#mysql:reconnectCnt=

# the connection timeout (in seconds)
#mysql:waitTimeout=
















 

Test

 

With everything set up on OpenHAB, it was time for testing again!

 

First, I tried opening a chart of the temperature sensor by using following link: http://192.168.0.205:8080/chart?items=EnOcean_sensor_01809DC1&period=h&service=mysql

This generates a *.png image server side to visualise the history of my temperature sensor over the last hour. This was the result:

Screen Shot 2014-08-01 at 23.03.45.png

 

The graph is pretty empty. This could be because persistence was only just enabled and the temperature might not have changed yet.

So I decided to wait it out and request the graph again later.

 

Still nothing. I connected to the database and verified if any data was being generated, as the temperature was changing on the web interface.

 

mysql> use openhab;
Database changed

mysql> show tables;
Empty set (0.00 sec)














 

The database was completely empty and didn't even contain tables. I decided to restart openHAB to see if it helped.

 

And it did ... persistence was now working properly!

chart.pngchart (1).pngchart (2).png

 

Integrate

 

Finally, I integrated the chart in the openHAB GUI by specifying a chart in the sitemap:

 

sitemap demo label="Main Menu"
{
  Frame label="EnOcean" {
  ...
  Chart item=EnOcean_sensor_01809DC1 period=D refresh=10000
  ...
  }
}











 

Et voila, the result:

Screen Shot 2014-08-02 at 09.29.09.png

 

Combine, control, calculate

 

It's also possible to combine multiple values into one chart.

 

To do this, I created a group to which two temperature sensors are associated. The items are defined as follows:

Group Temperature_Chart
Number EnOcean_sensor_01809DC1 "Office [%.1f °C]" <temperature> (Temperature_Chart) {enocean="{id=01:80:9D:C1, eep=A5:02:05, parameter=TEMPERATURE}"}
Number EnOcean_sensor_0181A67A "Veranda [%.1f °C]" <temperature> (Temperature_Chart) {enocean="{id=01:81:A6:7A, eep=A5:02:05, parameter=TEMPERATURE}"}






 

The chart defined earlier had a static period. OpenHAB offers the possibility to visualise items based on conditions.

This is particularly useful to change the period of a chart using different buttons. The sitemap is defined like this:

 

Switch item=Temperature_Chart_Period label="Chart Period"mappings=[0="Hour", 1="Day", 2="Week"]
Chart item=Temperature_Chart period=h refresh=300 visibility=[Temperature_Chart_Period==0, Temperature_Chart_Period=="Uninitialized"]
Chart item=Temperature_Chart period=D refresh=1800 visibility=[Temperature_Chart_Period==1]
Chart item=Temperature_Chart period=W refresh=3600visibility=[Temperature_Chart_Period==2]






 

Three charts are defined, but only one will be visualised at the time, based on the value of the "Temperature_Chart_Period" button.

And finallyTemperature_Chart_Period

Finally, I put an average temperature as the label on the group. The average value is calculated using rules and updated every time one of the temperatures changes:

 

The item:

Number Average_temperature "Average Temperature [%.1f °C]" <temperature>





 

The rule:

rule "Average temperature"
when
  Item EnOcean_sensor_01809DC1 changed or
  Item EnOcean_sensor_0181A67A changed
then
  var temp1 = EnOcean_sensor_01809DC1.state as DecimalType
  var temp2 = EnOcean_sensor_0181A67A.state as DecimalType
  var average_temp = (temp1.floatValue + temp2.floatValue) / 2

  postUpdate(Average_temperature, average_temp)
end





 

The following pictures give a view on the combined result:

Screen Shot 2014-08-17 at 09.26.52.pngScreen Shot 2014-08-17 at 09.27.23.pngScreen Shot 2014-08-17 at 09.27.46.png

With data being collected over a longer period of time, patterns start to emerge.

In the above graph (the week overview) it is clear that my office temperature is rather constant, as opposed to the veranda which is fluctuating much more.

Peak temperature in the veranda is around mid-day which makes sense.