Antwort: Re: [daisy] Even more DB2 porting

[Aaron.Digulla at Globus.ch]

daisy-bounces at lists.cocoondev.org schrieb am 15.09.2006 10:50:23:

> > 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, ...

In DB2, all string searches are case sensitive. There is no special LIKE 
operator. The next best thing is to use UCASE(name) LIKE 'XXX%' but then, 
no index will be used.

To allow DB2 to use an index, you have to jump through a few hoops:

db2 create table address (name varchar(25), name_up generated always as 
(ucase(name)))
db2 create index i_up on address (name_up)
db2 select name from address where ucase(name) like 'ADAM%' 

That will use an index but all queries in Daisy would have to be adjusted.

But there is something I don't understand: When I search for "word" in the 
Wiki and limit the search to "name" fields, the document "Word 00001" is 
found. What's different when I run the query through the wiki and when I 
run a query directly?

> 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

-- 
Aaron Digulla