Search engine case sensitive

fontes

Joined: 2004-06-23
Posts: 2
Posted: Wed, 2004-06-23 20:52

New user of Gallery here. Love it. Thanks to avid developers.

I'm trying G2. I've noticed that the search engine is case sensitive. Is this true at present? Can I place a wish-list item to have it NOT case sensitive.

Thanks.

 
zombor

Joined: 2003-08-06
Posts: 57
Posted: Wed, 2004-06-23 21:32

Or an option to make it case sensitive or not would be better. some people like it, some don't

 
valiant

Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2004-06-24 00:01

different databases treat case sensivity differently.
oracle is case sensitive, mysql per default isn't, etc.

there is more than one solution to query case in-/sensitively (make the attribute binary in mysql, ...). the easiest would be to use the sql function "UPPER()", on both, attribute to be searched and the string. and you could easily activate/deactivate the case sensivity.

 
bharat
bharat's picture

Joined: 2002-05-21
Posts: 7993
Posted: Thu, 2004-06-24 17:24

Please file this as an RFE in the v2 group.

Right now we are purely using the database search so if you're using MySQL you get case insensitive search. If you're using Postgres, you get case insensitive search. I can look into using UPPER() on both pattern and target to see if that works with some kind of reasonable performance...

 
fontes

Joined: 2004-06-23
Posts: 2
Posted: Fri, 2004-06-25 23:54

Thanks for replies.

I am using a postgres database to back G2 on redhat AS 3.0 (version 7.3.4, I think) and I still get case sensitive.

In a different php application I'm using an sql query like this:

" WHERE (field name) ILIKE '%" . $searchtext . "%'"

The "ILIKE" is a case insensitive search and the % will match word fragments.

Any thoughts?

By the way, I wonder if anyone else wants G2 to store PDF documents and would also like full text search of the PDF file?

 
valiant

Joined: 2003-01-04
Posts: 32509
Posted: Sat, 2004-06-26 08:09

fontes, of course you still get case sensitive searches on postgres, it stores like oracle case sensitive.
@keyword "iLIKE"

Quote:
The key word ILIKE can be used instead of LIKE to make the match case insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

, source

suggestion: in DBS where the default behaviour is case insensitive as in mysql, make it case sensitive (in mysql with attribute BINARY). the default search option should be case insensitive. use UPPER() for case insensitive searches, it's part of the SQL92 standard.
but i don't know what performance hit you'd have to expect from this BINARY -> UPPER() conversion.

@PDF fulltext search. this would require a fully optimised search engine à la phpbb featuring an index over all words etc. If you'd search without such a cache, searching all pdfs in realtime for each search request, it would not only take more time, it would use quite a lot of CPU.
It's feasible, but you'll have to find someone willing to code such a module.

 
bharat
bharat's picture

Joined: 2002-05-21
Posts: 7993
Posted: Tue, 2004-06-29 03:31

The search module is not very sophisticated yet. We should be able to do all of these things, but it's not a high priority right now. We've got a bunch of other more critical things that we need to hash out before we get to alpha. Later on in the release cycle we'll probably get around to adding more sophistication to the search module.

If, in the meantime, somebody wants to work on it .. please feel free. Come by the #gallery channel on irc and talk to me about it and I'll help you with the code.

 
yknott

Joined: 2005-07-06
Posts: 5
Posted: Fri, 2005-07-08 20:56

well in the meantime, how about an indication on the search results page that mentions that the search may have been case sensitive

 
robe

Joined: 2005-10-20
Posts: 2
Posted: Thu, 2005-10-20 16:23

I'm using a 10/19/05 snapshot of G2 against a postgres database. I think its really great. The only problem I have is that since I'm using postgres, the search is case-sensitive.

I was going to try and fix this myself if it hasn't already been done and hopefully give it back to the community, but just wanted to make sure I'm not missing a configuration somewhere. I'm very new to G2 so not sure how long it will take me to do.

 
valiant

Joined: 2003-01-04
Posts: 32509
Posted: Thu, 2005-10-20 16:40

i guess no request has been filed for this feature and it has not been implemented.
so feel free to submit a patch :)

 
TiMu

Joined: 2005-04-18
Posts: 4
Posted: Tue, 2006-01-17 22:30

I also need that case-sensitive patch, I'm using G2 2.0.2

TiMu

 
pjmorse

Joined: 2007-01-11
Posts: 16
Posted: Thu, 2007-01-11 15:04

I have Gallery 2.1.2 and PostgreSQL 8.1.5, and I am still seeing this case-sensitive searching.

I'm trying to suss out where to patch the code (as "robe" suggested above) but I can't find out where the search query is being sent to the database. The only place where the "LIKE" operator appears at all seems to be modules/core/classes/GalleryStorage/PostgreSqlStorage.class, but I subbed in "ILIKE" on line 214 (not 213) of that file, and it doesn't appear to change anything.

Can anyone point me to a spot where this change can be made? Thanks.

 
robe

Joined: 2005-10-20
Posts: 2
Posted: Thu, 2007-01-11 16:23

Hi pjmorse,

I was meaning to provide a patch to the group, but I hadn't figured the best place to do that. My patch at the moment is more of a hack than setup ready.

I'm not running the latest, but to resolve the case sensitivity issue - I had to change
/modules/core/classes/GalleryCoreSearch.class -> search

. I opted to do Upper instead of ILIKE assuming that Upper was more generic than ILIKE for other case sensitive databases.

I changed something like
if (isset($options[$key]) && false) {//eventually this will be used to allow case sensitive - the false is to prevent this from ever running for now
$whereList[] = "$column LIKE ?";
$whereData[] = '%' . $criteria . '%';
$selectMap[$column] = $columnNumber++;;
$selectList[] = $column;
}
elseif (isset($options[$key])) {//added to allow option for case insensitive
$whereList[] = "UPPER($column) LIKE ?";
$whereData[] = '%' . strtoupper($criteria) . '%';
$selectMap[$column] = $columnNumber++;;
$selectList[] = $column;
}

I hope that helps,

Regina

 
pjmorse

Joined: 2007-01-11
Posts: 16
Posted: Mon, 2007-01-15 01:54

Beautiful, Regina, thanks. This puts me on the right track for now; a hack to make it work is all we need at this point.