From 8c7ab3e13e3e5744d95c6ed645d3cc25527e9fee Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Thu, 5 Nov 2015 13:30:51 -0500 Subject: [PATCH] LP#1513554 - Prevent deletion of reserved rows. It was previously possible to delete reserved rows in the acq.cancel_reasons table via the UI. We take a belt and suspenders approach here: 1) Grey out the checkboxes beside cancel reasons with an ID lower than 2000. 2) Create a trigger on the acq.cancel_reason table that prevents deletion of rows with an ID lower than 2000. The trigger executes a new generally available function for use in similar situations in the future. Signed-off-by: Chris Sharp --- Open-ILS/src/sql/Pg/000.functions.general.sql | 9 ++++++++ Open-ILS/src/sql/Pg/200.schema.acq.sql | 5 +++++ ...4_do_not_delete_reserved_cancel_reasons.pg | 13 ++++++++++++ ...XX.schema.no_delete_acq_cancel_reasons.sql | 21 +++++++++++++++++++ .../conify/global/acq/cancel_reason.js | 5 +++++ 5 files changed, 53 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/t/lp1513554_do_not_delete_reserved_cancel_reasons.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no_delete_acq_cancel_reasons.sql diff --git a/Open-ILS/src/sql/Pg/000.functions.general.sql b/Open-ILS/src/sql/Pg/000.functions.general.sql index d62bd70c76..0cfa909c00 100644 --- a/Open-ILS/src/sql/Pg/000.functions.general.sql +++ b/Open-ILS/src/sql/Pg/000.functions.general.sql @@ -76,4 +76,13 @@ $$ LANGUAGE PLPERLU; COMMENT ON FUNCTION evergreen.could_be_serial_holding_code(TEXT) IS 'Return true if parameter is valid JSON representing an array that at minimum doesn''t make MARC::Field balk and only has subfield labels exactly one character long. Otherwise false.'; +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; +END +$protect_reserved$ +LANGUAGE plpgsql; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 3405ad253b..0359ed7ab4 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -358,6 +358,11 @@ CREATE TABLE acq.cancel_reason ( SELECT SETVAL('acq.cancel_reason_id_seq'::TEXT, 2000); +DROP TRIGGER IF EXISTS acq_no_deleted_reserved_cancel_reasons ON acq.cancel_reason; + +CREATE TRIGGER acq_no_deleted_reserved_cancel_reasons BEFORE DELETE ON acq.cancel_reason + FOR EACH ROW EXECUTE PROCEDURE evergreen.protect_reserved_rows_from_delete(2000); + CREATE TABLE acq.purchase_order ( id SERIAL PRIMARY KEY, owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, 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 new file mode 100644 index 0000000000..8042860013 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/lp1513554_do_not_delete_reserved_cancel_reasons.pg @@ -0,0 +1,13 @@ +BEGIN; + +SELECT plan(1); + +SELECT throws_ok( + 'delete from acq.cancel_reason where id = 1', + 'P0001', + 'Cannot delete row with reserved ID 1' +); + +SELECT * FROM finish(); + +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no_delete_acq_cancel_reasons.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no_delete_acq_cancel_reasons.sql new file mode 100644 index 0000000000..54581fcb4a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no_delete_acq_cancel_reasons.sql @@ -0,0 +1,21 @@ +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; +END +$protect_reserved$ +LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS acq_no_deleted_reserved_cancel_reasons ON acq.cancel_reason; + +CREATE TRIGGER acq_no_deleted_reserved_cancel_reasons BEFORE DELETE ON acq.cancel_reason + FOR EACH ROW EXECUTE PROCEDURE evergreen.protect_reserved_rows_from_delete(2000); + +ALTER TABLE acq.cancel_reason ENABLE TRIGGER acq_no_deleted_reserved_cancel_reasons; + +COMMIT; diff --git a/Open-ILS/web/js/ui/default/conify/global/acq/cancel_reason.js b/Open-ILS/web/js/ui/default/conify/global/acq/cancel_reason.js index 6cd329f7e3..86d8c61092 100644 --- a/Open-ILS/web/js/ui/default/conify/global/acq/cancel_reason.js +++ b/Open-ILS/web/js/ui/default/conify/global/acq/cancel_reason.js @@ -19,6 +19,11 @@ function setup() { ); }; + crGrid.disableSelectorForRow = function(rowIdx) { + var item = crGrid.getItem(rowIdx); + return (crGrid.store.getValue(item, 'id') < 2000); + } + new openils.User().buildPermOrgSelector( 'ADMIN_ACQ_CANCEL_CAUSE', contextOrgSelector, null, connect); } -- 2.43.2