4 -- TODO process to delete history items once the age threshold
5 -- history.circ.retention_age is reached?
7 SELECT evergreen.upgrade_deps_block_check('0960', :eg_version);
9 CREATE TABLE action.usr_circ_history (
10 id BIGSERIAL PRIMARY KEY,
11 usr INTEGER NOT NULL REFERENCES actor.usr(id)
12 DEFERRABLE INITIALLY DEFERRED,
13 xact_start TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
14 target_copy BIGINT NOT NULL,
15 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
16 checkin_time TIMESTAMP WITH TIME ZONE,
17 source_circ BIGINT REFERENCES action.circulation(id)
18 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
21 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
22 RETURNS TRIGGER AS $FUNK$
28 -- Any retention value signifies history is enabled.
29 -- This assumes that clearing these values via external
30 -- process deletes the action.usr_circ_history rows.
31 -- TODO: replace these settings w/ a single bool setting?
32 PERFORM 1 FROM actor.usr_setting
33 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
34 'history.circ.retention_age',
35 'history.circ.retention_start'
42 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
43 -- Starting a new circulation. Insert the history row.
44 INSERT INTO action.usr_circ_history
45 (usr, xact_start, target_copy, due_date, source_circ)
57 -- find the first and last circs in the circ chain
58 -- for the currently modified circ.
59 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
60 IF first_circ IS NULL THEN
61 first_circ := cur_circ;
64 -- Allow the loop to continue so that at as the loop
65 -- completes cur_circ points to the final circulation.
68 IF NEW.id <> cur_circ THEN
69 -- Modifying an intermediate circ. Ignore it.
73 -- Update the due_date/checkin_time on the history row if the current
74 -- circ is the last circ in the chain and an update is warranted.
76 UPDATE action.usr_circ_history
78 due_date = NEW.due_date,
79 checkin_time = NEW.checkin_time
81 source_circ = first_circ
83 due_date <> NEW.due_date OR (
84 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
85 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
86 (checkin_time <> NEW.checkin_time)
91 $FUNK$ LANGUAGE PLPGSQL;
93 CREATE TRIGGER maintain_usr_circ_history_tgr
94 AFTER INSERT OR UPDATE ON action.circulation
95 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
97 UPDATE action_trigger.hook
98 SET core_type = 'auch'
99 WHERE key ~ '^circ.format.history.';
101 UPDATE action_trigger.event_definition SET template =
104 [%- SET user = target.0.usr -%]
105 To: [%- params.recipient_email || user.email %]
106 From: [%- params.sender_email || default_sender %]
107 Subject: Circulation History
109 [% FOR circ IN target %]
110 [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
111 Barcode: [% circ.target_copy.barcode %]
112 Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
113 Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
116 helpers.format_date(circ.checkin_time), '%Y-%m-%d')
117 IF circ.checkin_time;
121 WHERE id = 25 AND template =
124 [%- SET user = target.0.usr -%]
125 To: [%- params.recipient_email || user.email %]
126 From: [%- params.sender_email || default_sender %]
127 Subject: Circulation History
129 [% FOR circ IN target %]
130 [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
131 Barcode: [% circ.target_copy.barcode %]
132 Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
133 Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
134 Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
138 -- avoid TT undef date errors
139 UPDATE action_trigger.event_definition SET template =
143 <style> li { padding: 8px; margin 5px; }</style>
144 <div>[% date.format %]</div>
147 [% user.family_name %], [% user.first_given_name %]
149 [% FOR circ IN target %]
151 <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
152 <div>Barcode: [% circ.target_copy.barcode %]</div>
153 <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
154 <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
157 helpers.format_date(circ.checkin_time), '%Y-%m-%d')
158 IF circ.checkin_time; -%]
165 WHERE id = 26 AND template = -- only replace template if it matches stock
169 <style> li { padding: 8px; margin 5px; }</style>
170 <div>[% date.format %]</div>
173 [% user.family_name %], [% user.first_given_name %]
175 [% FOR circ IN target %]
177 <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
178 <div>Barcode: [% circ.target_copy.barcode %]</div>
179 <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
180 <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
181 <div>Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]</div>
188 -- NOTE: ^-- stock CSV template does not include checkin_time, so
189 -- no modifications are required.
191 -- Create circ history rows for existing circ history data.
195 cur_circ action.circulation%ROWTYPE;
196 last_circ action.circulation%ROWTYPE;
197 counter INTEGER DEFAULT 1;
201 'Migrating circ history for % users. This might take a while...',
202 (SELECT COUNT(DISTINCT(au.id)) FROM actor.usr au
203 JOIN actor.usr_setting aus ON (aus.usr = au.id)
204 WHERE NOT au.deleted AND
205 aus.name ~ '^history.circ.retention_');
208 SELECT DISTINCT(au.id)
210 JOIN actor.usr_setting aus ON (aus.usr = au.id)
211 WHERE NOT au.deleted AND
212 aus.name ~ '^history.circ.retention_' LOOP
214 FOR cur_circ IN SELECT * FROM action.usr_visible_circs(cur_usr) LOOP
216 PERFORM TRUE FROM asset.copy WHERE id = cur_circ.target_copy;
218 -- Avoid inserting a circ history row when the circulated
219 -- item has been (forcibly) removed from the database.
224 -- Find the last circ in the circ chain.
225 SELECT INTO last_circ *
226 FROM action.circ_chain(cur_circ.id)
227 ORDER BY xact_start DESC LIMIT 1;
229 -- Create the history row.
230 -- It's OK if last_circ = cur_circ
231 INSERT INTO action.usr_circ_history
232 (usr, xact_start, target_copy,
233 due_date, checkin_time, source_circ)
237 cur_circ.target_copy,
239 last_circ.checkin_time,
243 -- useful for alleviating administrator anxiety.
244 IF counter % 10000 = 0 THEN
245 RAISE NOTICE 'Migrated history for % total circs', counter;
248 counter := counter + 1;
255 DROP FUNCTION IF EXISTS action.usr_visible_circs (INTEGER);
256 DROP FUNCTION IF EXISTS action.usr_visible_circ_copies (INTEGER);
258 -- remove user retention age checks
259 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
261 org_keep_age INTERVAL;
262 org_use_last BOOL = false;
263 org_age_is_min BOOL = false;
269 circ_chain_head action.circulation%ROWTYPE;
270 circ_chain_tail action.circulation%ROWTYPE;
275 last_finished TIMESTAMP WITH TIME ZONE;
280 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
282 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
283 IF org_keep_count IS NULL THEN
284 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
287 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
288 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
290 -- First, find copies with more than keep_count non-renewal circs
293 COUNT(*) AS total_real_circs
294 FROM action.circulation
295 WHERE parent_circ IS NULL
296 AND xact_finish IS NOT NULL
298 HAVING COUNT(*) > org_keep_count
300 -- And, for those, select circs that are finished and older than keep_age
301 FOR circ_chain_head IN
302 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
303 -- The outer query then uses that information to skip the most recent set the library wants to keep
304 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
305 SELECT ac.* FROM action.circulation ac JOIN (
306 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
307 FROM action.circulation ac
308 WHERE ac.target_copy = target_acp.target_copy
309 AND ac.parent_circ IS NULL
310 ORDER BY ac.xact_start ) ranked USING (id)
311 WHERE ranked.rank > org_keep_count
314 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
315 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);
316 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
318 IF NOT org_use_last THEN
319 last_finished := circ_chain_tail.xact_finish;
322 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
324 IF org_age_is_min THEN
325 keep_age := GREATEST( keep_age, org_keep_age );
328 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
330 -- We've passed the purging tests, purge the circ chain starting at the end
331 -- A trigger should auto-purge the rest of the chain.
332 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
334 count_purged := count_purged + 1;
341 $func$ LANGUAGE PLPGSQL;
343 -- delete circ history rows when a user is purged.
344 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
346 specified_dest_usr IN INTEGER
350 renamable_row RECORD;
354 IF specified_dest_usr IS NULL THEN
355 dest_usr := 1; -- Admin user on stock installs
357 dest_usr := specified_dest_usr;
361 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
362 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
363 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
364 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
365 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
366 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
367 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
369 -- Update with a rename to avoid collisions
373 WHERE owner = src_usr
375 suffix := ' (' || src_usr || ')';
379 SET owner = dest_usr, name = name || suffix
380 WHERE id = renamable_row.id;
381 EXCEPTION WHEN unique_violation THEN
382 suffix := suffix || ' ';
389 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
390 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
391 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
392 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
393 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
394 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
395 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
396 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
399 DELETE FROM action.circulation WHERE usr = src_usr;
400 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
401 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
402 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
403 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
404 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
405 DELETE FROM action.hold_request WHERE usr = src_usr;
406 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
407 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
408 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
409 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
410 DELETE FROM action.survey_response WHERE usr = src_usr;
411 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
412 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
415 DELETE FROM actor.card WHERE usr = src_usr;
416 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
418 -- The following update is intended to avoid transient violations of a foreign
419 -- key constraint, whereby actor.usr_address references itself. It may not be
420 -- necessary, but it does no harm.
421 UPDATE actor.usr_address SET replaces = NULL
422 WHERE usr = src_usr AND replaces IS NOT NULL;
423 DELETE FROM actor.usr_address WHERE usr = src_usr;
424 DELETE FROM actor.usr_note WHERE usr = src_usr;
425 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
426 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
427 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
428 DELETE FROM actor.usr_setting WHERE usr = src_usr;
429 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
430 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
433 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
434 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
435 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
436 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
437 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
438 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
441 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
442 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
443 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
444 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
445 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
446 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
447 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
448 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
451 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
452 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
453 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
454 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
457 -- Update buckets with a rename to avoid collisions
460 FROM container.biblio_record_entry_bucket
461 WHERE owner = src_usr
463 suffix := ' (' || src_usr || ')';
466 UPDATE container.biblio_record_entry_bucket
467 SET owner = dest_usr, name = name || suffix
468 WHERE id = renamable_row.id;
469 EXCEPTION WHEN unique_violation THEN
470 suffix := suffix || ' ';
479 FROM container.call_number_bucket
480 WHERE owner = src_usr
482 suffix := ' (' || src_usr || ')';
485 UPDATE container.call_number_bucket
486 SET owner = dest_usr, name = name || suffix
487 WHERE id = renamable_row.id;
488 EXCEPTION WHEN unique_violation THEN
489 suffix := suffix || ' ';
498 FROM container.copy_bucket
499 WHERE owner = src_usr
501 suffix := ' (' || src_usr || ')';
504 UPDATE container.copy_bucket
505 SET owner = dest_usr, name = name || suffix
506 WHERE id = renamable_row.id;
507 EXCEPTION WHEN unique_violation THEN
508 suffix := suffix || ' ';
517 FROM container.user_bucket
518 WHERE owner = src_usr
520 suffix := ' (' || src_usr || ')';
523 UPDATE container.user_bucket
524 SET owner = dest_usr, name = name || suffix
525 WHERE id = renamable_row.id;
526 EXCEPTION WHEN unique_violation THEN
527 suffix := suffix || ' ';
534 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
537 DELETE FROM money.billable_xact WHERE usr = src_usr;
538 DELETE FROM money.collections_tracker WHERE usr = src_usr;
539 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
542 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
543 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
544 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
545 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
548 -- Update with a rename to avoid collisions
552 FROM reporter.output_folder
553 WHERE owner = src_usr
555 suffix := ' (' || src_usr || ')';
558 UPDATE reporter.output_folder
559 SET owner = dest_usr, name = name || suffix
560 WHERE id = renamable_row.id;
561 EXCEPTION WHEN unique_violation THEN
562 suffix := suffix || ' ';
568 EXCEPTION WHEN undefined_table THEN
573 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
574 EXCEPTION WHEN undefined_table THEN
578 -- Update with a rename to avoid collisions
582 FROM reporter.report_folder
583 WHERE owner = src_usr
585 suffix := ' (' || src_usr || ')';
588 UPDATE reporter.report_folder
589 SET owner = dest_usr, name = name || suffix
590 WHERE id = renamable_row.id;
591 EXCEPTION WHEN unique_violation THEN
592 suffix := suffix || ' ';
598 EXCEPTION WHEN undefined_table THEN
603 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
604 EXCEPTION WHEN undefined_table THEN
609 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
610 EXCEPTION WHEN undefined_table THEN
614 -- Update with a rename to avoid collisions
618 FROM reporter.template_folder
619 WHERE owner = src_usr
621 suffix := ' (' || src_usr || ')';
624 UPDATE reporter.template_folder
625 SET owner = dest_usr, name = name || suffix
626 WHERE id = renamable_row.id;
627 EXCEPTION WHEN unique_violation THEN
628 suffix := suffix || ' ';
634 EXCEPTION WHEN undefined_table THEN
639 -- Update with a rename to avoid collisions
643 WHERE owner = src_usr
645 suffix := ' (' || src_usr || ')';
648 UPDATE vandelay.queue
649 SET owner = dest_usr, name = name || suffix
650 WHERE id = renamable_row.id;
651 EXCEPTION WHEN unique_violation THEN
652 suffix := suffix || ' ';
659 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
660 -- can access the information before deletion.
664 mailing_address = NULL,
665 billing_address = NULL