You are viewing a plain text version of this content. The canonical link for it is here.

Posted to ddlutils-user@db.apache.org by Laurent ROCHE <la...@yahoo.com> on 2007/01/08 17:17:58 UTC

FK troubles [Was: Not much info with: "Could not parse or write data file"] aka "The Saga continues II "

Hi,

Now that I have the latest DDL-Utils version, I can use verbosity="DEBUG", and I can see what the problems are with foreign keys.
Just a reminder: I am importing a database from PostgreSQL to Derby. The schema is generated fine but I have problems with Foreign keys when inserting data.
Inserting the data with DB-Unit works fine ... but I would like to do everything with one tool: DDL-Utils.

PB 1: table self references
I have a table where the Foreign Key references the table it-self (case of a parent-child schema).
On most of the rows, the FK references another row but for the top rows/primary ancestors (called them whatever you want) the FK references the row it self.
DDL-Utils apparently does not know how to manage this ... and no rows are inserted (as the parents are not inserted, no children are).

PB2 : do not insert generated IDs for IDENTITY
I have some tables with GENERATED BY DEFAULT AS IDENTITY for the Primary Key (most of my tables refered by FK).
I would then expect to get the PK from the given value and not a generated value. This is not the case hence the rows from other tables referencing the PK on the first table will not insert as the values are identical.

Solutions ?
I can see a way to solve the problem PK/FK dependencies, that will be fairly efficient as well.
When doing the export, create a dependency tree for the tables PK/FK and then export the data according to that tree (I thought ensureforeignkeyorder="true" and/or sortforeignkeys="true" will do this but they don't).
The main advantage is that during import there is no need to defer imports. I realise that's not a solution for everybody but that might be more efficient than parsing during import.

Anyway, maybe I have not set the options correctly to make this work.
I am waiting for any explanations/solutions !

If somebody is interested in the shcema/data files, I could post them but they are not small (66Ko/182Ko unzipped).

Cheers,
L@u
The Computing Froggy

----- Message d'origine ----
De : Laurent ROCHE <la...@yahoo.com>
À : ddlutils-user@db.apache.org
Envoyé le : Mercredi, 3 Janvier 2007, 18h43mn 30s
Objet : PB with SVN files ? [Was: Not much info with: "Could not parse or write data file" aka "The Saga continues"]

Hi Tom,

I've updated my DDL-Utils from the SVN repository.
I then tried to run the ant task with the verbosity parameter, and I got the following error:
===== screen dump =====
C:\apps\Ddlutils-test>ant import-derby
Buildfile: build.xml

import-derby:

BUILD FAILED
C:\apps\Ddlutils-test\build.xml:35: The <ddlToDatabase> type doesn't support the "verbosity" attribute.

Total time: 1 second
===== End of screen dump =====

I have removed it and I got the same error for another parameter I have set (after reading the new doc): useexplicitidentityvalues.
So I assume I am not picking up the right version from SVN !

What can I do ?

Cheers,
L@u
The Computing Froggy

----- Message d'origine ----
De : Thomas Dudziak <to...@gmail.com>
À : ddlutils-user@db.apache.org
Envoyé le : Mardi, 2 Janvier 2007, 4h26mn 09s
Objet : Re: Re : Re : Re : Not much info with: "Could not parse or write data file"

On 12/27/06, Laurent ROCHE <la...@yahoo.com> wrote:

> I have upgraded to the latest DDL utils (and the latest Derby) and I still get the same errors !
>
> I suspect some lines insert fails and then the others can not get inserted because the first ones failed !
> Hence, it will be nice to get extra user friendly messages when data insert fails !
>
> This can be a really tricky part when migrating data from one system to another, if we can not know what data failed to insert and the reason, this tool is going to be difficult to use !
> A line number in data.xml, the SQL instruction that failed and the SQL error message would help a lot to correct the errors when moving data from one system to another.

Please update DdlUtils and set the new verbosity parameter
(http://db.apache.org/ddlutils/ant/org.apache.ddlutils.task.DdlToDatabaseTask.html#parameter-verbosity)
to DEBUG in order to see which rows are hold back.

Tom

__________________________________________________
Do You Yahoo!?
En finir avec le spam? Yahoo! Mail vous offre la meilleure protection possible contre les messages non sollicités
http://mail.yahoo.fr Yahoo! Mail

Re: FK troubles [Was: Not much info with: "Could not parse or write data file"] aka "The Saga continues II "

Posted by Thomas Dudziak <to...@gmail.com>.

On 1/8/07, Laurent ROCHE <la...@yahoo.com> wrote:

> I have a table where the Foreign Key references the table it-self (case of a parent-child schema).
> On most of the rows, the FK references another row but for the top rows/primary ancestors (called them whatever you want) the FK references the row it self.
> DDL-Utils apparently does not know how to manage this ... and no rows are inserted (as the parents are not inserted, no children are).

You're right, DdlUtils did not handle them. I've added code to deal
with them (even when the PK contains identity columns), please update
and test again.

Tom

Re: FK troubles [Was: Not much info with: "Could not parse or write data file"] aka "The Saga continues II "

Posted by Thomas Dudziak <to...@gmail.com>.

On 1/8/07, Laurent ROCHE <la...@yahoo.com> wrote:

> PB 1: table self references
> I have a table where the Foreign Key references the table it-self (case of a parent-child schema).
> On most of the rows, the FK references another row but for the top rows/primary ancestors (called them whatever you want) the FK references the row it self.
> DDL-Utils apparently does not know how to manage this ... and no rows are inserted (as the parents are not inserted, no children are).

I have to check this.

> PB2 : do not insert generated IDs for IDENTITY
> I have some tables with GENERATED BY DEFAULT AS IDENTITY for the Primary Key (most of my tables refered by FK).
> I would then expect to get the PK from the given value and not a generated value. This is not the case hence the rows from other tables referencing the PK on the first table will not insert as the values are identical.

This should work fine. It is controlled via the
useExplicitIdentityValues parameter
(http://db.apache.org/ddlutils/ant/org.apache.ddlutils.task.WriteDataToDatabaseCommand.html#parameter-useexplicitidentityvalues)
whose default value is false (i.e. use the value defined by the
database (identity column) not the one in the data xml file).

> Solutions ?
> I can see a  way to solve the problem PK/FK dependencies, that will be fairly efficient as well.
> When doing the export, create a dependency tree for the tables PK/FK and then export the data according to that tree (I thought ensureforeignkeyorder="true" and/or sortforeignkeys="true" will do this but they don't).
> The main advantage is that during import there is no need to defer imports. I realise that's not a solution for everybody but that might be more efficient than parsing during import.

Right now, DdlUtils inserts data as it reads it from XML  and only
defers insertion of rows as necessary (minimal memory footprint, high
speed).
Building such a dependency tree (which btw. is a graph because you can
have circular dependencies) however requires loading the complete data
into memory. This limits the usability of the data import because it
requires way more memory and is a lot slower.

Tom