From f82abb282f60191ee530c552bf025e509e2a57e8 Mon Sep 17 00:00:00 2001
From: Matthew Somerville <matthew@mysociety.org>
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 <matthew@mysociety.org>
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 <matthew@mysociety.org>
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 <matthew@mysociety.org>
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 <matthew@mysociety.org>
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 <matthew@mysociety.org>
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 <matthew@mysociety.org>
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 <matthew@mysociety.org>
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 <matthew@mysociety.org>
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

