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