From f82abb282f60191ee530c552bf025e509e2a57e8 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Mon, 10 Dec 2012 16:28:52 +0000 Subject: Rename 'open311conf' database table to 'body'. --- db/schema.sql | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index 6ebec5689..87b5102c7 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -447,12 +447,12 @@ create table admin_log ( whenedited timestamp not null default ms_current_timestamp() ); --- Record open 311 configuration details +-- Record details of reporting bodies, including open311 configuration details -create table open311conf ( +create table body ( id serial primary key, area_id integer not null unique, - endpoint text not null, + endpoint text, jurisdiction text, api_key text, send_method text, -- cgit v1.2.3 From 07b07d90cf666a06cb071ceebcecbd21d91e6b60 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Wed, 12 Dec 2012 13:22:19 +0000 Subject: Rename from_council to from_body, and small related changes. --- db/schema.sql | 20 +------------------- 1 file changed, 1 insertion(+), 19 deletions(-) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index 87b5102c7..a829e68db 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 ' @@ -143,7 +125,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 + from_body integer, -- id of body user is from or null/0 if not flagged boolean not null default 'f', title text ); -- cgit v1.2.3 From fa4947c017af7599699892c1c3a647fc61f971ed Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Wed, 12 Dec 2012 16:48:27 +0000 Subject: Rename area_id on Contacts table to body_id. Add foreign key constraint from contacts to body. --- db/schema.sql | 24 +++++------------------- 1 file changed, 5 insertions(+), 19 deletions(-) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index a829e68db..549222b1a 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -32,24 +32,10 @@ create function ms_current_timestamp() end; ' language 'plpgsql'; - --- 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 +-- 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, @@ -74,7 +60,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. @@ -82,7 +68,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, @@ -101,7 +87,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'; -- cgit v1.2.3 From b715f69fc0a92e25761f124a3a05b7116a73c598 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Thu, 13 Dec 2012 12:27:38 +0000 Subject: Convert report sending code to use bodies rather than areas/councils. --- db/schema.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index 549222b1a..9fd4ce332 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -419,6 +419,7 @@ create table admin_log ( create table body ( id serial primary key, + name text not null, area_id integer not null unique, endpoint text, jurisdiction text, -- cgit v1.2.3 From 9fb130a0ab1bd36e977439b7697fba5fec5b1f00 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Thu, 13 Dec 2012 15:48:48 +0000 Subject: Rename council column to bodies_str, and all the related code. --- db/schema.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index 9fd4ce332..1608b08ba 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -124,8 +124,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, -- cgit v1.2.3 From ff06783c6dc50321100a54b4d4a624fde2212689 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Fri, 14 Dec 2012 12:11:13 +0000 Subject: Make from_body a foreign key. --- db/schema.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index 1608b08ba..8045ca6c4 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -111,7 +111,7 @@ create table users ( name text, phone text, password text not null default '', - from_body integer, -- id of body user is from or null/0 if not + from_body integer references body(id), -- id of body user is from or null/0 if not flagged boolean not null default 'f', title text ); -- cgit v1.2.3 From ddf9dbdbf6486c781ca4a5822614b0b7cee2c062 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Fri, 14 Dec 2012 19:07:08 +0000 Subject: Allow bodies to span multiple area IDs, and areas can be covered by more than one body. --- db/schema.sql | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index 8045ca6c4..fd0a3abd4 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -420,7 +420,6 @@ create table admin_log ( create table body ( id serial primary key, name text not null, - area_id integer not null unique, endpoint text, jurisdiction text, api_key text, @@ -430,3 +429,9 @@ create table body ( suppress_alerts boolean not null default 'f', can_be_devolved 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); -- cgit v1.2.3 From 3ce8e191fdd1f41555b353d589cb6a4a5ef1b38d Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Fri, 14 Dec 2012 23:51:42 +0000 Subject: Rejig schema.sql to prevent circular references. --- db/schema.sql | 83 +++++++++++++++++++++++++++++++---------------------------- 1 file changed, 43 insertions(+), 40 deletions(-) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index fd0a3abd4..e8ec42859 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -32,6 +32,49 @@ 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 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, + 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' +); + +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, @@ -97,25 +140,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_body integer references body(id), -- id of body 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, @@ -414,24 +438,3 @@ create table admin_log ( or action = 'resend'), whenedited timestamp not null default ms_current_timestamp() ); - --- Record details of reporting bodies, including open311 configuration details - -create table body ( - id serial primary key, - name text not null, - 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' -); - -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); -- cgit v1.2.3 From 1c1e454cc049531fc99c5c4d4eed293aab40d406 Mon Sep 17 00:00:00 2001 From: Matthew Somerville Date: Wed, 19 Dec 2012 14:19:30 +0000 Subject: Allow bodies to have parent bodies. --- db/schema.sql | 1 + 1 file changed, 1 insertion(+) (limited to 'db/schema.sql') diff --git a/db/schema.sql b/db/schema.sql index e8ec42859..d8cc0675a 100644 --- a/db/schema.sql +++ b/db/schema.sql @@ -55,6 +55,7 @@ create table users ( create table body ( id serial primary key, name text not null, + parent integer references body(id), endpoint text, jurisdiction text, api_key text, -- cgit v1.2.3