You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by Adam Heath <do...@brainfood.com> on 2007/10/18 09:39:49 UTC
ofbiz sql
David mentioned recently that the delegator has quite a number of
methods in it; this can be daunting to new users.
If it could handle sql natively, then this could be reduced.
EntityCondition condition = new EntityConditionList(
UtilMisc.toList(
new EntityExpr("firstName", EntityOperator.LIKE, "%Adam%"),
new EntityExpr("lastName", EntityOperator.LIKE, "%Heath%")
),
EntityOperator.AND
);
List people = delegator.findByConditionCache("Person", condition, null,
UtilMisc.toList("firstName", "lastName"));
... or ....
Query query = delegator.compileSql("SELECT * FROM Person WHERE firstName
LIKE $firstName AND lastName LIKE $lastName ORDER BY firstName, lastName");
query.setParameter("firstName", "%Adam%");
query.setParameter("lastName", "%Heath%");
List people = delegator.runQuery(query);
The compiled query could be cached, or created during an init phase;
this would speed things up. Additionally, views could also be done with
this sql code. And the sql string could be generated by a template;
meaning that whole parts of it might be conditional, using
freemarker/velocity conditions, both on joins, and regular group
by/order by/select fields/conditions.
Re: ofbiz sql
Posted by Adrian Crum <ad...@hlmksw.com>.
There was some effort a while ago to have the EE support running on an LDAP datastore. How would
this work with a (possible future) LDAP datastore?
Adam Heath wrote:
> David mentioned recently that the delegator has quite a number of
> methods in it; this can be daunting to new users.
>
> If it could handle sql natively, then this could be reduced.
>
> EntityCondition condition = new EntityConditionList(
> UtilMisc.toList(
> new EntityExpr("firstName", EntityOperator.LIKE, "%Adam%"),
> new EntityExpr("lastName", EntityOperator.LIKE, "%Heath%")
> ),
> EntityOperator.AND
> );
> List people = delegator.findByConditionCache("Person", condition, null,
> UtilMisc.toList("firstName", "lastName"));
>
> ... or ....
>
> Query query = delegator.compileSql("SELECT * FROM Person WHERE firstName
> LIKE $firstName AND lastName LIKE $lastName ORDER BY firstName, lastName");
> query.setParameter("firstName", "%Adam%");
> query.setParameter("lastName", "%Heath%");
> List people = delegator.runQuery(query);
>
> The compiled query could be cached, or created during an init phase;
> this would speed things up. Additionally, views could also be done with
> this sql code. And the sql string could be generated by a template;
> meaning that whole parts of it might be conditional, using
> freemarker/velocity conditions, both on joins, and regular group
> by/order by/select fields/conditions.
>
Re: ofbiz sql
Posted by Jacques Le Roux <ja...@les7arts.com>.
> > In my new feature(which is not done, and I don't know when I'll get back to
> > working on it), you can just write sql code that does a join itself.
>
> If the point is to circumvent entity views, then I would advise against it. I'd rather enhance the
> entity view engine.
>
> Being able to declaratively define a view in a consolidated area is great for code organization
> and self-evident docs.
This is an interesting POV
Thanks Jonathon
Jacques
>
> Jonathon
>
> Adam Heath wrote:
> > Jacques Le Roux wrote:
> >> De : "Adam Heath" <do...@brainfood.com>
> >>
> >>> Jacques Le Roux wrote:
> >>>> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a
> >> changes
> >>>> implie
> >>>> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
> >>> I don't think you understand.
> >> Mmm..., do you think so ?
> >>
> >>> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
> >>> sql dialect. Ofbiz would then translate that on the fly to the correct
> >>> underlying form.
> >> In your example the difference is very light !
> >
> > In OfBiz, you can create views; this has to be done by editing an
> > entitymodel.xml file. This is more difficult than I care for.
> >
> > In my new feature(which is not done, and I don't know when I'll get back
> > to working on it), you can just write sql code that does a join itself.
> >
> >
>
Re: ofbiz sql
Posted by Jonathon -- Improov <jo...@improov.com>.
> In my new feature(which is not done, and I don't know when I'll get back to
> working on it), you can just write sql code that does a join itself.
If the point is to circumvent entity views, then I would advise against it. I'd rather enhance the
entity view engine.
Being able to declaratively define a view in a consolidated area is great for code organization
and self-evident docs.
Jonathon
Adam Heath wrote:
> Jacques Le Roux wrote:
>> De : "Adam Heath" <do...@brainfood.com>
>>
>>> Jacques Le Roux wrote:
>>>> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a
>> changes
>>>> implie
>>>> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
>>> I don't think you understand.
>> Mmm..., do you think so ?
>>
>>> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
>>> sql dialect. Ofbiz would then translate that on the fly to the correct
>>> underlying form.
>> In your example the difference is very light !
>
> In OfBiz, you can create views; this has to be done by editing an
> entitymodel.xml file. This is more difficult than I care for.
>
> In my new feature(which is not done, and I don't know when I'll get back
> to working on it), you can just write sql code that does a join itself.
>
>
Re: ofbiz sql
Posted by Adam Heath <do...@brainfood.com>.
Jacques Le Roux wrote:
> De : "Adam Heath" <do...@brainfood.com>
>
>> Jacques Le Roux wrote:
>>> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a
> changes
>>> implie
>>> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
>> I don't think you understand.
>
> Mmm..., do you think so ?
>
>> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
>> sql dialect. Ofbiz would then translate that on the fly to the correct
>> underlying form.
>
> In your example the difference is very light !
In OfBiz, you can create views; this has to be done by editing an
entitymodel.xml file. This is more difficult than I care for.
In my new feature(which is not done, and I don't know when I'll get back
to working on it), you can just write sql code that does a join itself.
Re: ofbiz sql
Posted by Jacques Le Roux <ja...@les7arts.com>.
De : "Adam Heath" <do...@brainfood.com>
> Jacques Le Roux wrote:
> > Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a
changes
> > implie
> > major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
>
> I don't think you understand.
Mmm..., do you think so ?
>
> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
> sql dialect. Ofbiz would then translate that on the fly to the correct
> underlying form.
In your example the difference is very light !
Jacques
Re: ofbiz sql
Posted by Adrian Crum <ad...@hlmksw.com>.
Adam,
That makes it much clearer. In that case, you have my vote.
+1
-Adrian
Adam Heath wrote:
> Jacques Le Roux wrote:
>
>>Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a changes
>>implie
>>major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
>
>
> I don't think you understand.
>
> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
> sql dialect. Ofbiz would then translate that on the fly to the correct
> underlying form.
>
Re: ofbiz sql
Posted by Jacques Le Roux <ja...@les7arts.com>.
Hi Karl,
I have just read you document, very interesting. I would like to integrate your work in OFBiz. It's well documented and a must when
it comes to optimise performances.
My primarily intention is hence to integrate not as a subsititure in any way (this is clearly explained in the documentation) but as
an option when you need more SQL power and control.
Of course everybody using it should be aware of the main limitation : no ECAs are fired !
Other limitations or warning are cleary underlined (I like icons you used like in a professionnal doc. ;o) in the documentation.
If nobody complains I will commit in some days
Thanks
Jacques
De : "Eilebrecht, Karl (Key-Work)" <ka...@key-work.de>
> The issue "JIRA-1033 Ofbiz SQL Integration Features"
> might be interesting for your discussion.
>
> Maybe this is a basis for supporting multiple ways to solve common problems
> (RDBMS *dependent* (much better to debug and optimize) vs. RDBMS-independent "HQL-like").
>
> Remark: in 2005/06 we first tried to solve our query problems by heavily using Dynamic View Entities. One major problem (besides
the readability/optimize problems) with this approach was that all conditions (also "Join conditions") are applied to the WHERE
clause. Some databases react with bad performance on such SQL-statements, others "auto-optimize" them quietly. However, I think it's
a good idea (when reworking the entity engine) to enhance the ability to have join conditions as a part of the view entities'
"metadata".
>
> Regards.
> Karl
>
>
> -----Ursprüngliche Nachricht-----
> Von: Jonathon -- Improov [mailto:jonw@improov.com]
> Gesendet: Montag, 22. Oktober 2007 05:30
> An: dev@ofbiz.apache.org
> Betreff: Re: ofbiz sql
>
> I've read a similar thread (and possibly responded to it).
>
> I vote for it. The point is to be able to programmatically generate queries to the Entity Engine.
> Using raw SQL will of course be RDBMS-dependent. Using an OFBiz SQL dialect will be RDBMS-independent.
>
> That's what little I remember from the thread I read and/or responded to.
>
> Jonathon
>
> Adam Heath wrote:
> > Jacques Le Roux wrote:
> >> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a
changes
> >> implie
> >> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
> >
> > I don't think you understand.
> >
> > You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
> > sql dialect. Ofbiz would then translate that on the fly to the correct
> > underlying form.
> >
> >
>
>
> Besuchen Sie uns:
>
> - Mail Order World in Wiesbaden vom 24.-25.10.2007
> Halle 5, Stand 510
> http://www.key-work.de/mow
>
> - Effizienztag Mode/Modehandels-Kongress
> in Düsseldorf vom 7.11.-8.11.2007, Stand 16
> http://www.key-work.de/mhk
>
> - IMB-Forum in Köln vom 21.-22.11.2007
> Halle 08, Stand Boulevard Gang A Nr. 29
> http://www.key-work.de/imb
>
>
> --
> Karl Eilebrecht
> Key-Work Consulting GmbH
>
> Kriegsstr. 100 - 76133 Karlsruhe - Germany
> Fon: +49-721-78203-277 - Fax: +49-721-78203-10
> karl.eilebrecht@key-work.de
>
>
> Key-Work Consulting GmbH Karlsruhe, HRB 108695, HRG Mannheim
> Geschäftsführer: Andreas Stappert, Tobin Wotring
>
>
>
Re: AW: ofbiz sql
Posted by Jonathon -- Improov <jo...@improov.com>.
> One major problem (besides the readability/optimize problems) with this
> approach was that all conditions (also "Join conditions") are applied to the
> WHERE clause. Some databases react with bad performance on such
> SQL-statements, others "auto-optimize" them quietly.
That's what I thought too, about the bad performance. View entities (dynamic or otherwise) in
OFBiz do not allow for "join conditions" (eg join table_a on "some condition").
See a thread I created at http://www.nabble.com/forum/ViewPost.jtp?post=12018323&framed=y .
Jonathon
Eilebrecht, Karl (Key-Work) wrote:
> The issue "JIRA-1033 Ofbiz SQL Integration Features"
> might be interesting for your discussion.
>
> Maybe this is a basis for supporting multiple ways to solve common problems
> (RDBMS *dependent* (much better to debug and optimize) vs. RDBMS-independent "HQL-like").
>
> Remark: in 2005/06 we first tried to solve our query problems by heavily using Dynamic View Entities. One major problem (besides the readability/optimize problems) with this approach was that all conditions (also "Join conditions") are applied to the WHERE clause. Some databases react with bad performance on such SQL-statements, others "auto-optimize" them quietly. However, I think it's a good idea (when reworking the entity engine) to enhance the ability to have join conditions as a part of the view entities' "metadata".
>
> Regards.
> Karl
>
>
> -----Ursprüngliche Nachricht-----
> Von: Jonathon -- Improov [mailto:jonw@improov.com]
> Gesendet: Montag, 22. Oktober 2007 05:30
> An: dev@ofbiz.apache.org
> Betreff: Re: ofbiz sql
>
> I've read a similar thread (and possibly responded to it).
>
> I vote for it. The point is to be able to programmatically generate queries to the Entity Engine.
> Using raw SQL will of course be RDBMS-dependent. Using an OFBiz SQL dialect will be RDBMS-independent.
>
> That's what little I remember from the thread I read and/or responded to.
>
> Jonathon
>
> Adam Heath wrote:
>> Jacques Le Roux wrote:
>>> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a changes
>>> implie
>>> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
>> I don't think you understand.
>>
>> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
>> sql dialect. Ofbiz would then translate that on the fly to the correct
>> underlying form.
>>
>>
>
>
> Besuchen Sie uns:
>
> - Mail Order World in Wiesbaden vom 24.-25.10.2007
> Halle 5, Stand 510
> http://www.key-work.de/mow
>
> - Effizienztag Mode/Modehandels-Kongress
> in Düsseldorf vom 7.11.-8.11.2007, Stand 16
> http://www.key-work.de/mhk
>
> - IMB-Forum in Köln vom 21.-22.11.2007
> Halle 08, Stand Boulevard Gang A Nr. 29
> http://www.key-work.de/imb
>
>
AW: ofbiz sql
Posted by "Eilebrecht, Karl (Key-Work)" <ka...@key-work.de>.
The issue "JIRA-1033 Ofbiz SQL Integration Features"
might be interesting for your discussion.
Maybe this is a basis for supporting multiple ways to solve common problems
(RDBMS *dependent* (much better to debug and optimize) vs. RDBMS-independent "HQL-like").
Remark: in 2005/06 we first tried to solve our query problems by heavily using Dynamic View Entities. One major problem (besides the readability/optimize problems) with this approach was that all conditions (also "Join conditions") are applied to the WHERE clause. Some databases react with bad performance on such SQL-statements, others "auto-optimize" them quietly. However, I think it's a good idea (when reworking the entity engine) to enhance the ability to have join conditions as a part of the view entities' "metadata".
Regards.
Karl
-----Ursprüngliche Nachricht-----
Von: Jonathon -- Improov [mailto:jonw@improov.com]
Gesendet: Montag, 22. Oktober 2007 05:30
An: dev@ofbiz.apache.org
Betreff: Re: ofbiz sql
I've read a similar thread (and possibly responded to it).
I vote for it. The point is to be able to programmatically generate queries to the Entity Engine.
Using raw SQL will of course be RDBMS-dependent. Using an OFBiz SQL dialect will be RDBMS-independent.
That's what little I remember from the thread I read and/or responded to.
Jonathon
Adam Heath wrote:
> Jacques Le Roux wrote:
>> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a changes
>> implie
>> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
>
> I don't think you understand.
>
> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
> sql dialect. Ofbiz would then translate that on the fly to the correct
> underlying form.
>
>
Besuchen Sie uns:
- Mail Order World in Wiesbaden vom 24.-25.10.2007
Halle 5, Stand 510
http://www.key-work.de/mow
- Effizienztag Mode/Modehandels-Kongress
in Düsseldorf vom 7.11.-8.11.2007, Stand 16
http://www.key-work.de/mhk
- IMB-Forum in Köln vom 21.-22.11.2007
Halle 08, Stand Boulevard Gang A Nr. 29
http://www.key-work.de/imb
--
Karl Eilebrecht
Key-Work Consulting GmbH
Kriegsstr. 100 - 76133 Karlsruhe - Germany
Fon: +49-721-78203-277 - Fax: +49-721-78203-10
karl.eilebrecht@key-work.de
Key-Work Consulting GmbH Karlsruhe, HRB 108695, HRG Mannheim
Geschäftsführer: Andreas Stappert, Tobin Wotring
Re: ofbiz sql
Posted by Jonathon -- Improov <jo...@improov.com>.
I've read a similar thread (and possibly responded to it).
I vote for it. The point is to be able to programmatically generate queries to the Entity Engine.
Using raw SQL will of course be RDBMS-dependent. Using an OFBiz SQL dialect will be RDBMS-independent.
That's what little I remember from the thread I read and/or responded to.
Jonathon
Adam Heath wrote:
> Jacques Le Roux wrote:
>> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a changes
>> implie
>> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
>
> I don't think you understand.
>
> You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
> sql dialect. Ofbiz would then translate that on the fly to the correct
> underlying form.
>
>
Re: ofbiz sql
Posted by Adam Heath <do...@brainfood.com>.
Jacques Le Roux wrote:
> Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a changes
> implie
> major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
I don't think you understand.
You wouldn't be writing sql to the raw jdbc. You'd be writing an Ofbiz
sql dialect. Ofbiz would then translate that on the fly to the correct
underlying form.
Re: ofbiz sql
Posted by Jacques Le Roux <ja...@les7arts.com>.
Was not delegator methods thought to hide SQL code ? I think that David meant to prune not to replace. In both case such a changes
implie
major tedious refactoring. Though using deprecated concept it could be carried on carefully in time...
I like the idea of replacing the cache avatars by a parameter. It implies a lot of tedious refactoring too...
For the idea below, I like it as enhancement (compiled query being a must) not a replacement, which is a paradox because this
thread was about to prune.
Jacques
PS : I added David's initial message a bottom to not lose the thread
+1 for the "less is more" concept. I guess deprecating is the way, no time to fill the list yet...
> David mentioned recently that the delegator has quite a number of
> methods in it; this can be daunting to new users.
>
> If it could handle sql natively, then this could be reduced.
>
> EntityCondition condition = new EntityConditionList(
> UtilMisc.toList(
> new EntityExpr("firstName", EntityOperator.LIKE, "%Adam%"),
> new EntityExpr("lastName", EntityOperator.LIKE, "%Heath%")
> ),
> EntityOperator.AND
> );
> List people = delegator.findByConditionCache("Person", condition, null,
> UtilMisc.toList("firstName", "lastName"));
>
> ... or ....
>
> Query query = delegator.compileSql("SELECT * FROM Person WHERE firstName
> LIKE $firstName AND lastName LIKE $lastName ORDER BY firstName, lastName");
> query.setParameter("firstName", "%Adam%");
> query.setParameter("lastName", "%Heath%");
> List people = delegator.runQuery(query);
>
> The compiled query could be cached, or created during an init phase;
> this would speed things up. Additionally, views could also be done with
> this sql code. And the sql string could be generated by a template;
> meaning that whole parts of it might be conditional, using
> freemarker/velocity conditions, both on joins, and regular group
> by/order by/select fields/conditions.
>
De : "David E Jones" <jo...@hotwaxmedia.com>
>
> This has been discussed before, but I thought it might be a good time
> to bring it up again based on Adam Heath's recent additions to the
> GenericDelegator (in revs r585808, r585803, r585802, r585759).
>
> One of the issue with the Entity Engine that has been getting worse
> over the years is method bloat. There are WAY too many variations of
> different methods, IMO. These have been added for convenience, but
> the net effect (that I don't think was expected or even considered)
> is that when looking at the massive list it is tricky to figure out
> which methods to use for what. This is a big problem for people new
> to the Entity Engine, but also a problem for experienced EE users.
>
> In short lately I'm thinking that having so many methods is worse
> than the convenience they offer to make life easier for "lazy"
> coders. Actually, with a decent IDE having lots of parameters isn't
> such a big deal.
>
> This does have the down side of requiring changes to lots of existing
> code to use the fully featured methods instead of the simplified ones
> (most of which just call the full featured ones with default values).
>
> The up side is we end up with a really happy and clean
> GenericDelegator class with only maybe 10-15 methods for different
> general operations, and perhaps even less than that, like 5-10...
> (aside from the cache maintenance methods, and other utility methods,
> many of which we might want to make private, the general goal being
> to simplify the class).
>
> In shorter I think this is getting to be one of those cases where
> less is more...
>
> Any thoughts? Should we start mass-marking methods as deprecated?
> Which ones and in what forms of the methods should we leave?
>
> -David
>
>
Re: ofbiz sql
Posted by Adam Heath <do...@brainfood.com>.
BJ Freeman wrote:
> there is a sql ability in the webtools
> so I am sure that can be implemented in code.
That is not the same thing. That does a *raw* query to jdbc.
What I am talking about, is having the delegator parse the sql string,
do all mappings(entityname -> table name, field name -> column name,
etc), and then do the query.
This would even work for views. For example:
SELECT * FROM PartyContactWithPurpose;
That would be auto-converted to the appropriate real sql code on the
back end. You could also write sql code that does a join inline:
SELECT
a.partyId, a.partyTypeId,
b.firstName, b.lastName,
c.groupName
FROM
Party a LEFT JOIN Person b USING (partyId)
LEFT JOIN PartyGroup c USING (partyId)
;
Re: ofbiz sql
Posted by BJ Freeman <bj...@free-man.net>.
there is a sql ability in the webtools
so I am sure that can be implemented in code.
Adam Heath sent the following on 10/18/2007 12:39 AM:
> David mentioned recently that the delegator has quite a number of
> methods in it; this can be daunting to new users.
>
> If it could handle sql natively, then this could be reduced.
>
> EntityCondition condition = new EntityConditionList(
> UtilMisc.toList(
> new EntityExpr("firstName", EntityOperator.LIKE, "%Adam%"),
> new EntityExpr("lastName", EntityOperator.LIKE, "%Heath%")
> ),
> EntityOperator.AND
> );
> List people = delegator.findByConditionCache("Person", condition, null,
> UtilMisc.toList("firstName", "lastName"));
>
> ... or ....
>
> Query query = delegator.compileSql("SELECT * FROM Person WHERE firstName
> LIKE $firstName AND lastName LIKE $lastName ORDER BY firstName, lastName");
> query.setParameter("firstName", "%Adam%");
> query.setParameter("lastName", "%Heath%");
> List people = delegator.runQuery(query);
>
> The compiled query could be cached, or created during an init phase;
> this would speed things up. Additionally, views could also be done with
> this sql code. And the sql string could be generated by a template;
> meaning that whole parts of it might be conditional, using
> freemarker/velocity conditions, both on joins, and regular group
> by/order by/select fields/conditions.
>
>