Rename area_id on Contacts table to body_id.

Add foreign key constraint from contacts to body.

2 files changed, 30 insertions, 19 deletions

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';
diff --git a/db/schema_0029-rename_contacts_area_id.sql b/db/schema_0029-rename_contacts_area_id.sql
new file mode 100644
index 000000000..e28f73802
--- /dev/null
+++ b/db/schema_0029-rename_contacts_area_id.sql
@@ -0,0 +1,25 @@
+begin;
+
+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;
+
+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();
+
+commit;