db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kim Haase (JIRA)" <j...@apache.org>
Subject [jira] [Closed] (DERBY-6042) Document the syscs_util.syscs_register_tool procedure added by DERBY-6022.
Date Tue, 28 May 2013 13:15:20 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Kim Haase closed DERBY-6042.
----------------------------


Closing, since changes appeared in 10.10.1.1 documentation set.
                
> 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.1.1
>            Reporter: Rick Hillegas
>            Assignee: Kim Haase
>             Fix For: 10.10.1.1
>
>         Attachments: DERBY-6042-2.diff, DERBY-6042-2.zip, 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

Mime
View raw message