Add database index for full text search.

12 files changed, 164 insertions, 33 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index df449a480..e7f42fdc0 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -33,6 +33,7 @@
         - Interface for enabling anonymous reports for certain categories. #2989
         - Better sort admin user table.
         - Centralise update creation to include fields.
+        - Add full text index to speed up admin search.
     - Development improvements:
         - `#geolocate_link` is now easier to re-style. #3006
         - Links inside `#front-main` can be customised using `$primary_link_*` Sass variables. #3007
diff --git a/bin/update-schema b/bin/update-schema
index 44e299b91..90876cf76 100755
--- a/bin/update-schema
+++ b/bin/update-schema
@@ -78,6 +78,8 @@ print "= Dry run =\n" unless $commit;
 
 my $upgrade = !$downgrade;
 
+my $db_fts_config = FixMyStreet->config('DB_FULL_TEXT_SEARCH_CONFIG') || 'english';
+
 sub get_statements {
     my $path = shift;
     open(my $FP, '<', $path) or die $!;
@@ -91,6 +93,7 @@ sub get_statements {
         $in_function = 1 if /create (or replace )?function/i;
         $in_function = 0 if /language (sql|'plpgsql')/i;
         if ($s =~ /;/ && !$in_function) {
+            $s =~ s/DB_FULL_TEXT_SEARCH_CONFIG/$db_fts_config/g;
             push @statements, $s;
             $s = '';
         }
@@ -212,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 '0073' if index_exists('problem_fulltext_idx');
     return '0072' if constraint_contains('contacts_state_check', 'staff');
     return '0071' if table_exists('manifest_theme');
     return '0070' if column_like('alert_type', "ref='new_problems'", 'head_title', '{{SITE_NAME}}');
diff --git a/conf/general.yml-example b/conf/general.yml-example
index e42a1433e..ece50d0f3 100644
--- a/conf/general.yml-example
+++ b/conf/general.yml-example
@@ -66,6 +66,10 @@ EXAMPLE_PLACES: [ 'High Street', 'Main Street' ]
 LANGUAGES:
   - 'en-gb,English,en_GB'
 
+# A postgresql text search configuration value - it should match the
+# configuration used by the problem_fulltext_idx index.
+DB_FULL_TEXT_SEARCH_CONFIG: 'english'
+
 # If you're running an installation that is being used in a different time zone
 # from the server, you can set the time zone here (standard time zone string)
 TIME_ZONE: ""
diff --git a/db/downgrade_0073---0072.sql b/db/downgrade_0073---0072.sql
new file mode 100644
index 000000000..1ae19b9d7
--- /dev/null
+++ b/db/downgrade_0073---0072.sql
@@ -0,0 +1,2 @@
+DROP INDEX problem_fulltext_idx;
+DROP INDEX comment_fulltext_idx;
diff --git a/db/schema.sql b/db/schema.sql
index ed21aded6..f198a287b 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -246,6 +246,12 @@ create index problem_user_id_idx on problem ( user_id );
 create index problem_external_body_idx on problem(lower(external_body));
 create index problem_radians_latitude_longitude_idx on problem(radians(latitude), radians(longitude));
 create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ','));
+create index problem_fulltext_idx on problem USING GIN(
+    to_tsvector(
+        'english',
+        translate(id || ' ' || coalesce(external_id,'') || ' ' || coalesce(bodies_str,'') || ' ' || name || ' ' || title || ' ' || detail, '/.', '  ')
+    )
+);
 
 create table questionnaire (
     id serial not null primary key,
@@ -354,6 +360,12 @@ create table comment (
 create index comment_user_id_idx on comment(user_id);
 create index comment_problem_id_idx on comment(problem_id);
 create index comment_problem_id_created_idx on comment(problem_id, created);
+create index comment_fulltext_idx on comment USING GIN(
+    to_tsvector(
+        'english',
+        translate(id || ' ' || problem_id || ' ' || coalesce(name,'') || ' ' || text, '/.', '  ')
+    )
+);
 
 -- Tokens for confirmations
 create table token (
diff --git a/db/schema_0073-add-full-text-search-index.sql b/db/schema_0073-add-full-text-search-index.sql
new file mode 100644
index 000000000..9871386bd
--- /dev/null
+++ b/db/schema_0073-add-full-text-search-index.sql
@@ -0,0 +1,13 @@
+CREATE INDEX CONCURRENTLY problem_fulltext_idx ON problem USING GIN(
+    to_tsvector(
+        'DB_FULL_TEXT_SEARCH_CONFIG',
+        translate(id || ' ' || coalesce(external_id,'') || ' ' || coalesce(bodies_str,'') || ' ' || name || ' ' || title || ' ' || detail, '/.', '  ')
+    )
+);
+
+CREATE INDEX CONCURRENTLY comment_fulltext_idx on comment USING GIN(
+    to_tsvector(
+        'DB_FULL_TEXT_SEARCH_CONFIG',
+        translate(id || ' ' || problem_id || ' ' || coalesce(name,'') || ' ' || text, '/.', '  ')
+    )
+);
diff --git a/perllib/DBIx/Class/Helper/ResultSet/Me.pm b/perllib/DBIx/Class/Helper/ResultSet/Me.pm
new file mode 100644
index 000000000..6077cffe7
--- /dev/null
+++ b/perllib/DBIx/Class/Helper/ResultSet/Me.pm
@@ -0,0 +1,78 @@
+package DBIx::Class::Helper::ResultSet::Me;
+$DBIx::Class::Helper::ResultSet::Me::VERSION = '2.036000';
+# ABSTRACT: Define predefined searches more nicely
+
+use strict;
+use warnings;
+
+use parent 'DBIx::Class::ResultSet';
+
+sub me { join('.', shift->current_source_alias, shift || q{})  }
+
+1;
+
+__END__
+
+=pod
+
+=head1 NAME
+
+DBIx::Class::Helper::ResultSet::Me - Define predefined searches more nicely
+
+=head1 SYNOPSIS
+
+ # note that this is normally a component for a ResultSet
+ package MySchema::ResultSet::Bar;
+
+ use strict;
+ use warnings;
+
+ use parent 'DBIx::Class::ResultSet';
+
+ use constant CANDY => 1;
+
+ __PACKAGE__->load_components('Helper::ResultSet::Me');
+
+ sub candy {
+    $_[0]->search({ $_[0]->me.'type' => CANDY })
+ }
+
+ sub cake {
+    $_[0]->search({ $_[0]->me('type') => CAKE })
+ }
+
+ # in code using resultset:
+ my $candy_bars = $schema->resultset('Bar')->candy;
+ my $cake_bars  = $schema->resultset('Bar')->cake;
+
+=head1 DESCRIPTION
+
+This component allows slightly nicer predefined search definition.  See
+L<DBIx::Class::Helper::ResultSet/NOTE> for a nice way to apply it to your
+entire schema.
+
+It defines a single method that is shorter and (to most) clearer than
+L<DBIx::Class::ResultSet/current_source_alias>, which is what it uses
+for the L</me> method.
+
+=head1 METHODS
+
+=head2 me
+
+Merely returns the SQL namespace for the current search with a C<.> at the end,
+allowing internal resultset methods to be defined with C<< $self->me >> instead
+of C<< $self->current_source_alias . q(.) >>.  Also, if you pass it a single
+argument it will append that to the returned string.
+
+=head1 AUTHOR
+
+Arthur Axel "fREW" Schmidt <frioux+cpan@gmail.com>
+
+=head1 COPYRIGHT AND LICENSE
+
+This software is copyright (c) 2020 by Arthur Axel "fREW" Schmidt.
+
+This is free software; you can redistribute it and/or modify it under
+the same terms as the Perl 5 programming language system itself.
+
+=cut
diff --git a/perllib/FixMyStreet/App/Controller/Admin/Reports.pm b/perllib/FixMyStreet/App/Controller/Admin/Reports.pm
index 48386cf3e..052230643 100644
--- a/perllib/FixMyStreet/App/Controller/Admin/Reports.pm
+++ b/perllib/FixMyStreet/App/Controller/Admin/Reports.pm
@@ -50,6 +50,8 @@ sub index : Path {
 
     return if $c->cobrand->call_hook(report_search_query => $query, $p_page, $u_page, $order);
 
+    my $problems = $c->cobrand->problems;
+
     if (my $search = $c->get_param('search')) {
         $search = $self->trim($search);
 
@@ -62,9 +64,6 @@ sub index : Path {
 
         $c->stash->{searched} = $search;
 
-        my $search_n = 0;
-        $search_n = int($search) if $search =~ /^\d+$/;
-
         my $like_search = "%$search%";
 
         my $parsed = FixMyStreet::SMS->parse_username($search);
@@ -92,20 +91,10 @@ sub index : Path {
                 'me.external_id' => { like => "%$1%" }
             ];
         } else {
-            $query->{'-or'} = [
-                'me.id' => $search_n,
-                'user.email' => { ilike => $like_search },
-                'user.phone' => { ilike => $like_search },
-                'me.external_id' => { ilike => $like_search },
-                'me.name' => { ilike => $like_search },
-                'me.title' => { ilike => $like_search },
-                detail => { ilike => $like_search },
-                bodies_str => { like => $like_search },
-                cobrand_data => { like => $like_search },
-            ];
+            $problems = $problems->search_text($search);
         }
 
-        my $problems = $c->cobrand->problems->search(
+        $problems = $problems->search(
             $query,
             {
                 join => 'user',
@@ -119,6 +108,7 @@ sub index : Path {
         $c->stash->{problems} = [ $problems->all ];
         $c->stash->{problems_pager} = $problems->pager;
 
+        my $updates = $c->cobrand->updates;
         if ($valid_email) {
             $query = [
                 'user.email' => { ilike => $like_search },
@@ -133,24 +123,18 @@ sub index : Path {
                 'me.problem_id' => int($1),
             ];
         } elsif ($search =~ /^area:(\d+)$/) {
-            $query = [];
+            $query = 0;
         } else {
-            $query = [
-                'me.id' => $search_n,
-                'problem.id' => $search_n,
-                'user.email' => { ilike => $like_search },
-                'user.phone' => { ilike => $like_search },
-                'me.name' => { ilike => $like_search },
-                text => { ilike => $like_search },
-                'me.cobrand_data' => { ilike => $like_search },
-            ];
+            $updates = $updates->search_text($search);
+            $query = 1;
         }
 
-        if (@$query) {
-            my $updates = $c->cobrand->updates->search(
-                {
-                    -or => $query,
-                },
+        $query = { -or => $query } if ref $query;
+
+        if ($query) {
+            $query = undef unless ref $query;
+            $updates = $updates->search(
+                $query,
                 {
                     '+columns' => ['user.email'],
                     join => 'user',
@@ -165,7 +149,7 @@ sub index : Path {
 
     } else {
 
-        my $problems = $c->cobrand->problems->search(
+        $problems = $problems->search(
             $query,
             {
                 '+columns' => ['user.email'],
diff --git a/perllib/FixMyStreet/DB/ResultSet/Comment.pm b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
index 034b86a40..0a4403e52 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Comment.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Comment.pm
@@ -4,12 +4,17 @@ use base 'DBIx::Class::ResultSet';
 use strict;
 use warnings;
 
+use Moo;
+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 to_body {
     my ($rs, $bodies) = @_;
     return FixMyStreet::DB::ResultSet::Problem::to_body($rs, $bodies, 1);
 }
 
-
 sub timeline {
     my ( $rs ) = @_;
 
diff --git a/perllib/FixMyStreet/DB/ResultSet/Contact.pm b/perllib/FixMyStreet/DB/ResultSet/Contact.pm
index 3aceee9e7..accdbb7de 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Contact.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Contact.pm
@@ -5,7 +5,7 @@ use strict;
 use warnings;
 use POSIX qw(strcoll);
 
-sub me { join('.', shift->current_source_alias, shift || q{})  }
+__PACKAGE__->load_components('Helper::ResultSet::Me');
 
 =head2 not_deleted
 
diff --git a/perllib/FixMyStreet/DB/ResultSet/Problem.pm b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
index 9f7c035ed..3ac9187ee 100644
--- a/perllib/FixMyStreet/DB/ResultSet/Problem.pm
+++ b/perllib/FixMyStreet/DB/ResultSet/Problem.pm
@@ -8,6 +8,12 @@ use Memcached;
 use mySociety::Locale;
 use FixMyStreet::DB;
 
+use Moo;
+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) }
+
 my $site_key;
 
 sub set_restriction {
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;
+