[Rt-devel] SearchBuild and case insensitive matching on postgres

[Jesse Vincent]

> But, it still also uses lower on integers and id:s sometimes - this is bad. 
> I've poked around in the code, and it seems to me there is logic in RT 
> (lib/RT/Record.pm:207) that tries to prevent the usage of lower where the 
> name of the column is 'id', but this code does not always help. 
> SearchBuilder.pm:801 will run MakeClauseCaseInsensitive even if I try to 
> match on '^(.*\.)?id$' in Record.pm. I see no easy way to get around this, 
> without breaking SearchBuilder for other projects (it is supposed to be 
> somewhat independant of RT, I assume?). This said, the following patch 
> makes RT quite usable for me, in fact it's suddenly pretty fast. It should 
> do the right thing for RT, but is an ugly hack and most probably breaks 
> SearchBuilder for use with most other projects:

So, DBIx::SearchBuilder::_ClassAccessible has type information
available. You should be able to just ask it if a given row is an int
type.
> 
> --- SearchBuilder/Handle/Pg.pm.orig     Fri May 14 23:32:14 2004
> +++ SearchBuilder/Handle/Pg.pm  Fri May 14 23:34:47 2004
> @@ -279,8 +279,12 @@
>        # on the value. they only expect field, operator, value.
>        #
>        else {
> -               return ( "$field", $operator, lc($value));
> -
> +           if ($field =~ /id$/i) {
> +               return  ( "$field", $operator, lc($value));
> +           }
> +           else {
> +               return ( "LOWER($field)", $operator, lc($value));
> +           }
>        }
>     }
>     else {
> 
> 
> The correct way to do this would be not to run MakeCaseInsensitive for 
> id:s, but since It is done in SearchBuilder, I can't come up with an easy 
> way to fix this.
> 
> Hopefully, someone can fix this in a proper way?
> 
> Best regards,
> Palle

--