db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-716) Re-enable VTIs
Date Fri, 18 Nov 2005 15:55:42 GMT
    [ 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


Mime
View raw message