From 9f05aadd29c7f66344c146a91dfffabca381d131 Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Mon, 8 Feb 2016 13:00:06 -0500 Subject: [PATCH] LP#1533331 - Fix evergreen.protect_reserved_rows_from_delete() trigger function The fix for bug 1513554 included creating a general trigger function to prevent the deletion of reserved table rows with an ID lower than a specified threshold. Testing for that seemed to pass muster, but using the function in practice revealed a failure to delete deletable rows either. This adds an explicit RETURN to the function, which fixes the issue. Signed-off-by: Chris Sharp Signed-off-by: Jason Stephenson --- Open-ILS/src/sql/Pg/000.functions.general.sql | 1 + ...13554_do_not_delete_reserved_cancel_reasons.pg | 12 +++++++++++- ...hema.fix_protect_reserved_rows_from_delete.sql | 15 +++++++++++++++ 3 files changed, 27 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_protect_reserved_rows_from_delete.sql diff --git a/Open-ILS/src/sql/Pg/000.functions.general.sql b/Open-ILS/src/sql/Pg/000.functions.general.sql index 0cfa909c00..ad6182e0f5 100644 --- a/Open-ILS/src/sql/Pg/000.functions.general.sql +++ b/Open-ILS/src/sql/Pg/000.functions.general.sql @@ -81,6 +81,7 @@ BEGIN IF OLD.id < TG_ARGV[0]::INT THEN RAISE EXCEPTION 'Cannot delete row with reserved ID %', OLD.id; END IF; +RETURN OLD; END $protect_reserved$ LANGUAGE plpgsql; diff --git a/Open-ILS/src/sql/Pg/t/lp1513554_do_not_delete_reserved_cancel_reasons.pg b/Open-ILS/src/sql/Pg/t/lp1513554_do_not_delete_reserved_cancel_reasons.pg index 8042860013..7dedeb243a 100644 --- a/Open-ILS/src/sql/Pg/t/lp1513554_do_not_delete_reserved_cancel_reasons.pg +++ b/Open-ILS/src/sql/Pg/t/lp1513554_do_not_delete_reserved_cancel_reasons.pg @@ -1,6 +1,6 @@ BEGIN; -SELECT plan(1); +SELECT plan(3); SELECT throws_ok( 'delete from acq.cancel_reason where id = 1', @@ -8,6 +8,16 @@ SELECT throws_ok( 'Cannot delete row with reserved ID 1' ); +SELECT lives_ok( + 'insert into acq.cancel_reason (id, org_unit, label, description) values (3001, 1, ''Test Reason'', ''Test Cancel Reason'')', + 'Creating test cancel reason' +); + +SELECT lives_ok( + 'delete from acq.cancel_reason where id = 3001', + 'Testing delete of non-reserved cancel reason' +); + SELECT * FROM finish(); ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_protect_reserved_rows_from_delete.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_protect_reserved_rows_from_delete.sql new file mode 100644 index 0000000000..6a527a9016 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix_protect_reserved_rows_from_delete.sql @@ -0,0 +1,15 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.protect_reserved_rows_from_delete() RETURNS trigger AS $protect_reserved$ +BEGIN +IF OLD.id < TG_ARGV[0]::INT THEN + RAISE EXCEPTION 'Cannot delete row with reserved ID %', OLD.id; +END IF; +RETURN OLD; +END +$protect_reserved$ +LANGUAGE plpgsql; + +COMMIT; -- 2.43.2