Universal DAO plugin for SQL databases like PostgreSQL, MySQL, etc.

[albogdano]

Implement DAO to work with any SQL database, possibly using a framework like JOOQ.

Para is schemaless so we need to decide how objects are going to be mapped to DB columns. One way to do this is by having a JSON column holding a ParaObject, serialized in JSON + an id column.

Ideas are welcome.

[jswiesner]

Have done any work on this yet? I’m thinking Hibernate may be a better choice over JOOQ. From what I’ve read about JOOQ, its features start to shine when you do complex interaction with your data. But a SQL DAO in Para is only ever going to do key-value reading so this DAO implementation does need advanced reading capabilities. What are your thoughts on this?

[albogdano]

I have not. I don't have a preference for JOOQ. The only goal is to make this universal so that we don't have to rewrite H2DAO all over again for every DB engine. Is this possible with Hibernate? If it's used we don't need to include any specific database drivers, right?

[jswiesner]

Yes Hibernate supports a wide range of SQL databases, including H2. You do need a SQL database driver that implements java.sql.driver, but JDBC should work for this. Are you thinking the genetic SQL DAO would replace the H2 DAO in Para? Or is this a intended to be an external plugin? Also, I agree with your point about the SQL schema being simply an ID primary key column and a second column being the JSON object. The object column could be a text type or it also could be a BLOB, I’m not sure which would be better. In theory you could map the ParaObject fields to columns, but I don’t see any value in doing this since we only need the DAO to perform lookups by ID.

[albogdano]

I think this should definitely be an external plugin - para-dao-sql is a possible choice for a name. The H2DAO can be used a starting point. ID + JSON works well but BLOB is also an option. The only reason why I chose JSON for H2 is because you can see the data, in case you want to do some debugging. Modern DBs also have compression, so this shouldn't be an issue.

[jswiesner]

I started working on this issue and I'm realizing that even Hibernate is overkill what what is needed here - the DAO class is only doing the most basic CRUD operations. I'm thinking this implementation can just simply use JDBC. To use the generic SQL DAO plugin you would need to specify some basic properties like URL, user name and password of the database, and then the JDBC driver class name (i.e. "com.mysql.jdbc.Driver"). Lastly, you'd need to include the jarfile with the JDBC driver in it in your classpath when running Para server. What do you think about this?

[jswiesner]

We'll definitely need a connection pooling mechanism on top of JDBC. I propose we try HikariCP. I've never used it before, but it looks solid.

[jswiesner]

I finished an initial implementation of this plugin if you're interested to check it out so far (repo here). I used H2DAO as the foundation for this implementation. Connection pooling managed by HikariCPI. I used the in-memory H2 database for integration testing and setup multiple tests so they run with various compatibility modes (H2, MySQL, Oracle, PostreSQL, and SQL Server). I haven't done a careful review of the SqlUtils code yet and mostly just reused the code from H2Utils. I also need to enhance the testing a bit more, it's pretty light at the moment. HikariCP offers quite a few useful settings, so we may want to think about exposing some of these. I also need to work on the documentation a bit more.

After doing a review I'll likely have a more comprehensive list of questions/points to discuss regarding some of the implementation details. Any feedback you have in the mean time would be much appreciated.

[albogdano]

Awesome! The code looks very good. HikariCP is a great choice for connection pooling. You're right about Hibernate being overkill. I just kinda forgot that JDBC existed and started thinking in other directions. 🙂

[jswiesner]

Initial implementation complete, project added to Erudika organization repo!
https://github.com/Erudika/para-dao-sql
This closes #18.