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

[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