3 SELECT evergreen.upgrade_deps_block_check('1126', :eg_version);
5 CREATE TABLE vandelay.session_tracker (
6 id BIGSERIAL PRIMARY KEY,
8 -- string of characters (e.g. md5) used for linking trackers
9 -- of different actions into a series. There can be multiple
10 -- session_keys of each action type, creating the opportunity
11 -- to link multiple action trackers into a single session.
12 session_key TEXT NOT NULL,
14 -- optional user-supplied name
17 usr INTEGER NOT NULL REFERENCES actor.usr(id)
18 DEFERRABLE INITIALLY DEFERRED,
20 -- org unit can be derived from WS
21 workstation INTEGER NOT NULL REFERENCES actor.workstation(id)
22 ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
25 record_type vandelay.bib_queue_queue_type NOT NULL DEFAULT 'bib',
27 -- Queue defines the source of the data, it does not necessarily
28 -- mean that an action is being performed against an entire queue.
29 -- E.g. some imports are misc. lists of record IDs, but they always
30 -- come from one queue.
31 -- No foreign key -- could be auth or bib queue.
32 queue BIGINT NOT NULL,
34 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
35 update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
37 state TEXT NOT NULL DEFAULT 'active',
39 action_type TEXT NOT NULL DEFAULT 'enqueue', -- import
41 -- total number of tasks to perform / loosely defined
42 -- could be # of recs to import or # of recs + # of copies
43 -- depending on the import context
44 total_actions INTEGER NOT NULL DEFAULT 0,
46 -- total number of tasked performed so far
47 actions_performed INTEGER NOT NULL DEFAULT 0,
49 CONSTRAINT vand_tracker_valid_state
50 CHECK (state IN ('active','error','complete')),
52 CONSTRAINT vand_tracker_valid_action_type
53 CHECK (action_type IN ('upload', 'enqueue', 'import'))
57 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 $$
66 -- do some initial cleanup
67 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
68 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
69 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
73 DELETE FROM actor.card where usr = src_usr;
75 IF deactivate_cards THEN
76 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
78 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
83 DELETE FROM actor.usr_address WHERE usr = src_usr;
85 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
88 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
89 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
90 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
91 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
92 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
95 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
96 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
97 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
98 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
103 -- For each *_bucket table: transfer every bucket belonging to src_usr
104 -- into the custody of dest_usr.
106 -- In order to avoid colliding with an existing bucket owned by
107 -- the destination user, append the source user's id (in parenthesese)
108 -- to the name. If you still get a collision, add successive
109 -- spaces to the name and keep trying until you succeed.
113 FROM container.biblio_record_entry_bucket
114 WHERE owner = src_usr
116 suffix := ' (' || src_usr || ')';
119 UPDATE container.biblio_record_entry_bucket
120 SET owner = dest_usr, name = name || suffix
121 WHERE id = bucket_row.id;
122 EXCEPTION WHEN unique_violation THEN
123 suffix := suffix || ' ';
132 FROM container.call_number_bucket
133 WHERE owner = src_usr
135 suffix := ' (' || src_usr || ')';
138 UPDATE container.call_number_bucket
139 SET owner = dest_usr, name = name || suffix
140 WHERE id = bucket_row.id;
141 EXCEPTION WHEN unique_violation THEN
142 suffix := suffix || ' ';
151 FROM container.copy_bucket
152 WHERE owner = src_usr
154 suffix := ' (' || src_usr || ')';
157 UPDATE container.copy_bucket
158 SET owner = dest_usr, name = name || suffix
159 WHERE id = bucket_row.id;
160 EXCEPTION WHEN unique_violation THEN
161 suffix := suffix || ' ';
170 FROM container.user_bucket
171 WHERE owner = src_usr
173 suffix := ' (' || src_usr || ')';
176 UPDATE container.user_bucket
177 SET owner = dest_usr, name = name || suffix
178 WHERE id = bucket_row.id;
179 EXCEPTION WHEN unique_violation THEN
180 suffix := suffix || ' ';
187 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
190 -- transfer queues the same way we transfer buckets (see above)
194 WHERE owner = src_usr
196 suffix := ' (' || src_usr || ')';
199 UPDATE vandelay.queue
200 SET owner = dest_usr, name = name || suffix
201 WHERE id = queue_row.id;
202 EXCEPTION WHEN unique_violation THEN
203 suffix := suffix || ' ';
210 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
213 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
214 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
215 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
216 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
217 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
220 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
221 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
222 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
223 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
225 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
226 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
227 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
228 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
230 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
231 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
232 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
233 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
234 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
237 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
238 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
240 -- transfer picklists the same way we transfer buckets (see above)
244 WHERE owner = src_usr
246 suffix := ' (' || src_usr || ')';
250 SET owner = dest_usr, name = name || suffix
251 WHERE id = picklist_row.id;
252 EXCEPTION WHEN unique_violation THEN
253 suffix := suffix || ' ';
260 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
261 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
262 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
263 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
264 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
265 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
266 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
267 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
270 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
271 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
272 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
273 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
274 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
275 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
278 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
279 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
282 -- It's not uncommon to define the reporter schema in a replica
283 -- DB only, so don't assume these tables exist in the write DB.
285 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
286 EXCEPTION WHEN undefined_table THEN
290 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
291 EXCEPTION WHEN undefined_table THEN
295 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
296 EXCEPTION WHEN undefined_table THEN
300 -- transfer folders the same way we transfer buckets (see above)
303 FROM reporter.template_folder
304 WHERE owner = src_usr
306 suffix := ' (' || src_usr || ')';
309 UPDATE reporter.template_folder
310 SET owner = dest_usr, name = name || suffix
311 WHERE id = folder_row.id;
312 EXCEPTION WHEN unique_violation THEN
313 suffix := suffix || ' ';
319 EXCEPTION WHEN undefined_table THEN
323 -- transfer folders the same way we transfer buckets (see above)
326 FROM reporter.report_folder
327 WHERE owner = src_usr
329 suffix := ' (' || src_usr || ')';
332 UPDATE reporter.report_folder
333 SET owner = dest_usr, name = name || suffix
334 WHERE id = folder_row.id;
335 EXCEPTION WHEN unique_violation THEN
336 suffix := suffix || ' ';
342 EXCEPTION WHEN undefined_table THEN
346 -- transfer folders the same way we transfer buckets (see above)
349 FROM reporter.output_folder
350 WHERE owner = src_usr
352 suffix := ' (' || src_usr || ')';
355 UPDATE reporter.output_folder
356 SET owner = dest_usr, name = name || suffix
357 WHERE id = folder_row.id;
358 EXCEPTION WHEN unique_violation THEN
359 suffix := suffix || ' ';
365 EXCEPTION WHEN undefined_table THEN
369 -- propagate preferred name values from the source user to the
370 -- destination user, but only when values are not being replaced.
371 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr)
374 COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)),
375 pref_first_given_name =
376 COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)),
377 pref_second_given_name =
378 COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)),
380 COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)),
382 COALESCE(pref_suffix, (SELECT pref_suffix FROM susr))
385 -- Copy and deduplicate name keywords
386 -- String -> array -> rows -> DISTINCT -> array -> string
387 WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr),
388 dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr)
389 UPDATE actor.usr SET name_keywords = (
391 SELECT DISTINCT UNNEST(
392 REGEXP_SPLIT_TO_ARRAY(
393 COALESCE((SELECT name_keywords FROM susr), '') || ' ' ||
394 COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+'
397 ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw
398 ) WHERE id = dest_usr;
400 -- Finally, delete the source user
401 DELETE FROM actor.usr WHERE id = src_usr;
407 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
409 specified_dest_usr IN INTEGER
413 renamable_row RECORD;
417 IF specified_dest_usr IS NULL THEN
418 dest_usr := 1; -- Admin user on stock installs
420 dest_usr := specified_dest_usr;
424 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
425 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
426 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
427 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
428 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
429 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
430 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
432 -- Update with a rename to avoid collisions
436 WHERE owner = src_usr
438 suffix := ' (' || src_usr || ')';
442 SET owner = dest_usr, name = name || suffix
443 WHERE id = renamable_row.id;
444 EXCEPTION WHEN unique_violation THEN
445 suffix := suffix || ' ';
452 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
453 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
454 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
455 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
456 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
457 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
458 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
459 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
462 DELETE FROM action.circulation WHERE usr = src_usr;
463 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
464 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
465 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
466 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
467 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
468 DELETE FROM action.hold_request WHERE usr = src_usr;
469 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
470 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
471 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
472 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
473 DELETE FROM action.survey_response WHERE usr = src_usr;
474 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
475 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
478 DELETE FROM actor.card WHERE usr = src_usr;
479 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
481 -- The following update is intended to avoid transient violations of a foreign
482 -- key constraint, whereby actor.usr_address references itself. It may not be
483 -- necessary, but it does no harm.
484 UPDATE actor.usr_address SET replaces = NULL
485 WHERE usr = src_usr AND replaces IS NOT NULL;
486 DELETE FROM actor.usr_address WHERE usr = src_usr;
487 DELETE FROM actor.usr_note WHERE usr = src_usr;
488 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
489 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
490 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
491 DELETE FROM actor.usr_setting WHERE usr = src_usr;
492 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
493 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
496 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
497 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
498 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
499 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
500 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
501 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
504 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
505 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
506 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
507 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
508 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
509 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
510 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
511 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
514 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
515 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
516 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
517 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
520 -- Update buckets with a rename to avoid collisions
523 FROM container.biblio_record_entry_bucket
524 WHERE owner = src_usr
526 suffix := ' (' || src_usr || ')';
529 UPDATE container.biblio_record_entry_bucket
530 SET owner = dest_usr, name = name || suffix
531 WHERE id = renamable_row.id;
532 EXCEPTION WHEN unique_violation THEN
533 suffix := suffix || ' ';
542 FROM container.call_number_bucket
543 WHERE owner = src_usr
545 suffix := ' (' || src_usr || ')';
548 UPDATE container.call_number_bucket
549 SET owner = dest_usr, name = name || suffix
550 WHERE id = renamable_row.id;
551 EXCEPTION WHEN unique_violation THEN
552 suffix := suffix || ' ';
561 FROM container.copy_bucket
562 WHERE owner = src_usr
564 suffix := ' (' || src_usr || ')';
567 UPDATE container.copy_bucket
568 SET owner = dest_usr, name = name || suffix
569 WHERE id = renamable_row.id;
570 EXCEPTION WHEN unique_violation THEN
571 suffix := suffix || ' ';
580 FROM container.user_bucket
581 WHERE owner = src_usr
583 suffix := ' (' || src_usr || ')';
586 UPDATE container.user_bucket
587 SET owner = dest_usr, name = name || suffix
588 WHERE id = renamable_row.id;
589 EXCEPTION WHEN unique_violation THEN
590 suffix := suffix || ' ';
597 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
600 DELETE FROM money.billable_xact WHERE usr = src_usr;
601 DELETE FROM money.collections_tracker WHERE usr = src_usr;
602 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
605 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
606 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
607 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
608 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
611 -- Update with a rename to avoid collisions
615 FROM reporter.output_folder
616 WHERE owner = src_usr
618 suffix := ' (' || src_usr || ')';
621 UPDATE reporter.output_folder
622 SET owner = dest_usr, name = name || suffix
623 WHERE id = renamable_row.id;
624 EXCEPTION WHEN unique_violation THEN
625 suffix := suffix || ' ';
631 EXCEPTION WHEN undefined_table THEN
636 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
637 EXCEPTION WHEN undefined_table THEN
641 -- Update with a rename to avoid collisions
645 FROM reporter.report_folder
646 WHERE owner = src_usr
648 suffix := ' (' || src_usr || ')';
651 UPDATE reporter.report_folder
652 SET owner = dest_usr, name = name || suffix
653 WHERE id = renamable_row.id;
654 EXCEPTION WHEN unique_violation THEN
655 suffix := suffix || ' ';
661 EXCEPTION WHEN undefined_table THEN
666 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
667 EXCEPTION WHEN undefined_table THEN
672 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
673 EXCEPTION WHEN undefined_table THEN
677 -- Update with a rename to avoid collisions
681 FROM reporter.template_folder
682 WHERE owner = src_usr
684 suffix := ' (' || src_usr || ')';
687 UPDATE reporter.template_folder
688 SET owner = dest_usr, name = name || suffix
689 WHERE id = renamable_row.id;
690 EXCEPTION WHEN unique_violation THEN
691 suffix := suffix || ' ';
697 EXCEPTION WHEN undefined_table THEN
702 -- Update with a rename to avoid collisions
706 WHERE owner = src_usr
708 suffix := ' (' || src_usr || ')';
711 UPDATE vandelay.queue
712 SET owner = dest_usr, name = name || suffix
713 WHERE id = renamable_row.id;
714 EXCEPTION WHEN unique_violation THEN
715 suffix := suffix || ' ';
722 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
724 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
725 -- can access the information before deletion.
729 mailing_address = NULL,
730 billing_address = NULL