[
https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12489204
]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for your feedback, Dan and Christian.
I agree that ResultSet has become very large. It is, however, the tabular
interface defined by the JDBC standard. In a previous conversation (which I
can't cite more specifically), I was persuaded that modern IDEs are very good
at stubbing out these big interfaces. The H2 template which Christian cites is
very interesting. If we think that stubbing out this interface for all
platforms is too onerous, we could consider supplying a JDBC4 template in a
demo directory--the user could prune that template back to a JSR169 compliant
form if necessary. We could even provide templates for all the JDBC levels we
support--that does not seem like a lot of work for us.
I think that, over the long haul, we will want both the api which Dan suggests
and the ResultSet api. I can see good use-cases for both.
I don't know how to map the SQL/MED model onto table functions. It seems to me
that chapter 9 of the SQL Standard (SQL/MED) defines an interface to foreign
servers. These seem to be entire SQL servers complete with their own
authentication schemes and SQL interpreters. The optimizer interface in chapter
9 applies, I believe, to "foreign tables" (defined in section 4.10.1 of that
chapter) and not to the "derived tables" returned by table functions. I am
having a hard time mapping the SQL/MED model onto table functions:
1) SQL/MED seems to assume a handshake with a wrapper interface, a gateway
which wraps the external server. Getting one's hands on one of these gateways
implies a lot of machinery that is not present for table functions.
2) The SQL/MED optimizer calls seem to assume that the external data source can
be repositioned with repeatable-read behavior. This is probably true if you are
talking to an external relational server but I don't think you can assume that
is generally true for table functions. VTICosting lets you override this
assumption.
3) The SQL/MED predicate pushing is indeed powerful. However, it again assumes
that you are dealing with a gateway which wraps a SQL interpreter.
I'm eager to see alternative optimizer apis, though. At the very least, they
will help us think through the issues. Maybe you could explain in greater
detail how you would map this "foreign table" model onto table functions.
Thanks for pointing out that the FROM clause already handles table functions
provided that they are the diagnostic VTIs. I could not find this syntax
documented in the FROM clause section of the Reference Manual but, now that you
point it out, I see that it is mentioned in the section of that manual titled
"SYSCS_DIAG diagnostic tables and functions". This is good news because it is
another chunk of work which has been done already.
> 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.
