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 Re: productizing the metadata wrapper functions
Date Mon, 13 Dec 2010 17:01:56 GMT
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.


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

View raw message