I wanted to give the PostgreSQL persistence a try but the jar is not included in the distribution-1.4.0-SNAPSHOT-addons.zip at https://openhab.ci.cloudbees.com/job/openHAB/. The module gets builded fine and I can dowload it seperatly at https://openhab.ci.cloudbees.com/job/openHAB/org.openhab.persistence$org.openhab.persistence.sql.postgresql/.

Can you please take a look at this and include the binding in the addons zip?

I leave this for Kai and the team since I don't know what needs to be done. Nor do I have the privileges to do anything.

@pakerfeldt Are you actively using the bundle? As far as I have learned the hard way the PostgreSQL persistence is not usable at the moment because the general persistence bundle org.openhab.persistence.sql is solely MySQL specific!

As long as org.openhab.persistence.sql doesn´t implement a database abstraction layer the PostgreSQL persistence bundle is useless and should not be included in the build.

@kaikreuzer Am I right or have I missed a major information regarding sql persistence? And why the hell is the class SqlPersistenceService MySQL specific at all ?!?

I'm currently working on the sql binding to bring it inline with the other persistence services. I don't think it's mysql specific - it uses jdbc as an abstraction layer. If I search the source, mysql doesn't feature at all. You can set up the driver class in the openhab.cfg file, so this should be all that's necessary to change to PostgresSQL.

I've posted a recent snapshot on issue 441 on google code (https://code.google.com/p/openhab/issues/detail?id=441). There's also some discuss there about type conversions so I'd recommend taking a look there as it carries the most recent discussion on the SQL persistence.

Give the snapshot a try - if you find problems with it that stop it working with PostgresSQL I'm happy to try and resolve them as it should not be database dependent.

@cdjackson Hi, thanks for your reply. I already used your snaphot posted at https://code.google.com/p/openhab/issues/detail?id=441 and according to my analysis the are a lot of things not working with PostgeSQL (and I am sure not with other DBMS as well).

JDBC is not an a database abstraction layer. JDBC defines how a client access a database and provides methods for querying and updating data in a database. As long as you are using plain SQL you can never be sure your code will work with a diffrent DBMS.

e.g.

MySQL:
SHOW TABLES LIKE 'Items'
PostgreSQL:
SELECT table_name FROM information_schema.tables WHERE table_name = 'Items'

MySQL:

CREATE TABLE Items (ItemId INT NOT NULL AUTO_INCREMENT,ItemName VARCHAR(200) NOT NULL,
PRIMARY KEY (ItemId));

PostgreSQL:

CREATE TABLE Items (ItemId integer NOT NULL, ItemName character varying(254) NOT NULL);

CREATE SEQUENCE items_itemid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

And data types in general. PostgreSQL for example doesn't have VARCHAR, DOUBLE, DATETIME, TINYINT to name only a few.

PostgreSQL Case Sensitivity

You can read a good article about PostgreSQL Case Sensitivity at http://www.thenextage.com/wordpress/postgresql-case-sensitivity-part-1-the-ddl/

Regards

Ingo

Hi Ingo,
Thanks. I thought the SQL language was "reasonably standard", and therefore by using JDBC for the DB interface that we'd be database independent - it seems not :( I've used mysql and Micosoft before, and from memory there were no issues porting between them (ie no changes to the SQL at all) - we just changed the driver.

If we can come up with a SQL statement that is "standard", then that seems the best (easiest!) approach, otherwise I'd suggest we'd need to move all the SQL out of the sql binding and into the separate mysql/postgressql bindings.

The SQL is generally pretty simple, so I would like to think we could find SQL statements that work across all (or most) databases - but maybe I'm dreaming:)

I'll take a look at this tonight. Also, we should be able to find datatypes that are common (??), but if not, the sql service does allow these to be changed (within reason) in the openhab.cfg.

What do you think?

Cheers
Chris

Hi Chris,

as long as there is plain sql in the source code you get to incompatibilities quite fast. Sure you can write most of the SELECT, INSERT AND UPDATE statements to work on most or all dbms and just move the specific parts in the bindings e.g. CREATE statements. Or you switch to an ORM like Hibernate but thats a really really big step. But thats a design decission and maybe we should wait or ask what goals/visions Kai and Thomas have?

@kaikreuzer @teichsta What are your opinions? Can you enlighten us poor fellows :-)

Hi Ingo,
I would probably prefer to avoid making a major change like this - it seems a bit drastic given the relatively simple storage requirements. Kai/Thomas can correct me if I'm wrong, but as I see the persistence services, there are a few relatively simple methods (store/query) for accessing them, and I don't see this changing significantly since it's the common interface to all persistence services.

So, this limits what the service needs to do to a relatively simple set of functionality - unless Kai/Thomas are intending on extending this in future with a general change to the model?

Chris

I agree with Chris that an ORM like Hibernate would be too heavy weight for our rather simple use cases here.
Sorry for having pulled the change without noticing the incompatibilities. If you can suggest changes to make it work as it is (one service with two different drivers), perfect. If not, I would not mind to partially duplicate code here and have two separate persistence services in place.

Hi all,
Thinking about this some more, and I could ask "why do we have an sql persistence service rather than separate mysql, postgres, sqlite, wotever-sql services? I guess they all have SQL in the name, but if they aren't compatible, then why are we trying to make them so.

In the original (pre 1.3) sql service, there were no SQL statements. The SQL was added as the alias to the sql.persist config file. This has the shortfall that it was only possible to store data since there is no knowledge of the schema, so queries were not possible. I could have added to the complexity by trying to find a way to add this to the config file, but I think it's an impossible task with all the possible query filter options.

The persistence services (currently) only have 2 exports methods - store and query. I would like to add delete and update to this, but that's still only the standard 4 methods. I think the easiest way forward here is to combine the persistence.mysql driver and the current persistence.sql service into a single persistence.mysql service, and then to do the same for the postgressql. In my mind, this is consistent with other persistence services - they are all slightly different - the interface is store and query, and adding another layer is not necessary.

That's my opinion anyway - fire away :)

Chris

Yes, would make it easier as the user would only have to drop a single jar in his addons folder, just as for any other addons. Having to take care about dependencies himself as it is at the moment is a bit awkward.

I agree that it is the best to kill the org.openhab.persistence.sql binding and have a single org.openhab.persistence.mysql, org.openhab.persistence.postgresql or whatever dbms binding.
But I would like to suggest to define a common interface or abstract class inside the openhab core e.g. org.openhab.core.persistence.SqlPersistenceService which every SQL persistence binding must implement/extend. Connect/Close to a database is common to all SQL persistance bindings and the methods to implement should be forced e.g. store or query as in current SqlPersistenceService.java.

What do you think?

Hi Ingo,
I'm not sure it's necessary - or actually, it's already existing.

The internal interface from openHAB to the persistence service is already defined and is implemented by all persistence services - sql, d440, rrd4j, log, etc. Currently this is just the store/query methods, but as I mentioned earlier, I think this should be extended to full CRUD at some stage (soon). The connect/close functionality is an internal function of the service and doesn't really need any standardisation interface specific to SQL - we just use the existing interface?

Chris

I had just a quick look in the source code and can't really judge the whole thing.
I´ll trust your experience with openHAB. Once you are finished with the MySQL persistence I'm happy and willing to port the whole thing to PostgreSQL :-)

Going from memory, there are currently two classes defined - "PersistenceService", and "QueryablePersistenceClass". For SQL, I changed in 1.3 to the "with query" version, and this has the query method. The persistence manager then checks the class type to decide if the selected persistence service supports query or not.....

If Kai is still watching, I think we ought to define another abstract class for CRUD. Currently we have store and query, a "CRUDPersistenceClass" could extend QueryablePersistenceClass and add update() and delete() - each allowing update and delete. Parameters of (Item, Time) for delete, and (Item, Time, State) for update.

I know this should be a separate issue, but just thought I'd mention it here since we're discussing this sort of thing....

I refactored the sql bindings last night, so now have a single persistence.mysql binding. It seems to work fine and I'll put it onto my production server tonight to give it a proper run.

I've also added a CRUDPersistenceClass which extends QueryablePersistenceClass, so all current code continues to work. The CRUD class adds the two extra methods for update and delete. I've also changed the mysql binding to extend from this class and I hope to test this tonight.

If this is all ok, I will push the persistence class changes for review - it's all reasonably simple, so hopefully everyone is ok with it :) Then I can push the mysql binding.

I think it's best to do it this way to avoid multiple refactoring of the bindings, but if there's any comments, please shoot, otherwise I'll push this in the next day or so and we can discuss it there if needed :)

Hi all,
have ported cdjacksons mysql persistence to PostgreSQL now.
@itheiss: Sorry, could not wait ;)

What is to do for getting it in main 1.4.0 branch?
Please tell me...

Here you can find both, Source of new PostgreSQL and a corrected MySql Persistence-Service:
https://github.com/lewie/openhab/tree/master/bundles/persistence

For testing you can find generated versions here:
https://groups.google.com/forum/#!topic/openhab/sSwvx9qWzNo
org.openhab.persistence.postgresql-1.4.0-SNAPSHOT.jar
org.openhab.persistence.mysql-1.4.0-SNAPSHOT.jar

For Tester, PostgreSQL in openhab.cfg:
######## PostgreSQL Persistence Service ########
postgresql:url=jdbc:postgresql://192.168.0.1/dbname
postgresql:user=dbuser
postgresql:password=dbpass

Helmut

Why is JPA or Hibernate such a heavy weight? If I look at the overall project adding JPA wouldn't add to the complexity at all. If I look at OpenJPA most of the dependent libraries are available in openHAB already anyway.
I've used JPA even in small projects where it absolutely wasn't required because there was only one database. It's super simple. Here, for openHAB, the benefit would be big. Because it simply adds what otherwise would need to be added manually: adapting for each and every database out there.

Support for databases like Postgres or Apache Derby is a must.
My suggestion is:
create a JPA based persistence binding. In openhab.cfg only the connection url and the driver class may be configured. If the driver is not part of openHAB, there should be a place where additional libraries can be placed which will be added to class path upon start of openHAB.

The JPA based binding can also (but need not) support none SQL databases. I've seen that MongoDB, OrientDB and such also have JPA abstraction layers.

If this actually still reading anyone? :)

I’d suggest that if you want to do this, then go for it. IF it allows us to retire some other persistence stores then fine - if not, and if it adds support to other stores, then it’s a benefit to the community…

On 21 Jun 2014, at 16:27, mdbergmann notifications@github.com wrote:

Support for databases like Postgres or Apache Derby is a must.
My suggestion is:
create a JPA based persistence binding. In openhab.cfg only the connection url and the driver class may be configured. If the driver is not part of openHAB, there should be a place where additional libraries can be placed which will be added to class path upon start of openHAB.

The JPA based binding can also (but need not) support none SQL databases. I've seen that MongoDB, OrientDB and such also have JPA abstraction layers.

If this actually still reading anyone? :)

—
Reply to this email directly or view it on GitHub.

OK. Fair enough.
I can try that. Though it'll take a while getting into openHAB project as such. My struggles at first are possibly also getting openHAB working in IntelliJ IDEA my favorite IDE.

Setup in IntelliJ doesn't seem to be possible. The project is too tightly tied to Eclipse runtime.

@cdjackson Could you please close this issue. A JPA Persistence binding is available. And the original scope of this issue is dead.