cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lukas Eder <lukas.e...@gmail.com>
Subject Re: persistence layer
Date Tue, 26 Nov 2013 11:57:58 GMT
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.Huang@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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message