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');
60 INSERT INTO action_trigger.hook (key,core_type,description) VALUES ('bre.edit','bre','A bib record was edited');
61 INSERT INTO action_trigger.hook (key, core_type, description) VALUES ('au.email.test', 'au', 'A test email has been requested for this user');
62 INSERT INTO action_trigger.hook (key, core_type, description) VALUES ('au.sms_text.test', 'au', 'A test SMS has been requested for this user');
64 -- and much more, I'm sure
66 -- Specialized collection modules. Given an FM object, gather some info and return a scalar or ref.
67 CREATE TABLE action_trigger.collector (
68 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Collector:: namespace
71 INSERT INTO action_trigger.collector (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
72 --INSERT INTO action_trigger.collector (module,description) VALUES ('CircCountsByCircMod','Count of Circulations for a User, broken down by circulation modifier');
74 -- Simple tests on an FM object from hook.core_type to test for "should we still do this."
75 CREATE TABLE action_trigger.validator (
76 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Validator:: namespace
79 INSERT INTO action_trigger.validator (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
80 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_True','Always returns true -- validation always passes');
81 INSERT INTO action_trigger.validator (module,description) VALUES ('NOOP_False','Always returns false -- validation always fails');
82 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOpen','Check that the circulation is still open');
83 INSERT INTO action_trigger.validator (module,description) VALUES ('HoldIsAvailable','Check that an item is on the hold shelf');
84 INSERT INTO action_trigger.validator (module,description) VALUES ('CircIsOverdue','Check that the circulation is overdue');
85 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');
86 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');
88 -- After an event passes validation (action_trigger.validator), the reactor processes it.
89 CREATE TABLE action_trigger.reactor (
90 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Reactor:: namespace
94 INSERT INTO action_trigger.reactor (module,description) VALUES
98 'Returns the answer to life, the universe and everything',
103 INSERT INTO action_trigger.reactor (module,description) VALUES
107 'Always returns true -- reaction always passes',
112 INSERT INTO action_trigger.reactor (module,description) VALUES
116 'Always returns false -- reaction always fails',
121 INSERT INTO action_trigger.reactor (module,description) VALUES
125 'Send an email based on a user-defined template',
131 -- TODO: build a PDF generator
132 --INSERT INTO action_trigger.reactor (module,description) VALUES
133 --( 'GenerateBatchOverduePDF',
134 -- oils_i18n_gettext(
135 -- 'GenerateBatchOverduePDF',
136 -- 'Output a batch PDF of overdue notices for printing',
142 INSERT INTO action_trigger.reactor (module,description) VALUES
146 'Marks a circulation and associated item as lost',
151 INSERT INTO action_trigger.reactor (module,description) VALUES
155 'Applies a billing with a pre-defined amount to a circulation',
160 INSERT INTO action_trigger.reactor (module,description) VALUES
164 'Processes the configured template',
170 -- After an event is reacted to (either success or failure) a cleanup module is run against the resulting environment
171 CREATE TABLE action_trigger.cleanup (
172 module TEXT PRIMARY KEY, -- All live under the OpenILS::Trigger::Cleanup:: namespace
175 INSERT INTO action_trigger.cleanup (module,description) VALUES ('fourty_two','Returns the answer to life, the universe and everything');
176 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_True','Always returns true -- cleanup always passes');
177 INSERT INTO action_trigger.cleanup (module,description) VALUES ('NOOP_False','Always returns false -- cleanup always fails');
178 INSERT INTO action_trigger.cleanup (module,description) VALUES ('ClearAllPending','Remove all future, pending notifications for this target');
180 CREATE TABLE action_trigger.event_definition (
181 id SERIAL PRIMARY KEY,
182 active BOOL NOT NULL DEFAULT TRUE,
183 owner INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
185 hook TEXT NOT NULL REFERENCES action_trigger.hook (key) DEFERRABLE INITIALLY DEFERRED,
186 validator TEXT NOT NULL REFERENCES action_trigger.validator (module) DEFERRABLE INITIALLY DEFERRED,
187 reactor TEXT NOT NULL REFERENCES action_trigger.reactor (module) DEFERRABLE INITIALLY DEFERRED,
188 cleanup_success TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
189 cleanup_failure TEXT REFERENCES action_trigger.cleanup (module) DEFERRABLE INITIALLY DEFERRED,
190 delay INTERVAL NOT NULL DEFAULT '5 minutes',
192 repeat_delay INTERVAL,
194 opt_in_setting TEXT REFERENCES config.usr_setting_type (name) DEFERRABLE INITIALLY DEFERRED,
195 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()
196 group_field TEXT, -- field from this.hook.core_type to batch event targets together on, fed into reactor a group at a time.
197 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.
198 granularity TEXT, -- could specify a batch which is the only time these events should actually run
200 context_usr_path TEXT, -- for optimizing action_trigger.event
201 context_library_path TEXT, -- '''
202 context_bib_path TEXT, -- '''
203 context_item_path TEXT, -- '''
205 message_template TEXT,
206 message_usr_path TEXT,
207 message_library_path TEXT,
209 retention_interval INTERVAL,
211 CONSTRAINT ev_def_owner_hook_val_react_clean_delay_once UNIQUE (owner, hook, validator, reactor, delay, delay_field),
212 CONSTRAINT ev_def_name_owner_once UNIQUE (owner, name)
215 CREATE TABLE action_trigger.alternate_template (
217 event_def INTEGER REFERENCES action_trigger.event_definition(id) INITIALLY DEFERRED,
219 active BOOLEAN DEFAULT TRUE,
220 locale TEXT REFERENCES config.i18n_locale(code) INITIALLY DEFERRED,
222 message_template TEXT,
223 UNIQUE (event_def,locale)
226 CREATE OR REPLACE FUNCTION action_trigger.check_valid_retention_interval()
227 RETURNS TRIGGER AS $_$
230 * 1. Retention intervals are always allowed on active hooks.
231 * 2. On passive hooks, retention intervals are only allowed
232 * when the event definition has a max_delay value and the
233 * retention_interval value is greater than the difference
234 * beteween the delay and max_delay values.
236 PERFORM TRUE FROM action_trigger.hook
237 WHERE key = NEW.hook AND NOT passive;
243 IF NEW.max_delay IS NOT NULL THEN
244 IF EXTRACT(EPOCH FROM NEW.retention_interval) >
245 ABS(EXTRACT(EPOCH FROM (NEW.max_delay - NEW.delay))) THEN
246 RETURN NEW; -- all good
248 RAISE EXCEPTION 'retention_interval is too short';
251 RAISE EXCEPTION 'retention_interval requires max_delay';
254 $_$ LANGUAGE PLPGSQL;
256 CREATE TRIGGER is_valid_retention_interval
257 BEFORE INSERT OR UPDATE ON action_trigger.event_definition
258 FOR EACH ROW WHEN (NEW.retention_interval IS NOT NULL)
259 EXECUTE PROCEDURE action_trigger.check_valid_retention_interval();
261 CREATE TABLE action_trigger.environment (
262 id SERIAL PRIMARY KEY,
263 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
264 path TEXT, -- fields to flesh. given a hook with a core_type of circ, imagine circ_lib.parent_ou expanding to
265 -- {flesh: 2, flesh_fields: {circ: ['circ_lib'], aou: ['parent_ou']}} ... default is to flesh all
267 collector TEXT REFERENCES action_trigger.collector (module) DEFERRABLE INITIALLY DEFERRED, -- if set, given the object at 'path', return some data
268 -- to be stashed at environment.<label>
269 label TEXT CHECK (label NOT IN ('result','target','event')),
270 CONSTRAINT env_event_label_once UNIQUE (event_def,label)
273 CREATE TABLE action_trigger.event_output (
274 id BIGSERIAL PRIMARY KEY,
275 create_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
276 is_error BOOLEAN NOT NULL DEFAULT FALSE,
281 CREATE TABLE action_trigger.event (
282 id BIGSERIAL PRIMARY KEY,
283 target BIGINT NOT NULL, -- points at the id from class defined by event_def.hook.core_type
284 event_def INT REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
285 add_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
286 run_time TIMESTAMPTZ NOT NULL,
287 start_time TIMESTAMPTZ,
288 update_time TIMESTAMPTZ,
289 complete_time TIMESTAMPTZ,
291 state TEXT NOT NULL DEFAULT 'pending' CHECK (state IN ('pending','invalid','found','collecting','collected','validating','valid','reacting','reacted','cleaning','complete','error')),
292 user_data TEXT CHECK (user_data IS NULL OR is_json( user_data )),
293 template_output BIGINT REFERENCES action_trigger.event_output (id),
294 error_output BIGINT REFERENCES action_trigger.event_output (id),
295 async_output BIGINT REFERENCES action_trigger.event_output (id),
296 context_user INT REFERENCES actor.usr (id),
297 context_library INT REFERENCES actor.org_unit (id),
298 context_bib BIGINT REFERENCES biblio.record_entry (id),
301 CREATE INDEX atev_target_def_idx ON action_trigger.event (target,event_def);
302 CREATE INDEX atev_def_state ON action_trigger.event (event_def,state);
303 CREATE INDEX atev_template_output ON action_trigger.event (template_output);
304 CREATE INDEX atev_async_output ON action_trigger.event (async_output);
305 CREATE INDEX atev_error_output ON action_trigger.event (error_output);
306 CREATE INDEX atev_context_user ON action_trigger.event (context_user);
307 CREATE INDEX atev_context_library ON action_trigger.event (context_library);
308 CREATE INDEX atev_context_item ON action_trigger.event (context_item);
310 CREATE TRIGGER action_trigger_event_context_item_fkey_trig
311 AFTER INSERT OR UPDATE ON action_trigger.event
312 FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('context_item');
314 CREATE TABLE action_trigger.event_params (
315 id BIGSERIAL PRIMARY KEY,
316 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id) DEFERRABLE INITIALLY DEFERRED,
317 param TEXT NOT NULL, -- the key under environment.event.params to store the output of ...
318 value TEXT NOT NULL, -- ... the eval() output of this. Has access to environment (and, well, all of perl)
319 CONSTRAINT event_params_event_def_param_once UNIQUE (event_def,param)
322 CREATE TABLE action_trigger.event_def_group (
323 id SERIAL PRIMARY KEY,
324 owner INT NOT NULL REFERENCES actor.org_unit (id)
325 ON DELETE RESTRICT ON UPDATE CASCADE
326 DEFERRABLE INITIALLY DEFERRED,
327 hook TEXT NOT NULL REFERENCES action_trigger.hook (key)
328 ON DELETE RESTRICT ON UPDATE CASCADE
329 DEFERRABLE INITIALLY DEFERRED,
330 active BOOL NOT NULL DEFAULT TRUE,
333 SELECT SETVAL('action_trigger.event_def_group_id_seq'::TEXT, 100, TRUE);
335 CREATE TABLE action_trigger.event_def_group_member (
336 id SERIAL PRIMARY KEY,
337 grp INT NOT NULL REFERENCES action_trigger.event_def_group (id)
338 ON DELETE CASCADE ON UPDATE CASCADE
339 DEFERRABLE INITIALLY DEFERRED,
340 event_def INT NOT NULL REFERENCES action_trigger.event_definition (id)
341 ON DELETE RESTRICT ON UPDATE CASCADE
342 DEFERRABLE INITIALLY DEFERRED,
343 sortable BOOL NOT NULL DEFAULT TRUE,
344 holdings BOOL NOT NULL DEFAULT FALSE,
345 external BOOL NOT NULL DEFAULT FALSE,
349 CREATE OR REPLACE FUNCTION action_trigger.purge_events() RETURNS VOID AS $_$
351 * Deleting expired events without simultaneously deleting their outputs
352 * creates orphaned outputs. Deleting their outputs and all of the events
353 * linking back to them, plus any outputs those events link to is messy and
354 * inefficient. It's simpler to handle them in 2 sweeping steps.
356 * 1. Delete expired events.
357 * 2. Delete orphaned event outputs.
359 * This has the added benefit of removing outputs that may have been
360 * orphaned by some other process. Such outputs are not usuable by
363 * This does not guarantee that all events within an event group are
364 * purged at the same time. In such cases, the remaining events will
365 * be purged with the next instance of the purge (or soon thereafter).
366 * This is another nod toward efficiency over completeness of old
367 * data that's circling the bit bucket anyway.
371 DELETE FROM action_trigger.event WHERE id IN (
373 FROM action_trigger.event evt
374 JOIN action_trigger.event_definition def ON (def.id = evt.event_def)
375 WHERE def.retention_interval IS NOT NULL
376 AND evt.state <> 'pending'
377 AND evt.update_time < (NOW() - def.retention_interval)
380 WITH linked_outputs AS (
381 SELECT templates.id AS id FROM (
382 SELECT DISTINCT(template_output) AS id
383 FROM action_trigger.event WHERE template_output IS NOT NULL
385 SELECT DISTINCT(error_output) AS id
386 FROM action_trigger.event WHERE error_output IS NOT NULL
388 SELECT DISTINCT(async_output) AS id
389 FROM action_trigger.event WHERE async_output IS NOT NULL
391 ) DELETE FROM action_trigger.event_output
392 WHERE id NOT IN (SELECT id FROM linked_outputs);
395 $_$ LANGUAGE PLPGSQL;