From bd42d6a1fcb6c8e6d89413e0ee22617625d95bad Mon Sep 17 00:00:00 2001
From: Matthew Somerville <matthew@mysociety.org>
Date: Wed, 29 Jul 2020 09:03:56 +0100
Subject: Add database index for full text search.

---
 perllib/FixMyStreet/Roles/FullTextSearch.pm | 22 ++++++++++++++++++++++
 1 file changed, 22 insertions(+)
 create mode 100644 perllib/FixMyStreet/Roles/FullTextSearch.pm

(limited to 'perllib/FixMyStreet/Roles/FullTextSearch.pm')

diff --git a/perllib/FixMyStreet/Roles/FullTextSearch.pm b/perllib/FixMyStreet/Roles/FullTextSearch.pm
new file mode 100644
index 000000000..78dbd5c18
--- /dev/null
+++ b/perllib/FixMyStreet/Roles/FullTextSearch.pm
@@ -0,0 +1,22 @@
+package FixMyStreet::Roles::FullTextSearch;
+
+use Moo::Role;
+use FixMyStreet;
+
+requires 'text_search_columns';
+requires 'text_search_nulls';
+
+sub search_text {
+    my ($rs, $query) = @_;
+    my %nulls = map { $_ => 1 } $rs->text_search_nulls;
+    my @cols = map {
+        my $col = $rs->me($_);
+        $nulls{$_} ? "coalesce($col, '')" : $col;
+    } $rs->text_search_columns;
+    my $vector = "translate(" . join(" || ' ' || ", @cols) . ", '/.', '  ')";
+    my $config = FixMyStreet->config('DB_FULL_TEXT_SEARCH_CONFIG') || 'english';
+    $rs->search(\[ "to_tsvector('$config', $vector) @@ plainto_tsquery('$config', ?)", $query ]);
+}
+
+1;
+
-- 
cgit v1.2.3


From 21efead606530cb754bb7b54586dbb8c1a1502ca Mon Sep 17 00:00:00 2001
From: M Somerville <matthew-github@dracos.co.uk>
Date: Wed, 5 Aug 2020 15:55:29 +0100
Subject: Perform translate on full text search query too.

---
 perllib/FixMyStreet/Roles/FullTextSearch.pm | 6 ++++--
 1 file changed, 4 insertions(+), 2 deletions(-)

(limited to 'perllib/FixMyStreet/Roles/FullTextSearch.pm')

diff --git a/perllib/FixMyStreet/Roles/FullTextSearch.pm b/perllib/FixMyStreet/Roles/FullTextSearch.pm
index 78dbd5c18..dc7730798 100644
--- a/perllib/FixMyStreet/Roles/FullTextSearch.pm
+++ b/perllib/FixMyStreet/Roles/FullTextSearch.pm
@@ -13,9 +13,11 @@ sub search_text {
         my $col = $rs->me($_);
         $nulls{$_} ? "coalesce($col, '')" : $col;
     } $rs->text_search_columns;
-    my $vector = "translate(" . join(" || ' ' || ", @cols) . ", '/.', '  ')";
+    my $vector = join(" || ' ' || ", @cols);
+    $vector = "translate($vector, '/.', '  ')";
+    my $bind = "translate(?, '/.', '  ')";
     my $config = FixMyStreet->config('DB_FULL_TEXT_SEARCH_CONFIG') || 'english';
-    $rs->search(\[ "to_tsvector('$config', $vector) @@ plainto_tsquery('$config', ?)", $query ]);
+    $rs->search(\[ "to_tsvector('$config', $vector) @@ plainto_tsquery('$config', $bind)", $query ]);
 }
 
 1;
-- 
cgit v1.2.3


From 7af4f2cc87cd6ff55501bb2856193a03fe72158c Mon Sep 17 00:00:00 2001
From: M Somerville <matthew-github@dracos.co.uk>
Date: Wed, 5 Aug 2020 15:56:10 +0100
Subject: Add database index for user full text search.

---
 perllib/FixMyStreet/Roles/FullTextSearch.pm | 9 +++++++--
 1 file changed, 7 insertions(+), 2 deletions(-)

(limited to 'perllib/FixMyStreet/Roles/FullTextSearch.pm')

diff --git a/perllib/FixMyStreet/Roles/FullTextSearch.pm b/perllib/FixMyStreet/Roles/FullTextSearch.pm
index dc7730798..871b1d185 100644
--- a/perllib/FixMyStreet/Roles/FullTextSearch.pm
+++ b/perllib/FixMyStreet/Roles/FullTextSearch.pm
@@ -5,6 +5,7 @@ use FixMyStreet;
 
 requires 'text_search_columns';
 requires 'text_search_nulls';
+requires 'text_search_translate';
 
 sub search_text {
     my ($rs, $query) = @_;
@@ -14,8 +15,12 @@ sub search_text {
         $nulls{$_} ? "coalesce($col, '')" : $col;
     } $rs->text_search_columns;
     my $vector = join(" || ' ' || ", @cols);
-    $vector = "translate($vector, '/.', '  ')";
-    my $bind = "translate(?, '/.', '  ')";
+    my $bind = '?';
+    if (my $trans = $rs->text_search_translate) {
+        my $replace = ' ' x length $trans;
+        $vector = "translate($vector, '$trans', '$replace')";
+        $bind = "translate(?, '$trans', '$replace')";
+    }
     my $config = FixMyStreet->config('DB_FULL_TEXT_SEARCH_CONFIG') || 'english';
     $rs->search(\[ "to_tsvector('$config', $vector) @@ plainto_tsquery('$config', $bind)", $query ]);
 }
-- 
cgit v1.2.3