diff options
Diffstat (limited to 'db')
| -rw-r--r-- | db/alert_types.sql | 54 | ||||
| -rw-r--r-- | db/alert_types_eha.sql | 8 | ||||
| -rw-r--r-- | db/schema.sql | 140 | ||||
| -rw-r--r-- | db/schema_0022-add_interest_count_to_problems.sql | 2 | ||||
| -rw-r--r-- | db/schema_0024-add_non_public_categories_and_problems.sql | 1 | ||||
| -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 | ||||
| -rw-r--r-- | db/schema_0026-change_interest_count_default.sql | 9 | ||||
| -rw-r--r-- | db/schema_0028-add-bodies.sql | 87 | ||||
| -rw-r--r-- | db/schema_0029-add_deleted_flag_to_body.sql | 6 | ||||
| -rw-r--r-- | db/schema_0030-drop-action-log-check-constraint.sql | 6 | ||||
| -rw-r--r-- | db/schema_0031-add_url_to_body.sql | 9 |
12 files changed, 312 insertions, 99 deletions
diff --git a/db/alert_types.sql b/db/alert_types.sql index 4116d29dc..4022ddaf7 100644 --- a/db/alert_types.sql +++ b/db/alert_types.sql @@ -17,7 +17,13 @@ insert into alert_type item_title, item_link, item_description, template) 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'')', 'created desc', + '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'' )', + 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem'); -- New fixed problems anywhere on the site @@ -39,7 +45,13 @@ insert into alert_type item_title, item_link, item_description, template) 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'')', 'created desc', + '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'')', + 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a location @@ -61,7 +73,13 @@ insert into alert_type item_title, item_link, item_description, template) 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'')', 'created desc', + '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'')', + 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-nearby'); -- New problems around a postcode with a particular state @@ -83,8 +101,15 @@ insert into alert_type item_title, item_link, item_description, template) 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'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + '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 + (bodies_str like ''%''||?||''%'' or bodies_str is null) and + areas like ''%,''||?||'',%''', + 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council' ); @@ -97,8 +122,15 @@ insert into alert_type 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'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + '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 + (bodies_str like ''%''||?||''%'' or bodies_str is null) and + areas like ''%,''||?||'',%''', + 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward' ); @@ -111,7 +143,13 @@ 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'') and areas like ''%,''||?||'',%''', 'created desc', + '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 + areas like ''%,''||?||'',%''', + 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-area' ); diff --git a/db/alert_types_eha.sql b/db/alert_types_eha.sql index af074717b..e4ec0c986 100644 --- a/db/alert_types_eha.sql +++ b/db/alert_types_eha.sql @@ -61,8 +61,8 @@ insert into alert_type item_title, item_link, item_description, template) values ('council_problems', '', '', 'New reports to {{COUNCIL}} on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} reported by users', - 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%'' + or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-council' ); @@ -75,8 +75,8 @@ insert into alert_type values ('ward_problems', '', '', 'New reports for {{COUNCIL}} within {{WARD}} ward on reportemptyhomes.com', '/reports', 'The latest reports for {{COUNCIL}} within {{WARD}} ward reported by users', - 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (council like ''%''||?||''%'' - or council is null) and areas like ''%,''||?||'',%''', 'created desc', + 'problem', 'problem.state in (''confirmed'', ''investigating'', ''planned'', ''in progress'', ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'') and (bodies_str like ''%''||?||''%'' + or bodies_str is null) and areas like ''%,''||?||'',%''', 'created desc', '{{title}}, {{confirmed}}', '/report/{{id}}', '{{detail}}', 'alert-problem-ward' ); diff --git a/db/schema.sql b/db/schema.sql index 87e785de2..6ddb7bae6 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -4,8 +4,6 @@ -- Copyright (c) 2006 UK Citizens Online Democracy. All rights reserved. -- Email: matthew@mysociety.org; WWW: http://www.mysociety.org/ -- --- $Id: schema.sql,v 1.49 2009-11-10 13:53:33 louise Exp $ --- -- secret -- A random secret. @@ -19,22 +17,6 @@ create table debugdate ( override_today date ); --- Returns the date of "today", which can be overriden for testing. -create function ms_current_date() - returns date as ' - declare - today date; - begin - today = (select override_today from debugdate); - if today is not null then - return today; - else - return current_date; - end if; - - end; -' language 'plpgsql' stable; - -- Returns the timestamp of current time, but with possibly overriden "today". create function ms_current_timestamp() returns timestamp as ' @@ -50,24 +32,57 @@ create function ms_current_timestamp() end; ' language 'plpgsql'; +-- table for sessions - needed by Catalyst::Plugin::Session::Store::DBIC +create table sessions ( + id char(72) primary key, + session_data text, + expires integer +); --- users, but call the table person rather than user so we don't have to quote --- its name in every statement.... --- create table person ( --- id serial not null primary key, --- name text, --- email text not null, --- password text, --- website text, --- numlogins integer not null default 0 --- ); --- --- create unique index person_email_idx on person(email); - --- Who to send problems for a specific MaPit area ID to +-- users table +create table users ( + id serial not null primary key, + email text not null unique, + name text, + phone text, + password text not null default '', + from_body integer, + flagged boolean not null default 'f', + title text +); + +-- Record details of reporting bodies, including open311 configuration details +create table body ( + id serial primary key, + name text not null, + external_url text, + parent integer references body(id), + endpoint text, + jurisdiction text, + api_key text, + send_method text, + 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', + send_extended_statuses boolean not null default 'f', + deleted boolean not null default 'f' +); + +create table body_areas ( + body_id integer not null references body(id), + area_id integer not null +); +create unique index body_areas_body_id_area_id_idx on body_areas(body_id, area_id); + +-- Now can create reference from users to body +ALTER TABLE users ADD CONSTRAINT users_from_body_fkey + FOREIGN KEY (from_body) REFERENCES body(id); + +-- The contact for a category within a particular body create table contacts ( id serial primary key, - area_id integer not null, + body_id integer not null references body(id), category text not null default 'Other', email text not null, confirmed boolean not null, @@ -92,7 +107,7 @@ create table contacts ( api_key text default '', send_method text ); -create unique index contacts_area_id_category_idx on contacts(area_id, category); +create unique index contacts_body_id_category_idx on contacts(body_id, category); -- History of changes to contacts - automatically updated -- whenever contacts is changed, using trigger below. @@ -100,7 +115,7 @@ create table contacts_history ( contacts_history_id serial not null primary key, contact_id integer not null, - area_id integer not null, + body_id integer not null, category text not null default 'Other', email text not null, confirmed boolean not null, @@ -119,7 +134,7 @@ create table contacts_history ( create function contacts_updated() returns trigger as ' begin - insert into contacts_history (contact_id, area_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.area_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); + insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); return new; end; ' language 'plpgsql'; @@ -129,25 +144,6 @@ create trigger contacts_update_trigger after update on contacts create trigger contacts_insert_trigger after insert on contacts for each row execute procedure contacts_updated(); --- table for sessions - needed by Catalyst::Plugin::Session::Store::DBIC -create table sessions ( - id char(72) primary key, - session_data text, - expires integer -); - --- users table -create table users ( - id serial not null primary key, - email text not null unique, - 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', - title text -); - -- Problems reported by users of site create table problem ( id serial not null primary key, @@ -156,8 +152,8 @@ create table problem ( postcode text not null, latitude double precision not null, longitude double precision not null, - council text, -- the council(s) we'll report this problem to - areas text not null, -- the voting areas this location is in + bodies_str text, -- the body(s) we'll report this problem to + areas text not null, -- the mapit areas this location is in category text not null default 'Other', title text not null, detail text not null, @@ -183,12 +179,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 +332,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? @@ -443,24 +449,6 @@ create table admin_log ( or object_type = 'user' ), object_id integer not null, - action text not null check ( - action = 'edit' - or action = 'state_change' - or action = 'resend'), + action text not null, whenedited timestamp not null default ms_current_timestamp() ); - --- Record open 311 configuration details - -create table open311conf ( - id serial primary key, - area_id integer not null unique, - endpoint text not null, - jurisdiction text, - api_key text, - send_method text, - 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' -); diff --git a/db/schema_0022-add_interest_count_to_problems.sql b/db/schema_0022-add_interest_count_to_problems.sql index 62092aa0a..4a4703ae1 100644 --- a/db/schema_0022-add_interest_count_to_problems.sql +++ b/db/schema_0022-add_interest_count_to_problems.sql @@ -1,6 +1,6 @@ begin; ALTER table problem - ADD COLUMN interest_count integer; + ADD COLUMN interest_count integer DEFAULT 0; commit; diff --git a/db/schema_0024-add_non_public_categories_and_problems.sql b/db/schema_0024-add_non_public_categories_and_problems.sql index c133b3ef2..89367610a 100644 --- a/db/schema_0024-add_non_public_categories_and_problems.sql +++ b/db/schema_0024-add_non_public_categories_and_problems.sql @@ -34,4 +34,3 @@ BEGIN; commit; -COMMIT; 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..f450bd8a9 --- /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.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'')' + WHERE ref = 'postcode_local_problems'; + UPDATE alert_type set item_where = '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'' )' + 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 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.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 + (council like ''%''||?||''%'' or council is null) and + areas like ''%,''||?||'',%''' WHERE ref = 'council_problems'; + UPDATE alert_type set item_where = '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 + (council like ''%''||?||''%'' or council is null) and + areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems'; + UPDATE alert_type set item_where = '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 + 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_0026-change_interest_count_default.sql b/db/schema_0026-change_interest_count_default.sql deleted file mode 100644 index 7c78f6fe8..000000000 --- a/db/schema_0026-change_interest_count_default.sql +++ /dev/null @@ -1,9 +0,0 @@ -BEGIN; - - ALTER TABLE problem - ALTER COLUMN interest_count SET DEFAULT 0; - - UPDATE problem SET interest_count = 0 - WHERE interest_count IS NULL; - -COMMIT; diff --git a/db/schema_0028-add-bodies.sql b/db/schema_0028-add-bodies.sql new file mode 100644 index 000000000..b56738cf3 --- /dev/null +++ b/db/schema_0028-add-bodies.sql @@ -0,0 +1,87 @@ +BEGIN; + +-- Remove unused function +drop function ms_current_date(); + +-- Rename open311conf to create the new body table +ALTER TABLE open311conf RENAME TO body; +ALTER INDEX open311conf_pkey RENAME TO body_pkey; +ALTER INDEX open311conf_area_id_key RENAME TO body_area_id_key; +ALTER TABLE body ALTER COLUMN endpoint DROP NOT NULL; +ALTER TABLE body DROP CONSTRAINT open311conf_comment_user_id_fkey; +ALTER TABLE body ADD CONSTRAINT body_comment_user_id_fkey + FOREIGN KEY (comment_user_id) REFERENCES users(id); +ALTER SEQUENCE open311conf_id_seq RENAME TO body_id_seq; + +-- Update contacts column to be better named +ALTER TABLE contacts RENAME area_id TO body_id; +ALTER TABLE contacts_history RENAME area_id TO body_id; +ALTER INDEX contacts_area_id_category_idx RENAME TO contacts_body_id_category_idx; + +-- Data migration from contacts +UPDATE body SET id = area_id; +INSERT INTO body (id, area_id) + SELECT DISTINCT body_id, body_id FROM contacts WHERE body_id not in (SELECT id FROM body); +SELECT setval('body_id_seq', (SELECT MAX(id) FROM body) ); + +ALTER TABLE contacts ADD CONSTRAINT contacts_body_id_fkey + FOREIGN KEY (body_id) REFERENCES body(id); + +DROP TRIGGER contacts_update_trigger ON contacts; +DROP TRIGGER contacts_insert_trigger ON contacts; +DROP FUNCTION contacts_updated(); +create function contacts_updated() + returns trigger as ' + begin + insert into contacts_history (contact_id, body_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.id, new.body_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted); + return new; + end; +' language 'plpgsql'; +create trigger contacts_update_trigger after update on contacts + for each row execute procedure contacts_updated(); +create trigger contacts_insert_trigger after insert on contacts + for each row execute procedure contacts_updated(); + +-- Give bodies a name +ALTER TABLE body ADD name text; +UPDATE body SET name=''; +ALTER table body ALTER COLUMN name SET NOT NULL; + +-- Update users column to be better named +ALTER TABLE users RENAME COLUMN from_council TO from_body; +ALTER TABLE users ADD CONSTRAINT users_from_body_fkey + FOREIGN KEY (from_body) REFERENCES body(id); + +-- Rename problem's council column +ALTER TABLE problem RENAME COLUMN council TO bodies_str; + +-- Update alert types that used 'council' column +UPDATE alert_type set item_where = '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 +(bodies_str like ''%''||?||''%'' or bodies_str is null) and +areas like ''%,''||?||'',%''' WHERE ref = 'council_problems'; +UPDATE alert_type set item_where = '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 +(bodies_str like ''%''||?||''%'' or bodies_str is null) and +areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems'; + +-- Move to many-many relationship between bodies and areas +create table body_areas ( + body_id integer not null references body(id), + area_id integer not null +); +create unique index body_areas_body_id_area_id_idx on body_areas(body_id, area_id); +INSERT INTO body_areas (body_id, area_id) + SELECT id, area_id FROM body; +ALTER TABLE body DROP COLUMN area_id; + +-- Allow bodies to have a hierarchy +ALTER TABLE body ADD parent INTEGER REFERENCES body(id); + +COMMIT; diff --git a/db/schema_0029-add_deleted_flag_to_body.sql b/db/schema_0029-add_deleted_flag_to_body.sql new file mode 100644 index 000000000..3a8be7890 --- /dev/null +++ b/db/schema_0029-add_deleted_flag_to_body.sql @@ -0,0 +1,6 @@ +BEGIN; + +ALTER table body + ADD column deleted BOOL NOT NULL DEFAULT 'f'; + +COMMIT; diff --git a/db/schema_0030-drop-action-log-check-constraint.sql b/db/schema_0030-drop-action-log-check-constraint.sql new file mode 100644 index 000000000..dfc5387f3 --- /dev/null +++ b/db/schema_0030-drop-action-log-check-constraint.sql @@ -0,0 +1,6 @@ +BEGIN; + +ALTER TABLE admin_log + DROP CONSTRAINT admin_log_action_check; + +COMMIT; diff --git a/db/schema_0031-add_url_to_body.sql b/db/schema_0031-add_url_to_body.sql new file mode 100644 index 000000000..7aaa78c64 --- /dev/null +++ b/db/schema_0031-add_url_to_body.sql @@ -0,0 +1,9 @@ +BEGIN; + +-- `external_url' includes an URL for reporting problems to the body directly. +-- It can be displayed in the new report form when no contacts are set; see +-- templates/web/fixamingata/report/new/councils_text_none.html for an example. + +ALTER TABLE body ADD external_url TEXT; + +COMMIT; |