db-derby-dev mailing list archives

Frank Griffin wrote:
> Hi All,
>
> I'm evaluating the extending of Derby for use as a JDBC access path to a
> non-SQL data engine. Conceptually, what I need to do is a CREATE TABLE
> which reflects the metadata of the result set which will be returned by
> the non-SQL engine, and then intercept SELECT requests to this table,
> and inject that result set.
>
> I've been looking at the internals documentation on the project site and
> wiki as well as the source code, but I can't quite get my hands around
> the proper way to do this. Judging by class names, the Virtual Table
> Interface seems like a likely candidate, but I can't seem to locate any
> explicit use of it. Another thought was to provide a new implementation
> of Conglomerate, but again, I'm having a difficult time in the source
> code locating exactly where this would be plugged in.
>
> Could you please advise as to the "best" way to do something like this,
> and point me to the areas of the source code involved ?
>
> Thanks in advance,
> Frank Griffin
>
Hi Frank,
It sounds as though table functions (the Virtual Table Interface you
mention) might be a good fit for this job. Table functions will be
introduced in the next Derby feature release, 10.4, which we expect to
produce around February/March. I recommend the following sources of
information to help you understand Derby's table functions:
1) The "Programming Derby-style Table Functions" section of the Derby
Developer's Guide: http://db.apache.org/derby/docs/dev/devguide/
2) The talk on table functions which I gave at Apache Con last month:
http://people.apache.org/~rhillegas/vtiDemo/doc/saucerSeparation.html
3) The demo toolkit which I wrote to support the Apache Con talk. This
toolkit lives in the development trunk at java/demo/vtis. The README
file in that directory explains how to build the toolkit and its
javadoc. In the java/demo/vtis/sql subdirectory you will find some
sample scripts which show how to declare and invoke table functions.
Take a look at the demoFileVTIs.sql script. This script shows how to
declare and use table functions which make external files look like SQL
tables. When you want to look under the hood, I recommend starting with
PropertyFileVTI.java. This table function makes a property file look
like a table with two columns (key and value). I think it's pretty
straightforward. Once you understand that table function, I would
recommend taking a look at VTIs.java. That class holds a number of table
functions which are used to present xml files as SQL tables and which
are used to access query results from other relational databases.
No doubt you will have questions as you dig into this. I am happy to
provide any help I can.
Hope this is useful,
-Rick