search

SmartAxiom / openhab

Automatically exported from code.google.com/p/openhab
0 stars 0 forks source link

Make SQL persistence service queryable #166

Closed GoogleCodeExporter closed 8 years ago

GoogleCodeExporter commented 8 years ago 
Add support for restoreOnStartup as well as possibility to query historic 
values from the SQL persistence service.

Original issue reported on code.google.com by henrik.sjostrand on 3 Dec 2012 at 11:43

GoogleCodeExporter commented 8 years ago

Original comment by kai.openhab on 22 May 2013 at 8:13

GoogleCodeExporter commented 8 years ago 
I have modified the SQL persistence service to be queryable. Unlike the 
original SQL service, it implements a fixed database structure rather than 
relying on the alias configuration in the persist files. Relying on the alias 
meant that adding a query was difficult since table names (etc) are not known 
to OH without extra reconfiguration. It also made configuration a lot more 
messy, and (IMHO) was not necessary (of course others may disagree!).

The table structure is as follows -:
A Table called "Items" is created with an ID and the name of the item. This 
allows the service to find the table with the stored data. Data is stored in a 
table called "Item<ID>" where ID is the ID from the Items table. Time and Value 
are stored - Value is stored as a double, so mapping is used to convert non 
numeric data (although I've not tried this yet!). This is similar to the RRD 
implementation. Storing numbers allows better statistics (again, IMHO!).

The tables are generated automatically - the user just needs to set up the JDBC 
variables (address, user, password) in the openhab.cfg file as usual.

The source is in the clone https://code.google.com/r/chris-openhab.

The attached files are compiled for the latest OH (10th Aug 2013).

No doubt this needs more work - comments appreciated!

Original comment by ch...@cd-jackson.com on 10 Aug 2013 at 11:41

Attachments:

GoogleCodeExporter commented 8 years ago

Original comment by teichsta on 13 Aug 2013 at 3:43

GoogleCodeExporter commented 8 years ago 
Hi Chris,

many thanks for this contribution. Please find my review comments below:

# SqlItem
* class javadoc seems to be copy&pasted from rrd4j - please refine
* did really Kai authored this class?

# SqlPersistenceService
* please organize imports
* L147 please remove printStackTrace() and add proper logging instead
* L360 please removed unused method
* L414-417 please remove commented code
* L428-429 please remove TODO printStackTrace() and add proper logging instead
* L432-460 please remove commented code
 Best,

Thomas E.-E.

Original comment by teichsta on 18 Aug 2013 at 9:18

GoogleCodeExporter commented 8 years ago 
Thanks Thomas.
This was one of the first things I did with OH, so sorry i was a bit cut and 
past / messy :)

I've implemented your comments and also tidied up SqlPersistenceService with 
the removal of some other commented code. Changes committed and I'll push them 
onto the server tonight.

Thanks
Chris

Original comment by ch...@cd-jackson.com on 19 Aug 2013 at 11:26

GoogleCodeExporter commented 8 years ago 
thanks, could you mention the changeset, when done?

Original comment by teichsta on 27 Aug 2013 at 9:38

GoogleCodeExporter commented 8 years ago 
https://code.google.com/r/chris-openhab/source/detail?r=ff718681719d118f09fa20f5
20117aa0e514b55d

Original comment by ch...@cd-jackson.com on 27 Aug 2013 at 10:22

GoogleCodeExporter commented 8 years ago 
merged into default (see 
http://code.google.com/p/openhab/source/detail?r=3d6b443cc91ef94a982c40c2440f56c
2ab1e8963) - thanks Chris for this contribution!

Original comment by teichsta on 30 Aug 2013 at 7:18