From 33f25780b0dccf306ec0cffd5fbee02c8f66cca8 Mon Sep 17 00:00:00 2001
From: Edmund von der Burg <evdb@mysociety.org>
Date: Mon, 4 Apr 2011 15:53:01 +0100
Subject: Moved schema changes back into the main schema file for deploy

---
 db/schema.sql | 21 +++++++++++++++++++--
 1 file changed, 19 insertions(+), 2 deletions(-)

(limited to 'db/schema.sql')

diff --git a/db/schema.sql b/db/schema.sql
index fbff047fb..9d194004c 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -115,6 +115,22 @@ 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 ''
+);
+
 -- Problems reported by users of site
 create table problem (
     id serial not null primary key,
@@ -132,9 +148,8 @@ create table problem (
     used_map boolean not null,
 
     -- User's details
+    user_id int references users(id) not null,    
     name text not null,
-    email text not null,
-    phone text not null,
     anonymous boolean not null,
 
     -- Metadata
@@ -156,6 +171,7 @@ create table problem (
     send_questionnaire boolean not null default 't'
 );
 create index problem_state_latitude_longitude_idx on problem(state, latitude, longitude);
+create index problem_user_id_idx on problem ( user_id );
 
 create table questionnaire (
     id serial not null primary key,
@@ -362,3 +378,4 @@ create table admin_log (
         or action = 'resend'),
     whenedited timestamp not null default ms_current_timestamp()
 ); 
+
-- 
cgit v1.2.3


From 4dd129c52e3afbe41c631f5b5ef31fa33e8b13f4 Mon Sep 17 00:00:00 2001
From: Edmund von der Burg <evdb@mysociety.org>
Date: Tue, 5 Apr 2011 11:42:33 +0100
Subject: Add back contacts changes

---
 db/schema.sql | 2 ++
 1 file changed, 2 insertions(+)

(limited to 'db/schema.sql')

diff --git a/db/schema.sql b/db/schema.sql
index 9d194004c..ae66fc28c 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -66,6 +66,7 @@ create function ms_current_timestamp()
 
 -- Who to send problems for a specific MaPit area ID to
 create table contacts (
+    id serial primary key,
     area_id integer not null,
     category text not null default 'Other',
     email text not null,
@@ -86,6 +87,7 @@ create unique index contacts_area_id_category_idx on contacts(area_id, category)
 create table contacts_history (
     contacts_history_id serial not null primary key,
 
+    contact_id integer not null,
     area_id integer not null,
     category text not null default 'Other',
     email text not null,
-- 
cgit v1.2.3


From 329a234c97dc7b8fa8280fee4ddb74a3e77f3a74 Mon Sep 17 00:00:00 2001
From: Matthew Somerville <matthew@fury.ukcod.org.uk>
Date: Mon, 11 Apr 2011 11:03:26 +0100
Subject: Update contacts trigger, fix typo.

---
 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 a44b91ca7..297a13946 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -107,7 +107,7 @@ create table contacts_history (
 create function contacts_updated()
     returns trigger as '
     begin
-        insert into contacts_history (area_id, category, email, editor, whenedited, note, confirmed, deleted) values (new.area_id, new.category, new.email, new.editor, new.whenedited, new.note, new.confirmed, new.deleted);
+        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);
         return new;
     end;
 ' language 'plpgsql';
-- 
cgit v1.2.3


From 502409482509c8ba6f085881553c894954ad2c65 Mon Sep 17 00:00:00 2001
From: Edmund von der Burg <evdb@mysociety.org>
Date: Fri, 15 Apr 2011 12:12:29 +0100
Subject: Add PK to 'abuse' table

---
 db/schema.sql | 3 +--
 1 file changed, 1 insertion(+), 2 deletions(-)

(limited to 'db/schema.sql')

diff --git a/db/schema.sql b/db/schema.sql
index 297a13946..86a9598c4 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -358,9 +358,8 @@ create table flickr_imported (
 create unique index flickr_imported_id_idx on flickr_imported(id);
 
 create table abuse (
-    email text not null
+    email text primary key check( lower(email) = email )
 );
-create unique index abuse_email_idx on abuse(lower(email));
 
 create table textmystreet (
     name text not null,
-- 
cgit v1.2.3


From 983ddaff992ba81091360e718c107ab66299cd2d Mon Sep 17 00:00:00 2001
From: Matthew Somerville <matthew@mysociety.org>
Date: Fri, 20 May 2011 12:19:21 +0100
Subject: Add DB::Questionnaire (and update schema.sql with schema changes).

---
 db/schema.sql | 10 ++++++----
 1 file changed, 6 insertions(+), 4 deletions(-)

(limited to 'db/schema.sql')

diff --git a/db/schema.sql b/db/schema.sql
index 86a9598c4..9c5b3d8fd 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -262,8 +262,9 @@ create function problem_find_nearby(double precision, double precision, double p
 create table comment (
     id serial not null primary key,
     problem_id integer not null references problem(id),
+    user_id int references users(id) not null,
+    anonymous bool not null,
     name text, -- null means anonymous
-    email text not null,
     website text,
     created timestamp not null default ms_current_timestamp(),
     confirmed timestamp,
@@ -283,6 +284,7 @@ create table comment (
     -- and should be highlighted in the display?
 );
 
+create index comment_user_id_idx on comment(user_id);
 create index comment_problem_id_idx on comment(problem_id);
 create index comment_problem_id_created_idx on comment(problem_id, created);
 
@@ -318,7 +320,7 @@ create table alert (
     alert_type text not null references alert_type(ref),
     parameter text, -- e.g. Problem ID for new updates, Longitude for local problem alerts
     parameter2 text, -- e.g. Latitude for local problem alerts
-    email text not null,
+    user_id int references users(id) not null,
     confirmed integer not null default 0,
     lang text not null default 'en-gb',
     cobrand text not null default '' check (cobrand ~* '^[a-z0-9]*$'), 
@@ -326,11 +328,11 @@ create table alert (
     whensubscribed timestamp not null default ms_current_timestamp(),
     whendisabled timestamp default null
 );
-create index alert_email_idx on alert(email);
+create index alert_user_id_idx on alert ( user_id );
 create index alert_alert_type_confirmed_whendisabled_idx on alert(alert_type, confirmed, whendisabled);
 create index alert_whendisabled_cobrand_idx on alert(whendisabled, cobrand);
 create index alert_whensubscribed_confirmed_cobrand_idx on alert(whensubscribed, confirmed, cobrand);
--- Possible indexes - unique (alert_type,email,parameter)
+-- Possible indexes - unique (alert_type,user_id,parameter)
 
 create table alert_sent (
     alert_id integer not null references alert(id),
-- 
cgit v1.2.3

