From 6214235b7ee8d041d1a4f06fd562281b30663db9 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Wed, 15 Mar 2017 15:39:55 -0400 Subject: [PATCH] LP#1672775 Action/Trigger retention interval SQL/IDL Adds a new 'retention_interval' column for Action/Trigger Event Definitions and an action_trigger.purge_events() function for deleting events that have exceeded their configured retention time, including any outputs linked to those events. Signed-off-by: Bill Erickson Signed-off-by: Galen Charlton --- Open-ILS/examples/fm_IDL.xml | 1 + .../src/sql/Pg/400.schema.action_trigger.sql | 88 ++++++++++++++- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 52 ++++----- ...XXX.schema.action-trigger-purge-events.sql | 105 ++++++++++++++++++ .../YYYY.data.action-trigger-purge-events.sql | 22 ++++ 5 files changed, 234 insertions(+), 34 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.action-trigger-purge-events.sql create mode 100644 Open-ILS/src/sql/Pg/upgrade/YYYY.data.action-trigger-purge-events.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 86a9f3b645..b1d287eaea 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1406,6 +1406,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + diff --git a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql index 81fce60d45..d9f292e4ae 100644 --- a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql +++ b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql @@ -49,8 +49,8 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('pen INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_CHECKOUT_COUNT','ausp','Patron has exceeded allowed checkout count',TRUE); INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_COLLECTIONS_WARNING','ausp','Patron has exceeded maximum fine amount for collections department warning',TRUE); INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('acqpo.activated','acqpo','Purchase order was activated',FALSE); -INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.html','acqpo','Formats a Purchase Order as an HTML document',TRUE); -INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('format.po.pdf','acqpo','Formats a Purchase Order as a PDF document',TRUE); +INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('format.po.html','acqpo','Formats a Purchase Order as an HTML document'); +INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('format.po.pdf','acqpo','Formats a Purchase Order as a PDF document'); INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('damaged','acp','Item marked damaged'); INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout.damaged','circ','A circulating item is marked damaged and the patron is fined'); INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('renewal','circ','Item renewed to user'); @@ -195,11 +195,47 @@ CREATE TABLE action_trigger.event_definition ( message_usr_path TEXT, message_library_path TEXT, message_title TEXT, + retention_interval INTERVAL, CONSTRAINT ev_def_owner_hook_val_react_clean_delay_once UNIQUE (owner, hook, validator, reactor, delay, delay_field), CONSTRAINT ev_def_name_owner_once UNIQUE (owner, name) ); +CREATE OR REPLACE FUNCTION action_trigger.check_valid_retention_interval() + RETURNS TRIGGER AS $_$ +BEGIN + /* + * 1. Retention intervals are always allowed on active hooks. + * 2. On passive hooks, retention intervals are only allowed + * when the event definition has a max_delay value and the + * retention_interval value is greater than the difference + * beteween the delay and max_delay values. + */ + PERFORM TRUE FROM action_trigger.hook + WHERE key = NEW.hook AND NOT passive; + + IF FOUND THEN + RETURN NEW; + END IF; + + IF NEW.max_delay IS NOT NULL THEN + IF EXTRACT(EPOCH FROM NEW.retention_interval) > + ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN + RETURN NEW; -- all good + ELSE + RAISE EXCEPTION 'retention_interval is too short'; + END IF; + ELSE + RAISE EXCEPTION 'retention_interval requires max_delay'; + END IF; +END; +$_$ LANGUAGE PLPGSQL; + +CREATE TRIGGER is_valid_retention_interval + BEFORE INSERT OR UPDATE ON action_trigger.event_definition + FOR EACH ROW WHEN (NEW.retention_interval IS NOT NULL) + EXECUTE PROCEDURE action_trigger.check_valid_retention_interval(); + CREATE TABLE action_trigger.environment ( id SERIAL PRIMARY KEY, event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED, @@ -246,5 +282,53 @@ CREATE TABLE action_trigger.event_params ( CONSTRAINT event_params_event_def_param_once UNIQUE (event_def,param) ); +CREATE OR REPLACE FUNCTION action_trigger.purge_events() RETURNS VOID AS $_$ +/** + * Deleting expired events without simultaneously deleting their outputs + * creates orphaned outputs. Deleting their outputs and all of the events + * linking back to them, plus any outputs those events link to is messy and + * inefficient. It's simpler to handle them in 2 sweeping steps. + * + * 1. Delete expired events. + * 2. Delete orphaned event outputs. + * + * This has the added benefit of removing outputs that may have been + * orphaned by some other process. Such outputs are not usuable by + * the system. + * + * This does not guarantee that all events within an event group are + * purged at the same time. In such cases, the remaining events will + * be purged with the next instance of the purge (or soon thereafter). + * This is another nod toward efficiency over completeness of old + * data that's circling the bit bucket anyway. + */ +BEGIN + + DELETE FROM action_trigger.event WHERE id IN ( + SELECT evt.id + FROM action_trigger.event evt + JOIN action_trigger.event_definition def ON (def.id = evt.event_def) + WHERE def.retention_interval IS NOT NULL + AND evt.state <> 'pending' + AND evt.update_time < (NOW() - def.retention_interval) + ); + + WITH linked_outputs AS ( + SELECT templates.id AS id FROM ( + SELECT DISTINCT(template_output) AS id + FROM action_trigger.event WHERE template_output IS NOT NULL + UNION + SELECT DISTINCT(error_output) AS id + FROM action_trigger.event WHERE error_output IS NOT NULL + UNION + SELECT DISTINCT(async_output) AS id + FROM action_trigger.event WHERE async_output IS NOT NULL + ) templates + ) DELETE FROM action_trigger.event_output + WHERE id NOT IN (SELECT id FROM linked_outputs); + +END; +$_$ LANGUAGE PLPGSQL; + COMMIT; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index e3c34b4415..9e0517ffa9 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -9441,7 +9441,7 @@ INSERT INTO action_trigger.event_params (event_def, param, value) -- trigger data related to acq user requests -INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( +INSERT INTO action_trigger.hook (key,core_type,description) VALUES ( 'aur.ordered', 'aur', oils_i18n_gettext( @@ -9449,8 +9449,7 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'A patron acquisition request has been marked On-Order.', 'ath', 'description' - ), - TRUE + ) ), ( 'aur.received', 'aur', @@ -9459,8 +9458,7 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'A patron acquisition request has been marked Received.', 'ath', 'description' - ), - TRUE + ) ), ( 'aur.cancelled', 'aur', @@ -9469,8 +9467,7 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'A patron acquisition request has been marked Cancelled.', 'ath', 'description' - ), - TRUE + ) ), ( 'aur.created', 'aur', @@ -9479,8 +9476,7 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'A patron has made an acquisitions request.', 'ath', 'description' - ), - TRUE + ) ), ( 'aur.rejected', 'aur', @@ -9489,8 +9485,7 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'A patron acquisition request has been rejected.', 'ath', 'description' - ), - TRUE + ) ) ; @@ -9764,12 +9759,11 @@ INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 20, 'usr.home_ou' ); -INSERT INTO action_trigger.hook (key, core_type, description, passive) +INSERT INTO action_trigger.hook (key, core_type, description) VALUES ( 'format.acqcle.html', 'acqcle', - 'Formats claim events into a voucher', - TRUE + 'Formats claim events into a voucher' ); INSERT INTO action_trigger.event_definition ( @@ -9824,12 +9818,11 @@ INSERT INTO action_trigger.environment (event_def, path) VALUES ; -INSERT INTO action_trigger.hook (key, core_type, description, passive) +INSERT INTO action_trigger.hook (key, core_type, description) VALUES ( 'format.acqinv.html', 'acqinv', - 'Formats invoices into a voucher', - TRUE + 'Formats invoices into a voucher' ); INSERT INTO action_trigger.event_definition ( @@ -10318,12 +10311,11 @@ INSERT INTO action_trigger.reactor (module,description) VALUES -- self-check checkout receipt -INSERT INTO action_trigger.hook (key, core_type, description, passive) +INSERT INTO action_trigger.hook (key, core_type, description) VALUES ( 'format.selfcheck.checkout', 'circ', - 'Formats circ objects for self-checkout receipt', - TRUE + 'Formats circ objects for self-checkout receipt' ); INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) @@ -10424,12 +10416,11 @@ INSERT INTO action_trigger.environment ( event_def, path) VALUES -- items out selfcheck receipt -INSERT INTO action_trigger.hook (key, core_type, description, passive) +INSERT INTO action_trigger.hook (key, core_type, description) VALUES ( 'format.selfcheck.items_out', 'circ', - 'Formats items out for self-checkout receipt', - TRUE + 'Formats items out for self-checkout receipt' ); INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) @@ -10472,12 +10463,11 @@ INSERT INTO action_trigger.environment ( event_def, path) VALUES ( 11, 'circ_lib.hours_of_operation'), ( 11, 'usr'); -INSERT INTO action_trigger.hook (key, core_type, description, passive) +INSERT INTO action_trigger.hook (key, core_type, description) VALUES ( 'format.selfcheck.holds', 'ahr', - 'Formats holds for self-checkout receipt', - TRUE + 'Formats holds for self-checkout receipt' ); INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, group_field, granularity, template ) @@ -10533,12 +10523,11 @@ INSERT INTO action_trigger.environment ( event_def, path) VALUES -- fines receipt -INSERT INTO action_trigger.hook (key, core_type, description, passive) +INSERT INTO action_trigger.hook (key, core_type, description) VALUES ( 'format.selfcheck.fines', 'au', - 'Formats fines for self-checkout receipt', - TRUE + 'Formats fines for self-checkout receipt' ); INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, granularity, template ) @@ -10583,12 +10572,11 @@ $$ $$ ); -INSERT INTO action_trigger.hook (key, core_type, description, passive) +INSERT INTO action_trigger.hook (key, core_type, description) VALUES ( 'format.acqli.html', 'jub', - 'Formats lineitem worksheet for titles received', - TRUE + 'Formats lineitem worksheet for titles received' ); INSERT INTO action_trigger.event_definition (id, active, owner, name, hook, validator, reactor, granularity, template) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.action-trigger-purge-events.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.action-trigger-purge-events.sql new file mode 100644 index 0000000000..30f4dd6cac --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.action-trigger-purge-events.sql @@ -0,0 +1,105 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('1XXX', :eg_version); + +ALTER TABLE action_trigger.event_definition + ADD COLUMN retention_interval INTERVAL; + +CREATE OR REPLACE FUNCTION action_trigger.check_valid_retention_interval() + RETURNS TRIGGER AS $_$ +BEGIN + + /* + * 1. Retention intervals are alwyas allowed on active hooks. + * 2. On passive hooks, retention intervals are only allowed + * when the event definition has a max_delay value and the + * retention_interval value is greater than the difference + * beteween the delay and max_delay values. + */ + PERFORM TRUE FROM action_trigger.hook + WHERE key = NEW.hook AND NOT passive; + + IF FOUND THEN + RETURN NEW; + END IF; + + IF NEW.max_delay IS NOT NULL THEN + IF EXTRACT(EPOCH FROM NEW.retention_interval) > + ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN + RETURN NEW; -- all good + ELSE + RAISE EXCEPTION 'retention_interval is too short'; + END IF; + ELSE + RAISE EXCEPTION 'retention_interval requires max_delay'; + END IF; +END; +$_$ LANGUAGE PLPGSQL; + +CREATE TRIGGER is_valid_retention_interval + BEFORE INSERT OR UPDATE ON action_trigger.event_definition + FOR EACH ROW WHEN (NEW.retention_interval IS NOT NULL) + EXECUTE PROCEDURE action_trigger.check_valid_retention_interval(); + +CREATE OR REPLACE FUNCTION action_trigger.purge_events() RETURNS VOID AS $_$ +/** + * Deleting expired events without simultaneously deleting their outputs + * creates orphaned outputs. Deleting their outputs and all of the events + * linking back to them, plus any outputs those events link to is messy and + * inefficient. It's simpler to handle them in 2 sweeping steps. + * + * 1. Delete expired events. + * 2. Delete orphaned event outputs. + * + * This has the added benefit of removing outputs that may have been + * orphaned by some other process. Such outputs are not usuable by + * the system. + * + * This does not guarantee that all events within an event group are + * purged at the same time. In such cases, the remaining events will + * be purged with the next instance of the purge (or soon thereafter). + * This is another nod toward efficiency over completeness of old + * data that's circling the bit bucket anyway. + */ +BEGIN + + DELETE FROM action_trigger.event WHERE id IN ( + SELECT evt.id + FROM action_trigger.event evt + JOIN action_trigger.event_definition def ON (def.id = evt.event_def) + WHERE def.retention_interval IS NOT NULL + AND evt.state <> 'pending' + AND evt.update_time < (NOW() - def.retention_interval) + ); + + WITH linked_outputs AS ( + SELECT templates.id AS id FROM ( + SELECT DISTINCT(template_output) AS id + FROM action_trigger.event WHERE template_output IS NOT NULL + UNION + SELECT DISTINCT(error_output) AS id + FROM action_trigger.event WHERE error_output IS NOT NULL + UNION + SELECT DISTINCT(async_output) AS id + FROM action_trigger.event WHERE async_output IS NOT NULL + ) templates + ) DELETE FROM action_trigger.event_output + WHERE id NOT IN (SELECT id FROM linked_outputs); + +END; +$_$ LANGUAGE PLPGSQL; + + +/* -- UNDO -- + +BEGIN; +DROP FUNCTION IF EXISTS action_trigger.purge_events(); +DROP TRIGGER IF EXISTS is_valid_retention_interval ON action_trigger.event_definition; +DROP FUNCTION IF EXISTS action_trigger.check_valid_retention_interval(); +ALTER TABLE action_trigger.event_definition DROP COLUMN retention_interval; +COMMIT; + +*/ + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.data.action-trigger-purge-events.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.action-trigger-purge-events.sql new file mode 100644 index 0000000000..1c790529cc --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.action-trigger-purge-events.sql @@ -0,0 +1,22 @@ +BEGIN; + +-- SELECT evergreen.upgrade_deps_block_check('1XXX', :eg_version); + +UPDATE action_trigger.hook SET passive = FALSE WHERE key IN ( + 'format.po.html', + 'format.po.pdf', + 'format.selfcheck.checkout', + 'format.selfcheck.items_out', + 'format.selfcheck.holds', + 'format.selfcheck.fines', + 'format.acqcle.html', + 'format.acqinv.html', + 'format.acqli.html', + 'aur.ordered', + 'aur.received', + 'aur.cancelled', + 'aur.created', + 'aur.rejected' +); + +COMMIT; -- 2.43.2