Add state table to database.

4 files changed, 101 insertions, 63 deletions

diff --git a/db/alert_types.sql b/db/alert_types.sql
index 471fd905f..84f446f2c 100644
--- a/db/alert_types.sql
+++ b/db/alert_types.sql
@@ -18,11 +18,8 @@ insert into alert_type
 values ('new_problems', '', '',
     'New problems on FixMyStreet', '/', 'The latest problems reported by users',
     'problem',
-    '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'' )',
+    'problem.non_public = ''f'' and problem.state NOT IN
+        (''unconfirmed'', ''hidden'', ''partial'')',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem');
 
@@ -46,11 +43,8 @@ insert into alert_type
 values ('local_problems', '', '',
     'New local problems on FixMyStreet', '/', 'The latest local problems reported by users',
     'problem_find_nearby(?, ?, ?) as nearby,problem',
-    'nearby.problem_id = problem.id and 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'')',
+    'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state NOT IN
+        (''unconfirmed'', ''hidden'', ''partial'')',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
@@ -74,11 +68,8 @@ insert into alert_type
 values ('postcode_local_problems', '', '',
     'New problems near {{POSTCODE}} on FixMyStreet', '/', 'The latest local problems reported by users',
     'problem_find_nearby(?, ?, ?) as nearby,problem',
-    'nearby.problem_id = problem.id and 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'')',
+    'nearby.problem_id = problem.id and problem.non_public = ''f'' and problem.state NOT IN
+        (''unconfirmed'', ''hidden'', ''partial'')',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby');
 
@@ -102,11 +93,8 @@ insert into alert_type
 values ('council_problems', '', '',
     'New problems to {{COUNCIL}} on FixMyStreet', '/reports', 'The latest problems for {{COUNCIL}} reported by users',
     'problem',
-    '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
+    'problem.non_public = ''f'' and problem.state NOT IN
+        (''unconfirmed'', ''hidden'', ''partial'') AND
     regexp_split_to_array(bodies_str, '','') && ARRAY[?]',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council'
@@ -122,11 +110,8 @@ values ('ward_problems', '', '',
     'New problems for {{COUNCIL}} within {{WARD}} ward on FixMyStreet', '/reports',
     'The latest problems for {{COUNCIL}} within {{WARD}} ward reported by users',
     'problem',
-    '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
+    'problem.non_public = ''f'' and problem.state NOT IN
+        (''unconfirmed'', ''hidden'', ''partial'') AND
     (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and
     areas like ''%,''||?||'',%''',
     'created desc',
@@ -142,11 +127,8 @@ insert into alert_type
 values ('area_problems', '', '',
     'New problems within {{NAME}}''s boundary on FixMyStreet', '/reports',
     'The latest problems within {{NAME}}''s boundary reported by users', 'problem',
-    '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
+    'problem.non_public = ''f'' and problem.state NOT IN
+        (''unconfirmed'', ''hidden'', ''partial'') AND
     areas like ''%,''||?||'',%''',
     'created desc',
     '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area'
diff --git a/db/downgrade_0054---0053.sql b/db/downgrade_0054---0053.sql
new file mode 100644
index 000000000..6fcc41c33
--- /dev/null
+++ b/db/downgrade_0054---0053.sql
@@ -0,0 +1,39 @@
+BEGIN;
+
+DROP TABLE state;
+
+ALTER TABLE problem ADD CONSTRAINT problem_state_check CHECK (
+    state = 'unconfirmed'
+    or state = 'hidden'
+    or state = 'partial'
+    or state = 'confirmed'
+    or state = 'investigating'
+    or state = 'planned'
+    or state = 'in progress'
+    or state = 'action scheduled'
+    or state = 'fixed'
+    or state = 'fixed - council'
+    or state = 'fixed - user'
+    or state = 'closed'
+    or state = 'unable to fix'
+    or state = 'not responsible'
+    or state = 'duplicate'
+    or state = 'internal referral'
+);
+ALTER TABLE comment ADD CONSTRAINT comment_problem_state_check CHECK (
+    problem_state = 'confirmed'
+    or problem_state = 'investigating'
+    or problem_state = 'planned'
+    or problem_state = 'in progress'
+    or problem_state = 'action scheduled'
+    or problem_state = 'fixed'
+    or problem_state = 'fixed - council'
+    or problem_state = 'fixed - user'
+    or problem_state = 'closed'
+    or problem_state = 'unable to fix'
+    or problem_state = 'not responsible'
+    or problem_state = 'duplicate'
+    or problem_state = 'internal referral'
+);
+
+COMMIT;
diff --git a/db/schema.sql b/db/schema.sql
index ed930a13e..12e66b53a 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -177,24 +177,7 @@ create table problem (
     -- Metadata
     created timestamp not null default current_timestamp,
     confirmed timestamp,
-    state text not null check (
-        state = 'unconfirmed'
-        or state = 'confirmed'
-        or state = 'investigating'
-        or state = 'planned'
-        or state = 'in progress'
-        or state = 'action scheduled'
-        or state = 'closed'
-        or state = 'fixed'
-        or state = 'fixed - council'
-        or state = 'fixed - user'
-        or state = 'hidden'
-        or state = 'partial'
-        or state = 'unable to fix'
-        or state = 'not responsible'
-        or state = 'duplicate'
-        or state = 'internal referral'
-    ),
+    state text not null,
     lang text not null default 'en-gb',
     service text not null default '',
     cobrand text not null default '' check (cobrand ~* '^[a-z0-9_]*$'),
@@ -327,21 +310,7 @@ create table comment (
     cobrand_data text not null default '' check (cobrand_data ~* '^[a-z0-9_]*$'), -- Extra data used in cobranded versions of the site
     mark_fixed boolean not null,
     mark_open boolean not null default 'f',
-    problem_state text check (
-        problem_state = 'confirmed'
-        or problem_state = 'investigating'
-        or problem_state = 'planned'
-        or problem_state = 'in progress'
-        or problem_state = 'action scheduled'
-        or problem_state = 'closed'
-        or problem_state = 'fixed'
-        or problem_state = 'fixed - council'
-        or problem_state = 'fixed - user'
-        or problem_state = 'unable to fix'
-        or problem_state = 'not responsible'
-        or problem_state = 'duplicate'
-        or problem_state = 'internal referral'
-    ),
+    problem_state text,
     -- other fields? one to indicate whether this was written by the council
     -- and should be highlighted in the display?
     external_id text,
@@ -546,3 +515,10 @@ CREATE TABLE report_extra_fields (
     language text,
     extra text
 );
+
+CREATE TABLE state (
+    id serial not null primary key,
+    label text not null unique,
+    type text not null check (type = 'open' OR type = 'closed'),
+    name text not null unique
+);
diff --git a/db/schema_0054-add-state-table.sql b/db/schema_0054-add-state-table.sql
new file mode 100644
index 000000000..c052f145a
--- /dev/null
+++ b/db/schema_0054-add-state-table.sql
@@ -0,0 +1,41 @@
+BEGIN;
+
+CREATE TABLE state (
+    id serial not null primary key,
+    label text not null unique,
+    type text not null check (type = 'open' OR type = 'closed'),
+    name text not null unique
+);
+
+ALTER TABLE problem DROP CONSTRAINT problem_state_check;
+ALTER TABLE comment DROP CONSTRAINT comment_problem_state_check;
+
+UPDATE alert_type SET item_where = 'nearby.problem_id = problem.id
+    and problem.non_public = ''f''
+    and problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')'
+    WHERE ref = 'postcode_local_problems';
+UPDATE alert_type set item_where = 'problem.non_public = ''f''
+    and problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')'
+    WHERE ref = 'new_problems';
+UPDATE alert_type set item_where = 'problem.non_public = ''f''
+    and problem.state in (''fixed'', ''fixed - user'', ''fixed - council'')'
+    WHERE ref = 'new_fixed_problems';
+UPDATE alert_type set item_where = 'nearby.problem_id = problem.id
+    and problem.non_public = ''f''
+    and problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')'
+    WHERE ref = 'local_problems';
+UPDATE alert_type set item_where = 'problem.non_public = ''f''
+    AND problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')
+    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 NOT IN (''hidden'', ''unconfirmed'', ''partial'')
+    AND (regexp_split_to_array(bodies_str, '','') && ARRAY[?] or bodies_str is null) and
+    areas like ''%,''||?||'',%'''
+    WHERE ref = 'ward_problems';
+UPDATE alert_type set item_where = 'problem.non_public = ''f''
+    AND problem.state NOT IN (''hidden'', ''unconfirmed'', ''partial'')
+    AND areas like ''%,''||?||'',%'''
+    WHERE ref = 'area_problems';
+
+COMMIT;