Add back contacts changes

2 files changed, 20 insertions, 1 deletions

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,
diff --git a/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql b/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql
index 6d67a259e..aadaa0993 100644
--- a/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql
+++ b/db/schema_0001-add_sessions_and_users_and_dbic_fields.sql
@@ -18,5 +18,22 @@ create table users (
     password        text    not null default ''
 );
 
--- rollback;
+--- add PK to contacts table
+ALTER TABLE contacts
+    ADD COLUMN id SERIAL PRIMARY KEY;
+
+AlTER TABLE contacts_history
+    ADD COLUMN contact_id integer;
+
+update contacts_history
+    set contact_id = (
+        select id
+        from contacts
+        where contacts_history.category = contacts.category
+          and contacts_history.area_id = contacts.area_id
+    );
+
+AlTER TABLE contacts_history
+    alter COLUMN contact_id SET NOT NULL;
+
 commit;