On Nov 1, 2003, at 5:29 AM, Mirko Viviani wrote:
> Ciao!
>
> In attach there is a patch to support Left/Right/Full outer joins for
> both SQL92 and pre standard (EOF style).
>
> For SQL92 it builds the join constructor in the from clause, for pre
> standard it uses the classic where classic with =, *= and =*
> operators.
> I've tested it with postgres 7.3 with only _one_ join and it works.
> The same should be true for db2 (I'll test next week), oracle 9i and
> firebird.
> Problems arises with non standard implementation (ie Oracle pre 9).
> The SQL92 implementation of this patch fails with complex qualifiers
> (ie if you have more than one relationship of the same entity inside
> the join clause).
> The patch is against v1.0.2 (no modeler patch)
> Any comments are welcome.
>
> Ciao
> Mirko
Hi Mirko,
I understand that the main motivation here is to add support for outer
joins (since inner join with "=" syntax works on every database I
know). This is very cool! It was one of those things on the TODO list
that was constantly postponed. I am glad we will have it, the lack of
outer joins can really cripple lots of things.
Since this is a big overhaul of the translator code and introduces the
change in the mapping, I suggest we implement this on 1.1 HEAD branch,
and do release it in 1.0.x. As a reminder to the people still crippled
by the broken anonymous CVS access, 12-hr snapshots of both STABLE and
HEAD branches are available from here:
http://objectstyle.org/downloads/cayenne/cvs-snapshots/
Now since we want all our unit tests to pass consistently, and you are
saying that only the simple cases work now (I should've really tried it
myself before making any comments...), we need some integration path. I
guess for now we can apply the patches to the map.* and dba.* package,
and wait till translator stabilizes. Does it sound reasonable?
** A few notes
Frankly I never used OUTER JOINS in O/R situations, only in straight
SQL. I can definitely see how this can be useful in qualifiers. But how
about resolving relationships? E.g. when resolving a to-many
relationship based on a LEFT OUTER JOIN, I don't think we should
include NULLs in the relationship array (this would be confusing and I
don't see any use for it outside of SQL), thus such relationship
resolution would effectively follow the inner join semantics. On the
other hand RIGHT OUTER JOIN on "to many" should probably return *all*
rows from the target table...
Truly the case when relational logic of arbitrary data morphing meets
O/R logic based on graphs! :-)
To help this mess, I think it would be helpful if we could define this
semantics in a simple OO terms understandable by users (just like with
complex expressions that were introduced by Giulio Cesare), so that the
term for a given relationship would reflect what objects it returns...
Don't know if this is even possible? Maybe we should indeed limit the
OUTER JOINS to qualifiers only, and consistently use inner joins in
relationship traversal. Ideas?
Thanks
Andrus
This archive was generated by hypermail 2.0.0 : Sat Nov 01 2003 - 17:01:33 EST