db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6022) Add a system procedure for (un)registering optional packages of Derby tools.
Date Wed, 09 Jan 2013 18:30:12 GMT

     [ 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

Mime
View raw message