db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fox <Thomas....@seitenbau.net>
Subject Errors while executing the test SQL (was:RE: [Vote]release torque 4.0)
Date Thu, 10 Jan 2013 21:33:25 GMT

Greg Monroe wrote:
> bug - Running Test project against MySQL 5.1 produces errors in the
>       sql:execute phase:  Problem sql files are:
>
>       - bookstore-schema ( ALTER TABLE / FOREIGN KEY statements)
>       - databaseMapInit-schema.sql (mostly creating date field defaults
>           and some alter table/foreign key statements )
>       - pk-schema.sql (Table exists error)
>       - test-schema.sql (ALTER TABLE / FOREIGN KEY statements)
>       - pk-schema-idtable-init.sql (Insert failed because of duplicate
key)
>
>       Note: Some of errors get hidden during second run with tables
created.
>
> bug - Running Test project against MS SQL 2005 produces errors in the
>       sql:execute phase.  Problem sql files are:
>
>       - bookstore-schema.sql ( ALTER TABLE / DROP CONSTRAINT statments
and
>           DROP VIEW statement)
>       - default-value-schema.sql ( mostly creating date field defaults
>           and some alter table drop constraint key statements )
>       - pk-schema.sql (Table exists error)
>       - qualified-table-name-schema.sql  (Schema exists on 2nd run)
>       - save-method-in-peer-schema.sql (ALTER TABLE / DROP CONSTRAINT
stmts)
>       - test-schema.sql (ALTER TABLE/DROP CONSTRAINT and CREATE BASE
table)
>       - pk-schema-idtable-init.sql (Insert failed because of duplicate
key)

There are a number of reasons for the errors.
1) There are ALTER TABLE DROP CONSTRAINT statements created which fail if
the tables do not exist (i.e in an empty database or if tables are added).
The reason for these statements is that they make possible to recreate the
database even if circular foreign key relations exist.
Imagine, e.g., a database with two tables, A and B, where a has a foreign
key pointing to B and B has a foreign key pointing to A.
The user adds a column to table A and runs the generated SQL script. After
it, the new column also will be present in the database.
Without any manual interaction (of course, the data is lost).
Without the ALTER TABLE DROP CONSTRAINT statements, most databases refuse
to drop table A because there are still foreign keys pointing to it; and
subsequently the create table command will fail because the table A already
exists, which leaves us without the new column in the database (Note that
some Databases e.g. oracle have a CASCADE CONSTRAINTS option which drop the
constraints along with the table, but some other databases do not have this
option)
2) Not all databases support the IF EXISTS clause for dropping tables,
resulting in errors
3) Some table definitions use types which do not exist in some databases
(e.g. oracle and derby do not have BIT datatypes)
4) Some column names used in the test project are reserved words in some
databases.
5) There might be other errors I am not currently aware of.

Although 4) could be dealt with by renaming the offending columns and 3) by
some ant scripting and table reorganization, I do not have a good idea how
to tackle 1) and 2).
Anybody got a idea ? Note that type 1 and 2 errors will go away once the
database is filled.

     Thomas

P.S. As a side note, I cannot help remarking that this situation is not
new, it was like this at least in the 3.2 and 3.3 releases.
As long as all of the tests were passed, explainable errors in the SQL did
not matter too much.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message