db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-716) Re-enable VTIs
Date Mon, 16 Apr 2007 19:29:15 GMT

    [ 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.

View raw message