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]