db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Limitations of org.apache.derby.vti.Restriction classes
Date Fri, 13 May 2011 13:22:06 GMT
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.

> Tim

View raw message