db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Limitations of org.apache.derby.vti.Restriction classes
Date Fri, 13 May 2011 10:22:51 GMT
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


Mime
View raw message