2016-02-28

Logging to database

In OpenNethome Items like thermometers can log the values to file, so they can be presented as graphs. The values are stored in flat text files which get quite big after a while, and then the graphs gets slower to present.

Now we have got a great contribution from Peter Lagerhem; He has implemented a database home item (H2DatabaseTCPServer) and the ability to log to the database and generate the graphs from there. Since the database is much more efficient in finding values for a time period, the graphs are generated much faster. It is also easier to access the values from other programs.

The database file can be specified in two ways: either for each Item as you do for log files or one central database file which then is automatically used by all Items that log values. The central database file is configured on the settings page in the GlobalLogger field:



To specify A database log, the log name has the format:

jdbc:h2:tcp://127.0.1.1:9092//usr/share/data/logs/sqllog.log

Where /usr/share/data/logs/sqllog.log in this case is the name of the actual file and jdbc:h2:tcp://127.0.1.1:9092/ is the database connection address which you can see on the H2DatabaseTCPServer-Item.

When specify the database file name as logger, the database with data model is automatically created. You also have to create a H2DatabaseTCPServer-Item which implements the database server.

Peter also included a feature to migrate all existing log data from the text files into the database and this is done with the "UpdateGlobalLogger"-button. Note that if you have a lot of logged data this migration can take a couple of minutes.

The changes are in the nightly build - thanks Peter!