From 34337bda97bb667dd06b3c90c539e60b58530ebe Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Fri, 1 May 2015 15:33:05 -0400 Subject: [PATCH] LP 1450561: Restore org. unit settings history limit function and trigger The evergreen.limit_oustl function and the limit_log_oustl trigger on config.org_unit_setting_type_log were missed in the upgrade scripts when these were added to the base schema. This commit adds them and a pgtap test to check that they exist after. This commit also alters the body of the limit_oustl function to take into account of the org unit when deleting entries from the log table. This change allows multiple consortium members to have their own five history entries. The original code only kept the last five entries of any given setting across the whole installation. Signed-off-by: Jason Stephenson Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/002.schema.config.sql | 6 ++--- .../src/sql/Pg/t/lp1450561-limit_oustl.pg | 12 +++++++++ .../Pg/upgrade/XXXX.lmit_logs_oust-create.sql | 26 +++++++++++++++++++ 3 files changed, 41 insertions(+), 3 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/t/lp1450561-limit_oustl.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.lmit_logs_oust-create.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 436b66adf8..71d46aee9b 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -960,11 +960,11 @@ in actor.org_unit_setting, allowing for mistakes to be undone. This is NOT meant to be an auditor, but rather an undo/redo. $$; -CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$ +CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$ BEGIN -- Only keeps the most recent five settings changes. - DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN - (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4); + DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN + (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org ORDER BY date_applied DESC LIMIT 4); IF (TG_OP = 'UPDATE') THEN RETURN NEW; diff --git a/Open-ILS/src/sql/Pg/t/lp1450561-limit_oustl.pg b/Open-ILS/src/sql/Pg/t/lp1450561-limit_oustl.pg new file mode 100644 index 0000000000..ed5b78aba0 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/lp1450561-limit_oustl.pg @@ -0,0 +1,12 @@ +BEGIN; + +SELECT plan(2); + +SELECT can('evergreen', ARRAY['limit_oustl'], 'evergreen.limit_oustl function exists'); + +SELECT has_trigger('config', 'org_unit_setting_type_log', 'limit_logs_oust', + 'config.org_unit_setting_type_log limit_logs_oust trigger exists'); + +SELECT * FROM finish(); + +ROLLBACK; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.lmit_logs_oust-create.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.lmit_logs_oust-create.sql new file mode 100644 index 0000000000..e7cfb05a98 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.lmit_logs_oust-create.sql @@ -0,0 +1,26 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$ + BEGIN + -- Only keeps the most recent five settings changes. + DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN + (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org ORDER BY date_applied DESC LIMIT 4); + + IF (TG_OP = 'UPDATE') THEN + RETURN NEW; + ELSIF (TG_OP = 'INSERT') THEN + RETURN NEW; + END IF; + RETURN NULL; + END; +$oustl_limit$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS limit_logs_oust ON config.org_unit_setting_type_log; + +CREATE TRIGGER limit_logs_oust + BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log + FOR EACH ROW EXECUTE PROCEDURE limit_oustl(); + +COMMIT; -- 2.43.2