From 0d4113d3c229822444e5e95cbfe9ef542ab96950 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 1 Sep 2016 17:35:16 -0400 Subject: [PATCH] JBAS-1554 C/O history phase 2 SQL Signed-off-by: Bill Erickson --- .../schema/deploy/patron-co-history-stage-2.sql | 290 ++++++++ KCLS/sql/schema/deploy/patron-co-history-table.sql | 796 --------------------- .../schema/revert/patron-co-history-stage-2.sql | 308 ++++++++ KCLS/sql/schema/revert/patron-co-history-table.sql | 639 ----------------- KCLS/sql/schema/sqitch.plan | 4 +- ...ory-table.sql => patron-co-history-stage-2.sql} | 0 6 files changed, 600 insertions(+), 1437 deletions(-) create mode 100644 KCLS/sql/schema/deploy/patron-co-history-stage-2.sql delete mode 100644 KCLS/sql/schema/deploy/patron-co-history-table.sql create mode 100644 KCLS/sql/schema/revert/patron-co-history-stage-2.sql delete mode 100644 KCLS/sql/schema/revert/patron-co-history-table.sql rename KCLS/sql/schema/verify/{patron-co-history-table.sql => patron-co-history-stage-2.sql} (100%) diff --git a/KCLS/sql/schema/deploy/patron-co-history-stage-2.sql b/KCLS/sql/schema/deploy/patron-co-history-stage-2.sql new file mode 100644 index 0000000000..9145a0157a --- /dev/null +++ b/KCLS/sql/schema/deploy/patron-co-history-stage-2.sql @@ -0,0 +1,290 @@ +-- Deploy kcls-evergreen:patron-co-history-table to pg +-- requires: sip-activity-types + +BEGIN; + +SET STATEMENT_TIMEOUT = 0; + +\set eg_version '''2.7.8''' +SELECT evergreen.upgrade_deps_block_check('0960', :eg_version); + +UPDATE action_trigger.hook + SET core_type = 'auch' + WHERE key ~ '^circ.format.history.'; + +-- We are not using these template locally and have not modified them +-- Use whatever EG is using natively. + +UPDATE action_trigger.event_definition SET template = +$$ +[%- USE date -%] +[%- SET user = target.0.usr -%] +To: [%- params.recipient_email || user.email %] +From: [%- params.sender_email || default_sender %] +Subject: Circulation History + + [% FOR circ IN target %] + [% helpers.get_copy_bib_basics(circ.target_copy.id).title %] + Barcode: [% circ.target_copy.barcode %] + Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %] + Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %] + Returned: [% + date.format( + helpers.format_date(circ.checkin_time), '%Y-%m-%d') + IF circ.checkin_time; + %] + [% END %] +$$ +WHERE id = 25 AND template = +$$ +[%- USE date -%] +[%- SET user = target.0.usr -%] +To: [%- params.recipient_email || user.email %] +From: [%- params.sender_email || default_sender %] +Subject: Circulation History + + [% FOR circ IN target %] + [% helpers.get_copy_bib_basics(circ.target_copy.id).title %] + Barcode: [% circ.target_copy.barcode %] + Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %] + Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %] + Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %] + [% END %] +$$; + +-- avoid TT undef date errors +UPDATE action_trigger.event_definition SET template = +$$ +[%- USE date -%] +
+ +
[% date.format %]
+
+ + [% user.family_name %], [% user.first_given_name %] +
    + [% FOR circ IN target %] +
  1. +
    [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
    +
    Barcode: [% circ.target_copy.barcode %]
    +
    Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
    +
    Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
    +
    Returned: [% + date.format( + helpers.format_date(circ.checkin_time), '%Y-%m-%d') + IF circ.checkin_time; -%] +
    +
    2. + [% END %] +
+
+$$ +WHERE id = 26 AND template = -- only replace template if it matches stock +$$ +[%- USE date -%] +
+ +
[% date.format %]
+
+ + [% user.family_name %], [% user.first_given_name %] +
    + [% FOR circ IN target %] +
  1. +
    [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
    +
    Barcode: [% circ.target_copy.barcode %]
    +
    Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
    +
    Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
    +
    Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
    +
    2. + [% END %] +
+
+$$; + +-- NOTE: ^-- stock CSV template does not include checkin_time, so +-- no modifications are required. + +CREATE OR REPLACE FUNCTION action.purge_circulations_custom () RETURNS INT AS $func$ +DECLARE + usr_keep_age actor.usr_setting%ROWTYPE; + usr_keep_start actor.usr_setting%ROWTYPE; + org_keep_age INTERVAL; + org_use_last BOOL = false; + org_age_is_min BOOL = false; + org_keep_count INT; + + keep_age INTERVAL; + + target_acp RECORD; + circ_chain_head action.circulation%ROWTYPE; + circ_chain_tail action.circulation%ROWTYPE; + + count_purged INT; + num_incomplete INT; + + last_finished TIMESTAMP WITH TIME ZONE; + cmd_main_loop_item int; + cmd_count_kept int; + time_start timestamp with time zone; +BEGIN + time_start = clock_timestamp(); + count_purged := 0; + cmd_count_kept := 0; + cmd_main_loop_item := 0; + + SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; + raise debug 'in_time_start=%; in_time_end=%; org_keep_age=%',in_time_start,in_time_end,org_keep_age; + + SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; + IF org_keep_count IS NULL THEN + RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever + END IF; + + SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; + SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; + + -- And, for those, select circs that are finished and older than keep_age + FOR circ_chain_head IN + SELECT ac.* FROM action.circulation ac + WHERE 1=1 + AND ac.parent_circ IS NULL + AND xact_finish < now() - coalesce(org_keep_age,'2000 years'::interval) + AND xact_finish between in_time_start and in_time_end + LOOP + cmd_main_loop_item := cmd_main_loop_item + 1; + if (cmd_main_loop_item - 1) % 1000 = 0 then + raise debug '% Main loop item %...',(extract(epoch from (clock_timestamp()-time_start))/60)::int,cmd_main_loop_item; + end if; + + SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; + SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); + CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; + + IF NOT org_use_last THEN + last_finished := circ_chain_tail.xact_finish; + END IF; + + keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); + + IF org_age_is_min THEN + keep_age := GREATEST( keep_age, org_keep_age ); + END IF; + + if AGE(NOW(), last_finished) < keep_age then + cmd_count_kept := cmd_count_kept + 1; + end if; + CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; + + -- We've passed the purging tests, purge the circ chain starting at the end + -- A trigger should auto-purge the rest of the chain. + DELETE FROM action.circulation WHERE id = circ_chain_tail.id; + + count_purged := count_purged + 1; + if count_purged % 1000 = 0 then + raise debug 'Purged Chains %', count_purged; + end if; + END LOOP; + + raise debug '% Done. Purged Chains=% Kept Chains=%',(extract(epoch from (clock_timestamp()-time_start))/60)::int,count_purged,cmd_count_kept; + return count_purged; +END; + +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$ +DECLARE + org_keep_age INTERVAL; + org_use_last BOOL = false; + org_age_is_min BOOL = false; + org_keep_count INT; + + keep_age INTERVAL; + + target_acp RECORD; + circ_chain_head action.circulation%ROWTYPE; + circ_chain_tail action.circulation%ROWTYPE; + + count_purged INT; + num_incomplete INT; + + last_finished TIMESTAMP WITH TIME ZONE; +BEGIN + + count_purged := 0; + + SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; + + SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; + IF org_keep_count IS NULL THEN + RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever + END IF; + + SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; + SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; + + -- First, find copies with more than keep_count non-renewal circs + FOR target_acp IN + SELECT target_copy, + COUNT(*) AS total_real_circs + FROM action.circulation + WHERE parent_circ IS NULL + AND xact_finish IS NOT NULL + GROUP BY target_copy + HAVING COUNT(*) > org_keep_count + LOOP + -- And, for those, select circs that are finished and older than keep_age + FOR circ_chain_head IN + -- For reference, the subquery uses a window function to order the circs newest to oldest and number them + -- The outer query then uses that information to skip the most recent set the library wants to keep + -- End result is we don't care what order they come out in, as they are all potentials for deletion. + SELECT ac.* FROM action.circulation ac JOIN ( + SELECT rank() OVER (ORDER BY xact_start DESC), ac.id + FROM action.circulation ac + WHERE ac.target_copy = target_acp.target_copy + AND ac.parent_circ IS NULL + ORDER BY ac.xact_start ) ranked USING (id) + WHERE ranked.rank > org_keep_count + LOOP + + SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; + SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); + CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; + + IF NOT org_use_last THEN + last_finished := circ_chain_tail.xact_finish; + END IF; + + keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); + + IF org_age_is_min THEN + keep_age := GREATEST( keep_age, org_keep_age ); + END IF; + + CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; + + -- We've passed the purging tests, purge the circ chain starting at the end + -- A trigger should auto-purge the rest of the chain. + DELETE FROM action.circulation WHERE id = circ_chain_tail.id; + + count_purged := count_purged + 1; + + END LOOP; + END LOOP; + + return count_purged; +END; +$func$ LANGUAGE PLPGSQL; + + +COMMIT; + +-- CLEAN THESE UP AFTER CONFIRMING CIRC HISTORY IS OK. + +/* + +DROP FUNCTION IF EXISTS action.usr_visible_circs (INTEGER); +DROP FUNCTION IF EXISTS action.usr_visible_circ_copies (INTEGER); + +*/ + diff --git a/KCLS/sql/schema/deploy/patron-co-history-table.sql b/KCLS/sql/schema/deploy/patron-co-history-table.sql deleted file mode 100644 index 882c9a409d..0000000000 --- a/KCLS/sql/schema/deploy/patron-co-history-table.sql +++ /dev/null @@ -1,796 +0,0 @@ --- Deploy kcls-evergreen:patron-co-history-table to pg --- requires: sip-activity-types - -BEGIN; - -SET STATEMENT_TIMEOUT = 0; - -\set eg_version '''2.7.8''' -SELECT evergreen.upgrade_deps_block_check('0960', :eg_version); - -DROP FUNCTION IF EXISTS action.all_circ_chain (INTEGER); - -CREATE TABLE action.usr_circ_history ( - id BIGSERIAL PRIMARY KEY, - usr INTEGER NOT NULL REFERENCES actor.usr(id) - DEFERRABLE INITIALLY DEFERRED, - xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), - target_copy BIGINT NOT NULL, -- asset.copy.id / serial.unit.id - due_date TIMESTAMP WITH TIME ZONE NOT NULL, - checkin_time TIMESTAMP WITH TIME ZONE, - source_circ BIGINT REFERENCES action.circulation(id) - ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED -); - - -CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history() - RETURNS TRIGGER AS $FUNK$ -DECLARE - cur_circ BIGINT; - first_circ BIGINT; -BEGIN - - -- Any retention value signifies history is enabled. - -- This assumes that clearing these values via external - -- process deletes the action.usr_circ_history rows. - -- TODO: replace these settings w/ a single bool setting? - PERFORM 1 FROM actor.usr_setting - WHERE usr = NEW.usr AND value IS NOT NULL AND name IN ( - 'history.circ.retention_age', - 'history.circ.retention_start' - ); - - IF NOT FOUND THEN - RETURN NEW; - END IF; - - IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN - -- Starting a new circulation. Insert the history row. - INSERT INTO action.usr_circ_history - (usr, xact_start, target_copy, due_date, source_circ) - VALUES ( - NEW.usr, - NEW.xact_start, - NEW.target_copy, - NEW.due_date, - NEW.id - ); - - RETURN NEW; - END IF; - - -- find the first and last circs in the circ chain - -- for the currently modified circ. - FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP - IF first_circ IS NULL THEN - first_circ := cur_circ; - CONTINUE; - END IF; - -- Allow the loop to continue so that at as the loop - -- completes cur_circ points to the final circulation. - END LOOP; - - IF NEW.id <> cur_circ THEN - -- Modifying an intermediate circ. Ignore it. - RETURN NEW; - END IF; - - -- Update the due_date/checkin_time on the history row if the current - -- circ is the last circ in the chain and an update is warranted. - - UPDATE action.usr_circ_history - SET - due_date = NEW.due_date, - checkin_time = NEW.checkin_time - WHERE - source_circ = first_circ - AND ( - due_date <> NEW.due_date OR ( - (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR - (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR - (checkin_time <> NEW.checkin_time) - ) - ); - RETURN NEW; -END; -$FUNK$ LANGUAGE PLPGSQL; - --- Create TRIGGER after migration -CREATE TRIGGER maintain_usr_circ_history_tgr - AFTER INSERT OR UPDATE ON action.circulation - FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history(); - -UPDATE action_trigger.hook - SET core_type = 'auch' - WHERE key ~ '^circ.format.history.'; - - --- We are not using these template locally and have not modified them --- Use whatever EG is using natively. - -UPDATE action_trigger.event_definition SET template = -$$ -[%- USE date -%] -[%- SET user = target.0.usr -%] -To: [%- params.recipient_email || user.email %] -From: [%- params.sender_email || default_sender %] -Subject: Circulation History - - [% FOR circ IN target %] - [% helpers.get_copy_bib_basics(circ.target_copy.id).title %] - Barcode: [% circ.target_copy.barcode %] - Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %] - Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %] - Returned: [% - date.format( - helpers.format_date(circ.checkin_time), '%Y-%m-%d') - IF circ.checkin_time; - %] - [% END %] -$$ -WHERE id = 25 AND template = -$$ -[%- USE date -%] -[%- SET user = target.0.usr -%] -To: [%- params.recipient_email || user.email %] -From: [%- params.sender_email || default_sender %] -Subject: Circulation History - - [% FOR circ IN target %] - [% helpers.get_copy_bib_basics(circ.target_copy.id).title %] - Barcode: [% circ.target_copy.barcode %] - Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %] - Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %] - Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %] - [% END %] -$$; - --- avoid TT undef date errors -UPDATE action_trigger.event_definition SET template = -$$ -[%- USE date -%] -
- -
[% date.format %]
-
- - [% user.family_name %], [% user.first_given_name %] -
    - [% FOR circ IN target %] -
  1. -
    [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
    -
    Barcode: [% circ.target_copy.barcode %]
    -
    Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
    -
    Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
    -
    Returned: [% - date.format( - helpers.format_date(circ.checkin_time), '%Y-%m-%d') - IF circ.checkin_time; -%] -
    -
    2. - [% END %] -
-
-$$ -WHERE id = 26 AND template = -- only replace template if it matches stock -$$ -[%- USE date -%] -
- -
[% date.format %]
-
- - [% user.family_name %], [% user.first_given_name %] -
    - [% FOR circ IN target %] -
  1. -
    [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
    -
    Barcode: [% circ.target_copy.barcode %]
    -
    Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
    -
    Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
    -
    Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
    -
    2. - [% END %] -
-
-$$; - --- NOTE: ^-- stock CSV template does not include checkin_time, so --- no modifications are required. - --- Create circ history rows for existing circ history data. -CREATE OR REPLACE FUNCTION action.migrate_usr_circ_history - (mod_factor INT, mod_slot INT) RETURNS VOID AS $FUNK$ -DECLARE - cur_usr INTEGER; - cur_circ action.circulation%ROWTYPE; - last_circ action.circulation%ROWTYPE; - circ_counter INTEGER DEFAULT 1; - usr_counter INTEGER DEFAULT 1; -BEGIN - - RAISE NOTICE - 'Migrating circ history for % users. This might take a while...', - (SELECT COUNT(DISTINCT(au.id)) FROM actor.usr au - JOIN actor.usr_setting aus ON (aus.usr = au.id) - WHERE - NOT au.deleted - AND aus.name ~ '^history.circ.retention_' - AND (au.id % mod_factor) = mod_slot); - - FOR cur_usr IN - SELECT DISTINCT(au.id) - FROM actor.usr au - JOIN actor.usr_setting aus ON (aus.usr = au.id) - WHERE - NOT au.deleted - AND aus.name ~ '^history.circ.retention_' - AND (au.id % mod_factor) = mod_slot LOOP - - FOR cur_circ IN SELECT * FROM action.usr_visible_circs(cur_usr) LOOP - - PERFORM TRUE FROM asset.copy WHERE id = cur_circ.target_copy; - - -- Avoid inserting a circ history row when the circulated - -- item has been (forcibly) removed from the database. - IF NOT FOUND THEN - CONTINUE; - END IF; - - -- Find the last circ in the circ chain. - SELECT INTO last_circ * - FROM action.circ_chain(cur_circ.id) - ORDER BY xact_start DESC LIMIT 1; - - -- Create the history row. - -- It's OK if last_circ = cur_circ - INSERT INTO action.usr_circ_history - (usr, xact_start, target_copy, - due_date, checkin_time, source_circ) - VALUES ( - cur_circ.usr, - cur_circ.xact_start, - cur_circ.target_copy, - last_circ.due_date, - last_circ.checkin_time, - cur_circ.id - ); - - -- useful for alleviating administrator anxiety. - IF circ_counter % 10000 = 0 THEN - RAISE NOTICE 'Migrated history for % total circs', circ_counter; - END IF; - - circ_counter := circ_counter + 1; - - END LOOP; - - -- useful for alleviating administrator anxiety. - IF usr_counter % 1000 = 0 THEN - RAISE NOTICE 'Migrated history for % total users', usr_counter; - END IF; - - usr_counter := usr_counter + 1; - - END LOOP; - -END $FUNK$ LANGUAGE PLPGSQL; - - -CREATE OR REPLACE FUNCTION action.purge_circulations_custom () RETURNS INT AS $func$ -DECLARE - usr_keep_age actor.usr_setting%ROWTYPE; - usr_keep_start actor.usr_setting%ROWTYPE; - org_keep_age INTERVAL; - org_use_last BOOL = false; - org_age_is_min BOOL = false; - org_keep_count INT; - - keep_age INTERVAL; - - target_acp RECORD; - circ_chain_head action.circulation%ROWTYPE; - circ_chain_tail action.circulation%ROWTYPE; - - count_purged INT; - num_incomplete INT; - - last_finished TIMESTAMP WITH TIME ZONE; - cmd_main_loop_item int; - cmd_count_kept int; - time_start timestamp with time zone; -BEGIN - time_start = clock_timestamp(); - count_purged := 0; - cmd_count_kept := 0; - cmd_main_loop_item := 0; - - SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; - raise debug 'in_time_start=%; in_time_end=%; org_keep_age=%',in_time_start,in_time_end,org_keep_age; - - SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; - IF org_keep_count IS NULL THEN - RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever - END IF; - - SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; - SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; - - -- And, for those, select circs that are finished and older than keep_age - FOR circ_chain_head IN - SELECT ac.* FROM action.circulation ac - WHERE 1=1 - AND ac.parent_circ IS NULL - AND xact_finish < now() - coalesce(org_keep_age,'2000 years'::interval) - AND xact_finish between in_time_start and in_time_end - LOOP - cmd_main_loop_item := cmd_main_loop_item + 1; - if (cmd_main_loop_item - 1) % 1000 = 0 then - raise debug '% Main loop item %...',(extract(epoch from (clock_timestamp()-time_start))/60)::int,cmd_main_loop_item; - end if; - - SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; - SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); - CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; - - IF NOT org_use_last THEN - last_finished := circ_chain_tail.xact_finish; - END IF; - - keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); - - IF org_age_is_min THEN - keep_age := GREATEST( keep_age, org_keep_age ); - END IF; - - if AGE(NOW(), last_finished) < keep_age then - cmd_count_kept := cmd_count_kept + 1; - end if; - CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; - - -- We've passed the purging tests, purge the circ chain starting at the end - -- A trigger should auto-purge the rest of the chain. - DELETE FROM action.circulation WHERE id = circ_chain_tail.id; - - count_purged := count_purged + 1; - if count_purged % 1000 = 0 then - raise debug 'Purged Chains %', count_purged; - end if; - END LOOP; - - raise debug '% Done. Purged Chains=% Kept Chains=%',(extract(epoch from (clock_timestamp()-time_start))/60)::int,count_purged,cmd_count_kept; - return count_purged; -END; - -$func$ LANGUAGE PLPGSQL; - -CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$ -DECLARE - org_keep_age INTERVAL; - org_use_last BOOL = false; - org_age_is_min BOOL = false; - org_keep_count INT; - - keep_age INTERVAL; - - target_acp RECORD; - circ_chain_head action.circulation%ROWTYPE; - circ_chain_tail action.circulation%ROWTYPE; - - count_purged INT; - num_incomplete INT; - - last_finished TIMESTAMP WITH TIME ZONE; -BEGIN - - count_purged := 0; - - SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; - - SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; - IF org_keep_count IS NULL THEN - RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever - END IF; - - SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; - SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; - - -- First, find copies with more than keep_count non-renewal circs - FOR target_acp IN - SELECT target_copy, - COUNT(*) AS total_real_circs - FROM action.circulation - WHERE parent_circ IS NULL - AND xact_finish IS NOT NULL - GROUP BY target_copy - HAVING COUNT(*) > org_keep_count - LOOP - -- And, for those, select circs that are finished and older than keep_age - FOR circ_chain_head IN - -- For reference, the subquery uses a window function to order the circs newest to oldest and number them - -- The outer query then uses that information to skip the most recent set the library wants to keep - -- End result is we don't care what order they come out in, as they are all potentials for deletion. - SELECT ac.* FROM action.circulation ac JOIN ( - SELECT rank() OVER (ORDER BY xact_start DESC), ac.id - FROM action.circulation ac - WHERE ac.target_copy = target_acp.target_copy - AND ac.parent_circ IS NULL - ORDER BY ac.xact_start ) ranked USING (id) - WHERE ranked.rank > org_keep_count - LOOP - - SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; - SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); - CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; - - IF NOT org_use_last THEN - last_finished := circ_chain_tail.xact_finish; - END IF; - - keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); - - IF org_age_is_min THEN - keep_age := GREATEST( keep_age, org_keep_age ); - END IF; - - CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; - - -- We've passed the purging tests, purge the circ chain starting at the end - -- A trigger should auto-purge the rest of the chain. - DELETE FROM action.circulation WHERE id = circ_chain_tail.id; - - count_purged := count_purged + 1; - - END LOOP; - END LOOP; - - return count_purged; -END; -$func$ LANGUAGE PLPGSQL; - --- delete circ history rows when a user is purged. -CREATE OR REPLACE FUNCTION actor.usr_purge_data( - src_usr IN INTEGER, - specified_dest_usr IN INTEGER -) RETURNS VOID AS $$ -DECLARE - suffix TEXT; - renamable_row RECORD; - dest_usr INTEGER; -BEGIN - - IF specified_dest_usr IS NULL THEN - dest_usr := 1; -- Admin user on stock installs - ELSE - dest_usr := specified_dest_usr; - END IF; - - -- acq.* - UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr; - UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr; - UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr; - DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr; - - -- Update with a rename to avoid collisions - FOR renamable_row in - SELECT id, name - FROM acq.picklist - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE acq.picklist - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr; - UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr; - - -- action.* - DELETE FROM action.circulation WHERE usr = src_usr; - UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr; - UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr; - UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr; - UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr; - UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr; - DELETE FROM action.hold_request WHERE usr = src_usr; - UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr; - UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr; - DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr; - UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr; - DELETE FROM action.survey_response WHERE usr = src_usr; - UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr; - DELETE FROM action.usr_circ_history WHERE usr = src_usr; - - -- actor.* - DELETE FROM actor.card WHERE usr = src_usr; - DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr; - - -- The following update is intended to avoid transient violations of a foreign - -- key constraint, whereby actor.usr_address references itself. It may not be - -- necessary, but it does no harm. - UPDATE actor.usr_address SET replaces = NULL - WHERE usr = src_usr AND replaces IS NOT NULL; - DELETE FROM actor.usr_address WHERE usr = src_usr; - DELETE FROM actor.usr_note WHERE usr = src_usr; - UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr; - DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr; - UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr; - DELETE FROM actor.usr_setting WHERE usr = src_usr; - DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr; - UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr; - - -- asset.* - UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr; - UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr; - UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr; - UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr; - UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr; - - -- auditor.* - DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr; - DELETE FROM auditor.actor_usr_history WHERE id = src_usr; - UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr; - UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr; - UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr; - UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr; - UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr; - UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr; - - -- biblio.* - UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr; - UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr; - UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr; - - -- container.* - -- Update buckets with a rename to avoid collisions - FOR renamable_row in - SELECT id, name - FROM container.biblio_record_entry_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.biblio_record_entry_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - FOR renamable_row in - SELECT id, name - FROM container.call_number_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.call_number_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - FOR renamable_row in - SELECT id, name - FROM container.copy_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.copy_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - FOR renamable_row in - SELECT id, name - FROM container.user_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.user_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - DELETE FROM container.user_bucket_item WHERE target_user = src_usr; - - -- money.* - DELETE FROM money.billable_xact WHERE usr = src_usr; - DELETE FROM money.collections_tracker WHERE usr = src_usr; - UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr; - - -- permission.* - DELETE FROM permission.usr_grp_map WHERE usr = src_usr; - DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr; - DELETE FROM permission.usr_perm_map WHERE usr = src_usr; - DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr; - - -- reporter.* - -- Update with a rename to avoid collisions - BEGIN - FOR renamable_row in - SELECT id, name - FROM reporter.output_folder - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE reporter.output_folder - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - BEGIN - UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - -- Update with a rename to avoid collisions - BEGIN - FOR renamable_row in - SELECT id, name - FROM reporter.report_folder - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE reporter.report_folder - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - BEGIN - UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - BEGIN - UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - -- Update with a rename to avoid collisions - BEGIN - FOR renamable_row in - SELECT id, name - FROM reporter.template_folder - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE reporter.template_folder - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - -- vandelay.* - -- Update with a rename to avoid collisions - FOR renamable_row in - SELECT id, name - FROM vandelay.queue - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE vandelay.queue - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - -- NULL-ify addresses last so other cleanup (e.g. circ anonymization) - -- can access the information before deletion. - UPDATE actor.usr SET - active = FALSE, - card = NULL, - mailing_address = NULL, - billing_address = NULL - WHERE id = src_usr; - -END; -$$ LANGUAGE plpgsql; - -COMMIT; - --- APPLY THESE POST-MIGRATION - -/* - -CREATE TRIGGER action_usr_circ_history_target_copy_trig - AFTER INSERT OR UPDATE ON action.usr_circ_history - FOR EACH ROW EXECUTE PROCEDURE evergreen.fake_fkey_tgr('target_copy'); - - -DROP FUNCTION IF EXISTS action.usr_visible_circs (INTEGER); -DROP FUNCTION IF EXISTS action.usr_visible_circ_copies (INTEGER); - -CREATE INDEX action_usr_circ_history_usr_idx ON action.usr_circ_history (usr); - -*/ - diff --git a/KCLS/sql/schema/revert/patron-co-history-stage-2.sql b/KCLS/sql/schema/revert/patron-co-history-stage-2.sql new file mode 100644 index 0000000000..513880ddb6 --- /dev/null +++ b/KCLS/sql/schema/revert/patron-co-history-stage-2.sql @@ -0,0 +1,308 @@ +-- Revert kcls-evergreen:patron-co-history-table from pg + +BEGIN; + +CREATE OR REPLACE FUNCTION action.purge_circulations_custom () RETURNS INT AS $func$ +DECLARE + usr_keep_age actor.usr_setting%ROWTYPE; + usr_keep_start actor.usr_setting%ROWTYPE; + org_keep_age INTERVAL; + org_use_last BOOL = false; + org_age_is_min BOOL = false; + org_keep_count INT; + + keep_age INTERVAL; + + target_acp RECORD; + circ_chain_head action.circulation%ROWTYPE; + circ_chain_tail action.circulation%ROWTYPE; + + count_purged INT; + num_incomplete INT; + + last_finished TIMESTAMP WITH TIME ZONE; + cmd_main_loop_item int; + cmd_count_kept int; + time_start timestamp with time zone; +BEGIN + time_start = clock_timestamp(); + count_purged := 0; + cmd_count_kept := 0; + cmd_main_loop_item := 0; + + SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; + raise debug 'in_time_start=%; in_time_end=%; org_keep_age=%',in_time_start,in_time_end,org_keep_age; + + SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; + IF org_keep_count IS NULL THEN + RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever + END IF; + + SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; + SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; + + -- And, for those, select circs that are finished and older than keep_age + FOR circ_chain_head IN + SELECT ac.* FROM action.circulation ac + WHERE 1=1 + AND ac.parent_circ IS NULL + AND xact_finish < now() - coalesce(org_keep_age,'2000 years'::interval) + AND xact_finish between in_time_start and in_time_end + LOOP + cmd_main_loop_item := cmd_main_loop_item + 1; + if (cmd_main_loop_item - 1) % 1000 = 0 then + raise debug '% Main loop item %...',(extract(epoch from (clock_timestamp()-time_start))/60)::int,cmd_main_loop_item; + end if; + + SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; + SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); + CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; + + IF NOT org_use_last THEN + last_finished := circ_chain_tail.xact_finish; + END IF; + + -- Now get the user settings, if any, to block purging if the user wants to keep more circs + usr_keep_age.value := NULL; + SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age'; + + usr_keep_start.value := NULL; + SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start'; + + IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN + IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); + ELSE + keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL; + END IF; + ELSIF usr_keep_start.value IS NOT NULL THEN + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); + ELSE + keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); + END IF; + + IF org_age_is_min THEN + keep_age := GREATEST( keep_age, org_keep_age ); + END IF; + + if AGE(NOW(), last_finished) < keep_age then + cmd_count_kept := cmd_count_kept + 1; + end if; + CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; + + -- We've passed the purging tests, purge the circ chain starting at the end + -- A trigger should auto-purge the rest of the chain. + DELETE FROM action.circulation WHERE id = circ_chain_tail.id; + + count_purged := count_purged + 1; + if count_purged % 1000 = 0 then + raise debug 'Purged Chains %', count_purged; + end if; + END LOOP; + + raise debug '% Done. Purged Chains=% Kept Chains=%',(extract(epoch from (clock_timestamp()-time_start))/60)::int,count_purged,cmd_count_kept; + return count_purged; +END; + +$func$ LANGUAGE PLPGSQL; + + +-- TODO: recover these functions +CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$ +DECLARE + usr_keep_age actor.usr_setting%ROWTYPE; + usr_keep_start actor.usr_setting%ROWTYPE; + org_keep_age INTERVAL; + org_use_last BOOL = false; + org_age_is_min BOOL = false; + org_keep_count INT; + + keep_age INTERVAL; + + target_acp RECORD; + circ_chain_head action.circulation%ROWTYPE; + circ_chain_tail action.circulation%ROWTYPE; + + count_purged INT; + num_incomplete INT; + + last_finished TIMESTAMP WITH TIME ZONE; +BEGIN + + count_purged := 0; + + SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; + + SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; + IF org_keep_count IS NULL THEN + RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever + END IF; + + SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; + SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; + + -- First, find copies with more than keep_count non-renewal circs + FOR target_acp IN + SELECT target_copy, + COUNT(*) AS total_real_circs + FROM action.circulation + WHERE parent_circ IS NULL + AND xact_finish IS NOT NULL + GROUP BY target_copy + HAVING COUNT(*) > org_keep_count + LOOP + -- And, for those, select circs that are finished and older than keep_age + FOR circ_chain_head IN + -- For reference, the subquery uses a window function to order the circs newest to oldest and number them + -- The outer query then uses that information to skip the most recent set the library wants to keep + -- End result is we don't care what order they come out in, as they are all potentials for deletion. + SELECT ac.* FROM action.circulation ac JOIN ( + SELECT rank() OVER (ORDER BY xact_start DESC), ac.id + FROM action.circulation ac + WHERE ac.target_copy = target_acp.target_copy + AND ac.parent_circ IS NULL + ORDER BY ac.xact_start ) ranked USING (id) + WHERE ranked.rank > org_keep_count + LOOP + + SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; + SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); + CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; + + IF NOT org_use_last THEN + last_finished := circ_chain_tail.xact_finish; + END IF; + + -- Now get the user settings, if any, to block purging if the user wants to keep more circs + usr_keep_age.value := NULL; + SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age'; + + usr_keep_start.value := NULL; + SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start'; + + IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN + IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); + ELSE + keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL; + END IF; + ELSIF usr_keep_start.value IS NOT NULL THEN + keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); + ELSE + keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); + END IF; + + IF org_age_is_min THEN + keep_age := GREATEST( keep_age, org_keep_age ); + END IF; + + CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; + + -- We've passed the purging tests, purge the circ chain starting at the end + -- A trigger should auto-purge the rest of the chain. + DELETE FROM action.circulation WHERE id = circ_chain_tail.id; + + count_purged := count_purged + 1; + + END LOOP; + END LOOP; + + return count_purged; +END; +$func$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INTEGER) + RETURNS SETOF action.circulation AS $FUNC$ +DECLARE + c action.circulation%ROWTYPE; + view_age INTERVAL; + usr_view_age actor.usr_setting%ROWTYPE; + usr_view_start actor.usr_setting%ROWTYPE; +BEGIN + SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age'; + SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start'; + + IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN + -- User opted in and supplied a retention age + IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + ELSE + view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; + END IF; + ELSIF usr_view_start.value IS NOT NULL THEN + -- User opted in + view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); + ELSE + -- User did not opt in + RETURN; + END IF; + + FOR c IN + SELECT * + FROM action.circulation + WHERE usr = usr_id + AND parent_circ IS NULL + AND xact_start > NOW() - view_age + ORDER BY xact_start DESC + LOOP + RETURN NEXT c; + END LOOP; + + RETURN; +END; +$FUNC$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies(integer) RETURNS SETOF bigint + LANGUAGE sql ROWS 10 + AS $_$ + SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1) +$_$; + + +UPDATE action_trigger.event_definition SET template = +$$ +[%- USE date -%] +[%- SET user = target.0.usr -%] +To: [%- params.recipient_email || user.email %] +From: [%- params.sender_email || default_sender %] +Subject: Circulation History + +[% FOR circ IN target %] + [% helpers.get_copy_bib_basics(circ.target_copy.id).title %] + Barcode: [% circ.target_copy.barcode %] + Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %] + Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %] + Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %] +[% END %] +$$ WHERE id = 25; + +UPDATE action_trigger.event_definition SET template = +$$ +[%- USE date -%] +
+ +
[% date.format %]
+
+ + [% user.family_name %], [% user.first_given_name %] +
    + [% FOR circ IN target %] +
  1. +
    [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
    +
    Barcode: [% circ.target_copy.barcode %]
    +
    Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
    +
    Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
    +
    Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
    +
    2. + [% END %] +
+
+$$ WHERE id = 26; + +UPDATE action_trigger.hook + SET core_type = 'circ' + WHERE key ~ '^circ.format.history.'; + +DELETE FROM config.upgrade_log WHERE version = '0960'; + +COMMIT; diff --git a/KCLS/sql/schema/revert/patron-co-history-table.sql b/KCLS/sql/schema/revert/patron-co-history-table.sql deleted file mode 100644 index 821ac3b255..0000000000 --- a/KCLS/sql/schema/revert/patron-co-history-table.sql +++ /dev/null @@ -1,639 +0,0 @@ --- Revert kcls-evergreen:patron-co-history-table from pg - -BEGIN; - -CREATE OR REPLACE FUNCTION action.purge_circulations_custom () RETURNS INT AS $func$ -DECLARE - usr_keep_age actor.usr_setting%ROWTYPE; - usr_keep_start actor.usr_setting%ROWTYPE; - org_keep_age INTERVAL; - org_use_last BOOL = false; - org_age_is_min BOOL = false; - org_keep_count INT; - - keep_age INTERVAL; - - target_acp RECORD; - circ_chain_head action.circulation%ROWTYPE; - circ_chain_tail action.circulation%ROWTYPE; - - count_purged INT; - num_incomplete INT; - - last_finished TIMESTAMP WITH TIME ZONE; - cmd_main_loop_item int; - cmd_count_kept int; - time_start timestamp with time zone; -BEGIN - time_start = clock_timestamp(); - count_purged := 0; - cmd_count_kept := 0; - cmd_main_loop_item := 0; - - SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; - raise debug 'in_time_start=%; in_time_end=%; org_keep_age=%',in_time_start,in_time_end,org_keep_age; - - SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; - IF org_keep_count IS NULL THEN - RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever - END IF; - - SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; - SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; - - -- And, for those, select circs that are finished and older than keep_age - FOR circ_chain_head IN - SELECT ac.* FROM action.circulation ac - WHERE 1=1 - AND ac.parent_circ IS NULL - AND xact_finish < now() - coalesce(org_keep_age,'2000 years'::interval) - AND xact_finish between in_time_start and in_time_end - LOOP - cmd_main_loop_item := cmd_main_loop_item + 1; - if (cmd_main_loop_item - 1) % 1000 = 0 then - raise debug '% Main loop item %...',(extract(epoch from (clock_timestamp()-time_start))/60)::int,cmd_main_loop_item; - end if; - - SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; - SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); - CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; - - IF NOT org_use_last THEN - last_finished := circ_chain_tail.xact_finish; - END IF; - - -- Now get the user settings, if any, to block purging if the user wants to keep more circs - usr_keep_age.value := NULL; - SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age'; - - usr_keep_start.value := NULL; - SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start'; - - IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN - IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN - keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); - ELSE - keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL; - END IF; - ELSIF usr_keep_start.value IS NOT NULL THEN - keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); - ELSE - keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); - END IF; - - IF org_age_is_min THEN - keep_age := GREATEST( keep_age, org_keep_age ); - END IF; - - if AGE(NOW(), last_finished) < keep_age then - cmd_count_kept := cmd_count_kept + 1; - end if; - CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; - - -- We've passed the purging tests, purge the circ chain starting at the end - -- A trigger should auto-purge the rest of the chain. - DELETE FROM action.circulation WHERE id = circ_chain_tail.id; - - count_purged := count_purged + 1; - if count_purged % 1000 = 0 then - raise debug 'Purged Chains %', count_purged; - end if; - END LOOP; - - raise debug '% Done. Purged Chains=% Kept Chains=%',(extract(epoch from (clock_timestamp()-time_start))/60)::int,count_purged,cmd_count_kept; - return count_purged; -END; - -$func$ LANGUAGE PLPGSQL; - - --- TODO: recover these functions -CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$ -DECLARE - usr_keep_age actor.usr_setting%ROWTYPE; - usr_keep_start actor.usr_setting%ROWTYPE; - org_keep_age INTERVAL; - org_use_last BOOL = false; - org_age_is_min BOOL = false; - org_keep_count INT; - - keep_age INTERVAL; - - target_acp RECORD; - circ_chain_head action.circulation%ROWTYPE; - circ_chain_tail action.circulation%ROWTYPE; - - count_purged INT; - num_incomplete INT; - - last_finished TIMESTAMP WITH TIME ZONE; -BEGIN - - count_purged := 0; - - SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled; - - SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled; - IF org_keep_count IS NULL THEN - RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever - END IF; - - SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished'; - SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min'; - - -- First, find copies with more than keep_count non-renewal circs - FOR target_acp IN - SELECT target_copy, - COUNT(*) AS total_real_circs - FROM action.circulation - WHERE parent_circ IS NULL - AND xact_finish IS NOT NULL - GROUP BY target_copy - HAVING COUNT(*) > org_keep_count - LOOP - -- And, for those, select circs that are finished and older than keep_age - FOR circ_chain_head IN - -- For reference, the subquery uses a window function to order the circs newest to oldest and number them - -- The outer query then uses that information to skip the most recent set the library wants to keep - -- End result is we don't care what order they come out in, as they are all potentials for deletion. - SELECT ac.* FROM action.circulation ac JOIN ( - SELECT rank() OVER (ORDER BY xact_start DESC), ac.id - FROM action.circulation ac - WHERE ac.target_copy = target_acp.target_copy - AND ac.parent_circ IS NULL - ORDER BY ac.xact_start ) ranked USING (id) - WHERE ranked.rank > org_keep_count - LOOP - - SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1; - SELECT COUNT(CASE WHEN xact_finish IS NULL THEN 1 ELSE NULL END), MAX(xact_finish) INTO num_incomplete, last_finished FROM action.circ_chain(circ_chain_head.id); - CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0; - - IF NOT org_use_last THEN - last_finished := circ_chain_tail.xact_finish; - END IF; - - -- Now get the user settings, if any, to block purging if the user wants to keep more circs - usr_keep_age.value := NULL; - SELECT * INTO usr_keep_age FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_age'; - - usr_keep_start.value := NULL; - SELECT * INTO usr_keep_start FROM actor.usr_setting WHERE usr = circ_chain_head.usr AND name = 'history.circ.retention_start'; - - IF usr_keep_age.value IS NOT NULL AND usr_keep_start.value IS NOT NULL THEN - IF oils_json_to_text(usr_keep_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ) THEN - keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); - ELSE - keep_age := oils_json_to_text(usr_keep_age.value)::INTERVAL; - END IF; - ELSIF usr_keep_start.value IS NOT NULL THEN - keep_age := AGE(NOW(), oils_json_to_text(usr_keep_start.value)::TIMESTAMPTZ); - ELSE - keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL ); - END IF; - - IF org_age_is_min THEN - keep_age := GREATEST( keep_age, org_keep_age ); - END IF; - - CONTINUE WHEN AGE(NOW(), last_finished) < keep_age; - - -- We've passed the purging tests, purge the circ chain starting at the end - -- A trigger should auto-purge the rest of the chain. - DELETE FROM action.circulation WHERE id = circ_chain_tail.id; - - count_purged := count_purged + 1; - - END LOOP; - END LOOP; - - return count_purged; -END; -$func$ LANGUAGE PLPGSQL; - -CREATE OR REPLACE FUNCTION actor.usr_purge_data( - src_usr IN INTEGER, - specified_dest_usr IN INTEGER -) RETURNS VOID AS $$ -DECLARE - suffix TEXT; - renamable_row RECORD; - dest_usr INTEGER; -BEGIN - IF specified_dest_usr IS NULL THEN - dest_usr := 1; -- Admin user on stock installs - ELSE - dest_usr := specified_dest_usr; - END IF; - - -- acq.* - UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr; - UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr; - UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr; - DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr; - - -- Update with a rename to avoid collisions - FOR renamable_row in - SELECT id, name - FROM acq.picklist - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE acq.picklist - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr; - UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr; - UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr; - UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr; - - -- action.* - DELETE FROM action.circulation WHERE usr = src_usr; - UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr; - UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr; - UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr; - UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr; - UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr; - DELETE FROM action.hold_request WHERE usr = src_usr; - UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr; - UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr; - DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr; - UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr; - DELETE FROM action.survey_response WHERE usr = src_usr; - UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr; - - -- actor.* - DELETE FROM actor.card WHERE usr = src_usr; - DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr; - - -- The following update is intended to avoid transient violations of a foreign - -- key constraint, whereby actor.usr_address references itself. It may not be - -- necessary, but it does no harm. - UPDATE actor.usr_address SET replaces = NULL - WHERE usr = src_usr AND replaces IS NOT NULL; - DELETE FROM actor.usr_address WHERE usr = src_usr; - DELETE FROM actor.usr_note WHERE usr = src_usr; - UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr; - DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr; - UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr; - DELETE FROM actor.usr_setting WHERE usr = src_usr; - DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr; - UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr; - - -- asset.* - UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr; - UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr; - UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr; - UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr; - UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr; - - -- auditor.* - DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr; - DELETE FROM auditor.actor_usr_history WHERE id = src_usr; - UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr; - UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr; - UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr; - UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr; - UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr; - UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr; - - -- biblio.* - UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr; - UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr; - UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr; - UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr; - - -- container.* - -- Update buckets with a rename to avoid collisions - FOR renamable_row in - SELECT id, name - FROM container.biblio_record_entry_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.biblio_record_entry_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - FOR renamable_row in - SELECT id, name - FROM container.call_number_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.call_number_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - FOR renamable_row in - SELECT id, name - FROM container.copy_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.copy_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - FOR renamable_row in - SELECT id, name - FROM container.user_bucket - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE container.user_bucket - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - DELETE FROM container.user_bucket_item WHERE target_user = src_usr; - - -- money.* - DELETE FROM money.billable_xact WHERE usr = src_usr; - DELETE FROM money.collections_tracker WHERE usr = src_usr; - UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr; - - -- permission.* - DELETE FROM permission.usr_grp_map WHERE usr = src_usr; - DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr; - DELETE FROM permission.usr_perm_map WHERE usr = src_usr; - DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr; - - -- reporter.* - -- Update with a rename to avoid collisions - BEGIN - FOR renamable_row in - SELECT id, name - FROM reporter.output_folder - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE reporter.output_folder - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - BEGIN - UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - -- Update with a rename to avoid collisions - BEGIN - FOR renamable_row in - SELECT id, name - FROM reporter.report_folder - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE reporter.report_folder - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - BEGIN - UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - BEGIN - UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - -- Update with a rename to avoid collisions - BEGIN - FOR renamable_row in - SELECT id, name - FROM reporter.template_folder - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE reporter.template_folder - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - EXCEPTION WHEN undefined_table THEN - -- do nothing - END; - - -- vandelay.* - -- Update with a rename to avoid collisions - FOR renamable_row in - SELECT id, name - FROM vandelay.queue - WHERE owner = src_usr - LOOP - suffix := ' (' || src_usr || ')'; - LOOP - BEGIN - UPDATE vandelay.queue - SET owner = dest_usr, name = name || suffix - WHERE id = renamable_row.id; - EXCEPTION WHEN unique_violation THEN - suffix := suffix || ' '; - CONTINUE; - END; - EXIT; - END LOOP; - END LOOP; - - -- NULL-ify addresses last so other cleanup (e.g. circ anonymization) - -- can access the information before deletion. - UPDATE actor.usr SET - active = FALSE, - card = NULL, - mailing_address = NULL, - billing_address = NULL - WHERE id = src_usr; - -END; -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION action.usr_visible_circs (usr_id INTEGER) - RETURNS SETOF action.circulation AS $FUNC$ -DECLARE - c action.circulation%ROWTYPE; - view_age INTERVAL; - usr_view_age actor.usr_setting%ROWTYPE; - usr_view_start actor.usr_setting%ROWTYPE; -BEGIN - SELECT * INTO usr_view_age FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_age'; - SELECT * INTO usr_view_start FROM actor.usr_setting WHERE usr = usr_id AND name = 'history.circ.retention_start'; - - IF usr_view_age.value IS NOT NULL AND usr_view_start.value IS NOT NULL THEN - -- User opted in and supplied a retention age - IF oils_json_to_text(usr_view_age.value)::INTERVAL > AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ) THEN - view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); - ELSE - view_age := oils_json_to_text(usr_view_age.value)::INTERVAL; - END IF; - ELSIF usr_view_start.value IS NOT NULL THEN - -- User opted in - view_age := AGE(NOW(), oils_json_to_text(usr_view_start.value)::TIMESTAMPTZ); - ELSE - -- User did not opt in - RETURN; - END IF; - - FOR c IN - SELECT * - FROM action.circulation - WHERE usr = usr_id - AND parent_circ IS NULL - AND xact_start > NOW() - view_age - ORDER BY xact_start DESC - LOOP - RETURN NEXT c; - END LOOP; - - RETURN; -END; -$FUNC$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies(integer) RETURNS SETOF bigint - LANGUAGE sql ROWS 10 - AS $_$ - SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1) -$_$; - - -UPDATE action_trigger.event_definition SET template = -$$ -[%- USE date -%] -[%- SET user = target.0.usr -%] -To: [%- params.recipient_email || user.email %] -From: [%- params.sender_email || default_sender %] -Subject: Circulation History - -[% FOR circ IN target %] - [% helpers.get_copy_bib_basics(circ.target_copy.id).title %] - Barcode: [% circ.target_copy.barcode %] - Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %] - Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %] - Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %] -[% END %] -$$ WHERE id = 25; - -UPDATE action_trigger.event_definition SET template = -$$ -[%- USE date -%] -
- -
[% date.format %]
-
- - [% user.family_name %], [% user.first_given_name %] -
    - [% FOR circ IN target %] -
  1. -
    [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
    -
    Barcode: [% circ.target_copy.barcode %]
    -
    Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
    -
    Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
    -
    Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
    -
    2. - [% END %] -
-
-$$ WHERE id = 26; - -UPDATE action_trigger.hook - SET core_type = 'circ' - WHERE key ~ '^circ.format.history.'; - -DROP TRIGGER maintain_usr_circ_history_tgr ON action.circulation; -DROP FUNCTION action.maintain_usr_circ_history(); -DROP TABLE action.usr_circ_history; - -DROP FUNCTION action.migrate_usr_circ_history(INT, INT); - -DELETE FROM config.upgrade_log WHERE version = '0960'; - -COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 9ac4047aad..6001dfc3b1 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -45,5 +45,5 @@ revert-custom-qstats [backstage-exports-continued] 2017-02-06T22:09:42Z Bill Eri patron-co-history-stage-1 [sip-act-type-freegalsip] 2016-09-01T19:36:46Z Bill Erickson # Patron checkout history base tables and migration func 2.7-to-2.9-upgrade-part-2 [2.7-to-2.9-upgrade] 2016-12-12T15:19:07Z Bill Erickson # 2.7 to 2.9 Upgrade Secondary Updates revert-custom-hold-counts [backstage-exports-continued] 2017-02-06T21:33:32Z Bill Erickson # Remove custom hold count materialized data -patron-co-history-table [sip-activity-types] 2016-05-13T18:02:48Z Bill Erickson # Back-porting LP#1527342: Patron reading history -aged-circs-api [patron-co-history-table] 2016-08-25T14:14:36Z Bill Erickson # Aged circs API access +patron-co-history-stage-2 [sip-activity-types] 2016-05-13T18:02:48Z Bill Erickson # Back-porting LP#1527342: Patron reading history +aged-circs-api [patron-co-history-stage-2] 2016-08-25T14:14:36Z Bill Erickson # Aged circs API access diff --git a/KCLS/sql/schema/verify/patron-co-history-table.sql b/KCLS/sql/schema/verify/patron-co-history-stage-2.sql similarity index 100% rename from KCLS/sql/schema/verify/patron-co-history-table.sql rename to KCLS/sql/schema/verify/patron-co-history-stage-2.sql -- 2.11.0