From b6aff50955ab3971b2c072311f42ee248b0f23c6 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 21 Mar 2017 15:33:53 -0400 Subject: [PATCH] LP1672775 Action/Trigger retention interval SQL WIP Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/400.schema.action_trigger.sql | 88 ++++++++++++++++++++++- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 52 ++++++-------- 2 files changed, 106 insertions(+), 34 deletions(-) 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..c203e30cb1 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,passive) VALUES ('format.po.html','acqpo','Formats a Purchase Order as an HTML document'); +INSERT INTO action_trigger.hook (key,core_type,description,passive) 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 3729f08839..5b043881e9 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -9445,7 +9445,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( @@ -9453,8 +9453,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', @@ -9463,8 +9462,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', @@ -9473,8 +9471,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', @@ -9483,8 +9480,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', @@ -9493,8 +9489,7 @@ INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ( 'A patron acquisition request has been rejected.', 'ath', 'description' - ), - TRUE + ) ) ; @@ -9768,12 +9763,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 ( @@ -9828,12 +9822,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 ( @@ -10322,12 +10315,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 ) @@ -10428,12 +10420,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 ) @@ -10476,12 +10467,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 ) @@ -10537,12 +10527,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 ) @@ -10587,12 +10576,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) -- 2.11.0