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 -- Finally, delete the source user
371 DELETE FROM actor.usr WHERE id = src_usr;
377 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
379 specified_dest_usr IN INTEGER
383 renamable_row RECORD;
387 IF specified_dest_usr IS NULL THEN
388 dest_usr := 1; -- Admin user on stock installs
390 dest_usr := specified_dest_usr;
394 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
395 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
396 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
397 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
398 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
399 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
400 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
402 -- Update with a rename to avoid collisions
406 WHERE owner = src_usr
408 suffix := ' (' || src_usr || ')';
412 SET owner = dest_usr, name = name || suffix
413 WHERE id = renamable_row.id;
414 EXCEPTION WHEN unique_violation THEN
415 suffix := suffix || ' ';
422 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
423 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
424 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
425 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
426 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
427 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
428 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
429 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
432 DELETE FROM action.circulation WHERE usr = src_usr;
433 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
434 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
435 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
436 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
437 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
438 DELETE FROM action.hold_request WHERE usr = src_usr;
439 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
440 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
441 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
442 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
443 DELETE FROM action.survey_response WHERE usr = src_usr;
444 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
445 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
448 DELETE FROM actor.card WHERE usr = src_usr;
449 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
451 -- The following update is intended to avoid transient violations of a foreign
452 -- key constraint, whereby actor.usr_address references itself. It may not be
453 -- necessary, but it does no harm.
454 UPDATE actor.usr_address SET replaces = NULL
455 WHERE usr = src_usr AND replaces IS NOT NULL;
456 DELETE FROM actor.usr_address WHERE usr = src_usr;
457 DELETE FROM actor.usr_note WHERE usr = src_usr;
458 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
459 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
460 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
461 DELETE FROM actor.usr_setting WHERE usr = src_usr;
462 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
463 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
466 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
467 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
468 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
469 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
470 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
471 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
474 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
475 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
476 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
477 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
478 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
479 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
480 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
481 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
484 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
485 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
486 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
487 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
490 -- Update buckets with a rename to avoid collisions
493 FROM container.biblio_record_entry_bucket
494 WHERE owner = src_usr
496 suffix := ' (' || src_usr || ')';
499 UPDATE container.biblio_record_entry_bucket
500 SET owner = dest_usr, name = name || suffix
501 WHERE id = renamable_row.id;
502 EXCEPTION WHEN unique_violation THEN
503 suffix := suffix || ' ';
512 FROM container.call_number_bucket
513 WHERE owner = src_usr
515 suffix := ' (' || src_usr || ')';
518 UPDATE container.call_number_bucket
519 SET owner = dest_usr, name = name || suffix
520 WHERE id = renamable_row.id;
521 EXCEPTION WHEN unique_violation THEN
522 suffix := suffix || ' ';
531 FROM container.copy_bucket
532 WHERE owner = src_usr
534 suffix := ' (' || src_usr || ')';
537 UPDATE container.copy_bucket
538 SET owner = dest_usr, name = name || suffix
539 WHERE id = renamable_row.id;
540 EXCEPTION WHEN unique_violation THEN
541 suffix := suffix || ' ';
550 FROM container.user_bucket
551 WHERE owner = src_usr
553 suffix := ' (' || src_usr || ')';
556 UPDATE container.user_bucket
557 SET owner = dest_usr, name = name || suffix
558 WHERE id = renamable_row.id;
559 EXCEPTION WHEN unique_violation THEN
560 suffix := suffix || ' ';
567 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
570 DELETE FROM money.billable_xact WHERE usr = src_usr;
571 DELETE FROM money.collections_tracker WHERE usr = src_usr;
572 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
575 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
576 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
577 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
578 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
581 -- Update with a rename to avoid collisions
585 FROM reporter.output_folder
586 WHERE owner = src_usr
588 suffix := ' (' || src_usr || ')';
591 UPDATE reporter.output_folder
592 SET owner = dest_usr, name = name || suffix
593 WHERE id = renamable_row.id;
594 EXCEPTION WHEN unique_violation THEN
595 suffix := suffix || ' ';
601 EXCEPTION WHEN undefined_table THEN
606 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
607 EXCEPTION WHEN undefined_table THEN
611 -- Update with a rename to avoid collisions
615 FROM reporter.report_folder
616 WHERE owner = src_usr
618 suffix := ' (' || src_usr || ')';
621 UPDATE reporter.report_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.schedule SET runner = dest_usr WHERE runner = src_usr;
637 EXCEPTION WHEN undefined_table THEN
642 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
643 EXCEPTION WHEN undefined_table THEN
647 -- Update with a rename to avoid collisions
651 FROM reporter.template_folder
652 WHERE owner = src_usr
654 suffix := ' (' || src_usr || ')';
657 UPDATE reporter.template_folder
658 SET owner = dest_usr, name = name || suffix
659 WHERE id = renamable_row.id;
660 EXCEPTION WHEN unique_violation THEN
661 suffix := suffix || ' ';
667 EXCEPTION WHEN undefined_table THEN
672 -- Update with a rename to avoid collisions
676 WHERE owner = src_usr
678 suffix := ' (' || src_usr || ')';
681 UPDATE vandelay.queue
682 SET owner = dest_usr, name = name || suffix
683 WHERE id = renamable_row.id;
684 EXCEPTION WHEN unique_violation THEN
685 suffix := suffix || ' ';
692 UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr;
694 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
695 -- can access the information before deletion.
699 mailing_address = NULL,
700 billing_address = NULL