diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema.sql | 20 | ||||
| -rw-r--r-- | db/schema_0005-add_council_user_flag.sql | 6 | ||||
| -rw-r--r-- | db/schema_0006-alter_problem_state.sql | 19 | ||||
| -rw-r--r-- | db/schema_0007-add-comment-problem-state.sql | 16 | ||||
| -rw-r--r-- | db/schema_0008-add_user_object_to_admin_log.sql | 12 |
5 files changed, 72 insertions, 1 deletions
diff --git a/db/schema.sql b/db/schema.sql index 53c236dd6..99cf2832d 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -131,6 +131,7 @@ create table users ( name text, phone text, password text not null default '', + from_council integer, -- id of council user is from or null/0 if not flagged boolean not null default 'f' ); @@ -166,7 +167,13 @@ create table problem ( state text not null check ( state = 'unconfirmed' or state = 'confirmed' + or state = 'investigating' + or state = 'planned' + or state = 'in progress' + or state = 'closed' or state = 'fixed' + or state = 'fixed - council' + or state = 'fixed - user' or state = 'hidden' or state = 'partial' ), @@ -281,7 +288,17 @@ create table comment ( lang text not null default 'en-gb', 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' + 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 = 'closed' + or problem_state = 'fixed' + or problem_state = 'fixed - council' + or problem_state = 'fixed - user' + ) -- other fields? one to indicate whether this was written by the council -- and should be highlighted in the display? ); @@ -380,6 +397,7 @@ create table admin_log ( object_type text not null check ( object_type = 'problem' or object_type = 'update' + or object_type = 'user' ), object_id integer not null, action text not null check ( diff --git a/db/schema_0005-add_council_user_flag.sql b/db/schema_0005-add_council_user_flag.sql new file mode 100644 index 000000000..1ab1579b7 --- /dev/null +++ b/db/schema_0005-add_council_user_flag.sql @@ -0,0 +1,6 @@ +begin; + +ALTER table users + ADD COLUMN from_council integer; + +commit; diff --git a/db/schema_0006-alter_problem_state.sql b/db/schema_0006-alter_problem_state.sql new file mode 100644 index 000000000..6fada1eb8 --- /dev/null +++ b/db/schema_0006-alter_problem_state.sql @@ -0,0 +1,19 @@ +begin; + + ALTER TABLE problem DROP CONSTRAINT problem_state_check; + + ALTER TABLE problem ADD CONSTRAINT problem_state_check CHECK ( + state = 'unconfirmed' + or state = 'confirmed' + or state = 'investigating' + or state = 'planned' + or state = 'in progress' + or state = 'closed' + or state = 'fixed' + or state = 'fixed - council' + or state = 'fixed - user' + or state = 'hidden' + or state = 'partial' + ); + +commit; diff --git a/db/schema_0007-add-comment-problem-state.sql b/db/schema_0007-add-comment-problem-state.sql new file mode 100644 index 000000000..2a3a95ada --- /dev/null +++ b/db/schema_0007-add-comment-problem-state.sql @@ -0,0 +1,16 @@ +begin; + + ALTER TABLE comment ADD column problem_state text; + + 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 = 'closed' + or problem_state = 'fixed' + or problem_state = 'fixed - council' + or problem_state = 'fixed - user' + ); + +commit; diff --git a/db/schema_0008-add_user_object_to_admin_log.sql b/db/schema_0008-add_user_object_to_admin_log.sql new file mode 100644 index 000000000..dd8f03645 --- /dev/null +++ b/db/schema_0008-add_user_object_to_admin_log.sql @@ -0,0 +1,12 @@ +begin; + + ALTER TABLE admin_log DROP CONSTRAINT admin_log_object_type_check; + + ALTER TABLE admin_log ADD CONSTRAINT admin_log_object_type_check CHECK ( + object_type = 'problem' + or object_type = 'update' + or object_type = 'user' + ); + + +commit; |