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 REFERENCES asset.copy(id)
15 DEFERRABLE INITIALLY DEFERRED,
16 due_date TIMESTAMP WITH TIME ZONE NOT NULL,
17 checkin_time TIMESTAMP WITH TIME ZONE,
18 source_circ BIGINT REFERENCES action.circulation(id)
19 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
22 CREATE OR REPLACE FUNCTION action.maintain_usr_circ_history()
23 RETURNS TRIGGER AS $FUNK$
29 -- Any retention value signifies history is enabled.
30 -- This assumes that clearing these values via external
31 -- process deletes the action.usr_circ_history rows.
32 -- TODO: replace these settings w/ a single bool setting?
33 PERFORM 1 FROM actor.usr_setting
34 WHERE usr = NEW.usr AND value IS NOT NULL AND name IN (
35 'history.circ.retention_age',
36 'history.circ.retention_start'
43 IF TG_OP = 'INSERT' AND NEW.parent_circ IS NULL THEN
44 -- Starting a new circulation. Insert the history row.
45 INSERT INTO action.usr_circ_history
46 (usr, xact_start, target_copy, due_date, source_circ)
58 -- find the first and last circs in the circ chain
59 -- for the currently modified circ.
60 FOR cur_circ IN SELECT id FROM action.circ_chain(NEW.id) LOOP
61 IF first_circ IS NULL THEN
62 first_circ := cur_circ;
65 -- Allow the loop to continue so that at as the loop
66 -- completes cur_circ points to the final circulation.
69 IF NEW.id <> cur_circ THEN
70 -- Modifying an intermediate circ. Ignore it.
74 -- Update the due_date/checkin_time on the history row if the current
75 -- circ is the last circ in the chain and an update is warranted.
77 UPDATE action.usr_circ_history
79 due_date = NEW.due_date,
80 checkin_time = NEW.checkin_time
82 source_circ = first_circ
84 due_date <> NEW.due_date OR (
85 (checkin_time IS NULL AND NEW.checkin_time IS NOT NULL) OR
86 (checkin_time IS NOT NULL AND NEW.checkin_time IS NULL) OR
87 (checkin_time <> NEW.checkin_time)
92 $FUNK$ LANGUAGE PLPGSQL;
94 CREATE TRIGGER maintain_usr_circ_history_tgr
95 AFTER INSERT OR UPDATE ON action.circulation
96 FOR EACH ROW EXECUTE PROCEDURE action.maintain_usr_circ_history();
98 UPDATE action_trigger.hook
99 SET core_type = 'auch'
100 WHERE key ~ '^circ.format.history.';
102 UPDATE action_trigger.event_definition SET template =
105 [%- SET user = target.0.usr -%]
106 To: [%- params.recipient_email || user.email %]
107 From: [%- params.sender_email || default_sender %]
108 Subject: Circulation History
110 [% FOR circ IN target %]
111 [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
112 Barcode: [% circ.target_copy.barcode %]
113 Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
114 Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
117 helpers.format_date(circ.checkin_time), '%Y-%m-%d')
118 IF circ.checkin_time;
122 WHERE id = 25 AND template =
125 [%- SET user = target.0.usr -%]
126 To: [%- params.recipient_email || user.email %]
127 From: [%- params.sender_email || default_sender %]
128 Subject: Circulation History
130 [% FOR circ IN target %]
131 [% helpers.get_copy_bib_basics(circ.target_copy.id).title %]
132 Barcode: [% circ.target_copy.barcode %]
133 Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]
134 Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]
135 Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]
139 -- avoid TT undef date errors
140 UPDATE action_trigger.event_definition SET template =
144 <style> li { padding: 8px; margin 5px; }</style>
145 <div>[% date.format %]</div>
148 [% user.family_name %], [% user.first_given_name %]
150 [% FOR circ IN target %]
152 <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
153 <div>Barcode: [% circ.target_copy.barcode %]</div>
154 <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
155 <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
158 helpers.format_date(circ.checkin_time), '%Y-%m-%d')
159 IF circ.checkin_time; -%]
166 WHERE id = 26 AND template = -- only replace template if it matches stock
170 <style> li { padding: 8px; margin 5px; }</style>
171 <div>[% date.format %]</div>
174 [% user.family_name %], [% user.first_given_name %]
176 [% FOR circ IN target %]
178 <div>[% helpers.get_copy_bib_basics(circ.target_copy.id).title %]</div>
179 <div>Barcode: [% circ.target_copy.barcode %]</div>
180 <div>Checked Out: [% date.format(helpers.format_date(circ.xact_start), '%Y-%m-%d') %]</div>
181 <div>Due Date: [% date.format(helpers.format_date(circ.due_date), '%Y-%m-%d') %]</div>
182 <div>Returned: [% date.format(helpers.format_date(circ.checkin_time), '%Y-%m-%d') %]</div>
189 -- NOTE: ^-- stock CSV template does not include checkin_time, so
190 -- no modifications are required.
192 -- Create circ history rows for existing circ history data.
196 cur_circ action.circulation%ROWTYPE;
197 last_circ action.circulation%ROWTYPE;
198 counter INTEGER DEFAULT 1;
202 'Migrating circ history for % users. This might take a while...',
203 (SELECT COUNT(DISTINCT(au.id)) FROM actor.usr au
204 JOIN actor.usr_setting aus ON (aus.usr = au.id)
205 WHERE NOT au.deleted AND
206 aus.name ~ '^history.circ.retention_');
209 SELECT DISTINCT(au.id)
211 JOIN actor.usr_setting aus ON (aus.usr = au.id)
212 WHERE NOT au.deleted AND
213 aus.name ~ '^history.circ.retention_' LOOP
215 FOR cur_circ IN SELECT * FROM action.usr_visible_circs(cur_usr) LOOP
217 -- Find the last circ in the circ chain.
218 SELECT INTO last_circ *
219 FROM action.circ_chain(cur_circ.id)
220 ORDER BY xact_start DESC LIMIT 1;
222 -- Create the history row.
223 -- It's OK if last_circ = cur_circ
224 INSERT INTO action.usr_circ_history
225 (usr, xact_start, target_copy,
226 due_date, checkin_time, source_circ)
230 cur_circ.target_copy,
232 last_circ.checkin_time,
236 -- useful for alleviating administrator anxiety.
237 IF counter % 10000 = 0 THEN
238 RAISE NOTICE 'Migrated history for % total users', counter;
241 counter := counter + 1;
248 DROP FUNCTION IF EXISTS action.usr_visible_circs (INTEGER);
249 DROP FUNCTION IF EXISTS action.usr_visible_circ_copies (INTEGER);
251 -- remove user retention age checks
252 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
254 org_keep_age INTERVAL;
255 org_use_last BOOL = false;
256 org_age_is_min BOOL = false;
262 circ_chain_head action.circulation%ROWTYPE;
263 circ_chain_tail action.circulation%ROWTYPE;
268 last_finished TIMESTAMP WITH TIME ZONE;
273 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
275 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
276 IF org_keep_count IS NULL THEN
277 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
280 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
281 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
283 -- First, find copies with more than keep_count non-renewal circs
286 COUNT(*) AS total_real_circs
287 FROM action.circulation
288 WHERE parent_circ IS NULL
289 AND xact_finish IS NOT NULL
291 HAVING COUNT(*) > org_keep_count
293 -- And, for those, select circs that are finished and older than keep_age
294 FOR circ_chain_head IN
295 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
296 -- The outer query then uses that information to skip the most recent set the library wants to keep
297 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
298 SELECT ac.* FROM action.circulation ac JOIN (
299 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
300 FROM action.circulation ac
301 WHERE ac.target_copy = target_acp.target_copy
302 AND ac.parent_circ IS NULL
303 ORDER BY ac.xact_start ) ranked USING (id)
304 WHERE ranked.rank > org_keep_count
307 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
308 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);
309 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
311 IF NOT org_use_last THEN
312 last_finished := circ_chain_tail.xact_finish;
315 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
317 IF org_age_is_min THEN
318 keep_age := GREATEST( keep_age, org_keep_age );
321 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
323 -- We've passed the purging tests, purge the circ chain starting at the end
324 -- A trigger should auto-purge the rest of the chain.
325 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
327 count_purged := count_purged + 1;
334 $func$ LANGUAGE PLPGSQL;
336 -- delete circ history rows when a user is purged.
337 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
339 specified_dest_usr IN INTEGER
343 renamable_row RECORD;
347 IF specified_dest_usr IS NULL THEN
348 dest_usr := 1; -- Admin user on stock installs
350 dest_usr := specified_dest_usr;
354 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
355 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
356 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
357 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
358 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
359 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
360 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
362 -- Update with a rename to avoid collisions
366 WHERE owner = src_usr
368 suffix := ' (' || src_usr || ')';
372 SET owner = dest_usr, name = name || suffix
373 WHERE id = renamable_row.id;
374 EXCEPTION WHEN unique_violation THEN
375 suffix := suffix || ' ';
382 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
383 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
384 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
385 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
386 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
387 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
388 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
389 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
392 DELETE FROM action.circulation WHERE usr = src_usr;
393 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
394 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
395 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
396 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
397 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
398 DELETE FROM action.hold_request WHERE usr = src_usr;
399 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
400 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
401 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
402 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
403 DELETE FROM action.survey_response WHERE usr = src_usr;
404 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
405 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
408 DELETE FROM actor.card WHERE usr = src_usr;
409 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
411 -- The following update is intended to avoid transient violations of a foreign
412 -- key constraint, whereby actor.usr_address references itself. It may not be
413 -- necessary, but it does no harm.
414 UPDATE actor.usr_address SET replaces = NULL
415 WHERE usr = src_usr AND replaces IS NOT NULL;
416 DELETE FROM actor.usr_address WHERE usr = src_usr;
417 DELETE FROM actor.usr_note WHERE usr = src_usr;
418 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
419 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
420 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
421 DELETE FROM actor.usr_setting WHERE usr = src_usr;
422 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
423 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
426 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
427 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
428 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
429 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
430 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
431 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
434 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
435 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
436 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
437 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
438 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
439 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
440 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
441 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
444 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
445 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
446 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
447 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
450 -- Update buckets with a rename to avoid collisions
453 FROM container.biblio_record_entry_bucket
454 WHERE owner = src_usr
456 suffix := ' (' || src_usr || ')';
459 UPDATE container.biblio_record_entry_bucket
460 SET owner = dest_usr, name = name || suffix
461 WHERE id = renamable_row.id;
462 EXCEPTION WHEN unique_violation THEN
463 suffix := suffix || ' ';
472 FROM container.call_number_bucket
473 WHERE owner = src_usr
475 suffix := ' (' || src_usr || ')';
478 UPDATE container.call_number_bucket
479 SET owner = dest_usr, name = name || suffix
480 WHERE id = renamable_row.id;
481 EXCEPTION WHEN unique_violation THEN
482 suffix := suffix || ' ';
491 FROM container.copy_bucket
492 WHERE owner = src_usr
494 suffix := ' (' || src_usr || ')';
497 UPDATE container.copy_bucket
498 SET owner = dest_usr, name = name || suffix
499 WHERE id = renamable_row.id;
500 EXCEPTION WHEN unique_violation THEN
501 suffix := suffix || ' ';
510 FROM container.user_bucket
511 WHERE owner = src_usr
513 suffix := ' (' || src_usr || ')';
516 UPDATE container.user_bucket
517 SET owner = dest_usr, name = name || suffix
518 WHERE id = renamable_row.id;
519 EXCEPTION WHEN unique_violation THEN
520 suffix := suffix || ' ';
527 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
530 DELETE FROM money.billable_xact WHERE usr = src_usr;
531 DELETE FROM money.collections_tracker WHERE usr = src_usr;
532 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
535 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
536 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
537 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
538 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
541 -- Update with a rename to avoid collisions
545 FROM reporter.output_folder
546 WHERE owner = src_usr
548 suffix := ' (' || src_usr || ')';
551 UPDATE reporter.output_folder
552 SET owner = dest_usr, name = name || suffix
553 WHERE id = renamable_row.id;
554 EXCEPTION WHEN unique_violation THEN
555 suffix := suffix || ' ';
561 EXCEPTION WHEN undefined_table THEN
566 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
567 EXCEPTION WHEN undefined_table THEN
571 -- Update with a rename to avoid collisions
575 FROM reporter.report_folder
576 WHERE owner = src_usr
578 suffix := ' (' || src_usr || ')';
581 UPDATE reporter.report_folder
582 SET owner = dest_usr, name = name || suffix
583 WHERE id = renamable_row.id;
584 EXCEPTION WHEN unique_violation THEN
585 suffix := suffix || ' ';
591 EXCEPTION WHEN undefined_table THEN
596 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
597 EXCEPTION WHEN undefined_table THEN
602 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
603 EXCEPTION WHEN undefined_table THEN
607 -- Update with a rename to avoid collisions
611 FROM reporter.template_folder
612 WHERE owner = src_usr
614 suffix := ' (' || src_usr || ')';
617 UPDATE reporter.template_folder
618 SET owner = dest_usr, name = name || suffix
619 WHERE id = renamable_row.id;
620 EXCEPTION WHEN unique_violation THEN
621 suffix := suffix || ' ';
627 EXCEPTION WHEN undefined_table THEN
632 -- Update with a rename to avoid collisions
636 WHERE owner = src_usr
638 suffix := ' (' || src_usr || ')';
641 UPDATE vandelay.queue
642 SET owner = dest_usr, name = name || suffix
643 WHERE id = renamable_row.id;
644 EXCEPTION WHEN unique_violation THEN
645 suffix := suffix || ' ';
652 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
653 -- can access the information before deletion.
657 mailing_address = NULL,
658 billing_address = NULL