Here's a further refinement:
I think that we would not need to introduce the system procedure
syscs_util.syscs_load_metadata_wrappers(). Something like the following
is almost possible with Derby today:
create procedure loadDBMDWrappers( ) language java parameter style java
modifies sql data external name 'org.apache.derby.diag.DBMDWrapper.load';
call loadDBMDWrappers();
select type_name, minimum_scale, maximum_scale from table(
sysmetadata.getTypeInfo() ) s;
I say "almost" because, for security reasons, you can't invoke a
procedure which resolves to a method in Derby's package space. We would
simply need to relax this restriction and, say, let users register and
execute any public static method in the org.apache.derby.diag package.
This approach is only slightly wordier than the original proposal. It
could be a general technique for distributing optional diagnostic
packages like this.
The proposal would now be simpler:
1) Put DBMDWrapper into the org.apache.derby.diag package. It weighs 24K.
2) Allow users to register and invoke procedures in org.apache.derby.diag.
3) Create a wiki page explaining how to use the wrapper functions.
4) Use these methods for tech support and Derby development but do not
expose them in the public api or the user guides. We would not incur any
new compatibility burden.
5) We could remove the DBMDWrapper class if we got around to
implementing the SQL Information schema.
Thanks,
-Rick
On 12/10/10 9:46 AM, Rick Hillegas wrote:
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 ParameterMetaData.
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.
Thanks,
-Rick
