Persistence support

[GoogleCodeExporter]

There should be some way to persist the states of items, which would be useful 
in different use cases:
 # openHAB could remember the last state on a server restart
 # time series could be used to draw graphs and to provide historic data
 # diff counters could be based on it (e.g. consumed electricity since midnight)

These examples make clear that there are different levels of persistence 
required:
# only store latest state (mainly useful for restarts)
# keep all past states and store a new one on each update
# store the current state with a fixed frequency (e.g. every minute), i.e. 
independently of update events (useful for smooth graphs)
# store states in a round-robin database (rrd), so that it does not grow in 
size over time

Preferably, an embedded implementation could be done, so that no separate 
(database) server process is required. The "Oracle Berkeley DB Java Edition" 
might be a good option for this.

Nonetheless should the persistance API designed in a way that it is independent 
of a concrete implementation. It should be foreseen that the data could also be 
stored "in the cloud", i.e. the data is sent to the web to some service 
provider.

Original issue reported on code.google.com by kai.openhab on 21 Mar 2011 at 12:46

[GoogleCodeExporter]

Hi Kai,

About persistence (i prefer to move our conversation here), take a look at DB4O 
deployed in a OSGi environment.

http://www.osgi.org/wiki/uploads/CommunityEvent2007/PersistenceForOSGi_CRosenber
ger.pdf

I think that it could be a good idea to have such a service in OSGi and then, a 
specific bundle could manage the item/state info in openHab.

This way, other bundles could use persistence service and you could develop 
specific code for openHab items persistence requirements, and also for query 
this info.

Do you think it could be a good idea?

regards

Original comment by dmora...@gpm.es on 22 Aug 2011 at 11:59

[GoogleCodeExporter]

I didn't yet know about db4o for OSGi, this sounds indeed very interesting with 
a very low footprint.
Do you know by chance, if there is some out-of-the-box time series support? 
I.e. is it possible to store the same object multiple times without overwriting 
the last version, but by simply being able to query by timestamps? If not, we 
would probably have to wrap each item instance into something with a timestamp 
before storing it.

Original comment by kai.openhab on 22 Aug 2011 at 12:15

[GoogleCodeExporter]

Yes, i think (we are starting with db4o, too). A couple of links:

http://measuringmeasures.com/blog/2009/1/20/building-a-time-series-databse-in-db
4o.html
http://developer.db4o.com/Blogs/Product/tabid/167/entryid/131/Default.aspx

I think that it's an already walked path, so it could be easy to deal with.

We will do some research about that so i will keep you update.

Regards

Original comment by dmora...@gpm.es on 22 Aug 2011 at 1:06

[GoogleCodeExporter]

This seems to be a better link as it includes some follow up on the initial 
posting, which speeds up performance by 100x: 
http://developer.db4o.com/Forums/tabid/98/aft/5193/Default.aspx

Ok, it looks promising, so let me know the outcome of your research - I'd be 
more than happy to include a timeseries-capable state persistence based on db4o 
with your help!

Original comment by kai.openhab on 24 Aug 2011 at 7:46

[GoogleCodeExporter]

Thanks Kai,

You could follow part II and III with this links:

http://measuringmeasures.blogspot.com/2009/03/db4o-performance-tuning-part-ii.ht
ml
http://measuringmeasures.blogspot.com/2009/03/db4o-performance-tuning-part-iii.h
tml

Well, we've prepared a bundle with db40 as a plugin, and some services exposed 
(basic things, get connection, crud...)

We will update this thread with news.

Original comment by dmora...@gpm.es on 25 Aug 2011 at 12:36

[GoogleCodeExporter]

Hi again,

As i promised, i'm going to update this thread with our steps forward a 
persistence solution.

Attached, you will find a pdf file (in english) with the detail of the 
solution, and also a png (in spanish) where you could see images that could 
help to understand the code and final results.

There is a tricky way to select the items to log. We have used a specific 
group, so you must create the group and associate the items to log to that 
group.

We have just finished a working version, and it works fine.

It would be great to receive your feedback.

regards.

Original comment by dmora...@gpm.es on 16 Sep 2011 at 9:28

Attachments:

• [Aviary srvpsat-gpm-int Picture 1.png](https://storage.googleapis.com/google-code-attachments/openhab/issue-21/comment-6/Aviary srvpsat-gpm-int Picture 1.png)
• DB4O(1).pdf

[GoogleCodeExporter]

In the UI layer, we plan to use flot (http://code.google.com/p/flot/) to draw 
different charts with this info.

Things to do in persistence layer:

- I think that it will be useful to delete data far away from X weeks, for 
example. This way the database must be cleaned by a job.

- We will rename some code, to use names more appropiate.

- There is no security in this layer. Is there any need for that?

Original comment by dmora...@gpm.es on 16 Sep 2011 at 9:32

[GoogleCodeExporter]

Wow, that looks cool - allow me the weekend to look over it and provide you 
feedback!

> There is no security in this layer. Is there any need for that?

No, not for the moment as openHAB is lacking security features in general...

Original comment by kai.openhab on 16 Sep 2011 at 9:39

[GoogleCodeExporter]

Hi David,

As promised, here is some feedback:
1. db4o indeed seems to be a good choice, the integration and usage looks 
straight forward
2. The idea to use a group to "mark" the items to be persisted is good, 
although it seems as if it is a workaround in order to not having to change the 
item DSL syntax. I am not yet decided about the best solution here. One idea 
would be to add some binding configuration like { persist="events" } to it, but 
persistance is more a "core" feature than a simple binding (as persistence also 
needs access to the ItemRegistry). Out of this reason, I could also imagine to 
alter the DSL (and possibly the Item interface) to have persistance properties.
3. I would like to see other options than only persisting events. Something 
like "persist a value every x seconds", "persist ONLY the very latest status" 
(just to be able to recover from crashes), some RRD way "only keep 1000 values 
taken every second, 1000 every hour, 1000 every day, etc.." (this could solve 
your "cleaning old values" issue).
4. Besides item states/events, the persistence service should also support 
other kinds of data - e.g. something like logging information (see for example 
the "News" tab on the tablet prototype http://goo.gl/GpZ4H))
5. I couldn't really see the Java API for accessing the persistence service 
from your documents. Imho, there should be an OSGi service for an openHAB 
specific persistence service (which does not expose the db4o interfaces), which 
can be used by other bundles. The interface for this service would be very 
important to get right.
6. I would like the servlet to be included into the REST API one day. The user 
could then request information in a format of his choice (XML, JSON, JSONP) and 
it would be an "official" part of the REST API.
7. How is the database configured? I.e. where does db4o write its data to?

That much for now, looking forward to your answers :-)
Kai

Original comment by kai.openhab on 17 Sep 2011 at 11:10

[GoogleCodeExporter]

hi everyone, how is this going? seems promising!!

Original comment by pablo.ro...@gmail.com on 17 Oct 2011 at 12:52

[GoogleCodeExporter]

Hi there,

We will update this thread asap.

Thanks

Original comment by dmora...@gpm.es on 20 Oct 2011 at 10:19

[GoogleCodeExporter]

Asap is a very relative term ;-)
Could you give us an update? Cheers!

Original comment by kai.openhab on 27 Nov 2011 at 9:47

[GoogleCodeExporter]

If there is no response on this issue, I might look into it myself soon as I am 
really requiring this feature and would like to see some progress.
So David, if you are still on it, please be so kind and give an update - cheers!

Original comment by kai.openhab on 6 Dec 2011 at 9:02

[GoogleCodeExporter]

Hi Kai,

First of all, sorry for the delay...

We will send our work as soon as possible, in fact, we want to release it 
today, but we are suffering some troubles with the development enviroment, 
probably due to the last changes comitted to the repository (drools, etc.)

We are trying to rebuild the enviroment from scratch and see if it's related to 
our code. 

We will keep you update.

Regards

Original comment by dmora...@gpm.es on 13 Dec 2011 at 4:26

[GoogleCodeExporter]

Hi Kai,

First of all, i want to send you a brief explanation of how it's working. It's 
not perfect, but could be a good start:

We have developed 5 bundles:

1. org.openhab.io.bbdd ==> Database. It contains the service interface: (CRUD 
operations and paginated select, get and close connection.

2. org.openhab.io.bbdd.db4o ==> DB4O Implementation. It contains all the 
services to work with DB4O + driver (database is written in $home/logs path)

3. org.openhab.io.bbdd.listener ==> It uses io.bbdd bundle. We called it 
EventTracer. 
- How it works: AbstractEventSubscriber implementation to listen the openhab 
event bus (commands and updates). 
- It receives a Item Registry to know which items will trace, as they have to 
belong to a specific group (it's a little tricky) to be traced by the system.

5. org.openhab.ui.querydata ==> It also uses io.bddd bundle. Returns all data 
in JSON or XML format. 
- It works as a servlet, but it will be better to work as REST service (not 
done).

To use the last bundle (to recover data), there are two services:

url = localhost:8080/query/alerts ==> It returns all traced events in the last 
week (doesn't matter which item) ordered by date (it could feed a "last alarms" 
service).
url = localhost:8080/query/events/itemname ==> It returns all traced events in 
the last week for one specific item (it could feed a flot graph, we will 
explain how to use it).

Kai, could you please tell me the usual way to send/share this code?

Thanks.
Regards

Original comment by dmora...@gpm.es on 20 Dec 2011 at 4:47

[GoogleCodeExporter]

Sorry, i forgot to explain the 4th bundle. It's a MySQL implementation but it's 
not ready yet. 

Original comment by dmora...@gpm.es on 20 Dec 2011 at 4:48

[GoogleCodeExporter]

Thanks, looking forward to dive into the code :-)

> Kai, could you please tell me the usual way to send/share this code?

Simply create a clone of the repo at 
http://code.google.com/p/openhab/source/clones and push your changes to this 
repository.

Regards,
Kai

Original comment by kai.openhab on 20 Dec 2011 at 8:01

• Changed state: Started

[GoogleCodeExporter]

What about external persistence? Do you know pachube or thingspeak? It could be 
great to have these features in openhab.

As far as i know, thingspeak allows http put calls to send your measures 
(limited, i think) and pachube has json http calls (maybe a little harder).

What about using pachube or thingspeak in rules? This way you could check or 
uncheck which items do you want to trace... and the only thing you need is a 
function (pachube-trace, thingspeak-trace), and maybe a couple of lines in the 
conf file.

What do you think?

Original comment by juank...@gmail.com on 20 Dec 2011 at 8:38

[GoogleCodeExporter]

Hi,

You could get all changes from our clone: 
http://code.google.com/r/dmorales-persistence/

We have uploaded all bundles (unless mysql, number four) and also pom files, so 
you can check our code.

Regards

Original comment by dmora...@gpm.es on 21 Dec 2011 at 4:56

[GoogleCodeExporter]

Cool, thanks! I cannot promise that I can review it before christmas, but 
definitely still this year :-)

Original comment by kai.openhab on 21 Dec 2011 at 5:02

[GoogleCodeExporter]

ok, have nice days¡

Original comment by dmora...@gpm.es on 21 Dec 2011 at 5:03

[GoogleCodeExporter]

I promised too much, the year end comes faster than expected... I already had a 
look at the code, but to give a decent feedback with reflected suggestions, I 
will need some more time - but you are not forgotten! Have a good start in 2012!

Original comment by kai.openhab on 30 Dec 2011 at 9:31

[GoogleCodeExporter]

I finally managed to get through the code - it already looks very useful indeed!
Still, most of my wishes from comment 9 remain; I'd like to continue the 
discussion on the mailing list, see 
https://groups.google.com/d/topic/openhab/uQ3WYYqYuYk/discussion

Original comment by kai.openhab on 2 Feb 2012 at 9:23

[GoogleCodeExporter]

i have all setup, the data is going to the database, how do use them, how to 
get out from there ? do you have widget for openhab ? how do you connect to the 
database ? i would like to have a graph from temperatures ... how is that 
possible ?

Original comment by openhab.lb on 2 Mar 2012 at 12:01

[GoogleCodeExporter]

Might I ask to wait with those questions until the feature is implemented?
The solution for openHAB 1.0.0 will look different to what David had posted 
here. See the discussion at 
https://groups.google.com/forum/?fromgroups#!topic/openhab/uQ3WYYqYuYk
I hope to come up with some initial implementation in a few weeks. I'll post an 
update in the discussion group once it's there.

Original comment by kai.openhab on 2 Mar 2012 at 12:40

• Added labels: Version-1.0.0
• Removed labels: Version-later

[GoogleCodeExporter]

Please, Read comment #15 in this thread (how to recover data)

Original comment by dmora...@gpm.es on 2 Mar 2012 at 12:40

[GoogleCodeExporter]

Thanks a lot for answering

i could set it und the data is in the database ...

but with

To use the last bundle (to recover data), there are two services:

url = localhost:8080/query/alerts ==> It returns all traced events in the last 
week (doesn't matter which item) ordered by date (it could feed a "last alarms" 
service).
url = localhost:8080/query/events/itemname ==> It returns all traced events in 
the last week for one specific item (it could feed a flot graph, we will 
explain how to use it).

i don´t know how to do that...

HTTP ERROR 404

Problem accessing /query/alerts. Reason:

    ProxyServlet: /query/alerts

Original comment by openhab.lb on 2 Mar 2012 at 1:59

[GoogleCodeExporter]

Hi Kay,

i have build the peristence branch. i copied the addons in the addon directory 
and they are loaded, but no database files are created and it seems that the 
persistence directory is not observed by the folderobserver. i used you default 
configuration with the line for the folderobserver

Original comment by openhab.lb on 4 May 2012 at 7:22

[GoogleCodeExporter]

The feature branch has been merged to the default branch (and only there the 
Maven build has been tested), so please use this.

Details on what has been implemented for this issue can be found here:
https://groups.google.com/forum/?fromgroups#!topic/openhab/fecbooXCN_U

Original comment by kai.openhab on 4 May 2012 at 7:28

• Changed state: Implemented