[jira] [Updated] (DERBY-6219) Provide additional support for importing data via the optional foreignViews tool

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-6219?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-6219:
---------------------------------

    Attachment: 6219.sql
                derby-6219-01-aa-orderByForeignKeys.diff

Attaching derby-6219-01-aa-orderByForeignKeys.diff. This patch adds an additional argument to the foreignViews optional tool. The new argument is the name of a file which will hold a script for loading local tables with data from the foreign database. Also attaching 6219.sql, a script which shows the revised tool in action.

With this patch, the foreignViews tool can be loaded like this:

call syscs_util.syscs_register_tool
(
    'foreignViews', true,
    'jdbc:derby:memory:foreignDBnoCycle',
    'CP_',
    'loadScript.sql'
);

The script of INSERT statements will be ordered by foreign key dependencies if there are no cycles in the foreign key graph. If there are cycles, then the tool abandons any attempt to order the INSERTs and just writes the INSERTS in a non-deterministic order.

I think that this is useful functionality, but I'm reluctant to check this in without more work and clearly, a battery of tests. I think that something needs to be done to disable (and then re-enable) triggers and identity columns. Also, when Dag is further along with his work on deferred constraints, we could consider toggling constraints off and back on when there are cycles in the foreign key graph.


Touches the following files:

M       java/tools/org/apache/derby/impl/tools/optional/ForeignDBViews.java
M       java/tools/org/apache/derby/loc/toolsmessages.properties


> Provide additional support for importing data via the optional foreignViews tool
> --------------------------------------------------------------------------------
>
>                 Key: DERBY-6219
>                 URL: https://issues.apache.org/jira/browse/DERBY-6219
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Rick Hillegas
>              Labels: derby_triage10_11
>         Attachments: 6219.sql, derby-6219-01-aa-orderByForeignKeys.diff
>
>
> Once you have created views on a foreign database using the optional foreignViews tool (see DERBY-6022), you may need to import the data into your local schema. You confront various problems, including:
> 1) The need to order your inserts by foreign key dependencies.
> 2) The need to restart identity columns after the inserts.
> This issue is a place to track issues like this and brainstorm solutions.



--
This message was sent by Atlassian JIRA
(v6.1.5#6160)