Simplify/consolidate body restriction db code.

Make dashboard work properly in two-tier councils, showing reports sent
to both. Create an index on the array of the bodies_str column to speed
up performance, and use that throughout the code replacing all LIKE
scans. This also enables a simplifying tidy of the restriction code.

5 files changed, 25 insertions, 127 deletions

diff --git a/db/alert_types.sql b/db/alert_types.sql
index 4022ddaf7..471fd905f 100644
--- a/db/alert_types.sql
+++ b/db/alert_types.sql
@@ -93,7 +93,7 @@ values ('postcode_local_problems_state', '', '',
     'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state in (?)', 'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
--- New problems sent to a particular council
+-- New problems sent to a particular body
 insert into alert_type
 (ref, head_sql_query, head_table,
     head_title, head_link, head_description,
@@ -107,8 +107,7 @@ values ('council_problems', '', '',
       ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
      ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
      ''internal referral'' ) AND
-    (bodies_str like ''%''||?||''%'' or bodies_str is null) and
-    areas like ''%,''||?||'',%''',
+    regexp_split_to_array(bodies_str, '','') && ARRAY[?]',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
 );
@@ -128,7 +127,7 @@ values ('ward_problems', '', '',
      ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
      ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
      ''internal referral'' ) AND
-    (bodies_str like ''%''||?||''%'' or bodies_str is null) and
+    (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and
     areas like ''%,''||?||'',%''',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
diff --git a/db/alert_types_eha.pl b/db/alert_types_eha.pl
deleted file mode 100644
index b090522e6..000000000
--- a/db/alert_types_eha.pl
+++ /dev/null
@@ -1,28 +0,0 @@
-# This file is only here so that the strings from alert_types_eha.sql
-# get into the .po translation file.
-
-# New problems anywhere on the site
-    _('New reports on reportemptyhomes.com'),
-
-# New fixed problems anywhere on the site
-    _('Properties recently reported as put back to use on reportemptyhomes.com'),
-    _('The latest properties reported back to use by users'),
-
-# New problems around a location
-    _('New local reports on reportemptyhomes.com'),
-    _('The latest local reports reported by users'),
-
-# New problems around a postcode
-    _('New reports on reportemptyhomes.com near {{POSTCODE}}'),
-
-# New problems sent to a particular council
-    _('New reports to {{COUNCIL}} on reportemptyhomes.com'),
-    _('The latest reports for {{COUNCIL}} reported by users'),
-
-# New problems within a particular ward sent to a particular council
-    _('New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com'),
-    _('The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users'),
-
-# New problems within a particular voting area (ward, constituency, whatever)
-    _('New reports within {{NAME}}\'s boundary on reportemptyhomes.com'),
-    _('The latest reports within {{NAME}}\'s boundary reported by users'),
diff --git a/db/alert_types_eha.sql b/db/alert_types_eha.sql
deleted file mode 100644
index e4ec0c986..000000000
--- a/db/alert_types_eha.sql
+++ /dev/null
@@ -1,95 +0,0 @@
--- New updates on a particular problem report
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('new_updates', 'select * from problem where id=?', 'problem',
-    'Updates on {{title}}', '/', 'Updates on {{title}}',
-    'comment', 'comment.state=''confirmed''', 'created desc',
-    'Update by {{name}}', '/report/{{problem_id}}#comment_{{id}}', '{{text}}', 'alert-update');
-
--- New problems anywhere on the site
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('new_problems', '', '',
-    'New reports on reportemptyhomes.com', '/', 'The latest empty properties reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
-
--- New fixed problems anywhere on the site
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('new_fixed_problems', '', '',
-    'Properties recently reported as put back to use on reportemptyhomes.com', '/', 'The latest properties reported back to use by users',
-    'problem', 'problem.state in (''fixed'', ''sixed - council'', ''fixed - user'')', 'lastupdate desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
-
--- New problems around a location
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('local_problems', '', '',
-    'New local reports on reportemptyhomes.com', '/', 'The latest local reports reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-
--- New problems around a postcode
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('postcode_local_problems', '', '',
-    'New reports on reportemptyhomes.com near {{POSTCODE}}', '/', 'The latest local reports reported by users',
-    'problem_find_nearby(?, ?, ?) as nearby,problem', 'nearby.problem_id = problem.id and problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'')', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
-
--- New problems sent to a particular council
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('council_problems', '', '',
-    'New reports to {{COUNCIL}} on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%''
-        or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
-);
-
--- New problems within a particular ward sent to a particular council
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('ward_problems', '', '',
-    'New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com', '/reports',
-    'The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users',
-    'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%''
-        or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward'
-);
-
--- New problems within a particular voting area (ward, constituency, whatever)
-insert into alert_type
-(ref, head_sql_query, head_table,
-    head_title, head_link, head_description,
-    item_table, item_where, item_order,
-    item_title, item_link, item_description, template)
-values ('area_problems', '', '',
-    'New reports within {{NAME}}''s boundary on reportemptyhomes.com', '/reports',
-    'The latest reports within {{NAME}}''s boundary reported by users', 'problem',
-    'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and areas like ''%,''||?||'',%''', 'created desc',
-    '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
-);
-
diff --git a/db/schema.sql b/db/schema.sql
index 5d42d57cf..ea2698b0e 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -207,6 +207,7 @@ create index problem_state_latitude_longitude_idx on problem(state, latitude, lo
 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 table questionnaire (
     id serial not null primary key,
diff --git a/db/schema_0035-bodies_str-tidying.sql b/db/schema_0035-bodies_str-tidying.sql
index c4c7badaf..f5e1dbbdd 100644
--- a/db/schema_0035-bodies_str-tidying.sql
+++ b/db/schema_0035-bodies_str-tidying.sql
@@ -7,4 +7,25 @@ update problem
         bodies_str = split_part(bodies_str, '|', 1)
     where bodies_str like '%|%';
 
+create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ','));
+
+UPDATE alert_type set item_where =
+    'problem.non_public = ''f'' and problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+      ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+     ''internal referral'' ) AND
+    regexp_split_to_array(bodies_str, '','') && ARRAY[?]'
+    WHERE ref = 'council_problems';
+
+UPDATE alert_type set item_where =
+    'problem.non_public = ''f'' and problem.state in
+    (''confirmed'', ''investigating'', ''planned'', ''in progress'',
+     ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+     ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+     ''internal referral'' ) AND
+    (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and
+    areas like ''%,''||?||'',%'''
+    WHERE ref = 'ward_problems';
+
 commit;