New schema to bring alert_type up to date with what it says it is.

1 files changed, 19 insertions, 0 deletions

diff --git a/db/schema_0035-update_alert_types_for_bodies.sql b/db/schema_0035-update_alert_types_for_bodies.sql
new file mode 100644
index 000000000..d7f1be475
--- /dev/null
+++ b/db/schema_0035-update_alert_types_for_bodies.sql
@@ -0,0 +1,19 @@
+BEGIN;
+
+    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
+    (bodies_str like ''%''||?||''%'' or bodies_str is null) and
+    areas like ''%,''||?||'',%''' 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
+    (bodies_str like ''%''||?||''%'' or bodies_str is null) and
+    areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems';
+
+COMMIT;