On Monday, Sep 29, 2003, at 14:27 Europe/Rome, Mike Kienenberger wrote:
> Giulio Cesare Solaroli <slrgcs..bn-italy.com> wrote:
>> In EOF we have written a few qualifiers (in Cayenne they are called
>> expressions, if I get it right) like:
>> - empty relationship;
>> - not empty relationship;
>> - match all values;
>> - no match for value;
>
> Take a look at
>
> http://www.objectstyle.org/cayenne/userguide/expressions/shortcut.html
>
> and the rest of chapter 11. I think some of what you're looking for
> is in
> there.
I have looked there, but I don't think that the current Cayenne
implementation of "empty relationship" is the same as what I am looking
for:
From a post on Cayenne-user mailing list (2003/06/0028)
> just tried this out on MySQL. It works, but... Due to the relational
> logic
> specifics, "noMatchExp" returns all users who are at least in one of
> teams
> that do not match the criteria. If you turn on the SQL logging per
>
> http://objectstyle.org/cayenne/userguide/deploy/logging.html
>
> You will the SQL generated for the query:
> SELECT DISTINCT t0.pass, t0.username, t0.userid FROM USERS t0, USERTEAM
> t1, TEAMS t2 WHERE t0.userid = t1.users_userid AND t1.teams_teamid =
> t2.teamid AND (t2.teamname <> ?) [bind: 'a']
>
> There is indeed a mismatch between relational and common logic here
> :-).
Instead, our implementation of the "empty relationship qualifier" would
generate a SQL statement like this:
SELECT DISTINCT t0.pass, t0.username, t0.userid
FROM USERS t0
WHERE not exists (SELECT t2.teamid
FROM USERTEAM t1, TEAMS t2
WHERE t0.userid = t1.users_userid
AND t1.teams_teamid = t2.teamid
AND t2.teamname = ?)
This because there is a to-many relationship; if it was a simple to-one
(or chains of many to-one relationship) a simple join would be enough.
The same for all the other expressions/qualifiers; the problem is to
create "sofisticated" queries in order to match the "relational and
common logic" (as stated in the referenced post) even when to-many
relationships are involved in the expressions.
I can see a problem with DB engines not supporting nested queries or
exists operator, but otherwise there expressions can be really helpful,
as we have experienced directly.
>> Plus, we have extended the editing context to implement a few method
>> to:
>> - return the number of records/objects matching a given
>> qualifier/expression (select count(*));
This should be not a big problem; in EOF we did create a clone of any
entity involved in the count operation, replace every attribute with a
dummy "count(*)" attribute and add a new method to the editing context
to select the right "modified" entity, issue the select, read the
result and return it as a number. Nothing really DB dependent.
>> - return if a given expression matches any value (select dummy from
>> dual where exists(...); similar to a select count(*) checking if the
>> result is greater than zero, but much more efficient, as it returs as
>> soon as it finds a match).
>
> I don't know the answer to this, but I would guess the Expression
> class or
> one of the others in the exp package. Your extensions sound like
> useful
> additions to Expression providing you can implement them in a database
> independent way (the second one sounds a little tricky and looks
> Oracle-specific).
It's true that the dual table exists only on Oracle (by default), but
it is also a really simple table to create from scratch: one table, one
column, one raw. The real problem could be the lack of the "exists"
operator and the correct handling of subqueries.
The problem is that I though I could create subclasses of Expression;
but Cayenne seems to take a different approach, using the
ExpressionFactory class.
I am not a Java expert and I could miss some common pattern here, but
how do I arrange the code to do the above?
Thanks for your attention.
Giulio Cesare Solaroli
This archive was generated by hypermail 2.0.0 : Mon Sep 29 2003 - 09:02:01 EDT