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 -- Find the last circ in the circ chain.
217 SELECT INTO last_circ *
218 FROM action.circ_chain(cur_circ.id)
219 ORDER BY xact_start DESC LIMIT 1;
221 -- Create the history row.
222 -- It's OK if last_circ = cur_circ
223 INSERT INTO action.usr_circ_history
224 (usr, xact_start, target_copy,
225 due_date, checkin_time, source_circ)
229 cur_circ.target_copy,
231 last_circ.checkin_time,
235 -- useful for alleviating administrator anxiety.
236 IF counter % 10000 = 0 THEN
237 RAISE NOTICE 'Migrated history for % total users', counter;
240 counter := counter + 1;
247 DROP FUNCTION IF EXISTS action.usr_visible_circs (INTEGER);
248 DROP FUNCTION IF EXISTS action.usr_visible_circ_copies (INTEGER);
250 -- remove user retention age checks
251 CREATE OR REPLACE FUNCTION action.purge_circulations () RETURNS INT AS $func$
253 org_keep_age INTERVAL;
254 org_use_last BOOL = false;
255 org_age_is_min BOOL = false;
261 circ_chain_head action.circulation%ROWTYPE;
262 circ_chain_tail action.circulation%ROWTYPE;
267 last_finished TIMESTAMP WITH TIME ZONE;
272 SELECT value::INTERVAL INTO org_keep_age FROM config.global_flag WHERE name = 'history.circ.retention_age' AND enabled;
274 SELECT value::INT INTO org_keep_count FROM config.global_flag WHERE name = 'history.circ.retention_count' AND enabled;
275 IF org_keep_count IS NULL THEN
276 RETURN count_purged; -- Gimme a count to keep, or I keep them all, forever
279 SELECT enabled INTO org_use_last FROM config.global_flag WHERE name = 'history.circ.retention_uses_last_finished';
280 SELECT enabled INTO org_age_is_min FROM config.global_flag WHERE name = 'history.circ.retention_age_is_min';
282 -- First, find copies with more than keep_count non-renewal circs
285 COUNT(*) AS total_real_circs
286 FROM action.circulation
287 WHERE parent_circ IS NULL
288 AND xact_finish IS NOT NULL
290 HAVING COUNT(*) > org_keep_count
292 -- And, for those, select circs that are finished and older than keep_age
293 FOR circ_chain_head IN
294 -- For reference, the subquery uses a window function to order the circs newest to oldest and number them
295 -- The outer query then uses that information to skip the most recent set the library wants to keep
296 -- End result is we don't care what order they come out in, as they are all potentials for deletion.
297 SELECT ac.* FROM action.circulation ac JOIN (
298 SELECT rank() OVER (ORDER BY xact_start DESC), ac.id
299 FROM action.circulation ac
300 WHERE ac.target_copy = target_acp.target_copy
301 AND ac.parent_circ IS NULL
302 ORDER BY ac.xact_start ) ranked USING (id)
303 WHERE ranked.rank > org_keep_count
306 SELECT * INTO circ_chain_tail FROM action.circ_chain(circ_chain_head.id) ORDER BY xact_start DESC LIMIT 1;
307 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);
308 CONTINUE WHEN circ_chain_tail.xact_finish IS NULL OR num_incomplete > 0;
310 IF NOT org_use_last THEN
311 last_finished := circ_chain_tail.xact_finish;
314 keep_age := COALESCE( org_keep_age, '2000 years'::INTERVAL );
316 IF org_age_is_min THEN
317 keep_age := GREATEST( keep_age, org_keep_age );
320 CONTINUE WHEN AGE(NOW(), last_finished) < keep_age;
322 -- We've passed the purging tests, purge the circ chain starting at the end
323 -- A trigger should auto-purge the rest of the chain.
324 DELETE FROM action.circulation WHERE id = circ_chain_tail.id;
326 count_purged := count_purged + 1;
333 $func$ LANGUAGE PLPGSQL;
335 -- delete circ history rows when a user is purged.
336 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
338 specified_dest_usr IN INTEGER
342 renamable_row RECORD;
346 IF specified_dest_usr IS NULL THEN
347 dest_usr := 1; -- Admin user on stock installs
349 dest_usr := specified_dest_usr;
353 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
354 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
355 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
356 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
357 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
358 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
359 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
361 -- Update with a rename to avoid collisions
365 WHERE owner = src_usr
367 suffix := ' (' || src_usr || ')';
371 SET owner = dest_usr, name = name || suffix
372 WHERE id = renamable_row.id;
373 EXCEPTION WHEN unique_violation THEN
374 suffix := suffix || ' ';
381 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
382 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
383 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
384 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
385 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
386 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
387 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
388 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
391 DELETE FROM action.circulation WHERE usr = src_usr;
392 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
393 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
394 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
395 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
396 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
397 DELETE FROM action.hold_request WHERE usr = src_usr;
398 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
399 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
400 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
401 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
402 DELETE FROM action.survey_response WHERE usr = src_usr;
403 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
404 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
407 DELETE FROM actor.card WHERE usr = src_usr;
408 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
410 -- The following update is intended to avoid transient violations of a foreign
411 -- key constraint, whereby actor.usr_address references itself. It may not be
412 -- necessary, but it does no harm.
413 UPDATE actor.usr_address SET replaces = NULL
414 WHERE usr = src_usr AND replaces IS NOT NULL;
415 DELETE FROM actor.usr_address WHERE usr = src_usr;
416 DELETE FROM actor.usr_note WHERE usr = src_usr;
417 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
418 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
419 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
420 DELETE FROM actor.usr_setting WHERE usr = src_usr;
421 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
422 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
425 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
426 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
427 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
428 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
429 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
430 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
433 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
434 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
435 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
436 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
437 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
438 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
439 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
440 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
443 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
444 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
445 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
446 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
449 -- Update buckets with a rename to avoid collisions
452 FROM container.biblio_record_entry_bucket
453 WHERE owner = src_usr
455 suffix := ' (' || src_usr || ')';
458 UPDATE container.biblio_record_entry_bucket
459 SET owner = dest_usr, name = name || suffix
460 WHERE id = renamable_row.id;
461 EXCEPTION WHEN unique_violation THEN
462 suffix := suffix || ' ';
471 FROM container.call_number_bucket
472 WHERE owner = src_usr
474 suffix := ' (' || src_usr || ')';
477 UPDATE container.call_number_bucket
478 SET owner = dest_usr, name = name || suffix
479 WHERE id = renamable_row.id;
480 EXCEPTION WHEN unique_violation THEN
481 suffix := suffix || ' ';
490 FROM container.copy_bucket
491 WHERE owner = src_usr
493 suffix := ' (' || src_usr || ')';
496 UPDATE container.copy_bucket
497 SET owner = dest_usr, name = name || suffix
498 WHERE id = renamable_row.id;
499 EXCEPTION WHEN unique_violation THEN
500 suffix := suffix || ' ';
509 FROM container.user_bucket
510 WHERE owner = src_usr
512 suffix := ' (' || src_usr || ')';
515 UPDATE container.user_bucket
516 SET owner = dest_usr, name = name || suffix
517 WHERE id = renamable_row.id;
518 EXCEPTION WHEN unique_violation THEN
519 suffix := suffix || ' ';
526 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
529 DELETE FROM money.billable_xact WHERE usr = src_usr;
530 DELETE FROM money.collections_tracker WHERE usr = src_usr;
531 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
534 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
535 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
536 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
537 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
540 -- Update with a rename to avoid collisions
544 FROM reporter.output_folder
545 WHERE owner = src_usr
547 suffix := ' (' || src_usr || ')';
550 UPDATE reporter.output_folder
551 SET owner = dest_usr, name = name || suffix
552 WHERE id = renamable_row.id;
553 EXCEPTION WHEN unique_violation THEN
554 suffix := suffix || ' ';
560 EXCEPTION WHEN undefined_table THEN
565 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
566 EXCEPTION WHEN undefined_table THEN
570 -- Update with a rename to avoid collisions
574 FROM reporter.report_folder
575 WHERE owner = src_usr
577 suffix := ' (' || src_usr || ')';
580 UPDATE reporter.report_folder
581 SET owner = dest_usr, name = name || suffix
582 WHERE id = renamable_row.id;
583 EXCEPTION WHEN unique_violation THEN
584 suffix := suffix || ' ';
590 EXCEPTION WHEN undefined_table THEN
595 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
596 EXCEPTION WHEN undefined_table THEN
601 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
602 EXCEPTION WHEN undefined_table THEN
606 -- Update with a rename to avoid collisions
610 FROM reporter.template_folder
611 WHERE owner = src_usr
613 suffix := ' (' || src_usr || ')';
616 UPDATE reporter.template_folder
617 SET owner = dest_usr, name = name || suffix
618 WHERE id = renamable_row.id;
619 EXCEPTION WHEN unique_violation THEN
620 suffix := suffix || ' ';
626 EXCEPTION WHEN undefined_table THEN
631 -- Update with a rename to avoid collisions
635 WHERE owner = src_usr
637 suffix := ' (' || src_usr || ')';
640 UPDATE vandelay.queue
641 SET owner = dest_usr, name = name || suffix
642 WHERE id = renamable_row.id;
643 EXCEPTION WHEN unique_violation THEN
644 suffix := suffix || ' ';
651 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
652 -- can access the information before deletion.
656 mailing_address = NULL,
657 billing_address = NULL