SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure

Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 82693 invoked by uid 99); 22 Jan 2013 22:24:28 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Jan 2013 22:24:28 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=5.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 22 Jan 2013 22:24:26 +0000 Received: from eris.apache.org (localhost [127.0.0.1]) by eris.apache.org (Postfix) with ESMTP id 358DE23888D2; Tue, 22 Jan 2013 22:24:07 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1437218 - in /db/derby/docs/trunk/src: ref/refderby.ditamap ref/rrefregistertoolproc.dita tools/ctoolsopttools.dita tools/derbytools.ditamap tools/rtoolsoptdbmetadata.dita tools/rtoolsoptforeignviews.dita Date: Tue, 22 Jan 2013 22:24:06 -0000 To: derby-commits@db.apache.org From: chaase3@apache.org X-Mailer: svnmailer-1.0.8-patched Message-Id: <20130122222407.358DE23888D2@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Author: chaase3 Date: Tue Jan 22 22:24:06 2013 New Revision: 1437218 URL: http://svn.apache.org/viewvc?rev=1437218&view=rev Log: DERBY-6042 Document the syscs_util.syscs_register_tool procedure added by DERBY-6022. Added one new Reference Manual topic and three new Tools Guide topics. Patch: DERBY-6042-2.diff Added: db/derby/docs/trunk/src/ref/rrefregistertoolproc.dita (with props) db/derby/docs/trunk/src/tools/ctoolsopttools.dita (with props) db/derby/docs/trunk/src/tools/rtoolsoptdbmetadata.dita (with props) db/derby/docs/trunk/src/tools/rtoolsoptforeignviews.dita (with props) Modified: db/derby/docs/trunk/src/ref/refderby.ditamap db/derby/docs/trunk/src/tools/derbytools.ditamap Modified: db/derby/docs/trunk/src/ref/refderby.ditamap URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/refderby.ditamap?rev=1437218&r1=1437217&r2=1437218&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/refderby.ditamap (original) +++ db/derby/docs/trunk/src/ref/refderby.ditamap Tue Jan 22 22:24:06 2013 @@ -884,6 +884,8 @@ limitations under the License. + + Added: db/derby/docs/trunk/src/ref/rrefregistertoolproc.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefregistertoolproc.dita?rev=1437218&view=auto ============================================================================== --- db/derby/docs/trunk/src/ref/rrefregistertoolproc.dita (added) +++ db/derby/docs/trunk/src/ref/rrefregistertoolproc.dita Tue Jan 22 22:24:06 2013 @@ -0,0 +1,115 @@ + + + + + +SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure +The SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure +loads and unloads optional tools packages. + + +system proceduresSYSCS_UTIL.SYSCS_REGISTER_TOOL +SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure +toolsSYSCS_UTIL.SYSCS_REGISTER_TOOL +system procedure +registering toolsSYSCS_UTIL.SYSCS_REGISTER_TOOL +system 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: The name of the optional tool. Must be either +'databaseMetaData' or 'foreignViews'.
REGISTER: A value of true tells + to load the tool. A +value of false tells to +unload the tool.
OPTIONALARGS: Optional arguments specific to each tool.

Execute privileges +

If authentication and SQL authorization are both enabled, only the +database owner has +execute privileges on this procedure by default. See "Enabling user +authentication" and "Setting the SQL standard authorization mode" in the + for more information. The +database owner can grant access to other users.

Usage +

The following optional tools are supported:

databaseMetaData +
This optional tool creates functions and table functions to wrap the methods +in java.sql.DatabaseMetaData, allowing you to use DatabaseMetaData +methods in queries. For example, you can 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 ) + +
See the for 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 +. This tool takes two +additional arguments:
+
+
CONNECTION_URL
+
This is a connection URL string suitable for creating a connection to the +foreign database by calling 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_' ) + +
See the for more +information on how to use this tool.
+

+ + Propchange: db/derby/docs/trunk/src/ref/rrefregistertoolproc.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/tools/ctoolsopttools.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tools/ctoolsopttools.dita?rev=1437218&view=auto ============================================================================== --- db/derby/docs/trunk/src/tools/ctoolsopttools.dita (added) +++ db/derby/docs/trunk/src/tools/ctoolsopttools.dita Tue Jan 22 22:24:06 2013 @@ -0,0 +1,32 @@ + + + + +Optional tools + + + + + + +

supports optional +tools, which you can load and unload by using the +SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure as described in +the .

+ + Propchange: db/derby/docs/trunk/src/tools/ctoolsopttools.dita ------------------------------------------------------------------------------ svn:eol-style = native Modified: db/derby/docs/trunk/src/tools/derbytools.ditamap URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tools/derbytools.ditamap?rev=1437218&r1=1437217&r2=1437218&view=diff ============================================================================== --- db/derby/docs/trunk/src/tools/derbytools.ditamap (original) +++ db/derby/docs/trunk/src/tools/derbytools.ditamap Tue Jan 22 22:24:06 2013 @@ -198,5 +198,9 @@ limitations under the License. + + + + Added: db/derby/docs/trunk/src/tools/rtoolsoptdbmetadata.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tools/rtoolsoptdbmetadata.dita?rev=1437218&view=auto ============================================================================== --- db/derby/docs/trunk/src/tools/rtoolsoptdbmetadata.dita (added) +++ db/derby/docs/trunk/src/tools/rtoolsoptdbmetadata.dita Tue Jan 22 22:24:06 2013 @@ -0,0 +1,81 @@ + + + + +Using the databaseMetaData optional tool +The databaseMetaData optional tool creates functions +and table functions corresponding to most of the methods in the +java.sql.DatabaseMetaData interface. + + +optional toolsdatabaseMetaData +databaseMetaData optional tool +toolsdatabaseMetaData optional tool + + + +

You can load and unload the databaseMetaData tool by using +the SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure. See the + for information about this +procedure.

To load the databaseMetaData tool, use the following +statement:

+call syscs_util.syscs_register_tool( 'databaseMetaData', true ) + +

This command creates metadata functions and table functions in the current +schema. The functions and table functions have the same names as the +corresponding java.sql.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 few DatabaseMetaData methods take array arguments. Because those +arguments cannot be represented as + 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 do not have corresponding metadata +routines:

getRowIdLifetime() is eliminated because + does not provide an +implementation of java.sql.RowIdLifetime.
getSchemas() is eliminated because + does not support +overloads. The more general getSchemas( String, String ) method is +included.
supportsConvert() is eliminated because + does not support +overloads. The more general supportsConvert( int, int ) is included.

When you have finished joining metadata results, you can drop this package of +functions and table functions as follows:

+call syscs_util.syscs_register_tool( 'databaseMetaData', false ) + +

+ + Propchange: db/derby/docs/trunk/src/tools/rtoolsoptdbmetadata.dita ------------------------------------------------------------------------------ svn:eol-style = native Added: db/derby/docs/trunk/src/tools/rtoolsoptforeignviews.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/tools/rtoolsoptforeignviews.dita?rev=1437218&view=auto ============================================================================== --- db/derby/docs/trunk/src/tools/rtoolsoptforeignviews.dita (added) +++ db/derby/docs/trunk/src/tools/rtoolsoptforeignviews.dita Tue Jan 22 22:24:06 2013 @@ -0,0 +1,96 @@ + + + + +Using the foreignViews optional tool +The foreignViews optional 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. + + +optional toolsforeignViews +foreignViews optional tool +toolsforeignViews optional tool + + + +

You can load and unload the foreignViews tool by using +the SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure. See the + for information about this +procedure.

To load the foreignViews tool, use a statement like the +following:

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

The two trailing arguments have the following meanings:

foreignDatabaseURL is a URL suitable for creating a connection to +the foreign database by calling java.sql.DriverManager.getConnection(). +For example: +'jdbc:derby:db3;user=fred;password=fredpassword' +
'XYZ_' is a string prefixed to the names of all schemas +created by this tool. This argument may be omitted. If it is omitted, the tool +will create schemas which have the same names as the schemas in the foreign +database.

For example, 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 + 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 by using the following SELECT statements:

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

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

+ + Propchange: db/derby/docs/trunk/src/tools/rtoolsoptforeignviews.dita ------------------------------------------------------------------------------ svn:eol-style = native