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 Mon, 16 May 2011 19:17:13 GMT
Hi Tim,

One comment below:

On 5/13/11 6:49 AM, Tim Dudgeon wrote:
> Hi Rick,
> Thanks for quick response.
> Thoughts below.
> Tim
> On 13/05/2011 14:22, Rick Hillegas wrote:
>> 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?
> No, I'm not using these directly. Just assuming that Derby could do it 
> more efficiently e.g. in the case of
> a < 1 and b < 2 and c < 3
> type where clauses.
> These unnecessarily cause a nesting of Restriction elements, and 
> result in unnecessary brackets being applied. I expect this does not 
> have any impact on query execution, but it just looks ugly!
>> 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.
> I think they are correct, but unnecessary.
> No real problem with this. Just seems a little untidy.
>>> 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.
> OK, good. Thanks.
>>> 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.
> I think the obvious case is MySQL that needs to use backticks for 
> quoting identifiers?
> I haven't tested, but I imagine the generated SQL will not work on MySQL.
> A library of translators seems like a good plan. Visitor pattern 
> springs to mind.
> Something to handle an entire select statement would be even nicer.
> Does derby have similar classes for handling things other than the 
> where clause?
Maybe we could build out ForeignTableVTI and find a place for it in 

>> Thanks,
>> -Rick
>>> Tim

View raw message