On 24/04/2009, at 4:02 PM, Andrus Adamchik wrote:
> Yeah, I am aware of this issue, as I am using MySQL daily. Here is
> the problem - if we force case-sensitive syntax for case-insensitive
> searches on MySQL, this will break for case-sensitive (non-default)
> collations. I am personally using case-sensitive LIKE searches in my
> code explicitly, which makes such code MySQL-specific, which is
> definitely not good, and I'd hate to recommend it to the users.
> Checking and overriding collation for every column also doesn't seem
> practical.
Well, there are two ways in MySQL to force case sensitive searches.
Either by picking a binary collation for the text field, or by
changing the column type from TEXT/VARCHAR to BLOB/CLOB. The beauty of
the latter is that Cayenne knows about the datatype and so can behave
differently. But Cayenne knows nothing about collations (perhaps it
should?).
So there is a workaround to the problem you raise. But there is no
workaround to getting case-insensitive searches (which in our use
cases are 99.9% of searches on text fields) to use an index.
The only case where existing behaviour changes due to this
modification is if:
* Binary collation (which is non-standard)
* Explicit attempt to perform non-case sensitive search (which
questions why they chose a binary collation)
The recommendation for users is to change that column to CLOB and
current behaviour continues.
What do you think?
Ari Maniatis
-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001 fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A
This archive was generated by hypermail 2.0.0 : Fri Apr 24 2009 - 02:21:54 EDT