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
[EMAIL PROTECTED]
(970)226-9291
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
