[hibernate-dev] Hibernate ORM SQL generation

Sat Sep 5 14:04:15 EDT 2015

Getting back to this work this weekend and next week.

Initially I am working on the basics for SQL generation from a SQM model.
There was an open question before with regards to whether it made sense to
render the SQM directly into SQL (String) or whether it instead made sense
to render the SQM into a "SQL model" which we'd walk afterwards to generate
the SQL.  So let's investigate this second approach...

CONS

The major drawback is that we'd end up walking the tree (a different tree,
the "SQL model") again which would have both a performance and some
complexity concerns.

The performance concern may or may not be a big deal.  We'd end up having
to walk parts of the SQM tree multiple times anyway.

Part of the complexity comes from having to write yet another walker for
walking this "SQL model" tree.  It's not a major issue as we have already
seen it work in the SQM walkers.  A complication here is that it does limit
how we apply Dialect intervention and the types of intervention a Dialect
can achieve.  Rendering to a String is pretty free-form and the Dialect can
do all kinds of things.  When we render to a tree, the Dialect has to
adhere to available constructs we have encoded into the tree structure.  A
silly example is query that the Oracle dialect wants to render using a
CONNECT-BY query.  Rendering that to a String directly is not a big deal.
But if we render that to a tree, the tree needs to be capable of
understanding that construct and substructure; that or we allow for
"free-form nodes" in the tree, but that gets tricky for walking.  So if we
stick to "general SQL structure", rendering to a SQL tree should be very
doable.

PROS

The main advantage is to better leverage contextual information.  Duh,
that's always the reason a structure is better than a BLOB/String :)  Here,
specifically it would better allow us to render smarter SQL.  The one that
I was initially working though which prompted this email is the case of
"repeating expressions".  Consider a SQL query like "select a+b from XYZ
where a+b > 2".  The more efficient way to write that query is "select a+b
as c from XYZ where c > 2".  We could technically even do that kind of
normalization while rendering the SQM directly to String.  But it is easier
and less error prone if we rendered to a SQL tree first.

Other things include leaving off un-needed joins, etc.

On Fri, Aug 28, 2015 at 6:07 AM Sanne Grinovero <sanne at hibernate.org> wrote:

> On 27 August 2015 at 18:30, Steve Ebersole <steve at hibernate.org> wrote:
> > Nevermind.  I will not do that.  I think I have found a still-easyish way
> > to do it.
>
> Great! Highly appreciate that.
>
>
> >
> > On Thu, Aug 27, 2015 at 10:57 AM Steve Ebersole <steve at hibernate.org>
> wrote:
> >
> >> I do want to pull ORM in to the hibernate-sqm module as a test
> dependency
> >> to be able to more easily set up the ModelMetadata stuff based on a
> >> SessionFactory.  That is possibly awkward later when we then use
> >> hibernate-sqm in ORM in terms of having 2 different versions of ORM.  I
> am
> >> open to alternatives that don't involve *me* developing a real(ish)
> ModelMetadata
> >> impl from scratch.
> >>
> >> On Thu, Aug 27, 2015 at 8:45 AM Gunnar Morling <gunnar at hibernate.org>
> >> wrote:
> >>
> >>> 2015-08-26 14:41 GMT+02:00 Steve Ebersole <steve at hibernate.org>:
> >>> > On Wed, Aug 26, 2015 at 2:10 AM Gunnar Morling <gunnar at hibernate.org
> >
> >>> wrote:
> >>> >>
> >>> >> Hi Steve,
> >>> >>
> >>> >> > The other approach is to use a 3-phase translation (input
> >>> >> > -> semantic-tree -> semantic-SQL-tree(s) -> SQL).  This gives a
> hint
> >>> to
> >>> >> > one
> >>> >> > of the major problems.  One source "semantic" query will often
> >>> >> > correspond
> >>> >> > to multiple SQL queries; that is hard to manage in the 2-phase
> >>> approach.
> >>> >>
> >>> >> In which situations will this happen? I can see inheritance where a
> >>> >> HQL query targeting a super-type needs to be translated into a SQL
> >>> >> query per sub-type table. What others are there?
> >>> >
> >>> >
> >>> > For ORM the only time this happens today for a SELECT query is in the
> >>> "split
> >>> > query" case I mentioned elsewhere (a query like 'from
> >>> java.lang.Object').
> >>> > SQM does this much better than we do in ORM today.  in SQM we build a
> >>> > semantic tree that encodes the "unmapped polymorphism" such that we
> get
> >>> a
> >>> > tree with 'java.lang.Object' as the root from element. But it is a
> >>> > FromElement with a special type of EntityTypeDescriptor (which comes
> >>> from
> >>> > the caller remember): PolymorphicEntityTypeDescriptor.  On the ORM
> side
> >>> then
> >>> > I have a QuerySplitter that takes that query and makes a copy of that
> >>> entire
> >>> > SQM tree, one for each mapped implementor of the specified class.
> >>> FWIW, ORM
> >>> > does this today, albeit in a different way.  Today we split the query
> >>> based
> >>> > on String manip and then feed it parser.  Here we feed it to the
> parser
> >>> and
> >>> > use the tree to split it; much less brittle :)
> >>> >
> >>> > Really the cases where this would happen (one "concrete SQM" ->
> multiple
> >>> > SQL) would be UPDATE and DELETE queries against "multi-table
> structures"
> >>> > (inheritance, secondary tables).
> >>> >
> >>> >
> >>> >> For the purposes of OGM this phase ideally would not be tied to SQL,
> >>> >> as we phase the same task with non-SQL backends in SQL. I.e. i'd be
> >>> >> beneficial to have input -> semantic-tree ->
> >>> >> semantic-output-query-tree(s) -> (SQL|non-SQL query). There
> >>> >> "semantic-output-query-tree(s)" would be an abstract representation
> of
> >>> >> the queries to be executed, e.g. referencing the table name(s). But
> it
> >>> >> would be unaware of SQL specifics.
> >>> >
> >>> >
> >>> > OGM would be doing this.  This SQM is the end result of the shared
> >>> library.
> >>> > WHat each caller does with the SQM is up to that particular caller.
> We
> >>> > should consider moving QuerySplitter (its in my PoC, which now acts
> as
> >>> the
> >>> > PoC for using this in ORM) into the hibernate-sqm module.  Any caller
> >>> > wanting to support those unmapped class references will need to do
> the
> >>> same
> >>> > thing.
> >>>
> >>> Yes, that'd be good I think. We'd have to apply the same rules for
> >>> splitting as ORM.
> >>>
> >>> >
> >>> > BTW, another cool thing to note is the (still expanding) support for
> >>> "strict
> >>> > JPQL compliance" enforcement.
> >>>
> >>
> > _______________________________________________
> > hibernate-dev mailing list
> > hibernate-dev at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/hibernate-dev
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>