Thanks for the comments on Restriction, Tim. Some responses inline..
On 5/13/11 3:22 AM, Tim Dudgeon wrote:
Hi, I was looking at writing restricted table functions to retrieve
data from a foreign database, and think I have identified some
limitations of the org.apache.derby.vti.Restriction classes. I would
welcome some advice or comments on this.
1. Constructor of Restriction.AND and Restriction.OR only allows two
terms to be added. In reality and AND or OR expression could take any
number of elements, so really there should be ability to add List
(and/or Array) of elements. When Derby passes through simple
expression like
where a < 1 and b < 2 and c < 3
it ends up as a nested expression and the generated SQL starts to look
ugly.
I think there are two issues in here:
i) the usability of the Restriction constructors
ii) the readability of the toSQL() output
The constructors were designed to be used internally by Derby when
invoking a RestrictedVTI. Do you find that you are calling these
constructors directly? If so, can you tell me something more about the
use case so that we can design a friendlier constructor api?
Similarly, the output of toSQL() was meant to be consumed by a foreign
RDBMS. The output was designed to be correct ANSI/ISO SQL. It was not
designed to be friendly to read and it was not expected that it would be
parseable by non-ANSI/ISO dialects. For some foreign databases, your
RestrictedVTI may need to hand-construct a WHERE clause by walking the
Restriction tree, calling getLeftChild() and getRightChild() as you go.
But again, can you tell me more about your use case which would benefit
from more readable toSQL() output?
2. When using Restiction.toSQL() braces are not put in the rigth
places for AND and OR restictions. Generated SQL is like this:
select x, y, x from foo where ( a = 1 ) AND ( b = 2 )
Surely should be:
select x, y, x from foo where ( a = 1 AND b = 2 )
Although generated SQL seems to be legal (even when nested) its
difficult to read.
I believe that the parentheses are placed correctly. Please let me know
if you can identify a case where the generated SQL has the wrong
meaning. The scheme you suggest may work too, but it may encounter
problems in foreign databases whose operators bind in a non-ANSI/ISO order.
3. Then using text (e.g. for varchar columns) comparisons (and
presumably date) the values passed through by Derby are not quoted,
and the Restiction.toSQL() function does not add quotes. This results
in SQL like this:
select x, y, x from foo where ( name = bar)
not
select x, y, x from foo where ( name = 'bar' )
and so SQL is invalid
This is an ugly bug. Thanks for finding it. I have logged
https://issues.apache.org/jira/browse/DERBY-5231 to track this one.
4. Column names etc. are double quoted when using Restiction.toSQL().
This is fine for most DBs, but is going to give problems with some.
e.g. MySQL which uses backticks.
Right. Here you have to fall back on walking the Restriction tree
yourself, hand-generating a WHERE clause which will work for the SQL
dialect in your foreign RDBMS. I can see the value of having a library
of WHERE clause generators targeted at different SQL dialects. This is
something which the user community could help assemble. If there is
interest in donating these implementations, we could discuss where this
library should live.
Thanks,
-Rick
Tim
