[hibernate-dev] Hibernate ORM SQL generation

Tue Sep 8 16:32:44 EDT 2015

The more I think about it the more I think it makes the most sense to do
this in 2 steps/phases.  So we'll have one step that takes the SQM and
generates a "SQL tree"; and then an additional step that renders the "SQL
tree" into the SQL string (and friends).

On Sat, Sep 5, 2015 at 1:04 PM Steve Ebersole <steve at hibernate.org> wrote:

> 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
>>
>