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] [Created] (DERBY-6042) Document the syscs_util.syscs_register_tool procedure added by DERBY-6022.
Date Tue, 15 Jan 2013 14:50:12 GMT
Rick Hillegas created DERBY-6042:
------------------------------------

             Summary: Document the syscs_util.syscs_register_tool procedure added by DERBY-6022.
                 Key: DERBY-6042
                 URL: https://issues.apache.org/jira/browse/DERBY-6042
             Project: Derby
          Issue Type: Improvement
          Components: Documentation
    Affects Versions: 10.10.0.0
            Reporter: Rick Hillegas


We should document the new syscs_util.syscs_register_tool() procedure and the new optional
tools which it loads and unloads. The following changes make sense to me:

--
-- Reference Manual
--
-- New section on the syscs_util.syscs_register_tool() procedure
--

This procedure loads and unloads optional tools packages. By default, only the DBO can run
this procedure.

Syntax:

SYSCS_UTIL.SYSCS_REGISTER_TOOL
(
    IN TOOLNAME VARCHAR(128),
    IN REGISTER BOOLEAN,
    IN OPTIONALARGS VARCHAR(128) ...
);

No result set is returned by this procedure.

TOOLNAME

  Name of the optional tool. One of: 'databaseMetaData' or 'foreignViews'

REGISTER

  True means the tool is loaded. False means it is unloaded.

OPTIONALARGS

  Optional arguments specific to each tool.



The following optional tools are supported:

databaseMetaData

This optional tool creates functions and table functions to wrap the methods in java.sql.DatabaseMetaData.
This lets you use DatabaseMetaData methods in queries. This includes the ability to join and
filter the ResultSets returned by DatabaseMetaData methods. This tool does not require any
optional arguments. To create the metadata functions and table functions, do the following:

call syscs_util.syscs_register_tool( 'databaseMetaData', true )

To drop the functions and table functions, do the following:

call syscs_util.syscs_register_tool( 'databaseMetaData', false )

The Tools Guide provides more information on how to use this tool.



foreignViews

This optional tool creates schemas, table functions, and convenience views for all user tables
in a foreign database. The table functions and views are useful for bulk-importing foreign
data into Derby. This tool takes two additional arguments:

CONNECTION_URL

  This is a connection URL string suitable for creating a connection to the foreign database
via DriverManager.getConnection().

SCHEMA_PREFIX

  This is an optional string prefixed to all of the schema names which the tool creates. This
argument may be omitted. If it is omitted, then the tool will create schemas which have the
same names as the schemas in the foreign database.

To create views on the foreign data, do the following:

call syscs_util.syscs_register_tool( 'foreignViews', true, 'foreignDatabaseURL', 'XYZ_' )

To drop the views on the foreign data, do the following:

call syscs_util.syscs_register_tool( 'foreignViews', false, 'foreignDatabaseURL', 'XYZ_' )

The Tools Guide provides more information on how to use this tool.




--
-- Tools Guide
--
-- New section on the optional databaseMetaData tool
--

This is an optional tool loaded and unloaded by the syscs_util.syscs_register_tool() system
procedure. Loading this tool creates functions and table functions corresponding to most of
the methods in the java.sql.DatabaseMetaData interface. To load this tool, do the following:

call syscs_util.syscs_register_tool( 'databaseMetaData', true )

That command creates metadata functions and table functions in the current schema. The functions
and table functions have the same names as the corresponding DatabaseMetaData methods which
they wrap. Once you have loaded this tool, you can filter and join these functions to create
powerful metadata queries. For instance, the following query lists the column names and datatypes
for all columns in tables created by users:

select t.table_schem, t.table_name, c.column_name, c.type_name
from table( getTables( null, '%', '%' ) ) t,
        table( getColumns( null, '%', '%', '%') ) c
where c.table_schem = t.table_schem
and c.table_name = t.table_name
and t.table_type = 'TABLE'
order by table_schem, table_name, column_name

A couple DatabaseMetaData methods take array arguments. Because those arguments can't be represented
as Derby types, the arguments are eliminated. This means that the trailing "types" arguments
to getTables() and getUDTs() have been eliminated. In addition, the following DatabaseMetaData
methods don't have corresponding metadata routines:

o getRowIdLifetime() is eliminated because Derby does not provide an implementation of java.sql.RowIdLifetime.

o getSchemas() is eliminated because Derby does not support overloads. The more general getSchemas(
String, String ) method is included.

o supportsConvert() is eliminated because Derby does not support overloads. The more general
supportsConvert( int, int ) is included.

When you are done joining metadata results, you can drop this package of functions and table
functions:

call syscs_util.syscs_register_tool( 'databaseMetaData', false )





--
-- Tools Guide
--
-- New section on the optional foreignViews tool
--

This is an optional tool loaded and unloaded by the syscs_util.syscs_register_tool() system
procedure. Loading this tool creates schemas, table functions, and convenience views for all
user tables in a foreign database. This can be useful for bulk-importing foreign data. To
load this tool, do the following...

call syscs_util.syscs_register_tool( 'foreignViews', true, 'foreignDatabaseURL', 'XYZ_' )

...where the trailing 2 arguments have these meanings:

o foreignDatabaseURL is an URL suitable for creating a connection to the foreign database
via java.sql.DriverManager.getConnection(). E.g.: 'jdbc:derby:db3;user=fred;password=fredpassword'

o XYZ_ is a string prefixed to the names of all schemas created by this tool. This argument
may be omitted. If it is omitted, then the tool will create schemas which have the same names
as the schemas in the foreign database.

Suppose that the foreign database has two schemas S1 and S2. S1 contains two user tables T1
and T2. S2 contains two user tables, U1 and U2. Loading the tool as shown above will create
the following objects in your Derby database:

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 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 bulk-importing the foreign data, you can drop this package of schemas, table
functions and views:

call syscs_util.syscs_register_tool( 'foreignViews', false, 'foreignDatabaseURL', 'XYZ_' )


--
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