Thanks Lukas. Things are clear now
> -----Original Message----- > From: Lukas Eder [mailto:lukas.e...@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 <frank.zh...@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 <frank.zh...@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.e...@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 <alex.hu...@citrix.com> > > > > > > > > > > > Has anyone actually tried dropping in a different jdbc driver > > > > > > and see if CS can use another DB? I don't think the current > > > > > > CS DB layer prevents anyone from doing that. > > > > > > > > > > > > This is different from MariaDB which, as othes have pointed > > > > > > out, is drop-in replacement for MySQL. I'm talking about > > > > > > stuff like derby or sqlserver or oracle or db2. > > > > > > > > > > > > > > > > MariaDB and MySQL are diverging quickly, as Oracle invests quite > > > > > some development efforts into MySQL. While the drop-in > > > > > replacement argument is valid right now, it might not be two years > from now. > > > > > > > > > > In general, SQL dialects are very different when it comes to the > > > > subtleties of > > > > > syntax or feature sets. Since you're comparing MySQL with Derby, > > > > > SQL > > > > Server, > > > > > Oracle, or DB2, let me give you some ideas: > > > > > > > > > > - MySQL has a lot of vendor-specific clauses that no one else > > > > > has > > (e.g. > > > > ON > > > > > DUPLICATE KEY UPDATE) > > > > > - MySQL bends the SQL syntax and accepts what is otherwise > > > > > illegal > > > > syntax (e.g. > > > > > GROUP BY semantics [1]) > > > > > - MySQL doesn't correctly implement the SQL standard NOT IN > > > > > semantics [2] > > > > > - Derby and DB2 (prior to version 9.7) are extremely type-safe. > > > > > For > > > > instance, > > > > > CAST(NULL AS INT) and CAST(NULL AS VARCHAR) are two entirely > > > > > different things. This can lead to tedious work when binding > > > > > null as a bind > > > > variable. Some > > > > > background info [3] > > > > > - DB2 has a lot of peculiarities when it comes to memory > > > > > management of VARCHAR [4] > > > > > - Case-sensitivity of schema, table, and column names is an > > > > > eternal > > > > hassle > > > > > between RDBMS > > > > > - JDBC drivers implement things very differently in some areas. > > > > > Fetching > > > > an > > > > > inserted ID is really a pain. > > > > > > > > > > [1]: http://blog.jooq.org/2012/08/05/mysql-bad-idea-384/ > > > > > [2]: > > > > > > > > > http://blog.jooq.org/2012/01/27/sql-incompatibilities-not-in-and-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 > > > > > >
