[
https://issues.apache.org/jira/browse/DERBY-6022?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6022:
---------------------------------
Attachment: derby-6022-03-aa-foreignDBviews.diff
Attaching derby-6022-03-aa-foreignDBviews.diff. This patch adds an optional
tool for siphoning data out of foreign databases without indirecting through
csv dumps. This is a productization of the ForeignTableVTI attached to
DERBY-4962. I am running tests now.
The tool creates schemas, table functions, and wrapping views against all user
tables in a foreign database. The foreign database could be another Derby
database or any other RDBMS. An optional argument lets you prefix the local
schema names with a string to distinguish them from the local schemas that you
are importing into.
Suppose that you have a foreign database with two schemas S1 and S2 which
contain, respectively, tables T1 and T2 and U1 and U2. If you issue the
following command...
call syscs_util.syscs_register_tool( 'fdbv', true,
'connectionUrlToForeignDB', 'XYZ_' )
...then Derby will create the following objects:
schema XYZ_S1
table function XYZ_S1.T1, which reads S1.T1 from the foreign database
table function XYZ_S1.T2, which reads S1.T2 from the foreign database
view XYZ_S1.T1, which wraps the corresponding table function
view XYZ_S1.T2, which wraps the corresponding table function
schema XYZ_S2
table function XYZ_S2.U1, which reads S2.U1 from the foreign database
table function XYZ_S2.U2, which reads S2.U2 from the foreign database
view XYZ_S2.U1, which wraps the corresponding table function
view XYZ_S2.U2, which wraps the corresponding table function
The views hide the ugly arguments to the table functions. You can then populate
your local schema via the following SELECTs:
insert into S1.T1 select * from XYZ_S1.T1
insert into S1.T2 select * from XYZ_S1.T2
insert into S2.U1 select * from XYZ_S2.U1
insert into S2.U2 select * from XYZ_S2.U2
When you are done migrating the data, you can drop the objects added by the
tool:
call syscs_util.syscs_register_tool( 'fdbv', false,
'connectionUrlToForeignDB', 'XYZ_' )
The trailing schema prefix argument is optional and can be omitted if there is
no overlap between the names of your local schemas and the names of the foreign
schemas:
call syscs_util.syscs_register_tool( 'fdbv', true,
'connectionUrlToForeignDB' )
call syscs_util.syscs_register_tool( 'fdbv', false,
'connectionUrlToForeignDB' )
Touches the following files:
-------------
A java/engine/org/apache/derby/vti/ForeignTableVTI.java
M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
M java/engine/org/apache/derby/vti/build.xml
M tools/javadoc/publishedapi.ant
Adds to the public API a RestrictedVTI for reading a table from a
foreign database.
-------------
M java/build/org/apache/derbyBuild/classlister.java
A java/tools/org/apache/derby/impl/tools/optional/ForeignDBViews.java
M java/engine/org/apache/derby/catalog/Java5SystemProcedures.java
M tools/jar/extraDBMSclasses.properties
M tools/jar/tools.properties
M java/tools/org/apache/derby/loc/toolsmessages.properties
Adds the new fdbv OptionalTool.
-------------
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/OptionalToolsTest.java
Adds some tests of the new fdbv OptionalTool.
> Add a system procedure for (un)registering optional packages of Derby tools.
> ----------------------------------------------------------------------------
>
> Key: DERBY-6022
> URL: https://issues.apache.org/jira/browse/DERBY-6022
> Project: Derby
> Issue Type: Improvement
> Components: SQL, Tools
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Attachments: derby-6022-01-aa-registerToolProc.diff,
> derby-6022-02-aa-dbmdWrapper.diff, derby-6022-03-aa-foreignDBviews.diff
>
>
> Now that vararg routines have been added to Derby (see DERBY-3069), I would
> like to add a new vararg system procedure for registering and unregistering
> optional packages of Derby tools. For starters, these would be tools which
> aren't checked into the Derby codeline but are just attached to various
> JIRAs. These tools are:
> o DBMDWrapper (DERBY-3973 and DERBY-5967) - This tool creates functions and
> table functions for all of the DatabaseMetaData methods so that you can write
> complicated queries which join and filter JDBC metadata.
> o ForeignTableVTI (DERBY-4962) - This tool creates views against foreign
> databases so that you can bulk-import foreign data into Derby without
> indirecting through csv files.
> It also may be possible to use this approach to expose the log and data file
> reading tools attached to DERBY-5195 and DERBY-5201.
> The new system procedure would look like this:
> create procedure syscs_util.syscs_register_tool
> (
> toolName varchar( 32672 ),
> boolean register,
> optionalArgs varchar( 32672 ) ...
> )
> language java parameter style derby modifies sql data
> external name 'willFigureOutWhereToPutThis';
> The arguments would have these meanings:
> o toolName - A name specific to the tool.
> o register - True means "register the tool" and false means "unregister the
> tool".
> o optionalArgs - Each tool could have its own variable set of additional
> configuration parameters.
> By default, only the DBO could run this procedure. The DBO could grant
> execute permission to other users.
> The known tool names and their optional parameters would be documented in the
> Derby Reference Manual in the section on syscs_util.syscs_register_tool.
> I am thinking that we should put the optional tools themselves in
> derbytools.jar. We might want to document all of the optional tools in the
> Tools Guide, although I can see arguments for documenting some tools in the
> Admin Guide.
> I would appreciate other people's thoughts about this proposal.
> Thanks,
> -Rick
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
