[rt-users] case insensitive searching in Content?

[Václav Ovsík]

Hi Ken,

On Tue, Jun 08, 2010 at 07:48:15AM -0500, Kenneth Marshall wrote:
> ILIKE is fine on PostgreSQL as long as the search is anchored on
> the left and there is an index. Otherwise it will do a sequential
> scan of the attachments table to find matching records. The wiki
> entries for full-text indexing with PostgreSQL and Oracle can avoid
> the sequential scan through the use of the database's full-text

thanks for your work on PostgreSQLFullText wiki page! Good starting
point. I'm playing with the fulltext on one instance already. I must
discuss the possibility to use the fulltext on Pg with my leader. The
results are different from those get from ILIKE. I tried to find 'cisco'
for example :). Normal search returned 25 tickets, but fulltext only 15.
Fulltext parser did tokens from URL and www.cisco.com was a lexem as is
(not broken further). I can't say this is fine or not, it is difference.
Users must know this - how to write the query correctly.

> index support. I do agree that patching the actual DBIx::SearchBuilder
> is not preferable. Unfortunately, since that is the API that is used
> to provide a database abstraction layer and it does not yet include
> the ability to specify full-text indexing details, you have to patch
> it. If it is any consolation, the patch is much, much simpler (trivial)
> for the PostgreSQL support versus the version that is needed for
> OracleText. :)

I completely understand. I put the package libdbix-searchbuilder-perl
(Debian) on hold in aptitude, so sysadmin should remember, that this
package needs a special handling. I did a copy of SearchBuilder.pm file
into <rt-prefix>/local/lib/DBIx/ and did a modification. This perl path
is searched first, so a modification is beside other RT code...

I have prepared a script for converting data from Mysql to Pg and for
adding/removing fulltext support based on your wiki page. Maybe it can
be added to wiki for someone else to play with fulltext and Pg. I must
to test it a bit and switch some fixed parts into conditional blocks
controlled by command-line options.

Regards
-- 
Zito