Page 1 of 2

New value item logger implementation!

Posted: Tue Jan 05, 2016 1:53 pm
by SourceCodeSourcerer
Hello everyone.

I just want to let you know about a new addition coming to the nightly build - as soon as Stefan has approved the pull request and merged with the master code.

Here's some information describing what this all means.

Background
The previous value item logger always wrote to a text file. If the file became large, it also became a lot slower in viewing a graph of the data.
So the value item logger has been enhanced to log not only to individual files but also to a database! This will speed up the graph view many times.
I've chosen H2 as the database (I'm not associated with them), and you can find more information here: http://www.h2database.com/html/main.html.

All home items capable of logging their value (must implement the ValueItem class) and has a LoggerComponent object (pretty much every thermometer and moist item), will continue log to the file configured in the LogFile field. The name of the file is unchanged - so nothing will break.
But it is enhanced so you can prefix the name with file: for clarity and it will still log to a file. And if you prefix with jdbc:h2, it will log to a database instead. Configuring the individual LogFile field is what I'd like to call a local logger configuration. I suggest that you leave this field, as-is.

Now, the good part is that the Server Settings page has a new field, GlobalLogger, which is where you enable logging of all ValueItem home items - the global logger configuration. Simply configure the field the same way as mentioned earlier; either file: or jdbc:h2.

What this means is that it is now possible to let your home item optionally log locally and/or optionally log globally.

Here are some examples of how one would configure this field:

GlobalLogger
jdbc:h2:tcp://localhost/nhs_values.log
file:global_values.log, or simply global_values.log

Let's break that database configuration down:
  • jdbc:h2 - prefix that states a database logger is to be configured
  • :tcp://localhost/ - important as it sets up a connection with a new home item named H2DatabaseTCPServer running locally to the NetHomeServer - more on that later.
  • nhs_values.log - the name of the database file name. There is currently one important table in this database and the name is VALUELOGGER.
The location of the database is by default the logger directory. On my raspberry pi this is the /var/log/nethome/.
The table will hold all home item values, and it can become large. But it will be a lot faster than those text files.

H2 Console
It is useful to be able to have a look at the database, and H2 allows us to do that by using a database console.
You can download their console from here: http://www.h2database.com/html/download.html.
On my Mac I double click the downloaded file: h2-1.4.190.jar, which launches the console. I can then see a small icon in the menu bar with some options to launch the H2 Console.
The address to the console is on my setup http://192.168.168.107:8082/. Yours might be different...
The fields I use are:
"Generic H2 Server"
Driver Class: "org.h2.Driver"
JDBC URL: "jdbc:h2:tcp://localhost/nhs_values.log"
User name and password are both set to "sa". These passwords will probably be configurable in a later release of the H2 integration.

H2DatabaseTCPServer
This is a new Home Item that is required in order to log to the database. Make sure to create one, and it is available in the Ports section.
You can leave out the port number on creating the item, as H2 will assign one for you - the port number is displayed when created. You can tweak it if you need to. The H2 TCP database server will run in its own process space locally to the NetHomeServer. The ConnectionString will expand to the ip adress shown in the home item, where as it is probably enough by setting the jdbc:h2 connection to localhost. You may have to elaborate with this depending on your environment. I guess some of you may want to have the database running on another machine - which I haven't tried yet - so please share your experiences with the rest of us.

With the H2DatabaseTCPServer and the GlobalLogger configured, you should be able to start viewing any incoming values of home items. However, you will not find many on startup, which brings us to the next section.

Import all local text files to the global logger
Yes, this is supported by a new button, Update Global Logger, also found on the Server Settings page.
When clicked, it will find all home items that have a configured LogFile that is a text file, and then import all values to the global logger.
No duplicates will be imported, so you can click this button at anytime and it will import any new values from the text file that are not in the database.

Be aware of that the import may take a considerable long time, especially on a raspi device.
On my Mac, it's a lot faster of course, but it depends on the size of the log files.
What I do is to connect via ssh to the raspi device and then tail the log file, like so: tail -f /var/log/nethome/HomeManager0.log. This will let me know how the import progresses.

So, a recap:
  • Upgrade to the nightly build that includes the new Value Item Logger and restart your server.
  • Create a H2DatabaseTCPServer item
  • Configure the GlobalLogger
  • Important: Do either one of the following:
    1 Click to view at least one home item graph
    2 Let some values be stored to the database. This varies as a new startup of the net home server will not logg values until the next coming hour.
    Your best bet is the alternative #1.
    In any case... this will automagically create the VALUELOGGER table and you can see this yourself in the log showing up as something similar to "Enabled global logging of type: LoggerComponentH2Database with descriptor: jdbc:h2:tcp://localhost/nhs_values.log".
  • With the table created, you should now be able to import all previous log files into the database.
  • Refer to the logs for debugging/info messages.
  • Use the H2 database console
    Tip! Start the console after starting NetHomeServer or you won't be able to connect correctly. And if the console is started before the NetHomeServer, the port will probably already be occupied by the console as it will be started as the TCPServer. Check the NetHomeServer logs for messages.
I hope you will find this enhancement to the NetHomeServer useful. You can see the code changes here:

https://github.com/SourceCodeSourcerer/ ... dbc_logger

If you have any comments, please leave a note here at the forum.

I also hope that this can be a trigger for some of you who wish to build upon this new enhancement and continue contribute to this great open source project!

Cheers!

/Peter

Re: New value item logger implementation!

Posted: Tue Jan 05, 2016 7:43 pm
by stefangsbb
This is absolutely excellent! Open source at its best! I have wanted to add support for logging to a database but never had time to do it. I will take your pull request and make a new nightly build. Thanks!!

Re: New value item logger implementation!

Posted: Wed Jan 06, 2016 10:51 am
by Jocke.g
I switched to OpenNetHome from SwitchKing just a couple of days ago, so I'm new here. The main reasons was because I could not get my blinds to work, and that the development has stopped. I really start to like OpenNetHome, especially that it is still alive, and new features are comming all the time. Thanks for great work!

And now, on topic:
This update is great! One of the bigger drawbacks I found with OpenNetHome is that I cant install telldus drivers and pick up events by my own code as well. Earlier I used to save temperature values in a MySQL database. So i would like to have the possibility to do the same now. Since the H2 implementation uses JDBC, it should not be a big issue to add support for MySQL too.

The main reason not to use H2 is that I already have a server running MySQL on a hard drive disk. I dont want to save the data on sd-card. Sd-cards dont last long if you save to them all the time. I have had some crashes, resulting in my home automation system not working for some month due to lack of time/pleasure to start all over again.

Re: New value item logger implementation!

Posted: Wed Jan 06, 2016 11:30 am
by Jocke.g
I was going to try out this H2 database, but i could not get it to work. I cant find the H2DatabaseTCPServer under Ports section.

I have upgraded OpenNetHome with the sript "install/raspbian/upgrade_nethome_nightly.sh", it seemed to work well. I have also rebooted my Pi. I have also cleared my browser cache, just in case. Server Settings page is telling me I use version 2.1-SNAPSHOT-1c78c, and I do got the LogFile field (not GlobalLogger as forum post tells) in settings.

Re: New value item logger implementation!

Posted: Wed Jan 06, 2016 7:34 pm
by stefangsbb
Ok, hold your horses - I had not merged the pull request from Peter yet! Now it is merged and available in the nightly build.

Re: New value item logger implementation!

Posted: Wed Jan 13, 2016 1:02 pm
by TotalPE
Am i Stupid? I dont get any updates to my graphs after the latest update? I have not started using the H2 datbase, I haven not changed anything in the items that shuld have graphs, I thougt that i would still work as before if i did not change anything? It reads the Logfile if I scroll back to one day before I did the latest update, there are graph for that day? I did not plan to start using the H2 database just yet.

Regards

Re: New value item logger implementation!

Posted: Wed Jan 13, 2016 7:57 pm
by TotalPE
Whell! I Started using the H2DatabaseTCPServer now, typed in the GlobalLogger like this jdbc:h2:tcp://localhost:9092/nhs_values.log
Pressed the Update Global Logger button
And now the the Graph is gone, even the data that i had before i start using H2 Database is gone.

The Log says:
16.01.13 20:49:15 ValueItemLoggerH2Database No entries were imported for item id: 126 from /var/log/nethome//home/nethome/Termometer700.Log containing 0 rows.
16.01.13 20:49:15 ValueItemLoggerH2Database java.io.FileNotFoundException: /var/log/nethome/home/nethome/Termometer700.Log (No such file or directory)
16.01.13 20:49:15 HomeServer Termometer 700 Pappa's Sovrum with id 126 has a private logfile ('/var/log/nethome//home/nethome/Termometer700.Log') that will be merged with the global database.
16.01.13 20:49:15 ValueItemLoggerH2Database No entries were imported for item id: 124 from /var/log/nethome//home/nethome/Termometer600.Log containing 0 rows.
16.01.13 20:49:15 ValueItemLoggerH2Database java.io.FileNotFoundException: /var/log/nethome/home/nethome/Termometer600.Log (No such file or directory)
16.01.13 20:49:15 HomeServer Termometer 600 Utomhus Baksida with id 124 has a private logfile ('/var/log/nethome//home/nethome/Termometer600.Log') that will be merged with the global database.
16.01.13 20:49:15 ValueItemLoggerH2Database No entries were imported for item id: 288 from /var/log/nethome//home/nethome/Termometer600Grannen.Log containing 0 rows.
16.01.13 20:49:15 ValueItemLoggerH2Database java.io.FileNotFoundException: /var/log/nethome/home/nethome/Termometer600Grannen.Log (No such file or directory)
16.01.13 20:49:15 HomeServer Termometer 600 Grannen with id 288 has a private logfile ('/var/log/nethome//home/nethome/Termometer600Grannen.Log') that will be merged with the global database.
16.01.13 20:49:15 ValueItemLoggerH2Database No entries were imported for item id: 203 from /var/log/nethome//home/nethome/Termometer3200.Log containing 0 rows.
16.01.13 20:49:15 ValueItemLoggerH2Database java.io.FileNotFoundException: /var/log/nethome/home/nethome/Termometer3200.Log (No such file or directory)
16.01.13 20:49:15 HomeServer Termometer 3200 Vardagsrummet with id 203 has a private logfile ('/var/log/nethome//home/nethome/Termometer3200.Log') that will be merged with the global database.
16.01.13 20:49:15 ValueItemLoggerH2Database No entries were imported for item id: 76 from /var/log/nethome//home/nethome/Termometer300.Log containing 0 rows.
16.01.13 20:49:15 ValueItemLoggerH2Database java.io.FileNotFoundException: /var/log/nethome/home/nethome/Termometer300.Log (No such file or directory)
16.01.13 20:49:15 HomeServer Termometer 300 Moa's Rum with id 76 has a private logfile ('/var/log/nethome//home/nethome/Termometer300.Log') that will be merged with the global database.
16.01.13 20:49:15 ValueItemLoggerH2Database No entries were imported for item id: 66 from /var/log/nethome//home/nethome/Termometer200.Log containing 0 rows.
16.01.13 20:49:15 ValueItemLoggerH2Database java.io.FileNotFoundException: /var/log/nethome/home/nethome/Termometer200.Log (No such file or directory)
16.01.13 20:49:15 HomeServer Termometer 200 Filip's Rum with id 66 has a private logfile ('/var/log/nethome//home/nethome/Termometer200.Log') that will be merged with the global database.
16.01.13 20:49:15 ValueItemLoggerH2Database No entries were imported for item id: 287 from /var/log/nethome//home/nethome/HygrometerGrannen600.Log containing 0 rows.
16.01.13 20:49:15 ValueItemLoggerH2Database java.io.FileNotFoundException: /var/log/nethome/home/nethome/HygrometerGrannen600.Log (No such file or directory)
16.01.13 20:49:15 HomeServer Hygrometer 600 Grannen with id 287 has a private logfile ('/var/log/nethome//home/nethome/HygrometerGrannen600.Log') that will

Regards

Re: New value item logger implementation!

Posted: Fri Jan 15, 2016 6:27 am
by stefangsbb
My logs also stopped working the last days. I found an issue in the initialization of the new GlobalLogger field which I have corrected. The fix is in the nightly build.

Re: New value item logger implementation!

Posted: Fri Jan 15, 2016 11:59 am
by TotalPE
Supergreat!! Ill try it out...
Thanx for the good job!

Regards

Re: New value item logger implementation!

Posted: Fri Jan 15, 2016 12:21 pm
by TotalPE
Okey, now it show a graph, but it culd not import the old data.