[
https://issues.apache.org/jira/browse/DERBY-6042?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13560006#comment-13560006
]
Rick Hillegas commented on DERBY-6042:
--------------------------------------
Thanks, Kim. That was the only improvement which jumped out at me.
> 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
> Assignee: Kim Haase
> Attachments: DERBY-6042.diff, DERBY-6042.stat, DERBY-6042.zip
>
>
> 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
