Make search case insensitive

[GoogleCodeExporter]

When searching for a password the search should be done case insensitive so you 
don't have to know the capitalization.

We are using wps 1.1 on OpenSUSE 11.4 on top of VMWare ESXi.

Original issue reported on code.google.com by ee...@bolt.nl on 23 Dec 2011 at 9:13

[GoogleCodeExporter]

It should already be case insensitive, which database type are you using?

Original comment by joshdrum...@gmail.com on 24 Dec 2011 at 12:37

[GoogleCodeExporter]

PostgreSQL 9.0.3

I have enabled query logging for postgres and can see the search is done using 
like. Below an example: 

2011-12-27 08:31:51 CET wps wps LOG:  duration: 3.633 ms  bind <unnamed>: 
select this_.id as id3_6_, this_.active as active3_6_, this_.date_created as 
date3_3_6_, this_.date_last_update as date4_3_6_, this_.max_histor
y as max5_3_6_, this_.name as name3_6_, this_.notes as notes3_6_, this_.target 
as target3_6_, this_.user_created_id as user10_3_6_, this_.user_last_update_id 
as user11_3_6_, this_.username as username3_6_, pm1_.id as
id8_0_, pm1_.access_level as access2_8_0_, pm1_.password_id as password3_8_0_, 
pm1_.subject_id as subject4_8_0_, password4_.id as id3_1_, password4_.active as 
active3_1_, password4_.date_created as date3_3_1_, passwor
d4_.date_last_update as date4_3_1_, password4_.max_history as max5_3_1_, 
password4_.name as name3_1_, password4_.notes as notes3_1_, password4_.target 
as target3_1_, password4_.user_created_id as user10_3_1_, password
4_.user_last_update_id as user11_3_1_, password4_.username as username3_1_, 
user5_.id as id6_2_, user5_1_.type as type6_2_, user5_.active as active12_2_, 
user5_.date_created as date2_12_2_, user5_.email as email12_2_,
 user5_.fullname as fullname12_2_, user5_.last_login as last5_12_2_, user5_.username as username12_2_, user6_.id as id6_3_, user6_1_.type as type6_3_, user6_.active as active12_3_, user6_.date_created as date2_12_3_,
user6_.email as email12_3_, user6_.fullname as fullname12_3_, user6_.last_login 
as last5_12_3_, user6_.username as username12_3_, subject7_.id as id6_4_, 
subject7_.type as type6_4_, subject7_1_.name as name7_4_, subje
ct7_2_.active as active12_4_, subject7_2_.date_created as date2_12_4_, 
subject7_2_.email as email12_4_, subject7_2_.fullname as fullname12_4_, 
subject7_2_.last_login as last5_12_4_, subject7_2_.username as username12_
4_, case when subject7_1_.id is not null then 1 when subject7_2_.id is not null 
then 2 when subject7_.id is not null then 0 end as clazz_4_, tags8_.password_id 
as password1_3_8_, tag9_.id as tag2_8_, tag9_.id as id11_
5_, tag9_.name as name11_5_ from passwords this_ inner join permissions pm1_ on 
this_.id=pm1_.password_id left outer join passwords password4_ on 
pm1_.password_id=password4_.id left outer join users user5_ on password
4_.user_created_id=user5_.id left outer join subjects user5_1_ on 
user5_.id=user5_1_.id left outer join users user6_ on 
password4_.user_last_update_id=user6_.id left outer join subjects user6_1_ on 
user6_.id=user6_1_.
id left outer join subjects subject7_ on pm1_.subject_id=subject7_.id left 
outer join groups subject7_1_ on subject7_.id=subject7_1_.id left outer join 
users subject7_2_ on subject7_.id=subject7_2_.id left outer join
password_tags tags8_ on this_.id=tags8_.password_id left outer join tags tag9_ 
on tags8_.tag_id=tag9_.id where ((this_.name like $1 or this_.username like $2) 
or this_.notes like $3) and this_.active=$4 and pm1_.acces
s_level in ($5, $6, $7) and (pm1_.subject_id=$8 or pm1_.subject_id in (select 
this_.id as y0_ from groups this_ inner join subjects this_1_ on 
this_.id=this_1_.id inner join user_groups users3_ on this_.id=users3_.gro
up_id inner join users u1_ on users3_.user_id=u1_.id left outer join subjects 
u1_1_ on u1_.id=u1_1_.id where u1_.id=$9)) order by this_.name asc

Original comment by ee...@bolt.nl on 27 Dec 2011 at 7:55

[GoogleCodeExporter]

Original comment by joshdrum...@gmail.com on 8 Jan 2012 at 3:39

• Changed state: Accepted

[GoogleCodeExporter]

Original comment by joshdrum...@gmail.com on 22 May 2012 at 6:30

• Added labels: Milestone-Release1.2

[GoogleCodeExporter]

Yes, password search dao should use ilike to support postgresql, oracle and 
hsql.  However adding that to the name/username/notes columns breaks mssql 
because it can't handle an "upper" on a text column which the notes column is 
(https://hibernate.onjira.com/browse/HHH-4539).  For now only adding to 
name/username columns so mssql doesn't break, that means search of the notes 
field will still be case-sensitive for some database types.  May change notes 
field to be varchar(max) in the future rather than text type, however that's a 
significant datamodel change for a major version upgrade.

Original comment by joshdrum...@gmail.com on 22 May 2012 at 6:59

[GoogleCodeExporter]

Decided to kludge the solution, will do ilike on notes column except if using 
MSSQL dialect.  Hopefully a cleaner solution in the future, but for now this 
will get the job done...

Original comment by joshdrum...@gmail.com on 23 May 2012 at 1:08

• Changed state: Fixed

[GoogleCodeExporter]

Original comment by joshdrum...@gmail.com on 23 May 2012 at 4:02

• Added labels: Usability