Hi David,
Some responses inline...
On 3/12/13 6:06 AM, drv wrote:
Hi - I am the principal developer of the Gaian Database which uses old style
Derby VTIs extensively.
I am currently looking at migrating to Table Functions and have hit some
problems -
1 - The main issue is that Table functions seem to require a static return
schema defined at registration time. Our VTIs return a variable (dynamic)
result schema, based on input arguments - With VTIs, Derby obtains this
through the getMetaData() method.
The static typing of table functions is a feature of the ANSI/ISO
syntax. Each possible table shape needs to be bound by its own CREATE
FUNCTION statement. I think you have discovered that you can hide the
messy arguments by wrapping the table function invocations in helper
views. You may find that varargs routines (to be introduced by Derby
10.10) will help you get more mileage if you want to write a single Java
method which generates multiple, independently declared table functions.
Table functions only seem to make use of
the VTICosting and RestrictedVTI interfaces from what i can see.
2 - Is there any way that I can resolve the name of the table function at
run-time, from within the static method that implements it? I don't think a
"catalog look-up" based on my java method name would work because I intend
to have multiple table functions referencing it.
Are you trying to make the single Java method smart enough to figure out
at run time which table shape to return? What about giving the Java
method an argument which encodes the table shape?
3 - I've seen no mention of Table Functions supporting the IFastPath
interface. This was useful with VTIs because it avoids the tedious job of
having to implement all the ResultSet getxxx() methods.
Table functions don't take advantage of the IFastPath interface as far
as I know. But you shouldn't need to implement all of the getXXX()
methods if you use the building-block table functions which appear in
Derby's public api:
org.apache.derby.vti.StringColumnVTI
org.apache.derby.vti.VTITemplate.
4 - I've yet to experiment with Table Functions properly, but one issue I
had with VTIs was that projected columns (unlike qualifiers) were not pushed
through a VIEW defined as a SELECT * on the VTI. Is this something that
would work with Table Functions now?
Restrictions are passed through views into RestrictedVTIs, but
projections aren't yet. It's a tricky problem tracked by
https://issues.apache.org/jira/browse/DERBY-6036.
5 - As an aside - Neither VTIs nor Table Functions can have a SYNONYM
defined for them. This would be useful to avoid VIEW indirections when
trying to achieve a more standard syntax for querying the Table Function or
VTI (i.e. "select * from
" instead of "select * from new com.ibm.db2j....() T" or "select * from
TABLE(
(...) ) T")
Afraid I'm not following this point. Do you need this as a workaround
for (4) or do you have some other use case in mind?
Based on points 1 and 3 above - it seems to me that Table Functions are
currently a step behind what VTIs could offer before. Are there any plans to
improve them in future?
You are welcome to file additional enhancement requests for table
functions. We do want to make table functions even more useful.
Thanks,
-Rick
Regards
David
--
View this message in context:
http://apache-database.10148.n7.nabble.com/Limitations-of-Table-Functions-vs-old-VTIs-tp127988.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
