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
