aboutsummaryrefslogtreecommitdiffstats
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/schema.sql18
-rw-r--r--db/schema_0025-add_more_statuses_to_problem.sql83
-rw-r--r--db/schema_0026-add_send_extended_comments_to_open311conf.sql6
-rw-r--r--db/schema_0027-add_sub_category_to_problem.sql6
4 files changed, 111 insertions, 2 deletions
diff --git a/db/schema.sql b/db/schema.sql
index d8cc0675a..5e4bc4a3e 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -63,7 +63,8 @@ create table body (
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'
);
create table body_areas (
@@ -176,12 +177,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 '',
@@ -210,7 +216,10 @@ create table problem (
external_source_id text,
-- number of me toos
- interest_count integer default 0
+ interest_count integer default 0,
+
+ -- subcategory to enable filtering in reporting --
+ subcategory text
);
create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude);
create index problem_user_id_idx on problem ( user_id );
@@ -321,10 +330,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?
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;
diff --git a/db/schema_0027-add_sub_category_to_problem.sql b/db/schema_0027-add_sub_category_to_problem.sql
new file mode 100644
index 000000000..6787bd371
--- /dev/null
+++ b/db/schema_0027-add_sub_category_to_problem.sql
@@ -0,0 +1,6 @@
+begin;
+
+ALTER TABLE problem
+ ADD COLUMN subcategory TEXT;
+
+commit;