Inconsistencies between sql types in mapping and ddl scripts of CTF

Le Duc Bao, can you please check attached consolidated patch. I needed to modify some of your changes for the CTF tests to run without failures.

Hi Ralf, attached please find the patches of sql and xml changes

Hi Ralf, please apply this patch with changes from yours and mine

Hi Le Duc Bao, it seams you have not tried to execute any of the changed scripts against one of the database engines as the scripts contained a bunch of failures like:

• last column definition of create table ends with ',' which causes table not to be created at all ( e.g. tc7x_col)
• insert into statements refer to columns that are not present any more causing insert to fail (e.g. prop at tc7x_container)

While I only tested this against mysql I found and fixed the same problems for all other database engines. After all these changes I was able to execute sql script against mysql without failures. Having said that I have not tried to run test suite yet and that I expect lots of failing tests that may require to revert some of the changes.

As next step I'll run test suite and report findings to this issue.

The first execution of test suite showed the result I expected in my previous comment.

-> Tests run: 65, Failures: 1, Errors: 63

The main cause which was reported has been:

-> The method getOwner in class ctf.jdo.tc9x.ProductKeyGen .... was not found

This could be fixed by reverting the the following additional field mapping from ctf.jdo.tc9x.ProductKeyGen

<field name="owner" type="ctf.jdo.tc9x.Owner">
<sql name="owner" />
</field>

You added this field mapping as there is another mapping to the same table which uses the 'owner' column but as it is allowed to define mappings that do not map all columns of a table the mapping of ctf.jdo.tc9x.ProductKeyGen is valid without the 'owner' column.

With above change the result of test suite looks much better but still not ideal:

-> Tests run: 65, Failures: 0, Errors: 4

The failure messages of the 4 failing tests are:

1) [castor.mysql.TC12] Type Conversion tests
java.sql.SQLException: Unknown column 'bool_short' in 'field list'

2) [castor.mysql.TC20] Key generators: MAX, HIGH-LOW
org.exolab.castor.jdo.PersistenceException: SQL exception in the key generator org.exolab.castor.jdo.keygen.HighLowKeyGenerator: java.sql.SQLException: Base table or view not found message from server: "Table 'test.tc2x_seqtable' doesn't exist".: Base table or view not found message from server: "Table 'test.tc2x_seqtable' doesn't exist"

3) [castor.mysql.TC31] OQL queries for extends
org.exolab.castor.jdo.PersistenceException: Nested error: java.sql.SQLException: Unknown column 'test_oqltag.id2' in 'field list' while executing SELECT tc3x_persistent.id,tc3x_persistent.ctime,tc3x_persistent.mtime,tc3x_persistent.value1,tc3x_persistent.parent_id,tc3x_persistent.group_id,tc3x_related.id,tc3x_extends1.ident,tc3x_extends1.ext,test_oqltag.id2 FROM tc3x_extends1 LEFT OUTER JOIN tc3x_related ON tc3x_extends1.ident=tc3x_related.persist_id LEFT OUTER JOIN test_oqltag ON tc3x_extends1.ident=test_oqltag.id1,tc3x_persistent WHERE tc3x_extends1.ident=tc3x_persistent.id: Unknown column 'test_oqltag.id2' in 'field list'

4) [castor.mysql.TC80] self-referential relation test with extend hierarchies
org.exolab.castor.jdo.PersistenceException: Nested error: java.sql.SQLException: Unknown column 'tc8x_self_refer_parent_f1.fid' in 'on clause' while executing SELECT tc8x_self_refer_parent.id,tc8x_self_refer_parent.name,tc8x_self_refer_parent_f1.id,tc8x_self_refer_child.id,tc8x_self_refer_child.descr FROM tc8x_self_refer_parent LEFT OUTER JOIN tc8x_self_refer_parent tc8x_self_refer_parent_f1 ON tc8x_self_refer_parent.id=tc8x_self_refer_parent_f1.fid LEFT OUTER JOIN tc8x_self_refer_child ON tc8x_self_refer_parent.id=tc8x_self_refer_child.id WHERE (tc8x_self_refer_parent.id = ?1): Unknown column 'tc8x_self_refer_parent_f1.fid' in 'on clause'

The first one at TC12 can be fixed by changing line 238 of ctf.jdo.tc1x.TestTypeConversion from:

ResultSet rset = stmt.executeQuery(
"select bool_short, bool_short_minus, bool_int, "
+ "bool_int_minus, bool_bigdec, bool_bigdec_minus "
+ "from tc1x_conv where id = " + TypeConversion.DEFAULT_ID);

to

ResultSet rset = stmt.executeQuery(
"select bool_int, "
+ "bool_int_minus, bool_bigdec, bool_bigdec_minus "
+ "from tc1x_conv where id = " + TypeConversion.DEFAULT_ID);

The second problem at TC20 has been caused by removal of 'tc2x_seqtable' which is required by following keygen mappings of ctf.jdo.tc2x.mapping.xml:

<key-generator name="HIGH-LOW">
<param name="table" value="tc2x_seqtable"/>
<param name="key-column" value="table_name"/>
<param name="value-column" value="max_id"/>
<param name="grab-size" value="1000"/>
</key-generator>

<key-generator name="HIGH-LOW" alias="HLSC">
<param name="table" value="tc2x_seqtable"/>
<param name="key-column" value="table_name"/>
<param name="value-column" value="max_id"/>
<param name="grab-size" value="1000"/>
<param name="same-connection" value="true"/>
</key-generator>

This can be fixed by reverting removal of 'tc2x_seqtable' at sql scripts of all database engines. With regard to ddlgen this means that we need to generate such tables for HIGH-LOW keygenerators.

The third problem at TC31 has been caused by the removal of column 'id2' from table 'test_oqltag'. As this is an n:m relation table it should obviously have 2 columns. The n:m relation is build between ctf.jdo.tc3x.ExtendsEntity1 and ctf.jdo.tc3x.ExtendsEntity2 which both extend ctf.jdo.tc3x.PersistentEntity. Therefore ctf.jdo.tc3x.PersistentEntity defines the primary key 'id' for both tables of the relation. The mapping of the n:m relation is only defined for ctf.jdo.tc3x.ExtendsEntity1 with:

<field name="list" type="ctf.jdo.tc3x.ExtendsEntity2" collection="collection" lazy="false">
<sql many-table="test_oqltag" name="id2" many-key="id1"/>
</field>

It seams that ddlgen needs to care on the 'name' attribute on 'sql' tag which it does not at the moment. If a collosion is detected between 'name' definition on one object and 'many-key' on the other, a warning should be logged and column name of 'many-key' should be used.

After fixing the problem with missing 'id2' column another problem apeared and the last one from above still is not solved:

1) [castor.mysql.TC31] OQL queries for extends
org.exolab.castor.jdo.PersistenceException: Nested error: java.sql.SQLException: Column index out of range.: Column index out of range.

2) [castor.mysql.TC80] self-referential relation test with extend hierarchies
org.exolab.castor.jdo.PersistenceException: Nested error: java.sql.SQLException: Unknown column 'tc8x_self_refer_parent_f1.fid' in 'on clause' while executing SELECT tc8x_self_refer_parent.id,tc8x_self_refer_parent.name,tc8x_self_refer_parent_f1.id,tc8x_self_refer_child.id,tc8x_self_refer_child.descr FROM tc8x_self_refer_parent LEFT OUTER JOIN tc8x_self_refer_parent tc8x_self_refer_parent_f1 ON tc8x_self_refer_parent.id=tc8x_self_refer_parent_f1.fid LEFT OUTER JOIN tc8x_self_refer_child ON tc8x_self_refer_parent.id=tc8x_self_refer_child.id WHERE (tc8x_self_refer_parent.id = ?1): Unknown column 'tc8x_self_refer_parent_f1.fid' in 'on clause'

I'll look into them later on and prepare a new patch when I managed to resolve them.

I found the remaining problems and was able to resolve them as described below.

The second problem on TC31 has been caused by changing sql type of 2 columns of ctf.jdo.tc3x.PersistentEntity. By changing column types back to:

<field name="creationTime" type="java.util.Date" required="true">
<sql name="ctime" type="timestamp" />
</field>
<field name="modificationTime" type="java.util.Date" required="false">
<sql name="mtime" type="timestamp" dirty="ignore" />
</field>

the problem could be resolved. Having said that I have no idea why sql type was changed from timestamp to date.

The problem with TC80 was caused by the removal of 'fid' column from 'tc8x_self_refer_parent' table. This column is required by following mapping of ctf.jdo.tc8x.SelfReferentialParent:

<field name="entities" type="ctf.jdo.tc8x.SelfReferentialParent"
collection="arraylist">
<sql many-key="fid" />
</field>

Obviously this field mapping defines a self referential 1:n relation between ctf.jdo.tc8x.SelfReferentialParent instances. While this kind of unidierectional definition does not work in all cases, it seams to works here and I'd view it valid. Anyway by adding:

<field name="parent" type="ctf.jdo.tc8x.SelfReferentialParent" >
<sql name="fid"/>
</field>

we make the relation bidirectional and easier to generate by ddlgen.

Now that all problems are resolved at scripts and mapping for castor test suite ddlgen needs to be changed to be able to generate the required scripts. Having said that I would much apreciate if the original author of a patch would resolve such things on his own especially as I had resolved some of them with my first update of the patch.

After commiting CASTOR-1474 we also have to check progress related scripts for the same changes then with all the other database engines before commiting this patch.

Fixed similar inconsistencies at progress as with other database engines.
Added CAHNGELOG and release-notes.xml entries.