Add database index for user full text search.

10 files changed, 29 insertions, 20 deletions

diff --git a/bin/update-schema b/bin/update-schema
index 90876cf76..1cfb59f52 100755
--- a/bin/update-schema
+++ b/bin/update-schema
@@ -215,6 +215,7 @@ else {
 # (assuming schema change files are never half-applied, which should be the case)
 sub get_db_version {
     return 'EMPTY' if ! table_exists('problem');
+    return '0074' if index_exists('users_fulltext_idx');
     return '0073' if index_exists('problem_fulltext_idx');
     return '0072' if constraint_contains('contacts_state_check', 'staff');
     return '0071' if table_exists('manifest_theme');
diff --git a/db/downgrade_0074---0073.sql b/db/downgrade_0074---0073.sql
new file mode 100644
index 000000000..f900cc1d7
--- /dev/null
+++ b/db/downgrade_0074---0073.sql
@@ -0,0 +1 @@
+DROP INDEX users_fulltext_idx;
diff --git a/db/schema.sql b/db/schema.sql
index f198a287b..2c0cedb8d 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -41,6 +41,12 @@ create table users (
 );
 CREATE UNIQUE INDEX users_email_verified_unique ON users (email) WHERE email_verified;
 CREATE UNIQUE INDEX users_phone_verified_unique ON users (phone) WHERE phone_verified;
+create index users_fulltext_idx on users USING GIN(
+    to_tsvector(
+        'english',
+        translate(id || ' ' || coalesce(name,'') || ' ' || coalesce(email,'') || ' ' || coalesce(phone,''), '@.', '  ')
+    )
+);
 
 -- Record details of reporting bodies, including open311 configuration details
 create table body (
diff --git a/db/schema_0074-add-users-full-text-search-index.sql b/db/schema_0074-add-users-full-text-search-index.sql
new file mode 100644
index 000000000..dc743bd47
--- /dev/null
+++ b/db/schema_0074-add-users-full-text-search-index.sql
@@ -0,0 +1,6 @@
+CREATE INDEX CONCURRENTLY users_fulltext_idx on users USING GIN(
+    to_tsvector(
+        'DB_FULL_TEXT_SEARCH_CONFIG',
+        translate(id || ' ' || coalesce(name,'') || ' ' || coalesce(email,'') || ' ' || coalesce(phone,''), '@.', '  ')
+    )
+);
diff --git a/perllib/FixMyStreet/App/Controller/Admin/Users.pm b/perllib/FixMyStreet/App/Controller/Admin/Users.pm
index 9fd4cd32d..a05e737ab 100644
--- a/perllib/FixMyStreet/App/Controller/Admin/Users.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin/Users.pm
@@ -49,26 +49,11 @@ sub index :Path : Args(0) {
     my $role = $c->get_param('role');
     my $users = $c->cobrand->users;
     if ($search || $role) {
-        my $isearch;
         if ($search) {
             $search = $self->trim($search);
             $search =~ s/^<(.*)>$/$1/; # In case email wrapped in <...>
             $c->stash->{searched} = $search;
-
-            $isearch = '%' . $search . '%';
-            my $search_n = 0;
-            $search_n = int($search) if $search =~ /^\d+$/;
-
-            $users = $users->search(
-                {
-                    -or => [
-                        email => { ilike => $isearch },
-                        phone => { ilike => $isearch },
-                        'me.name' => { ilike => $isearch },
-                        from_body => $search_n,
-                    ]
-                }
-            );
+            $users = $users->search_text($search);
         }
         if ($role) {
             $c->stash->{role_selected} = $role;
diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
index 0a4403e52..ea38b3e14 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Comment.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
@@ -9,6 +9,7 @@ with 'FixMyStreet::Roles::FullTextSearch';
 __PACKAGE__->load_components('Helper::ResultSet::Me');
 sub text_search_columns { qw(id problem_id name text) }
 sub text_search_nulls { qw(name) }
+sub text_search_translate { '/.' }
 
 sub to_body {
     my ($rs, $bodies) = @_;
diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
index 3ac9187ee..a7c365c1e 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
@@ -13,6 +13,7 @@ with 'FixMyStreet::Roles::FullTextSearch';
 __PACKAGE__->load_components('Helper::ResultSet::Me');
 sub text_search_columns { qw(id external_id bodies_str name title detail) }
 sub text_search_nulls { qw(external_id bodies_str) }
+sub text_search_translate { '/.' }
 
 my $site_key;
 
diff --git a/perllib/FixMyStreet/DB/ResultSet/User.pm b/perllib/FixMyStreet/DB/ResultSet/User.pm
index 9a8a50559..baae024bf 100644
--- a/perllib/FixMyStreet/DB/ResultSet/User.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/User.pm
@@ -5,6 +5,11 @@ use strict;
 use warnings;
 
 use Moo;
+with 'FixMyStreet::Roles::FullTextSearch';
+__PACKAGE__->load_components('Helper::ResultSet::Me');
+sub text_search_columns { qw(id name email phone) }
+sub text_search_nulls { qw(name email phone) }
+sub text_search_translate { '@.' }
 
 # The database has a partial unique index on email (when email_verified is
 # true), and phone (when phone_verified is true). In the code, we can only
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 ]);
 }
diff --git a/t/app/controller/admin/users.t b/t/app/controller/admin/users.t
index bc8d28e2d..a3bd4a784 100644
--- a/t/app/controller/admin/users.t
+++ b/t/app/controller/admin/users.t
@@ -84,8 +84,6 @@ subtest 'user search' => sub {
         permissions => ['moderate', 'user_edit'],
     });
     $user->add_to_roles($role);
-    $mech->get_ok('/admin/users?search=' . $haringey->id );
-    $mech->content_contains('test@example.com');
     $mech->get_ok('/admin/users?role=' . $role->id);
     $mech->content_contains('selected>Role A');
     $mech->content_contains('test@example.com');