Support ordering of imported tables to avoid disabling foreign key constraints

[Arisnp]

[markddrake]

@Arisnp : I will address this in several phases. The first phase will allows you to specify a list of tables and ensure that the export and import operations will be performed in the order the tables are specified.

Once the export is ordered the import is, by definition, ordered given the nature of the JSON processing. With operations that generate multiple files operations (load/unload) or when copying directly between two databases it would be easy to use the same logic to order the tables (subject to the same caveat regarding circular dependencies). However doing so would potentially break parallel mode import operations (or make the task of assigning tables to the parallel workers much more complex).

However if there is a recursive or circular relationship between two tables insert operations cannot be guaranteed to succeed if constraints are enabled. Performing an Import with Foreign Keys enabled also has performance implications (index Maintenance and performing the actual checks) . consequently I think the default behavior should be to for Yadamu to continue disabling foreign keys while performing the import. If you see a use-case for needing them to be enabled during the import, rather re-enabled following the import, I will add a parameter to enable this behavior at a later date.

Also note that currently automatically disabling and re-enabling constraints is only supported with Oracle. None of the other database drivers currently provide support for disabling and enabling foreign keys. The Oracle Driver supports this feature because it offers DDL_AND_DATA mode, where the DBMS_METADATA package is used to capture all the DDL from the source schema during the export phase and then the information is used to recreate the target schema during the import phase before loading the data.

The other database drivers have been developed under the assumption that they will be responsible for creating the tables in the target schema, as part of the import process. Since the other drivers currently only support DATA_ONLY mode, this means that only tables are created, not keys and constraints. This assumption will need to re-visited and code will need to added to the other database drivers to disable/enable constraints if there is a strong need to load into pre-existing schemas where constraints have been defined.

Longer term I have considered adding an option to automatically order the tables in the export file by the PK/FK dependencies.
As stated above this can never solve 100% of the since you can have recursive and/or circular dependencies.

[markddrake]

@Arisnp. When you provide a list of tables what would you expect to happen if one or more of the specified tables does not exist in the source schema or export file. Should YADAMU proceed with the operation on the tables that do exist or should it abort the operation until the list of tables is valid ?

[Arisnp]

My specific use case is importing historical data from the source database while the target database is already in production. Hence the need for the constraints to be in place. I would prefer to abort in case of an error.

[markddrake]

Partially addressed by 2fed030 which allows you to use the TABLES parameter to manually specify the order in which table will be processed. This issue will remain open while the feasibility of providing some level of support for automatically ordering the tables is investigated.