[daisy] Even more DB2 porting

[Bruno Dumon]

On Thu, 2006-09-14 at 18:16 +0200, Aaron.Digulla at Globus.ch wrote:
> Hello,
> 
> With a lot of hacks and workarounds, I finally managed to setup the 
> repository and get the wiki running. I'll post a patch to JIRA for comment 
> tomorrow.
> 
> Right now, there is only one thing left which I can't figure out: The 
> query
> 
> select id where name LIKE '%word%' limit 50
> 
> finds documents in MySQL which have "word", "Word" or "WORD" in the name. 
> On DB2, this doesn't work. The actual SQL which is generated in my case is 
> this:
> 
> SELECT   Distinct documents.id,
>          document_variants.branch_id,
>          document_variants.lang_id
> FROM     documents 
>          left join document_variants on (documents.id = 
> document_variants.doc_id) 
>          left outer join document_versions on (documents.id = 
> document_versions.doc_id and document_variants.branch_id = 
> document_versions.branch_id and document_variants.lang_id = 
> document_versions.lang_id and document_variants.liveversion_id = 
> document_versions.id)
> WHERE    ( ( document_versions.name LIKE '%word%') )
> AND      document_variants.retired = 'false'
> AND      document_variants.liveversion_id != -1
> 
> I would expect to see "UCASE(document_versions.name) LIKE '%WORD%'" in 
> there to make the query case-insensitive. How does this work in MySQL? I 
> checked JdbcHelper but the method getUpperCaseFunction() is never used 
> (plus the fact that the search string is not converted either :-/).
> 
> Any ideas?

Seems like MySQL's LIKE operator is case-insentive then [1]. We'll have
to abstract this via the JdbcHelper or always use the UCASE function.

However, this pops up some questions:
 * are all string searches case sensitive in DB2? (e.g. when using '=')
 * isn't there a specific case-insensitive LIKE operator?
 * is it possible to define the columns themselves to be
case-insensitive? (so that they are indexed case-insensitively)
 * what's the situation in Oracle, Postgresql, SQL server, ...

All this also reminds me we some other case-sensitivity issues to solve,
e.g. names of document types are considered to be case-sensitive by
Daisy, but are not case-sensitive in the MySQL database.

[1] http://dev.mysql.com/doc/refman/4.1/en/case-sensitivity.html

-- 
Bruno Dumon                             http://outerthought.org/
Outerthought - Open Source, Java & XML Competence Support Center
bruno at outerthought.org                          bruno at apache.org