From 9d34585e540d95da348163252f276c30bf574701 Mon Sep 17 00:00:00 2001 From: Jason Etheridge Date: Wed, 29 Jul 2020 08:11:33 -0400 Subject: [PATCH] LP1207533 first cut at schema Signed-off-by: Jason Etheridge --- Open-ILS/examples/fm_IDL.xml | 26 ++++++----- Open-ILS/src/sql/Pg/400.schema.action_trigger.sql | 9 +++- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 22 +++++++++ .../Pg/upgrade/XXXX.schema.triggered_event_log.sql | 53 ++++++++++++++++++++++ 4 files changed, 97 insertions(+), 13 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.triggered_event_log.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index f7bfcf725c..605e935324 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -1454,6 +1454,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + @@ -1492,12 +1494,16 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + @@ -1547,25 +1553,19 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA atev.template_output, atev.error_output, atev.async_output, - targ_circ.id AS target_circ, - targ_ahr.id AS target_hold, - COALESCE( - targ_circ.circ_lib, - targ_ahr.pickup_lib - ) AS perm_lib + CASE WHEN ath.core_type = 'circ' THEN atev.target END AS target_circ, + CASE WHEN ath.core_type = 'ahr' THEN atev.target END AS target_hold, + atev.context_user, + atev.context_library AS perm_lib FROM action_trigger.event atev JOIN action_trigger.event_definition atevdef ON (atevdef.id = atev.event_def) JOIN action_trigger.hook ath ON - (ath.key = atevdef.hook) - LEFT JOIN action.circulation targ_circ ON - (ath.core_type = 'circ' AND targ_circ.id = atev.target) - LEFT JOIN action.hold_request targ_ahr ON - (ath.core_type = 'ahr' AND targ_ahr.id = atev.target) + (ath.key = atevdef.hook AND ath.core_type IN ('circ','ahr')) WHERE atev.add_time > NOW() - (SELECT MIN(value) FROM ( SELECT value::INTERVAL FROM actor.org_unit_ancestor_setting( 'circ.staff.max_visible_event_age', - COALESCE(targ_circ.circ_lib, targ_ahr.pickup_lib) + atev.context_library ) UNION SELECT '1000 YEARS'::INTERVAL AS value ) ous) @@ -1589,6 +1589,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -1598,6 +1599,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 69b365d3f8..c8c6095b00 100644 --- a/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql +++ b/Open-ILS/src/sql/Pg/400.schema.action_trigger.sql @@ -194,6 +194,9 @@ CREATE TABLE action_trigger.event_definition ( 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. granularity TEXT, -- could specify a batch which is the only time these events should actually run + context_usr_path TEXT, -- for optimizing action_trigger.event + context_library_path TEXT, -- ''' + message_template TEXT, message_usr_path TEXT, message_library_path TEXT, @@ -272,13 +275,17 @@ CREATE TABLE action_trigger.event ( user_data TEXT CHECK (user_data IS NULL OR is_json( user_data )), template_output BIGINT REFERENCES action_trigger.event_output (id), error_output BIGINT REFERENCES action_trigger.event_output (id), - async_output BIGINT REFERENCES action_trigger.event_output (id) + async_output BIGINT REFERENCES action_trigger.event_output (id), + context_user INT REFERENCES actor.usr (id), + context_library INT REFERENCES actor.org_unit (id) ); CREATE INDEX atev_target_def_idx ON action_trigger.event (target,event_def); CREATE INDEX atev_def_state ON action_trigger.event (event_def,state); CREATE INDEX atev_template_output ON action_trigger.event (template_output); CREATE INDEX atev_async_output ON action_trigger.event (async_output); CREATE INDEX atev_error_output ON action_trigger.event (error_output); +CREATE INDEX atev_context_user ON action_trigger.event (context_user); +CREATE INDEX atev_context_library ON action_trigger.event (context_library); CREATE TABLE action_trigger.event_params ( id BIGSERIAL PRIMARY KEY, 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 36e655d460..0137a38d36 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -17150,6 +17150,28 @@ INSERT INTO action_trigger.environment ( 'circ_lib' ); +UPDATE + action_trigger.event_definition +SET + context_usr_path = 'usr', + context_library_path = 'circ_lib' +WHERE + hook IN ( + SELECT key FROM action_trigger.hook WHERE core_type = 'circ' + ) +; + +UPDATE + action_trigger.event_definition +SET + context_usr_path = 'usr', + context_library_path = 'pickup_lib' +WHERE + hook IN ( + SELECT key FROM action_trigger.hook WHERE core_type = 'ahr' + ) +; + INSERT INTO config.org_unit_setting_type (name, grp, label, description, datatype) VALUES diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.triggered_event_log.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.triggered_event_log.sql new file mode 100644 index 0000000000..5492ff02f8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.triggered_event_log.sql @@ -0,0 +1,53 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- context_usr_path TEXT, -- for optimizing action_trigger.event +-- context_library_path TEXT, -- ''' +ALTER TABLE action_trigger.event_definition ADD COLUMN context_usr_path TEXT; +ALTER TABLE action_trigger.event_definition ADD COLUMN context_library_path TEXT; + +-- context_user INT REFERENCES actor.usr (id), +-- context_library INT REFERENCES actor.org_unit (id) +ALTER TABLE action_trigger.event ADD COLUMN context_user INT REFERENCES actor.usr (id); +ALTER TABLE action_trigger.event ADD COLUMN context_library INT REFERENCES actor.org_unit (id); +CREATE INDEX atev_context_user ON action_trigger.event (context_user); +CREATE INDEX atev_context_library ON action_trigger.event (context_library); + +UPDATE + action_trigger.event_definition +SET + context_usr_path = 'usr', + context_library_path = 'circ_lib' +WHERE + hook IN ( + SELECT key FROM action_trigger.hook WHERE core_type = 'circ' + ) +; + +UPDATE + action_trigger.event_definition +SET + context_usr_path = 'usr', + context_library_path = 'pickup_lib' +WHERE + hook IN ( + SELECT key FROM action_trigger.hook WHERE core_type = 'ahr' + ) +; + +-- Retroactively setting context_user and context_library on existing rows in action_trigger.event: +-- This is not done by default because it'll likely take a long time depending on the Evergreen +-- installation. You may want to do this out-of-band with the upgrade if you want to do this at all. +-- +-- \pset format unaligned +-- \t +-- \o update_action_trigger_events_for_circs.sql +-- SELECT 'UPDATE action_trigger.event e SET context_user = c.usr, context_library = c.circ_lib FROM action.circulation c WHERE c.id = e.target AND e.id = ' || e.id || ' RETURNING ' || e.id || ';' FROM action_trigger.event e, action.circulation c WHERE e.target = c.id AND e.event_def IN (SELECT id FROM action_trigger.event_definition WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'circ')) ORDER BY e.id DESC; +-- \o +-- \o update_action_trigger_events_for_holds.sql +-- SELECT 'UPDATE action_trigger.event e SET context_user = h.usr, context_library = h.pickup_lib FROM action.hold_request h WHERE h.id = e.target AND e.id = ' || e.id || ' RETURNING ' || e.id || ';' FROM action_trigger.event e, action.hold_request h WHERE e.target = h.id AND e.event_def IN (SELECT id FROM action_trigger.event_definition WHERE hook in (SELECT key FROM action_trigger.hook WHERE core_type = 'ahr')) ORDER BY e.id DESC; +-- \o + +COMMIT; + -- 2.11.0