You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cloudstack.apache.org by Darren Shepherd <da...@gmail.com> on 2013/12/02 18:32:38 UTC
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-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 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
> >
>