PostgreSQLFullTextTrgm - Revision history

Phanousk: /* An experimental PostgreSQL full text trigram based setup */

2018-07-13T13:05:27Z

An experimental PostgreSQL full text trigram based setup

← Older revision		Revision as of 09:05, 13 July 2018
Line 13:		Line 13:

	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.		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.):		* Patch for SearchBuilder (I have placed the modified version into <rt-prefix>/local/lib/DBIx/SearchBuilder.pm.):
			<source lang="Perl">
	--- SearchBuilder.pm.orig 2011-03-24 16:26:16.000000000 +0100		--- SearchBuilder.pm.orig 2011-03-24 16:26:16.000000000 +0100
	+++ SearchBuilder.pm 2011-03-30 17:11:18.000000000 +0200		+++ SearchBuilder.pm 2011-03-30 17:11:18.000000000 +0200
Line 67:		Line 66:

	return ( $args{'ALIAS'} );		return ( $args{'ALIAS'} );
			</source>

	* To prepare already converted [[PostgreSQL]] database run:		* To prepare already converted [[PostgreSQL]] database run:

	rt-mysql2pg -v --dst-dsn dbi:Pg:dbname=rt3 --fulltext --vacuum		<tt>rt-mysql2pg -v --dst-dsn dbi:Pg:dbname=rt3 --fulltext --vacuum</tt>

	-- zito		-- zito

Admin: 5 revisions imported

2016-04-06T20:20:29Z

5 revisions imported

← Older revision		Revision as of 16:20, 6 April 2016
(No difference)

193.86.153.231 at 21:43, 9 April 2014

2014-04-09T21:43:26Z

New page

{{Outdated}}

= 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 @@

}

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

# Juju because this should come _AFTER_ the EA
my @prefix;
@@ -945,10 +967,10 @@
}

if ( lc( $args{'ENTRYAGGREGATOR'} || "" ) eq 'none' || !@$restriction ) {
- @$restriction = (@prefix, $clause);
+ @$restriction = (@prefix, @clause);
}
else {
- push @$restriction, $args{'ENTRYAGGREGATOR'}, @prefix, $clause;
+ push @$restriction, $args{'ENTRYAGGREGATOR'}, @prefix, @clause;
}

return ( $args{'ALIAS'} );

* To prepare already converted [[PostgreSQL]] database run:

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

-- zito

95.173.94.255: filter by trigrams (index) & like (to remove false matches)

2011-04-06T08:59:53Z

filter by trigrams (index) & like (to remove false matches)

New page

= An experimental PostgreSQL full text trigram based setup =

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 @@

}

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

# Juju because this should come _AFTER_ the EA
my @prefix;
@@ -945,10 +967,10 @@
}

if ( lc( $args{'ENTRYAGGREGATOR'} || "" ) eq 'none' || !@$restriction ) {
- @$restriction = (@prefix, $clause);
+ @$restriction = (@prefix, @clause);
}
else {
- push @$restriction, $args{'ENTRYAGGREGATOR'}, @prefix, $clause;
+ push @$restriction, $args{'ENTRYAGGREGATOR'}, @prefix, @clause;
}

return ( $args{'ALIAS'} );

* To prepare already converted [[PostgreSQL]] database run:

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

-- zito