You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cloudstack.apache.org by Laszlo Hornyak <la...@gmail.com> on 2013/11/11 20:33:05 UTC
persistence layer
Hi,
What are the general directions with the persistence system?
What I know about it is:
- It works with JPA (javax.persistence) annotations
- But rather than integrating a general JPA implementation such us
hibernate, eclipselink or OpenJPA it uses its own query generator and DAO
classes to generate SQL statements.
Questions:
- Are you planing to use JPA? What is the motivation behind the custom DAO
system?
- There are some capabilities in the DAO system that are not used. Should
these capabilities be maintained or is it ok to remove the support for
unused features in small steps?
--
EOF
Re: persistence layer
Posted by Travis Graham <tg...@tgraham.us>.
MariaDB is a drop in replacement for MySQL, so it can be used with or without the JOOQ changes.
Travis
On Nov 25, 2013, at 5:20 AM, Sebastien Goasguen <ru...@gmail.com> wrote:
>
> On Nov 23, 2013, at 4:13 PM, Laszlo Hornyak <la...@gmail.com> wrote:
>
>> Wouldn't it be a lot of work to move to JOOQ? All queries will have to be
>> rewritten.
>>
>>
>
> An a non-java developer question: Will that help support different databases ? like moving to MariaDB ?
>
>>
>> On Sat, Nov 23, 2013 at 11:32 AM, Darren Shepherd <
>> darren.s.shepherd@gmail.com> wrote:
>>
>>> Going to an ORM is not as simple as you would expect. First, one can make
>>> a strong argument that ORM is not the right solution, but that can be
>>> ignored right now.
>>>
>>> You have to look at the context of ACS and figure out what technology is
>>> the most practical to adopt. ACS does not have ORM today. It has a custom
>>> query api, object mapping, and change tracking for simple CRUD. Honestly
>>> these features are quite sufficient for ACS needs. The problem, and why we
>>> should change it, is that the current framework is custom, limited in
>>> functionality, undocumented, and generally a barrier to people developing
>>> on ACS. So jOOQ is a somewhat similar approach but it is just far far
>>> better, has a community of users that have developed over 3-4 years, is
>>> well documented, and honestly just a very well thought out framework.
>>>
>>> Darren
>>>
>>>> On Nov 22, 2013, at 6:50 PM, Alex Ough <al...@sungard.com> wrote:
>>>>
>>>> All,
>>>>
>>>> I'm very interested in converting the current DAO framework to an ORM. I
>>>> didn't have any experience with java related ORMs, but I've done quite
>>> lots
>>>> of works with Django and LINQ. So can you add me if this project is
>>> started?
>>>>
>>>> Thanks
>>>> Alex Ough
>>>>
>>>>
>>>> On Fri, Nov 22, 2013 at 7:06 AM, Daan Hoogland <daan.hoogland@gmail.com
>>>> wrote:
>>>>
>>>>> Had a quick look, It looks alright. One question/doubt: will we thigh
>>>>> ourselves more to mysql if we code sql more directly instead of
>>>>> abstracting away from it so we can leave db choice to the operator in
>>>>> the future!?!?
>>>>>
>>>>> On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
>>>>> <da...@gmail.com> wrote:
>>>>>> I've done a lot of analysis on the data access layer, but just haven't
>>>>> had time to put together a discuss/recommendation. In the end I'd
>>> propose
>>>>> we move to jOOQ. It's an excellent framework that will be very natural
>>> to
>>>>> the style of data access that CloudStack uses and we can slowly migrate
>>> to
>>>>> it. I've hacked up some code and proven that I can get the two
>>> frameworks
>>>>> to seamlessly interoperate. So you can select from a custom DAO and
>>> commit
>>>>> with jOOQ or vice versa. Additionally jOOQ will work with the existing
>>>>> pojos we have today.
>>>>>>
>>>>>> Check out jOOQ and let me know what you think of it. I know for most
>>>>> people the immediate thought would be to move to JPA, but the way we
>>>>> managed "session" is completely incompatible with JPA and will require
>>>>> constant merging. Additionally mixing our custom DAO framework with a
>>> JPA
>>>>> solution looks darn near impossible.
>>>>>>
>>>>>> Darren
>>>>>>
>>>>>>> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <laszlo.hornyak@gmail.com
>>>>
>>>>> wrote:
>>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> What are the general directions with the persistence system?
>>>>>>> What I know about it is:
>>>>>>> - It works with JPA (javax.persistence) annotations
>>>>>>> - But rather than integrating a general JPA implementation such us
>>>>>>> hibernate, eclipselink or OpenJPA it uses its own query generator and
>>>>> DAO
>>>>>>> classes to generate SQL statements.
>>>>>>>
>>>>>>> Questions:
>>>>>>> - Are you planing to use JPA? What is the motivation behind the custom
>>>>> DAO
>>>>>>> system?
>>>>>>> - There are some capabilities in the DAO system that are not used.
>>>>> Should
>>>>>>> these capabilities be maintained or is it ok to remove the support for
>>>>>>> unused features in small steps?
>>>>>>>
>>>>>>> --
>>>>>>>
>>>>>>> EOF
>>>>>
>>>>>
>>>
>>
>>
>>
>> --
>>
>> EOF
>
Re: persistence layer
Posted by Chiradeep Vittal <Ch...@citrix.com>.
Great discussion. I do find it a pain to construct a query in the current
system (if there was a REPL, it might be easier, but oh well).
+1 to JOOQ
On 12/2/13 5:27 PM, "Kelven Yang" <ke...@citrix.com> wrote:
>I generally in agree with what Darren has pointed. Within the context of
>ACS, declarative transaction support is not as urgent as solving the
>problem as we are having in writing queries. Solving basic CRUD and query
>problem alone in a consistent pattern can move CloudStack persistence
>layer a big step ahead. And the good part of it is that we can do it
>incrementally, which I think it¹s the key for us to really move forward
>quickly.
>
>I saw a lot of efforts trying to hide the complexity of underlying problem
>but eventually end up to a more complex solution. With that said, myself
>actually prefer a simple solution that is close to SQL as natural but
>solves the DB-agnostic problem to a reasonable level, I don¹t like too
>much of the idea trying to hide SQL completely away from developers. So
>far JOOQ sounds like a good candidate to me.
>
>Kelven
>
>On 12/2/13, 3:07 PM, "Frank Zhang" <Fr...@citrix.com> wrote:
>
>>Hi Lukas:
>> Thank for detailed reply, see my comments inline
>>
>>>
>>> Hi Frank,
>>>
>>> Thank you for your detailed feedback. This is very interesting insight
>>>for me and
>>> for Data Geekery. Let me try to address your concerns by first
>>>categorising
>>> them into the following items (please correct me if I might have
>>> misunderstood).
>>>
>>> - A concern about Spring support.
>>> - A concern about standards (JPA)
>>> - A concern about CRUD verbosity
>>> - A concern about fluent APIs vs. SQL
>>> - A concern about fluent APIs vs. custom query building APIs
>>
>>Your explanation frees my concerns from last three items. I don't
>>carefully read
>>entire JOOQ document and I think most people won't when they try to get
>>an overall picture of a new project.
>>
>>So if we can highlight these items(especially CRUD verbosity and custom
>>query builder)
>>somewhere, it would be more appealing to developers who have
>>JPA/Hibernate background.
>>
>>>
>>> Spring support
>>> ===========
>>> It is true that we cannot offer you a "formal" affiliation with Spring
>>>(or rather
>>> with Oliver Gierke, the lead developer behind Spring Data). I have been
>>> observing this collaboration between Spring Data and QueryDSL and I
>>>feel that
>>> it is a strategic alliance that mutually benefits both vendors.
>>> QueryDSL gets some more traction by being auto-shipped to a much
>>>broader
>>> audience, whereas Spring Data gets a fluent API for both JPA and SQL.
>>>
>>> From our perspective, we want jOOQ to be entirely independent of any
>>> transaction and connection pooling API as this facilitates integration
>>>with JTA
>>> transaction modelling and other models. We believe that by keeping
>>>things
>>> simple and by separating concerns, you will get a stack that is better
>>>focused on
>>> your individual needs. In that way, we believe that you should be able
>>>to
>>> combine Spring IOC with jOOQ just as much as with JEE components.
>>>
>>> Most importantly, Spring Data, much like JDO in the past and like
>>>QueryDSL
>>> today aim for integrating NoSQL databases into a unified API, which we
>>>find a
>>> poor fit, as transaction models, data models, query models are
>>>fundamentally
>>> different between these various data stores. We believe that by
>>>pursuing such
>>> unification, frameworks are easily distracted from sound SQL support.
>>>We
>>> highly value Spring's transaction model simplification and this has
>>>worked well
>>> with jOOQ in the past.
>>
>>My whole point here is about declarative transaction management which is
>>the
>>most compelling part of Spring Data(from Spring's own words). Just did
>>more
>>research that JOOQ is a DB access layer it won't involve in any
>>transaction
>>management stuff. If my understanding is correct, it should automatically
>>work
>>with Spring TX? Am I right?
>>
>>No matter the answer, JOOQ should give the best practice in document,
>>searching chapter names in http://www.jooq.org/doc/3.2/manual/
>>returns nothing about transaction and I have seen people asking
>>similar question on Stack Overflow. I saw your answer too :)
>>
>>http://stackoverflow.com/questions/15159842/how-to-start-transaction-and-
>>r
>>ollback-with-jooq
>>
>>it's very true that we can draw a clear boundary between DB access layer
>>and
>>transaction management, but for most of time users don't care about this
>>because
>>they are searching an entire solution for DB. Even if JOOQ developers
>>know
>>transaction management is not focus of this project, they still need to
>>tell their users
>>the best way to go instead of letting user figure out it themselves.
>>
>>I don't think JOOQ has to integrate with SpringDB like QueryDSL does, for
>>example,
>>having a JOOQTemplate. But I do think integrating with Spring TX is
>>important
>>given it's popularity.
>>
>>
>>>
>>> Standards (JPA)
>>> ============
>>> Now, one of your arguments in favour of JPA is the non-lock-in. Spring
>>>has been
>>> challenging JEE standards for quite some time and there is no other
>>>vendor in
>>> the market to replace Spring once you build upon Spring. Even more so,
>>>by
>>> choosing a Spring+QueryDSL combination, you get an equally strong
>>>vendor-
>>> lockin (or even a stronger one, as the stack gets more
>>> complex) than when choosing Spring+jOOQ. If standards are important to
>>>you, I
>>> suggest you only use JPA and Criteria Query (Note, that JEE also
>>>supports IoC).
>>> And I'm sure that Adam Bien will agree with me on this :-) But even
>>>then, you
>>> will have a hard time migrating from Hibernate to EclipseLink or
>>>OpenJPA as
>>> the implementations are subtly different in many ways.
>>>
>>> The decision is up to you, and I fully understand these concerns.
>>>Standards are a
>>> good thing because many disparate things from various vendors have been
>>> unified and hopefully matured over time. But vendors also innovate and
>>>thus
>>> deviate from the standard again. Without such deviation, there would be
>>>no
>>> more progress, nor innovation in a standard. Take EclipseLink for
>>>instance, a
>>> JPA implementation that has started to "flirt" with NoSQL
>>> support:
>>> http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
>>>
>>> The above is orthogonal to JPA and will add lots of complexity to this
>>>particular
>>> implementation. JDO was another standard that has attempted to
>>>integrate
>>> other, non-relational data stores in a single API, yet it has not
>>>succeeded,
>>> leaving a pretty much dead standard.
>>>
>>> Now, while you are absolutely right by saying that you cannot switch
>>>the
>>> implementation of the jOOQ API at the moment, we're striving to
>>>innovate on
>>> the standards front as we see room for various JSRs in the area of
>>>RDBMS meta
>>> modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling in
>>> general, SQL DSL modelling in particular. We believe that jOOQ is the
>>>most
>>> innovative platform for SQL to Java integration in the market at the
>>>moment
>>>
>>
>>I strongly agree with the point that "none lock-in" is actually in theory
>>most of time,
>>In reality, there is more or less you have to do or sacrifice when
>>moving.
>>
>>My concern here is about SQL engine layer. The SQL engine I mean is the
>>part which
>>generates final SQL statement. For my perspective, JOOQ is DSL API + SQL
>>engine,
>>where DSL API is the main business. My understanding is the DSL API is
>>now tightly coupled
>>with SQL engine that I can not switch to another vender like Hibernate.
>>
>>As a user, I may prefer DSL API provided by JOOQ, but have some
>>performance concerns about
>>underlying SQL engine and apt to use Hibernate, then I have to make a
>>hard decision.
>>This may not be a valid point to you. First JOOQ may have better
>>performance than Hibernate(I saw
>>reads about SQL performance mentioned in JOOQ document, which hints that
>>JOOQ concerns much
>>about performance). Second, I don't know if Hibernate can be solely used
>>as a SQL engine.
>>
>>But decoupling the API from SQL engine provides potential flexibility for
>>future, and might be easier
>>to make the DSL API as a JSR.
>>We have seen this much in DB. JPA is just a set of API. Mysql splits
>>storage engine from server logic
>>(Though the storage engine still couples with some server side logic like
>>row locks).
>>
>>Conclusion, besides the transaction management, I don't have too much
>>concerns in using JOOQ in CloudStack now.
>>I am glad to see after many years there is still a project actively
>>working on solving DB problem in Java.
>>
>>
>>> CRUD verbosity
>>> ===========
>>> > In this area, JPA does the right thing.
>>> >
>>> > Author author = new Author(...);
>>> > JPAEntityManary.persist(author);
>>> >
>>> > The later one is more Java more OOP.
>>>
>>> Compare this to jOOQ's CRUD features:
>>> AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
>>>
>>> Or alternatively:
>>> AuthorDao dao = ...
>>> dao.insert(new Author());
>>>
>>> Relevant sections in the manual are:
>>> -
>>> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
>>> updatablerecords/
>>> -
>>> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
>>> updatablerecords/simple-crud/
>>> - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
>>>
>>> Fluent APIs vs. SQL
>>> ===============
>>> I fully understand this concern. Using the jOOQ fluent API is not the
>>>same as
>>> writing SQL directly. But let me remind you of the concerns you are
>>>having with
>>> respect to database independence. By writing SQL directly (through
>>>JDBC,
>>> through views or through stored procedures) you will not be able to
>>>support
>>> dozens of databases very easily. Both JPA and jOOQ solve this problem
>>>quite
>>> well. JPA abstracts SQL entirely, while jOOQ abstracts the subtle
>>>differences,
>>> only.
>>>
>>> Fluent APIs vs. custom query building APIs
>>> ================================ In addition to jOOQ's fluent API,
>>>there
>>> is also a simpler, object-oriented SelectQuery model:
>>> http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
>>>
>>> The same applies for other types of statements (InsertQuery,
>>>UpdateQuery,
>>> etc.). Thus, compare this fluent API usage:
>>>
>>> > create.selectFrom(BOOK)
>>> > .where(BOOK.PUBLISHED_IN.eq(2011))
>>> > .orderBy(BOOK.TITLE)
>>>
>>> With this SelectQuery usage:
>>>
>>> SelectQuery<Book> select = create.selectQuery(BOOK);
>>> select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
>>> select.addOrderBy(BOOK.TITLE);
>>> Result<Book> result = select.fetch();
>>>
>>> This is also documented here:
>>>
>>>http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and-n
>>>o
>>>n-
>>> dsl/
>>>
>>> Conclusion
>>> ========
>>> I hope I could put jOOQ in a different light to you. If JPA is
>>>sufficient for your
>>> use-cases, then use JPA (or JPA+jOOQ). There are some problems that
>>>jOOQ
>>> will never solve, which are solved very well in JPA. We have outlined
>>>these
>>> differences here:
>>> http://www.hibernate-alternative.com
>>>
>>> However, by using jOOQ, you not only gain a very high level of
>>>typesafety and
>>> thus an increase of productivity through the usage of jOOQ's code
>>>generator
>>> which helps you detect errors early, you also get access to a very rich
>>>set of
>>> service provider interfaces, which were off-topic in this discussion so
>>>far. Some
>>> examples:
>>>
>>> ExecuteListeners for custom query execution lifecycle handling:
>>> http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
>>>
>>> Schema and table mapping for multi-tenancy support:
>>>
>>>http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-sche
>>>m
>>>a-
>>> mapping/
>>>
>>> Record mapping in order to provide custom mapping algorithms from jOOQ
>>> records to your domain model:
>>> http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-
>>> recordmapper-provider/
>>>
>>> A query transformation listener that is still being worked on, allowing
>>>for
>>> arbitrary SQL transformation on the jOOQ AST. This can be very useful
>>>for
>>> features like row-level security.
>>>
>>> As I said, we believe that there is currently no other software in the
>>>Java
>>> market that takes your SQL so seriously, and we're highly committed to
>>> improve this even further in the near future.
>>>
>>> Best Regards,
>>> Lukas
>>>
>>> 2013/11/27 Frank Zhang <Fr...@citrix.com>
>>>
>>> > Hi Lukas:
>>> > Glad to see developer behind JOOQ here, I originally
>>> > recommended using QueryDSL instead of JOOQ, there are some of my
>>> > concerns:
>>> >
>>> > - Spring support. You know CloudStack is based on Spring IOC, the
>>>most
>>> > compelling thing of Spring DB is its transaction management
>>>framework.
>>> > QueryDSL has been integrated into Spring for two years. But there is
>>> > only a simple page (
>>> >
>>>http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with
>>> > -spring/) demonstrating JOOQ and Spring, however, the code just
>>>shows
>>> > how to load JOOQ in IOC, it doesn't show any advanced feature of
>>> > Spring DB for example declarative transaction management. If JOOQ is
>>> > not compatible with Spring DB, then we loss a widely used feature.
>>> >
>>> > - It's not JPA based on. This might be an argument that JOOQ may
>>>claim
>>> > as its biggest advantage. I agree JPA does have some bad thing(e.g.
>>> > criteria API), but the rest part is doing very well.
>>> > The most important thing here is JPA is just API so I can choose
>>> > underlying vendor by my preference. This non-lock-in feature is very
>>> > appealing. For example, Hibernate has been proven by industry for 10
>>> > years, I can fairly trust its quality. And vendor may have some
>>> > advanced feature like secondary cache I may need someday.
>>> > Instead, using JOOQ means I have to rely on its underlying
>>> > implementation with no choice.
>>> >
>>> > You know almost all JPA vendors support different SQL dialects. And
>>> > fluent API(or DSL) is not attractive to me. Frankly speaking, IMO,
>>>for
>>> > insert/update/delete clause the fluent API makes thing unnecessary
>>> > complex that you are writing something which is even more complicated
>>> > than SQL, for example in JOOQ,
>>> >
>>> > == persistence ===
>>> > create.insertInto(AUTHOR)
>>> > .set(AUTHOR.ID, 100)
>>> > .set(AUTHOR.FIRST_NAME, "Hermann")
>>> > .set(AUTHOR.LAST_NAME, "Hesse")
>>> > .newRecord()
>>> > .set(AUTHOR.ID, 101)
>>> > .set(AUTHOR.FIRST_NAME, "Alfred")
>>> > .set(AUTHOR.LAST_NAME, "Döblin");
>>> >
>>> > In this area, JPA does the right thing.
>>> >
>>> > Author author = new Author(...);
>>> > JPAEntityManary.persist(author);
>>> >
>>> > The later one is more Java more OOP.
>>> >
>>> > === single table query ====
>>> > Speaking of query, for single table query, fluent API is not
>>> > attractive too. For example:
>>> >
>>> > Raw SQL:
>>> > SELECT * FROM BOOK
>>> > WHERE BOOK.PUBLISHED_IN = 2011
>>> > ORDER BY BOOK.TITLE
>>> >
>>> > JOOQ:
>>> > create.selectFrom(BOOK)
>>> > .where(BOOK.PUBLISHED_IN.eq(2011))
>>> > .orderBy(BOOK.TITLE)
>>> >
>>> >
>>> > CloudStack search criteria built on JPA:
>>> > Qeury<Book> q = new Qeury<Book>();
>>> > q.add(Book_.PublishedIn. Op.eq, 2011); q.orderBy(Book_.title);
>>> > List<Book> books = q.list();
>>> >
>>> > The last way is more Java for sure, people who even has no SQL
>>> > knowledge can write the search.
>>> >
>>> > === multiple tables query ===
>>> > The only place fluent API shining is querying multiple tables:
>>> >
>>> > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
>>> > FROM AUTHOR
>>> > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
>>> > WHERE BOOK.LANGUAGE = 'DE'
>>> > AND BOOK.PUBLISHED > '2008-01-01'
>>> > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
>>> > HAVING COUNT(*) > 5
>>> > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
>>> > LIMIT 2
>>> > OFFSET 1
>>> > FOR UPDATE
>>> >
>>> > JOOQ:
>>> >
>>> > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
>>> > .from(AUTHOR)
>>> > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
>>> > .where(BOOK.LANGUAGE.equal("DE"))
>>> > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
>>> > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
>>> > .having(count().greaterThan(5))
>>> > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
>>> > .limit(1)
>>> > .offset(2)
>>> > .forUpdate();
>>> >
>>> > JPA does very badly in this area. However, you notice you are
>>>actually
>>> > translating raw SQL into JOOQ DSL. In fact writing such a query is
>>> > harder in JOOQ than in raw SQL, because you must not only know SQL
>>> > knowledge but also know JOOQ dialects. The only gain is type-safe. I
>>> > know Java developers are used to static type too much, they are not
>>> > willing to write any code that cannot be checked by compiler. Type
>>> > safe is the reason that no ORM is perfect, and it can never be.
>>> >
>>> > In summary, I don't think dropping in any ORM can solve problem
>>> > perfectly, if I did it, I would do:
>>> >
>>> > - Using JPA based framework.
>>> > - building persistent interface(create/update/delete) and single
>>>table
>>> > search criteria using JPA
>>> > - for multiple tables query, either using JPA JPQL(if you don't mind
>>> > type
>>> > safe) or dropping in some fluent API framework if you can not stand
>>> > for any type unsafe thing.
>>> >
>>> >
>>> >
>>> > > -----Original Message-----
>>> > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
>>> > > Sent: Tuesday, November 26, 2013 3:58 AM
>>> > > To: dev@cloudstack.apache.org
>>> > > Subject: Re: persistence layer
>>> > >
>>> > > 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 <Al...@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-wi
>>> > > th-
>>> > > 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
>>> >
>
Re: persistence layer
Posted by Kelven Yang <ke...@citrix.com>.
I generally in agree with what Darren has pointed. Within the context of
ACS, declarative transaction support is not as urgent as solving the
problem as we are having in writing queries. Solving basic CRUD and query
problem alone in a consistent pattern can move CloudStack persistence
layer a big step ahead. And the good part of it is that we can do it
incrementally, which I think it¹s the key for us to really move forward
quickly.
I saw a lot of efforts trying to hide the complexity of underlying problem
but eventually end up to a more complex solution. With that said, myself
actually prefer a simple solution that is close to SQL as natural but
solves the DB-agnostic problem to a reasonable level, I don¹t like too
much of the idea trying to hide SQL completely away from developers. So
far JOOQ sounds like a good candidate to me.
Kelven
On 12/2/13, 3:07 PM, "Frank Zhang" <Fr...@citrix.com> wrote:
>Hi Lukas:
> Thank for detailed reply, see my comments inline
>
>>
>> Hi Frank,
>>
>> Thank you for your detailed feedback. This is very interesting insight
>>for me and
>> for Data Geekery. Let me try to address your concerns by first
>>categorising
>> them into the following items (please correct me if I might have
>> misunderstood).
>>
>> - A concern about Spring support.
>> - A concern about standards (JPA)
>> - A concern about CRUD verbosity
>> - A concern about fluent APIs vs. SQL
>> - A concern about fluent APIs vs. custom query building APIs
>
>Your explanation frees my concerns from last three items. I don't
>carefully read
>entire JOOQ document and I think most people won't when they try to get
>an overall picture of a new project.
>
>So if we can highlight these items(especially CRUD verbosity and custom
>query builder)
>somewhere, it would be more appealing to developers who have
>JPA/Hibernate background.
>
>>
>> Spring support
>> ===========
>> It is true that we cannot offer you a "formal" affiliation with Spring
>>(or rather
>> with Oliver Gierke, the lead developer behind Spring Data). I have been
>> observing this collaboration between Spring Data and QueryDSL and I
>>feel that
>> it is a strategic alliance that mutually benefits both vendors.
>> QueryDSL gets some more traction by being auto-shipped to a much broader
>> audience, whereas Spring Data gets a fluent API for both JPA and SQL.
>>
>> From our perspective, we want jOOQ to be entirely independent of any
>> transaction and connection pooling API as this facilitates integration
>>with JTA
>> transaction modelling and other models. We believe that by keeping
>>things
>> simple and by separating concerns, you will get a stack that is better
>>focused on
>> your individual needs. In that way, we believe that you should be able
>>to
>> combine Spring IOC with jOOQ just as much as with JEE components.
>>
>> Most importantly, Spring Data, much like JDO in the past and like
>>QueryDSL
>> today aim for integrating NoSQL databases into a unified API, which we
>>find a
>> poor fit, as transaction models, data models, query models are
>>fundamentally
>> different between these various data stores. We believe that by
>>pursuing such
>> unification, frameworks are easily distracted from sound SQL support. We
>> highly value Spring's transaction model simplification and this has
>>worked well
>> with jOOQ in the past.
>
>My whole point here is about declarative transaction management which is
>the
>most compelling part of Spring Data(from Spring's own words). Just did
>more
>research that JOOQ is a DB access layer it won't involve in any
>transaction
>management stuff. If my understanding is correct, it should automatically
>work
>with Spring TX? Am I right?
>
>No matter the answer, JOOQ should give the best practice in document,
>searching chapter names in http://www.jooq.org/doc/3.2/manual/
>returns nothing about transaction and I have seen people asking
>similar question on Stack Overflow. I saw your answer too :)
>
>http://stackoverflow.com/questions/15159842/how-to-start-transaction-and-r
>ollback-with-jooq
>
>it's very true that we can draw a clear boundary between DB access layer
>and
>transaction management, but for most of time users don't care about this
>because
>they are searching an entire solution for DB. Even if JOOQ developers know
>transaction management is not focus of this project, they still need to
>tell their users
>the best way to go instead of letting user figure out it themselves.
>
>I don't think JOOQ has to integrate with SpringDB like QueryDSL does, for
>example,
>having a JOOQTemplate. But I do think integrating with Spring TX is
>important
>given it's popularity.
>
>
>>
>> Standards (JPA)
>> ============
>> Now, one of your arguments in favour of JPA is the non-lock-in. Spring
>>has been
>> challenging JEE standards for quite some time and there is no other
>>vendor in
>> the market to replace Spring once you build upon Spring. Even more so,
>>by
>> choosing a Spring+QueryDSL combination, you get an equally strong
>>vendor-
>> lockin (or even a stronger one, as the stack gets more
>> complex) than when choosing Spring+jOOQ. If standards are important to
>>you, I
>> suggest you only use JPA and Criteria Query (Note, that JEE also
>>supports IoC).
>> And I'm sure that Adam Bien will agree with me on this :-) But even
>>then, you
>> will have a hard time migrating from Hibernate to EclipseLink or
>>OpenJPA as
>> the implementations are subtly different in many ways.
>>
>> The decision is up to you, and I fully understand these concerns.
>>Standards are a
>> good thing because many disparate things from various vendors have been
>> unified and hopefully matured over time. But vendors also innovate and
>>thus
>> deviate from the standard again. Without such deviation, there would be
>>no
>> more progress, nor innovation in a standard. Take EclipseLink for
>>instance, a
>> JPA implementation that has started to "flirt" with NoSQL
>> support:
>> http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
>>
>> The above is orthogonal to JPA and will add lots of complexity to this
>>particular
>> implementation. JDO was another standard that has attempted to integrate
>> other, non-relational data stores in a single API, yet it has not
>>succeeded,
>> leaving a pretty much dead standard.
>>
>> Now, while you are absolutely right by saying that you cannot switch the
>> implementation of the jOOQ API at the moment, we're striving to
>>innovate on
>> the standards front as we see room for various JSRs in the area of
>>RDBMS meta
>> modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling in
>> general, SQL DSL modelling in particular. We believe that jOOQ is the
>>most
>> innovative platform for SQL to Java integration in the market at the
>>moment
>>
>
>I strongly agree with the point that "none lock-in" is actually in theory
>most of time,
>In reality, there is more or less you have to do or sacrifice when moving.
>
>My concern here is about SQL engine layer. The SQL engine I mean is the
>part which
>generates final SQL statement. For my perspective, JOOQ is DSL API + SQL
>engine,
>where DSL API is the main business. My understanding is the DSL API is
>now tightly coupled
>with SQL engine that I can not switch to another vender like Hibernate.
>
>As a user, I may prefer DSL API provided by JOOQ, but have some
>performance concerns about
>underlying SQL engine and apt to use Hibernate, then I have to make a
>hard decision.
>This may not be a valid point to you. First JOOQ may have better
>performance than Hibernate(I saw
>reads about SQL performance mentioned in JOOQ document, which hints that
>JOOQ concerns much
>about performance). Second, I don't know if Hibernate can be solely used
>as a SQL engine.
>
>But decoupling the API from SQL engine provides potential flexibility for
>future, and might be easier
>to make the DSL API as a JSR.
>We have seen this much in DB. JPA is just a set of API. Mysql splits
>storage engine from server logic
>(Though the storage engine still couples with some server side logic like
>row locks).
>
>Conclusion, besides the transaction management, I don't have too much
>concerns in using JOOQ in CloudStack now.
>I am glad to see after many years there is still a project actively
>working on solving DB problem in Java.
>
>
>> CRUD verbosity
>> ===========
>> > In this area, JPA does the right thing.
>> >
>> > Author author = new Author(...);
>> > JPAEntityManary.persist(author);
>> >
>> > The later one is more Java more OOP.
>>
>> Compare this to jOOQ's CRUD features:
>> AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
>>
>> Or alternatively:
>> AuthorDao dao = ...
>> dao.insert(new Author());
>>
>> Relevant sections in the manual are:
>> -
>> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
>> updatablerecords/
>> -
>> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
>> updatablerecords/simple-crud/
>> - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
>>
>> Fluent APIs vs. SQL
>> ===============
>> I fully understand this concern. Using the jOOQ fluent API is not the
>>same as
>> writing SQL directly. But let me remind you of the concerns you are
>>having with
>> respect to database independence. By writing SQL directly (through JDBC,
>> through views or through stored procedures) you will not be able to
>>support
>> dozens of databases very easily. Both JPA and jOOQ solve this problem
>>quite
>> well. JPA abstracts SQL entirely, while jOOQ abstracts the subtle
>>differences,
>> only.
>>
>> Fluent APIs vs. custom query building APIs
>> ================================ In addition to jOOQ's fluent API, there
>> is also a simpler, object-oriented SelectQuery model:
>> http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
>>
>> The same applies for other types of statements (InsertQuery,
>>UpdateQuery,
>> etc.). Thus, compare this fluent API usage:
>>
>> > create.selectFrom(BOOK)
>> > .where(BOOK.PUBLISHED_IN.eq(2011))
>> > .orderBy(BOOK.TITLE)
>>
>> With this SelectQuery usage:
>>
>> SelectQuery<Book> select = create.selectQuery(BOOK);
>> select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
>> select.addOrderBy(BOOK.TITLE);
>> Result<Book> result = select.fetch();
>>
>> This is also documented here:
>>
>>http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and-no
>>n-
>> dsl/
>>
>> Conclusion
>> ========
>> I hope I could put jOOQ in a different light to you. If JPA is
>>sufficient for your
>> use-cases, then use JPA (or JPA+jOOQ). There are some problems that jOOQ
>> will never solve, which are solved very well in JPA. We have outlined
>>these
>> differences here:
>> http://www.hibernate-alternative.com
>>
>> However, by using jOOQ, you not only gain a very high level of
>>typesafety and
>> thus an increase of productivity through the usage of jOOQ's code
>>generator
>> which helps you detect errors early, you also get access to a very rich
>>set of
>> service provider interfaces, which were off-topic in this discussion so
>>far. Some
>> examples:
>>
>> ExecuteListeners for custom query execution lifecycle handling:
>> http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
>>
>> Schema and table mapping for multi-tenancy support:
>>
>>http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-schem
>>a-
>> mapping/
>>
>> Record mapping in order to provide custom mapping algorithms from jOOQ
>> records to your domain model:
>> http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-
>> recordmapper-provider/
>>
>> A query transformation listener that is still being worked on, allowing
>>for
>> arbitrary SQL transformation on the jOOQ AST. This can be very useful
>>for
>> features like row-level security.
>>
>> As I said, we believe that there is currently no other software in the
>>Java
>> market that takes your SQL so seriously, and we're highly committed to
>> improve this even further in the near future.
>>
>> Best Regards,
>> Lukas
>>
>> 2013/11/27 Frank Zhang <Fr...@citrix.com>
>>
>> > Hi Lukas:
>> > Glad to see developer behind JOOQ here, I originally
>> > recommended using QueryDSL instead of JOOQ, there are some of my
>> > concerns:
>> >
>> > - Spring support. You know CloudStack is based on Spring IOC, the most
>> > compelling thing of Spring DB is its transaction management framework.
>> > QueryDSL has been integrated into Spring for two years. But there is
>> > only a simple page (
>> > http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with
>> > -spring/) demonstrating JOOQ and Spring, however, the code just shows
>> > how to load JOOQ in IOC, it doesn't show any advanced feature of
>> > Spring DB for example declarative transaction management. If JOOQ is
>> > not compatible with Spring DB, then we loss a widely used feature.
>> >
>> > - It's not JPA based on. This might be an argument that JOOQ may claim
>> > as its biggest advantage. I agree JPA does have some bad thing(e.g.
>> > criteria API), but the rest part is doing very well.
>> > The most important thing here is JPA is just API so I can choose
>> > underlying vendor by my preference. This non-lock-in feature is very
>> > appealing. For example, Hibernate has been proven by industry for 10
>> > years, I can fairly trust its quality. And vendor may have some
>> > advanced feature like secondary cache I may need someday.
>> > Instead, using JOOQ means I have to rely on its underlying
>> > implementation with no choice.
>> >
>> > You know almost all JPA vendors support different SQL dialects. And
>> > fluent API(or DSL) is not attractive to me. Frankly speaking, IMO, for
>> > insert/update/delete clause the fluent API makes thing unnecessary
>> > complex that you are writing something which is even more complicated
>> > than SQL, for example in JOOQ,
>> >
>> > == persistence ===
>> > create.insertInto(AUTHOR)
>> > .set(AUTHOR.ID, 100)
>> > .set(AUTHOR.FIRST_NAME, "Hermann")
>> > .set(AUTHOR.LAST_NAME, "Hesse")
>> > .newRecord()
>> > .set(AUTHOR.ID, 101)
>> > .set(AUTHOR.FIRST_NAME, "Alfred")
>> > .set(AUTHOR.LAST_NAME, "Döblin");
>> >
>> > In this area, JPA does the right thing.
>> >
>> > Author author = new Author(...);
>> > JPAEntityManary.persist(author);
>> >
>> > The later one is more Java more OOP.
>> >
>> > === single table query ====
>> > Speaking of query, for single table query, fluent API is not
>> > attractive too. For example:
>> >
>> > Raw SQL:
>> > SELECT * FROM BOOK
>> > WHERE BOOK.PUBLISHED_IN = 2011
>> > ORDER BY BOOK.TITLE
>> >
>> > JOOQ:
>> > create.selectFrom(BOOK)
>> > .where(BOOK.PUBLISHED_IN.eq(2011))
>> > .orderBy(BOOK.TITLE)
>> >
>> >
>> > CloudStack search criteria built on JPA:
>> > Qeury<Book> q = new Qeury<Book>();
>> > q.add(Book_.PublishedIn. Op.eq, 2011); q.orderBy(Book_.title);
>> > List<Book> books = q.list();
>> >
>> > The last way is more Java for sure, people who even has no SQL
>> > knowledge can write the search.
>> >
>> > === multiple tables query ===
>> > The only place fluent API shining is querying multiple tables:
>> >
>> > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
>> > FROM AUTHOR
>> > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
>> > WHERE BOOK.LANGUAGE = 'DE'
>> > AND BOOK.PUBLISHED > '2008-01-01'
>> > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
>> > HAVING COUNT(*) > 5
>> > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
>> > LIMIT 2
>> > OFFSET 1
>> > FOR UPDATE
>> >
>> > JOOQ:
>> >
>> > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
>> > .from(AUTHOR)
>> > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
>> > .where(BOOK.LANGUAGE.equal("DE"))
>> > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
>> > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
>> > .having(count().greaterThan(5))
>> > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
>> > .limit(1)
>> > .offset(2)
>> > .forUpdate();
>> >
>> > JPA does very badly in this area. However, you notice you are actually
>> > translating raw SQL into JOOQ DSL. In fact writing such a query is
>> > harder in JOOQ than in raw SQL, because you must not only know SQL
>> > knowledge but also know JOOQ dialects. The only gain is type-safe. I
>> > know Java developers are used to static type too much, they are not
>> > willing to write any code that cannot be checked by compiler. Type
>> > safe is the reason that no ORM is perfect, and it can never be.
>> >
>> > In summary, I don't think dropping in any ORM can solve problem
>> > perfectly, if I did it, I would do:
>> >
>> > - Using JPA based framework.
>> > - building persistent interface(create/update/delete) and single table
>> > search criteria using JPA
>> > - for multiple tables query, either using JPA JPQL(if you don't mind
>> > type
>> > safe) or dropping in some fluent API framework if you can not stand
>> > for any type unsafe thing.
>> >
>> >
>> >
>> > > -----Original Message-----
>> > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
>> > > Sent: Tuesday, November 26, 2013 3:58 AM
>> > > To: dev@cloudstack.apache.org
>> > > Subject: Re: persistence layer
>> > >
>> > > 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 <Al...@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-wi
>> > > th-
>> > > 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
>> >
RE: persistence layer
Posted by Frank Zhang <Fr...@citrix.com>.
Thanks Lukas. Things are clear now
> -----Original Message-----
> From: Lukas Eder [mailto:lukas.eder@gmail.com]
> Sent: Thursday, December 05, 2013 7:05 AM
> To: dev@cloudstack.apache.org
> Subject: Re: persistence layer
>
> Hello,
>
> From the follow-up posts on this discussion, I can see that many concerns have
> been resolved. So I'll merely answer the open points Frank had.
>
> 2013/12/3 Frank Zhang <Fr...@citrix.com>
>
> > Hi Lukas:
> > Thank for detailed reply, see my comments inline
> >
> > >
> > > Hi Frank,
> > >
> > > Thank you for your detailed feedback. This is very interesting
> > > insight
> > for me and
> > > for Data Geekery. Let me try to address your concerns by first
> > categorising
> > > them into the following items (please correct me if I might have
> > > misunderstood).
> > >
> > > - A concern about Spring support.
> > > - A concern about standards (JPA)
> > > - A concern about CRUD verbosity
> > > - A concern about fluent APIs vs. SQL
> > > - A concern about fluent APIs vs. custom query building APIs
> >
> > Your explanation frees my concerns from last three items. I don't
> > carefully read entire JOOQ document and I think most people won't when
> > they try to get an overall picture of a new project.
> >
> > So if we can highlight these items(especially CRUD verbosity and
> > custom query builder) somewhere, it would be more appealing to
> > developers who have JPA/Hibernate background.
> >
> > >
> > > Spring support
> > > ===========
> > > It is true that we cannot offer you a "formal" affiliation with
> > > Spring
> > (or rather
> > > with Oliver Gierke, the lead developer behind Spring Data). I have
> > > been observing this collaboration between Spring Data and QueryDSL
> > > and I feel
> > that
> > > it is a strategic alliance that mutually benefits both vendors.
> > > QueryDSL gets some more traction by being auto-shipped to a much
> > > broader audience, whereas Spring Data gets a fluent API for both JPA and
> SQL.
> > >
> > > From our perspective, we want jOOQ to be entirely independent of any
> > > transaction and connection pooling API as this facilitates
> > > integration
> > with JTA
> > > transaction modelling and other models. We believe that by keeping
> > > things simple and by separating concerns, you will get a stack that
> > > is better
> > focused on
> > > your individual needs. In that way, we believe that you should be
> > > able to combine Spring IOC with jOOQ just as much as with JEE components.
> > >
> > > Most importantly, Spring Data, much like JDO in the past and like
> > QueryDSL
> > > today aim for integrating NoSQL databases into a unified API, which
> > > we
> > find a
> > > poor fit, as transaction models, data models, query models are
> > fundamentally
> > > different between these various data stores. We believe that by
> > > pursuing
> > such
> > > unification, frameworks are easily distracted from sound SQL
> > > support. We highly value Spring's transaction model simplification
> > > and this has
> > worked well
> > > with jOOQ in the past.
> >
> > My whole point here is about declarative transaction management which
> > is the most compelling part of Spring Data(from Spring's own words).
> > Just did more research that JOOQ is a DB access layer it won't involve
> > in any transaction management stuff. If my understanding is correct,
> > it should automatically work with Spring TX? Am I right?
> >
>
> Yes, people on the jOOQ User Group have experienced this particular
> integration to be very easy thanks to Spring TX's versatility.
>
>
> > No matter the answer, JOOQ should give the best practice in document,
> > searching chapter names in http://www.jooq.org/doc/3.2/manual/
> > returns nothing about transaction and I have seen people asking
> > similar question on Stack Overflow. I saw your answer too :)
> >
>
> Yes, that is true. We're working on better documentation and examples in that
> area.
>
> http://stackoverflow.com/questions/15159842/how-to-start-transaction-and-
> rollback-with-jooq
> >
> > it's very true that we can draw a clear boundary between DB access
> > layer and transaction management, but for most of time users don't
> > care about this because they are searching an entire solution for DB.
> > Even if JOOQ developers know transaction management is not focus of
> > this project, they still need to tell their users the best way to go
> > instead of letting user figure out it themselves.
> >
>
> This is not what we have experienced. It is true that complete default
> behaviours are desireable to decrease the initial learning curve.
>
> But from our experience, things like transaction management are so complex
> and uniquely tied to the target architecture that imposing a transaction model
> would be a bad idea. Hibernate's session management for instance may easily
> leak out from your data access layer into your business (or even UI) logic.
>
> From what I've read on this thread, many people appreciate Apache
> CloudStack's current transaction model, wanting to replace only the querying.
> In that sense, jOOQ not imposing a transaction model could be seen as a good
> thing.
>
>
> > I don't think JOOQ has to integrate with SpringDB like QueryDSL does,
> > for example, having a JOOQTemplate. But I do think integrating with
> > Spring TX is important given it's popularity.
> >
>
> Yes, absolutely.
>
> Best Regards,
> Lukas
>
>
> > >
> > > Standards (JPA)
> > > ============
> > > Now, one of your arguments in favour of JPA is the non-lock-in.
> > > Spring
> > has been
> > > challenging JEE standards for quite some time and there is no other
> > vendor in
> > > the market to replace Spring once you build upon Spring. Even more
> > > so, by choosing a Spring+QueryDSL combination, you get an equally
> > > strong vendor- lockin (or even a stronger one, as the stack gets
> > > more
> > > complex) than when choosing Spring+jOOQ. If standards are important
> > > to
> > you, I
> > > suggest you only use JPA and Criteria Query (Note, that JEE also
> > supports IoC).
> > > And I'm sure that Adam Bien will agree with me on this :-) But even
> > then, you
> > > will have a hard time migrating from Hibernate to EclipseLink or
> > > OpenJPA
> > as
> > > the implementations are subtly different in many ways.
> > >
> > > The decision is up to you, and I fully understand these concerns.
> > Standards are a
> > > good thing because many disparate things from various vendors have
> > > been unified and hopefully matured over time. But vendors also
> > > innovate and
> > thus
> > > deviate from the standard again. Without such deviation, there would
> > > be
> > no
> > > more progress, nor innovation in a standard. Take EclipseLink for
> > instance, a
> > > JPA implementation that has started to "flirt" with NoSQL
> > > support:
> > > http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
> > >
> > > The above is orthogonal to JPA and will add lots of complexity to
> > > this
> > particular
> > > implementation. JDO was another standard that has attempted to
> > > integrate other, non-relational data stores in a single API, yet it
> > > has not
> > succeeded,
> > > leaving a pretty much dead standard.
> > >
> > > Now, while you are absolutely right by saying that you cannot switch
> > > the implementation of the jOOQ API at the moment, we're striving to
> > > innovate
> > on
> > > the standards front as we see room for various JSRs in the area of
> > > RDBMS
> > meta
> > > modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling
> > > in general, SQL DSL modelling in particular. We believe that jOOQ is
> > > the
> > most
> > > innovative platform for SQL to Java integration in the market at the
> > moment
> > >
> >
> > I strongly agree with the point that "none lock-in" is actually in
> > theory most of time, In reality, there is more or less you have to do
> > or sacrifice when moving.
> >
> > My concern here is about SQL engine layer. The SQL engine I mean is
> > the part which generates final SQL statement. For my perspective, JOOQ
> > is DSL API + SQL engine, where DSL API is the main business. My
> > understanding is the DSL API is now tightly coupled with SQL engine
> > that I can not switch to another vender like Hibernate.
> >
> > As a user, I may prefer DSL API provided by JOOQ, but have some
> > performance concerns about underlying SQL engine and apt to use
> > Hibernate, then I have to make a hard decision.
> > This may not be a valid point to you. First JOOQ may have better
> > performance than Hibernate(I saw reads about SQL performance mentioned
> > in JOOQ document, which hints that JOOQ concerns much about
> > performance). Second, I don't know if Hibernate can be solely used as
> > a SQL engine.
> >
> > But decoupling the API from SQL engine provides potential flexibility
> > for future, and might be easier to make the DSL API as a JSR.
> > We have seen this much in DB. JPA is just a set of API. Mysql splits
> > storage engine from server logic (Though the storage engine still
> > couples with some server side logic like row locks).
> >
> > Conclusion, besides the transaction management, I don't have too much
> > concerns in using JOOQ in CloudStack now.
> > I am glad to see after many years there is still a project actively
> > working on solving DB problem in Java.
> >
> >
> > > CRUD verbosity
> > > ===========
> > > > In this area, JPA does the right thing.
> > > >
> > > > Author author = new Author(...);
> > > > JPAEntityManary.persist(author);
> > > >
> > > > The later one is more Java more OOP.
> > >
> > > Compare this to jOOQ's CRUD features:
> > > AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
> > >
> > > Or alternatively:
> > > AuthorDao dao = ...
> > > dao.insert(new Author());
> > >
> > > Relevant sections in the manual are:
> > > -
> > > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
> > > updatablerecords/
> > > -
> > > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
> > > updatablerecords/simple-crud/
> > > - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
> > >
> > > Fluent APIs vs. SQL
> > > ===============
> > > I fully understand this concern. Using the jOOQ fluent API is not
> > > the
> > same as
> > > writing SQL directly. But let me remind you of the concerns you are
> > having with
> > > respect to database independence. By writing SQL directly (through
> > > JDBC, through views or through stored procedures) you will not be
> > > able to
> > support
> > > dozens of databases very easily. Both JPA and jOOQ solve this
> > > problem
> > quite
> > > well. JPA abstracts SQL entirely, while jOOQ abstracts the subtle
> > differences,
> > > only.
> > >
> > > Fluent APIs vs. custom query building APIs
> > > ================================ In addition to jOOQ's fluent API,
> > > there is also a simpler, object-oriented SelectQuery model:
> > > http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
> > >
> > > The same applies for other types of statements (InsertQuery,
> > > UpdateQuery, etc.). Thus, compare this fluent API usage:
> > >
> > > > create.selectFrom(BOOK)
> > > > .where(BOOK.PUBLISHED_IN.eq(2011))
> > > > .orderBy(BOOK.TITLE)
> > >
> > > With this SelectQuery usage:
> > >
> > > SelectQuery<Book> select = create.selectQuery(BOOK);
> > > select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
> > > select.addOrderBy(BOOK.TITLE);
> > > Result<Book> result = select.fetch();
> > >
> > > This is also documented here:
> > >
> > http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and
> > -non-
> > > dsl/
> > >
> > > Conclusion
> > > ========
> > > I hope I could put jOOQ in a different light to you. If JPA is
> > sufficient for your
> > > use-cases, then use JPA (or JPA+jOOQ). There are some problems that
> > > jOOQ will never solve, which are solved very well in JPA. We have
> > > outlined
> > these
> > > differences here:
> > > http://www.hibernate-alternative.com
> > >
> > > However, by using jOOQ, you not only gain a very high level of
> > typesafety and
> > > thus an increase of productivity through the usage of jOOQ's code
> > generator
> > > which helps you detect errors early, you also get access to a very
> > > rich
> > set of
> > > service provider interfaces, which were off-topic in this discussion
> > > so
> > far. Some
> > > examples:
> > >
> > > ExecuteListeners for custom query execution lifecycle handling:
> > > http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
> > >
> > > Schema and table mapping for multi-tenancy support:
> > >
> > http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-sc
> > hema-
> > > mapping/
> > >
> > > Record mapping in order to provide custom mapping algorithms from
> > > jOOQ records to your domain model:
> > > http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with
> > > -
> > > recordmapper-provider/
> > >
> > > A query transformation listener that is still being worked on,
> > > allowing
> > for
> > > arbitrary SQL transformation on the jOOQ AST. This can be very
> > > useful for features like row-level security.
> > >
> > > As I said, we believe that there is currently no other software in
> > > the
> > Java
> > > market that takes your SQL so seriously, and we're highly committed
> > > to improve this even further in the near future.
> > >
> > > Best Regards,
> > > Lukas
> > >
> > > 2013/11/27 Frank Zhang <Fr...@citrix.com>
> > >
> > > > Hi Lukas:
> > > > Glad to see developer behind JOOQ here, I originally
> > > > recommended using QueryDSL instead of JOOQ, there are some of my
> > > > concerns:
> > > >
> > > > - Spring support. You know CloudStack is based on Spring IOC, the
> > > > most compelling thing of Spring DB is its transaction management
> framework.
> > > > QueryDSL has been integrated into Spring for two years. But there
> > > > is only a simple page (
> > > > http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-
> > > > with
> > > > -spring/) demonstrating JOOQ and Spring, however, the code just
> > > > shows how to load JOOQ in IOC, it doesn't show any advanced
> > > > feature of Spring DB for example declarative transaction
> > > > management. If JOOQ is not compatible with Spring DB, then we loss a
> widely used feature.
> > > >
> > > > - It's not JPA based on. This might be an argument that JOOQ may
> > > > claim as its biggest advantage. I agree JPA does have some bad thing(e.g.
> > > > criteria API), but the rest part is doing very well.
> > > > The most important thing here is JPA is just API so I can choose
> > > > underlying vendor by my preference. This non-lock-in feature is
> > > > very appealing. For example, Hibernate has been proven by industry
> > > > for 10 years, I can fairly trust its quality. And vendor may have
> > > > some advanced feature like secondary cache I may need someday.
> > > > Instead, using JOOQ means I have to rely on its underlying
> > > > implementation with no choice.
> > > >
> > > > You know almost all JPA vendors support different SQL dialects.
> > > > And fluent API(or DSL) is not attractive to me. Frankly speaking,
> > > > IMO, for insert/update/delete clause the fluent API makes thing
> > > > unnecessary complex that you are writing something which is even
> > > > more complicated than SQL, for example in JOOQ,
> > > >
> > > > == persistence ===
> > > > create.insertInto(AUTHOR)
> > > > .set(AUTHOR.ID, 100)
> > > > .set(AUTHOR.FIRST_NAME, "Hermann")
> > > > .set(AUTHOR.LAST_NAME, "Hesse")
> > > > .newRecord()
> > > > .set(AUTHOR.ID, 101)
> > > > .set(AUTHOR.FIRST_NAME, "Alfred")
> > > > .set(AUTHOR.LAST_NAME, "Döblin");
> > > >
> > > > In this area, JPA does the right thing.
> > > >
> > > > Author author = new Author(...);
> > > > JPAEntityManary.persist(author);
> > > >
> > > > The later one is more Java more OOP.
> > > >
> > > > === single table query ====
> > > > Speaking of query, for single table query, fluent API is not
> > > > attractive too. For example:
> > > >
> > > > Raw SQL:
> > > > SELECT * FROM BOOK
> > > > WHERE BOOK.PUBLISHED_IN = 2011
> > > > ORDER BY BOOK.TITLE
> > > >
> > > > JOOQ:
> > > > create.selectFrom(BOOK)
> > > > .where(BOOK.PUBLISHED_IN.eq(2011))
> > > > .orderBy(BOOK.TITLE)
> > > >
> > > >
> > > > CloudStack search criteria built on JPA:
> > > > Qeury<Book> q = new Qeury<Book>(); q.add(Book_.PublishedIn. Op.eq,
> > > > 2011); q.orderBy(Book_.title); List<Book> books = q.list();
> > > >
> > > > The last way is more Java for sure, people who even has no SQL
> > > > knowledge can write the search.
> > > >
> > > > === multiple tables query ===
> > > > The only place fluent API shining is querying multiple tables:
> > > >
> > > > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
> > > > FROM AUTHOR
> > > > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
> > > > WHERE BOOK.LANGUAGE = 'DE'
> > > > AND BOOK.PUBLISHED > '2008-01-01'
> > > > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
> > > > HAVING COUNT(*) > 5
> > > > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
> > > > LIMIT 2
> > > > OFFSET 1
> > > > FOR UPDATE
> > > >
> > > > JOOQ:
> > > >
> > > > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
> > > > .from(AUTHOR)
> > > > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
> > > > .where(BOOK.LANGUAGE.equal("DE"))
> > > > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
> > > > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
> > > > .having(count().greaterThan(5))
> > > > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
> > > > .limit(1)
> > > > .offset(2)
> > > > .forUpdate();
> > > >
> > > > JPA does very badly in this area. However, you notice you are
> > > > actually translating raw SQL into JOOQ DSL. In fact writing such a
> > > > query is harder in JOOQ than in raw SQL, because you must not only
> > > > know SQL knowledge but also know JOOQ dialects. The only gain is
> > > > type-safe. I know Java developers are used to static type too
> > > > much, they are not willing to write any code that cannot be
> > > > checked by compiler. Type safe is the reason that no ORM is perfect, and
> it can never be.
> > > >
> > > > In summary, I don't think dropping in any ORM can solve problem
> > > > perfectly, if I did it, I would do:
> > > >
> > > > - Using JPA based framework.
> > > > - building persistent interface(create/update/delete) and single
> > > > table search criteria using JPA
> > > > - for multiple tables query, either using JPA JPQL(if you don't
> > > > mind type
> > > > safe) or dropping in some fluent API framework if you can not
> > > > stand for any type unsafe thing.
> > > >
> > > >
> > > >
> > > > > -----Original Message-----
> > > > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
> > > > > Sent: Tuesday, November 26, 2013 3:58 AM
> > > > > To: dev@cloudstack.apache.org
> > > > > Subject: Re: persistence layer
> > > > >
> > > > > 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 <Al...@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-n
> > > > ull-
> > > > values/
> > > > > [3]:
> > > > http://blog.jooq.org/2011/08/31/rdbms-bind-variable-casting-madnes
> > > > s/
> > > > > [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/joo
> > > > > q-wi
> > > > > th-
> > > > > spring/
> > > > > -
> > > > > http://blog.jooq.org/2012/09/19/a-nice-way-of-using-jooq-with-sp
> > > > > ring
> > > > > /
> > > > > -
> > > > > 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
> > > >
> >
Re: persistence layer
Posted by Lukas Eder <lu...@gmail.com>.
Hello,
>From the follow-up posts on this discussion, I can see that many concerns
have been resolved. So I'll merely answer the open points Frank had.
2013/12/3 Frank Zhang <Fr...@citrix.com>
> Hi Lukas:
> Thank for detailed reply, see my comments inline
>
> >
> > Hi Frank,
> >
> > Thank you for your detailed feedback. This is very interesting insight
> for me and
> > for Data Geekery. Let me try to address your concerns by first
> categorising
> > them into the following items (please correct me if I might have
> > misunderstood).
> >
> > - A concern about Spring support.
> > - A concern about standards (JPA)
> > - A concern about CRUD verbosity
> > - A concern about fluent APIs vs. SQL
> > - A concern about fluent APIs vs. custom query building APIs
>
> Your explanation frees my concerns from last three items. I don't
> carefully read
> entire JOOQ document and I think most people won't when they try to get
> an overall picture of a new project.
>
> So if we can highlight these items(especially CRUD verbosity and custom
> query builder)
> somewhere, it would be more appealing to developers who have JPA/Hibernate
> background.
>
> >
> > Spring support
> > ===========
> > It is true that we cannot offer you a "formal" affiliation with Spring
> (or rather
> > with Oliver Gierke, the lead developer behind Spring Data). I have been
> > observing this collaboration between Spring Data and QueryDSL and I feel
> that
> > it is a strategic alliance that mutually benefits both vendors.
> > QueryDSL gets some more traction by being auto-shipped to a much broader
> > audience, whereas Spring Data gets a fluent API for both JPA and SQL.
> >
> > From our perspective, we want jOOQ to be entirely independent of any
> > transaction and connection pooling API as this facilitates integration
> with JTA
> > transaction modelling and other models. We believe that by keeping things
> > simple and by separating concerns, you will get a stack that is better
> focused on
> > your individual needs. In that way, we believe that you should be able to
> > combine Spring IOC with jOOQ just as much as with JEE components.
> >
> > Most importantly, Spring Data, much like JDO in the past and like
> QueryDSL
> > today aim for integrating NoSQL databases into a unified API, which we
> find a
> > poor fit, as transaction models, data models, query models are
> fundamentally
> > different between these various data stores. We believe that by pursuing
> such
> > unification, frameworks are easily distracted from sound SQL support. We
> > highly value Spring's transaction model simplification and this has
> worked well
> > with jOOQ in the past.
>
> My whole point here is about declarative transaction management which is
> the
> most compelling part of Spring Data(from Spring's own words). Just did more
> research that JOOQ is a DB access layer it won't involve in any transaction
> management stuff. If my understanding is correct, it should automatically
> work
> with Spring TX? Am I right?
>
Yes, people on the jOOQ User Group have experienced this particular
integration to be very easy thanks to Spring TX's versatility.
> No matter the answer, JOOQ should give the best practice in document,
> searching chapter names in http://www.jooq.org/doc/3.2/manual/
> returns nothing about transaction and I have seen people asking
> similar question on Stack Overflow. I saw your answer too :)
>
Yes, that is true. We're working on better documentation and examples in
that area.
http://stackoverflow.com/questions/15159842/how-to-start-transaction-and-rollback-with-jooq
>
> it's very true that we can draw a clear boundary between DB access layer
> and
> transaction management, but for most of time users don't care about this
> because
> they are searching an entire solution for DB. Even if JOOQ developers know
> transaction management is not focus of this project, they still need to
> tell their users
> the best way to go instead of letting user figure out it themselves.
>
This is not what we have experienced. It is true that complete default
behaviours are desireable to decrease the initial learning curve.
But from our experience, things like transaction management are so complex
and uniquely tied to the target architecture that imposing a transaction
model would be a bad idea. Hibernate's session management for instance may
easily leak out from your data access layer into your business (or even UI)
logic.
>From what I've read on this thread, many people appreciate Apache
CloudStack's current transaction model, wanting to replace only the
querying. In that sense, jOOQ not imposing a transaction model could be
seen as a good thing.
> I don't think JOOQ has to integrate with SpringDB like QueryDSL does, for
> example,
> having a JOOQTemplate. But I do think integrating with Spring TX is
> important
> given it's popularity.
>
Yes, absolutely.
Best Regards,
Lukas
> >
> > Standards (JPA)
> > ============
> > Now, one of your arguments in favour of JPA is the non-lock-in. Spring
> has been
> > challenging JEE standards for quite some time and there is no other
> vendor in
> > the market to replace Spring once you build upon Spring. Even more so, by
> > choosing a Spring+QueryDSL combination, you get an equally strong vendor-
> > lockin (or even a stronger one, as the stack gets more
> > complex) than when choosing Spring+jOOQ. If standards are important to
> you, I
> > suggest you only use JPA and Criteria Query (Note, that JEE also
> supports IoC).
> > And I'm sure that Adam Bien will agree with me on this :-) But even
> then, you
> > will have a hard time migrating from Hibernate to EclipseLink or OpenJPA
> as
> > the implementations are subtly different in many ways.
> >
> > The decision is up to you, and I fully understand these concerns.
> Standards are a
> > good thing because many disparate things from various vendors have been
> > unified and hopefully matured over time. But vendors also innovate and
> thus
> > deviate from the standard again. Without such deviation, there would be
> no
> > more progress, nor innovation in a standard. Take EclipseLink for
> instance, a
> > JPA implementation that has started to "flirt" with NoSQL
> > support:
> > http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
> >
> > The above is orthogonal to JPA and will add lots of complexity to this
> particular
> > implementation. JDO was another standard that has attempted to integrate
> > other, non-relational data stores in a single API, yet it has not
> succeeded,
> > leaving a pretty much dead standard.
> >
> > Now, while you are absolutely right by saying that you cannot switch the
> > implementation of the jOOQ API at the moment, we're striving to innovate
> on
> > the standards front as we see room for various JSRs in the area of RDBMS
> meta
> > modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling in
> > general, SQL DSL modelling in particular. We believe that jOOQ is the
> most
> > innovative platform for SQL to Java integration in the market at the
> moment
> >
>
> I strongly agree with the point that "none lock-in" is actually in theory
> most of time,
> In reality, there is more or less you have to do or sacrifice when moving.
>
> My concern here is about SQL engine layer. The SQL engine I mean is the
> part which
> generates final SQL statement. For my perspective, JOOQ is DSL API + SQL
> engine,
> where DSL API is the main business. My understanding is the DSL API is
> now tightly coupled
> with SQL engine that I can not switch to another vender like Hibernate.
>
> As a user, I may prefer DSL API provided by JOOQ, but have some
> performance concerns about
> underlying SQL engine and apt to use Hibernate, then I have to make a hard
> decision.
> This may not be a valid point to you. First JOOQ may have better
> performance than Hibernate(I saw
> reads about SQL performance mentioned in JOOQ document, which hints that
> JOOQ concerns much
> about performance). Second, I don't know if Hibernate can be solely used
> as a SQL engine.
>
> But decoupling the API from SQL engine provides potential flexibility for
> future, and might be easier
> to make the DSL API as a JSR.
> We have seen this much in DB. JPA is just a set of API. Mysql splits
> storage engine from server logic
> (Though the storage engine still couples with some server side logic like
> row locks).
>
> Conclusion, besides the transaction management, I don't have too much
> concerns in using JOOQ in CloudStack now.
> I am glad to see after many years there is still a project actively
> working on solving DB problem in Java.
>
>
> > CRUD verbosity
> > ===========
> > > In this area, JPA does the right thing.
> > >
> > > Author author = new Author(...);
> > > JPAEntityManary.persist(author);
> > >
> > > The later one is more Java more OOP.
> >
> > Compare this to jOOQ's CRUD features:
> > AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
> >
> > Or alternatively:
> > AuthorDao dao = ...
> > dao.insert(new Author());
> >
> > Relevant sections in the manual are:
> > -
> > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
> > updatablerecords/
> > -
> > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
> > updatablerecords/simple-crud/
> > - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
> >
> > Fluent APIs vs. SQL
> > ===============
> > I fully understand this concern. Using the jOOQ fluent API is not the
> same as
> > writing SQL directly. But let me remind you of the concerns you are
> having with
> > respect to database independence. By writing SQL directly (through JDBC,
> > through views or through stored procedures) you will not be able to
> support
> > dozens of databases very easily. Both JPA and jOOQ solve this problem
> quite
> > well. JPA abstracts SQL entirely, while jOOQ abstracts the subtle
> differences,
> > only.
> >
> > Fluent APIs vs. custom query building APIs
> > ================================ In addition to jOOQ's fluent API, there
> > is also a simpler, object-oriented SelectQuery model:
> > http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
> >
> > The same applies for other types of statements (InsertQuery, UpdateQuery,
> > etc.). Thus, compare this fluent API usage:
> >
> > > create.selectFrom(BOOK)
> > > .where(BOOK.PUBLISHED_IN.eq(2011))
> > > .orderBy(BOOK.TITLE)
> >
> > With this SelectQuery usage:
> >
> > SelectQuery<Book> select = create.selectQuery(BOOK);
> > select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
> > select.addOrderBy(BOOK.TITLE);
> > Result<Book> result = select.fetch();
> >
> > This is also documented here:
> >
> http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and-non-
> > dsl/
> >
> > Conclusion
> > ========
> > I hope I could put jOOQ in a different light to you. If JPA is
> sufficient for your
> > use-cases, then use JPA (or JPA+jOOQ). There are some problems that jOOQ
> > will never solve, which are solved very well in JPA. We have outlined
> these
> > differences here:
> > http://www.hibernate-alternative.com
> >
> > However, by using jOOQ, you not only gain a very high level of
> typesafety and
> > thus an increase of productivity through the usage of jOOQ's code
> generator
> > which helps you detect errors early, you also get access to a very rich
> set of
> > service provider interfaces, which were off-topic in this discussion so
> far. Some
> > examples:
> >
> > ExecuteListeners for custom query execution lifecycle handling:
> > http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
> >
> > Schema and table mapping for multi-tenancy support:
> >
> http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-schema-
> > mapping/
> >
> > Record mapping in order to provide custom mapping algorithms from jOOQ
> > records to your domain model:
> > http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-
> > recordmapper-provider/
> >
> > A query transformation listener that is still being worked on, allowing
> for
> > arbitrary SQL transformation on the jOOQ AST. This can be very useful for
> > features like row-level security.
> >
> > As I said, we believe that there is currently no other software in the
> Java
> > market that takes your SQL so seriously, and we're highly committed to
> > improve this even further in the near future.
> >
> > Best Regards,
> > Lukas
> >
> > 2013/11/27 Frank Zhang <Fr...@citrix.com>
> >
> > > Hi Lukas:
> > > Glad to see developer behind JOOQ here, I originally
> > > recommended using QueryDSL instead of JOOQ, there are some of my
> > > concerns:
> > >
> > > - Spring support. You know CloudStack is based on Spring IOC, the most
> > > compelling thing of Spring DB is its transaction management framework.
> > > QueryDSL has been integrated into Spring for two years. But there is
> > > only a simple page (
> > > http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with
> > > -spring/) demonstrating JOOQ and Spring, however, the code just shows
> > > how to load JOOQ in IOC, it doesn't show any advanced feature of
> > > Spring DB for example declarative transaction management. If JOOQ is
> > > not compatible with Spring DB, then we loss a widely used feature.
> > >
> > > - It's not JPA based on. This might be an argument that JOOQ may claim
> > > as its biggest advantage. I agree JPA does have some bad thing(e.g.
> > > criteria API), but the rest part is doing very well.
> > > The most important thing here is JPA is just API so I can choose
> > > underlying vendor by my preference. This non-lock-in feature is very
> > > appealing. For example, Hibernate has been proven by industry for 10
> > > years, I can fairly trust its quality. And vendor may have some
> > > advanced feature like secondary cache I may need someday.
> > > Instead, using JOOQ means I have to rely on its underlying
> > > implementation with no choice.
> > >
> > > You know almost all JPA vendors support different SQL dialects. And
> > > fluent API(or DSL) is not attractive to me. Frankly speaking, IMO, for
> > > insert/update/delete clause the fluent API makes thing unnecessary
> > > complex that you are writing something which is even more complicated
> > > than SQL, for example in JOOQ,
> > >
> > > == persistence ===
> > > create.insertInto(AUTHOR)
> > > .set(AUTHOR.ID, 100)
> > > .set(AUTHOR.FIRST_NAME, "Hermann")
> > > .set(AUTHOR.LAST_NAME, "Hesse")
> > > .newRecord()
> > > .set(AUTHOR.ID, 101)
> > > .set(AUTHOR.FIRST_NAME, "Alfred")
> > > .set(AUTHOR.LAST_NAME, "Döblin");
> > >
> > > In this area, JPA does the right thing.
> > >
> > > Author author = new Author(...);
> > > JPAEntityManary.persist(author);
> > >
> > > The later one is more Java more OOP.
> > >
> > > === single table query ====
> > > Speaking of query, for single table query, fluent API is not
> > > attractive too. For example:
> > >
> > > Raw SQL:
> > > SELECT * FROM BOOK
> > > WHERE BOOK.PUBLISHED_IN = 2011
> > > ORDER BY BOOK.TITLE
> > >
> > > JOOQ:
> > > create.selectFrom(BOOK)
> > > .where(BOOK.PUBLISHED_IN.eq(2011))
> > > .orderBy(BOOK.TITLE)
> > >
> > >
> > > CloudStack search criteria built on JPA:
> > > Qeury<Book> q = new Qeury<Book>();
> > > q.add(Book_.PublishedIn. Op.eq, 2011); q.orderBy(Book_.title);
> > > List<Book> books = q.list();
> > >
> > > The last way is more Java for sure, people who even has no SQL
> > > knowledge can write the search.
> > >
> > > === multiple tables query ===
> > > The only place fluent API shining is querying multiple tables:
> > >
> > > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
> > > FROM AUTHOR
> > > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
> > > WHERE BOOK.LANGUAGE = 'DE'
> > > AND BOOK.PUBLISHED > '2008-01-01'
> > > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
> > > HAVING COUNT(*) > 5
> > > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
> > > LIMIT 2
> > > OFFSET 1
> > > FOR UPDATE
> > >
> > > JOOQ:
> > >
> > > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
> > > .from(AUTHOR)
> > > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
> > > .where(BOOK.LANGUAGE.equal("DE"))
> > > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
> > > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
> > > .having(count().greaterThan(5))
> > > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
> > > .limit(1)
> > > .offset(2)
> > > .forUpdate();
> > >
> > > JPA does very badly in this area. However, you notice you are actually
> > > translating raw SQL into JOOQ DSL. In fact writing such a query is
> > > harder in JOOQ than in raw SQL, because you must not only know SQL
> > > knowledge but also know JOOQ dialects. The only gain is type-safe. I
> > > know Java developers are used to static type too much, they are not
> > > willing to write any code that cannot be checked by compiler. Type
> > > safe is the reason that no ORM is perfect, and it can never be.
> > >
> > > In summary, I don't think dropping in any ORM can solve problem
> > > perfectly, if I did it, I would do:
> > >
> > > - Using JPA based framework.
> > > - building persistent interface(create/update/delete) and single table
> > > search criteria using JPA
> > > - for multiple tables query, either using JPA JPQL(if you don't mind
> > > type
> > > safe) or dropping in some fluent API framework if you can not stand
> > > for any type unsafe thing.
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
> > > > Sent: Tuesday, November 26, 2013 3:58 AM
> > > > To: dev@cloudstack.apache.org
> > > > Subject: Re: persistence layer
> > > >
> > > > 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 <Al...@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-wi
> > > > th-
> > > > 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
> > >
>
RE: persistence layer
Posted by Frank Zhang <Fr...@citrix.com>.
Hi Lukas:
Thank for detailed reply, see my comments inline
>
> Hi Frank,
>
> Thank you for your detailed feedback. This is very interesting insight for me and
> for Data Geekery. Let me try to address your concerns by first categorising
> them into the following items (please correct me if I might have
> misunderstood).
>
> - A concern about Spring support.
> - A concern about standards (JPA)
> - A concern about CRUD verbosity
> - A concern about fluent APIs vs. SQL
> - A concern about fluent APIs vs. custom query building APIs
Your explanation frees my concerns from last three items. I don't carefully read
entire JOOQ document and I think most people won't when they try to get
an overall picture of a new project.
So if we can highlight these items(especially CRUD verbosity and custom query builder)
somewhere, it would be more appealing to developers who have JPA/Hibernate background.
>
> Spring support
> ===========
> It is true that we cannot offer you a "formal" affiliation with Spring (or rather
> with Oliver Gierke, the lead developer behind Spring Data). I have been
> observing this collaboration between Spring Data and QueryDSL and I feel that
> it is a strategic alliance that mutually benefits both vendors.
> QueryDSL gets some more traction by being auto-shipped to a much broader
> audience, whereas Spring Data gets a fluent API for both JPA and SQL.
>
> From our perspective, we want jOOQ to be entirely independent of any
> transaction and connection pooling API as this facilitates integration with JTA
> transaction modelling and other models. We believe that by keeping things
> simple and by separating concerns, you will get a stack that is better focused on
> your individual needs. In that way, we believe that you should be able to
> combine Spring IOC with jOOQ just as much as with JEE components.
>
> Most importantly, Spring Data, much like JDO in the past and like QueryDSL
> today aim for integrating NoSQL databases into a unified API, which we find a
> poor fit, as transaction models, data models, query models are fundamentally
> different between these various data stores. We believe that by pursuing such
> unification, frameworks are easily distracted from sound SQL support. We
> highly value Spring's transaction model simplification and this has worked well
> with jOOQ in the past.
My whole point here is about declarative transaction management which is the
most compelling part of Spring Data(from Spring's own words). Just did more
research that JOOQ is a DB access layer it won't involve in any transaction
management stuff. If my understanding is correct, it should automatically work
with Spring TX? Am I right?
No matter the answer, JOOQ should give the best practice in document,
searching chapter names in http://www.jooq.org/doc/3.2/manual/
returns nothing about transaction and I have seen people asking
similar question on Stack Overflow. I saw your answer too :)
http://stackoverflow.com/questions/15159842/how-to-start-transaction-and-rollback-with-jooq
it's very true that we can draw a clear boundary between DB access layer and
transaction management, but for most of time users don't care about this because
they are searching an entire solution for DB. Even if JOOQ developers know
transaction management is not focus of this project, they still need to tell their users
the best way to go instead of letting user figure out it themselves.
I don't think JOOQ has to integrate with SpringDB like QueryDSL does, for example,
having a JOOQTemplate. But I do think integrating with Spring TX is important
given it's popularity.
>
> Standards (JPA)
> ============
> Now, one of your arguments in favour of JPA is the non-lock-in. Spring has been
> challenging JEE standards for quite some time and there is no other vendor in
> the market to replace Spring once you build upon Spring. Even more so, by
> choosing a Spring+QueryDSL combination, you get an equally strong vendor-
> lockin (or even a stronger one, as the stack gets more
> complex) than when choosing Spring+jOOQ. If standards are important to you, I
> suggest you only use JPA and Criteria Query (Note, that JEE also supports IoC).
> And I'm sure that Adam Bien will agree with me on this :-) But even then, you
> will have a hard time migrating from Hibernate to EclipseLink or OpenJPA as
> the implementations are subtly different in many ways.
>
> The decision is up to you, and I fully understand these concerns. Standards are a
> good thing because many disparate things from various vendors have been
> unified and hopefully matured over time. But vendors also innovate and thus
> deviate from the standard again. Without such deviation, there would be no
> more progress, nor innovation in a standard. Take EclipseLink for instance, a
> JPA implementation that has started to "flirt" with NoSQL
> support:
> http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
>
> The above is orthogonal to JPA and will add lots of complexity to this particular
> implementation. JDO was another standard that has attempted to integrate
> other, non-relational data stores in a single API, yet it has not succeeded,
> leaving a pretty much dead standard.
>
> Now, while you are absolutely right by saying that you cannot switch the
> implementation of the jOOQ API at the moment, we're striving to innovate on
> the standards front as we see room for various JSRs in the area of RDBMS meta
> modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling in
> general, SQL DSL modelling in particular. We believe that jOOQ is the most
> innovative platform for SQL to Java integration in the market at the moment
>
I strongly agree with the point that "none lock-in" is actually in theory most of time,
In reality, there is more or less you have to do or sacrifice when moving.
My concern here is about SQL engine layer. The SQL engine I mean is the part which
generates final SQL statement. For my perspective, JOOQ is DSL API + SQL engine,
where DSL API is the main business. My understanding is the DSL API is now tightly coupled
with SQL engine that I can not switch to another vender like Hibernate.
As a user, I may prefer DSL API provided by JOOQ, but have some performance concerns about
underlying SQL engine and apt to use Hibernate, then I have to make a hard decision.
This may not be a valid point to you. First JOOQ may have better performance than Hibernate(I saw
reads about SQL performance mentioned in JOOQ document, which hints that JOOQ concerns much
about performance). Second, I don't know if Hibernate can be solely used as a SQL engine.
But decoupling the API from SQL engine provides potential flexibility for future, and might be easier
to make the DSL API as a JSR.
We have seen this much in DB. JPA is just a set of API. Mysql splits storage engine from server logic
(Though the storage engine still couples with some server side logic like row locks).
Conclusion, besides the transaction management, I don't have too much concerns in using JOOQ in CloudStack now.
I am glad to see after many years there is still a project actively working on solving DB problem in Java.
> CRUD verbosity
> ===========
> > In this area, JPA does the right thing.
> >
> > Author author = new Author(...);
> > JPAEntityManary.persist(author);
> >
> > The later one is more Java more OOP.
>
> Compare this to jOOQ's CRUD features:
> AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
>
> Or alternatively:
> AuthorDao dao = ...
> dao.insert(new Author());
>
> Relevant sections in the manual are:
> -
> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
> updatablerecords/
> -
> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-
> updatablerecords/simple-crud/
> - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
>
> Fluent APIs vs. SQL
> ===============
> I fully understand this concern. Using the jOOQ fluent API is not the same as
> writing SQL directly. But let me remind you of the concerns you are having with
> respect to database independence. By writing SQL directly (through JDBC,
> through views or through stored procedures) you will not be able to support
> dozens of databases very easily. Both JPA and jOOQ solve this problem quite
> well. JPA abstracts SQL entirely, while jOOQ abstracts the subtle differences,
> only.
>
> Fluent APIs vs. custom query building APIs
> ================================ In addition to jOOQ's fluent API, there
> is also a simpler, object-oriented SelectQuery model:
> http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
>
> The same applies for other types of statements (InsertQuery, UpdateQuery,
> etc.). Thus, compare this fluent API usage:
>
> > create.selectFrom(BOOK)
> > .where(BOOK.PUBLISHED_IN.eq(2011))
> > .orderBy(BOOK.TITLE)
>
> With this SelectQuery usage:
>
> SelectQuery<Book> select = create.selectQuery(BOOK);
> select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
> select.addOrderBy(BOOK.TITLE);
> Result<Book> result = select.fetch();
>
> This is also documented here:
> http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and-non-
> dsl/
>
> Conclusion
> ========
> I hope I could put jOOQ in a different light to you. If JPA is sufficient for your
> use-cases, then use JPA (or JPA+jOOQ). There are some problems that jOOQ
> will never solve, which are solved very well in JPA. We have outlined these
> differences here:
> http://www.hibernate-alternative.com
>
> However, by using jOOQ, you not only gain a very high level of typesafety and
> thus an increase of productivity through the usage of jOOQ's code generator
> which helps you detect errors early, you also get access to a very rich set of
> service provider interfaces, which were off-topic in this discussion so far. Some
> examples:
>
> ExecuteListeners for custom query execution lifecycle handling:
> http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
>
> Schema and table mapping for multi-tenancy support:
> http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-schema-
> mapping/
>
> Record mapping in order to provide custom mapping algorithms from jOOQ
> records to your domain model:
> http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-
> recordmapper-provider/
>
> A query transformation listener that is still being worked on, allowing for
> arbitrary SQL transformation on the jOOQ AST. This can be very useful for
> features like row-level security.
>
> As I said, we believe that there is currently no other software in the Java
> market that takes your SQL so seriously, and we're highly committed to
> improve this even further in the near future.
>
> Best Regards,
> Lukas
>
> 2013/11/27 Frank Zhang <Fr...@citrix.com>
>
> > Hi Lukas:
> > Glad to see developer behind JOOQ here, I originally
> > recommended using QueryDSL instead of JOOQ, there are some of my
> > concerns:
> >
> > - Spring support. You know CloudStack is based on Spring IOC, the most
> > compelling thing of Spring DB is its transaction management framework.
> > QueryDSL has been integrated into Spring for two years. But there is
> > only a simple page (
> > http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with
> > -spring/) demonstrating JOOQ and Spring, however, the code just shows
> > how to load JOOQ in IOC, it doesn't show any advanced feature of
> > Spring DB for example declarative transaction management. If JOOQ is
> > not compatible with Spring DB, then we loss a widely used feature.
> >
> > - It's not JPA based on. This might be an argument that JOOQ may claim
> > as its biggest advantage. I agree JPA does have some bad thing(e.g.
> > criteria API), but the rest part is doing very well.
> > The most important thing here is JPA is just API so I can choose
> > underlying vendor by my preference. This non-lock-in feature is very
> > appealing. For example, Hibernate has been proven by industry for 10
> > years, I can fairly trust its quality. And vendor may have some
> > advanced feature like secondary cache I may need someday.
> > Instead, using JOOQ means I have to rely on its underlying
> > implementation with no choice.
> >
> > You know almost all JPA vendors support different SQL dialects. And
> > fluent API(or DSL) is not attractive to me. Frankly speaking, IMO, for
> > insert/update/delete clause the fluent API makes thing unnecessary
> > complex that you are writing something which is even more complicated
> > than SQL, for example in JOOQ,
> >
> > == persistence ===
> > create.insertInto(AUTHOR)
> > .set(AUTHOR.ID, 100)
> > .set(AUTHOR.FIRST_NAME, "Hermann")
> > .set(AUTHOR.LAST_NAME, "Hesse")
> > .newRecord()
> > .set(AUTHOR.ID, 101)
> > .set(AUTHOR.FIRST_NAME, "Alfred")
> > .set(AUTHOR.LAST_NAME, "Döblin");
> >
> > In this area, JPA does the right thing.
> >
> > Author author = new Author(...);
> > JPAEntityManary.persist(author);
> >
> > The later one is more Java more OOP.
> >
> > === single table query ====
> > Speaking of query, for single table query, fluent API is not
> > attractive too. For example:
> >
> > Raw SQL:
> > SELECT * FROM BOOK
> > WHERE BOOK.PUBLISHED_IN = 2011
> > ORDER BY BOOK.TITLE
> >
> > JOOQ:
> > create.selectFrom(BOOK)
> > .where(BOOK.PUBLISHED_IN.eq(2011))
> > .orderBy(BOOK.TITLE)
> >
> >
> > CloudStack search criteria built on JPA:
> > Qeury<Book> q = new Qeury<Book>();
> > q.add(Book_.PublishedIn. Op.eq, 2011); q.orderBy(Book_.title);
> > List<Book> books = q.list();
> >
> > The last way is more Java for sure, people who even has no SQL
> > knowledge can write the search.
> >
> > === multiple tables query ===
> > The only place fluent API shining is querying multiple tables:
> >
> > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
> > FROM AUTHOR
> > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
> > WHERE BOOK.LANGUAGE = 'DE'
> > AND BOOK.PUBLISHED > '2008-01-01'
> > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
> > HAVING COUNT(*) > 5
> > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
> > LIMIT 2
> > OFFSET 1
> > FOR UPDATE
> >
> > JOOQ:
> >
> > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
> > .from(AUTHOR)
> > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
> > .where(BOOK.LANGUAGE.equal("DE"))
> > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
> > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
> > .having(count().greaterThan(5))
> > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
> > .limit(1)
> > .offset(2)
> > .forUpdate();
> >
> > JPA does very badly in this area. However, you notice you are actually
> > translating raw SQL into JOOQ DSL. In fact writing such a query is
> > harder in JOOQ than in raw SQL, because you must not only know SQL
> > knowledge but also know JOOQ dialects. The only gain is type-safe. I
> > know Java developers are used to static type too much, they are not
> > willing to write any code that cannot be checked by compiler. Type
> > safe is the reason that no ORM is perfect, and it can never be.
> >
> > In summary, I don't think dropping in any ORM can solve problem
> > perfectly, if I did it, I would do:
> >
> > - Using JPA based framework.
> > - building persistent interface(create/update/delete) and single table
> > search criteria using JPA
> > - for multiple tables query, either using JPA JPQL(if you don't mind
> > type
> > safe) or dropping in some fluent API framework if you can not stand
> > for any type unsafe thing.
> >
> >
> >
> > > -----Original Message-----
> > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
> > > Sent: Tuesday, November 26, 2013 3:58 AM
> > > To: dev@cloudstack.apache.org
> > > Subject: Re: persistence layer
> > >
> > > 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 <Al...@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-wi
> > > th-
> > > 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
> >
Re: persistence layer
Posted by Kelven Yang <ke...@citrix.com>.
On 12/2/13, 5:00 PM, "Frank Zhang" <Fr...@citrix.com> wrote:
>It's really long ....
>
>I don't know if it's impossible to move us to JPA but I do agree that
>whatever
>path we choose it's lot of work to do.
>
>Given you have tried JOOQ and Lukas's explanation, my only concern is if
>we can leverage Spring TX with JOOQ. Especially we just moved to Spring
>TX recently.
>
>I agree most of your statements except DAO pattern. IMO, we should
>get rid of this pattern at all. No matter how Java community prefers it,
>I always think DAO is an anti-pattern that should have never been
>existing.
>
>There is no standard for implementing a DAO. But all implementation has
>below problems:
>
>1. unnecessary
>All DAO have different persistence methods for different domain objects.
>For example.
>
>CustomerDao.saveCustomer(...)
>CustomerDao.updateCustomer(...)
>
>OrderDao.saveOrder(...)
>OrderDao.updateOrder(...)
>
>Why do we need so many interfaces? Why not simply use
>
>EntityManager.persist(CustomerVO);
>EntityManager.persist(OrderVO);
>EntityManager.update(CustomerVO);
>EntityManager.update(OrderVO);
>
>If there any special interests(rare case), using an entity listener can
>solve it perfectly
>
>2. plain wrong
>Lots of DAO(not all) create method for every query. You have stated it
>lacks of extensibility.
>I thought it's just plain wrong.
>
>Combinations of query parameter is infinite, that's why people use SQL
>which is a DSL to manipulate
>DB because you can not enumerate all possibilities by function. Someone
>may argue NoSql doesn't have
>a language like SQL. But if you look into NoSQL query functions, they are
>DSL in essence. DSL like SQL is
>called external DSL which needs a parser, DSL like NoSql query function
>is called internal DSL which uses
>facilities provided by host programming language. So when we create DAO
>like:
>
>CustomerDao.findCustomerByLastName(...)
>CustomerDao.findCustomerByFirstName(...)
>CustomerDao.findCustomerByFirstAndLastName(...)
>
>We are wrong.
>
>My suggestion is, once we switch to new ORM, we should never create DAO
>anymore. We can have a single
>interface to do CRUD, and directly use ORM like JOOQ to do the query.
>Someone may argue again this will duplicate
>query. That's true but doesn't matter. To get flexibility and
>extensibility, duplicate code is a necessary sacrifice.
>We can put the most common query that is queried by primary key in CRUD
>interface, for others, just create a query criteria or
>JOOQ fluent query on demand.
+1. I know Alex has already added entity manager trying to get rid of
unnecessary DAO methods, but due to the way on how we construct queries,
we still have to rely on individual DAOs. A simple CRUD mechanism and SQL
compatible query(agnostic to underlying DB) would be enough for most of
persistence needs in CloudStack.
>
>
>
>> -----Original Message-----
>> From: Darren Shepherd [mailto:darren.s.shepherd@gmail.com]
>> Sent: Monday, December 02, 2013 9:33 AM
>> To: dev@cloudstack.apache.org
>> Subject: Re: persistence layer
>>
>> Alright, this is long..
>>
>> I have to say after the 10+ years of struggling with JDBC, Hibernate,
>> JbdcTemplate, JPA, QueryDSL, and countless custom solutions, I've have
>>never
>> found a database access solution that has impressed me as much as jooq.
>>If I
>> was starting from scratch today I'd definitely choose jooq. Now all of
>>that
>> doesn't matter though. Evaluating database access solutions in
>>isolation is
>> about as productive as discussing if c++ or java is a better language.
>>The
>> answer being "it depends." We really need to ground this discussion in
>>the
>> realities of what ACS is today, what are the issues and where do we
>>want to go.
>> Nobody is going to sign on to a complete gut and rewrite of the data
>>acces
>> layer (DAL), so any solution must allow a slow migration to it.
>>
>> Current solution
>> ============
>> ACS currently has a completely custom DAL. While the VOs are annotated
>>with
>> JPA annotations it is dangerous to compare the custom ACS framework to
>>JPA
>> as the differences are substantial. It is also important to note that
>>ACS does not
>> have a ORM today. There is no relational mapping.
>>
>> The ACS DAL can be broken down to five parts. 1) DAO framework
>>(GenericDao)
>> 2) object mapping 3) query api 4) crud w/ change tracking 5) transaction
>> management
>>
>> DAO framework
>> ============
>> ACS has a strict 1-to-1 mapping of VO to DAO. The generic DAO provides
>>the
>> common methods like findById that you see in most java DAOs. The generic
>> DAO uses reflection to query the VOs' JPA annotations to build up the
>>metadata
>> for the crud operations and query building.
>>
>> Object mapping
>> ===========
>> The metadata obtained through the GenericDao is used to map sql to the
>>VOs.
>> If you use the GenericDaos findX or listByX methods or the query api it
>>will map
>> the result to a VO. Additionally if you update a VO and persist it the
>>mapper
>> will generate SQL from the VO.
>>
>> Query API
>> ========
>> ACS has a search builder api that allows you to build sql queries in a
>>type safe
>> manner. The functionality is mostly limited to basically conditions and
>>joins.
>> The api works for most of the use cases in ACS but still there a quite
>>a lot of
>> raw sql statements in ACS because the api is not functional enough.
>>
>> CRUD with changing tracking
>> =====================
>> When POJOs are created by the GenericDao they are enhanced with cglib.
>>All
>> changes to the pojos are tracked as updates. When persist is called the
>>updated
>> fields are used to construct an update.
>>
>> Transaction management
>> ===================
>> Transactions are managed programmatically through the transaction api.
>> Nested transactions and transaction propagation are not supported.
>> Isolation level is determined at the DB pool level, so all connections
>>have the
>> same level which is read committed.
>>
>> Problems
>> ========
>> The general problems of the dal in ACS is that it's 100% custom. The
>>APIs,
>> especially around transactions and querying, are often a stumbling
>>block for
>> people developing in ACS. Additionally, since this is all custom, the
>> maintenance and enhancements of the DAL is solely the responsibility of
>>the
>> ACS community. Finally, very little documentation exists around all of
>>this, and
>> there is realistically about 3 people who understand the core
>>implementation.
>>
>> You have to consider that ACS was started almost 5 years ago. I can
>>understand
>> the creators not wanting to use hibernate or spring and instead rolling
>>their
>> own solutions. The problem with rolling your own solution typically
>>ends up
>> being that your use cases start off simple and your custom framework
>>does too.
>> As the product matures and more complex use cases are tackled things
>>are no
>> longer simple. You are then forced to enhance your simple framework, but
>> then the warts start to really show. Or, you start bypassing it because
>>it is too
>> limiting.
>>
>> DAO framework
>> ============
>> I don't have major fundamental complaints with the DAOs. The API
>>exposed is
>> fairly consistent with most DAOs you would see in any java application.
>>The
>> main complaint I have is the strict 1-to-1 mapping between VO and DAO.
>>In
>> general I don't like that design pattern and prefer to group DAOs
>>according to
>> function and not tables. That's an opinion and I think the larger java
>>community
>> tends to disagree with me. So I concede on that point. The bigger
>>problem with
>> the 1-to-1 model is extensibility. Today, if you are writing a new
>>plugin and
>> need to execute some query that doesn't exist today, you really don't
>>have
>> much choice but to change the existing DAO in the core.
>> This leads to people instead of doing DB logic in a DAO, they end up
>>doing it in
>> the services classes which is just bad.
>>
>> Object mapping
>> ===========
>> The object mapping in ACS is quite simple. Since there is no relational
>>mapping,
>> it just basically does the equivalent of BeanUtils.setProperty().
>> There is additional logic for database encryption. One of the down side
>>of the
>> current mapping is that it just works for VOs. If you want to do some
>>more
>> complex sql query that returns arbitrary output, you are forced to fall
>>back to
>> raw JDBC. The Query API, since it builds off of the object mapping, it
>>also
>> oriented to just a single VO. If you join across tables you are only
>>joining to
>> restrict the result set of the main table you are querying. If you are
>>trying to
>> get the values of the parent and child tables at the same time, that
>>really isn't
>> possible (or at least obvious how to do it).
>>
>> Query API
>> ========
>> The query API is very problematic. It is your typical in-house SQL
>>abstraction in
>> that probably solved the original creators problems and made sense at
>>the time,
>> but to others it is obscure and difficult to understand.
>> I honestly don't really know off the top of my head how to use it and
>>just copy
>> and paste other examples. For example, tell me if it's obvious to you
>>what the
>> below code is doing.
>>
>> SearchBuilder<NicVO> nicSearch = _nicDao.createSearchBuilder();
>> nicSearch.and("networkId",
>>nicSearch.entity().getNetworkId(),
>> SearchCriteria.Op.EQ);
>> nicSearch.and().op("ip4Address",
>>nicSearch.entity().getIp4Address(),
>> SearchCriteria.Op.NNULL);
>> nicSearch.or("ip6Address",
>>nicSearch.entity().getIp6Address(),
>> SearchCriteria.Op.NNULL);
>> nicSearch.cp();
>>
>> UserVmSearch = createSearchBuilder();
>> UserVmSearch.and("states", UserVmSearch.entity().getState(),
>> SearchCriteria.Op.IN);
>> UserVmSearch.join("nicSearch", nicSearch,
>> UserVmSearch.entity().getId(), nicSearch.entity().getInstanceId(),
>> JoinBuilder.JoinType.INNER);
>> UserVmSearch.done();
>>
>> I will be quite frank and say that the query API is the number one
>>thing in ACS
>> that I despise. The reason being that an IaaS orchestration platform is
>>largely a
>> metadata management platform. It is all about storing metadata and
>>state and
>> reconcilioning that state with external resources. As such, I would
>>expect the
>> platform to have a simple and rich API to access data that establishes
>>good
>> patterns. Instead we have an api that is very limited and obscures the
>>real
>> query going on under the hood. I also think the nature of the api has
>>also lead
>> to a lot of bad practices in code. Those being the scattering of DB
>>logic in the
>> services layer and raw JDBC and SQL strings embedded in code.
>>
>> CRUD with changing tracking
>> =====================
>> The current change tracking is done with enhancing the VOs with cglib.
>>The
>> framework basically is what it is. The pattern is used by other
>>frameworks and I
>> don't have too much complaints.
>>
>> Transaction management
>> ===================
>> In 4.3 I already rewrote the transaction API. The previous problems
>>were all
>> based around the fact that the transaction management is completely
>> programmatic. Developers had to call start(), commit(), and rollback().
>>The
>> patterns of how those APIs were called was very inconsistent and rarely
>>was
>> rollback done properly. The @DB masked a lot of the underlying issues
>>with
>> rollback() as it would do an implicit rollback on failure. The @DB
>>annotation
>> was problematic in that it's meaning was not clearly understood and the
>>AOP
>> style it demands is not compatible with proxy based AOP like Spring.
>>
>> In 4.3 I've change the transaction api to closely match the semantics
>>of Spring's
>> programmatic transaction API. The @DB annotation has no meaning now for
>> the management server (AWS and usage still require @DB).
>>
>> Proposed solutions
>> ==============
>> In general I do not wish to gut the DAL. The impact would greatly
>>destabilize
>> ACS. Instead I want to find a way in which new code can be done better
>>and old
>> code can be slowly refactored if we choose. After looking at JPA
>>solutions I've
>> come the conclusions that having a JPA solution running in parallel
>>with the
>> custom DAO stuff would be a nightmare. Much of this has to do with how
>>ACS
>> and something like hibernate work. I know we can't use hibernate
>>because of
>> the license, but hibernate is the library I know best so I will speak
>>in those
>> terms. (As a side note, the fact that we can't use hibernate also
>>makes me
>> weary of using JPA. If I had to choose I'd probably go with TopLink,
>>but that just
>> makes me nervous. JPA solutions are so complex and you're really
>>married to
>> them once you start using them. It's hard for me to agree to
>>committing to one
>> unless I've had substantial experience with it. It is also extremely
>>difficult to
>> write a fully JPA standard app. You usually fallback to using
>>implementation
>> specific APIs or just plain JDBC.)
>>
>> In ACS we track changes to VOs with cglib. In JPA, changes are really
>>tracked in
>> the context of a session (or entitymanager). Once you close the
>>session or
>> detach the object from a session it loses it's context. Since DB logic
>>is scattered
>> everywhere is ACS, there is no clear place to start and close sessions.
>> This is
>> really not completely the fault of ACS design or anything. JPA is
>>really designed
>> with the idea of container managed transactions and by container that
>>implies
>> Servlet or EJB. We obviously have no EJBs and very little logic in ACS
>>happens
>> in the servlet container. The majority of logic happens in
>>non-container
>> managed code.
>>
>> Additionally, the VOs annotations are not really compatible with JPA.
>> There are some subtleties around inheritance that won't work. I can't
>> remember the details of the issues there. Inheritance in JPA/Hibernate
>>is just a
>> nightmare anyhow. It does really strange things like creating temp
>>tables and
>> since MySQL isn't all that fancy of a DB, you run into weird deadlocks
>>as
>> hibernate is trying to do operations from two tables and then
>>additionally a
>> temp table. Regardless of the interpretation of the JPA annotation,
>>the custom
>> field mapping like encryption that has been done also causes issues.
>> If nobody believe me what a nightmare it will be to run ACS in parallel
>>with JPA,
>> just go try it yourself.
>>
>> I propose we integrate jOOQ. jOOQ will allow us to have a much better
>>API but
>> not have to gut anything. We can include it to specifically address
>>the areas of
>> concern. I have already tested it out and we can run it seemless in
>>parallel
>> with the custom ACS stuff. The custom object mapping ACS does is
>>completely
>> compatible with jOOQ and the cglib stuff is too.
>> You can query from jooq and persist with ACS genericdao. From a
>>developer
>> perspective all that really changes is that instead of using the
>> SearchBuilder/SearchCriteria API you use the jOOQ fluent API to query.
>>All of
>> the existing findByX and listBy methods can remain. We will add more
>> listBy() method that take a jOOQ Condition and not a SearchCriteria.
>> Here's how I see the benefits of moving to jOOQ.
>>
>> DAO framework
>> ============
>> The DAOs will generally stay the same. Since the VO metadata and
>>mapping
>> needs to be integrated we will pull the logic for that out of the
>>GenericDaoBase
>> and externalize it so that it can be used in both the context of the
>>SearchBuilder
>> and also jOOQ. This has the benefit that the strict 1-to-1 mapping of
>>VO to
>> DAO will be removed. Plugins will be free to create their own DAOs
>>that don't
>> conflict with the core DAOs.
>>
>> Object mapping
>> ===========
>> Object mapping will still work as it does today. What we gain is that
>>for more
>> complex queries that don't map to a single VO (or a VO in general) can
>>be
>> mapped to any regular POJOs. The mapping framework in jOOQ is very
>> flexible and powerful, but for most use cases just creating a POJO with
>>the right
>> field names is sufficient.
>>
>> Query API
>> ========
>> The query API is where will see the most benefit from jOOQ. We can
>> deprecate the SearchBuilder/SearchCriteria and just focus on jOOQ. When
>> somebody new comes to ACS we can then just point them to the excellent
>> jOOQ documentation. Additionally, if they already understand SQL,
>>there is
>> very little to learn. Since jOOQ is so powerful we can also get rid of
>>the all the
>> views that were created for the API. I really, really do not like the
>>views that
>> we are creating these days. The problem with views is that every time
>>we
>> make a modification to the API it requires a DDL change.
>> Additionally the view definitions are not compatible across databases.
>> I've already tried to get ACS running on HSQLDB but I gave up on the
>>views.
>> The problem with views is just the problem with SQL in general.
>> Each DB has its nuances with SQL. You need to ensure that the SQL in
>>the views
>> is completely cross DB compatible (which is a pain). Also note that
>>jOOQ
>> handles those SQL nuances so we don't need to the be SQL-92 experts.
>>
>> CRUD with changing tracking
>> =====================
>> This largely stays as is. We will continue with the ACS cglib approach.
>> We will not use jOOQ records that do change tracking themselves. From
>> jOOQ's perspective we will be using just POJOs. Those POJOs will be
>>mapped
>> with our custom mapper and as such we will add the necessary cglib
>>enhancers.
>>
>> Transaction management
>> ===================
>> I've already in the past propose that we move to Spring transaction
>> management. jOOQ is fully compatible with Spring TX. Even if we don't
>>use
>> Spring TX, jOOQ is compatible with ACS's current TX approach. (As a
>>side note,
>> the discussion of Spring Data is moot. I've already somewhat discussed
>>the
>> idea that we will leverage Spring core IoC and Spring TX from Spring and
>> nothing else. I do not wish to use Spring Data. There is not enough
>>value to tie
>> us specifically to that framework. Spring APIs are a can of worms.
>>Once you
>> starting higher level frameworks like Spring MVC or Spring Data you
>>suddenly
>> bind you app to a massive framework. Today if we didn't like Spring
>>anymore,
>> moving to something like Guice would be a small effort. Once you
>>starting
>> using Spring Data, for example, that ability goes away. Also, magic
>> RDBMS/NoSQL abstraction frameworks are useless in my mind. You need to
>> know if you are using a RDBMS or NoSQL. As a separate thread, I do
>>think we
>> should move the usage data to NoSQL using Apache Gora maybe. Gora is
>> intending to use jOOQ under the hood too, so we can still persist usage
>>to a
>> RDBMS if somebody doesn't want a big data
>> solution.)
>>
>> Okay, if you actually made it to this point without falling asleep, I
>>hope you see
>> that I just want to make relatively simple enhancements to the current
>>DAL in
>> ACS and leverage the power of jOOQ to do that.
>>
>> Darren
>>
>>
>>
>> On Thu, Nov 28, 2013 at 4:45 AM, Lukas Eder <lu...@gmail.com>
>>wrote:
>>
>> > Hi Frank,
>> >
>> > Thank you for your detailed feedback. This is very interesting insight
>> > for me and for Data Geekery. Let me try to address your concerns by
>> > first categorising them into the following items (please correct me if
>> > I might have misunderstood).
>> >
>> > - A concern about Spring support.
>> > - A concern about standards (JPA)
>> > - A concern about CRUD verbosity
>> > - A concern about fluent APIs vs. SQL
>> > - A concern about fluent APIs vs. custom query building APIs
>> >
>> > Spring support
>> > ===========
>> > It is true that we cannot offer you a "formal" affiliation with Spring
>> > (or rather with Oliver Gierke, the lead developer behind Spring Data).
>> > I have been observing this collaboration between Spring Data and
>> > QueryDSL and I feel that it is a strategic alliance that mutually
>>benefits both
>> vendors.
>> > QueryDSL gets some more traction by being auto-shipped to a much
>> > broader audience, whereas Spring Data gets a fluent API for both JPA
>>and SQL.
>> >
>> > From our perspective, we want jOOQ to be entirely independent of any
>> > transaction and connection pooling API as this facilitates integration
>> > with JTA transaction modelling and other models. We believe that by
>> > keeping things simple and by separating concerns, you will get a stack
>> > that is better focused on your individual needs. In that way, we
>> > believe that you should be able to combine Spring IOC with jOOQ just
>> > as much as with JEE components.
>> >
>> > Most importantly, Spring Data, much like JDO in the past and like
>> > QueryDSL today aim for integrating NoSQL databases into a unified API,
>> > which we find a poor fit, as transaction models, data models, query
>> > models are fundamentally different between these various data stores.
>> > We believe that by pursuing such unification, frameworks are easily
>> > distracted from sound SQL support. We highly value Spring's
>> > transaction model simplification and this has worked well with jOOQ
>>in the
>> past.
>> >
>> > Standards (JPA)
>> > ============
>> > Now, one of your arguments in favour of JPA is the non-lock-in. Spring
>> > has been challenging JEE standards for quite some time and there is no
>> > other vendor in the market to replace Spring once you build upon
>> > Spring. Even more so, by choosing a Spring+QueryDSL combination, you
>> > get an equally strong vendor-lockin (or even a stronger one, as the
>> > stack gets more
>> > complex) than when choosing Spring+jOOQ. If standards are important to
>> > you, I suggest you only use JPA and Criteria Query (Note, that JEE
>> > also supports IoC). And I'm sure that Adam Bien will agree with me on
>> > this :-) But even then, you will have a hard time migrating from
>> > Hibernate to EclipseLink or OpenJPA as the implementations are subtly
>> > different in many ways.
>> >
>> > The decision is up to you, and I fully understand these concerns.
>> > Standards are a good thing because many disparate things from various
>> > vendors have been unified and hopefully matured over time. But vendors
>> > also innovate and thus deviate from the standard again. Without such
>> > deviation, there would be no more progress, nor innovation in a
>> > standard. Take EclipseLink for instance, a JPA implementation that has
>> > started to "flirt" with NoSQL
>> > support:
>> > http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
>> >
>> > The above is orthogonal to JPA and will add lots of complexity to this
>> > particular implementation. JDO was another standard that has attempted
>> > to integrate other, non-relational data stores in a single API, yet it
>> > has not succeeded, leaving a pretty much dead standard.
>> >
>> > Now, while you are absolutely right by saying that you cannot switch
>> > the implementation of the jOOQ API at the moment, we're striving to
>> > innovate on the standards front as we see room for various JSRs in the
>> > area of RDBMS meta modelling (beyond JDBC's DatabaseMetaData),
>> > internal DSL modelling in general, SQL DSL modelling in particular. We
>> > believe that jOOQ is the most innovative platform for SQL to Java
>> > integration in the market at the moment
>> >
>> > CRUD verbosity
>> > ===========
>> > > In this area, JPA does the right thing.
>> > >
>> > > Author author = new Author(...);
>> > > JPAEntityManary.persist(author);
>> > >
>> > > The later one is more Java more OOP.
>> >
>> > Compare this to jOOQ's CRUD features:
>> > AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
>> >
>> > Or alternatively:
>> > AuthorDao dao = ...
>> > dao.insert(new Author());
>> >
>> > Relevant sections in the manual are:
>> > -
>> >
>> > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablere
>> > cords/
>> > -
>> >
>> > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablere
>> > cords/simple-crud/
>> > - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
>> >
>> > Fluent APIs vs. SQL
>> > ===============
>> > I fully understand this concern. Using the jOOQ fluent API is not the
>> > same as writing SQL directly. But let me remind you of the concerns
>> > you are having with respect to database independence. By writing SQL
>> > directly (through JDBC, through views or through stored procedures)
>> > you will not be able to support dozens of databases very easily. Both
>> > JPA and jOOQ solve this problem quite well. JPA abstracts SQL
>> > entirely, while jOOQ abstracts the subtle differences, only.
>> >
>> > Fluent APIs vs. custom query building APIs
>> > ================================ In addition to jOOQ's fluent API,
>> > there is also a simpler, object-oriented SelectQuery model:
>> > http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
>> >
>> > The same applies for other types of statements (InsertQuery,
>> > UpdateQuery, etc.). Thus, compare this fluent API usage:
>> >
>> > > create.selectFrom(BOOK)
>> > > .where(BOOK.PUBLISHED_IN.eq(2011))
>> > > .orderBy(BOOK.TITLE)
>> >
>> > With this SelectQuery usage:
>> >
>> > SelectQuery<Book> select = create.selectQuery(BOOK);
>> > select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
>> > select.addOrderBy(BOOK.TITLE);
>> > Result<Book> result = select.fetch();
>> >
>> > This is also documented here:
>> >
>> > http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and
>> > -non-dsl/
>> >
>> > Conclusion
>> > ========
>> > I hope I could put jOOQ in a different light to you. If JPA is
>> > sufficient for your use-cases, then use JPA (or JPA+jOOQ). There are
>> > some problems that jOOQ will never solve, which are solved very well
>> > in JPA. We have outlined these differences here:
>> > http://www.hibernate-alternative.com
>> >
>> > However, by using jOOQ, you not only gain a very high level of
>> > typesafety and thus an increase of productivity through the usage of
>> > jOOQ's code generator which helps you detect errors early, you also
>> > get access to a very rich set of service provider interfaces, which
>> > were off-topic in this discussion so far. Some examples:
>> >
>> > ExecuteListeners for custom query execution lifecycle handling:
>> > http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
>> >
>> > Schema and table mapping for multi-tenancy support:
>> >
>> > http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-sc
>> > hema-mapping/
>> >
>> > Record mapping in order to provide custom mapping algorithms from jOOQ
>> > records to your domain model:
>> >
>> > http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-r
>> > ecordmapper-provider/
>> >
>> > A query transformation listener that is still being worked on,
>> > allowing for arbitrary SQL transformation on the jOOQ AST. This can be
>> > very useful for features like row-level security.
>> >
>> > As I said, we believe that there is currently no other software in the
>> > Java market that takes your SQL so seriously, and we're highly
>> > committed to improve this even further in the near future.
>> >
>> > Best Regards,
>> > Lukas
>> >
>> > 2013/11/27 Frank Zhang <Fr...@citrix.com>
>> >
>> > > Hi Lukas:
>> > > Glad to see developer behind JOOQ here, I originally
>> > > recommended using QueryDSL instead of JOOQ, there are some of my
>> > > concerns:
>> > >
>> > > - Spring support. You know CloudStack is based on Spring IOC, the
>> > > most compelling thing of Spring DB is its transaction management
>> > > framework. QueryDSL has been integrated into Spring for two years.
>> > > But there is only a simple page (
>> > >
>> > http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with
>> > -spring/
>> > )
>> > > demonstrating JOOQ and Spring, however, the code just shows how to
>> > > load JOOQ in IOC, it doesn't show any advanced feature of Spring DB
>> > > for example declarative transaction management. If JOOQ is not
>> > > compatible with Spring DB, then we loss a widely used feature.
>> > >
>> > > - It's not JPA based on. This might be an argument that JOOQ may
>> > > claim as its biggest advantage. I agree JPA does have some bad
>> > > thing(e.g. criteria API), but the rest part is doing very well.
>> > > The most important thing here is JPA is just API so I can choose
>> > > underlying vendor by my preference. This non-lock-in feature is very
>> > > appealing. For example, Hibernate has been proven by industry for 10
>> > > years, I can fairly trust its quality. And vendor may have some
>> > > advanced feature like secondary cache I may need someday.
>> > > Instead, using JOOQ means I have to rely on its underlying
>> > > implementation with no choice.
>> > >
>> > > You know almost all JPA vendors support different SQL dialects. And
>> > fluent
>> > > API(or DSL) is not attractive to me. Frankly speaking, IMO, for
>> > > insert/update/delete clause the fluent API makes thing unnecessary
>> > > complex that you are writing something which is even more
>> > > complicated than SQL, for example in JOOQ,
>> > >
>> > > == persistence ===
>> > > create.insertInto(AUTHOR)
>> > > .set(AUTHOR.ID, 100)
>> > > .set(AUTHOR.FIRST_NAME, "Hermann")
>> > > .set(AUTHOR.LAST_NAME, "Hesse")
>> > > .newRecord()
>> > > .set(AUTHOR.ID, 101)
>> > > .set(AUTHOR.FIRST_NAME, "Alfred")
>> > > .set(AUTHOR.LAST_NAME, "Döblin");
>> > >
>> > > In this area, JPA does the right thing.
>> > >
>> > > Author author = new Author(...);
>> > > JPAEntityManary.persist(author);
>> > >
>> > > The later one is more Java more OOP.
>> > >
>> > > === single table query ====
>> > > Speaking of query, for single table query, fluent API is not
>> > > attractive too. For example:
>> > >
>> > > Raw SQL:
>> > > SELECT * FROM BOOK
>> > > WHERE BOOK.PUBLISHED_IN = 2011
>> > > ORDER BY BOOK.TITLE
>> > >
>> > > JOOQ:
>> > > create.selectFrom(BOOK)
>> > > .where(BOOK.PUBLISHED_IN.eq(2011))
>> > > .orderBy(BOOK.TITLE)
>> > >
>> > >
>> > > CloudStack search criteria built on JPA:
>> > > Qeury<Book> q = new Qeury<Book>();
>> > > q.add(Book_.PublishedIn. Op.eq, 2011); q.orderBy(Book_.title);
>> > > List<Book> books = q.list();
>> > >
>> > > The last way is more Java for sure, people who even has no SQL
>> > > knowledge can write the search.
>> > >
>> > > === multiple tables query ===
>> > > The only place fluent API shining is querying multiple tables:
>> > >
>> > > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
>> > > FROM AUTHOR
>> > > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
>> > > WHERE BOOK.LANGUAGE = 'DE'
>> > > AND BOOK.PUBLISHED > '2008-01-01'
>> > > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
>> > > HAVING COUNT(*) > 5
>> > > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
>> > > LIMIT 2
>> > > OFFSET 1
>> > > FOR UPDATE
>> > >
>> > > JOOQ:
>> > >
>> > > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
>> > > .from(AUTHOR)
>> > > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
>> > > .where(BOOK.LANGUAGE.equal("DE"))
>> > > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
>> > > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
>> > > .having(count().greaterThan(5))
>> > > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
>> > > .limit(1)
>> > > .offset(2)
>> > > .forUpdate();
>> > >
>> > > JPA does very badly in this area. However, you notice you are
>> > > actually translating raw SQL into JOOQ DSL. In fact writing such a
>> > > query is harder in JOOQ than in raw SQL, because you must not only
>> > > know SQL knowledge but also know JOOQ dialects. The only gain is
>> > > type-safe. I know Java developers are used to static type too much,
>> > > they are not willing to write any code that cannot be checked by
>> > > compiler. Type safe is the reason that no ORM is perfect, and it can
>> > > never be.
>> > >
>> > > In summary, I don't think dropping in any ORM can solve problem
>> > perfectly,
>> > > if I did it, I would do:
>> > >
>> > > - Using JPA based framework.
>> > > - building persistent interface(create/update/delete) and single
>> > > table search criteria using JPA
>> > > - for multiple tables query, either using JPA JPQL(if you don't mind
>> > > type
>> > > safe) or dropping in some fluent API framework if you can not stand
>> > > for
>> > any
>> > > type unsafe thing.
>> > >
>> > >
>> > >
>> > > > -----Original Message-----
>> > > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
>> > > > Sent: Tuesday, November 26, 2013 3:58 AM
>> > > > To: dev@cloudstack.apache.org
>> > > > Subject: Re: persistence layer
>> > > >
>> > > > 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 <Al...@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
>> > >
>> >
RE: persistence layer
Posted by Frank Zhang <Fr...@citrix.com>.
It's really long ....
I don't know if it's impossible to move us to JPA but I do agree that whatever
path we choose it's lot of work to do.
Given you have tried JOOQ and Lukas's explanation, my only concern is if
we can leverage Spring TX with JOOQ. Especially we just moved to Spring
TX recently.
I agree most of your statements except DAO pattern. IMO, we should
get rid of this pattern at all. No matter how Java community prefers it,
I always think DAO is an anti-pattern that should have never been existing.
There is no standard for implementing a DAO. But all implementation has below problems:
1. unnecessary
All DAO have different persistence methods for different domain objects.
For example.
CustomerDao.saveCustomer(...)
CustomerDao.updateCustomer(...)
OrderDao.saveOrder(...)
OrderDao.updateOrder(...)
Why do we need so many interfaces? Why not simply use
EntityManager.persist(CustomerVO);
EntityManager.persist(OrderVO);
EntityManager.update(CustomerVO);
EntityManager.update(OrderVO);
If there any special interests(rare case), using an entity listener can solve it perfectly
2. plain wrong
Lots of DAO(not all) create method for every query. You have stated it lacks of extensibility.
I thought it's just plain wrong.
Combinations of query parameter is infinite, that's why people use SQL which is a DSL to manipulate
DB because you can not enumerate all possibilities by function. Someone may argue NoSql doesn't have
a language like SQL. But if you look into NoSQL query functions, they are DSL in essence. DSL like SQL is
called external DSL which needs a parser, DSL like NoSql query function is called internal DSL which uses
facilities provided by host programming language. So when we create DAO like:
CustomerDao.findCustomerByLastName(...)
CustomerDao.findCustomerByFirstName(...)
CustomerDao.findCustomerByFirstAndLastName(...)
We are wrong.
My suggestion is, once we switch to new ORM, we should never create DAO anymore. We can have a single
interface to do CRUD, and directly use ORM like JOOQ to do the query. Someone may argue again this will duplicate
query. That's true but doesn't matter. To get flexibility and extensibility, duplicate code is a necessary sacrifice.
We can put the most common query that is queried by primary key in CRUD interface, for others, just create a query criteria or
JOOQ fluent query on demand.
> -----Original Message-----
> From: Darren Shepherd [mailto:darren.s.shepherd@gmail.com]
> Sent: Monday, December 02, 2013 9:33 AM
> To: dev@cloudstack.apache.org
> Subject: Re: persistence layer
>
> Alright, this is long..
>
> I have to say after the 10+ years of struggling with JDBC, Hibernate,
> JbdcTemplate, JPA, QueryDSL, and countless custom solutions, I've have never
> found a database access solution that has impressed me as much as jooq. If I
> was starting from scratch today I'd definitely choose jooq. Now all of that
> doesn't matter though. Evaluating database access solutions in isolation is
> about as productive as discussing if c++ or java is a better language. The
> answer being "it depends." We really need to ground this discussion in the
> realities of what ACS is today, what are the issues and where do we want to go.
> Nobody is going to sign on to a complete gut and rewrite of the data acces
> layer (DAL), so any solution must allow a slow migration to it.
>
> Current solution
> ============
> ACS currently has a completely custom DAL. While the VOs are annotated with
> JPA annotations it is dangerous to compare the custom ACS framework to JPA
> as the differences are substantial. It is also important to note that ACS does not
> have a ORM today. There is no relational mapping.
>
> The ACS DAL can be broken down to five parts. 1) DAO framework (GenericDao)
> 2) object mapping 3) query api 4) crud w/ change tracking 5) transaction
> management
>
> DAO framework
> ============
> ACS has a strict 1-to-1 mapping of VO to DAO. The generic DAO provides the
> common methods like findById that you see in most java DAOs. The generic
> DAO uses reflection to query the VOs' JPA annotations to build up the metadata
> for the crud operations and query building.
>
> Object mapping
> ===========
> The metadata obtained through the GenericDao is used to map sql to the VOs.
> If you use the GenericDaos findX or listByX methods or the query api it will map
> the result to a VO. Additionally if you update a VO and persist it the mapper
> will generate SQL from the VO.
>
> Query API
> ========
> ACS has a search builder api that allows you to build sql queries in a type safe
> manner. The functionality is mostly limited to basically conditions and joins.
> The api works for most of the use cases in ACS but still there a quite a lot of
> raw sql statements in ACS because the api is not functional enough.
>
> CRUD with changing tracking
> =====================
> When POJOs are created by the GenericDao they are enhanced with cglib. All
> changes to the pojos are tracked as updates. When persist is called the updated
> fields are used to construct an update.
>
> Transaction management
> ===================
> Transactions are managed programmatically through the transaction api.
> Nested transactions and transaction propagation are not supported.
> Isolation level is determined at the DB pool level, so all connections have the
> same level which is read committed.
>
> Problems
> ========
> The general problems of the dal in ACS is that it's 100% custom. The APIs,
> especially around transactions and querying, are often a stumbling block for
> people developing in ACS. Additionally, since this is all custom, the
> maintenance and enhancements of the DAL is solely the responsibility of the
> ACS community. Finally, very little documentation exists around all of this, and
> there is realistically about 3 people who understand the core implementation.
>
> You have to consider that ACS was started almost 5 years ago. I can understand
> the creators not wanting to use hibernate or spring and instead rolling their
> own solutions. The problem with rolling your own solution typically ends up
> being that your use cases start off simple and your custom framework does too.
> As the product matures and more complex use cases are tackled things are no
> longer simple. You are then forced to enhance your simple framework, but
> then the warts start to really show. Or, you start bypassing it because it is too
> limiting.
>
> DAO framework
> ============
> I don't have major fundamental complaints with the DAOs. The API exposed is
> fairly consistent with most DAOs you would see in any java application. The
> main complaint I have is the strict 1-to-1 mapping between VO and DAO. In
> general I don't like that design pattern and prefer to group DAOs according to
> function and not tables. That's an opinion and I think the larger java community
> tends to disagree with me. So I concede on that point. The bigger problem with
> the 1-to-1 model is extensibility. Today, if you are writing a new plugin and
> need to execute some query that doesn't exist today, you really don't have
> much choice but to change the existing DAO in the core.
> This leads to people instead of doing DB logic in a DAO, they end up doing it in
> the services classes which is just bad.
>
> Object mapping
> ===========
> The object mapping in ACS is quite simple. Since there is no relational mapping,
> it just basically does the equivalent of BeanUtils.setProperty().
> There is additional logic for database encryption. One of the down side of the
> current mapping is that it just works for VOs. If you want to do some more
> complex sql query that returns arbitrary output, you are forced to fall back to
> raw JDBC. The Query API, since it builds off of the object mapping, it also
> oriented to just a single VO. If you join across tables you are only joining to
> restrict the result set of the main table you are querying. If you are trying to
> get the values of the parent and child tables at the same time, that really isn't
> possible (or at least obvious how to do it).
>
> Query API
> ========
> The query API is very problematic. It is your typical in-house SQL abstraction in
> that probably solved the original creators problems and made sense at the time,
> but to others it is obscure and difficult to understand.
> I honestly don't really know off the top of my head how to use it and just copy
> and paste other examples. For example, tell me if it's obvious to you what the
> below code is doing.
>
> SearchBuilder<NicVO> nicSearch = _nicDao.createSearchBuilder();
> nicSearch.and("networkId", nicSearch.entity().getNetworkId(),
> SearchCriteria.Op.EQ);
> nicSearch.and().op("ip4Address", nicSearch.entity().getIp4Address(),
> SearchCriteria.Op.NNULL);
> nicSearch.or("ip6Address", nicSearch.entity().getIp6Address(),
> SearchCriteria.Op.NNULL);
> nicSearch.cp();
>
> UserVmSearch = createSearchBuilder();
> UserVmSearch.and("states", UserVmSearch.entity().getState(),
> SearchCriteria.Op.IN);
> UserVmSearch.join("nicSearch", nicSearch,
> UserVmSearch.entity().getId(), nicSearch.entity().getInstanceId(),
> JoinBuilder.JoinType.INNER);
> UserVmSearch.done();
>
> I will be quite frank and say that the query API is the number one thing in ACS
> that I despise. The reason being that an IaaS orchestration platform is largely a
> metadata management platform. It is all about storing metadata and state and
> reconcilioning that state with external resources. As such, I would expect the
> platform to have a simple and rich API to access data that establishes good
> patterns. Instead we have an api that is very limited and obscures the real
> query going on under the hood. I also think the nature of the api has also lead
> to a lot of bad practices in code. Those being the scattering of DB logic in the
> services layer and raw JDBC and SQL strings embedded in code.
>
> CRUD with changing tracking
> =====================
> The current change tracking is done with enhancing the VOs with cglib. The
> framework basically is what it is. The pattern is used by other frameworks and I
> don't have too much complaints.
>
> Transaction management
> ===================
> In 4.3 I already rewrote the transaction API. The previous problems were all
> based around the fact that the transaction management is completely
> programmatic. Developers had to call start(), commit(), and rollback(). The
> patterns of how those APIs were called was very inconsistent and rarely was
> rollback done properly. The @DB masked a lot of the underlying issues with
> rollback() as it would do an implicit rollback on failure. The @DB annotation
> was problematic in that it's meaning was not clearly understood and the AOP
> style it demands is not compatible with proxy based AOP like Spring.
>
> In 4.3 I've change the transaction api to closely match the semantics of Spring's
> programmatic transaction API. The @DB annotation has no meaning now for
> the management server (AWS and usage still require @DB).
>
> Proposed solutions
> ==============
> In general I do not wish to gut the DAL. The impact would greatly destabilize
> ACS. Instead I want to find a way in which new code can be done better and old
> code can be slowly refactored if we choose. After looking at JPA solutions I've
> come the conclusions that having a JPA solution running in parallel with the
> custom DAO stuff would be a nightmare. Much of this has to do with how ACS
> and something like hibernate work. I know we can't use hibernate because of
> the license, but hibernate is the library I know best so I will speak in those
> terms. (As a side note, the fact that we can't use hibernate also makes me
> weary of using JPA. If I had to choose I'd probably go with TopLink, but that just
> makes me nervous. JPA solutions are so complex and you're really married to
> them once you start using them. It's hard for me to agree to committing to one
> unless I've had substantial experience with it. It is also extremely difficult to
> write a fully JPA standard app. You usually fallback to using implementation
> specific APIs or just plain JDBC.)
>
> In ACS we track changes to VOs with cglib. In JPA, changes are really tracked in
> the context of a session (or entitymanager). Once you close the session or
> detach the object from a session it loses it's context. Since DB logic is scattered
> everywhere is ACS, there is no clear place to start and close sessions. This is
> really not completely the fault of ACS design or anything. JPA is really designed
> with the idea of container managed transactions and by container that implies
> Servlet or EJB. We obviously have no EJBs and very little logic in ACS happens
> in the servlet container. The majority of logic happens in non-container
> managed code.
>
> Additionally, the VOs annotations are not really compatible with JPA.
> There are some subtleties around inheritance that won't work. I can't
> remember the details of the issues there. Inheritance in JPA/Hibernate is just a
> nightmare anyhow. It does really strange things like creating temp tables and
> since MySQL isn't all that fancy of a DB, you run into weird deadlocks as
> hibernate is trying to do operations from two tables and then additionally a
> temp table. Regardless of the interpretation of the JPA annotation, the custom
> field mapping like encryption that has been done also causes issues.
> If nobody believe me what a nightmare it will be to run ACS in parallel with JPA,
> just go try it yourself.
>
> I propose we integrate jOOQ. jOOQ will allow us to have a much better API but
> not have to gut anything. We can include it to specifically address the areas of
> concern. I have already tested it out and we can run it seemless in parallel
> with the custom ACS stuff. The custom object mapping ACS does is completely
> compatible with jOOQ and the cglib stuff is too.
> You can query from jooq and persist with ACS genericdao. From a developer
> perspective all that really changes is that instead of using the
> SearchBuilder/SearchCriteria API you use the jOOQ fluent API to query. All of
> the existing findByX and listBy methods can remain. We will add more
> listBy() method that take a jOOQ Condition and not a SearchCriteria.
> Here's how I see the benefits of moving to jOOQ.
>
> DAO framework
> ============
> The DAOs will generally stay the same. Since the VO metadata and mapping
> needs to be integrated we will pull the logic for that out of the GenericDaoBase
> and externalize it so that it can be used in both the context of the SearchBuilder
> and also jOOQ. This has the benefit that the strict 1-to-1 mapping of VO to
> DAO will be removed. Plugins will be free to create their own DAOs that don't
> conflict with the core DAOs.
>
> Object mapping
> ===========
> Object mapping will still work as it does today. What we gain is that for more
> complex queries that don't map to a single VO (or a VO in general) can be
> mapped to any regular POJOs. The mapping framework in jOOQ is very
> flexible and powerful, but for most use cases just creating a POJO with the right
> field names is sufficient.
>
> Query API
> ========
> The query API is where will see the most benefit from jOOQ. We can
> deprecate the SearchBuilder/SearchCriteria and just focus on jOOQ. When
> somebody new comes to ACS we can then just point them to the excellent
> jOOQ documentation. Additionally, if they already understand SQL, there is
> very little to learn. Since jOOQ is so powerful we can also get rid of the all the
> views that were created for the API. I really, really do not like the views that
> we are creating these days. The problem with views is that every time we
> make a modification to the API it requires a DDL change.
> Additionally the view definitions are not compatible across databases.
> I've already tried to get ACS running on HSQLDB but I gave up on the views.
> The problem with views is just the problem with SQL in general.
> Each DB has its nuances with SQL. You need to ensure that the SQL in the views
> is completely cross DB compatible (which is a pain). Also note that jOOQ
> handles those SQL nuances so we don't need to the be SQL-92 experts.
>
> CRUD with changing tracking
> =====================
> This largely stays as is. We will continue with the ACS cglib approach.
> We will not use jOOQ records that do change tracking themselves. From
> jOOQ's perspective we will be using just POJOs. Those POJOs will be mapped
> with our custom mapper and as such we will add the necessary cglib enhancers.
>
> Transaction management
> ===================
> I've already in the past propose that we move to Spring transaction
> management. jOOQ is fully compatible with Spring TX. Even if we don't use
> Spring TX, jOOQ is compatible with ACS's current TX approach. (As a side note,
> the discussion of Spring Data is moot. I've already somewhat discussed the
> idea that we will leverage Spring core IoC and Spring TX from Spring and
> nothing else. I do not wish to use Spring Data. There is not enough value to tie
> us specifically to that framework. Spring APIs are a can of worms. Once you
> starting higher level frameworks like Spring MVC or Spring Data you suddenly
> bind you app to a massive framework. Today if we didn't like Spring anymore,
> moving to something like Guice would be a small effort. Once you starting
> using Spring Data, for example, that ability goes away. Also, magic
> RDBMS/NoSQL abstraction frameworks are useless in my mind. You need to
> know if you are using a RDBMS or NoSQL. As a separate thread, I do think we
> should move the usage data to NoSQL using Apache Gora maybe. Gora is
> intending to use jOOQ under the hood too, so we can still persist usage to a
> RDBMS if somebody doesn't want a big data
> solution.)
>
> Okay, if you actually made it to this point without falling asleep, I hope you see
> that I just want to make relatively simple enhancements to the current DAL in
> ACS and leverage the power of jOOQ to do that.
>
> Darren
>
>
>
> On Thu, Nov 28, 2013 at 4:45 AM, Lukas Eder <lu...@gmail.com> wrote:
>
> > Hi Frank,
> >
> > Thank you for your detailed feedback. This is very interesting insight
> > for me and for Data Geekery. Let me try to address your concerns by
> > first categorising them into the following items (please correct me if
> > I might have misunderstood).
> >
> > - A concern about Spring support.
> > - A concern about standards (JPA)
> > - A concern about CRUD verbosity
> > - A concern about fluent APIs vs. SQL
> > - A concern about fluent APIs vs. custom query building APIs
> >
> > Spring support
> > ===========
> > It is true that we cannot offer you a "formal" affiliation with Spring
> > (or rather with Oliver Gierke, the lead developer behind Spring Data).
> > I have been observing this collaboration between Spring Data and
> > QueryDSL and I feel that it is a strategic alliance that mutually benefits both
> vendors.
> > QueryDSL gets some more traction by being auto-shipped to a much
> > broader audience, whereas Spring Data gets a fluent API for both JPA and SQL.
> >
> > From our perspective, we want jOOQ to be entirely independent of any
> > transaction and connection pooling API as this facilitates integration
> > with JTA transaction modelling and other models. We believe that by
> > keeping things simple and by separating concerns, you will get a stack
> > that is better focused on your individual needs. In that way, we
> > believe that you should be able to combine Spring IOC with jOOQ just
> > as much as with JEE components.
> >
> > Most importantly, Spring Data, much like JDO in the past and like
> > QueryDSL today aim for integrating NoSQL databases into a unified API,
> > which we find a poor fit, as transaction models, data models, query
> > models are fundamentally different between these various data stores.
> > We believe that by pursuing such unification, frameworks are easily
> > distracted from sound SQL support. We highly value Spring's
> > transaction model simplification and this has worked well with jOOQ in the
> past.
> >
> > Standards (JPA)
> > ============
> > Now, one of your arguments in favour of JPA is the non-lock-in. Spring
> > has been challenging JEE standards for quite some time and there is no
> > other vendor in the market to replace Spring once you build upon
> > Spring. Even more so, by choosing a Spring+QueryDSL combination, you
> > get an equally strong vendor-lockin (or even a stronger one, as the
> > stack gets more
> > complex) than when choosing Spring+jOOQ. If standards are important to
> > you, I suggest you only use JPA and Criteria Query (Note, that JEE
> > also supports IoC). And I'm sure that Adam Bien will agree with me on
> > this :-) But even then, you will have a hard time migrating from
> > Hibernate to EclipseLink or OpenJPA as the implementations are subtly
> > different in many ways.
> >
> > The decision is up to you, and I fully understand these concerns.
> > Standards are a good thing because many disparate things from various
> > vendors have been unified and hopefully matured over time. But vendors
> > also innovate and thus deviate from the standard again. Without such
> > deviation, there would be no more progress, nor innovation in a
> > standard. Take EclipseLink for instance, a JPA implementation that has
> > started to "flirt" with NoSQL
> > support:
> > http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
> >
> > The above is orthogonal to JPA and will add lots of complexity to this
> > particular implementation. JDO was another standard that has attempted
> > to integrate other, non-relational data stores in a single API, yet it
> > has not succeeded, leaving a pretty much dead standard.
> >
> > Now, while you are absolutely right by saying that you cannot switch
> > the implementation of the jOOQ API at the moment, we're striving to
> > innovate on the standards front as we see room for various JSRs in the
> > area of RDBMS meta modelling (beyond JDBC's DatabaseMetaData),
> > internal DSL modelling in general, SQL DSL modelling in particular. We
> > believe that jOOQ is the most innovative platform for SQL to Java
> > integration in the market at the moment
> >
> > CRUD verbosity
> > ===========
> > > In this area, JPA does the right thing.
> > >
> > > Author author = new Author(...);
> > > JPAEntityManary.persist(author);
> > >
> > > The later one is more Java more OOP.
> >
> > Compare this to jOOQ's CRUD features:
> > AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
> >
> > Or alternatively:
> > AuthorDao dao = ...
> > dao.insert(new Author());
> >
> > Relevant sections in the manual are:
> > -
> >
> > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablere
> > cords/
> > -
> >
> > http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablere
> > cords/simple-crud/
> > - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
> >
> > Fluent APIs vs. SQL
> > ===============
> > I fully understand this concern. Using the jOOQ fluent API is not the
> > same as writing SQL directly. But let me remind you of the concerns
> > you are having with respect to database independence. By writing SQL
> > directly (through JDBC, through views or through stored procedures)
> > you will not be able to support dozens of databases very easily. Both
> > JPA and jOOQ solve this problem quite well. JPA abstracts SQL
> > entirely, while jOOQ abstracts the subtle differences, only.
> >
> > Fluent APIs vs. custom query building APIs
> > ================================ In addition to jOOQ's fluent API,
> > there is also a simpler, object-oriented SelectQuery model:
> > http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
> >
> > The same applies for other types of statements (InsertQuery,
> > UpdateQuery, etc.). Thus, compare this fluent API usage:
> >
> > > create.selectFrom(BOOK)
> > > .where(BOOK.PUBLISHED_IN.eq(2011))
> > > .orderBy(BOOK.TITLE)
> >
> > With this SelectQuery usage:
> >
> > SelectQuery<Book> select = create.selectQuery(BOOK);
> > select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
> > select.addOrderBy(BOOK.TITLE);
> > Result<Book> result = select.fetch();
> >
> > This is also documented here:
> >
> > http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and
> > -non-dsl/
> >
> > Conclusion
> > ========
> > I hope I could put jOOQ in a different light to you. If JPA is
> > sufficient for your use-cases, then use JPA (or JPA+jOOQ). There are
> > some problems that jOOQ will never solve, which are solved very well
> > in JPA. We have outlined these differences here:
> > http://www.hibernate-alternative.com
> >
> > However, by using jOOQ, you not only gain a very high level of
> > typesafety and thus an increase of productivity through the usage of
> > jOOQ's code generator which helps you detect errors early, you also
> > get access to a very rich set of service provider interfaces, which
> > were off-topic in this discussion so far. Some examples:
> >
> > ExecuteListeners for custom query execution lifecycle handling:
> > http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
> >
> > Schema and table mapping for multi-tenancy support:
> >
> > http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-sc
> > hema-mapping/
> >
> > Record mapping in order to provide custom mapping algorithms from jOOQ
> > records to your domain model:
> >
> > http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-r
> > ecordmapper-provider/
> >
> > A query transformation listener that is still being worked on,
> > allowing for arbitrary SQL transformation on the jOOQ AST. This can be
> > very useful for features like row-level security.
> >
> > As I said, we believe that there is currently no other software in the
> > Java market that takes your SQL so seriously, and we're highly
> > committed to improve this even further in the near future.
> >
> > Best Regards,
> > Lukas
> >
> > 2013/11/27 Frank Zhang <Fr...@citrix.com>
> >
> > > Hi Lukas:
> > > Glad to see developer behind JOOQ here, I originally
> > > recommended using QueryDSL instead of JOOQ, there are some of my
> > > concerns:
> > >
> > > - Spring support. You know CloudStack is based on Spring IOC, the
> > > most compelling thing of Spring DB is its transaction management
> > > framework. QueryDSL has been integrated into Spring for two years.
> > > But there is only a simple page (
> > >
> > http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with
> > -spring/
> > )
> > > demonstrating JOOQ and Spring, however, the code just shows how to
> > > load JOOQ in IOC, it doesn't show any advanced feature of Spring DB
> > > for example declarative transaction management. If JOOQ is not
> > > compatible with Spring DB, then we loss a widely used feature.
> > >
> > > - It's not JPA based on. This might be an argument that JOOQ may
> > > claim as its biggest advantage. I agree JPA does have some bad
> > > thing(e.g. criteria API), but the rest part is doing very well.
> > > The most important thing here is JPA is just API so I can choose
> > > underlying vendor by my preference. This non-lock-in feature is very
> > > appealing. For example, Hibernate has been proven by industry for 10
> > > years, I can fairly trust its quality. And vendor may have some
> > > advanced feature like secondary cache I may need someday.
> > > Instead, using JOOQ means I have to rely on its underlying
> > > implementation with no choice.
> > >
> > > You know almost all JPA vendors support different SQL dialects. And
> > fluent
> > > API(or DSL) is not attractive to me. Frankly speaking, IMO, for
> > > insert/update/delete clause the fluent API makes thing unnecessary
> > > complex that you are writing something which is even more
> > > complicated than SQL, for example in JOOQ,
> > >
> > > == persistence ===
> > > create.insertInto(AUTHOR)
> > > .set(AUTHOR.ID, 100)
> > > .set(AUTHOR.FIRST_NAME, "Hermann")
> > > .set(AUTHOR.LAST_NAME, "Hesse")
> > > .newRecord()
> > > .set(AUTHOR.ID, 101)
> > > .set(AUTHOR.FIRST_NAME, "Alfred")
> > > .set(AUTHOR.LAST_NAME, "Döblin");
> > >
> > > In this area, JPA does the right thing.
> > >
> > > Author author = new Author(...);
> > > JPAEntityManary.persist(author);
> > >
> > > The later one is more Java more OOP.
> > >
> > > === single table query ====
> > > Speaking of query, for single table query, fluent API is not
> > > attractive too. For example:
> > >
> > > Raw SQL:
> > > SELECT * FROM BOOK
> > > WHERE BOOK.PUBLISHED_IN = 2011
> > > ORDER BY BOOK.TITLE
> > >
> > > JOOQ:
> > > create.selectFrom(BOOK)
> > > .where(BOOK.PUBLISHED_IN.eq(2011))
> > > .orderBy(BOOK.TITLE)
> > >
> > >
> > > CloudStack search criteria built on JPA:
> > > Qeury<Book> q = new Qeury<Book>();
> > > q.add(Book_.PublishedIn. Op.eq, 2011); q.orderBy(Book_.title);
> > > List<Book> books = q.list();
> > >
> > > The last way is more Java for sure, people who even has no SQL
> > > knowledge can write the search.
> > >
> > > === multiple tables query ===
> > > The only place fluent API shining is querying multiple tables:
> > >
> > > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
> > > FROM AUTHOR
> > > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
> > > WHERE BOOK.LANGUAGE = 'DE'
> > > AND BOOK.PUBLISHED > '2008-01-01'
> > > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
> > > HAVING COUNT(*) > 5
> > > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
> > > LIMIT 2
> > > OFFSET 1
> > > FOR UPDATE
> > >
> > > JOOQ:
> > >
> > > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
> > > .from(AUTHOR)
> > > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
> > > .where(BOOK.LANGUAGE.equal("DE"))
> > > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
> > > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
> > > .having(count().greaterThan(5))
> > > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
> > > .limit(1)
> > > .offset(2)
> > > .forUpdate();
> > >
> > > JPA does very badly in this area. However, you notice you are
> > > actually translating raw SQL into JOOQ DSL. In fact writing such a
> > > query is harder in JOOQ than in raw SQL, because you must not only
> > > know SQL knowledge but also know JOOQ dialects. The only gain is
> > > type-safe. I know Java developers are used to static type too much,
> > > they are not willing to write any code that cannot be checked by
> > > compiler. Type safe is the reason that no ORM is perfect, and it can
> > > never be.
> > >
> > > In summary, I don't think dropping in any ORM can solve problem
> > perfectly,
> > > if I did it, I would do:
> > >
> > > - Using JPA based framework.
> > > - building persistent interface(create/update/delete) and single
> > > table search criteria using JPA
> > > - for multiple tables query, either using JPA JPQL(if you don't mind
> > > type
> > > safe) or dropping in some fluent API framework if you can not stand
> > > for
> > any
> > > type unsafe thing.
> > >
> > >
> > >
> > > > -----Original Message-----
> > > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
> > > > Sent: Tuesday, November 26, 2013 3:58 AM
> > > > To: dev@cloudstack.apache.org
> > > > Subject: Re: persistence layer
> > > >
> > > > 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 <Al...@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
> > >
> >
RE: persistence layer
Posted by Alex Hitchins <cl...@alexhitchins.com>.
Hello Darren,
I'm reading your reply - I shall post comments when I've got through it
completely.
-----Original Message-----
From: Darren Shepherd [mailto:darren.s.shepherd@gmail.com]
Sent: 02 December 2013 17:33
To: dev@cloudstack.apache.org
Subject: Re: persistence layer
Alright, this is long
.
I have to say after the 10+ years of struggling with JDBC, Hibernate,
JbdcTemplate, JPA, QueryDSL, and countless custom solutions, I've have never
found a database access solution that has impressed me as much as jooq. If I
was starting from scratch today I'd definitely choose jooq. Now all of that
doesn't matter though. Evaluating database access solutions in isolation is
about as productive as discussing if c++ or java is a better language. The
answer being "it depends." We really need to ground this discussion in the
realities of what ACS is today, what are the issues and where do we want to
go. Nobody is going to sign on to a complete gut and rewrite of the data
acces layer (DAL), so any solution must allow a slow migration to it.
Current solution
============
ACS currently has a completely custom DAL. While the VOs are annotated with
JPA annotations it is dangerous to compare the custom ACS framework to JPA
as the differences are substantial. It is also important to note that ACS
does not have a ORM today. There is no relational mapping.
The ACS DAL can be broken down to five parts. 1) DAO framework (GenericDao)
2) object mapping 3) query api 4) crud w/ change tracking 5) transaction
management
DAO framework
============
ACS has a strict 1-to-1 mapping of VO to DAO. The generic DAO provides the
common methods like findById that you see in most java DAOs. The generic DAO
uses reflection to query the VOs JPA annotations to build up the metadata
for the crud operations and query building.
Object mapping
===========
The metadata obtained through the GenericDao is used to map sql to the VOs.
If you use the GenericDaos findX or listByX methods or the query api it will
map the result to a VO. Additionally if you update a VO and persist it the
mapper will generate SQL from the VO.
Query API
========
ACS has a search builder api that allows you to build sql queries in a type
safe manner. The functionality is mostly limited to basically conditions and
joins. The api works for most of the use cases in ACS but still there a
quite a lot of raw sql statements in ACS because the api is not functional
enough.
CRUD with changing tracking
=====================
When POJOs are created by the GenericDao they are enhanced with cglib. All
changes to the pojos are tracked as updates. When persist is called the
updated fields are used to construct an update.
Transaction management
===================
Transactions are managed programmatically through the transaction api.
Nested transactions and transaction propagation are not supported.
Isolation level is determined at the DB pool level, so all connections have
the same level which is read committed.
Problems
========
The general problems of the dal in ACS is that it's 100% custom. The APIs,
especially around transactions and querying, are often a stumbling block for
people developing in ACS. Additionally, since this is all custom, the
maintenance and enhancements of the DAL is solely the responsibility of the
ACS community. Finally, very little documentation exists around all of this,
and there is realistically about 3 people who understand the core
implementation.
You have to consider that ACS was started almost 5 years ago. I can
understand the creators not wanting to use hibernate or spring and instead
rolling their own solutions. The problem with rolling your own solution
typically ends up being that your use cases start off simple and your custom
framework does too. As the product matures and more complex use cases are
tackled things are no longer simple. You are then forced to enhance your
simple framework, but then the warts start to really show. Or, you start
bypassing it because it is too limiting.
DAO framework
============
I don't have major fundamental complaints with the DAOs. The API exposed is
fairly consistent with most DAOs you would see in any java application. The
main complaint I have is the strict 1-to-1 mapping between VO and DAO. In
general I don't like that design pattern and prefer to group DAOs according
to function and not tables. That's an opinion and I think the larger java
community tends to disagree with me. So I concede on that point. The bigger
problem with the 1-to-1 model is extensibility. Today, if you are writing a
new plugin and need to execute some query that doesn't exist today, you
really don't have much choice but to change the existing DAO in the core.
This leads to people instead of doing DB logic in a DAO, they end up doing
it in the services classes which is just bad.
Object mapping
===========
The object mapping in ACS is quite simple. Since there is no relational
mapping, it just basically does the equivalent of BeanUtils.setProperty().
There is additional logic for database encryption. One of the down side of
the current mapping is that it just works for VOs. If you want to do some
more complex sql query that returns arbitrary output, you are forced to fall
back to raw JDBC. The Query API, since it builds off of the object mapping,
it also oriented to just a single VO. If you join across tables you are
only joining to restrict the result set of the main table you are querying.
If you are trying to get the values of the parent and child tables at the
same time, that really isnt possible (or at least obvious how to do it).
Query API
========
The query API is very problematic. It is your typical in-house SQL
abstraction in that probably solved the original creators problems and made
sense at the time, but to others it is obscure and difficult to understand.
I honestly don't really know off the top of my head how to use it and just
copy and paste other examples. For example, tell me if it's obvious to you
what the below code is doing.
SearchBuilder<NicVO> nicSearch = _nicDao.createSearchBuilder();
nicSearch.and("networkId", nicSearch.entity().getNetworkId(),
SearchCriteria.Op.EQ);
nicSearch.and().op("ip4Address",
nicSearch.entity().getIp4Address(), SearchCriteria.Op.NNULL);
nicSearch.or("ip6Address", nicSearch.entity().getIp6Address(),
SearchCriteria.Op.NNULL);
nicSearch.cp();
UserVmSearch = createSearchBuilder();
UserVmSearch.and("states", UserVmSearch.entity().getState(),
SearchCriteria.Op.IN);
UserVmSearch.join("nicSearch", nicSearch,
UserVmSearch.entity().getId(), nicSearch.entity().getInstanceId(),
JoinBuilder.JoinType.INNER);
UserVmSearch.done();
I will be quite frank and say that the query API is the number one thing in
ACS that I despise. The reason being that an IaaS orchestration platform is
largely a metadata management platform. It is all about storing metadata and
state and reconcilioning that state with external resources. As such, I
would expect the platform to have a simple and rich API to access data that
establishes good patterns. Instead we have an api that is very limited and
obscures the real query going on under the hood. I also think the nature of
the api has also lead to a lot of bad practices in code. Those being the
scattering of DB logic in the services layer and raw JDBC and SQL strings
embedded in code.
CRUD with changing tracking
=====================
The current change tracking is done with enhancing the VOs with cglib. The
framework basically is what it is. The pattern is used by other frameworks
and I don't have too much complaints.
Transaction management
===================
In 4.3 I already rewrote the transaction API. The previous problems were all
based around the fact that the transaction management is completely
programmatic. Developers had to call start(), commit(), and rollback(). The
patterns of how those APIs were called was very inconsistent and rarely was
rollback done properly. The @DB masked a lot of the underlying issues with
rollback() as it would do an implicit rollback on failure. The @DB
annotation was problematic in that it's meaning was not clearly understood
and the AOP style it demands is not compatible with proxy based AOP like
Spring.
In 4.3 I've change the transaction api to closely match the semantics of
Spring's programmatic transaction API. The @DB annotation has no meaning now
for the management server (AWS and usage still require @DB).
Proposed solutions
==============
In general I do not wish to gut the DAL. The impact would greatly
destabilize ACS. Instead I want to find a way in which new code can be done
better and old code can be slowly refactored if we choose. After looking at
JPA solutions I've come the conclusions that having a JPA solution running
in parallel with the custom DAO stuff would be a nightmare. Much of this
has to do with how ACS and something like hibernate work. I know we can't
use hibernate because of the license, but hibernate is the library I know
best so I will speak in those terms. (As a side note, the fact that we
can't use hibernate also makes me weary of using JPA. If I had to choose
I'd probably go with TopLink, but that just makes me nervous. JPA solutions
are so complex and you're really married to them once you start using them.
It's hard for me to agree to committing to one unless I've had substantial
experience with it. It is also extremely difficult to write a fully JPA
standard app. You usually fallback to using implementation specific APIs or
just plain JDBC.)
In ACS we track changes to VOs with cglib. In JPA, changes are really
tracked in the context of a session (or entitymanager). Once you close the
session or detach the object from a session it loses it's context. Since DB
logic is scattered everywhere is ACS, there is no clear place to start and
close sessions. This is really not completely the fault of ACS design or
anything. JPA is really designed with the idea of container managed
transactions and by container that implies Servlet or EJB. We obviously
have no EJBs and very little logic in ACS happens in the servlet container.
The majority of logic happens in non-container managed code.
Additionally, the VOs annotations are not really compatible with JPA.
There are some subtleties around inheritance that wont work. I cant
remember the details of the issues there. Inheritance in JPA/Hibernate is
just a nightmare anyhow. It does really strange things like creating temp
tables and since MySQL isnt all that fancy of a DB, you run into weird
deadlocks as hibernate is trying to do operations from two tables and then
additionally a temp table. Regardless of the interpretation of the JPA
annotation, the custom field mapping like encryption that has been done also
causes issues.
If nobody believe me what a nightmare it will be to run ACS in parallel with
JPA, just go try it yourself.
I propose we integrate jOOQ. jOOQ will allow us to have a much better API
but not have to gut anything. We can include it to specifically address the
areas of concern. I have already tested it out and we can run it seemless
in parallel with the custom ACS stuff. The custom object mapping ACS does
is completely compatible with jOOQ and the cglib stuff is too.
You can query from jooq and persist with ACS genericdao. From a developer
perspective all that really changes is that instead of using the
SearchBuilder/SearchCriteria API you use the jOOQ fluent API to query. All
of the existing findByX and listBy methods can remain. We will add more
listBy() method that take a jOOQ Condition and not a SearchCriteria.
Heres how I see the benefits of moving to jOOQ.
DAO framework
============
The DAOs will generally stay the same. Since the VO metadata and mapping
needs to be integrated we will pull the logic for that out of the
GenericDaoBase and externalize it so that it can be used in both the context
of the SearchBuilder and also jOOQ. This has the benefit that the strict
1-to-1 mapping of VO to DAO will be removed. Plugins will be free to create
their own DAOs that dont conflict with the core DAOs.
Object mapping
===========
Object mapping will still work as it does today. What we gain is that for
more complex queries that dont map to a single VO (or a VO in general) can
be mapped to any regular POJOs. The mapping framework in jOOQ is very
flexible and powerful, but for most use cases just creating a POJO with the
right field names is sufficient.
Query API
========
The query API is where will see the most benefit from jOOQ. We can
deprecate the SearchBuilder/SearchCriteria and just focus on jOOQ. When
somebody new comes to ACS we can then just point them to the excellent jOOQ
documentation. Additionally, if they already understand SQL, there is very
little to learn. Since jOOQ is so powerful we can also get rid of the all
the views that were created for the API. I really, really do not like the
views that we are creating these days. The problem with views is that every
time we make a modification to the API it requires a DDL change.
Additionally the view definitions are not compatible across databases.
Ive already tried to get ACS running on HSQLDB but I gave up on the views.
The problem with views is just the problem with SQL in general.
Each DB has its nuances with SQL. You need to ensure that the SQL in the
views is completely cross DB compatible (which is a pain). Also note that
jOOQ handles those SQL nuances so we dont need to the be SQL-92 experts.
CRUD with changing tracking
=====================
This largely stays as is. We will continue with the ACS cglib approach.
We will not use jOOQ records that do change tracking themselves. From
jOOQs perspective we will be using just POJOs. Those POJOs will be mapped
with our custom mapper and as such we will add the necessary cglib
enhancers.
Transaction management
===================
Ive already in the past propose that we move to Spring transaction
management. jOOQ is fully compatible with Spring TX. Even if we dont use
Spring TX, jOOQ is compatible with ACSs current TX approach. (As a side
note, the discussion of Spring Data is moot. Ive already somewhat
discussed the idea that we will leverage Spring core IoC and Spring TX from
Spring and nothing else. I do not wish to use Spring Data. There is not
enough value to tie us specifically to that framework. Spring APIs are a
can of worms. Once you starting higher level frameworks like Spring MVC or
Spring Data you suddenly bind you app to a massive framework. Today if we
didnt like Spring anymore, moving to something like Guice would be a small
effort. Once you starting using Spring Data, for example, that ability goes
away. Also, magic RDBMS/NoSQL abstraction frameworks are useless in my
mind. You need to know if you are using a RDBMS or NoSQL. As a separate
thread, I do think we should move the usage data to NoSQL using Apache Gora
maybe. Gora is intending to use jOOQ under the hood too, so we can still
persist usage to a RDBMS if somebody doesnt want a big data
solution.)
Okay, if you actually made it to this point without falling asleep, I hope
you see that I just want to make relatively simple enhancements to the
current DAL in ACS and leverage the power of jOOQ to do that.
Darren
On Thu, Nov 28, 2013 at 4:45 AM, Lukas Eder <lu...@gmail.com> wrote:
> Hi Frank,
>
> Thank you for your detailed feedback. This is very interesting insight
> for me and for Data Geekery. Let me try to address your concerns by
> first categorising them into the following items (please correct me if
> I might have misunderstood).
>
> - A concern about Spring support.
> - A concern about standards (JPA)
> - A concern about CRUD verbosity
> - A concern about fluent APIs vs. SQL
> - A concern about fluent APIs vs. custom query building APIs
>
> Spring support
> ===========
> It is true that we cannot offer you a "formal" affiliation with Spring
> (or rather with Oliver Gierke, the lead developer behind Spring Data).
> I have been observing this collaboration between Spring Data and
> QueryDSL and I feel that it is a strategic alliance that mutually benefits
both vendors.
> QueryDSL gets some more traction by being auto-shipped to a much
> broader audience, whereas Spring Data gets a fluent API for both JPA and
SQL.
>
> From our perspective, we want jOOQ to be entirely independent of any
> transaction and connection pooling API as this facilitates integration
> with JTA transaction modelling and other models. We believe that by
> keeping things simple and by separating concerns, you will get a stack
> that is better focused on your individual needs. In that way, we
> believe that you should be able to combine Spring IOC with jOOQ just
> as much as with JEE components.
>
> Most importantly, Spring Data, much like JDO in the past and like
> QueryDSL today aim for integrating NoSQL databases into a unified API,
> which we find a poor fit, as transaction models, data models, query
> models are fundamentally different between these various data stores.
> We believe that by pursuing such unification, frameworks are easily
> distracted from sound SQL support. We highly value Spring's
> transaction model simplification and this has worked well with jOOQ in the
past.
>
> Standards (JPA)
> ============
> Now, one of your arguments in favour of JPA is the non-lock-in. Spring
> has been challenging JEE standards for quite some time and there is no
> other vendor in the market to replace Spring once you build upon
> Spring. Even more so, by choosing a Spring+QueryDSL combination, you
> get an equally strong vendor-lockin (or even a stronger one, as the
> stack gets more
> complex) than when choosing Spring+jOOQ. If standards are important to
> you, I suggest you only use JPA and Criteria Query (Note, that JEE
> also supports IoC). And I'm sure that Adam Bien will agree with me on
> this :-) But even then, you will have a hard time migrating from
> Hibernate to EclipseLink or OpenJPA as the implementations are subtly
> different in many ways.
>
> The decision is up to you, and I fully understand these concerns.
> Standards are a good thing because many disparate things from various
> vendors have been unified and hopefully matured over time. But vendors
> also innovate and thus deviate from the standard again. Without such
> deviation, there would be no more progress, nor innovation in a
> standard. Take EclipseLink for instance, a JPA implementation that has
> started to "flirt" with NoSQL
> support:
> http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
>
> The above is orthogonal to JPA and will add lots of complexity to this
> particular implementation. JDO was another standard that has attempted
> to integrate other, non-relational data stores in a single API, yet it
> has not succeeded, leaving a pretty much dead standard.
>
> Now, while you are absolutely right by saying that you cannot switch
> the implementation of the jOOQ API at the moment, we're striving to
> innovate on the standards front as we see room for various JSRs in the
> area of RDBMS meta modelling (beyond JDBC's DatabaseMetaData),
> internal DSL modelling in general, SQL DSL modelling in particular. We
> believe that jOOQ is the most innovative platform for SQL to Java
> integration in the market at the moment
>
> CRUD verbosity
> ===========
> > In this area, JPA does the right thing.
> >
> > Author author = new Author(...);
> > JPAEntityManary.persist(author);
> >
> > The later one is more Java more OOP.
>
> Compare this to jOOQ's CRUD features:
> AuthorRecord author = ctx.newRecord(AUTHOR); author.store();
>
> Or alternatively:
> AuthorDao dao = ...
> dao.insert(new Author());
>
> Relevant sections in the manual are:
> -
>
> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablere
> cords/
> -
>
> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablere
> cords/simple-crud/
> - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
>
> Fluent APIs vs. SQL
> ===============
> I fully understand this concern. Using the jOOQ fluent API is not the
> same as writing SQL directly. But let me remind you of the concerns
> you are having with respect to database independence. By writing SQL
> directly (through JDBC, through views or through stored procedures)
> you will not be able to support dozens of databases very easily. Both
> JPA and jOOQ solve this problem quite well. JPA abstracts SQL
> entirely, while jOOQ abstracts the subtle differences, only.
>
> Fluent APIs vs. custom query building APIs
> ================================ In addition to jOOQ's fluent API,
> there is also a simpler, object-oriented SelectQuery model:
> http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
>
> The same applies for other types of statements (InsertQuery,
> UpdateQuery, etc.). Thus, compare this fluent API usage:
>
> > create.selectFrom(BOOK)
> > .where(BOOK.PUBLISHED_IN.eq(2011))
> > .orderBy(BOOK.TITLE)
>
> With this SelectQuery usage:
>
> SelectQuery<Book> select = create.selectQuery(BOOK);
> select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
> select.addOrderBy(BOOK.TITLE);
> Result<Book> result = select.fetch();
>
> This is also documented here:
>
> http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and
> -non-dsl/
>
> Conclusion
> ========
> I hope I could put jOOQ in a different light to you. If JPA is
> sufficient for your use-cases, then use JPA (or JPA+jOOQ). There are
> some problems that jOOQ will never solve, which are solved very well
> in JPA. We have outlined these differences here:
> http://www.hibernate-alternative.com
>
> However, by using jOOQ, you not only gain a very high level of
> typesafety and thus an increase of productivity through the usage of
> jOOQ's code generator which helps you detect errors early, you also
> get access to a very rich set of service provider interfaces, which
> were off-topic in this discussion so far. Some examples:
>
> ExecuteListeners for custom query execution lifecycle handling:
> http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
>
> Schema and table mapping for multi-tenancy support:
>
> http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-sc
> hema-mapping/
>
> Record mapping in order to provide custom mapping algorithms from jOOQ
> records to your domain model:
>
> http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-r
> ecordmapper-provider/
>
> A query transformation listener that is still being worked on,
> allowing for arbitrary SQL transformation on the jOOQ AST. This can be
> very useful for features like row-level security.
>
> As I said, we believe that there is currently no other software in the
> Java market that takes your SQL so seriously, and we're highly
> committed to improve this even further in the near future.
>
> Best Regards,
> Lukas
>
> 2013/11/27 Frank Zhang <Fr...@citrix.com>
>
> > Hi Lukas:
> > Glad to see developer behind JOOQ here, I originally
> > recommended using QueryDSL instead of JOOQ, there are some of my
> > concerns:
> >
> > - Spring support. You know CloudStack is based on Spring IOC, the
> > most compelling thing of Spring DB is its transaction management
> > framework. QueryDSL has been integrated into Spring for two years.
> > But there is only a simple page (
> >
> http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with
> -spring/
> )
> > demonstrating JOOQ and Spring, however, the code just shows how to
> > load JOOQ in IOC, it doesn't show any advanced feature of Spring DB
> > for example declarative transaction management. If JOOQ is not
> > compatible with Spring DB, then we loss a widely used feature.
> >
> > - It's not JPA based on. This might be an argument that JOOQ may
> > claim as its biggest advantage. I agree JPA does have some bad
> > thing(e.g. criteria API), but the rest part is doing very well.
> > The most important thing here is JPA is just API so I can choose
> > underlying vendor by my preference. This non-lock-in feature is very
> > appealing. For example, Hibernate has been proven by industry for 10
> > years, I can fairly trust its quality. And vendor may have some
> > advanced feature like secondary cache I may need someday.
> > Instead, using JOOQ means I have to rely on its underlying
> > implementation with no choice.
> >
> > You know almost all JPA vendors support different SQL dialects. And
> fluent
> > API(or DSL) is not attractive to me. Frankly speaking, IMO, for
> > insert/update/delete clause the fluent API makes thing unnecessary
> > complex that you are writing something which is even more
> > complicated than SQL, for example in JOOQ,
> >
> > == persistence ===
> > create.insertInto(AUTHOR)
> > .set(AUTHOR.ID, 100)
> > .set(AUTHOR.FIRST_NAME, "Hermann")
> > .set(AUTHOR.LAST_NAME, "Hesse")
> > .newRecord()
> > .set(AUTHOR.ID, 101)
> > .set(AUTHOR.FIRST_NAME, "Alfred")
> > .set(AUTHOR.LAST_NAME, "Döblin");
> >
> > In this area, JPA does the right thing.
> >
> > Author author = new Author(...);
> > JPAEntityManary.persist(author);
> >
> > The later one is more Java more OOP.
> >
> > === single table query ====
> > Speaking of query, for single table query, fluent API is not
> > attractive too. For example:
> >
> > Raw SQL:
> > SELECT * FROM BOOK
> > WHERE BOOK.PUBLISHED_IN = 2011
> > ORDER BY BOOK.TITLE
> >
> > JOOQ:
> > create.selectFrom(BOOK)
> > .where(BOOK.PUBLISHED_IN.eq(2011))
> > .orderBy(BOOK.TITLE)
> >
> >
> > CloudStack search criteria built on JPA:
> > Qeury<Book> q = new Qeury<Book>();
> > q.add(Book_.PublishedIn. Op.eq, 2011); q.orderBy(Book_.title);
> > List<Book> books = q.list();
> >
> > The last way is more Java for sure, people who even has no SQL
> > knowledge can write the search.
> >
> > === multiple tables query ===
> > The only place fluent API shining is querying multiple tables:
> >
> > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
> > FROM AUTHOR
> > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
> > WHERE BOOK.LANGUAGE = 'DE'
> > AND BOOK.PUBLISHED > '2008-01-01'
> > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
> > HAVING COUNT(*) > 5
> > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
> > LIMIT 2
> > OFFSET 1
> > FOR UPDATE
> >
> > JOOQ:
> >
> > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
> > .from(AUTHOR)
> > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
> > .where(BOOK.LANGUAGE.equal("DE"))
> > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
> > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
> > .having(count().greaterThan(5))
> > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
> > .limit(1)
> > .offset(2)
> > .forUpdate();
> >
> > JPA does very badly in this area. However, you notice you are
> > actually translating raw SQL into JOOQ DSL. In fact writing such a
> > query is harder in JOOQ than in raw SQL, because you must not only
> > know SQL knowledge but also know JOOQ dialects. The only gain is
> > type-safe. I know Java developers are used to static type too much,
> > they are not willing to write any code that cannot be checked by
> > compiler. Type safe is the reason that no ORM is perfect, and it can
> > never be.
> >
> > In summary, I don't think dropping in any ORM can solve problem
> perfectly,
> > if I did it, I would do:
> >
> > - Using JPA based framework.
> > - building persistent interface(create/update/delete) and single
> > table search criteria using JPA
> > - for multiple tables query, either using JPA JPQL(if you don't mind
> > type
> > safe) or dropping in some fluent API framework if you can not stand
> > for
> any
> > type unsafe thing.
> >
> >
> >
> > > -----Original Message-----
> > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
> > > Sent: Tuesday, November 26, 2013 3:58 AM
> > > To: dev@cloudstack.apache.org
> > > Subject: Re: persistence layer
> > >
> > > 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 <Al...@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
> >
>
Re: persistence layer
Posted by Darren Shepherd <da...@gmail.com>.
Alright, this is long….
I have to say after the 10+ years of struggling with JDBC, Hibernate,
JbdcTemplate, JPA, QueryDSL, and countless custom solutions, I've have
never found a database access solution that has impressed me as much as
jooq. If I was starting from scratch today I'd definitely choose jooq. Now
all of that doesn't matter though. Evaluating database access solutions in
isolation is about as productive as discussing if c++ or java is a better
language. The answer being "it depends." We really need to ground this
discussion in the realities of what ACS is today, what are the issues and
where do we want to go. Nobody is going to sign on to a complete gut and
rewrite of the data acces layer (DAL), so any solution must allow a slow
migration to it.
Current solution
============
ACS currently has a completely custom DAL. While the VOs are annotated with
JPA annotations it is dangerous to compare the custom ACS framework to JPA
as the differences are substantial. It is also important to note that ACS
does not have a ORM today. There is no relational mapping.
The ACS DAL can be broken down to five parts. 1) DAO framework (GenericDao)
2) object mapping 3) query api 4) crud w/ change tracking 5) transaction
management
DAO framework
============
ACS has a strict 1-to-1 mapping of VO to DAO. The generic DAO provides the
common methods like findById that you see in most java DAOs. The generic
DAO uses reflection to query the VOs’ JPA annotations to build up the
metadata for the crud operations and query building.
Object mapping
===========
The metadata obtained through the GenericDao is used to map sql to the VOs.
If you use the GenericDaos findX or listByX methods or the query api it
will map the result to a VO. Additionally if you update a VO and persist it
the mapper will generate SQL from the VO.
Query API
========
ACS has a search builder api that allows you to build sql queries in a type
safe manner. The functionality is mostly limited to basically conditions
and joins. The api works for most of the use cases in ACS but still there a
quite a lot of raw sql statements in ACS because the api is not functional
enough.
CRUD with changing tracking
=====================
When POJOs are created by the GenericDao they are enhanced with cglib. All
changes to the pojos are tracked as updates. When persist is called the
updated fields are used to construct an update.
Transaction management
===================
Transactions are managed programmatically through the transaction api.
Nested transactions and transaction propagation are not supported.
Isolation level is determined at the DB pool level, so all connections have
the same level which is read committed.
Problems
========
The general problems of the dal in ACS is that it's 100% custom. The APIs,
especially around transactions and querying, are often a stumbling block
for people developing in ACS. Additionally, since this is all custom, the
maintenance and enhancements of the DAL is solely the responsibility of the
ACS community. Finally, very little documentation exists around all of
this, and there is realistically about 3 people who understand the core
implementation.
You have to consider that ACS was started almost 5 years ago. I can
understand the creators not wanting to use hibernate or spring and instead
rolling their own solutions. The problem with rolling your own solution
typically ends up being that your use cases start off simple and your
custom framework does too. As the product matures and more complex use
cases are tackled things are no longer simple. You are then forced to
enhance your simple framework, but then the warts start to really show. Or,
you start bypassing it because it is too limiting.
DAO framework
============
I don't have major fundamental complaints with the DAOs. The API exposed is
fairly consistent with most DAOs you would see in any java application. The
main complaint I have is the strict 1-to-1 mapping between VO and DAO. In
general I don't like that design pattern and prefer to group DAOs according
to function and not tables. That's an opinion and I think the larger java
community tends to disagree with me. So I concede on that point. The bigger
problem with the 1-to-1 model is extensibility. Today, if you are writing a
new plugin and need to execute some query that doesn't exist today, you
really don't have much choice but to change the existing DAO in the core.
This leads to people instead of doing DB logic in a DAO, they end up doing
it in the services classes which is just bad.
Object mapping
===========
The object mapping in ACS is quite simple. Since there is no relational
mapping, it just basically does the equivalent of BeanUtils.setProperty().
There is additional logic for database encryption. One of the down side of
the current mapping is that it just works for VOs. If you want to do some
more complex sql query that returns arbitrary output, you are forced to
fall back to raw JDBC. The Query API, since it builds off of the object
mapping, it also oriented to just a single VO. If you join across tables
you are only joining to restrict the result set of the main table you are
querying. If you are trying to get the values of the parent and child
tables at the same time, that really isn’t possible (or at least obvious
how to do it).
Query API
========
The query API is very problematic. It is your typical in-house SQL
abstraction in that probably solved the original creators problems and made
sense at the time, but to others it is obscure and difficult to understand.
I honestly don't really know off the top of my head how to use it and just
copy and paste other examples. For example, tell me if it's obvious to you
what the below code is doing.
SearchBuilder<NicVO> nicSearch = _nicDao.createSearchBuilder();
nicSearch.and("networkId", nicSearch.entity().getNetworkId(),
SearchCriteria.Op.EQ);
nicSearch.and().op("ip4Address",
nicSearch.entity().getIp4Address(), SearchCriteria.Op.NNULL);
nicSearch.or("ip6Address", nicSearch.entity().getIp6Address(),
SearchCriteria.Op.NNULL);
nicSearch.cp();
UserVmSearch = createSearchBuilder();
UserVmSearch.and("states", UserVmSearch.entity().getState(),
SearchCriteria.Op.IN);
UserVmSearch.join("nicSearch", nicSearch,
UserVmSearch.entity().getId(), nicSearch.entity().getInstanceId(),
JoinBuilder.JoinType.INNER);
UserVmSearch.done();
I will be quite frank and say that the query API is the number one thing in
ACS that I despise. The reason being that an IaaS orchestration platform is
largely a metadata management platform. It is all about storing metadata
and state and reconcilioning that state with external resources. As such, I
would expect the platform to have a simple and rich API to access data that
establishes good patterns. Instead we have an api that is very limited and
obscures the real query going on under the hood. I also think the nature of
the api has also lead to a lot of bad practices in code. Those being the
scattering of DB logic in the services layer and raw JDBC and SQL strings
embedded in code.
CRUD with changing tracking
=====================
The current change tracking is done with enhancing the VOs with cglib. The
framework basically is what it is. The pattern is used by other frameworks
and I don't have too much complaints.
Transaction management
===================
In 4.3 I already rewrote the transaction API. The previous problems were
all based around the fact that the transaction management is completely
programmatic. Developers had to call start(), commit(), and rollback(). The
patterns of how those APIs were called was very inconsistent and rarely was
rollback done properly. The @DB masked a lot of the underlying issues with
rollback() as it would do an implicit rollback on failure. The @DB
annotation was problematic in that it's meaning was not clearly understood
and the AOP style it demands is not compatible with proxy based AOP like
Spring.
In 4.3 I've change the transaction api to closely match the semantics of
Spring's programmatic transaction API. The @DB annotation has no meaning
now for the management server (AWS and usage still require @DB).
Proposed solutions
==============
In general I do not wish to gut the DAL. The impact would greatly
destabilize ACS. Instead I want to find a way in which new code can be
done better and old code can be slowly refactored if we choose. After
looking at JPA solutions I've come the conclusions that having a JPA
solution running in parallel with the custom DAO stuff would be a
nightmare. Much of this has to do with how ACS and something like
hibernate work. I know we can't use hibernate because of the license, but
hibernate is the library I know best so I will speak in those terms. (As a
side note, the fact that we can't use hibernate also makes me weary of
using JPA. If I had to choose I'd probably go with TopLink, but that just
makes me nervous. JPA solutions are so complex and you're really married
to them once you start using them. It's hard for me to agree to committing
to one unless I've had substantial experience with it. It is also
extremely difficult to write a fully JPA standard app. You usually
fallback to using implementation specific APIs or just plain JDBC.)
In ACS we track changes to VOs with cglib. In JPA, changes are really
tracked in the context of a session (or entitymanager). Once you close the
session or detach the object from a session it loses it's context. Since
DB logic is scattered everywhere is ACS, there is no clear place to start
and close sessions. This is really not completely the fault of ACS design
or anything. JPA is really designed with the idea of container managed
transactions and by container that implies Servlet or EJB. We obviously
have no EJBs and very little logic in ACS happens in the servlet
container. The majority of logic happens in non-container managed code.
Additionally, the VOs annotations are not really compatible with JPA.
There are some subtleties around inheritance that won’t work. I can’t
remember the details of the issues there. Inheritance in JPA/Hibernate is
just a nightmare anyhow. It does really strange things like creating temp
tables and since MySQL isn’t all that fancy of a DB, you run into weird
deadlocks as hibernate is trying to do operations from two tables and then
additionally a temp table. Regardless of the interpretation of the JPA
annotation, the custom field mapping like encryption that has been done
also causes issues.
If nobody believe me what a nightmare it will be to run ACS in parallel
with JPA, just go try it yourself.
I propose we integrate jOOQ. jOOQ will allow us to have a much better API
but not have to gut anything. We can include it to specifically address
the areas of concern. I have already tested it out and we can run it
seemless in parallel with the custom ACS stuff. The custom object mapping
ACS does is completely compatible with jOOQ and the cglib stuff is too.
You can query from jooq and persist with ACS genericdao. From a developer
perspective all that really changes is that instead of using the
SearchBuilder/SearchCriteria API you use the jOOQ fluent API to query. All
of the existing findByX and listBy methods can remain. We will add more
listBy() method that take a jOOQ Condition and not a SearchCriteria.
Here’s how I see the benefits of moving to jOOQ.
DAO framework
============
The DAOs will generally stay the same. Since the VO metadata and mapping
needs to be integrated we will pull the logic for that out of the
GenericDaoBase and externalize it so that it can be used in both the
context of the SearchBuilder and also jOOQ. This has the benefit that the
strict 1-to-1 mapping of VO to DAO will be removed. Plugins will be free
to create their own DAOs that don’t conflict with the core DAOs.
Object mapping
===========
Object mapping will still work as it does today. What we gain is that for
more complex queries that don’t map to a single VO (or a VO in general) can
be mapped to any regular POJOs. The mapping framework in jOOQ is very
flexible and powerful, but for most use cases just creating a POJO with the
right field names is sufficient.
Query API
========
The query API is where will see the most benefit from jOOQ. We can
deprecate the SearchBuilder/SearchCriteria and just focus on jOOQ. When
somebody new comes to ACS we can then just point them to the excellent jOOQ
documentation. Additionally, if they already understand SQL, there is very
little to learn. Since jOOQ is so powerful we can also get rid of the all
the views that were created for the API. I really, really do not like the
views that we are creating these days. The problem with views is that
every time we make a modification to the API it requires a DDL change.
Additionally the view definitions are not compatible across databases.
I’ve already tried to get ACS running on HSQLDB but I gave up on the
views. The problem with views is just the problem with SQL in general.
Each DB has its nuances with SQL. You need to ensure that the SQL in the
views is completely cross DB compatible (which is a pain). Also note that
jOOQ handles those SQL nuances so we don’t need to the be SQL-92 experts.
CRUD with changing tracking
=====================
This largely stays as is. We will continue with the ACS cglib approach.
We will not use jOOQ records that do change tracking themselves. From
jOOQ’s perspective we will be using just POJOs. Those POJOs will be mapped
with our custom mapper and as such we will add the necessary cglib
enhancers.
Transaction management
===================
I’ve already in the past propose that we move to Spring transaction
management. jOOQ is fully compatible with Spring TX. Even if we don’t use
Spring TX, jOOQ is compatible with ACS’s current TX approach. (As a side
note, the discussion of Spring Data is moot. I’ve already somewhat
discussed the idea that we will leverage Spring core IoC and Spring TX from
Spring and nothing else. I do not wish to use Spring Data. There is not
enough value to tie us specifically to that framework. Spring APIs are a
can of worms. Once you starting higher level frameworks like Spring MVC or
Spring Data you suddenly bind you app to a massive framework. Today if we
didn’t like Spring anymore, moving to something like Guice would be a small
effort. Once you starting using Spring Data, for example, that ability
goes away. Also, magic RDBMS/NoSQL abstraction frameworks are useless in
my mind. You need to know if you are using a RDBMS or NoSQL. As a
separate thread, I do think we should move the usage data to NoSQL using
Apache Gora maybe. Gora is intending to use jOOQ under the hood too, so we
can still persist usage to a RDBMS if somebody doesn’t want a big data
solution.)
Okay, if you actually made it to this point without falling asleep, I hope
you see that I just want to make relatively simple enhancements to the
current DAL in ACS and leverage the power of jOOQ to do that.
Darren
On Thu, Nov 28, 2013 at 4:45 AM, Lukas Eder <lu...@gmail.com> wrote:
> Hi Frank,
>
> Thank you for your detailed feedback. This is very interesting insight for
> me and for Data Geekery. Let me try to address your concerns by first
> categorising them into the following items (please correct me if I might
> have misunderstood).
>
> - A concern about Spring support.
> - A concern about standards (JPA)
> - A concern about CRUD verbosity
> - A concern about fluent APIs vs. SQL
> - A concern about fluent APIs vs. custom query building APIs
>
> Spring support
> ===========
> It is true that we cannot offer you a "formal" affiliation with Spring (or
> rather with Oliver Gierke, the lead developer behind Spring Data). I have
> been observing this collaboration between Spring Data and QueryDSL and I
> feel that it is a strategic alliance that mutually benefits both vendors.
> QueryDSL gets some more traction by being auto-shipped to a much broader
> audience, whereas Spring Data gets a fluent API for both JPA and SQL.
>
> From our perspective, we want jOOQ to be entirely independent of any
> transaction and connection pooling API as this facilitates integration with
> JTA transaction modelling and other models. We believe that by keeping
> things simple and by separating concerns, you will get a stack that is
> better focused on your individual needs. In that way, we believe that you
> should be able to combine Spring IOC with jOOQ just as much as with JEE
> components.
>
> Most importantly, Spring Data, much like JDO in the past and like QueryDSL
> today aim for integrating NoSQL databases into a unified API, which we find
> a poor fit, as transaction models, data models, query models are
> fundamentally different between these various data stores. We believe that
> by pursuing such unification, frameworks are easily distracted from sound
> SQL support. We highly value Spring's transaction model simplification and
> this has worked well with jOOQ in the past.
>
> Standards (JPA)
> ============
> Now, one of your arguments in favour of JPA is the non-lock-in. Spring has
> been challenging JEE standards for quite some time and there is no other
> vendor in the market to replace Spring once you build upon Spring. Even
> more so, by choosing a Spring+QueryDSL combination, you get an equally
> strong vendor-lockin (or even a stronger one, as the stack gets more
> complex) than when choosing Spring+jOOQ. If standards are important to you,
> I suggest you only use JPA and Criteria Query (Note, that JEE also supports
> IoC). And I'm sure that Adam Bien will agree with me on this :-)
> But even then, you will have a hard time migrating from Hibernate to
> EclipseLink or OpenJPA as the implementations are subtly different in many
> ways.
>
> The decision is up to you, and I fully understand these concerns. Standards
> are a good thing because many disparate things from various vendors have
> been unified and hopefully matured over time. But vendors also innovate and
> thus deviate from the standard again. Without such deviation, there would
> be no more progress, nor innovation in a standard. Take EclipseLink for
> instance, a JPA implementation that has started to "flirt" with NoSQL
> support:
> http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
>
> The above is orthogonal to JPA and will add lots of complexity to this
> particular implementation. JDO was another standard that has attempted to
> integrate other, non-relational data stores in a single API, yet it has not
> succeeded, leaving a pretty much dead standard.
>
> Now, while you are absolutely right by saying that you cannot switch the
> implementation of the jOOQ API at the moment, we're striving to innovate on
> the standards front as we see room for various JSRs in the area of RDBMS
> meta modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling in
> general, SQL DSL modelling in particular. We believe that jOOQ is the most
> innovative platform for SQL to Java integration in the market at the moment
>
> CRUD verbosity
> ===========
> > In this area, JPA does the right thing.
> >
> > Author author = new Author(...);
> > JPAEntityManary.persist(author);
> >
> > The later one is more Java more OOP.
>
> Compare this to jOOQ's CRUD features:
> AuthorRecord author = ctx.newRecord(AUTHOR);
> author.store();
>
> Or alternatively:
> AuthorDao dao = ...
> dao.insert(new Author());
>
> Relevant sections in the manual are:
> -
>
> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablerecords/
> -
>
> http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablerecords/simple-crud/
> - http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
>
> Fluent APIs vs. SQL
> ===============
> I fully understand this concern. Using the jOOQ fluent API is not the same
> as writing SQL directly. But let me remind you of the concerns you are
> having with respect to database independence. By writing SQL directly
> (through JDBC, through views or through stored procedures) you will not be
> able to support dozens of databases very easily. Both JPA and jOOQ solve
> this problem quite well. JPA abstracts SQL entirely, while jOOQ abstracts
> the subtle differences, only.
>
> Fluent APIs vs. custom query building APIs
> ================================
> In addition to jOOQ's fluent API, there is also a simpler, object-oriented
> SelectQuery model:
> http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
>
> The same applies for other types of statements (InsertQuery, UpdateQuery,
> etc.). Thus, compare this fluent API usage:
>
> > create.selectFrom(BOOK)
> > .where(BOOK.PUBLISHED_IN.eq(2011))
> > .orderBy(BOOK.TITLE)
>
> With this SelectQuery usage:
>
> SelectQuery<Book> select = create.selectQuery(BOOK);
> select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
> select.addOrderBy(BOOK.TITLE);
> Result<Book> result = select.fetch();
>
> This is also documented here:
>
> http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and-non-dsl/
>
> Conclusion
> ========
> I hope I could put jOOQ in a different light to you. If JPA is sufficient
> for your use-cases, then use JPA (or JPA+jOOQ). There are some problems
> that jOOQ will never solve, which are solved very well in JPA. We have
> outlined these differences here:
> http://www.hibernate-alternative.com
>
> However, by using jOOQ, you not only gain a very high level of typesafety
> and thus an increase of productivity through the usage of jOOQ's code
> generator which helps you detect errors early, you also get access to a
> very rich set of service provider interfaces, which were off-topic in this
> discussion so far. Some examples:
>
> ExecuteListeners for custom query execution lifecycle handling:
> http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
>
> Schema and table mapping for multi-tenancy support:
>
> http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-schema-mapping/
>
> Record mapping in order to provide custom mapping algorithms from jOOQ
> records to your domain model:
>
> http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-recordmapper-provider/
>
> A query transformation listener that is still being worked on, allowing for
> arbitrary SQL transformation on the jOOQ AST. This can be very useful for
> features like row-level security.
>
> As I said, we believe that there is currently no other software in the Java
> market that takes your SQL so seriously, and we're highly committed to
> improve this even further in the near future.
>
> Best Regards,
> Lukas
>
> 2013/11/27 Frank Zhang <Fr...@citrix.com>
>
> > Hi Lukas:
> > Glad to see developer behind JOOQ here, I originally recommended
> > using QueryDSL instead of JOOQ,
> > there are some of my concerns:
> >
> > - Spring support. You know CloudStack is based on Spring IOC, the most
> > compelling thing of Spring DB is its transaction
> > management framework. QueryDSL has been integrated into Spring for two
> > years. But there is only a simple page
> > (
> >
> http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with-spring/
> )
> > demonstrating JOOQ and Spring, however,
> > the code just shows how to load JOOQ in IOC, it doesn't show any advanced
> > feature of Spring DB for example declarative
> > transaction management. If JOOQ is not compatible with Spring DB, then we
> > loss a widely used feature.
> >
> > - It's not JPA based on. This might be an argument that JOOQ may claim as
> > its biggest advantage. I agree JPA does have
> > some bad thing(e.g. criteria API), but the rest part is doing very well.
> > The most important thing here is JPA is just API so I can choose
> > underlying vendor by my preference. This non-lock-in feature is very
> > appealing. For example, Hibernate has been proven by industry
> > for 10 years, I can fairly trust its quality. And vendor may have some
> > advanced feature like secondary cache I may need someday.
> > Instead, using JOOQ means I have to rely on its underlying implementation
> > with no choice.
> >
> > You know almost all JPA vendors support different SQL dialects. And
> fluent
> > API(or DSL) is not attractive to me. Frankly speaking,
> > IMO, for insert/update/delete clause the fluent API makes thing
> > unnecessary complex that you are writing something which is even
> > more complicated than SQL, for example in JOOQ,
> >
> > == persistence ===
> > create.insertInto(AUTHOR)
> > .set(AUTHOR.ID, 100)
> > .set(AUTHOR.FIRST_NAME, "Hermann")
> > .set(AUTHOR.LAST_NAME, "Hesse")
> > .newRecord()
> > .set(AUTHOR.ID, 101)
> > .set(AUTHOR.FIRST_NAME, "Alfred")
> > .set(AUTHOR.LAST_NAME, "Döblin");
> >
> > In this area, JPA does the right thing.
> >
> > Author author = new Author(...);
> > JPAEntityManary.persist(author);
> >
> > The later one is more Java more OOP.
> >
> > === single table query ====
> > Speaking of query, for single table query, fluent API is not attractive
> > too. For example:
> >
> > Raw SQL:
> > SELECT * FROM BOOK
> > WHERE BOOK.PUBLISHED_IN = 2011
> > ORDER BY BOOK.TITLE
> >
> > JOOQ:
> > create.selectFrom(BOOK)
> > .where(BOOK.PUBLISHED_IN.eq(2011))
> > .orderBy(BOOK.TITLE)
> >
> >
> > CloudStack search criteria built on JPA:
> > Qeury<Book> q = new Qeury<Book>();
> > q.add(Book_.PublishedIn. Op.eq, 2011);
> > q.orderBy(Book_.title);
> > List<Book> books = q.list();
> >
> > The last way is more Java for sure, people who even has no SQL knowledge
> > can write the search.
> >
> > === multiple tables query ===
> > The only place fluent API shining is querying multiple tables:
> >
> > SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
> > FROM AUTHOR
> > JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
> > WHERE BOOK.LANGUAGE = 'DE'
> > AND BOOK.PUBLISHED > '2008-01-01'
> > GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
> > HAVING COUNT(*) > 5
> > ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
> > LIMIT 2
> > OFFSET 1
> > FOR UPDATE
> >
> > JOOQ:
> >
> > create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
> > .from(AUTHOR)
> > .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
> > .where(BOOK.LANGUAGE.equal("DE"))
> > .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
> > .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
> > .having(count().greaterThan(5))
> > .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
> > .limit(1)
> > .offset(2)
> > .forUpdate();
> >
> > JPA does very badly in this area. However, you notice you are actually
> > translating raw SQL into JOOQ DSL. In fact
> > writing such a query is harder in JOOQ than in raw SQL, because you must
> > not only know SQL knowledge but also know
> > JOOQ dialects. The only gain is type-safe. I know Java developers are
> > used to static type too much, they are not willing
> > to write any code that cannot be checked by compiler. Type safe is the
> > reason that no ORM is perfect, and it can never be.
> >
> > In summary, I don't think dropping in any ORM can solve problem
> perfectly,
> > if I did it, I would do:
> >
> > - Using JPA based framework.
> > - building persistent interface(create/update/delete) and single table
> > search criteria using JPA
> > - for multiple tables query, either using JPA JPQL(if you don't mind type
> > safe) or dropping in some fluent API framework if you can not stand for
> any
> > type unsafe thing.
> >
> >
> >
> > > -----Original Message-----
> > > From: Lukas Eder [mailto:lukas.eder@gmail.com]
> > > Sent: Tuesday, November 26, 2013 3:58 AM
> > > To: dev@cloudstack.apache.org
> > > Subject: Re: persistence layer
> > >
> > > 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 <Al...@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
> >
>
Re: persistence layer
Posted by Lukas Eder <lu...@gmail.com>.
Hi Frank,
Thank you for your detailed feedback. This is very interesting insight for
me and for Data Geekery. Let me try to address your concerns by first
categorising them into the following items (please correct me if I might
have misunderstood).
- A concern about Spring support.
- A concern about standards (JPA)
- A concern about CRUD verbosity
- A concern about fluent APIs vs. SQL
- A concern about fluent APIs vs. custom query building APIs
Spring support
===========
It is true that we cannot offer you a "formal" affiliation with Spring (or
rather with Oliver Gierke, the lead developer behind Spring Data). I have
been observing this collaboration between Spring Data and QueryDSL and I
feel that it is a strategic alliance that mutually benefits both vendors.
QueryDSL gets some more traction by being auto-shipped to a much broader
audience, whereas Spring Data gets a fluent API for both JPA and SQL.
>From our perspective, we want jOOQ to be entirely independent of any
transaction and connection pooling API as this facilitates integration with
JTA transaction modelling and other models. We believe that by keeping
things simple and by separating concerns, you will get a stack that is
better focused on your individual needs. In that way, we believe that you
should be able to combine Spring IOC with jOOQ just as much as with JEE
components.
Most importantly, Spring Data, much like JDO in the past and like QueryDSL
today aim for integrating NoSQL databases into a unified API, which we find
a poor fit, as transaction models, data models, query models are
fundamentally different between these various data stores. We believe that
by pursuing such unification, frameworks are easily distracted from sound
SQL support. We highly value Spring's transaction model simplification and
this has worked well with jOOQ in the past.
Standards (JPA)
============
Now, one of your arguments in favour of JPA is the non-lock-in. Spring has
been challenging JEE standards for quite some time and there is no other
vendor in the market to replace Spring once you build upon Spring. Even
more so, by choosing a Spring+QueryDSL combination, you get an equally
strong vendor-lockin (or even a stronger one, as the stack gets more
complex) than when choosing Spring+jOOQ. If standards are important to you,
I suggest you only use JPA and Criteria Query (Note, that JEE also supports
IoC). And I'm sure that Adam Bien will agree with me on this :-)
But even then, you will have a hard time migrating from Hibernate to
EclipseLink or OpenJPA as the implementations are subtly different in many
ways.
The decision is up to you, and I fully understand these concerns. Standards
are a good thing because many disparate things from various vendors have
been unified and hopefully matured over time. But vendors also innovate and
thus deviate from the standard again. Without such deviation, there would
be no more progress, nor innovation in a standard. Take EclipseLink for
instance, a JPA implementation that has started to "flirt" with NoSQL
support:
http://wiki.eclipse.org/EclipseLink/Examples/JPA/NoSQL
The above is orthogonal to JPA and will add lots of complexity to this
particular implementation. JDO was another standard that has attempted to
integrate other, non-relational data stores in a single API, yet it has not
succeeded, leaving a pretty much dead standard.
Now, while you are absolutely right by saying that you cannot switch the
implementation of the jOOQ API at the moment, we're striving to innovate on
the standards front as we see room for various JSRs in the area of RDBMS
meta modelling (beyond JDBC's DatabaseMetaData), internal DSL modelling in
general, SQL DSL modelling in particular. We believe that jOOQ is the most
innovative platform for SQL to Java integration in the market at the moment
CRUD verbosity
===========
> In this area, JPA does the right thing.
>
> Author author = new Author(...);
> JPAEntityManary.persist(author);
>
> The later one is more Java more OOP.
Compare this to jOOQ's CRUD features:
AuthorRecord author = ctx.newRecord(AUTHOR);
author.store();
Or alternatively:
AuthorDao dao = ...
dao.insert(new Author());
Relevant sections in the manual are:
-
http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablerecords/
-
http://www.jooq.org/doc/3.2/manual/sql-execution/crud-with-updatablerecords/simple-crud/
- http://www.jooq.org/doc/3.2/manual/sql-execution/daos/
Fluent APIs vs. SQL
===============
I fully understand this concern. Using the jOOQ fluent API is not the same
as writing SQL directly. But let me remind you of the concerns you are
having with respect to database independence. By writing SQL directly
(through JDBC, through views or through stored procedures) you will not be
able to support dozens of databases very easily. Both JPA and jOOQ solve
this problem quite well. JPA abstracts SQL entirely, while jOOQ abstracts
the subtle differences, only.
Fluent APIs vs. custom query building APIs
================================
In addition to jOOQ's fluent API, there is also a simpler, object-oriented
SelectQuery model:
http://www.jooq.org/javadoc/latest/org/jooq/SelectQuery.html
The same applies for other types of statements (InsertQuery, UpdateQuery,
etc.). Thus, compare this fluent API usage:
> create.selectFrom(BOOK)
> .where(BOOK.PUBLISHED_IN.eq(2011))
> .orderBy(BOOK.TITLE)
With this SelectQuery usage:
SelectQuery<Book> select = create.selectQuery(BOOK);
select.addCondition(BOOK.PUBLISHED_IN.eq(2011));
select.addOrderBy(BOOK.TITLE);
Result<Book> result = select.fetch();
This is also documented here:
http://www.jooq.org/doc/3.2/manual/sql-building/sql-statements/dsl-and-non-dsl/
Conclusion
========
I hope I could put jOOQ in a different light to you. If JPA is sufficient
for your use-cases, then use JPA (or JPA+jOOQ). There are some problems
that jOOQ will never solve, which are solved very well in JPA. We have
outlined these differences here:
http://www.hibernate-alternative.com
However, by using jOOQ, you not only gain a very high level of typesafety
and thus an increase of productivity through the usage of jOOQ's code
generator which helps you detect errors early, you also get access to a
very rich set of service provider interfaces, which were off-topic in this
discussion so far. Some examples:
ExecuteListeners for custom query execution lifecycle handling:
http://www.jooq.org/doc/3.2/manual/sql-execution/execute-listeners/
Schema and table mapping for multi-tenancy support:
http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/runtime-schema-mapping/
Record mapping in order to provide custom mapping algorithms from jOOQ
records to your domain model:
http://www.jooq.org/doc/3.2/manual/sql-execution/fetching/pojos-with-recordmapper-provider/
A query transformation listener that is still being worked on, allowing for
arbitrary SQL transformation on the jOOQ AST. This can be very useful for
features like row-level security.
As I said, we believe that there is currently no other software in the Java
market that takes your SQL so seriously, and we're highly committed to
improve this even further in the near future.
Best Regards,
Lukas
2013/11/27 Frank Zhang <Fr...@citrix.com>
> Hi Lukas:
> Glad to see developer behind JOOQ here, I originally recommended
> using QueryDSL instead of JOOQ,
> there are some of my concerns:
>
> - Spring support. You know CloudStack is based on Spring IOC, the most
> compelling thing of Spring DB is its transaction
> management framework. QueryDSL has been integrated into Spring for two
> years. But there is only a simple page
> (
> http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with-spring/)
> demonstrating JOOQ and Spring, however,
> the code just shows how to load JOOQ in IOC, it doesn't show any advanced
> feature of Spring DB for example declarative
> transaction management. If JOOQ is not compatible with Spring DB, then we
> loss a widely used feature.
>
> - It's not JPA based on. This might be an argument that JOOQ may claim as
> its biggest advantage. I agree JPA does have
> some bad thing(e.g. criteria API), but the rest part is doing very well.
> The most important thing here is JPA is just API so I can choose
> underlying vendor by my preference. This non-lock-in feature is very
> appealing. For example, Hibernate has been proven by industry
> for 10 years, I can fairly trust its quality. And vendor may have some
> advanced feature like secondary cache I may need someday.
> Instead, using JOOQ means I have to rely on its underlying implementation
> with no choice.
>
> You know almost all JPA vendors support different SQL dialects. And fluent
> API(or DSL) is not attractive to me. Frankly speaking,
> IMO, for insert/update/delete clause the fluent API makes thing
> unnecessary complex that you are writing something which is even
> more complicated than SQL, for example in JOOQ,
>
> == persistence ===
> create.insertInto(AUTHOR)
> .set(AUTHOR.ID, 100)
> .set(AUTHOR.FIRST_NAME, "Hermann")
> .set(AUTHOR.LAST_NAME, "Hesse")
> .newRecord()
> .set(AUTHOR.ID, 101)
> .set(AUTHOR.FIRST_NAME, "Alfred")
> .set(AUTHOR.LAST_NAME, "Döblin");
>
> In this area, JPA does the right thing.
>
> Author author = new Author(...);
> JPAEntityManary.persist(author);
>
> The later one is more Java more OOP.
>
> === single table query ====
> Speaking of query, for single table query, fluent API is not attractive
> too. For example:
>
> Raw SQL:
> SELECT * FROM BOOK
> WHERE BOOK.PUBLISHED_IN = 2011
> ORDER BY BOOK.TITLE
>
> JOOQ:
> create.selectFrom(BOOK)
> .where(BOOK.PUBLISHED_IN.eq(2011))
> .orderBy(BOOK.TITLE)
>
>
> CloudStack search criteria built on JPA:
> Qeury<Book> q = new Qeury<Book>();
> q.add(Book_.PublishedIn. Op.eq, 2011);
> q.orderBy(Book_.title);
> List<Book> books = q.list();
>
> The last way is more Java for sure, people who even has no SQL knowledge
> can write the search.
>
> === multiple tables query ===
> The only place fluent API shining is querying multiple tables:
>
> SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
> FROM AUTHOR
> JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
> WHERE BOOK.LANGUAGE = 'DE'
> AND BOOK.PUBLISHED > '2008-01-01'
> GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
> HAVING COUNT(*) > 5
> ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
> LIMIT 2
> OFFSET 1
> FOR UPDATE
>
> JOOQ:
>
> create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
> .from(AUTHOR)
> .join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
> .where(BOOK.LANGUAGE.equal("DE"))
> .and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
> .groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
> .having(count().greaterThan(5))
> .orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
> .limit(1)
> .offset(2)
> .forUpdate();
>
> JPA does very badly in this area. However, you notice you are actually
> translating raw SQL into JOOQ DSL. In fact
> writing such a query is harder in JOOQ than in raw SQL, because you must
> not only know SQL knowledge but also know
> JOOQ dialects. The only gain is type-safe. I know Java developers are
> used to static type too much, they are not willing
> to write any code that cannot be checked by compiler. Type safe is the
> reason that no ORM is perfect, and it can never be.
>
> In summary, I don't think dropping in any ORM can solve problem perfectly,
> if I did it, I would do:
>
> - Using JPA based framework.
> - building persistent interface(create/update/delete) and single table
> search criteria using JPA
> - for multiple tables query, either using JPA JPQL(if you don't mind type
> safe) or dropping in some fluent API framework if you can not stand for any
> type unsafe thing.
>
>
>
> > -----Original Message-----
> > From: Lukas Eder [mailto:lukas.eder@gmail.com]
> > Sent: Tuesday, November 26, 2013 3:58 AM
> > To: dev@cloudstack.apache.org
> > Subject: Re: persistence layer
> >
> > 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 <Al...@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
>
RE: persistence layer
Posted by Frank Zhang <Fr...@citrix.com>.
Hi Lukas:
Glad to see developer behind JOOQ here, I originally recommended using QueryDSL instead of JOOQ,
there are some of my concerns:
- Spring support. You know CloudStack is based on Spring IOC, the most compelling thing of Spring DB is its transaction
management framework. QueryDSL has been integrated into Spring for two years. But there is only a simple page
(http://www.jooq.org/doc/3.2/manual/getting-started/tutorials/jooq-with-spring/) demonstrating JOOQ and Spring, however,
the code just shows how to load JOOQ in IOC, it doesn't show any advanced feature of Spring DB for example declarative
transaction management. If JOOQ is not compatible with Spring DB, then we loss a widely used feature.
- It's not JPA based on. This might be an argument that JOOQ may claim as its biggest advantage. I agree JPA does have
some bad thing(e.g. criteria API), but the rest part is doing very well. The most important thing here is JPA is just API so I can choose
underlying vendor by my preference. This non-lock-in feature is very appealing. For example, Hibernate has been proven by industry
for 10 years, I can fairly trust its quality. And vendor may have some advanced feature like secondary cache I may need someday.
Instead, using JOOQ means I have to rely on its underlying implementation with no choice.
You know almost all JPA vendors support different SQL dialects. And fluent API(or DSL) is not attractive to me. Frankly speaking,
IMO, for insert/update/delete clause the fluent API makes thing unnecessary complex that you are writing something which is even
more complicated than SQL, for example in JOOQ,
== persistence ===
create.insertInto(AUTHOR)
.set(AUTHOR.ID, 100)
.set(AUTHOR.FIRST_NAME, "Hermann")
.set(AUTHOR.LAST_NAME, "Hesse")
.newRecord()
.set(AUTHOR.ID, 101)
.set(AUTHOR.FIRST_NAME, "Alfred")
.set(AUTHOR.LAST_NAME, "Döblin");
In this area, JPA does the right thing.
Author author = new Author(...);
JPAEntityManary.persist(author);
The later one is more Java more OOP.
=== single table query ====
Speaking of query, for single table query, fluent API is not attractive too. For example:
Raw SQL:
SELECT * FROM BOOK
WHERE BOOK.PUBLISHED_IN = 2011
ORDER BY BOOK.TITLE
JOOQ:
create.selectFrom(BOOK)
.where(BOOK.PUBLISHED_IN.eq(2011))
.orderBy(BOOK.TITLE)
CloudStack search criteria built on JPA:
Qeury<Book> q = new Qeury<Book>();
q.add(Book_.PublishedIn. Op.eq, 2011);
q.orderBy(Book_.title);
List<Book> books = q.list();
The last way is more Java for sure, people who even has no SQL knowledge can write the search.
=== multiple tables query ===
The only place fluent API shining is querying multiple tables:
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, COUNT(*)
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
WHERE BOOK.LANGUAGE = 'DE'
AND BOOK.PUBLISHED > '2008-01-01'
GROUP BY AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME
HAVING COUNT(*) > 5
ORDER BY AUTHOR.LAST_NAME ASC NULLS FIRST
LIMIT 2
OFFSET 1
FOR UPDATE
JOOQ:
create.select(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME, count())
.from(AUTHOR)
.join(BOOK).on(BOOK.AUTHOR_ID.equal(AUTHOR.ID))
.where(BOOK.LANGUAGE.equal("DE"))
.and(BOOK.PUBLISHED.greaterThan("2008-01-01"))
.groupBy(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
.having(count().greaterThan(5))
.orderBy(AUTHOR.LAST_NAME.asc().nullsFirst())
.limit(1)
.offset(2)
.forUpdate();
JPA does very badly in this area. However, you notice you are actually translating raw SQL into JOOQ DSL. In fact
writing such a query is harder in JOOQ than in raw SQL, because you must not only know SQL knowledge but also know
JOOQ dialects. The only gain is type-safe. I know Java developers are used to static type too much, they are not willing
to write any code that cannot be checked by compiler. Type safe is the reason that no ORM is perfect, and it can never be.
In summary, I don't think dropping in any ORM can solve problem perfectly, if I did it, I would do:
- Using JPA based framework.
- building persistent interface(create/update/delete) and single table search criteria using JPA
- for multiple tables query, either using JPA JPQL(if you don't mind type safe) or dropping in some fluent API framework if you can not stand for any type unsafe thing.
> -----Original Message-----
> From: Lukas Eder [mailto:lukas.eder@gmail.com]
> Sent: Tuesday, November 26, 2013 3:58 AM
> To: dev@cloudstack.apache.org
> Subject: Re: persistence layer
>
> 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 <Al...@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
Re: persistence layer
Posted by Chiradeep Vittal <Ch...@citrix.com>.
Hi Lukas, thanks for chiming in.
I do agree that abstracting over NoSQL and SQL is not compelling.
Both have their specific strengths and developers would be foolhardy to
simply assume that swapping one for the other will magically work.
On 11/26/13 3:57 AM, "Lukas Eder" <lu...@gmail.com> wrote:
>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 <Al...@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-valu
>es/
>[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-spr
>ing/
>- 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
Re: persistence layer
Posted by Lukas Eder <lu...@gmail.com>.
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 <Al...@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
RE: persistence layer
Posted by Alex Huang <Al...@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.
--Alex
> -----Original Message-----
> From: Sebastien Goasguen [mailto:runseb@gmail.com]
> Sent: Monday, November 25, 2013 2:21 AM
> To: dev@cloudstack.apache.org
> Subject: Re: persistence layer
>
>
> On Nov 23, 2013, at 4:13 PM, Laszlo Hornyak <la...@gmail.com>
> wrote:
>
> > Wouldn't it be a lot of work to move to JOOQ? All queries will have to
> > be rewritten.
> >
> >
>
> An a non-java developer question: Will that help support different
> databases ? like moving to MariaDB ?
>
> >
> > On Sat, Nov 23, 2013 at 11:32 AM, Darren Shepherd <
> > darren.s.shepherd@gmail.com> wrote:
> >
> >> Going to an ORM is not as simple as you would expect. First, one can
> >> make a strong argument that ORM is not the right solution, but that
> >> can be ignored right now.
> >>
> >> You have to look at the context of ACS and figure out what technology
> >> is the most practical to adopt. ACS does not have ORM today. It has a
> custom
> >> query api, object mapping, and change tracking for simple CRUD.
> Honestly
> >> these features are quite sufficient for ACS needs. The problem, and
> >> why we should change it, is that the current framework is custom,
> >> limited in functionality, undocumented, and generally a barrier to
> >> people developing on ACS. So jOOQ is a somewhat similar approach but
> >> it is just far far better, has a community of users that have
> >> developed over 3-4 years, is well documented, and honestly just a very
> well thought out framework.
> >>
> >> Darren
> >>
> >>> On Nov 22, 2013, at 6:50 PM, Alex Ough <al...@sungard.com>
> wrote:
> >>>
> >>> All,
> >>>
> >>> I'm very interested in converting the current DAO framework to an
> >>> ORM. I didn't have any experience with java related ORMs, but I've
> >>> done quite
> >> lots
> >>> of works with Django and LINQ. So can you add me if this project is
> >> started?
> >>>
> >>> Thanks
> >>> Alex Ough
> >>>
> >>>
> >>> On Fri, Nov 22, 2013 at 7:06 AM, Daan Hoogland
> >>> <daan.hoogland@gmail.com
> >>> wrote:
> >>>
> >>>> Had a quick look, It looks alright. One question/doubt: will we
> >>>> thigh ourselves more to mysql if we code sql more directly instead
> >>>> of abstracting away from it so we can leave db choice to the
> >>>> operator in the future!?!?
> >>>>
> >>>> On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
> >>>> <da...@gmail.com> wrote:
> >>>>> I've done a lot of analysis on the data access layer, but just
> >>>>> haven't
> >>>> had time to put together a discuss/recommendation. In the end I'd
> >> propose
> >>>> we move to jOOQ. It's an excellent framework that will be very
> >>>> natural
> >> to
> >>>> the style of data access that CloudStack uses and we can slowly
> >>>> migrate
> >> to
> >>>> it. I've hacked up some code and proven that I can get the two
> >> frameworks
> >>>> to seamlessly interoperate. So you can select from a custom DAO
> >>>> and
> >> commit
> >>>> with jOOQ or vice versa. Additionally jOOQ will work with the
> >>>> existing pojos we have today.
> >>>>>
> >>>>> Check out jOOQ and let me know what you think of it. I know for
> >>>>> most
> >>>> people the immediate thought would be to move to JPA, but the way
> >>>> we managed "session" is completely incompatible with JPA and will
> >>>> require constant merging. Additionally mixing our custom DAO
> >>>> framework with a
> >> JPA
> >>>> solution looks darn near impossible.
> >>>>>
> >>>>> Darren
> >>>>>
> >>>>>> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak
> >>>>>> <laszlo.hornyak@gmail.com
> >>>
> >>>> wrote:
> >>>>>>
> >>>>>> Hi,
> >>>>>>
> >>>>>> What are the general directions with the persistence system?
> >>>>>> What I know about it is:
> >>>>>> - It works with JPA (javax.persistence) annotations
> >>>>>> - But rather than integrating a general JPA implementation such
> >>>>>> us hibernate, eclipselink or OpenJPA it uses its own query
> >>>>>> generator and
> >>>> DAO
> >>>>>> classes to generate SQL statements.
> >>>>>>
> >>>>>> Questions:
> >>>>>> - Are you planing to use JPA? What is the motivation behind the
> >>>>>> custom
> >>>> DAO
> >>>>>> system?
> >>>>>> - There are some capabilities in the DAO system that are not used.
> >>>> Should
> >>>>>> these capabilities be maintained or is it ok to remove the
> >>>>>> support for unused features in small steps?
> >>>>>>
> >>>>>> --
> >>>>>>
> >>>>>> EOF
> >>>>
> >>>>
> >>
> >
> >
> >
> > --
> >
> > EOF
Re: persistence layer
Posted by Sebastien Goasguen <ru...@gmail.com>.
On Nov 23, 2013, at 4:13 PM, Laszlo Hornyak <la...@gmail.com> wrote:
> Wouldn't it be a lot of work to move to JOOQ? All queries will have to be
> rewritten.
>
>
An a non-java developer question: Will that help support different databases ? like moving to MariaDB ?
>
> On Sat, Nov 23, 2013 at 11:32 AM, Darren Shepherd <
> darren.s.shepherd@gmail.com> wrote:
>
>> Going to an ORM is not as simple as you would expect. First, one can make
>> a strong argument that ORM is not the right solution, but that can be
>> ignored right now.
>>
>> You have to look at the context of ACS and figure out what technology is
>> the most practical to adopt. ACS does not have ORM today. It has a custom
>> query api, object mapping, and change tracking for simple CRUD. Honestly
>> these features are quite sufficient for ACS needs. The problem, and why we
>> should change it, is that the current framework is custom, limited in
>> functionality, undocumented, and generally a barrier to people developing
>> on ACS. So jOOQ is a somewhat similar approach but it is just far far
>> better, has a community of users that have developed over 3-4 years, is
>> well documented, and honestly just a very well thought out framework.
>>
>> Darren
>>
>>> On Nov 22, 2013, at 6:50 PM, Alex Ough <al...@sungard.com> wrote:
>>>
>>> All,
>>>
>>> I'm very interested in converting the current DAO framework to an ORM. I
>>> didn't have any experience with java related ORMs, but I've done quite
>> lots
>>> of works with Django and LINQ. So can you add me if this project is
>> started?
>>>
>>> Thanks
>>> Alex Ough
>>>
>>>
>>> On Fri, Nov 22, 2013 at 7:06 AM, Daan Hoogland <daan.hoogland@gmail.com
>>> wrote:
>>>
>>>> Had a quick look, It looks alright. One question/doubt: will we thigh
>>>> ourselves more to mysql if we code sql more directly instead of
>>>> abstracting away from it so we can leave db choice to the operator in
>>>> the future!?!?
>>>>
>>>> On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
>>>> <da...@gmail.com> wrote:
>>>>> I've done a lot of analysis on the data access layer, but just haven't
>>>> had time to put together a discuss/recommendation. In the end I'd
>> propose
>>>> we move to jOOQ. It's an excellent framework that will be very natural
>> to
>>>> the style of data access that CloudStack uses and we can slowly migrate
>> to
>>>> it. I've hacked up some code and proven that I can get the two
>> frameworks
>>>> to seamlessly interoperate. So you can select from a custom DAO and
>> commit
>>>> with jOOQ or vice versa. Additionally jOOQ will work with the existing
>>>> pojos we have today.
>>>>>
>>>>> Check out jOOQ and let me know what you think of it. I know for most
>>>> people the immediate thought would be to move to JPA, but the way we
>>>> managed "session" is completely incompatible with JPA and will require
>>>> constant merging. Additionally mixing our custom DAO framework with a
>> JPA
>>>> solution looks darn near impossible.
>>>>>
>>>>> Darren
>>>>>
>>>>>> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <laszlo.hornyak@gmail.com
>>>
>>>> wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> What are the general directions with the persistence system?
>>>>>> What I know about it is:
>>>>>> - It works with JPA (javax.persistence) annotations
>>>>>> - But rather than integrating a general JPA implementation such us
>>>>>> hibernate, eclipselink or OpenJPA it uses its own query generator and
>>>> DAO
>>>>>> classes to generate SQL statements.
>>>>>>
>>>>>> Questions:
>>>>>> - Are you planing to use JPA? What is the motivation behind the custom
>>>> DAO
>>>>>> system?
>>>>>> - There are some capabilities in the DAO system that are not used.
>>>> Should
>>>>>> these capabilities be maintained or is it ok to remove the support for
>>>>>> unused features in small steps?
>>>>>>
>>>>>> --
>>>>>>
>>>>>> EOF
>>>>
>>>>
>>
>
>
>
> --
>
> EOF
Re: persistence layer
Posted by Laszlo Hornyak <la...@gmail.com>.
Wouldn't it be a lot of work to move to JOOQ? All queries will have to be
rewritten.
On Sat, Nov 23, 2013 at 11:32 AM, Darren Shepherd <
darren.s.shepherd@gmail.com> wrote:
> Going to an ORM is not as simple as you would expect. First, one can make
> a strong argument that ORM is not the right solution, but that can be
> ignored right now.
>
> You have to look at the context of ACS and figure out what technology is
> the most practical to adopt. ACS does not have ORM today. It has a custom
> query api, object mapping, and change tracking for simple CRUD. Honestly
> these features are quite sufficient for ACS needs. The problem, and why we
> should change it, is that the current framework is custom, limited in
> functionality, undocumented, and generally a barrier to people developing
> on ACS. So jOOQ is a somewhat similar approach but it is just far far
> better, has a community of users that have developed over 3-4 years, is
> well documented, and honestly just a very well thought out framework.
>
> Darren
>
> > On Nov 22, 2013, at 6:50 PM, Alex Ough <al...@sungard.com> wrote:
> >
> > All,
> >
> > I'm very interested in converting the current DAO framework to an ORM. I
> > didn't have any experience with java related ORMs, but I've done quite
> lots
> > of works with Django and LINQ. So can you add me if this project is
> started?
> >
> > Thanks
> > Alex Ough
> >
> >
> > On Fri, Nov 22, 2013 at 7:06 AM, Daan Hoogland <daan.hoogland@gmail.com
> >wrote:
> >
> >> Had a quick look, It looks alright. One question/doubt: will we thigh
> >> ourselves more to mysql if we code sql more directly instead of
> >> abstracting away from it so we can leave db choice to the operator in
> >> the future!?!?
> >>
> >> On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
> >> <da...@gmail.com> wrote:
> >>> I've done a lot of analysis on the data access layer, but just haven't
> >> had time to put together a discuss/recommendation. In the end I'd
> propose
> >> we move to jOOQ. It's an excellent framework that will be very natural
> to
> >> the style of data access that CloudStack uses and we can slowly migrate
> to
> >> it. I've hacked up some code and proven that I can get the two
> frameworks
> >> to seamlessly interoperate. So you can select from a custom DAO and
> commit
> >> with jOOQ or vice versa. Additionally jOOQ will work with the existing
> >> pojos we have today.
> >>>
> >>> Check out jOOQ and let me know what you think of it. I know for most
> >> people the immediate thought would be to move to JPA, but the way we
> >> managed "session" is completely incompatible with JPA and will require
> >> constant merging. Additionally mixing our custom DAO framework with a
> JPA
> >> solution looks darn near impossible.
> >>>
> >>> Darren
> >>>
> >>>> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <laszlo.hornyak@gmail.com
> >
> >> wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> What are the general directions with the persistence system?
> >>>> What I know about it is:
> >>>> - It works with JPA (javax.persistence) annotations
> >>>> - But rather than integrating a general JPA implementation such us
> >>>> hibernate, eclipselink or OpenJPA it uses its own query generator and
> >> DAO
> >>>> classes to generate SQL statements.
> >>>>
> >>>> Questions:
> >>>> - Are you planing to use JPA? What is the motivation behind the custom
> >> DAO
> >>>> system?
> >>>> - There are some capabilities in the DAO system that are not used.
> >> Should
> >>>> these capabilities be maintained or is it ok to remove the support for
> >>>> unused features in small steps?
> >>>>
> >>>> --
> >>>>
> >>>> EOF
> >>
> >>
>
--
EOF
Re: persistence layer
Posted by Darren Shepherd <da...@gmail.com>.
Going to an ORM is not as simple as you would expect. First, one can make a strong argument that ORM is not the right solution, but that can be ignored right now.
You have to look at the context of ACS and figure out what technology is the most practical to adopt. ACS does not have ORM today. It has a custom query api, object mapping, and change tracking for simple CRUD. Honestly these features are quite sufficient for ACS needs. The problem, and why we should change it, is that the current framework is custom, limited in functionality, undocumented, and generally a barrier to people developing on ACS. So jOOQ is a somewhat similar approach but it is just far far better, has a community of users that have developed over 3-4 years, is well documented, and honestly just a very well thought out framework.
Darren
> On Nov 22, 2013, at 6:50 PM, Alex Ough <al...@sungard.com> wrote:
>
> All,
>
> I'm very interested in converting the current DAO framework to an ORM. I
> didn't have any experience with java related ORMs, but I've done quite lots
> of works with Django and LINQ. So can you add me if this project is started?
>
> Thanks
> Alex Ough
>
>
> On Fri, Nov 22, 2013 at 7:06 AM, Daan Hoogland <da...@gmail.com>wrote:
>
>> Had a quick look, It looks alright. One question/doubt: will we thigh
>> ourselves more to mysql if we code sql more directly instead of
>> abstracting away from it so we can leave db choice to the operator in
>> the future!?!?
>>
>> On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
>> <da...@gmail.com> wrote:
>>> I've done a lot of analysis on the data access layer, but just haven't
>> had time to put together a discuss/recommendation. In the end I'd propose
>> we move to jOOQ. It's an excellent framework that will be very natural to
>> the style of data access that CloudStack uses and we can slowly migrate to
>> it. I've hacked up some code and proven that I can get the two frameworks
>> to seamlessly interoperate. So you can select from a custom DAO and commit
>> with jOOQ or vice versa. Additionally jOOQ will work with the existing
>> pojos we have today.
>>>
>>> Check out jOOQ and let me know what you think of it. I know for most
>> people the immediate thought would be to move to JPA, but the way we
>> managed "session" is completely incompatible with JPA and will require
>> constant merging. Additionally mixing our custom DAO framework with a JPA
>> solution looks darn near impossible.
>>>
>>> Darren
>>>
>>>> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <la...@gmail.com>
>> wrote:
>>>>
>>>> Hi,
>>>>
>>>> What are the general directions with the persistence system?
>>>> What I know about it is:
>>>> - It works with JPA (javax.persistence) annotations
>>>> - But rather than integrating a general JPA implementation such us
>>>> hibernate, eclipselink or OpenJPA it uses its own query generator and
>> DAO
>>>> classes to generate SQL statements.
>>>>
>>>> Questions:
>>>> - Are you planing to use JPA? What is the motivation behind the custom
>> DAO
>>>> system?
>>>> - There are some capabilities in the DAO system that are not used.
>> Should
>>>> these capabilities be maintained or is it ok to remove the support for
>>>> unused features in small steps?
>>>>
>>>> --
>>>>
>>>> EOF
>>
>>
RE: persistence layer
Posted by Frank Zhang <Fr...@citrix.com>.
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.
> -----Original Message-----
> From: Alex Ough [mailto:alex.ough@sungard.com]
> Sent: Friday, November 22, 2013 9:50 AM
> To: dev@cloudstack.apache.org
> Subject: Re: persistence layer
>
> All,
>
> I'm very interested in converting the current DAO framework to an ORM. I
> didn't have any experience with java related ORMs, but I've done quite lots of
> works with Django and LINQ. So can you add me if this project is started?
>
> Thanks
> Alex Ough
>
>
> On Fri, Nov 22, 2013 at 7:06 AM, Daan Hoogland
> <da...@gmail.com>wrote:
>
> > Had a quick look, It looks alright. One question/doubt: will we thigh
> > ourselves more to mysql if we code sql more directly instead of
> > abstracting away from it so we can leave db choice to the operator in
> > the future!?!?
> >
> > On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
> > <da...@gmail.com> wrote:
> > > I've done a lot of analysis on the data access layer, but just
> > > haven't
> > had time to put together a discuss/recommendation. In the end I'd
> > propose we move to jOOQ. It's an excellent framework that will be
> > very natural to the style of data access that CloudStack uses and we
> > can slowly migrate to it. I've hacked up some code and proven that I
> > can get the two frameworks to seamlessly interoperate. So you can
> > select from a custom DAO and commit with jOOQ or vice versa.
> > Additionally jOOQ will work with the existing pojos we have today.
> > >
> > > Check out jOOQ and let me know what you think of it. I know for
> > > most
> > people the immediate thought would be to move to JPA, but the way we
> > managed "session" is completely incompatible with JPA and will require
> > constant merging. Additionally mixing our custom DAO framework with a
> > JPA solution looks darn near impossible.
> > >
> > > Darren
> > >
> > >> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak
> > >> <la...@gmail.com>
> > wrote:
> > >>
> > >> Hi,
> > >>
> > >> What are the general directions with the persistence system?
> > >> What I know about it is:
> > >> - It works with JPA (javax.persistence) annotations
> > >> - But rather than integrating a general JPA implementation such us
> > >> hibernate, eclipselink or OpenJPA it uses its own query generator
> > >> and
> > DAO
> > >> classes to generate SQL statements.
> > >>
> > >> Questions:
> > >> - Are you planing to use JPA? What is the motivation behind the
> > >> custom
> > DAO
> > >> system?
> > >> - There are some capabilities in the DAO system that are not used.
> > Should
> > >> these capabilities be maintained or is it ok to remove the support
> > >> for unused features in small steps?
> > >>
> > >> --
> > >>
> > >> EOF
> >
> >
Re: persistence layer
Posted by Alex Ough <al...@sungard.com>.
All,
I'm very interested in converting the current DAO framework to an ORM. I
didn't have any experience with java related ORMs, but I've done quite lots
of works with Django and LINQ. So can you add me if this project is started?
Thanks
Alex Ough
On Fri, Nov 22, 2013 at 7:06 AM, Daan Hoogland <da...@gmail.com>wrote:
> Had a quick look, It looks alright. One question/doubt: will we thigh
> ourselves more to mysql if we code sql more directly instead of
> abstracting away from it so we can leave db choice to the operator in
> the future!?!?
>
> On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
> <da...@gmail.com> wrote:
> > I've done a lot of analysis on the data access layer, but just haven't
> had time to put together a discuss/recommendation. In the end I'd propose
> we move to jOOQ. It's an excellent framework that will be very natural to
> the style of data access that CloudStack uses and we can slowly migrate to
> it. I've hacked up some code and proven that I can get the two frameworks
> to seamlessly interoperate. So you can select from a custom DAO and commit
> with jOOQ or vice versa. Additionally jOOQ will work with the existing
> pojos we have today.
> >
> > Check out jOOQ and let me know what you think of it. I know for most
> people the immediate thought would be to move to JPA, but the way we
> managed "session" is completely incompatible with JPA and will require
> constant merging. Additionally mixing our custom DAO framework with a JPA
> solution looks darn near impossible.
> >
> > Darren
> >
> >> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <la...@gmail.com>
> wrote:
> >>
> >> Hi,
> >>
> >> What are the general directions with the persistence system?
> >> What I know about it is:
> >> - It works with JPA (javax.persistence) annotations
> >> - But rather than integrating a general JPA implementation such us
> >> hibernate, eclipselink or OpenJPA it uses its own query generator and
> DAO
> >> classes to generate SQL statements.
> >>
> >> Questions:
> >> - Are you planing to use JPA? What is the motivation behind the custom
> DAO
> >> system?
> >> - There are some capabilities in the DAO system that are not used.
> Should
> >> these capabilities be maintained or is it ok to remove the support for
> >> unused features in small steps?
> >>
> >> --
> >>
> >> EOF
>
>
Re: persistence layer
Posted by Darren Shepherd <da...@gmail.com>.
Jooq does a good job of handling the differences in dialects so as long as you don't use a feature specific to a database your are fine. More info at http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/sql-dialects/
I've used jooq across databases and it's been fine. The sql we use in ACS is quite simple so supporting multiple DBs will be fine.
Darren
> On Nov 22, 2013, at 2:06 PM, Daan Hoogland <da...@gmail.com> wrote:
>
> Had a quick look, It looks alright. One question/doubt: will we thigh
> ourselves more to mysql if we code sql more directly instead of
> abstracting away from it so we can leave db choice to the operator in
> the future!?!?
>
> On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
> <da...@gmail.com> wrote:
>> I've done a lot of analysis on the data access layer, but just haven't had time to put together a discuss/recommendation. In the end I'd propose we move to jOOQ. It's an excellent framework that will be very natural to the style of data access that CloudStack uses and we can slowly migrate to it. I've hacked up some code and proven that I can get the two frameworks to seamlessly interoperate. So you can select from a custom DAO and commit with jOOQ or vice versa. Additionally jOOQ will work with the existing pojos we have today.
>>
>> Check out jOOQ and let me know what you think of it. I know for most people the immediate thought would be to move to JPA, but the way we managed "session" is completely incompatible with JPA and will require constant merging. Additionally mixing our custom DAO framework with a JPA solution looks darn near impossible.
>>
>> Darren
>>
>>> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <la...@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> What are the general directions with the persistence system?
>>> What I know about it is:
>>> - It works with JPA (javax.persistence) annotations
>>> - But rather than integrating a general JPA implementation such us
>>> hibernate, eclipselink or OpenJPA it uses its own query generator and DAO
>>> classes to generate SQL statements.
>>>
>>> Questions:
>>> - Are you planing to use JPA? What is the motivation behind the custom DAO
>>> system?
>>> - There are some capabilities in the DAO system that are not used. Should
>>> these capabilities be maintained or is it ok to remove the support for
>>> unused features in small steps?
>>>
>>> --
>>>
>>> EOF
Re: persistence layer
Posted by Daan Hoogland <da...@gmail.com>.
Had a quick look, It looks alright. One question/doubt: will we thigh
ourselves more to mysql if we code sql more directly instead of
abstracting away from it so we can leave db choice to the operator in
the future!?!?
On Thu, Nov 21, 2013 at 7:03 AM, Darren Shepherd
<da...@gmail.com> wrote:
> I've done a lot of analysis on the data access layer, but just haven't had time to put together a discuss/recommendation. In the end I'd propose we move to jOOQ. It's an excellent framework that will be very natural to the style of data access that CloudStack uses and we can slowly migrate to it. I've hacked up some code and proven that I can get the two frameworks to seamlessly interoperate. So you can select from a custom DAO and commit with jOOQ or vice versa. Additionally jOOQ will work with the existing pojos we have today.
>
> Check out jOOQ and let me know what you think of it. I know for most people the immediate thought would be to move to JPA, but the way we managed "session" is completely incompatible with JPA and will require constant merging. Additionally mixing our custom DAO framework with a JPA solution looks darn near impossible.
>
> Darren
>
>> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <la...@gmail.com> wrote:
>>
>> Hi,
>>
>> What are the general directions with the persistence system?
>> What I know about it is:
>> - It works with JPA (javax.persistence) annotations
>> - But rather than integrating a general JPA implementation such us
>> hibernate, eclipselink or OpenJPA it uses its own query generator and DAO
>> classes to generate SQL statements.
>>
>> Questions:
>> - Are you planing to use JPA? What is the motivation behind the custom DAO
>> system?
>> - There are some capabilities in the DAO system that are not used. Should
>> these capabilities be maintained or is it ok to remove the support for
>> unused features in small steps?
>>
>> --
>>
>> EOF
Re: persistence layer
Posted by Darren Shepherd <da...@gmail.com>.
I've done a lot of analysis on the data access layer, but just haven't had time to put together a discuss/recommendation. In the end I'd propose we move to jOOQ. It's an excellent framework that will be very natural to the style of data access that CloudStack uses and we can slowly migrate to it. I've hacked up some code and proven that I can get the two frameworks to seamlessly interoperate. So you can select from a custom DAO and commit with jOOQ or vice versa. Additionally jOOQ will work with the existing pojos we have today.
Check out jOOQ and let me know what you think of it. I know for most people the immediate thought would be to move to JPA, but the way we managed "session" is completely incompatible with JPA and will require constant merging. Additionally mixing our custom DAO framework with a JPA solution looks darn near impossible.
Darren
> On Nov 11, 2013, at 8:33 PM, Laszlo Hornyak <la...@gmail.com> wrote:
>
> Hi,
>
> What are the general directions with the persistence system?
> What I know about it is:
> - It works with JPA (javax.persistence) annotations
> - But rather than integrating a general JPA implementation such us
> hibernate, eclipselink or OpenJPA it uses its own query generator and DAO
> classes to generate SQL statements.
>
> Questions:
> - Are you planing to use JPA? What is the motivation behind the custom DAO
> system?
> - There are some capabilities in the DAO system that are not used. Should
> these capabilities be maintained or is it ok to remove the support for
> unused features in small steps?
>
> --
>
> EOF