From 749fa835345882b0865755c848c5f80e756daa69 Mon Sep 17 00:00:00 2001 From: gmc Date: Fri, 3 Sep 2010 16:37:15 +0000 Subject: [PATCH] allow updates of permission.perm_list.id to cascade Doing this to make it possible to reshuffle permission IDs for upgrades to 2.0.x Signed-off-by: Galen Charlton git-svn-id: svn://svn.open-ils.org/ILS/trunk@17474 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/006.schema.permissions.sql | 6 +++--- Open-ILS/src/sql/Pg/800.fkeys.sql | 4 ++-- .../sql/Pg/upgrade/0385.schema.perm_list_fkeys.sql | 20 ++++++++++++++++++++ 4 files changed, 26 insertions(+), 6 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0385.schema.perm_list_fkeys.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 327ee54bd..c30c7d3cc 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0384'); -- berick +INSERT INTO config.upgrade_log (version) VALUES ('0385'); -- gmc CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index 7d2c0c342..27cf87bed 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -51,7 +51,7 @@ CREATE TABLE permission.grp_penalty_threshold ( CREATE TABLE permission.grp_perm_map ( id SERIAL PRIMARY KEY, grp INT NOT NULL REFERENCES permission.grp_tree (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, depth INT NOT NULL, grantable BOOL NOT NULL DEFAULT FALSE, CONSTRAINT perm_grp_once UNIQUE (grp,perm) @@ -60,7 +60,7 @@ CREATE TABLE permission.grp_perm_map ( CREATE TABLE permission.usr_perm_map ( id SERIAL PRIMARY KEY, usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, depth INT NOT NULL, grantable BOOL NOT NULL DEFAULT FALSE, CONSTRAINT perm_usr_once UNIQUE (usr,perm) @@ -69,7 +69,7 @@ CREATE TABLE permission.usr_perm_map ( CREATE TABLE permission.usr_object_perm_map ( id SERIAL PRIMARY KEY, usr INT NOT NULL REFERENCES actor.usr (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - perm INT NOT NULL REFERENCES permission.perm_list (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + perm INT NOT NULL REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, object_type TEXT NOT NULL, object_id TEXT NOT NULL, grantable BOOL NOT NULL DEFAULT FALSE, diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index afd2d429b..656ed8591 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -114,7 +114,7 @@ ALTER TABLE config.billing_type ADD CONSTRAINT config_billing_type_owner_fkey FO ALTER TABLE config.remote_account ADD CONSTRAINT config_remote_account_owner_fkey FOREIGN KEY (owner) REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT view_perm_fkey FOREIGN KEY (view_perm) REFERENCES permission.perm_list (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; -ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT update_perm_fkey FOREIGN KEY (update_perm) REFERENCES permission.perm_list (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT view_perm_fkey FOREIGN KEY (view_perm) REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT update_perm_fkey FOREIGN KEY (update_perm) REFERENCES permission.perm_list (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED; COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/0385.schema.perm_list_fkeys.sql b/Open-ILS/src/sql/Pg/upgrade/0385.schema.perm_list_fkeys.sql new file mode 100644 index 000000000..8dd242ad6 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0385.schema.perm_list_fkeys.sql @@ -0,0 +1,20 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0385'); --gmc + +ALTER TABLE permission.grp_perm_map DROP CONSTRAINT grp_perm_map_perm_fkey; +ALTER TABLE permission.grp_perm_map ADD CONSTRAINT grp_perm_map_perm_fkey FOREIGN KEY (perm) + REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE permission.usr_perm_map DROP CONSTRAINT usr_perm_map_perm_fkey; +ALTER TABLE permission.usr_perm_map ADD CONSTRAINT usr_perm_map_perm_fkey FOREIGN KEY (perm) + REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE permission.usr_object_perm_map DROP CONSTRAINT usr_object_perm_map_perm_fkey; +ALTER TABLE permission.usr_object_perm_map ADD CONSTRAINT usr_object_perm_map_perm_fkey FOREIGN KEY (perm) + REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; + +ALTER TABLE config.org_unit_setting_type DROP CONSTRAINT view_perm_fkey; +ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT view_perm_fkey FOREIGN KEY (view_perm) REFERENCES permission.perm_list (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE config.org_unit_setting_type DROP CONSTRAINT update_perm_fkey; +ALTER TABLE config.org_unit_setting_type ADD CONSTRAINT update_perm_fkey FOREIGN KEY (update_perm) REFERENCES permission.perm_list (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED; + +COMMIT; -- 2.11.0