Dear CloudStack developers, This thread has caught my attention and I thought I might chime in and give you some background information about jOOQ, JDBC, RDBMS in general (I work for Data Geekery, the company behind jOOQ). I've already had the pleasure to talk to Darren on the jOOQ User Group and on the phone.
Here are some answers to open questions I've seen in this thread: 2013/11/26 Alex Huang <alex.hu...@citrix.com> > Has anyone actually tried dropping in a different jdbc driver and see if > CS can use another DB? I don't think the current CS DB layer prevents > anyone from doing that. > > This is different from MariaDB which, as othes have pointed out, is > drop-in replacement for MySQL. I'm talking about stuff like derby or > sqlserver or oracle or db2. > MariaDB and MySQL are diverging quickly, as Oracle invests quite some development efforts into MySQL. While the drop-in replacement argument is valid right now, it might not be two years from now. In general, SQL dialects are very different when it comes to the subtleties of syntax or feature sets. Since you're comparing MySQL with Derby, SQL Server, Oracle, or DB2, let me give you some ideas: - MySQL has a lot of vendor-specific clauses that no one else has (e.g. ON DUPLICATE KEY UPDATE) - MySQL bends the SQL syntax and accepts what is otherwise illegal syntax (e.g. GROUP BY semantics [1]) - MySQL doesn't correctly implement the SQL standard NOT IN semantics [2] - Derby and DB2 (prior to version 9.7) are extremely type-safe. For instance, CAST(NULL AS INT) and CAST(NULL AS VARCHAR) are two entirely different things. This can lead to tedious work when binding null as a bind variable. Some background info [3] - DB2 has a lot of peculiarities when it comes to memory management of VARCHAR [4] - Case-sensitivity of schema, table, and column names is an eternal hassle between RDBMS - JDBC drivers implement things very differently in some areas. Fetching an inserted ID is really a pain. [1]: http://blog.jooq.org/2012/08/05/mysql-bad-idea-384/ [2]: http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-null-values/ [3]: http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madness/ [4]: http://stackoverflow.com/q/9234021/521799 If you want cross-database support, these things show that you should really consider moving away from using plain JDBC and use an abstraction that will take care of these incompatibilities for you. You might be choosing between Hibernate and jOOQ (or both) depending on the way you plan to interact with your RDBMS. Our point of view is illustrated here: http://www.hibernate-alternative.com Some larger jOOQ customers currently use jOOQ with Oracle, Sybase, SQL Server, MySQL, and H2 from the same application. > Frank Zhang Fri, 22 Nov 2013 10:42:09 -0800 > I recommend QueryDSL. Having a quick look at JOOQ, it's very similar to > QueryDSL. > QueryDSL has been integrated into Spring for 2 years, and JOOQ haven't had > official doc for Spring. > Besides Sql, QueryDSL also gets fair credit on manipulating NoSql which is an > additional plus. jOOQ and Spring work together quite nicely, even if there is not an "official" affiliation between the two stacks: - http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with-spring/ - http://blog.jooq.org/2012/09/19/a-nice-way-of-using-jooq-with-spring/ - http://blog.uws.ie/2013/04/using-jooq-with-spring-transactions/ - http://stackoverflow.com/q/4474365/521799 We're aware of QueryDSL offering SQL and NoSQL support through a single API. In our opinion, such greater unification attempts will always come with an impedance mismatch at some point. In other words, you'll be losing on SQL functionality in favour of greater standardisation / abstraction. >From our experience with jOOQ, SQL standardisation is already a very hard problem. NoSQL databases all work fundamentally differently. The added complexity by supporting NoSQL in the same API as SQL databases is significant. Of course, these things depend on whether replacing MySQL for any NoSQL database in CloudStack is really a desired, near-term option. ---- I'll be following this thread for another while and I'm more than happy to answer any questions you may have related to jOOQ, SQL, JDBC, etc. Best Regards, Lukas
