Re: Extent problem with ODMG

Jakob Braeuchi Fri, 20 Feb 2004 13:09:19 -0800

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:

<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>



<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>

<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>


<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>


<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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Extent problem with ODMG

Reply via email to