db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Thomas Fischer <tfisc...@apache.org>
Subject case sensitive comparisons
Date Wed, 01 Nov 2006 12:50:27 GMT
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: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message