[
https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12488829
]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
Using ResultSet as the api to implement table functions can be somewhat
awkward. When this VTI functionality was implemented in Cloudscape, ResultSet's
were somewhat simple, forward only, read-only and only JDBC 1.2. Now
ResultSet's need to support updates, deletes, inserts and scrolling. This means
that even a simple table function must implement a large number of methods even
if (I think) in the simplest case only four may be required (constructor, next,
getXXX and close). While one can try to create a template that has most of the
non-required methods (as Cloudscape did), this becomes a major pain if one
needs to support multiple JDBC environments, JDBC 3, 4 and JSR 169. In fact
using ResultSet means that the application developer will have a hard time
implementing a solution in a JDBC version agnostic way.
Also using ResultSet means that the application developer ends up implementing
a class that doesn't really implement the defined contract of ResultSet, just
the subset specific to Derby's functional tables, doesn't seem to be a ideal
postion to be in for an app developer.
I've been struggling with these issues while looking at the way Derby uses VTIs
(ResultSets) for triggers, currently the implementation uses classes that are
not in JSR169 but needs to because the code is compiled against JDBC 3. It just
seems to work, but may fail on some J2ME VM's that have stricter class loading
policies.
Contrast this with the standard mechanism for writing table functions in Java
that just use a single static Java method, much easier for an application
developer to work with, portable to multiple JDBC versions and easier to
understand (a single working method rather than 139 methods (in JDBC 3) and
most of them not used).
While the current Derby internal vti is quite powerful the SQL standard
provides for much of the same functionality through SQL/MED, e.g. pushing
predicates, providing optimizer costs etc.
Thus I have reservations about introducing a non-standard feature into Derby
and especially a non-standard feature that exposes a un-friendly api onto the
application developer.
I do think that Java table functions or wrappers (for updates as well) would be
a great addition to Derby, I just think that the api should be clean and easy
especially if it's not standard based.
I also have some questions on the details in the spec if the ResultSet approach
is taken, but here's one on the SQL. Why is the SQL for SELECT changing? Derby
already supports bultin table functions, so what needs to change here?
> Re-enable VTIs
> --------------
>
> Key: DERBY-716
> URL: https://issues.apache.org/jira/browse/DERBY-716
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Reporter: Rick Hillegas
> Attachments: functionTables.html
>
>
> Cloudscape used to expose Virtual Table Interfaces, by which any class which
> implemented ResultSet could be included in a query's FROM list. Derby still
> exposes a number of these VTIs as diagnostic tools. However, Derby now
> prevents customers from declaring their own VTIs. The parser raises an error
> if a VTI's package isn't one of the Derby diagnostic packages.
> This is a very powerful feature which customers can use to solve many
> problems. We should discuss the reasons that it was disabled and come up with
> a plan for putting this power back into our customers' hands.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
