From 8495bbe0f3e087b5ca7b68198d70e46c59edec76 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Mon, 13 Mar 2023 09:16:48 -0400 Subject: [PATCH] adding a foreign key check for selction_ou on action.hold_request Signed-off-by: Jane Sandberg adding a check in the upgrade script for a non-existent selection_ou and changing it to the request_lib Signed-off-by: Jane Sandberg --- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 +- .../xxxx.schema.function-selection-ou-fkey.sql | 15 +++++++++++++++ 2 files changed, 16 insertions(+), 1 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 69d6b03..7ce47d3 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -472,7 +472,7 @@ CREATE TABLE action.hold_request ( request_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, requestor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, usr INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, - selection_ou INT NOT NULL, + selection_ou INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED, selection_depth INT NOT NULL DEFAULT 0, pickup_lib INT NOT NULL REFERENCES actor.org_unit DEFERRABLE INITIALLY DEFERRED, hold_type TEXT REFERENCES config.hold_type (hold_type) DEFERRABLE INITIALLY DEFERRED, diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql new file mode 100644 index 0000000..2c8d2ee --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.schema.function-selection-ou-fkey.sql @@ -0,0 +1,15 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('xxxx', :eg_version); + +UPDATE action.hold_request +SET selection_ou = request_lib +WHERE id IN ( + SELECT ahr.id FROM action.hold_request ahr + LEFT JOIN actor.org_unit aou ON aou.id = ahr.selection_ou + WHERE aou.id IS NULL +); + +ALTER TABLE action.hold_request ADD CONSTRAINT hold_request_selection_ou_fkey FOREIGN KEY (selection_ou) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; + +COMMIT; -- 1.7.2.5