db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject productizing the metadata wrapper functions
Date Fri, 10 Dec 2010 17:46:19 GMT
I am considering adding metadata wrapper functions to the Derby product. 
I would like the community's feedback on how to expose these functions. 
Also, if anyone objects to adding this behavior, I would like to 
understand your reservations.

Currently, the code is attached to DERBY-3973, DERBY-4926, and 
DERBY-4927. These wrapper functions let you use SQL to examine database, 
ResultSet, and parameter metadata. Wrappers have been written for all of 
the methods in DatabaseMetaData (scalar functions wrap scalar methods, 
table functions wrap the methods which can return ResultSets). In 
addition, two other table functions wrap ResultSetMetaData and 

Two years ago Myrna suggested that the DatabaseMetaData wrappers should 
be added to Derby (see her 2008-12-04 comment on DERBY-3973). Dag 
recently made a similar point on DERBY-4927. I think that this should be 
possible with a little effort.

The wrapper functions would support the following use-cases:

1) By joining the table functions, you can simulate many of the views 
defined in the SQL Information Schema. This makes it easier to answer 
metadata questions which we get from time to time. Right now we answer 
these questions by writing queries against the system catalogs. In some 
cases we cannot provide a catalog-based query because the information we 
need is locked inside descriptor objects in the system tables.

2) The functions take a database URL as an argument. This makes it 
possible to deploy them against foreign databases like Oracle, DB2, 
MySQL--any database which provides a JDBC driver. 3rd parties can use 
these functions in order to write database-agnostic tools.

3) Derby developers can use these functions in ij to probe how our 
metadata works. That's why they were written originally.

The actual code behind these functions weighs 31K. That would not bloat 
the product significantly, so I think it would be ok to package this 
code in the engine jar. Registering the functions takes a couple 
seconds. For that reason I believe this registration should be optional 
and not incurred by every database creation. I can imagine that loading 
and using these optional functions might look something like this:

call syscs_util.syscs_load_metadata_wrappers( true );
select type_name, minimum_scale, maximum_scale from table( 
sysmetadata.getTypeInfo() ) s;

Please let me know your thoughts about this proposal.


View raw message