You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Steve Clark <sc...@euler.cr.usgs.gov> on 2004/02/20 00:31:25 UTC
Extent problem with ODMG
I'm having a problem with extents in ODMG. OJB is generating
incorrect (and, in fact, invalid) SQL for my OQL query. I'm using
RC5.
My data model consists of Sites, which have collections of each of two
kinds of Accomplishments (SubSites and TechAssists). An Accomplishment
has a collection of Partners. In the reverse direction, each Partner
is associated with exactly one Accomplishment (either a SubSite or a
TechAssist), and an Accomplishment knows about its parent Site. My
repository looks like this:
<!-- - - - - - - Site - - - - - - -->
<class-descriptor
class="gov.doi.habits.dataobjects.SiteImpl"
table="SITE_DETAIL"
proxy="dynamic">
<field-descriptor
name="siteKey"
column="SITE_KEY"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"/>
<field-descriptor
name="habTypeCode"
column="HAB_TYPE_CODE"
jdbc-type="INTEGER" />
<collection-descriptor
name="subSites"
element-class-ref="gov.doi.habits.dataobjects.SubSiteImpl"
collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
<inverse-foreignkey field-ref="siteKey" />
</collection-descriptor>
<collection-descriptor
name="techAssists"
element-class-ref="gov.doi.habits.dataobjects.TechAssistImpl"
collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
<inverse-foreignkey field-ref="siteKey" />
</collection-descriptor>
</class-descriptor>
<!-- - - - - - - Accomplishment - - - - - - -->
<class-descriptor class="gov.doi.habits.dataobjects.AccomplishmentImpl" >
<extent-class class-ref="gov.doi.habits.dataobjects.SubSiteImpl" />
<extent-class class-ref="gov.doi.habits.dataobjects.AssistImpl" />
</class-descriptor>
<!-- - - - - - - SubSite - - - - - - -->
<class-descriptor
class="gov.doi.habits.dataobjects.SubSiteImpl"
table="SUB_SITE_DETAIL"
proxy="dynamic">
<field-descriptor
name="accompKey"
column="ACCOMP_KEY"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
sequence-name="SEQ_ACCOMP_DETAIL" />
<field-descriptor
name="siteKey"
column="SITE_KEY"
jdbc-type="INTEGER" />
<reference-descriptor
name="site"
class-ref="gov.doi.habits.dataobjects.SiteImpl">
<foreignkey field-ref="siteKey" />
</reference-descriptor>
<collection-descriptor
name="partners"
element-class-ref="gov.doi.habits.dataobjects.PartnerImpl"
collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
<inverse-foreignkey field-ref="accompKey" />
</collection-descriptor>
</class-descriptor>
<!-- - - - - - - TechAssist - - - - - - -->
<class-descriptor
class="gov.doi.habits.dataobjects.TechAssistImpl"
table="ASSIST_DETAIL"
proxy="dynamic">
<field-descriptor
name="accompKey"
column="ACCOMP_KEY"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"
sequence-name="SEQ_ACCOMP_DETAIL" />
<field-descriptor
name="siteKey"
column="SITE_KEY"
jdbc-type="INTEGER" />
<reference-descriptor
name="site"
class-ref="gov.doi.habits.dataobjects.SiteImpl">
<foreignkey field-ref="siteKey" />
</reference-descriptor>
<collection-descriptor
name="partners"
element-class-ref="gov.doi.habits.dataobjects.PartnerImpl"
collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
<inverse-foreignkey field-ref="accompKey" />
</collection-descriptor>
</class-descriptor>
<!-- - - - - - - Partner - - - - - - -->
<class-descriptor
class="gov.doi.habits.dataobjects.PartnerImpl"
table="PARTNER_DETAIL"
proxy="dynamic">
<field-descriptor
name="partnerKey"
column="PARTNER_KEY"
jdbc-type="INTEGER"
primarykey="true"
autoincrement="true"/>
<field-descriptor
name="accompKey"
column="ACCOMP_KEY"
jdbc-type="INTEGER"/>
<reference-descriptor
name="accomp"
class-ref="gov.doi.habits.dataobjects.AccomplishmentImpl">
<foreignkey field-ref="accompKey"/>
</reference-descriptor>
</class-descriptor>
My query looks like this:
15:41:13,896 DEBUG [] accesslayer.JdbcAccessImpl (JdbcAccessImpl.java:282) - executeQuery : Query from class gov.doi.habits.dataobjects.PartnerImpl where [accomp.site.habTypeCode IN [1]]
Note that partner.accomp is an Accomplishment (the abstract superclass);
both extents (SubSite and TechAssist) have a site relationship.
The generated SQL looks like this:
15:41:13,901 DEBUG [] sql.SqlGeneratorDefaultImpl (SqlGeneratorDefaultImpl.java:200) - SQL:SELECT DISTINCT A0.ACCOMP_KEY,A0.PARTNER_KEY FROM PARTNER_DETAIL A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL A1E1 WHERE A0.ACCOMP_KEY=A1.ACCOMP_KEY(+) AND A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND ((habTypeCode IN ( ? ) OR habTypeCode IN ( ? )))
Note that SITE_DETAIL is not even included in the query, and habTypeCode as a
result is not rewritten to the appropriate column name.
I think the correct query would be more like this:
SELECT DISTINCT A0.ACCOMP_KEY,A0.PARTNER_KEY
FROM PARTNER_DETAIL A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL A1E1,SITE_DETAIL A2
WHERE ((A0.ACCOMP_KEY=A1.ACCOMP_KEY(+) AND A1.SITE_KEY=A2.SITE_KEY) OR
(A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND A1E1.SITE_KEY=A2.SITE_KEY)) AND
(A2.HAB_TYPE_CODE IN ( ? ))
... though I'm not sure that's exactly right even.
Is there a way to get working SQL out of this OQL?
thanks,
--
Steve Clark
Technology Applications Team
Natural Resources Research Center/USGS
sclark@support.tat.fws.gov
(970)226-9291
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Extent problem with ODMG
Posted by Steve Clark <sc...@euler.cr.usgs.gov>.
I'm still having this problem, so I'm going to try again.
Using ODMG, RC5, Oracle 9i.
Summary:
- Class A has a 1-to-1 relationship 'abs' to an abstract
superclass S
- Class S has concrete subclasses B and C
- Classes B and C share a common relationship 'com' to another
class D; this relationship is defined in the superclass S
- Class D has a property 'p'
- A, B, C, and D map to distinct tables
I am trying to retrieve all A's which have a given value for 'p' in
the D associated with the related B or C (whichever one it is). So:
select x from A where A.abs.com.p = ?
A.abs has type S, the abstract superclass; A.abs.com has type D. This
means that the query needs to generate some sort of interesting join
to check for both possible paths to D (via B or C), knowing that
either B or C will have exactly one row satisfying the join
condition(s). In pseudocode:
select x from A where
if A.abs instanceof B then ((B) A.abs).com.p = ?
else if A.abs instanceof C then ((C) A.abs).com.p = ?
Should I expect this to work? The SQL query which is being generated
is not only incorrect but invalid: OJB does not rewrite 'p', and in
fact does not even mention D at all. I assume this has to do with
the fact that the repository doesn't record the presence of the
relationship 'com' in the abstract superclass, but only in the
subclasses. Queries starting from B or C and following the 'com'
relationship work fine. Am I out of luck, or is there some way I can
get a working query out of this?
thanks for any insights,
Steve Clark
Technology Applications Team
Natural Resources Research Center/USGS
sclark@support.tat.fws.gov
(970)226-9291
PS: Original message below. Name mappings:
A = PartnerImpl
B = SubSiteImpl
C = TechAssistImpl
D = SiteImpl
S = AccomplishmentImpl
abs = accomp
com = site
p = habTypeCode
sc> I'm having a problem with extents in ODMG. OJB is generating
sc> incorrect (and, in fact, invalid) SQL for my OQL query. I'm
sc> using RC5.
sc> My data model consists of Sites, which have collections of
sc> each of two kinds of Accomplishments (SubSites and
sc> TechAssists). An Accomplishment has a collection of Partners.
sc> In the reverse direction, each Partner is associated with
sc> exactly one Accomplishment (either a SubSite or a TechAssist),
sc> and an Accomplishment knows about its parent Site. My
sc> repository looks like this:
sc> <!-- - - - - - - Site - - - - - - -->
sc> <class-descriptor
sc> class="gov.doi.habits.dataobjects.SiteImpl"
sc> table="SITE_DETAIL" proxy="dynamic">
sc> <field-descriptor
sc> name="siteKey" column="SITE_KEY" jdbc-type="INTEGER"
sc> primarykey="true" autoincrement="true"/>
sc> <field-descriptor
sc> name="habTypeCode" column="HAB_TYPE_CODE"
sc> jdbc-type="INTEGER" />
sc> <collection-descriptor
sc> name="subSites"
sc> element-class-ref="gov.doi.habits.dataobjects.SubSiteImpl"
sc> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
sc> <inverse-foreignkey field-ref="siteKey" />
sc> </collection-descriptor>
sc> <collection-descriptor
sc> name="techAssists"
sc> element-class-ref="gov.doi.habits.dataobjects.TechAssistImpl"
sc> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
sc> <inverse-foreignkey field-ref="siteKey" />
sc> </collection-descriptor>
sc> </class-descriptor>
sc> <!-- - - - - - - Accomplishment - - - - - - -->
sc> <class-descriptor
sc> class="gov.doi.habits.dataobjects.AccomplishmentImpl" >
sc> <extent-class
sc> class-ref="gov.doi.habits.dataobjects.SubSiteImpl" />
sc> <extent-class
sc> class-ref="gov.doi.habits.dataobjects.AssistImpl" />
sc> </class-descriptor>
sc> <!-- - - - - - - SubSite - - - - - - -->
sc> <class-descriptor
sc> class="gov.doi.habits.dataobjects.SubSiteImpl"
sc> table="SUB_SITE_DETAIL" proxy="dynamic">
sc> <field-descriptor
sc> name="accompKey" column="ACCOMP_KEY" jdbc-type="INTEGER"
sc> primarykey="true" autoincrement="true"
sc> sequence-name="SEQ_ACCOMP_DETAIL" />
sc> <field-descriptor
sc> name="siteKey" column="SITE_KEY" jdbc-type="INTEGER" />
sc> <reference-descriptor
sc> name="site"
sc> class-ref="gov.doi.habits.dataobjects.SiteImpl">
sc> <foreignkey field-ref="siteKey" />
sc> </reference-descriptor>
sc> <collection-descriptor
sc> name="partners"
sc> element-class-ref="gov.doi.habits.dataobjects.PartnerImpl"
sc> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
sc> <inverse-foreignkey field-ref="accompKey" />
sc> </collection-descriptor>
sc> </class-descriptor>
sc> <!-- - - - - - - TechAssist - - - - - - -->
sc> <class-descriptor
sc> class="gov.doi.habits.dataobjects.TechAssistImpl"
sc> table="ASSIST_DETAIL" proxy="dynamic">
sc> <field-descriptor
sc> name="accompKey" column="ACCOMP_KEY" jdbc-type="INTEGER"
sc> primarykey="true" autoincrement="true"
sc> sequence-name="SEQ_ACCOMP_DETAIL" />
sc> <field-descriptor
sc> name="siteKey" column="SITE_KEY" jdbc-type="INTEGER" />
sc> <reference-descriptor
sc> name="site"
sc> class-ref="gov.doi.habits.dataobjects.SiteImpl">
sc> <foreignkey field-ref="siteKey" />
sc> </reference-descriptor>
sc> <collection-descriptor
sc> name="partners"
sc> element-class-ref="gov.doi.habits.dataobjects.PartnerImpl"
sc> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
sc> <inverse-foreignkey field-ref="accompKey" />
sc> </collection-descriptor>
sc> </class-descriptor>
sc> <!-- - - - - - - Partner - - - - - - -->
sc> <class-descriptor
sc> class="gov.doi.habits.dataobjects.PartnerImpl"
sc> table="PARTNER_DETAIL" proxy="dynamic">
sc> <field-descriptor
sc> name="partnerKey" column="PARTNER_KEY"
sc> jdbc-type="INTEGER" primarykey="true"
sc> autoincrement="true"/>
sc> <field-descriptor
sc> name="accompKey" column="ACCOMP_KEY"
sc> jdbc-type="INTEGER"/>
sc> <reference-descriptor
sc> name="accomp"
sc> class-ref="gov.doi.habits.dataobjects.AccomplishmentImpl">
sc> <foreignkey field-ref="accompKey"/>
sc> </reference-descriptor>
sc> </class-descriptor>
sc> My query looks like this:
sc> 15:41:13,896 DEBUG [] accesslayer.JdbcAccessImpl
sc> (JdbcAccessImpl.java:282) - executeQuery : Query from class
sc> gov.doi.habits.dataobjects.PartnerImpl where
sc> [accomp.site.habTypeCode IN [1]]
sc> Note that partner.accomp is an Accomplishment (the abstract
sc> superclass); both extents (SubSite and TechAssist) have a site
sc> relationship.
sc> The generated SQL looks like this:
sc> 15:41:13,901 DEBUG [] sql.SqlGeneratorDefaultImpl
sc> (SqlGeneratorDefaultImpl.java:200) - SQL:SELECT DISTINCT
sc> A0.ACCOMP_KEY,A0.PARTNER_KEY FROM PARTNER_DETAIL
sc> A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL A1E1 WHERE
sc> A0.ACCOMP_KEY=A1.ACCOMP_KEY(+) AND
sc> A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND ((habTypeCode IN ( ? )
sc> OR habTypeCode IN ( ? )))
sc> Note that SITE_DETAIL is not even included in the query, and
sc> habTypeCode as a result is not rewritten to the appropriate
sc> column name.
sc> I think the correct query would be more like this:
sc> SELECT DISTINCT A0.ACCOMP_KEY,A0.PARTNER_KEY
sc> FROM PARTNER_DETAIL A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL
sc> A1E1,SITE_DETAIL A2 WHERE ((A0.ACCOMP_KEY=A1.ACCOMP_KEY(+)
sc> AND A1.SITE_KEY=A2.SITE_KEY) OR
sc> (A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND
sc> A1E1.SITE_KEY=A2.SITE_KEY)) AND
sc> (A2.HAB_TYPE_CODE IN ( ? ))
sc> ... though I'm not sure that's exactly right even.
sc> Is there a way to get working SQL out of this OQL?
sc> thanks, -- Steve Clark Technology Applications Team Natural
sc> Resources Research Center/USGS sclark@support.tat.fws.gov
sc> (970)226-9291
sc> ---------------------------------------------------------------------
sc> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org For
sc> additional commands, e-mail: ojb-user-help@db.apache.org
sc> --
Steve Clark
Technology Applications Team
Natural Resources Research Center/USGS
sclark@support.tat.fws.gov
(970)226-9291
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Extent problem with ODMG
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi steve,
imo OQL is not the problem here.
have you tried to query for AccomplishmentImp, just to see if 'site' can be
resolved ? may be you should use the latest from repository.
jakob
Steve Clark wrote:
> I'm having a problem with extents in ODMG. OJB is generating
> incorrect (and, in fact, invalid) SQL for my OQL query. I'm using
> RC5.
>
> My data model consists of Sites, which have collections of each of two
> kinds of Accomplishments (SubSites and TechAssists). An Accomplishment
> has a collection of Partners. In the reverse direction, each Partner
> is associated with exactly one Accomplishment (either a SubSite or a
> TechAssist), and an Accomplishment knows about its parent Site. My
> repository looks like this:
>
> <!-- - - - - - - Site - - - - - - -->
>
> <class-descriptor
> class="gov.doi.habits.dataobjects.SiteImpl"
> table="SITE_DETAIL"
> proxy="dynamic">
>
> <field-descriptor
> name="siteKey"
> column="SITE_KEY"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"/>
>
> <field-descriptor
> name="habTypeCode"
> column="HAB_TYPE_CODE"
> jdbc-type="INTEGER" />
>
> <collection-descriptor
> name="subSites"
> element-class-ref="gov.doi.habits.dataobjects.SubSiteImpl"
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
> <inverse-foreignkey field-ref="siteKey" />
> </collection-descriptor>
>
> <collection-descriptor
> name="techAssists"
> element-class-ref="gov.doi.habits.dataobjects.TechAssistImpl"
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
> <inverse-foreignkey field-ref="siteKey" />
> </collection-descriptor>
> </class-descriptor>
>
> <!-- - - - - - - Accomplishment - - - - - - -->
>
> <class-descriptor class="gov.doi.habits.dataobjects.AccomplishmentImpl" >
>
> <extent-class class-ref="gov.doi.habits.dataobjects.SubSiteImpl" />
> <extent-class class-ref="gov.doi.habits.dataobjects.AssistImpl" />
> </class-descriptor>
>
> <!-- - - - - - - SubSite - - - - - - -->
>
> <class-descriptor
> class="gov.doi.habits.dataobjects.SubSiteImpl"
> table="SUB_SITE_DETAIL"
> proxy="dynamic">
>
> <field-descriptor
> name="accompKey"
> column="ACCOMP_KEY"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"
> sequence-name="SEQ_ACCOMP_DETAIL" />
>
> <field-descriptor
> name="siteKey"
> column="SITE_KEY"
> jdbc-type="INTEGER" />
>
> <reference-descriptor
> name="site"
> class-ref="gov.doi.habits.dataobjects.SiteImpl">
> <foreignkey field-ref="siteKey" />
> </reference-descriptor>
>
> <collection-descriptor
> name="partners"
> element-class-ref="gov.doi.habits.dataobjects.PartnerImpl"
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
> <inverse-foreignkey field-ref="accompKey" />
> </collection-descriptor>
> </class-descriptor>
>
> <!-- - - - - - - TechAssist - - - - - - -->
>
> <class-descriptor
> class="gov.doi.habits.dataobjects.TechAssistImpl"
> table="ASSIST_DETAIL"
> proxy="dynamic">
>
> <field-descriptor
> name="accompKey"
> column="ACCOMP_KEY"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"
> sequence-name="SEQ_ACCOMP_DETAIL" />
>
> <field-descriptor
> name="siteKey"
> column="SITE_KEY"
> jdbc-type="INTEGER" />
>
> <reference-descriptor
> name="site"
> class-ref="gov.doi.habits.dataobjects.SiteImpl">
> <foreignkey field-ref="siteKey" />
> </reference-descriptor>
>
> <collection-descriptor
> name="partners"
> element-class-ref="gov.doi.habits.dataobjects.PartnerImpl"
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList">
> <inverse-foreignkey field-ref="accompKey" />
> </collection-descriptor>
> </class-descriptor>
>
> <!-- - - - - - - Partner - - - - - - -->
>
> <class-descriptor
> class="gov.doi.habits.dataobjects.PartnerImpl"
> table="PARTNER_DETAIL"
> proxy="dynamic">
>
> <field-descriptor
> name="partnerKey"
> column="PARTNER_KEY"
> jdbc-type="INTEGER"
> primarykey="true"
> autoincrement="true"/>
>
> <field-descriptor
> name="accompKey"
> column="ACCOMP_KEY"
> jdbc-type="INTEGER"/>
>
> <reference-descriptor
> name="accomp"
> class-ref="gov.doi.habits.dataobjects.AccomplishmentImpl">
> <foreignkey field-ref="accompKey"/>
> </reference-descriptor>
> </class-descriptor>
>
> My query looks like this:
>
> 15:41:13,896 DEBUG [] accesslayer.JdbcAccessImpl (JdbcAccessImpl.java:282) - executeQuery : Query from class gov.doi.habits.dataobjects.PartnerImpl where [accomp.site.habTypeCode IN [1]]
>
> Note that partner.accomp is an Accomplishment (the abstract superclass);
> both extents (SubSite and TechAssist) have a site relationship.
>
> The generated SQL looks like this:
>
> 15:41:13,901 DEBUG [] sql.SqlGeneratorDefaultImpl (SqlGeneratorDefaultImpl.java:200) - SQL:SELECT DISTINCT A0.ACCOMP_KEY,A0.PARTNER_KEY FROM PARTNER_DETAIL A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL A1E1 WHERE A0.ACCOMP_KEY=A1.ACCOMP_KEY(+) AND A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND ((habTypeCode IN ( ? ) OR habTypeCode IN ( ? )))
>
> Note that SITE_DETAIL is not even included in the query, and habTypeCode as a
> result is not rewritten to the appropriate column name.
>
> I think the correct query would be more like this:
>
> SELECT DISTINCT A0.ACCOMP_KEY,A0.PARTNER_KEY
> FROM PARTNER_DETAIL A0,SUB_SITE_DETAIL A1,ASSIST_DETAIL A1E1,SITE_DETAIL A2
> WHERE ((A0.ACCOMP_KEY=A1.ACCOMP_KEY(+) AND A1.SITE_KEY=A2.SITE_KEY) OR
> (A0.ACCOMP_KEY=A1E1.ACCOMP_KEY(+) AND A1E1.SITE_KEY=A2.SITE_KEY)) AND
> (A2.HAB_TYPE_CODE IN ( ? ))
>
> ... though I'm not sure that's exactly right even.
>
> Is there a way to get working SQL out of this OQL?
>
> thanks,
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org