Hi,
I am currently looking into the scape text problems (TORQUE-57), and, as
always when it comes changing the SQL generation, one sees one thing after
another that should be changed. So I ran into the issue of
case-sensitive and case-insensitive compares (for case insensitive
likes, postgres uses ILIKE, but most other databases have to do
UPPER()). When testing this, I was astonished to see that mysql by default
does case insensitive comparisons by default.
Now I'm asking myself the following:
- are there other databases where string comparisons are case insensitive
by default ? To get clues, I looked at the DB.ignoreCase() method in the
dapters, and I found that in addition to mysql, there is no action
performed in the following databases :
DBAxion, DBCloudscape, DBInformix, DBInstantDB, DBWeblogic
Does anybody know wheter comparison is indeed case-insensitive in these
databases ?
- Of course this different behaviour is not good for compatibility between
databases. One can force mysql to make case sensitive comparisons by
adding the keyword BINARY to the comparison. The manual says this can have
the following side effects
1) trailing spaces are important (so obviously, they are not important in
usual comparisons, which sounds also strange to me)
2) it may happen that indices are not used efficiently.
Given this, forcing mysql to compare case-sensitive by default is not a
good idea in my opinion. However, what we could do is the following:
- use a Boolean instead of a boolean for ignoreCase in Criteria and
Criterion. Default would be null (I don't care whether comparison is case
sensitive or case insensitive) and would use the database default. Setting
this to Boolean.TRUE would mean "force comparison to be case-sensitive"
and setting it to Boolean.FALSE would mean "force comparison to be case
insensitive". We could throw an SqlException if a particular database does
not offer a particular mode.
Of course, this is a change in the API, so this would definitely not be in
3.2.1, but I'd target that in 4.0
Any opinions ?
Thomas
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
