diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/schema.sql | 13 | ||||
| -rw-r--r-- | db/schema_0025-add_more_statuses_to_problem.sql | 83 | ||||
| -rw-r--r-- | db/schema_0026-add_send_extended_comments_to_open311conf.sql | 6 |
3 files changed, 101 insertions, 1 deletions
diff --git a/db/schema.sql b/db/schema.sql index 87e785de2..5bdd2a66a 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -183,12 +183,17 @@ create table problem ( 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' ), lang text not null default 'en-gb', service text not null default '', @@ -331,10 +336,15 @@ create table comment ( 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' ), -- other fields? one to indicate whether this was written by the council -- and should be highlighted in the display? @@ -462,5 +472,6 @@ create table open311conf ( send_comments boolean not null default 'f', comment_user_id int references users(id), suppress_alerts boolean not null default 'f', - can_be_devolved boolean not null default 'f' + can_be_devolved boolean not null default 'f', + send_extended_statuses boolean not null default 'f' ); diff --git a/db/schema_0025-add_more_statuses_to_problem.sql b/db/schema_0025-add_more_statuses_to_problem.sql new file mode 100644 index 000000000..14d1b3195 --- /dev/null +++ b/db/schema_0025-add_more_statuses_to_problem.sql @@ -0,0 +1,83 @@ +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 = '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' + ); + + + ALTER TABLE comment DROP CONSTRAINT comment_problem_state_check; + + 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 = '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' + ); + + UPDATE alert_type set item_where = 'nearby.problem_id = problem.id 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'')' + WHERE ref = 'postcode_local_problems'; + UPDATE alert_type set item_where = 'problem.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'' + ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', + ''internal referral'' )' + WHERE ref = 'new_problems'; + UPDATE alert_type set item_where = '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.state in + (''confirmed'', ''investigating'', ''planned'', ''in progress'', + ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'', + ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'', + ''internal referral'')' + WHERE ref = 'local_problems'; + UPDATE alert_type set item_where = '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 + (council like ''%''||?||''%'' or council is null) and + areas like ''%,''||?||'',%''' WHERE ref = 'council_problems'; + UPDATE alert_type set item_where = '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 + (council like ''%''||?||''%'' or council is null) and + areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems'; + UPDATE alert_type set item_where = '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 + areas like ''%,''||?||'',%''' WHERE ref = 'area_problems'; + +COMMIT; diff --git a/db/schema_0026-add_send_extended_comments_to_open311conf.sql b/db/schema_0026-add_send_extended_comments_to_open311conf.sql new file mode 100644 index 000000000..ee7b44b75 --- /dev/null +++ b/db/schema_0026-add_send_extended_comments_to_open311conf.sql @@ -0,0 +1,6 @@ +begin; + +ALTER table open311conf + ADD column send_extended_statuses BOOL NOT NULL DEFAULT 'f'; + +commit; |