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 (JIRA)" <de...@db.apache.org> on 2005/11/18 01:14:41 UTC
[jira] Created: (DERBY-716) Re-enable VTIs
Re-enable VTIs
--------------
Key: DERBY-716
URL: http://issues.apache.org/jira/browse/DERBY-716
Project: Derby
Type: New Feature
Reporter: Rick Hillegas
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.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Myrna van Lunteren <m....@gmail.com>.
On 7/10/07, Rick Hillegas (JIRA) <ji...@apache.org> wrote:
> [...snip...] All of the regression tests ran cleanly for me except for the wisconsin test. That test shows the following diff. It shows this diff even in a clean subversion client without my patch:
>
> 28712a28713
> > 0
> 28715a28717
> > 0
> Test Failed.
>
>
That test has been failing in the tinderbox for the last couple of
days. I know nothing further, only noticed it failed.
Myrna
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: functionTables.html
Attaching a first rev of a functional spec for this feature. I would be grateful for your feedback.
> 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.
[jira] Assigned: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas reassigned DERBY-716:
-----------------------------------
Assignee: Rick Hillegas
> 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
> Assignee: Rick Hillegas
> Attachments: functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12527159 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Attaching derby-716-07-dblook-aa.diff. This patch changes the type name returned by the RowMultiSetImpl class. The type name has been changed so that dblook will emit re-playable DDL for Table Functions. The type name has been changed from 'ROW( col1 type1, ... ) MULTISET' to 'TABLE( "COL1" type1, ... )'. Touches the following files:
M java/engine/org/apache/derby/catalog/types/RowMultiSetImpl.java
Changes the type name.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
Adjusts this test to account for the new type name.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12527487 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-07-dblook-aa.diff at subversion revision 575660.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12533472 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
I am recording here my understanding of the collation associated with string columns in the rows returned by Table Functions. The rules which I think apply are in the SQL Standard, part 2. The DDL for Table Functions is described in section 11.50. There the declaration of the types of the returned columns are governed by the same <data type> production which is used for columns in ordinary tables. The <data type> production is described in section 6.1. I therefore believe that the same rules apply for determining the collation of columns in ordinary tables and in the rowsets returned by Table Functions. For Derby this means that if a territory-based collation has been declared for the database, then all string columns returned by Table Functions must have territory-based collation. In databases which DO NOT have a territory-based collation, the collation of string columns returned by Table Functions must be the basic collation.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493525 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for the continued feedback, Dan.
> Why is the VTIEnvironment class being removed from the VTICosting interface? Does it cause issues in some way?
I had a couple issues with this class:
1) I don't know what to tell users who want to exploit this argument in their costing logic. The javadoc is not very helpful. In addition I couldn't find any explanation of this class in the Cloudscape 3.5 documentation which I consulted: There the class is mentioned as having been added for future expansion, but the methods are not explained. I could not find any examples of its actually being used by our diagnostic VTIs.
2) One of the methods in this class, isCompileTime(), seems geared toward the old Cloudscape VTIs, which were instantiated twice: at compile-time in order to bind() against the signature of the ResultSet, and at run-time in order to actually loop through the rows. This doesn't fit the ANSI scheme in which the bind() time information is declared when you CREATE the Function Table.
> The separation of the creation of the VTICosting object from the creation of the VTI class means that the costing cannot take into
> account the parameters being passed to the table function. Thus it might be hard for an application developer to have any meaningful
> costing information, defeating the whole purpose of the interface.
>
> It also limits the any class to supporting just one static method that returns a ResultSet, unless they can all share the same exact costing information.
I'm not happy with the flexibility of VTICosting and I welcome brainstorming on this topic.
The signature of the VTICosting instantiator is a tricky issue. Consider the example in my reply to Army above. Here the arguments to the VTI are not known until run time. In fact, they change as the query runs and the VTI is re-instantiated for each row in the outer query block. In this case, what would be the signature of the VTICosting instantiator?
For the moment, let's not worry about where we find the VTICosting instantiator. This could be a constructor in the Function Table's class, a distinctively named static method in that class, some other class or method bound to the Function Table via a system procedure, etc..
Instead, let's focus on the signature of the VTICosting instantiator. Here are some possibilities:
A) A 0-arg signature. This is essentially what the current spec proposes.
B) Some Derby interface like VTIEnvironment. The interface would have to be documented extensively.
C) The same signature as the Function Table itself. We would substitute conventional defaults for arguments which could not be computed at bind() time--like ? parameters and correlated column references from outer query blocks.
D) A leading prefix of the Function Table's signature. Here we would raise an exception if one of the leading arguments could not be computed at bind() time.
What are your thoughts?
> 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, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-10-datatypesCollation-aa.diff
Attaching derby-716-10-datatypesCollation-aa.diff. This adds tests verifying the following:
1) Make sure that Derby calls the expected getXXX() method for each legal datatype in the rowset returned by a table function.
2) Make sure that string columns in the returned rowset have the expected collation (described in the previous comment).
The second test uncovered a bug: we were always applying BASIC collation to string columns in the returned rowsets. This patch fixes that bug.
Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/CreateAliasNode.java
M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
Fix for the collation bug.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
New tests.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, derby-716-10-datatypesCollation-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493553 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Maybe the VTICosting instantiator could have a signature like this and it would be up to the implementation to throw a SQLException if there isn't enough information to cost the VTI:
public static VTICosting getVTICosting
(
String schemaName, // table function's schema as declared at CREATE FUNCTION time
String tableFunctionName, // table function's name as declared at CREATE FUNCTION time
HashMap functionArguments // key = arg name from CREATE FUNCTION, value = bind() time value, possibly null if bind() can't figure it out
)
throws SQLException;
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526786 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for pointing this out, Dan. I'll take a look at this.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, functionTables.html, functionTables.html, 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.
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Daniel John Debrunner <dj...@apache.org>.
Rick Hillegas (JIRA) wrote:
> [ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493550 ]
>
> Rick Hillegas commented on DERBY-716:
> -------------------------------------
>
> I searched the Derby codeline for all of the method names in VTIEnvironment. They do not appear to be invoked ...
because they would be invoked by an implementation of a virtual table,
not the engine itself.
Dan.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493550 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
I searched the Derby codeline for all of the method names in VTIEnvironment. They do not appear to be invoked anywhere although they are declared in the VTIEnvironment interface itself and in FromVTI and in VTIResultSet.
There may be some value in exposing a vacuous VTIEnvironment interface, which has no methods in it but which would be a placeholder for future expansion. I don't see the value in retaining the existing, unused methods.
The unused getSharedState() and setSharedState() methods suggest that the whole thing might be replaced with a Hashtable.
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12490135 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Dan> It's not that it's a lot of work for anyone, it moves to a model where an application that wants to support multiple JDBC environments has two unpleasant choices:
I think that Øystein ran some experiments using Derby 10.1 (supporting JDBC3) on Java 6 (which defines JDBC4). I think that Derby's JDBC3 ResultSets worked fine on Java 6 because the testing application never called JDBC4-specific methods. I'm cautiously hopeful that an application which needs to run on multiple VM revs can succeed as long as it codes to the least-common denominator.
I don't have any experience trying to run the same code on Java SE and Java ME. I have only very limited experience trying to code an application to run both places. My limited experience suggests that most people in this situation will end up writing a portablitliy layer, or even two implementations because desktop users will refuse to live with the limitations imposed by by the small-device environment.
> 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493469 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for the continued feedback, Army.
> 1) Under "Additional SELECT Syntax"
I welcome additional ideas about how to wordsmith this. I'm trying to accomplish the following:
a) Indicate that I'm not planning to support any syntax that doesn't currently work for the diagnostic vtis--at the same time, I'm not planning to disable anything that is useful and already implemented.
b) Sketch what can be said about this in the user manuals.
I think I have accomplished (a) but I agree that (b) is a bit fuzzy. To me, the wording that you suggest doesn't cover the following case:
select s.schemaName, t.tableName
from sys.sysschemas s, sys.systables t
where t.schemaid=s.schemaid
and exists
(
select vti.*
from table ( syscs_diag.space_table( s.schemaName, t.tableName ) ) as vti
where vti.numfreepages > 100
);
Here the arguments to the VTI constructor are variables in the context of the outer query block but constants in the context of the inner block. What do you think of something like the following: "Table Function arguments must resolve to expressions which are evaluated once in the context of their query block. This includes literals and ? parameters but may also include the return values of function calls as well as correlated references to columns in outer query blocks."
> 2) Under "Appendix E: Sample VTI"
I am sorry that this is so confusing. In my example, I am admitedly waving my hands over the complexity of managing connections to an external database. This is not how someone would really write this VTI. In additon to the awkwardness of closing down the whole VTI, this example is simply not re-entrant: If two different connections tried to use this VTI, they would trip over one another. Writing a bullet-proof VTI like this requires some work, which I think someone will want to do (and hopefully donate to the community). I'm not taking on that task as part of writing this functional spec. I will add some words to note that I'm waving my hands 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493538 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
VTIEnvironment was (is?) meant to be a general purpose class that provides some control over & information for virtual tables, it was meant to be applicable to more than costing, despite it's class javadoc. see how it is used on the other interfaces that are applicable to virtual tables. Keeping the class may allow future expansion, removing it would hinder future expansion.
isCompileTime() may provide useful state information if use of this class is expanded in the future, though it may be ok to remove the method, since it could be added back to the class at any time. Removing the class from the methods passed to VTICosting would make it harder to add back in the future.
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493419 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
Why is the VTIEnvironment class being removed from the VTICosting interface? Does it cause issues in some way?
The separation of the creation of the VTICosting object from the creation of the VTI class means that the costing cannot take into
account the parameters being passed to the table function. Thus it might be hard for an application developer to have any meaningful
costing information, defeating the whole purpose of the interface.
It also limits the any class to supporting just one static method that returns a ResultSet, unless they can all share the same exact costing information.
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Dyre Tjeldvoll (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12559466#action_12559466 ]
Dyre Tjeldvoll commented on DERBY-716:
--------------------------------------
Hi Rick, can we resolve/close this now? As you probably have seen, I have closed the sub-issues.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, derby-716-10-datatypesCollation-aa.diff, derby-716-11-subqueries-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-05-PublicAPI-aa.diff
Attaching derby-716-05-PublicAPI-aa.diff. This exposes VTICosting and VTIEnvironment in Derby's public API as described in the "Public API" section of the functional spec.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493529 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks, Dan and Army, for the continued discussion of the parameters to Function Tables.
Army and I seem to be concerned about different issues here. I am not concerned about the type resolution of arguments to the Function Tables. This seems to me to be exactly the same resolution logic which applies to existing (non-table) functions. I am not proposing to change that logic. If the user guides don't adequately describe the type resolution of function arguments, then that is another issue and it is someone else's itch.
I am concerned about the fact that certain expressions can appear in the arguments to non-table functions but those expressions can not appear in the arguments to Function Tables. For instance,
select *
from T, TABLE( foo( T.a ) )
where bar( T.a ) = 3;
Here the expression T.a is a legal argument to bar() but not to foo().
I hope we are not talking past one another.
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: functionTables.html
Attaching second rev of the functional spec, incorporating recent feedback.
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526894 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-06-TestEncoding-aa.diff at subversion revision 575060.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12529787 ]
Knut Anders Hatlen commented on DERBY-716:
------------------------------------------
In this part of the patch
@@ -10136,6 +10136,8 @@
( <COMMA> functionTableReturnColumn( names, types ) ) *
<RIGHT_PAREN>
{
+ checkVersion( DataDictionary.DD_VERSION_DERBY_10_4, "table functions");
+
columnCount = names.size();
nameArray = new String[ columnCount ];
names.toArray( nameArray );
would it be better to replace "table functions" with "CREATE FUNCTION ... RETURNS TABLE"? I think this would look better in the message since it puts quotes around the string (Use of '{0}' requires database to be upgraded from...). It would also look better in the localized messages since the SQL keywords are the same in all locales, whereas the term "table functions" would need to be translated.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Issue Comment Edited: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12519088 ]
rhillegas edited comment on DERBY-716 at 8/10/07 12:10 PM:
---------------------------------------------------------------
Lance Andersen, JDBC spec lead, tells me that for a Table Function, DatabaseMetaData.getFunctionColumns() should not return a first row which describes the overall shape of the returned ResultSet, that is the ROW MULTISET. I will change the output of this metadata function accordingly.
was (Author: rhillegas):
Lance Andersen, JDBC spec lead, tells me that for a Table Function, DatabaseMetaData.getFunctions() should not return a first row which describes the overall shape of the returned ResultSet, that is the ROW MULTISET. I will change the output of this metadata function accordingly.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493551 ]
A B commented on DERBY-716:
---------------------------
Dan> Maybe checking the java types would lead to the same answer, but logically it's a check of
Dan> SQL types only. Table functions should follow the same logic as regular functions.
Okay.
Rick> I hope we are not talking past one another.
Oops, I think we were.
Thank you both for clarifying...
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526174 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-04-Optimizer-aa.diff at subversion revision 574276.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Derby Info: [Patch Available]
Regression tests passed cleanly on this patch except for the testTriggerNegative heisenbug documented in DERBY-1585.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12476187 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks, Dan. I think what has me confused here is the wiki page's reference to Part 13, section 8.5. That section deals with ARRAY types. I see that 8.6 addresses the case of MULTISET return types. My reading (of Part 13, Section 9.8, syntax rule 12 and Part 13, Section 8.6) is that the routine resolution on the wiki page addresses the case when PARAMETER STYLE JAVA is specified.
My itch is to re-enable the ResultSet style VTIs, which you agree are elegant and powerful. Here's a proposal:
1) We introduce a new kind of parameter style, say PARAMETER STYLE DERBY. We can sand down the name of this style later on.
2) For a first rev, the customer must specify PARAMETER STYLE DERBY when they declare a table-returning function.These methods resolve to static methods which return ResultSets.
3) Later on, someone who has the itch can implement PARAMETER STYLE JAVA and the kinds of method signatures described in the wiki page.
4) Perhaps someone can contribute some machinery which will wrap DERBY-style methods inside JAVA-style ones and vice-versa.
I think this proposal
A) Enables a powerful programming model which distinguishes Derby
B) Nevertheless allows later implementation of the ANSI method-style for people who are interested in migrating table-functions to/from other databases.
> 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
>
> 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12535757 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-10-datatypesCollation-aa.diff at subversion revision 585710.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, derby-716-10-datatypesCollation-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12490115 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
Some comments on the spec:
I think the parameter style should be more specific than "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby specific types that could be added here, e.g. RSS.
"When you issue a query against a Table Function, Derby constructs a ResultSetMetaData for the result, based on the column names and datatypes you declared when you initially created the Table Function."
Not sure what this is really trying to say. Why would Derby create a ResultSetMetaData based upon the functions shape, what is this used for?
I don't see from the functional specification how VTICosting is tied in? What does the app developer do?
How about the Pushable interface, that's useful existing functionality as well?
> 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-04-Optimizer-aa.diff
Attaching derby-716-Optimizer-aa.diff. This is the work described in the functional spec section titled "Optimizer Support". Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
Adds optimizer support for Derby Style Table Functions.
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
Adds a new error message.
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
Adds test cases for this work.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12492109 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for the great feedback, Army. Your comments will help simplify the next rev of this spec.
> Under the "New SELECT Syntax" section:
You are correct: I left out the parentheses needed by the TABLE constructor in both the syntax description and the example.
> A standalone VALUES clause can include other types of expressions, as well. For example:
Function Table invocations will support the same spectrum of expressions that currently work with the diagnostic VTIs. I will reword this section.
> Under "Type System"
Yes, I think we need a new datatype for Function Tables, which will be returned by the corresponding RoutineAliasInfo.getReturnType() method. This returned datatype, in turn, can be used:
1) by the GetProcedureColumns diagnostic vti, which decodes the RoutineAliasInfo on behalf of java.sql.DatabaseMetaData.getFunctionColumns()
2) by our bind() logic in order to determine the names and types of columns in the derived table
Because this datatype is part of RoutineAliasInfo, it will be serialized to SYSALIASES.ALIASINFO.and that is why it has its own Formatable id.
You are, of course, correct that this is mostly an internal, implementation detail. This new datatype will only appear to users accidentally since it is part of the contents of SYSALIASES.ALIASINFO, which users can select and display. We won't be documenting this in the user guides. I will explain this better in the next rev of the spec.
This particular implementation seems like a fairly straightforward way to deliver (1) and (2). If you have another idea how to implement (1) and (2), please let me know.
> 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511568 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
I see that isRowMultiSet is used to indicate the function is a table function. Would it not be clearer to have an explict state in RoutineAliasInfo that the function is a table function, rather than overloading the return type to indicate this?
Also look at DERBY-2917, I'm trying to separate TypeDescriptor & DataTypeDescriptor, thus the cast you didn't want to add won't be valid soon.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12490111 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
Rick > 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.
It's not that it's a lot of work for anyone, it moves to a model where an application that wants to support multiple JDBC environments has two unpleasant choices:
- have a Java class with multiple versions (doesn't fit well into the Java development model)
- have different versions of the SQL schema for different platforms (different create function statements that point to different java classes)
Maybe that's not a huge concern, Derby could just say it has the facility to present a JDBC ResultSet as a virtual table and it's the user's problem if they want to support multiple environments.
> 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.
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Army <qo...@gmail.com> writes:
> Rick just posted about the diagnostic syntax. The CREATE TABLE AS
> ... syntax was added in 10.3 as part of DERBY-64. But it looks like
> there was no corresponding documentation. If nothing exists in the
> current documentation then I guess a new Jira should be opened for
> this...?
Thanks, Rick and Army. I filed DERBY-2593 for the missing doc for
DERBY-64.
Dag
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Army <qo...@gmail.com>.
Dag H. Wanvik wrote:
>>
>> ij> create table st as select * from
>> table (syscs_diag.space_table('T1')) s with no data;
>
> Is this form documented anywhere? I can see it in the grammar, but I
> can't find it anywhere in the docs. Omission?
Not sure if you are referring to the SYSCS_DIAG.SPACE_TABLE syntax or the
"create table AS ... with no data" option...
Rick just posted about the diagnostic syntax. The CREATE TABLE AS ... syntax
was added in 10.3 as part of DERBY-64. But it looks like there was no
corresponding documentation. If nothing exists in the current documentation
then I guess a new Jira should be opened for this...?
Army
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Rick Hillegas <Ri...@Sun.COM>.
Dag H. Wanvik wrote:
> "A B (JIRA)" <ji...@apache.org> writes:
>
>
>> Or as an alternate example, Derby currently supports the following:
>>
>> ij> create table st as select * from
>> table (syscs_diag.space_table('T1')) s with no data;
>>
>
> Is this form documented anywhere? I can see it in the grammar, but I
> can't find it anywhere in the docs. Omission?
>
> Thanks,
> Dag
>
Hi Dag,
This syntax does not seem to be documented where you'd expect it, that
is, in the Reference Manual section on the FROM clause. However, an
example of this syntax appears in the Reference Manual section titled
"SYSCS_DIAG diagnostic tables and functions".
Regards,
-Rick
>
>> ij> insert into st select * from table (syscs_diag.space_table('T1')) s;
>>
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
"A B (JIRA)" <ji...@apache.org> writes:
> Or as an alternate example, Derby currently supports the following:
>
> ij> create table st as select * from
> table (syscs_diag.space_table('T1')) s with no data;
Is this form documented anywhere? I can see it in the grammar, but I
can't find it anywhere in the docs. Omission?
Thanks,
Dag
>
> ij> insert into st select * from table (syscs_diag.space_table('T1')) s;
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12491734 ]
A B commented on DERBY-716:
---------------------------
I took a look at the functional spec for this issue and had the following questions. Apologies in advance for anything painfully obvious that I may have missed...
Under the "New SELECT Syntax" section:
-------------------------------------------
- Syntax for "TableFunctionInvocation" is missing parentheses that are required according to SQL 2003 7.6 <table function derived table>. Need a left paren before "function-name" and a right paren at the end of the line.
I think the example at the end of this section needs to be updated, as well.
- "Value - A Value is an expression which could appear as a column in a standalone VALUES
statement. That is, it is built out of literals, '?' parameters, and function calls."
A standalone VALUES clause can include other types of expressions, as well. For example:
ij> values (select distinct 2 from sys.systables);
Would this kind of expression be allowed within a TableFunctionInvocation? I'm assuming not, but just thought I'd ask. Note that such an expression is *not* currently allowed with the SYSCS_DIAG table functions (DERBY-2152).
Also, what does "function calls" mean here? Is this just referring to the SQL functions supported by Derby? Or are we talking about JDBC escape functions and/or other user-defined functions ("CREATE FUNCTION") as well?
Under "System Tables":
----------------------------
- "Each column in the returned table is represented by a row in SYSCOLUMNS just as each
function parameter is."
It's not clear to me why we need to store information about the function parameters in SYSCOLUMNS. The Derby documentation says that SYSCOLUMNS "Describes the columns within all tables in the current database." I'm not sure how function parameter info fits that description. Can you elaborate on this?
Note that based on some simple testing it would appear that we do *not* include info about parameters for other Derby functions. Ex:
ij version 10.3
ij> connect 'dbdb;create=true';
ij> select count(*) from sys.syscolumns;
1
-----------
119
1 row selected
ij> create function myf (i int, vc varchar(200)) returns integer
parameter style java language java external name 'hmm.myFunction';
0 rows inserted/updated/deleted
ij> select count(*) from sys.syscolumns;
1
-----------
119
1 row selected
We can see that no rows are added for the function parameters "i" nor for "vc". And that seems fine since information about the parameters is, as the spec says, available from the DBMD.getFunctionColumns() method. So what is the need for having rows in SYSCOLUMNS for parameters defined in a table function?
And going one step further, it even seems odd to me to keep the return columns themselves in SYSCOLUMNs. For one thing, a table function is ultimately a *function*, not a *table*, and hence doesn't fall into the category of "all tables in the current database". The argument here may be that the function ultimately _returns_ a table, and that the resultant table _does_ fit the description of "all tables in the current database". But in that case I think it's worth mentioning that the result is a *derived* table and is therefore neither persistent (the function is persistent but the table is not) nor referenceable outside of a specific query. So it seems odd to me to add rows for such a table into SYSCOLUMNS.
But it's quite possible I'm missing something obvious, so feel free to correct me :)
Under "Type System"
------------------------
- "We introduce a new Derby type RowMultisetImpl. This is the return type of Table Functions.
A RowMultisetImpl contains all of the column names in the returned table as well as their
datatypes."
Okay, sorry for the really dumb question, but...how does this new type fit into the picture?
From the spec the only use I can see is that this is what will be returned from a call to the proposed "getReturnType()" method on RoutineAliasInfo.
In particular, I'm wondering if this is something the user will see, or is this just internal to Derby? If it's internal to Derby then what is the need for this type here? The reason I ask is that we already have diagnostic table functions working in Derby (see DERBY-1852) and those functions do not depend on any new types; so what is it about the proposed DERBY-716 changes that require the new RowMultisetImpl type? Is this related to the fact that we want the table function to map to a static Java method (which is different from the diagnostic VTIs)?
Also, it's not immediately clear to me why we need a new Formatable id for this type. My underlying assumption here is that a formatable id is only required for serialization of the object (typically for writing/reading to/from disk). This is based on the following javadoc from iapi/services/io/StoredFormatIds:
A format id identifies a stored form of an object for the
purposes of locating a class which can read the stored form and
reconstruct the object using the java.io.Externalizable interface.
So can you envision any places where we would need to store/reconstruct a RowMultisetImpl via (de-)serialization? It seems like if we want to store the derived table result persistently we would just create an actual table and then select into it. Which is, for example, exactly what the example shown in the "New SELECT Syntax" section shows:
INSERT INTO employees
SELECT s.*
FROM TABLE (externalEmployees('jdbc:mysql://localhost/hr')) s;
Or as an alternate example, Derby currently supports the following:
ij> create table st as select * from
table (syscs_diag.space_table('T1')) s with no data;
ij> insert into st select * from table (syscs_diag.space_table('T1')) s;
There are of course other internal reasons to serialize an object, ex. iapi/types/SqlXmlUtil is serialized (and therefore needs a format id) because it used as a "saved object". Do you foresee a scenario where this could happen with user-defined table functions?
> 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.
[jira] Issue Comment Edited: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511554 ]
Daniel John Debrunner edited comment on DERBY-716 at 7/10/07 1:09 PM:
----------------------------------------------------------------------
Is there a need to add isRowMultiSet() to TypeDescriptor? It looks out of place in that interface.
All the other isXXXX() methods are on TypeId, and in fact the DataTypeDescriptor.isRowMultiSet() just calls the TypeId method.
Also the TypeDescriptorImpl version just returns false which looks wrong, or an indication the method should not be on TypeDescriptor.
was:
Is there a need to add isRowMultiSet() to TypeDescriptor? It looks out of place in that interface.
All the other isRowMultiSet() methods are on TypeId, and in fact the DataTypeDescriptor.isRowMultiSet() just calls the TypeId method.
Also the TypeDescriptorImpl version just returns false which looks wrong, or an indication the method should not be on TypeDescriptor.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12476013 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for helping work through the details here. You are, of course, correct that the column definitions are mandatory.
I looked at the http://wiki.apache.org/db-derby/JavaTableFunctions wiki page. It seems to describe the layout and behavior of functions which return ARRAY datatypes. I do not understand how this applies to functions which return TABLE types. The SQL Standard sections on user-defined-routines are very long and, at least for me, hard to read. Still, as I read the SQL Standard, the return type of a table function is equivalent to a MULTISET datatype, not an ARRAY type. That, at least, is how I read Part 2, Section 11.50, Syntax Rule 4.
Could you point me at the chapter and verse which leads you to believe that Java TABLE functions should be implemented as ARRAY returning functions?
> 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
>
> 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Mike Matrigali (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-716?page=all ]
Mike Matrigali updated DERBY-716:
---------------------------------
Component: SQL
> Re-enable VTIs
> --------------
>
> Key: DERBY-716
> URL: http://issues.apache.org/jira/browse/DERBY-716
> Project: Derby
> Type: New Feature
> Components: SQL
> Reporter: Rick Hillegas
>
> 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.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-07-dblook-aa.diff
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493503 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
> A table function argument A[i] can be any expression whose corresponding JDBC type is the same
> as the JDBC type which corresponds to the SQL type of the function's declared parameter P[i].
Why is JDBC being mentioned here? Parameters for table functions are declared as SQL types
and table functions are executed in SQL statements. There is no relationship to JDBC.
JDBC only defines the mapping of the declared parameter type to the Java type, it does not affect
the SQL compilation/binding.
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493405 ]
A B commented on DERBY-716:
---------------------------
Thank you for answering my previous questions, Rick, and for incorporating my feedback into the second version of the spec. I took a look at the latest spec and I have the following questions...
1) Under "Additional SELECT Syntax"
"Value - A Value is an expression which could appear as an argument in the invocation
of one of the diagnostic VTI functions. This includes literals and '?' parameters."
I wonder if it wouldn't be better to just explicitly state what is allowed here, instead of referencing the diagnostic VTIs? I.e. "Value" can be any expression which evaluates to a single value whose corresponding JDBC type equals the JDBC equivalent of the relevant function parameter's declared SQL type.
That's a mouthful (you'll probably want to wordsmith it a bit), but as an example:
CREATE FUNCTION externalEmployees (LAST_NAME VARCHAR(50))
RETURNS TABLE ...
The function parameter "LAST_NAME" has a declared SQL type of VARCHAR. The JDBC equivalent to this type is String. Call this PARAM_JDBC_TYPE. Then when calling the function:
SELECT * FROM TABLE (externalEmployees( <Value> )) as EMP
<Value> can be any expression that evaluates to a type whose JDBC equivalent is PARAM_JDBC_TYPE. One exception here may be LOBs; I don't think Derby allows passing of LOBs as function parameters?
In this case PARAM_JDBC_TYPE is "String", so <Value> can be any character expression. And yes, this includes literals and '?' parameters.
Note that something like:
SELECT * FROM TABLE (externalEmployees(SELECT DISTINCT 'hi' FROM SYS.SYSTABLES)) as EMP
would not work because the subquery returns "a result set with a single row", which is not the same as "a single value".
2) Under "Appendix E: Sample VTI"
It's great to have an example, so thank you for putting this together. Some initial comments...
A -- The javadoc for the class includes:
* 3) When you are done siphoning out the rows you need, release the
* connection to the external database:
*
* EmployeeTable.close();
I don't quite understand who the "you" is in this sentence? It sounds like it's referring to the user, but it seems odd to me to expect that the user is responsible for explicitly calling "close" on the VTI class.
Is the assumption here that an application will typically execute code such as:
ResultSet rs = conn.createStatement().executeQuery(
"select * from TABLE (employeeTable()) emps");
while (rs.next())
{
...
}
rs.close();
EmployeeTable.close();
If this is not what you had in mind, can you perhaps include an example program that would call the EmployeeTable VTI, process results, and then clean up?
Intuitively I would expect that a call to "rs.close()" internally leads Derby to call "close()" on the VTI class, sparing the user the need to do so. Which brings me to my next question...
B -- What is "rs" in the following:
ResultSet rs = conn.createStatement().executeQuery(
"select * from TABLE (employeeTable()) emps");
Is it:
a) The exact same ResultSet object that is returned from EmployeeTable.read()
b) A Derby ResultSet that somehow wraps the the EmployeeTable VTI
c) A Derby ResultSet that somehow wraps the ResultSet returned from EmployeeTable.read()
d) Something else entirely?
If it's "a" then the user/app would indeed be responsible for calling EmployeeTable.close() explicitly, which seems odd. If it's "b" then Derby can internally propagate "rs.close()" to EmployeeTable.close(), but would not have direct access to the underlying result set (or would it?). If it's "c" then Derby has more control over the behavior of the result set and can propagate calls on "rs" to the underlying (user-defined) ResultSet--but Derby would not be able to call methods on the VTI itself (such as EmployeeTable.close()). Can you say which of these, if any, correlates to your plans for VTIs?
Thanks for your patience as I try to wrap my head around this...
> 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, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: functionTables.html
Attaching 3rd rev of the functional spec. This incorporates feedback from Army and Dan.
Notably, I have restored the VTIEnvironment argument to the signatures of the VTICosting methods. I think that we could use VTIEnvironment to carry the function-invocation args whose values are known at optimize() time. This deserves more discussion and would be, I think, a related, follow-on JIRA.
> 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, functionTables.html, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-11-subqueries-aa.diff
Attaching derby-716-11-subqueries-aa.diff. This adds a test case to TableFunctionTest, verifying that user-written table functions behave correctly when invoked in subqueries with correlated references to outer query blocks.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, derby-716-10-datatypesCollation-aa.diff, derby-716-11-subqueries-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Christian d'Heureuse (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12476195 ]
Christian d'Heureuse commented on DERBY-716:
--------------------------------------------
Part 2 section 9.15 (Execution of multiset-returning functions) Rule 6 states: "The General Rules of Subclause 9.14 (Execution of array-returning functions) are applied...".
Therefore Part 13 section 8.5 (Execution of array-returning functions), which modifies Part 1 section 9.14, indirectly also applies to functions returning MULTISETs.
> 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
>
> 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Derby Info: (was: [Patch Available])
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Christian d'Heureuse (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12488968 ]
Christian d'Heureuse commented on DERBY-716:
--------------------------------------------
In H2 (http://www.h2database.com) there is a class called SimpleResultSet (http://www.h2database.com/javadoc/org/h2/tools/SimpleResultSet.html) which makes it easy to implement a simple stored procedure that returns a Resultset.
Example (from http://www.h2database.com/html/features.html#user_defined_functions)::
public static ResultSet simpleResultSet() throws SQLException {
SimpleResultSet rs = new SimpleResultSet();
rs.addColumn("ID", Types.INTEGER, 10, 0);
rs.addColumn("NAME", Types.VARCHAR, 255, 0);
rs.addRow(new Object[] { new Integer(0), "Hello" });
rs.addRow(new Object[] { new Integer(1), "World" });
return rs;
}
And the interface SimpleRowSource (http://www.h2database.com/javadoc/org/h2/tools/SimpleRowSource.html) can be used together with SimpleResultSet. to create rows on demand for dynamic result sets
> 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526788 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-05-PublicAPI-aa.diff at subversion revision 574945.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, functionTables.html, functionTables.html, 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.
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Rick Hillegas <Ri...@Sun.COM>.
Thanks, Dan. This is all useful feedback and will help make the next rev
of the spec clearer.
Regards,
-Rick
Daniel John Debrunner wrote:
> Rick Hillegas (JIRA) wrote:
>
>> Dan> I think the parameter style should be more specific than
>> "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby
>> specific types that could be added here, e.g. RSS.
>>
>> Sounds good to me. Maybe something shorter like DERBY_JDBC.
>
> Well, VTI's can also be written using PreparedStatement's, so that's
> why I added the RESULT_SET portion.
>
>>
>> Dan >"When you issue a query against a Table Function, Derby
>> constructs a ResultSetMetaData for the result, based on the column
>> names and datatypes you declared when you initially created the Table
>> Dan >Function."
>> Dan > Not sure what this is really trying to say. Why would Derby
>> create a ResultSetMetaData based upon the functions shape, what is
>> this used for?
>>
>> I will clarify this in the next rev of the spec. Here's the point I
>> was trying to make. Please let me know if this is still confusing:
>> The user will write a VTI, say myVTI. When the user issues "select *
>> from TABLE( myVTI( ... ) )", Derby will hand back a ResultSet, say an
>> EmbedResultSet20. The original CREATE FUNCTION statement determines
>> the shape of the metadata returned by EmbedResultSet20 regardless of
>> the shape of the metadata returned by myVTI.getResultSetMetaData().
>
> The point is valid but not in terms of mentioning JDBC ResultSet or
> ResultSetMetaData. This is SQL, a table function defines a table
> expression and its column types will be those defined by the function.
> The types for JDBC are defined by the select list, not the function
> definition directly, i.e. table functions can be used in more than a
> SELECT *.
>
>> Dan > How about the Pushable interface, that's useful existing
>> functionality as well?
>>
>> I don't see any implementations of Pushable in the Derby diagnostic
>> VTIs. Was this interface ever really used or is it, like
>> VTIEnvironment, part of someone's future plans?
>
> Yes, it was used and worked, it's a useful addition, especially if the
> ResultSet is coming from a back-end SQL database.
>>
>> In any event, I was only spec'ing read-only table functions, that is,
>> ones that implement ResultSet. From its javadoc, Pushable seems to
>> apply to read-write VTIs that implement PreparedStatement.
>
> OK, also the api I think you are proposing would preclude Pushable,
> though maybe it can use the same mechanism as the costing api.
>
> Note though that I wouldn't think of the VTIs as ResultSet=Readonly
> and PreparedStatement=Read-write. The use of PreparedStatement gives
> one a much cleaner separation between compile time and execute time,
> something that is very beneficial, even for read-only virtual tables.
>
> Dan.
>
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Daniel John Debrunner <dj...@apache.org>.
Rick Hillegas (JIRA) wrote:
> Dan> I think the parameter style should be more specific than "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby specific types that could be added here, e.g. RSS.
>
> Sounds good to me. Maybe something shorter like DERBY_JDBC.
Well, VTI's can also be written using PreparedStatement's, so that's why
I added the RESULT_SET portion.
>
> Dan >"When you issue a query against a Table Function, Derby constructs a ResultSetMetaData for the result, based on the column names and datatypes you declared when you initially created the Table Dan >Function."
> Dan > Not sure what this is really trying to say. Why would Derby create a ResultSetMetaData based upon the functions shape, what is this used for?
>
> I will clarify this in the next rev of the spec. Here's the point I was trying to make. Please let me know if this is still confusing: The user will write a VTI, say myVTI. When the user issues "select * from TABLE( myVTI( ... ) )", Derby will hand back a ResultSet, say an EmbedResultSet20. The original CREATE FUNCTION statement determines the shape of the metadata returned by EmbedResultSet20 regardless of the shape of the metadata returned by myVTI.getResultSetMetaData().
The point is valid but not in terms of mentioning JDBC ResultSet or
ResultSetMetaData. This is SQL, a table function defines a table
expression and its column types will be those defined by the function.
The types for JDBC are defined by the select list, not the function
definition directly, i.e. table functions can be used in more than a
SELECT *.
> Dan > How about the Pushable interface, that's useful existing functionality as well?
>
> I don't see any implementations of Pushable in the Derby diagnostic VTIs. Was this interface ever really used or is it, like VTIEnvironment, part of someone's future plans?
Yes, it was used and worked, it's a useful addition, especially if the
ResultSet is coming from a back-end SQL database.
>
> In any event, I was only spec'ing read-only table functions, that is, ones that implement ResultSet. From its javadoc, Pushable seems to apply to read-write VTIs that implement PreparedStatement.
OK, also the api I think you are proposing would preclude Pushable,
though maybe it can use the same mechanism as the costing api.
Note though that I wouldn't think of the VTIs as ResultSet=Readonly and
PreparedStatement=Read-write. The use of PreparedStatement gives one a
much cleaner separation between compile time and execute time, something
that is very beneficial, even for read-only virtual tables.
Dan.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12490163 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks again for the feedback, Dan.
Dan> I think the parameter style should be more specific than "DERBY", say "DERBY_JDBC_RESULT_SET", there may be other Derby specific types that could be added here, e.g. RSS.
Sounds good to me. Maybe something shorter like DERBY_JDBC.
Dan >"When you issue a query against a Table Function, Derby constructs a ResultSetMetaData for the result, based on the column names and datatypes you declared when you initially created the Table Dan >Function."
Dan > Not sure what this is really trying to say. Why would Derby create a ResultSetMetaData based upon the functions shape, what is this used for?
I will clarify this in the next rev of the spec. Here's the point I was trying to make. Please let me know if this is still confusing: The user will write a VTI, say myVTI. When the user issues "select * from TABLE( myVTI( ... ) )", Derby will hand back a ResultSet, say an EmbedResultSet20. The original CREATE FUNCTION statement determines the shape of the metadata returned by EmbedResultSet20 regardless of the shape of the metadata returned by myVTI.getResultSetMetaData().
Dan >I don't see from the functional specification how VTICosting is tied in? What does the app developer do?
Thanks, I will explain this in the next rev of the spec.
Dan > How about the Pushable interface, that's useful existing functionality as well?
I don't see any implementations of Pushable in the Derby diagnostic VTIs. Was this interface ever really used or is it, like VTIEnvironment, part of someone's future plans?
In any event, I was only spec'ing read-only table functions, that is, ones that implement ResultSet. From its javadoc, Pushable seems to apply to read-write VTIs that implement PreparedStatement.
> 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511533 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-01-basic-aa.diff at subversion revision 555032. I merged again today. All of the regression tests ran cleanly for me except for the wisconsin test. That test shows the following diff. It shows this diff even in a clean subversion client without my patch:
28712a28713
> 0
28715a28717
> 0
Test Failed.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12519088 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Lance Andersen, JDBC spec lead, tells me that for a Table Function, DatabaseMetaData.getFunctions() should not return a first row which describes the overall shape of the returned ResultSet, that is the ROW MULTISET. I will change the output of this metadata function accordingly.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12518717 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-02-DatabaseMetaData-aa.diff at subversion revision 564208.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-716?page=comments#action_12438229 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
I think the syntax of the RETURNS TABLE clause above is incorrect, I don't believe the column definitions are optional.
Implementing a table function in Java using ResultsSet is non-standard, here are some notes on the official standard Java mapping:
http://wiki.apache.org/db-derby/JavaTableFunctions
I do think the ResultSet/PreparedStatement mechanism is much more elegant and powerful though.
I don't believe Derby should provide templates for ResultSet and other JDBC classes for VTIs, it's a pain to support those with multiple JDBC versions, and nowadays IDE's provide easy mechanisms to create all the methods of an interface for a given class.
> Re-enable VTIs
> --------------
>
> Key: DERBY-716
> URL: http://issues.apache.org/jira/browse/DERBY-716
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Reporter: Rick Hillegas
>
> 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.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493697 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for helping me puzzle through the usage of VTIEnvironment, Dan. How does the following sound:
1) Leave the VTIEnvironment arguments in the VTICosting methods.
2) Remove isCompileTime() from VTIEnvironment.
3) Beef up the javadoc for VTIEnvironment:
a) Note that it is information used only by user-written costing logic.
b) It allows user-written costing methods to share information with each other.
c) Its lifetime is the lifetime of a statement plan in Derby's statement cache. If the invoking statement has to be recompiled, then the VTIEnvironment variable can be used to share information across re-compilations.
If we are going to use VTIEnvironment, then I think the user guides should give an example of how to exploit this variable.
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511570 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
I see the type system details in the functional spec, but it's lacking some details. With a multiset type
- What infomation will be stored in TypeDescriptorImpl, e.g. scale, precision, type name etc.
- how does code access the types & names in the multiset?
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493498 ]
A B commented on DERBY-716:
---------------------------
> To me, the wording that you suggest doesn't cover the following case:
Can you explain more about why the example is not covered? Is it because of my use of the term "single value"? If so, we could take that part out:
Let P[i] be the i-th declared parameter for some table function. Let A[i] be the i-th argument
passed to the table function when it is called.
A table function argument A[i] can be any expression whose corresponding JDBC type is the same
as the JDBC type which corresponds to the SQL type of the function's declared parameter P[i].
In the example you give, "s.schemaName" and "t.tableName" are simply expressions (in this case, column references) whose corresponding JDBC type is String, hence they are fine (because the JDBC type of the SPACE_TABLE parameters is String, too).
> What do you think of something like the following: "Table Function
> arguments must resolve to expressions which are evaluated once in the
> context of their query block. This includes literals and ? parameters
> but may also include the return values of function calls as well as
> correlated references to columns in outer query blocks.
This seems too concentrated on the idea of "evaluated once". The important thing here isn't how many times the expression is evaluated for a given query; it's that the expression's datatype match the datatype of the declared function parameter. Sorry if my previous suggestion made it seem otherwise...
> I will add some words to note that I'm waving my hands here.
On the one hand I agree, having a solid example is not the goal of the spec. On the other hand, if we can't come up with a solid example, I wonder how complete/appropriate any proposed solution will end up being? If we cannot get a good use case of how this feature might be used, it makes it hard to know whether or not the design is going to be a good one.
An example doesn't have to do anything complex like reference an external database. It could just be something really simple that, for example, creates a 2-d array of strings and returns that as a ResultSet. Proof of concept is what I'm hoping for. As it is, I can't get a good feel for how the proposed VTIs are actually supposed to be created work from a user perspective...
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493517 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
Still not sure "Java type" is correct. Is that really the rule for deciding if the arguments can be mapped to the parameter type?
For regular (non-table) functions and procedures the check is made to see if type of the SQL argument can be stored in the
type of the SQL parameter (defined in the routine's CREATE statement). Maybe checking the java types would lead to the
same answer, but logically it's a check of SQL types only.
Table functions should follow the same logic as regular functions.
See this check in StaticMethodCallNode.java
if (! getTypeCompiler(parameterTypeId).storable(argumentTypeId, getClassFactory()))
throw StandardException.newException(SQLState.LANG_NOT_STORABLE,
parameterTypeId.getSQLTypeName(),
argumentTypeId.getSQLTypeName() );
> 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, 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.
[jira] Closed: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas closed DERBY-716.
-------------------------------
Resolution: Fixed
Closing this issue because I've finished the work I planned to do on this and the issue has been dormant for a couple months.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, derby-716-10-datatypesCollation-aa.diff, derby-716-11-subqueries-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-08-upgrade-aa.diff
Attaching derby-716-08-upgrade-aa.diff. This prevents table functions from being created after soft-upgrade. Added a unit test to verify this behavior. Touches the following files:
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
Reject table function creation if we have only soft-upgraded.
M java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_4.java
Verify correct upgrade behavior.
Committed at subversion revision 578452.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-03-DatabaseMetaData-aa.diff
Committed derby-03-DatabaseMetaData-aa.diff at subversion revision 564800. This removes the summary row 0 (describing the whole ROW MULTISET) from the ResultSet returned by DatabaseMetaData.getFunctionColumns() as Lance said is required. Touches the following files:
M java/engine/org/apache/derby/catalog/GetProcedureColumns.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12492110 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Continuing with Army's feedback:
> Under "System Tables":
You are correct. We don't currently use SYSCOLUMNS to describe the parameters and return types of functions and procedures. I can rip out this section and spare myself this chunk of work. Thanks.
> 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536989 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Committed derby-716-11-subqueries-aa.diff at subversion revision 587491.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, derby-716-10-datatypesCollation-aa.diff, derby-716-11-subqueries-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-02-DatabaseMetaData-aa.diff
Attaching derby-716-02-DatabaseMetaData-aa.diff. This patch adds TableFunction support to our implementation of DatabaseMetaData.getFunctions() and getFunctionColumns(). Touches the following files:
M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java
Add a new method so that metadata queries can determine whether a function is a TableFunction.
M java/engine/org/apache/derby/impl/jdbc/metadata.properties
M java/engine/org/apache/derby/catalog/GetProcedureColumns.java
Amend the queries and the dbproc which provide the results for getFunctions() and getFunctionColumns().
M java/shared/org/apache/derby/shared/common/reference/JDBC40Translation.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
M java/testing/org/apache/derbyTesting/functionTests/tests/jdbc4/TestDbMetaData.java
M java/testing/org/apache/derbyTesting/junit/JDBC.java
Regrssion tests.
The regression tests ran cleanly for me under Java 6 except for the outstanding orphaned ResultSet problem in ProcedureInTriggerTest. That test runs cleanly standalone. This is the same regression test behavior seen in DERBY-2983.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12476192 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks, Christian. I think I'm more or less tracking now.
> 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
>
> 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.
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Rick Hillegas <Ri...@Sun.COM>.
I agree that your syntax looks cleaner. ANSI seems to require the TABLE
keyword and parentheses, and that's what DB2 implements. Microsoft
allows your syntax.
Cheers,
-Rick
Daniel John Debrunner wrote:
>Rick Hillegas (JIRA) wrote:
>
>
>
>> [ http://issues.apache.org/jira/browse/DERBY-716?page=comments#action_12357981 ]
>>
>>Rick Hillegas commented on DERBY-716:
>>-------------------------------------
>>
>>This feature is supported by other databases, including DB2 (see http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011177.htm) and Microsoft SQL Server (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp).
>>
>>These databases largely hew to the ANSI 2003 solution:
>>
>>1) Declare a function which returns a table
>>2) Invoke the function in your query's FROM list
>>
>>The ANSI syntax for declaring VTI-returning functions is defined in Part 2, section 11.50, under the <returns table type> production. It allows you to specify a table signature in the RETURNS clause of the function declaration:
>>
>>CREATE FUNCTION functionName ...
>>RETURNS TABLE( [ [ columnName, columnDatatype ] [, columnName columnDatatype ]* ] )
>>
>>The ANSI syntax for querying a VTI is defined in Part 2, section 7.6 under the <table function derived table> production. It allows a FROM list element to be a function invocation wrapped by a TABLE constructor:
>>
>>SELECT *
>>FROM TABLE( functionName( args ... ) )
>>
>>
>
>Is the table operator required when the function is declared as
>returning a TABLE?
>
>I was hoping would would be able to do
>
>SELECT * FROM functionName( args ... )
>
>Dan.
>
>
>
>
Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Rick Hillegas (JIRA) wrote:
> [ http://issues.apache.org/jira/browse/DERBY-716?page=comments#action_12357981 ]
>
> Rick Hillegas commented on DERBY-716:
> -------------------------------------
>
> This feature is supported by other databases, including DB2 (see http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011177.htm) and Microsoft SQL Server (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp).
>
> These databases largely hew to the ANSI 2003 solution:
>
> 1) Declare a function which returns a table
> 2) Invoke the function in your query's FROM list
>
> The ANSI syntax for declaring VTI-returning functions is defined in Part 2, section 11.50, under the <returns table type> production. It allows you to specify a table signature in the RETURNS clause of the function declaration:
>
> CREATE FUNCTION functionName ...
> RETURNS TABLE( [ [ columnName, columnDatatype ] [, columnName columnDatatype ]* ] )
>
> The ANSI syntax for querying a VTI is defined in Part 2, section 7.6 under the <table function derived table> production. It allows a FROM list element to be a function invocation wrapped by a TABLE constructor:
>
> SELECT *
> FROM TABLE( functionName( args ... ) )
Is the table operator required when the function is declared as
returning a TABLE?
I was hoping would would be able to do
SELECT * FROM functionName( args ... )
Dan.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-716?page=comments#action_12357981 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
This feature is supported by other databases, including DB2 (see http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011177.htm) and Microsoft SQL Server (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp).
These databases largely hew to the ANSI 2003 solution:
1) Declare a function which returns a table
2) Invoke the function in your query's FROM list
The ANSI syntax for declaring VTI-returning functions is defined in Part 2, section 11.50, under the <returns table type> production. It allows you to specify a table signature in the RETURNS clause of the function declaration:
CREATE FUNCTION functionName ...
RETURNS TABLE( [ [ columnName, columnDatatype ] [, columnName columnDatatype ]* ] )
The ANSI syntax for querying a VTI is defined in Part 2, section 7.6 under the <table function derived table> production. It allows a FROM list element to be a function invocation wrapped by a TABLE constructor:
SELECT *
FROM TABLE( functionName( args ... ) )
This differs from the Cloudscape approach, which was to declare the VTI on the fly at query time using a constructor. The ANSI approach seems simple, powerful, and elegant enough. Here's a sketch of what we could do:
1) Enhance the CREATE FUNCTION syntax to support the RETURNS TABLE clause.
2) Enhance query specifications to allow TABLE( functionName( args ... ) ) in the FROM list
3) Expose template ResultSet and ResultSetMetaData implementations which customers can extend. We currently have versions of these in org.apache.derby.vti.VTITemplate.
4) Raise a query-execution-time exception if a) the java function does not return a ResultSet, or b) the VTI's ResultSetMetaData does not match the signature declared by CREATE FUNCTION.
> Re-enable VTIs
> --------------
>
> Key: DERBY-716
> URL: http://issues.apache.org/jira/browse/DERBY-716
> Project: Derby
> Type: New Feature
> Reporter: Rick Hillegas
>
> 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.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511554 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
Is there a need to add isRowMultiSet() to TypeDescriptor? It looks out of place in that interface.
All the other isRowMultiSet() methods are on TypeId, and in fact the DataTypeDescriptor.isRowMultiSet() just calls the TypeId method.
Also the TypeDescriptorImpl version just returns false which looks wrong, or an indication the method should not be on TypeDescriptor.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Christian d'Heureuse (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12476186 ]
Christian d'Heureuse commented on DERBY-716:
--------------------------------------------
Rick, do you mean the array arguments that are used for the OUT parameters, e.g. "double[] cost"?
You have to look at SQL/Part 13 (SQL/JRT), sections 8.5 (Execution of array-returning functions) and 8.6 (Java routine signature determination). Not only at Part 2 (Foundation)) Section 11.50 (SQL-invoked routine)..
> 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
>
> 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-01-basic-aa.diff
Attaching a patch which adds basic support for Derby-style Table Functions. This patch allows you to declare user-defined Table Functions and to invoke them in simple SELECT statements. I've included a JUnit test for this functionality. The existing regression tests pass cleanly for me.
A description of the contents of this patch follows:
-----------------------------------------------------------
(1) Created a new datatype, RowMultiSetImpl, to represent the return type of a Table Function.
M java/engine/org/apache/derby/iapi/services/io/RegisteredFormatIds.java
M java/engine/org/apache/derby/iapi/services/io/StoredFormatIds.java
M java/engine/org/apache/derby/iapi/types/TypeId.java
M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java
M java/engine/org/apache/derby/iapi/types/DTSClassInfo.java
M java/engine/org/apache/derby/catalog/TypeDescriptor.java
A java/engine/org/apache/derby/catalog/types/RowMultiSetImpl.java
M java/engine/org/apache/derby/catalog/types/TypeDescriptorImpl.java
M java/engine/org/apache/derby/catalog/types/BaseTypeIdImpl.java
-----------------------------------------------------------
(2) Added new syntax for declaring Derby-style Table Functions.
M java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java
M java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
Factored out the check for builtin VTIs so that the parser can know whether to build nodes for a constructor-based VTI or a static-method-based VTI.
M java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
M java/engine/org/apache/derby/iapi/sql/dictionary/AliasDescriptor.java
M java/engine/org/apache/derby/catalog/types/RoutineAliasInfo.java
Parser support for Table Function DDL.
-----------------------------------------------------------
(3) Supported the invocation of Table Functions in simple SELECTs.
M java/engine/org/apache/derby/impl/sql/compile/MethodCallNode.java
M java/engine/org/apache/derby/impl/sql/compile/VTIDeferModPolicy.java
M java/engine/org/apache/derby/impl/sql/compile/NewInvocationNode.java
M java/engine/org/apache/derby/impl/sql/compile/FromVTI.java
Moved some bind() logic from NewInvocationNode into its superclass, MethodCallNode. The previous scheme allowed the invocation of VTIs which are constructors. The new scheme also allows the invocation of VTIs which are static methods.
M java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
Factored out some code which builds ResultColumns. The newly factored-out method is used to bind() the shape of the return value of a Table Function.
M java/engine/org/apache/derby/impl/sql/compile/StaticMethodCallNode.java
Don't try to cast returned ResultSets.
M java/engine/org/apache/derby/iapi/sql/execute/ResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/GenericResultSetFactory.java
M java/engine/org/apache/derby/impl/sql/execute/VTIResultSet.java
Accounted for the fact that the returned columns of Table Functions are nullable according to the SQL standard.
-----------------------------------------------------------
(4) Added new error messages.
M java/engine/org/apache/derby/loc/messages.xml
M java/shared/org/apache/derby/shared/common/reference/SQLState.java
-----------------------------------------------------------
(5) Added a JUnit test for this functionality.
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/TableFunctionTest.java
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringColumnVTI.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
M java/testing/org/apache/derbyTesting/functionTests/tests/lang/SysDiagVTIMappingTest.java
A java/testing/org/apache/derbyTesting/functionTests/tests/lang/StringArrayVTI.java
M java/testing/org/apache/derbyTesting/junit/BaseTestCase.java
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-09-upgradeLocalization-aa.diff
Thanks for raising this localization issue, Knut. I like your proposal for changing the error message. I have committed that change at subversion revision 578822. I've attached the change as derby-716-09-upgradeLocalization-aa.diff. It touches one file:
sqlgrammar.jj
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, derby-716-07-dblook-aa.diff, derby-716-08-upgrade-aa.diff, derby-716-09-upgradeLocalization-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12476155 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
I don't understand the comment about ARRAY returning functions, what in the wiki page leads you to believe it describes functions returning ARRAY datatypes?
> 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
>
> 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12493506 ]
A B commented on DERBY-716:
---------------------------
Oops, thanks. Meant "Java type" in all of these cases, not "JDBC type".
> 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, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12526630 ]
Daniel John Debrunner commented on DERBY-716:
---------------------------------------------
In the squeezeString() method in TableFunctionsTest this method call is made:
new String( bytes );
That constructor for String is platform dependent, thus this test will most likely fail on platforms that have a different default encoding.
See:
http://wiki.apache.org/db-derby/AvoidNonPortableMethods
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Updated: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-716:
--------------------------------
Attachment: derby-716-06-TestEncoding-aa.diff
Attaching derby-716-06-TestEncoding-aa.diff. This makes TableFunctionTest create strings from byte arrays using UTF8 encoding.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, derby-716-02-DatabaseMetaData-aa.diff, derby-716-03-DatabaseMetaData-aa.diff, derby-716-04-Optimizer-aa.diff, derby-716-05-PublicAPI-aa.diff, derby-716-06-TestEncoding-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511576 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Hi Dan. I have added a comment to derby-2917. Thanks for tackling that project. I am very interested in this conversation. Unfortunately, tomorrow is my last day before I go on vacation (and then a conference) for two and a half weeks. So, please don't be put off by my impending radio-silence.
> I see that isRowMultiSet is used to indicate the function is a table function. Would it not be clearer to have an explict state in RoutineAliasInfo that the function is a table function, rather than overloading the return type to indicate this?
It seemed to me that a Table Function was just a function which returned a Row Multi Set. I think it would certainly be reasonable to add an isTableFunction() method to RoutineAliasInfo. However, to avoid duplicating state, I think that that method would just turn around and inspect the return type to see if it were a Row Multi Set.
> - What infomation will be stored in TypeDescriptorImpl, e.g. scale, precision, type name etc.
I'm not sure that a TypeDescriptorImpl would ever be built for a Row Multi Set as part of implementing Table Functions. The return type is never used at runtime and is only briefly inspected at compilation time in order to build the shape of the returned Table. I think you have created derby-2917 because it seems to you, too, that it's hard to understand how behavior is divided between the types in the catalog package and the types which actually are persisted to the catalogs.
> how does code access the types & names in the multiset?
This is done in FromVTI.createResultColumnsForTableFunction().
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.
[jira] Commented: (DERBY-716) Re-enable VTIs
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12511566 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
Thanks for taking a look at this patch, Dan. I think that isRowMultiSet() could be removed from TypeDescriptor. It's in there so that AliasDescriptor doesn't have to cast a TypeDescriptor to DataTypeDescriptor. I'm comfortable with that cast and agree that isRowMutliSet() looks awkward in TypeDescriptorImpl.
> 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
> Assignee: Rick Hillegas
> Attachments: derby-716-01-basic-aa.diff, functionTables.html, functionTables.html, 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.