Re: [hibernate-dev] Hibernate ORM SQL generation

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(a)hibernate.org&gt; wrote: > On 27 August 2015 at 18:30, Steve Ebersole <steve(a)hibernate.org&gt; 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(a)hibernate.org&gt; > 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(a)hibernate.org&gt; > >> wrote: > >> > >>> 2015-08-26 14:41 GMT+02:00 Steve Ebersole <steve(a)hibernate.org&gt;: > >>> > On Wed, Aug 26, 2015 at 2:10 AM Gunnar Morling < > gunnar(a)hibernate.org&gt; > >>> 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(a)lists.jboss.org > > https://lists.jboss.org/mailman/listinfo/hibernate-dev > _______________________________________________ > hibernate-dev mailing list > hibernate-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/hibernate-dev >