Add state table to database.

1 files changed, 9 insertions, 33 deletions

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
+);