aboutsummaryrefslogtreecommitdiffstats
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/schema_0027-rename_open311conf.sql12
-rw-r--r--db/schema_0028-add-bodies.sql87
-rw-r--r--db/schema_0028-rename_from_council.sql5
-rw-r--r--db/schema_0029-rename_contacts_area_id.sql31
-rw-r--r--db/schema_0030-add-body-name.sql7
-rw-r--r--db/schema_0031-rename-problem-council-column.sql5
-rw-r--r--db/schema_0032-add-from_body-reference.sql6
-rw-r--r--db/schema_0033-body-areas-many-many.sql15
-rw-r--r--db/schema_0034-add-body-parent.sql5
-rw-r--r--db/schema_0035-update_alert_types_for_bodies.sql19
10 files changed, 87 insertions, 105 deletions
diff --git a/db/schema_0027-rename_open311conf.sql b/db/schema_0027-rename_open311conf.sql
deleted file mode 100644
index cef020648..000000000
--- a/db/schema_0027-rename_open311conf.sql
+++ /dev/null
@@ -1,12 +0,0 @@
-begin;
-
-ALTER TABLE open311conf RENAME TO body;
-ALTER INDEX open311conf_pkey RENAME TO body_pkey;
-ALTER INDEX open311conf_area_id_key RENAME TO body_area_id_key;
-ALTER TABLE body ALTER COLUMN endpoint DROP NOT NULL;
-ALTER TABLE body DROP CONSTRAINT open311conf_comment_user_id_fkey;
-ALTER TABLE body ADD CONSTRAINT body_comment_user_id_fkey
- FOREIGN KEY (comment_user_id) REFERENCES users(id);
-ALTER SEQUENCE open311conf_id_seq RENAME TO body_id_seq;
-
-commit;
diff --git a/db/schema_0028-add-bodies.sql b/db/schema_0028-add-bodies.sql
new file mode 100644
index 000000000..b56738cf3
--- /dev/null
+++ b/db/schema_0028-add-bodies.sql
@@ -0,0 +1,87 @@
+BEGIN;
+
+-- Remove unused function
+drop function ms_current_date();
+
+-- Rename open311conf to create the new body table
+ALTER TABLE open311conf RENAME TO body;
+ALTER INDEX open311conf_pkey RENAME TO body_pkey;
+ALTER INDEX open311conf_area_id_key RENAME TO body_area_id_key;
+ALTER TABLE body ALTER COLUMN endpoint DROP NOT NULL;
+ALTER TABLE body DROP CONSTRAINT open311conf_comment_user_id_fkey;
+ALTER TABLE body ADD CONSTRAINT body_comment_user_id_fkey
+ FOREIGN KEY (comment_user_id) REFERENCES users(id);
+ALTER SEQUENCE open311conf_id_seq RENAME TO body_id_seq;
+
+-- Update contacts column to be better named
+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;
+
+-- Data migration from contacts
+UPDATE body SET id = area_id;
+INSERT INTO body (id, area_id)
+ SELECT DISTINCT body_id, body_id FROM contacts WHERE body_id not in (SELECT id FROM body);
+SELECT setval('body_id_seq', (SELECT MAX(id) FROM body) );
+
+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();
+
+-- Give bodies a name
+ALTER TABLE body ADD name text;
+UPDATE body SET name='';
+ALTER table body ALTER COLUMN name SET NOT NULL;
+
+-- Update users column to be better named
+ALTER TABLE users RENAME COLUMN from_council TO from_body;
+ALTER TABLE users ADD CONSTRAINT users_from_body_fkey
+ FOREIGN KEY (from_body) REFERENCES body(id);
+
+-- Rename problem's council column
+ALTER TABLE problem RENAME COLUMN council TO bodies_str;
+
+-- Update alert types that used 'council' column
+UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in
+(''confirmed'', ''investigating'', ''planned'', ''in progress'',
+ ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+ ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+ ''internal referral'' ) AND
+(bodies_str like ''%''||?||''%'' or bodies_str is null) and
+areas like ''%,''||?||'',%''' WHERE ref = 'council_problems';
+UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in
+(''confirmed'', ''investigating'', ''planned'', ''in progress'',
+ ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
+ ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
+ ''internal referral'' ) AND
+(bodies_str like ''%''||?||''%'' or bodies_str is null) and
+areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems';
+
+-- Move to many-many relationship between bodies and areas
+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);
+INSERT INTO body_areas (body_id, area_id)
+ SELECT id, area_id FROM body;
+ALTER TABLE body DROP COLUMN area_id;
+
+-- Allow bodies to have a hierarchy
+ALTER TABLE body ADD parent INTEGER REFERENCES body(id);
+
+COMMIT;
diff --git a/db/schema_0028-rename_from_council.sql b/db/schema_0028-rename_from_council.sql
deleted file mode 100644
index 027a257a4..000000000
--- a/db/schema_0028-rename_from_council.sql
+++ /dev/null
@@ -1,5 +0,0 @@
-begin;
-
-ALTER TABLE users RENAME COLUMN from_council TO from_body;
-
-commit;
diff --git a/db/schema_0029-rename_contacts_area_id.sql b/db/schema_0029-rename_contacts_area_id.sql
deleted file mode 100644
index 861ace5c5..000000000
--- a/db/schema_0029-rename_contacts_area_id.sql
+++ /dev/null
@@ -1,31 +0,0 @@
-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;
-
--- Data migration
-UPDATE body SET id = area_id;
-INSERT INTO body (id, area_id)
- SELECT DISTINCT body_id, body_id FROM contacts WHERE body_id not in (SELECT id FROM body);
-SELECT setval('body_id_seq', (SELECT MAX(id) FROM body) );
-
-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;
diff --git a/db/schema_0030-add-body-name.sql b/db/schema_0030-add-body-name.sql
deleted file mode 100644
index 30ac26e98..000000000
--- a/db/schema_0030-add-body-name.sql
+++ /dev/null
@@ -1,7 +0,0 @@
-begin;
-
-ALTER TABLE body ADD name text;
-UPDATE body SET name='';
-ALTER table body ALTER COLUMN name SET NOT NULL;
-
-commit;
diff --git a/db/schema_0031-rename-problem-council-column.sql b/db/schema_0031-rename-problem-council-column.sql
deleted file mode 100644
index 87542a192..000000000
--- a/db/schema_0031-rename-problem-council-column.sql
+++ /dev/null
@@ -1,5 +0,0 @@
-begin;
-
-ALTER TABLE problem RENAME COLUMN council TO bodies_str;
-
-commit;
diff --git a/db/schema_0032-add-from_body-reference.sql b/db/schema_0032-add-from_body-reference.sql
deleted file mode 100644
index 4fa526963..000000000
--- a/db/schema_0032-add-from_body-reference.sql
+++ /dev/null
@@ -1,6 +0,0 @@
-begin;
-
-ALTER TABLE users ADD CONSTRAINT users_from_body_fkey
- FOREIGN KEY (from_body) REFERENCES body(id);
-
-commit;
diff --git a/db/schema_0033-body-areas-many-many.sql b/db/schema_0033-body-areas-many-many.sql
deleted file mode 100644
index e03f3fa44..000000000
--- a/db/schema_0033-body-areas-many-many.sql
+++ /dev/null
@@ -1,15 +0,0 @@
-begin;
-
-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);
-
-INSERT INTO body_areas (body_id, area_id)
- SELECT id, area_id FROM body;
-
-ALTER TABLE body DROP COLUMN area_id;
-
-commit;
-
diff --git a/db/schema_0034-add-body-parent.sql b/db/schema_0034-add-body-parent.sql
deleted file mode 100644
index 9360fbcb3..000000000
--- a/db/schema_0034-add-body-parent.sql
+++ /dev/null
@@ -1,5 +0,0 @@
-begin;
-
-ALTER TABLE body ADD parent INTEGER REFERENCES body(id);
-
-commit;
diff --git a/db/schema_0035-update_alert_types_for_bodies.sql b/db/schema_0035-update_alert_types_for_bodies.sql
deleted file mode 100644
index d7f1be475..000000000
--- a/db/schema_0035-update_alert_types_for_bodies.sql
+++ /dev/null
@@ -1,19 +0,0 @@
-BEGIN;
-
- UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in
- (''confirmed'', ''investigating'', ''planned'', ''in progress'',
- ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
- ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
- ''internal referral'' ) AND
- (bodies_str like ''%''||?||''%'' or bodies_str is null) and
- areas like ''%,''||?||'',%''' WHERE ref = 'council_problems';
-
- UPDATE alert_type set item_where = 'problem.non_public = ''f'' and problem.state in
- (''confirmed'', ''investigating'', ''planned'', ''in progress'',
- ''fixed'', ''fixed - council'', ''fixed - user'', ''closed'',
- ''action scheduled'', ''not responsible'', ''duplicate'', ''unable to fix'',
- ''internal referral'' ) AND
- (bodies_str like ''%''||?||''%'' or bodies_str is null) and
- areas like ''%,''||?||'',%''' WHERE ref = 'ward_problems';
-
-COMMIT;
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-26 17:32:17 +0000