db-derby-user mailing list archives

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





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



Mime
View raw message