Case insensitivity

[ShadowKatmandu]

We are using PG SQL for our backend, which is case-sensitive on queries. There is logic built into Mantis to do case insensitive queries for this; however, it requires a parameter be passed to the sql_like function to force it. There's a separate function, sql_ilike, to do this, but there is no circumstance where this is called. Is there a way to change this behavior?

[TerranceM]

There are a few ways to compare strings.

complete strings

partial strings/from the beginning

partial strings/somewhere


for each of these 3 types it can be exact match or case insensitive

so 6 different methods:

1) complete string/exact

select * from table where "column" = 'value'

2) complete string/case insentitive

select * from table where "column" = 'value' case insensitive

3) partial string from the beginning / exact match

select * from table where "column" like 'value%' ;
select * from table where position( 'value' in "column" ) = 1;
select * from table where substring( "column" from 1 for 5 ) = 'value';


4) partial string from the beginning / case insentitive

select * from table where "column" like 'value%' ignore case ;
select * from table where position( lower('value') in lower("column") ) = 1;
select * from table where substring( lower("column") from 1 for 5 ) =
lower('value');


5) partial string somewhere / exact match

select * from table where "column" like '%value%' ;
select * from table where position( 'value' in "column" ) > 0;

6) partial string somewhere / case insentitive

select * from table where "column" like '%value%' ignore case ;
select * from table where position( lower('value') in lower("column") ) > 0;


my experience is that functions (lower, upper etc) have a serious
overhead and cause serious performance degrade.

[ShadowKatmandu]

I understand the many ways to do this in a SQL query. That was not my question at all.

To be specific:

On the view issues page, there is a search box with an Apply Filter button next to it. This is a CASE SENSITIVE search. I gave technical details as to why this is so. There is functionality in Mantis to deal with it, but it doesn't seem to be used.

How do I get Mantis to do CASE INSENSITIVE searches with PG SQL without changing core code?

[progsdev]

Workaround:

tested: Mantis 2.25, Postgres 9.x
To force case insensitive for PostgresSQL
edit file

[mantiwwwsdir]/core/classes/DbQuery.class.php

and change line with function default value of last parameter: false

"public function sql_like( $p_alias, $p_pattern, $p_escape = null, $p_force_ci = false ) {"

to: true
"public function sql_like( $p_alias, $p_pattern, $p_escape = null, $p_force_ci = true ) {"

From now all Mantis GUI queries would using ILIKE.

Regards
Marcin

[cas]

This actually should be a setting in config_defaults_inc.php.
As mentioned above there is a function available sql_ilike but this one is not used.
So best would be to remove the function sql_ilike and only use sql_like where the last parameter would be a setting like $g_force_ci
This way it becomes flexable by installation and nobody has to touch the core code to achieve the requested behaviour.

[My Zodiac is Pisces]

This actually should be a setting in config_defaults_inc.php.
As mentioned above there is a function available sql_ilike but this one is not used.
So best would be to remove the function sql_ilike and only use sql_like where the last parameter would be a setting like $g_force_ci
This way it becomes flexable by installation and nobody has to touch the core code to achieve the requested behaviour.

Agreed with you.
It would be better then this.