hi steve,
Steve Clark wrote:
Jakob,
Steve> I have an inheritance hierarchy that I'm having real problems Steve> mapping correctly (well, let's say usefully). I have an Steve> abstract superclass Sup with two subclasses Sub1 and Sub2. Steve> Elsewhere in the model, I have classes which have relationships Steve> to Sup, to Sub1, and to Sub2. I need
Jakob> when these classes refer to Sup are you also interested in Jakob> the extents ?
I am interested in having collections of Sup. Sup is abstract, and so the elements of these collections will be instances of Sub1 and Sub2. So yes, I'm interested in the extends.
Steve> to be able to query against things like A.Sup.C.attr1, Steve> A.Sup.attr2, B.Sub1.C.attr1, and B.Sub1.attr3; and I need to Steve> have collections of Sup's as well as collections of Sub1's.
Jakob> you could try to use pathclass-hints to define exactly what Jakob> class you expect for a given path-segment.
Can you do this in ODMG? Can you indicate both classes/extents? One
well it's not so elegant but you can call OQLQueryImpl#getQuery and cast to QueryByCriteria.
of the problems comes down to this: Sup has a relationship to C, which means that both Sub1 and Sub2 have this relationship. In a report query, I want to retrieve something like "A.Sup.C.attr where A.Sup.C.attr in (some set)". So I actually want SQL that looks at A.Sub1.C.attr and A.Sub2.C.attr. The WHERE clause (at least usually) looks fine, but what gets retrieved may end up being A.Sub1.C.attr or
the where clause looks ok in your previous posts.
the problem is the alias of the selected column. sometimes it's C1.VALUE, sometimes C2.VALUE; but i've to admit, i do not know which one is correct. actually, i think it cannot be done in sql with just one single column :(
you could try using two different queries each with the pathClass-hint for A.Sup.C pointing to the desired class.
jakob
it may end up being A.Sub2.C.attr; this is not reliably predictable, and is not sensitive to which path actually satisfied the WHERE.
Steve> Currently, I've mapped this to two tables, SUB_1 and SUB_2. I Steve> have defined Sup as having two extents, Sub1 and Sub2. I have Steve> replicated all of the shared reference- and Steve> collection-descriptors in all three class-descriptors. My Steve> problem (see an email I sent yesterday) is that queries through Steve> a shared relationship from Sup end up with non-deterministic Steve> outer joins (is it A3.attr1 or A3C0.attr1?); indeed, I'm not Steve> clear that a correct query can always be written.
Jakob> could you please post the sql.
I'll attach a message below that I sent last week which describes an earlier phase of this problem. It includes SQL and some more analysis.
thanks, -steve
-------------------- snip snip --------------------
From: "Steve Clark" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: Non-deterministic queries generated for references through extents Date: Thu, 20 May 2004 16:03:16 -0600
I have encountered a situation where OJB at different times generates two different SQL queries from the same Criteria object, I guess according to the order of some (unordered) collection.
My repository is like this:
<!-- A is the top-level object. It has a reference bRef, --> <!-- which contains either a BSub1 or a BSub2, which are the --> <!-- two subclasses of BSuper -->
<class-descriptor class="A"> <field-descriptor name="aKey" column="A_KEY" primarykey="true" /> <field-descriptor name="bKey" column="B_KEY" /> <field-descriptor name="data" column="DATA" />
<reference-descriptor name="bRef" class-ref="BSuper"> <foreign-key field-ref="bKey"/> </reference-descriptor> </class-descriptor>
<!-- BSuper is the superclass of the two possible B classes -->
<class-descriptor class="BSuper"> <extent-class class-ref="BSub1" /> <extent-class class-ref="BSub2" />
<field-descriptor name="bKey" column="B_KEY" primarykey="true" />
<reference-descriptor name="cRef" class-ref="C"> <foreign-key field-ref="cKey"/> </reference-descriptor> </class-descriptor>
<!-- BSub1 is one of the possible B classes -->
<class-descriptor class="BSub1"> <field-descriptor name="bKey" column="B_KEY" primarykey="true" />
<field-descriptor name="cKey" column="C_KEY" />
<reference-descriptor name="cRef" class-ref="C"> <foreign-key field-ref="cKey"/> </reference-descriptor> </class-descriptor>
<!-- BSub2 is one of the possible B classes -->
<class-descriptor class="BSub2"> <field-descriptor name="bKey" column="B_KEY" primarykey="true" />
<field-descriptor name="cKey" column="C_KEY" />
<reference-descriptor name="cRef" class-ref="C"> <foreign-key field-ref="cKey"/> </reference-descriptor> </class-descriptor>
<!-- Both BSub1 and BSub2 have a reference to a C -->
<class-descriptor class="C"> <field-descriptor name="cKey" column="C_KEY" primarykey="true" /> <field-descriptor name="value" column="VALUE" /> </class-descriptor>
Given this setup, I now want to write a Report Query to retrieve A.data and C.value, and only for certain values of C.value. I don't care whether I got to C via BSub1 or via BSub2. So I use Criteria.addIn("bRef.cRef.value", values).
The SQL which is generated for my Report Query is non-deterministic. Sometimes it looks like this:
SELECT A.DATA, C1.VALUE FROM A, B1, C C1, B2, C C2 WHERE A.B_KEY = B1.B_KEY(+) AND B1.C_KEY = C1.C_KEY(+) AND A.B_KEY = B2.B_KEY(+) AND B2.C_KEY = C2.C_KEY(+) AND ((C1.VALUE IN (...)) OR (C2.VALUE IN (...)));
and sometimes it looks like this:
SELECT A.DATA, C2.VALUE FROM A, B1, C C1, B2, C C2 WHERE A.B_KEY = B1.B_KEY(+) AND B1.C_KEY = C1.C_KEY(+) AND A.B_KEY = B2.B_KEY(+) AND B2.C_KEY = C2.C_KEY(+) AND ((C1.VALUE IN (...)) OR (C2.VALUE IN (...)));
What it should look like is this:
SELECT A.DATA, C.VALUE FROM A, B1, B2, C WHERE ((A.B_KEY = B1.B_KEY(+) AND B1.C_KEY = C.C_KEY) OR (A.B_KEY = B2.B_KEY(+) AND B2.C_KEY = C.C_KEY); AND C.VALUE IN (...);
(I think ... I'm not so great with outer joins, so I'm not positive on this one). As a result of this problem, sometimes I get valid data from my query and sometimes all of the C.VALUEs are null, because I get values that match on one of the two routes to C but the values that are actually returned are from the other branch, where the outer join supplies NULL for the (otherwise non-matching) rows.
I'm using CVS HEAD as of yesterday. Any insights?
thanks,
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
