You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Rick Hillegas <ri...@oracle.com> on 2010/12/10 18:46:19 UTC
productizing the metadata wrapper functions
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
Re: productizing the metadata wrapper functions
Posted by Rick Hillegas <ri...@oracle.com>.
Thanks for the quick response, Kathey. Some comments inline...
On 12/10/10 10:09 AM, Kathey Marsden wrote:
> On 12/10/2010 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.
>
> Thank you Rick for this work. I have not looked closely at it but am
> wondering if you are proposing that these be exposed as a stable or
> unstable interface
I don't see much danger that the signatures of the functions will
change. They are pretty much the signatures of the corresponding
metadata methods. They might become redundant, though, if someone
implements the Information Schema. We also might rue our choice of
system schema and want to move the methods to another schema.
> and also wonder if we have a
> call syscs_util.syscs_load_metadata_wrappers( true ); , if there
> should also a be an unload or if the load method should reload new
> definitions.
I was thinking that you would unload the wrappers by invoking the new
system procedure like this:
call syscs_util.syscs_load_metadata_wrappers( false );
> I can't remember if columns might get added for metadata calls with
> new JDBC versions. It seems to me there is some sort of precedence
> for this. Even if there is just a bug it would be great to be able to
> drop and reload.
JDBC 4.0 added columns to some of the metadata ResultSets. JDBC 4.1 will
add a couple new methods to DatabaseMetaData. As you note, we should
make it possible to unload the old wrappers and load the new ones.
>
> Again having not looked at this, I don't understand how confident we
> will never want to change the wrappers, but it might be nice to first
> just expose them on a Wiki page as unstable and subject to change
> until they have been vetted
> instead of putting them in the formal documentation right away.
> Pardon my fear of compatibility commitments #:)
I share your concern about creating new compatibility commitments for a
large api. Documenting this on the wiki as an experimental feature
sounds prudent to me. This would discourage 3rd parties from building
tools using these wrappers. However, we would still be able to use the
methods ourselves and we could cite them in our tech support responses.
Thanks,
-Rick
>
>
> Thanks
>
> Kathey
>
>
>
>
>
Re: productizing the metadata wrapper functions
Posted by Kathey Marsden <km...@sbcglobal.net>.
On 12/10/2010 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.
Thank you Rick for this work. I have not looked closely at it but am
wondering if you are proposing that these be exposed as a stable or
unstable interface and also wonder if we have a
call syscs_util.syscs_load_metadata_wrappers( true ); , if there should
also a be an unload or if the load method should reload new
definitions. I can't remember if columns might get added for metadata
calls with new JDBC versions. It seems to me there is some sort of
precedence for this. Even if there is just a bug it would be great to be
able to drop and reload.
Again having not looked at this, I don't understand how confident we
will never want to change the wrappers, but it might be nice to first
just expose them on a Wiki page as unstable and subject to change until
they have been vetted
instead of putting them in the formal documentation right away. Pardon
my fear of compatibility commitments #:)
Thanks
Kathey
Re: productizing the metadata wrapper functions
Posted by Rick Hillegas <ri...@oracle.com>.
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
>
>
Re: productizing the metadata wrapper functions
Posted by Rick Hillegas <ri...@oracle.com>.
Thanks for continuing the discussion, Bryan. Some more comments inline...
On 12/10/10 8:27 PM, Bryan Pendleton wrote:
>> These wrapper functions let you use SQL to examine
>> database, ResultSet, and parameter metadata.
>
> The database metadata table function seems quite natural and powerful.
>
> I'm less clear on how to use the RS and Parameter functions; I looked
> at the examples in DERBY-4926 and DERBY-4927 but they seemed pretty
> artificial. Do you have more substantial examples? Also, would these
> latter two functions be useful in a JDBC program? Or are they something
> that would mostly be useful in IJ?
Agreed, it's harder to come up with use-cases for the RS and PMD
examples. I have found them useful for using ij to measure a database's
JDBC compliance. These functions don't add much value to a Java program.
We could leave them out.
>
> I guess what I'm asking is: could I somehow pass an actual ResultSet
> object or PreparedStatement object to one of these functions? Or do
> they always have to take a query string and compile it themselves?
They take a query string.
>
> Lastly, why does it take several seconds to register these functions?
> Is it due to how they are packaged? (That is, if they were embedded
> in derby.jar would the registration go faster?) I'm a bit surprised
> that something as seemingly trivial as registering three table
> functions could take that much time.
Right. Sorry for not explaining this in greater depth. A wrapper
function is registered for each method in DatabaseMetaData. So that's
about 170 functions which have to be registered. I think that if they
were directly registered by DataDictionaryImpl, it would be faster--that
would just involve stuffing 170 rows into a catalog. What I was
measuring was registration via a series of compiled CREATE FUNCTION
statements. There's some compiler overhead here.
Thanks,
-Rick
>
> Anyway, thanks very much for doing this work, and for advancing the
> proposal; it's quite interesting and I've been learning a lot by
> reading your work and following the discussions!
>
> bryan
>
Re: productizing the metadata wrapper functions
Posted by Bryan Pendleton <bp...@gmail.com>.
> These wrapper functions let you use SQL to examine
> database, ResultSet, and parameter metadata.
The database metadata table function seems quite natural and powerful.
I'm less clear on how to use the RS and Parameter functions; I looked
at the examples in DERBY-4926 and DERBY-4927 but they seemed pretty
artificial. Do you have more substantial examples? Also, would these
latter two functions be useful in a JDBC program? Or are they something
that would mostly be useful in IJ?
I guess what I'm asking is: could I somehow pass an actual ResultSet
object or PreparedStatement object to one of these functions? Or do
they always have to take a query string and compile it themselves?
Lastly, why does it take several seconds to register these functions?
Is it due to how they are packaged? (That is, if they were embedded
in derby.jar would the registration go faster?) I'm a bit surprised
that something as seemingly trivial as registering three table
functions could take that much time.
Anyway, thanks very much for doing this work, and for advancing the
proposal; it's quite interesting and I've been learning a lot by
reading your work and following the discussions!
bryan