2 * Copyright (C) 2009 Equinox Software, Inc.
3 * Mike Rylander <miker@esilibrary.com>
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
17 DROP SCHEMA IF EXISTS action_trigger CASCADE;
21 CREATE SCHEMA action_trigger;
23 CREATE TABLE action_trigger.hook (
25 core_type TEXT NOT NULL,
27 passive BOOL NOT NULL DEFAULT FALSE
29 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout','circ','Item checked out to user');
30 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkin','circ','Item checked in');
31 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost','circ','Circulating Item marked Lost');
32 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost.found','circ','Lost Circulating Item checked in');
33 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('lost.auto','circ','Circulating Item automatically marked lost');
34 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('claims_returned','circ','Circulating Item marked Claims Returned');
35 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('claims_returned.found','circ','Claims Returned Circulating Item is checked in');
36 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('missing','acp','Item marked Missing');
37 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('missing.found','acp','Missing Item checked in');
38 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('transit.start','acp','An Item is placed into transit');
39 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('transit.finish','acp','An Item is received from a transit');
40 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_request.success','ahr','A hold is successfully placed');
41 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_request.failure','ahr','A hold is attempted but not successfully placed');
42 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.capture','ahr','A targeted Item is captured for a hold');
43 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.available','ahr','A held item is ready for pickup');
44 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_transit.start','ahtc','A hold-captured Item is placed into transit');
45 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold_transit.finish','ahtc','A hold-captured Item is received from a transit');
46 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('checkout.due','circ','Checked out Item is Due',TRUE);
47 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_FINES','ausp','Patron has exceeded allowed fines',TRUE);
48 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_OVERDUE_COUNT','ausp','Patron has exceeded allowed overdue count',TRUE);
49 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('penalty.PATRON_EXCEEDS_CHECKOUT_COUNT','ausp','Patron has exceeded allowed checkout count',TRUE);
50 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);
51 INSERT INTO action_trigger.hook (key,core_type,description,passive) VALUES ('acqpo.activated','acqpo','Purchase order was activated',FALSE);
52 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('format.po.html','acqpo','Formats a Purchase Order as an HTML document');
53 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('format.po.pdf','acqpo','Formats a Purchase Order as a PDF document');
54 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('damaged','acp','Item marked damaged');
55 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout.damaged','circ','A circulating item is marked damaged and the patron is fined');
56 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('renewal','circ','Item renewed to user');
57 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('checkout.due.emergency_closing','aecc','Circulation due date was adjusted by the Emergency Closing handler');
58 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('hold.shelf_expire.emergency_closing','aech','Hold shelf expire time was adjusted by the Emergency Closing handler');
59 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('booking.due.emergency_closing','aecr','Booking reservation return date was adjusted by the Emergency Closing handler');
61 -- and much more, I'm sure
63 -- Specialized collection modules. Given an FM object, gather some info and return a scalar or ref.
64 CREATE TABLE action_trigger.collector (
65 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Collector:: namespace
68 INSERT INTO action_trigger.collector (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
69 --INSERT INTO action_trigger.collector (module,description) VALUES ('CircCountsByCircMod','Count of Circulations for a User, broken down by circulation modifier');
71 -- Simple tests on an FM object from hook.core_type to test for "should we still do this."
72 CREATE TABLE action_trigger.validator (
73 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Validator:: namespace
76 INSERT INTO action_trigger.validator (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
77 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_True','Always returns true -- validation always passes');
78 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_False','Always returns false -- validation always fails');
79 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOpen','Check that the circulation is still open');
80 INSERT INTO action_trigger.validator (module,description) VALUES ('HoldIsAvailable','Check that an item is on the hold shelf');
81 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOverdue','Check that the circulation is overdue');
82 INSERT INTO action_trigger.validator (module,description) VALUES ('MaxPassiveDelayAge','Check that the event is not too far past the delay_field time -- requires a max_delay_age interval parameter');
83 INSERT INTO action_trigger.validator (module,description) VALUES ('MinPassiveTargetAge','Check that the target is old enough to be used by this event -- requires a min_target_age interval parameter, and accepts an optional target_age_field to specify what time to use for offsetting');
85 -- After an event passes validation (action_trigger.validator), the reactor processes it.
86 CREATE TABLE action_trigger.reactor (
87 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Reactor:: namespace
91 INSERT INTO action_trigger.reactor (module,description) VALUES
95 'Returns the answer to life, the universe and everything',
100 INSERT INTO action_trigger.reactor (module,description) VALUES
104 'Always returns true -- reaction always passes',
109 INSERT INTO action_trigger.reactor (module,description) VALUES
113 'Always returns false -- reaction always fails',
118 INSERT INTO action_trigger.reactor (module,description) VALUES
122 'Send an email based on a user-defined template',
128 -- TODO: build a PDF generator
129 --INSERT INTO action_trigger.reactor (module,description) VALUES
130 --( 'GenerateBatchOverduePDF',
131 -- oils_i18n_gettext(
132 -- 'GenerateBatchOverduePDF',
133 -- 'Output a batch PDF of overdue notices for printing',
139 INSERT INTO action_trigger.reactor (module,description) VALUES
143 'Marks a circulation and associated item as lost',
148 INSERT INTO action_trigger.reactor (module,description) VALUES
152 'Applies a billing with a pre-defined amount to a circulation',
157 INSERT INTO action_trigger.reactor (module,description) VALUES
161 'Processes the configured template',
167 -- After an event is reacted to (either success or failure) a cleanup module is run against the resulting environment
168 CREATE TABLE action_trigger.cleanup (
169 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Cleanup:: namespace
172 INSERT INTO action_trigger.cleanup (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
173 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_True','Always returns true -- cleanup always passes');
174 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_False','Always returns false -- cleanup always fails');
175 INSERT INTO action_trigger.cleanup (module,description) VALUES ('ClearAllPending','Remove all future, pending notifications for this target');
177 CREATE TABLE action_trigger.event_definition (
178 id SERIAL PRIMARY KEY,
179 active BOOL NOT NULL DEFAULT TRUE,
180 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
182 hook TEXT NOT NULL REFERENCES action_trigger.hook (key) DEFERRABLE INITIALLY DEFERRED,
183 validator TEXT NOT NULL REFERENCES action_trigger.validator (module) DEFERRABLE INITIALLY DEFERRED,
184 reactor TEXT NOT NULL REFERENCES action_trigger.reactor (module) DEFERRABLE INITIALLY DEFERRED,
185 cleanup_success TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
186 cleanup_failure TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
187 delay INTERVAL NOT NULL DEFAULT '5 minutes',
189 repeat_delay INTERVAL,
191 opt_in_setting TEXT REFERENCES config.usr_setting_type (name) DEFERRABLE INITIALLY DEFERRED,
192 delay_field TEXT, -- for instance, xact_start on a circ hook ... look for fields on hook.core_type where datatype=timestamp? If not set, delay from now()
193 group_field TEXT, -- field from this.hook.core_type to batch event targets together on, fed into reactor a group at a time.
194 template TEXT, -- the TT block. will have an 'environment' hash (or array of hashes, grouped events) built up by validator and collector(s), which can be modified.
195 granularity TEXT, -- could specify a batch which is the only time these events should actually run
197 message_template TEXT,
198 message_usr_path TEXT,
199 message_library_path TEXT,
201 retention_interval INTERVAL,
203 CONSTRAINT ev_def_owner_hook_val_react_clean_delay_once UNIQUE (owner, hook, validator, reactor, delay, delay_field),
204 CONSTRAINT ev_def_name_owner_once UNIQUE (owner, name)
207 CREATE OR REPLACE FUNCTION action_trigger.check_valid_retention_interval()
208 RETURNS TRIGGER AS $_$
211 * 1. Retention intervals are always allowed on active hooks.
212 * 2. On passive hooks, retention intervals are only allowed
213 * when the event definition has a max_delay value and the
214 * retention_interval value is greater than the difference
215 * beteween the delay and max_delay values.
217 PERFORM TRUE FROM action_trigger.hook
218 WHERE key = NEW.hook AND NOT passive;
224 IF NEW.max_delay IS NOT NULL THEN
225 IF EXTRACT(EPOCH FROM NEW.retention_interval) >
226 ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN
227 RETURN NEW; -- all good
229 RAISE EXCEPTION 'retention_interval is too short';
232 RAISE EXCEPTION 'retention_interval requires max_delay';
235 $_$ LANGUAGE PLPGSQL;
237 CREATE TRIGGER is_valid_retention_interval
238 BEFORE INSERT OR UPDATE ON action_trigger.event_definition
239 FOR EACH ROW WHEN (NEW.retention_interval IS NOT NULL)
240 EXECUTE PROCEDURE action_trigger.check_valid_retention_interval();
242 CREATE TABLE action_trigger.environment (
243 id SERIAL PRIMARY KEY,
244 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
245 path TEXT, -- fields to flesh. given a hook with a core_type of circ, imagine circ_lib.parent_ou expanding to
246 -- {flesh: 2, flesh_fields: {circ: ['circ_lib'], aou: ['parent_ou']}} ... default is to flesh all
248 collector TEXT REFERENCES action_trigger.collector (module) DEFERRABLE INITIALLY DEFERRED, -- if set, given the object at 'path', return some data
249 -- to be stashed at environment.<label>
250 label TEXT CHECK (label NOT IN ('result','target','event')),
251 CONSTRAINT env_event_label_once UNIQUE (event_def,label)
254 CREATE TABLE action_trigger.event_output (
255 id BIGSERIAL PRIMARY KEY,
256 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
257 is_error BOOLEAN NOT NULL DEFAULT FALSE,
261 CREATE TABLE action_trigger.event (
262 id BIGSERIAL PRIMARY KEY,
263 target BIGINT NOT NULL, -- points at the id from class defined by event_def.hook.core_type
264 event_def INT REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
265 add_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
266 run_time TIMESTAMPTZ NOT NULL,
267 start_time TIMESTAMPTZ,
268 update_time TIMESTAMPTZ,
269 complete_time TIMESTAMPTZ,
271 state TEXT NOT NULL DEFAULT 'pending' CHECK (state IN ('pending','invalid','found','collecting','collected','validating','valid','reacting','reacted','cleaning','complete','error')),
272 user_data TEXT CHECK (user_data IS NULL OR is_json( user_data )),
273 template_output BIGINT REFERENCES action_trigger.event_output (id),
274 error_output BIGINT REFERENCES action_trigger.event_output (id),
275 async_output BIGINT REFERENCES action_trigger.event_output (id)
277 CREATE INDEX atev_target_def_idx ON action_trigger.event (target,event_def);
278 CREATE INDEX atev_def_state ON action_trigger.event (event_def,state);
279 CREATE INDEX atev_template_output ON action_trigger.event (template_output);
280 CREATE INDEX atev_async_output ON action_trigger.event (async_output);
281 CREATE INDEX atev_error_output ON action_trigger.event (error_output);
283 CREATE TABLE action_trigger.event_params (
284 id BIGSERIAL PRIMARY KEY,
285 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
286 param TEXT NOT NULL, -- the key under environment.event.params to store the output of ...
287 value TEXT NOT NULL, -- ... the eval() output of this. Has access to environment (and, well, all of perl)
288 CONSTRAINT event_params_event_def_param_once UNIQUE (event_def,param)
291 CREATE OR REPLACE FUNCTION action_trigger.purge_events() RETURNS VOID AS $_$
293 * Deleting expired events without simultaneously deleting their outputs
294 * creates orphaned outputs. Deleting their outputs and all of the events
295 * linking back to them, plus any outputs those events link to is messy and
296 * inefficient. It's simpler to handle them in 2 sweeping steps.
298 * 1. Delete expired events.
299 * 2. Delete orphaned event outputs.
301 * This has the added benefit of removing outputs that may have been
302 * orphaned by some other process. Such outputs are not usuable by
305 * This does not guarantee that all events within an event group are
306 * purged at the same time. In such cases, the remaining events will
307 * be purged with the next instance of the purge (or soon thereafter).
308 * This is another nod toward efficiency over completeness of old
309 * data that's circling the bit bucket anyway.
313 DELETE FROM action_trigger.event WHERE id IN (
315 FROM action_trigger.event evt
316 JOIN action_trigger.event_definition def ON (def.id = evt.event_def)
317 WHERE def.retention_interval IS NOT NULL
318 AND evt.state <> 'pending'
319 AND evt.update_time < (NOW() - def.retention_interval)
322 WITH linked_outputs AS (
323 SELECT templates.id AS id FROM (
324 SELECT DISTINCT(template_output) AS id
325 FROM action_trigger.event WHERE template_output IS NOT NULL
327 SELECT DISTINCT(error_output) AS id
328 FROM action_trigger.event WHERE error_output IS NOT NULL
330 SELECT DISTINCT(async_output) AS id
331 FROM action_trigger.event WHERE async_output IS NOT NULL
333 ) DELETE FROM action_trigger.event_output
334 WHERE id NOT IN (SELECT id FROM linked_outputs);
337 $_$ LANGUAGE PLPGSQL;