4 -- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
6 CREATE TABLE vandelay.session_tracker (
7 id BIGSERIAL PRIMARY KEY,
9 -- string of characters (e.g. md5) used for linking trackers
10 -- of different actions into a series. There can be multiple
11 -- session_keys of each action type, creating the opportunity
12 -- to link multiple action trackers into a single session.
13 session_key TEXT NOT NULL,
15 -- optional user-supplied name
18 usr INTEGER NOT NULL REFERENCES actor.usr(id)
19 DEFERRABLE INITIALLY DEFERRED,
21 -- org unit can be derived from WS
22 workstation INTEGER NOT NULL REFERENCES actor.workstation(id)
23 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
26 record_type vandelay.bib_queue_queue_type NOT NULL DEFAULT 'bib',
28 -- Queue defines the source of the data, it does not necessarily
29 -- mean that an action is being performed against an entire queue.
30 -- E.g. some imports are misc. lists of record IDs, but they always
31 -- come from one queue.
32 -- No foreign key -- could be auth or bib queue.
33 queue BIGINT NOT NULL,
35 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
36 update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
38 state TEXT NOT NULL DEFAULT 'active',
40 action_type TEXT NOT NULL DEFAULT 'enqueue', -- import
42 -- total number of tasks to perform / loosely defined
43 -- could be # of recs to import or # of recs + # of copies
44 -- depending on the import context
45 total_actions INTEGER NOT NULL DEFAULT 0,
47 -- total number of tasked performed so far
48 actions_performed INTEGER NOT NULL DEFAULT 0,
50 CONSTRAINT vand_tracker_valid_state
51 CHECK (state IN ('active','error','complete')),
53 CONSTRAINT vand_tracker_valid_action_type
54 CHECK (action_type IN ('upload', 'enqueue', 'import'))
58 CREATE OR REPLACE FUNCTION actor.usr_merge( src_usr INT, dest_usr INT, del_addrs BOOLEAN, del_cards BOOLEAN, deactivate_cards BOOLEAN ) RETURNS VOID AS $$
67 -- do some initial cleanup
68 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
69 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
70 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
74 DELETE FROM actor.card where usr = src_usr;
76 IF deactivate_cards THEN
77 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
79 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
84 DELETE FROM actor.usr_address WHERE usr = src_usr;
86 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
89 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
90 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
91 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
92 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
93 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
96 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
97 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
98 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
99 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
104 -- For each *_bucket table: transfer every bucket belonging to src_usr
105 -- into the custody of dest_usr.
107 -- In order to avoid colliding with an existing bucket owned by
108 -- the destination user, append the source user's id (in parenthesese)
109 -- to the name. If you still get a collision, add successive
110 -- spaces to the name and keep trying until you succeed.
114 FROM container.biblio_record_entry_bucket
115 WHERE owner = src_usr
117 suffix := ' (' || src_usr || ')';
120 UPDATE container.biblio_record_entry_bucket
121 SET owner = dest_usr, name = name || suffix
122 WHERE id = bucket_row.id;
123 EXCEPTION WHEN unique_violation THEN
124 suffix := suffix || ' ';
133 FROM container.call_number_bucket
134 WHERE owner = src_usr
136 suffix := ' (' || src_usr || ')';
139 UPDATE container.call_number_bucket
140 SET owner = dest_usr, name = name || suffix
141 WHERE id = bucket_row.id;
142 EXCEPTION WHEN unique_violation THEN
143 suffix := suffix || ' ';
152 FROM container.copy_bucket
153 WHERE owner = src_usr
155 suffix := ' (' || src_usr || ')';
158 UPDATE container.copy_bucket
159 SET owner = dest_usr, name = name || suffix
160 WHERE id = bucket_row.id;
161 EXCEPTION WHEN unique_violation THEN
162 suffix := suffix || ' ';
171 FROM container.user_bucket
172 WHERE owner = src_usr
174 suffix := ' (' || src_usr || ')';
177 UPDATE container.user_bucket
178 SET owner = dest_usr, name = name || suffix
179 WHERE id = bucket_row.id;
180 EXCEPTION WHEN unique_violation THEN
181 suffix := suffix || ' ';
188 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
191 -- transfer queues the same way we transfer buckets (see above)
195 WHERE owner = src_usr
197 suffix := ' (' || src_usr || ')';
200 UPDATE vandelay.queue
201 SET owner = dest_usr, name = name || suffix
202 WHERE id = queue_row.id;
203 EXCEPTION WHEN unique_violation THEN
204 suffix := suffix || ' ';
211 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
214 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
215 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
216 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
217 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
218 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
221 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
222 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
223 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
224 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
226 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
227 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
228 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
229 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
231 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
232 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
233 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
234 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
235 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
238 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
239 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
241 -- transfer picklists the same way we transfer buckets (see above)
245 WHERE owner = src_usr
247 suffix := ' (' || src_usr || ')';
251 SET owner = dest_usr, name = name || suffix
252 WHERE id = picklist_row.id;
253 EXCEPTION WHEN unique_violation THEN
254 suffix := suffix || ' ';
261 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
262 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
263 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
264 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
265 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
266 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
267 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
268 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
271 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
272 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
273 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
274 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
275 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
276 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
279 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
280 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
283 -- It's not uncommon to define the reporter schema in a replica
284 -- DB only, so don't assume these tables exist in the write DB.
286 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
287 EXCEPTION WHEN undefined_table THEN
291 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
292 EXCEPTION WHEN undefined_table THEN
296 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
297 EXCEPTION WHEN undefined_table THEN
301 -- transfer folders the same way we transfer buckets (see above)
304 FROM reporter.template_folder
305 WHERE owner = src_usr
307 suffix := ' (' || src_usr || ')';
310 UPDATE reporter.template_folder
311 SET owner = dest_usr, name = name || suffix
312 WHERE id = folder_row.id;
313 EXCEPTION WHEN unique_violation THEN
314 suffix := suffix || ' ';
320 EXCEPTION WHEN undefined_table THEN
324 -- transfer folders the same way we transfer buckets (see above)
327 FROM reporter.report_folder
328 WHERE owner = src_usr
330 suffix := ' (' || src_usr || ')';
333 UPDATE reporter.report_folder
334 SET owner = dest_usr, name = name || suffix
335 WHERE id = folder_row.id;
336 EXCEPTION WHEN unique_violation THEN
337 suffix := suffix || ' ';
343 EXCEPTION WHEN undefined_table THEN
347 -- transfer folders the same way we transfer buckets (see above)
350 FROM reporter.output_folder
351 WHERE owner = src_usr
353 suffix := ' (' || src_usr || ')';
356 UPDATE reporter.output_folder
357 SET owner = dest_usr, name = name || suffix
358 WHERE id = folder_row.id;
359 EXCEPTION WHEN unique_violation THEN
360 suffix := suffix || ' ';
366 EXCEPTION WHEN undefined_table THEN
370 -- propagate preferred name values from the source user to the
371 -- destination user, but only when values are not being replaced.
372 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
375 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
376 pref_first_given_name =
377 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
378 pref_second_given_name =
379 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
381 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
383 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
386 -- Copy and deduplicate name keywords
387 -- String -> array -> rows -> DISTINCT -> array -> string
388 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
389 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
390 UPDATE actor.usr SET name_keywords = (
392 SELECT DISTINCT UNNEST(
393 REGEXP_SPLIT_TO_ARRAY(
394 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
395 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
398 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
399 ) WHERE id = dest_usr;
401 -- Finally, delete the source user
402 DELETE FROM actor.usr WHERE id = src_usr;
408 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
410 specified_dest_usr IN INTEGER
414 renamable_row RECORD;
418 IF specified_dest_usr IS NULL THEN
419 dest_usr := 1; -- Admin user on stock installs
421 dest_usr := specified_dest_usr;
425 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
426 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
427 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
428 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
429 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
430 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
431 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
433 -- Update with a rename to avoid collisions
437 WHERE owner = src_usr
439 suffix := ' (' || src_usr || ')';
443 SET owner = dest_usr, name = name || suffix
444 WHERE id = renamable_row.id;
445 EXCEPTION WHEN unique_violation THEN
446 suffix := suffix || ' ';
453 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
454 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
455 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
456 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
457 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
458 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
459 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
460 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
463 DELETE FROM action.circulation WHERE usr = src_usr;
464 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
465 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
466 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
467 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
468 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
469 DELETE FROM action.hold_request WHERE usr = src_usr;
470 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
471 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
472 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
473 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
474 DELETE FROM action.survey_response WHERE usr = src_usr;
475 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
476 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
479 DELETE FROM actor.card WHERE usr = src_usr;
480 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
482 -- The following update is intended to avoid transient violations of a foreign
483 -- key constraint, whereby actor.usr_address references itself. It may not be
484 -- necessary, but it does no harm.
485 UPDATE actor.usr_address SET replaces = NULL
486 WHERE usr = src_usr AND replaces IS NOT NULL;
487 DELETE FROM actor.usr_address WHERE usr = src_usr;
488 DELETE FROM actor.usr_note WHERE usr = src_usr;
489 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
490 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
491 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
492 DELETE FROM actor.usr_setting WHERE usr = src_usr;
493 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
494 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
497 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
498 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
499 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
500 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
501 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
502 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
505 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
506 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
507 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
508 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
509 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
510 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
511 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
512 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
515 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
516 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
517 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
518 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
521 -- Update buckets with a rename to avoid collisions
524 FROM container.biblio_record_entry_bucket
525 WHERE owner = src_usr
527 suffix := ' (' || src_usr || ')';
530 UPDATE container.biblio_record_entry_bucket
531 SET owner = dest_usr, name = name || suffix
532 WHERE id = renamable_row.id;
533 EXCEPTION WHEN unique_violation THEN
534 suffix := suffix || ' ';
543 FROM container.call_number_bucket
544 WHERE owner = src_usr
546 suffix := ' (' || src_usr || ')';
549 UPDATE container.call_number_bucket
550 SET owner = dest_usr, name = name || suffix
551 WHERE id = renamable_row.id;
552 EXCEPTION WHEN unique_violation THEN
553 suffix := suffix || ' ';
562 FROM container.copy_bucket
563 WHERE owner = src_usr
565 suffix := ' (' || src_usr || ')';
568 UPDATE container.copy_bucket
569 SET owner = dest_usr, name = name || suffix
570 WHERE id = renamable_row.id;
571 EXCEPTION WHEN unique_violation THEN
572 suffix := suffix || ' ';
581 FROM container.user_bucket
582 WHERE owner = src_usr
584 suffix := ' (' || src_usr || ')';
587 UPDATE container.user_bucket
588 SET owner = dest_usr, name = name || suffix
589 WHERE id = renamable_row.id;
590 EXCEPTION WHEN unique_violation THEN
591 suffix := suffix || ' ';
598 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
601 DELETE FROM money.billable_xact WHERE usr = src_usr;
602 DELETE FROM money.collections_tracker WHERE usr = src_usr;
603 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
606 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
607 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
608 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
609 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
612 -- Update with a rename to avoid collisions
616 FROM reporter.output_folder
617 WHERE owner = src_usr
619 suffix := ' (' || src_usr || ')';
622 UPDATE reporter.output_folder
623 SET owner = dest_usr, name = name || suffix
624 WHERE id = renamable_row.id;
625 EXCEPTION WHEN unique_violation THEN
626 suffix := suffix || ' ';
632 EXCEPTION WHEN undefined_table THEN
637 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
638 EXCEPTION WHEN undefined_table THEN
642 -- Update with a rename to avoid collisions
646 FROM reporter.report_folder
647 WHERE owner = src_usr
649 suffix := ' (' || src_usr || ')';
652 UPDATE reporter.report_folder
653 SET owner = dest_usr, name = name || suffix
654 WHERE id = renamable_row.id;
655 EXCEPTION WHEN unique_violation THEN
656 suffix := suffix || ' ';
662 EXCEPTION WHEN undefined_table THEN
667 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
668 EXCEPTION WHEN undefined_table THEN
673 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
674 EXCEPTION WHEN undefined_table THEN
678 -- Update with a rename to avoid collisions
682 FROM reporter.template_folder
683 WHERE owner = src_usr
685 suffix := ' (' || src_usr || ')';
688 UPDATE reporter.template_folder
689 SET owner = dest_usr, name = name || suffix
690 WHERE id = renamable_row.id;
691 EXCEPTION WHEN unique_violation THEN
692 suffix := suffix || ' ';
698 EXCEPTION WHEN undefined_table THEN
703 -- Update with a rename to avoid collisions
707 WHERE owner = src_usr
709 suffix := ' (' || src_usr || ')';
712 UPDATE vandelay.queue
713 SET owner = dest_usr, name = name || suffix
714 WHERE id = renamable_row.id;
715 EXCEPTION WHEN unique_violation THEN
716 suffix := suffix || ' ';
723 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
725 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
726 -- can access the information before deletion.
730 mailing_address = NULL,
731 billing_address = NULL