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
