From 5edb9d1bb36f6da61ab39bafdc31e8221acd2194 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 19 Jul 2022 10:19:29 -0400 Subject: [PATCH] LP1910546 Prevent Copy Location Delete With Active Copies Raise a database exception when any attempt is made to marke a copy location as deleted when the location contains non-deleted copies. To Test: 1. Navigate to /eg2/staff/admin/local/asset/copy_location 2. Attempt to delete a copy location that is known to contain non-deleted copies. 3. Confirm an error message is displayed and the location is not marked as deleted. Signed-off-by: Bill Erickson Signed-off-by: Tiffany Little Signed-off-by: Michele Morgan --- Open-ILS/src/sql/Pg/800.fkeys.sql | 17 ++++++++++++ .../XXXX.schema.reject-active-acpl-delete.sql | 30 ++++++++++++++++++++++ 2 files changed, 47 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reject-active-acpl-delete.sql diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 6ddf4cc326..927eeff14d 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -28,8 +28,25 @@ CREATE RULE protect_bib_rec_delete AS CREATE RULE protect_bre_id_neg1 AS ON UPDATE TO biblio.record_entry WHERE OLD.id = -1 DO INSTEAD NOTHING; + +-- Kill any transaction that tries to mark a copy location as +-- deleted if the location contains any non-deleted copies. +CREATE OR REPLACE FUNCTION asset.check_delete_copy_location(acpl_id INTEGER) + RETURNS VOID AS $FUNK$ +BEGIN + PERFORM TRUE FROM asset.copy WHERE location = acpl_id AND NOT deleted LIMIT 1; + + IF FOUND THEN + RAISE EXCEPTION + 'Copy location % contains active copies and cannot be deleted', acpl_id; + END IF; +END; +$FUNK$ LANGUAGE plpgsql; + + CREATE RULE protect_copy_location_delete AS ON DELETE TO asset.copy_location DO INSTEAD ( + SELECT asset.check_delete_copy_location(OLD.id); -- exception on error UPDATE asset.copy_location SET deleted = TRUE WHERE OLD.id = asset.copy_location.id; UPDATE acq.lineitem_detail SET location = NULL WHERE location = OLD.id; DELETE FROM asset.copy_location_order WHERE location = OLD.id; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reject-active-acpl-delete.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reject-active-acpl-delete.sql new file mode 100644 index 0000000000..b767ba83fe --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.reject-active-acpl-delete.sql @@ -0,0 +1,30 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version); + +CREATE OR REPLACE FUNCTION asset.check_delete_copy_location(acpl_id INTEGER) + RETURNS VOID AS $FUNK$ +BEGIN + PERFORM TRUE FROM asset.copy WHERE location = acpl_id AND NOT deleted LIMIT 1; + + IF FOUND THEN + RAISE EXCEPTION + 'Copy location % contains active copies and cannot be deleted', acpl_id; + END IF; +END; +$FUNK$ LANGUAGE plpgsql; + +DROP RULE protect_copy_location_delete ON asset.copy_location; + +CREATE RULE protect_copy_location_delete AS + ON DELETE TO asset.copy_location DO INSTEAD ( + SELECT asset.check_delete_copy_location(OLD.id); + UPDATE asset.copy_location SET deleted = TRUE WHERE OLD.id = asset.copy_location.id; + UPDATE acq.lineitem_detail SET location = NULL WHERE location = OLD.id; + DELETE FROM asset.copy_location_order WHERE location = OLD.id; + DELETE FROM asset.copy_location_group_map WHERE location = OLD.id; + DELETE FROM config.circ_limit_set_copy_loc_map WHERE copy_loc = OLD.id; + ); + +COMMIT; + -- 2.11.0