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.

1 files changed, 21 insertions, 0 deletions

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;