diff options
| -rw-r--r-- | db/schema.sql | 2 | ||||
| -rw-r--r-- | db/schema_0001-add_sessions_and_users_and_dbic_fields.sql | 19 |
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; |