Re: Problems generating sql with the SqlGenerator

Armin Waibel <[email protected]>
Newsgroups gmane.comp.jakarta.ojb.user
Message-ID <[email protected]> 
Hi Josef,

could you clarify your problem: The generated sql is only invalid when 
you add your extension or is the generated sql invalid in general?

regards,
Armin

Josef Wagner wrote:
> Hi List,
> 
> i have written my one SqlGenerator, because i have to extend each query 
> width the criteria, that the status of the entry, which is described in 
> a seperate log-table is not 'DELETED'.
> ------------------
> Problem
> ------------------
> Now, my problem is, that the generated statemts are incorecct.
> I extend from the SqlGeneratorDefaultImpl and override the Method 
> getPreparedSelectStatement as followed:
> 
>   public String getPreparedSelectStatement(Query query, ClassDescriptor 
> cld) {
>       try {
>       if ((cld.getClassOfObject().newInstance() instanceof 
> LogableAbstractPoIfc)) {
>           query.getCriteria().addEqualTo("log.log_table", 
> OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
>           query.getCriteria().addNotEqualTo("log.log_status", "D");
>       }
>       }              catch(Exception iaex) {
>           logger.warn("Statement wurde evtl. nicht korrekt 
> aufbereitet");                      logger.warn(iaex);                  }
>             SqlCacheKey key = new SqlCacheKey(query, cld, 
> SqlCacheKey.TYPE_SELECT);
>       String result = (String) m_sqlCacheMap.get(key);
>       if (result == null) {
>           SqlStatement sql = new SqlSelectStatement(getPlatform(), cld, 
> query, logger);
>           result = sql.getStatement();
>           if (logger.isDebugEnabled()) {
>               logger.debug("SQL:" + result);
>           }
>           m_sqlCacheMap.put(key, result);
>       }
>       return result;
>   }
> 
> The following incorrect statment will be generated, wenn calling
> new User(1).getGroups().size();
> 
> -------------
> Incorrect generated sql-statement
> -------------
> SELECT count(*) FROM groups A0,users_groups INNER JOIN log 
> A1,users_groups ON A0.obj_id=A1.log_table_obj_id WHERE 
> (((users_groups.users_obj_id = 1) AND users_groups.groups_obj_id = 
> A0.obj_id) AND log_table = 'GrOUPS') AND log_funktion <> 'D'
> 
> ------------
> statement i would expect
> ------------
> SELECT count(*) FROM groups A0 INNER JOIN log A1 ON 
> A0.obj_id=A1.log_table_obj_id INNER JOIN users_groups ON A0.obj_id= 
> users_groups.groups_obj_id WHERE (((users_groups.users_obj_id = 1) AND 
> users_groups.groups_obj_id = A0.obj_id) AND log_table = 'GROUPS') AND 
> log_funktion <> 'D'
> 
> ---------------------------
> Description to the tables
> ---------------------------
> 
> 
> ##############            #####################           ##############
> # groups     #            # groups_users      #           # users      #
> ##############            #####################           ##############
> # int obj_id #            # int groups_obj_id #           # int obj_id #
> ##############            # int users_obj_id  #           ##############
> 
> 
> #########################
> # log                   #
> #########################
> # int obj_id            #
> # int log_table_obj_id  #
> # string log_table      #
> # string log_status     #
> #########################
> 
> - table groups and table users are joined width the m-n-table groups_users
> - for each data-entry, there is a entry in the log-table, which 
> describes the status of the data-entry (deleted, inserted...) the 
> repository.xml of groups and users have an collection-descrpitor to the 
> log-table with a query-optimizer vor verifing the source-table with 
> string log_table
>       <!-- 1-n to Log -->
>   <collection-descriptor
>      auto-retrieve="true"
>      auto-delete="object"
>      auto-update="object"
>      proxy="true"
>      name="log"
>      element-class-ref="de.on_ergy.lakon.data.model.Log"
>   >
>      <inverse-foreignkey field-ref="logTableObjId"/>
>      <query-customizer
>           class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>           <attribute
>               attribute-name="logTable"
>               attribute-value="GROUPS"
>           />
>       </query-customizer>
>        </collection-descriptor>
> 
> the query-customizer sets an and-criteria width log_table equals for 
> example "GROUPS"
> 
> 
> --------------------------------------
> groups_repostory.xml, users_repository is equal, only width other 
> parameters
> -----------------------------------------
> <class-descriptor class="de.on_ergy.lakon.data.model.Groups" 
> table="groups">
>   <field-descriptor name="objId"         column="obj_id"          
> jdbc-type="INTEGER"                primarykey="true" 
> autoincrement="true"></field-descriptor>
> 
>   <!-- m - n  users_groups to users -->
>   <collection-descriptor
>    name="users"
>    
> collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 
> 
>    element-class-ref="de.on_ergy.lakon.data.model.Users"
>    auto-retrieve="true"
>    auto-update="false"
>    auto-delete="link"
>      proxy="true"
>    indirection-table="users_groups"
>  >
>    <fk-pointing-to-this-class column="groups_obj_id"/>
>    <fk-pointing-to-element-class column="users_obj_id"/>
> </collection-descriptor>  <!-- 1-n to log -->
>   <collection-descriptor
>      auto-retrieve="true"
>      auto-delete="object"
>      auto-update="object"
>      proxy="true"
>      name="log"
>      element-class-ref="de.on_ergy.lakon.data.model.Log"
>   >
>      <inverse-foreignkey field-ref="logTableObjId"/>
>      <query-customizer
>           class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>           <attribute
>               attribute-name="logTable"
>               attribute-value="GROUPS"
>           />
>       </query-customizer>
>        </collection-descriptor>
>  </class-descriptor>
> 
> 
> ---------------------
> that complete repository.xml data are in attachment, but in german...
> 
> Thanks a lot for your help
> 
> Josef Wagner/ Germany
> 
> 
> ------------------------------------------------------------------------
> 
> <class-descriptor class="de.on_ergy.lakon.data.model.Benutzer" table="benutzer">
> 	<field-descriptor name="objId"       column="obj_id"         jdbc-type="INTEGER"                primarykey="true" autoincrement="true"></field-descriptor>
> 	<field-descriptor name="benutzerKz"   column="benutzer_kz"      jdbc-type="VARCHAR"  length="6"   ></field-descriptor>
> 	<field-descriptor name="vorName"  column="vorname"    jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
> 	<field-descriptor name="nachName"   column="nachname"      jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
> 	<field-descriptor name="passwort"   column="passwort"      jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
> 	<field-descriptor name="memoObjId"   column="memo_obj_id"    jdbc-type="INTEGER"                ></field-descriptor>
> 	<field-descriptor name="spracheObjId"   column="sprache_obj_id"    jdbc-type="INTEGER"                ></field-descriptor>
> 	<!-- Referenz auf den Memotexts -->
> 	<reference-descriptor
> 	   auto-delete="object"
> 	   auto-update="object"
> 	   proxy="true"
>        name="memotext"
>        class-ref="de.on_ergy.lakon.data.model.Memotexte"
>     >
>     <foreignkey field-ref="memoObjId"/>       
>     </reference-descriptor>        
> 	<!-- Referenz auf die Sprache -->
> 	<reference-descriptor
>        proxy="true"
>        name="sprache"
>        class-ref="de.on_ergy.lakon.data.model.Sprache"
>     >
>     <foreignkey field-ref="spracheObjId"/>       
>     </reference-descriptor>        
>     <!-- m - n ÃŒber benutzer_gruppen zu gruppen -->
>     <collection-descriptor
>      name="gruppen"
>      collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
>      element-class-ref="de.on_ergy.lakon.data.model.Gruppen"
>      auto-retrieve="true"
>      auto-update="false"
>      auto-delete="link"
>      proxy="true"
>      indirection-table="benutzer_gruppen"
>   >
>      <fk-pointing-to-this-class column="benutzer_obj_id"/>
>      <fk-pointing-to-element-class column="gruppen_obj_id"/>
>   </collection-descriptor>    
> 
>   <!-- m - n ÃŒber benutzer_teams zu den Teams -->
>   <collection-descriptor
>      name="teams"
>      collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
>      element-class-ref="de.on_ergy.lakon.data.model.Teams"
>      auto-retrieve="true"
>      auto-update="false"
>      auto-delete="link"
>      proxy="true"
>      indirection-table="benutzer_teams"
>   >
>      <fk-pointing-to-this-class column="benutzer_obj_id"/>
>      <fk-pointing-to-element-class column="team_obj_id"/>
>   </collection-descriptor>    
>     	
>     		    <!-- 1-n-Beziehung zu dem Log -->
> 	<collection-descriptor
>        auto-retrieve="true"
> 	   auto-delete="object"
> 	   auto-update="object"
> 	   proxy="true"
>        name="log"
>        element-class-ref="de.on_ergy.lakon.data.model.Log"
>     >
>        <inverse-foreignkey field-ref="logKlasseObjId"/>
>        <query-customizer
> 		    class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>        <!-- Achtung, diesen Werte bitte aus Klasse Tabellentyp entnehmen -->
>         	<attribute
>             	attribute-name="logTable"
>             	attribute-value="BENUTZER"
>         	/>
> 	    </query-customizer>
>        
> 	</collection-descriptor>
>     	
> </class-descriptor>
> 
> 
> ------------------------------------------------------------------------
> 
> <class-descriptor class="de.on_ergy.lakon.data.model.Gruppen" table="gruppen">
> 	<field-descriptor name="objId"         column="obj_id"          jdbc-type="INTEGER"                primarykey="true" autoincrement="true"></field-descriptor>
> 	<field-descriptor name="gruppeKz"  column="gruppe_kz"  jdbc-type="CHAR"     length="12"   ></field-descriptor>
> 	<field-descriptor name="standard"  column="standard"  jdbc-type="BIT"  ></field-descriptor>
> 	<field-descriptor name="memoObjId"         column="memo_obj_id"          jdbc-type="INTEGER"                ></field-descriptor>
> 	<!-- 1-n Referenz auf die Bezeichnungen -->
> 	<collection-descriptor
> 	   auto-delete="object"
> 	   auto-update="object"
>        proxy="true"
>        name="bezeichnungen"
>        element-class-ref="de.on_ergy.lakon.data.model.Bezeichnungen"
>        orderby="sprach_obj_id"
>        sort="ASC"
>     >
>        <inverse-foreignkey field-ref="parentObjId"/>
>        <query-customizer
> 		    class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>        <!-- Achtung, diesen Werte bitte der Schnittstelle TabellentypAttributeIfc entnehmen -->
>         	<attribute
>             	attribute-name="parentTabKz"
>             	attribute-value="GRUPPEN"
>         	/>
> 	    </query-customizer>
>        
> 	</collection-descriptor>
> 	<!-- Referenz auf den Memotexts -->
> 	<reference-descriptor
> 	   auto-delete="object"
> 	   auto-update="object"
>        proxy="true"
>        name="memotext"
>        class-ref="de.on_ergy.lakon.data.model.Memotexte"
>     >
>        <foreignkey field-ref="memoObjId"/>       
>     </reference-descriptor>
>     
>     <!-- m - n ÃŒber benutzer_gruppen zu Benutzer -->
>     <collection-descriptor
>      name="benutzer"
>      collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
>      element-class-ref="de.on_ergy.lakon.data.model.Benutzer"
>      auto-retrieve="true"
>      auto-update="false"
>      auto-delete="link"
>        proxy="true"
>      indirection-table="benutzer_gruppen"
>   >
>      <fk-pointing-to-this-class column="gruppen_obj_id"/>
>      <fk-pointing-to-element-class column="benutzer_obj_id"/>
>   </collection-descriptor>    
> 
> 
>     <!-- m - n ÃŒber gruppen_rollen zu rollen -->
>     <collection-descriptor
>      name="rollen"
>      collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
>      element-class-ref="de.on_ergy.lakon.data.model.Rollen"
>      auto-retrieve="true"
>      auto-update="false"
>      auto-delete="link"
>      proxy="true"
>      indirection-table="gruppen_rollen"
>   >
>      <fk-pointing-to-this-class column="gruppen_obj_id"/>
>      <fk-pointing-to-element-class column="rollen_obj_id"/>
>   </collection-descriptor>    
> 	
> 	    <!-- 1-n-Beziehung zu dem Log -->
> 	<collection-descriptor
>        auto-retrieve="true"
> 	   auto-delete="object"
> 	   auto-update="object"
> 	   proxy="true"
>        name="log"
>        element-class-ref="de.on_ergy.lakon.data.model.Log"
>     >
>        <inverse-foreignkey field-ref="logKlasseObjId"/>
>        <query-customizer
> 		    class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
>        <!-- Achtung, diesen Werte bitte aus Klasse Tabellentyp entnehmen -->
>         	<attribute
>             	attribute-name="logTable"
>             	attribute-value="GRUPPEN"
>         	/>
> 	    </query-customizer>
>        
> 	</collection-descriptor>
> 	
> </class-descriptor>
> 
> 
> ------------------------------------------------------------------------
> 
> <class-descriptor class="de.on_ergy.lakon.data.model.Log" table="log">
> 	<field-descriptor name="objId"         column="obj_id"         jdbc-type="INTEGER"                primarykey="true" autoincrement="true"></field-descriptor>
> 	<field-descriptor name="logBenutzerObjId"         column="log_benutzer_obj_id"         jdbc-type="INTEGER"></field-descriptor>
> 	<field-descriptor name="logKlasseObjId"     column="log_klasse_obj_id"     jdbc-type="INTEGER"></field-descriptor>
> 	<field-descriptor name="mnObjId"         column="m_n_obj_id"         jdbc-type="INTEGER"          ></field-descriptor>
> 	<field-descriptor name="logTable"     column="log_table"     jdbc-type="VARCHAR"     length="8"></field-descriptor>
> 	<field-descriptor name="logFunktion"   column="log_funktion"   jdbc-type="CHAR"     length="1"   ></field-descriptor>
> 	<field-descriptor name="logDataDefault"     column="log_data_default"     jdbc-type="VARCHAR"  length="65535"  ></field-descriptor>	
> 	<field-descriptor name="logDataExport"     column="log_data_export"     jdbc-type="VARCHAR"  length="65535"  ></field-descriptor>	
> 	<field-descriptor name="erstelltTimestamp"  column="erstellt_timestamp"  jdbc-type="TIMESTAMP"               ></field-descriptor>
> 	<field-descriptor name="letzteAenderungTimestamp"  column="letzte_aenderung_timestamp"  jdbc-type="TIMESTAMP"               ></field-descriptor>
> </class-descriptor>
> 
> 
> ------------------------------------------------------------------------
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.