PostgreSQLFullTextTrgm

From Request Tracker Wiki
Jump to navigation Jump to search

Outdated

This page contains out of date and possibly misleading information or instructions such as installation methods or configuration examples that no longer apply. Please consider this warning when reading the page below.


RT 4 has built in native full text support for PostgreSQL

An experimental PostgreSQL full text trigram based setup

please look at https://github.com/zito/rt-pgsql-fttrgm ... I will cleanup this page when i spare some free time. Thanks

This article describes a setup of PostgreSQL full text in a non native mode using trigrams based matching. PostgreSQL full text can't be used for substring searching in standard way. Version 8.4.x supports prefix matching, but it is still not sufficient for me. Inspired by http://kaiv.wordpress.com/2007/12/11/postgresql-substring-search/ and using know-how from PostgreSQLFullText I'm trying a fusion now :).

Read the page PostgreSQLFullText first! Changes are described bellow. You can use my script rt-mysql2pg to prepare database for full text without a tedious work.

  • Patch for SearchBuilder (I have placed the modified version into <rt-prefix>/local/lib/DBIx/SearchBuilder.pm.):
--- SearchBuilder.pm.orig  2011-03-24 16:26:16.000000000 +0100
 +++ SearchBuilder.pm   2011-03-30 17:11:18.000000000 +0200
 @@ -932,11 +932,33 @@

<code><pre>  }
</pre></code>

<ul>
<li>my $clause = {</li>
<li>my @clause = ( {
      field => $QualifiedField,
      op => $args{'OPERATOR'},
      value => $args{'VALUE'},</li>
<li>};</li>
<li>} );</li>
<li></li>
<li># Use FULLTEXT for large Attachments.Content and</li>
<li># ObjectCustomFieldValues.Largecontent in PostgreSQL.</li>
<li>if ( $QualifiedField =~ m/^(?: Attachments_\d+&#46;Content</li>
<li>| ObjectCustomFieldValues_\d+&#46;Largecontent )$/xi) {</li>
<li>if ( $args{'OPERATOR'} =~ m/^(?:NOT )?I?LIKE$/</li>
<li>&amp;&amp; $args{'VALUE'} =~ m/^'%.*%'$/ ) {</li>
<li>my $not = lc(substr($args{'OPERATOR'}, 0, 3)) eq 'not';</li>
<li>my $value = $args{'VALUE'};</li>
<li>$value  =~ s/^'%(.*)%'$/'$1'/;</li>
<li>$value  = $not ? "(!! text_to_trgm_tsquery($value))" : "text_to_trgm_tsquery($value)";</li>
<li>my $field = $QualifiedField;</li>
<li>$field =~ s/&#46;(?:Content|Largecontent)$/.trigrams/;</li>
<li>@clause = ( '(',</li>
<li>{</li>
<li>field => $field,</li>
<li>op => '@@',</li>
<li>value => $value,</li>
<li>},</li>
<li>'AND', @clause, ')' );</li>
<li>}</li>
<li>}

# Juju because this should come <em>AFTER</em> the EA
  my @prefix;
@@ -945,10 +967,10 @@
  }

if ( lc( $args{'ENTRYAGGREGATOR'} || "" ) eq 'none' || !@$restriction ) {</li>
<li>@$restriction = (@prefix, $clause);</li>
<li>@$restriction = (@prefix, @clause);
  }
  else {</li>
<li>push @$restriction, $args{'ENTRYAGGREGATOR'}, @prefix, $clause;</li>
<li><code><pre>  push @$restriction, $args{'ENTRYAGGREGATOR'}, @prefix, @clause;
</pre></code>

}

return ( $args{'ALIAS'} );
  • To prepare already converted PostgreSQL database run:

rt-mysql2pg -v --dst-dsn dbi:Pg:dbname=rt3 --fulltext --vacuum

-- zito