Tips and Tricks
<!--titre.oft-->
1 Object-relational mapping
1.1 My class A is mapped on a table which primary key is composed of several attributes. How can I declare the relational mapping of object slots referring to instances of class A ?
Let us suppose that your class A is mapped onto the table MYTABLE1:
| class A on MYTABLE1 type Tuple ( slot1: String on MYTABLE1.ATTR3, slot2: double on MYTABLE1.ATTR4 ) end; |
with MYTABLE1 defined by : | define table MYTABLE1 ( ATTR1 long keyPart, ATTR2 string keyPart, ATTR3 string, ATTR4 double ); |
If, in your class B mapped onto MYTABLE2, you want your slot myA to be persistent and of type "A reference", its mapping declaration will have to be consistent with this syntax:
| class B on MYTABLE2 type Tuple ( s1: String on MYTABLE.ATT2, s2: double on MYTABLE.ATT3 myA: A on Tuple( aA1: long on MYTABLE.ATT4, aA2: String on MYTABLE.ATT5 ) ) end; |
with MYTABLE2 defined by : | define table MYTABLE2 ( ATT1 long key, ATT2 string, ATT3 double, ATT4 long, ATT5 string ); |
where (MYTABLE2.ATT4, MYTABLE2.ATT5) is, of course, a foreign key of the primary key (MYTABLE1.ATTR1, MYTABLE1.ATTR2).
1.2 The relational mapping of my classes includes many complex constraints. ObjectDRIVER checks that these constraints are complied when I try to write an instance of one of these classes into the database. I know that my object values comply these contraints and that any check is useless. Can I avoid any constraint check to optimize performances ?
Yes you can, of course. Just set, in %ODPATH%\system\config.xml configuration file, the parameter transforming.relational.default.data.checkConstraints to false like below:
<transforming>
<relational>
<default>
<data>
<keys>
<checkUserData> true </checkUserData>
</keys>
<checkConstraints> false </checkConstraints>
</data>
<queries>
<optimization> full </optimization>
<bufferSize> 3000 </bufferSize>
</queries>
</default>
</relational>
</transforming>
However, be aware that if you insert into the database an object which value does not comply its class constraints, you will not be able to reload it as an instance of this class in a next transaction.
2 Virtual object-oriented databases
2.1 I did not map the primary key attribute of my table in the associated class. However, I would like, using OQL, to retrieve objects in the database by specifying their key values. That's quite "dirty", I know, but is there a way to do it however?
Yes. You must use OQL parameters to bypass ObjectDRIVER OQL expression type checking. ObjectDRIVER allows the late declaration of a parameter type, when its first value is defined. But, at this moment, the query has been already parsed and validated, and since ObjectDRIVER tries, before generating an error, to cast the variable value to the type it should have, and since it succeeds to do it, that does the trick!
For example, let us suppose that a class Employee has been defined and mapped onto a table EMP by
| class Employee on EMP type Tuple ( name: String on EMP.ENAME, salary: double on EMP.SAL ) end; |
with EMP defined by : | define table EMP ( EMPNO long key, ENAME string(20), FNAME string(20), SAL double ); |
Executing the OQL query below retrieves the Employee object which key attribute EMPNO is equal to 7839:
select e from Employee e where e = $1
bind $1 7839
execute
2.2 When I validate my transactions, I often get an error "Error during transaction validation for object 00F7FB18 (class MyClass): 23000[Sybase][ODBC Driver] Integrity constraint violation: no primary key value for foreign key 'MYATTRIBUTE' in table 'MYTABLE2'". What is the reason and what can I do?
Objects form graphs which nature is to contain cycles (for example imagine an object which refers to itself!). When one writes an object into the database, one can insert or update relational tuples containing foreign key values that refers to not existing yet primary key values. The resulting inconsistency is temporary only since the missing primary key value tuples are inserted just after. The database is consistent again before the transaction validates.
Some DBMS (or some databases) check, when a foreign key value is written into the base, that this value effectively fits to an existing primary key value. If it does not, they generate an error of the kind you get.
To fix this problem, just set, in %ODPATH%\system\config.xml configuration file, the parameter dataSources.relational.default.data.keys.insertPrimaryBeforeForeign to true like below:
<dataSources>
<relational>
<default>
<queryLanguage>
<name> SQL </name>
<version> 92 </version>
<prepare> true </prepare>
<parameters> true </parameters>
<select>
<forUpdate> false </forUpdate>
</select>
</queryLanguage>
<data>
<keys>
<insertPrimaryBeforeForeign> true </insertPrimaryBeforeForeign>
</keys>
</data>
<transactions>
<commit>
<select>
<before> false </before>
<after> false </after>
</select>
</commit>
</transactions>
</default>
...
</relational>
</dataSources>
This mode allows to defer the insertion of a foreign key value when the corresponding primary key value does not exist yet.
However, this "insertPrimaryBeforeForeign" mode is by nature less efficient than the default mode, because it is liable to generate more SQL queries to update the database during a transaction validation. So you should activate it only if the DBMS or the database requires it.
The best solution is to redefine it only for the DBMS or the database that requires it and to let the default mode set to false. To activate it for Oracle8i only, insert into the configuration file %ODPATH%\system\config.xml the parameter insertPrimaryBeforeForeign in the following way:
<dataSources>
<relational>
<default>
...
</default>
<dbms id="Oracle8i">
<name> Oracle </name>
<version> 8i </version>
<queryLanguage>
<name> SQL </name>
<version> 99 </version>
<select>
<forUpdate> true </forUpdate>
</select>
</queryLanguage>
<data>
<keys>
<insertPrimaryBeforeForeign> true </insertPrimaryBeforeForeign>
</keys>
<date>
<format> DD-MM-YYYY </format>
</date>
<timeStamp>
<format> DD-MM-YYYY HH:NN:SS </format>
</timeStamp>
</data>
</dbms>
</relational>
</dataSources>
If you prefer to redefine it for the Oracle8i database MyDemo only, insert into the configuration file %ODPATH%\system\config.xml the parameter insertPrimaryBeforeForeign in that way:
<dataSources>
<relational>
<default>
...
</default>
<dbms id="Oracle8i">
<name> Oracle </name>
<version> 8i </version>
<queryLanguage>
<name> SQL </name>
<version> 99 </version>
<select>
<forUpdate> true </forUpdate>
</select>
</queryLanguage>
<data>
</data>
<date>
<format> DD-MM-YYYY </format>
</date>
<timeStamp>
<format> DD-MM-YYYY HH:NN:SS </format>
</timeStamp>
</data>
<database id="MyDemo">
<name> MyDemo </name>
<data>
<keys>
<insertPrimaryBeforeForeign> true </insertPrimaryBeforeForeign>
</keys>
</data>
</database>
</dbms>
</relational>
</dataSources>