Previous posts for this project:
- [CaTS] ForgetMeNot - Index
- [CaTS] ForgetMeNot - Week 0: Project Description
- [CaTS] ForgetMeNot - Week 1: EnOceanPi and Sensors
- [CaTS] ForgetMeNot - Week 2: Elro CoCo and Pi Cam with OpenHAB
- [CaTS] ForgetMeNot - 3D Printing: EnOcean sensor bracket
- [CaTS] ForgetMeNot - 3D Printing: EnOcean rocker switch and magnet holder
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:
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!
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:
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:
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.
Top Comments