3 SELECT evergreen.upgrade_deps_block_check('1128', :eg_version);
5 DROP VIEW auditor.acq_invoice_lifecycle;
7 ALTER TABLE acq.invoice
8 ADD COLUMN close_date TIMESTAMPTZ,
9 ADD COLUMN closed_by INTEGER
10 REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED;
12 -- duplicate steps for auditor table
13 ALTER TABLE auditor.acq_invoice_history
14 ADD COLUMN close_date TIMESTAMPTZ,
15 ADD COLUMN closed_by INTEGER;
17 UPDATE acq.invoice SET close_date = NOW() WHERE complete;
18 UPDATE auditor.acq_invoice_history SET close_date = NOW() WHERE complete;
20 ALTER TABLE acq.invoice DROP COLUMN complete;
21 ALTER TABLE auditor.acq_invoice_history DROP COLUMN complete;
23 -- this recreates auditor.acq_invoice_lifecycle;
24 SELECT auditor.update_auditors();
26 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 $$
35 -- do some initial cleanup
36 UPDATE actor.usr SET card = NULL WHERE id = src_usr;
37 UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr;
38 UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr;
42 DELETE FROM actor.card where usr = src_usr;
44 IF deactivate_cards THEN
45 UPDATE actor.card SET active = 'f' WHERE usr = src_usr;
47 UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr;
52 DELETE FROM actor.usr_address WHERE usr = src_usr;
54 UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr;
57 UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr;
58 -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them...
59 UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr;
60 PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr);
61 PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr);
64 PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr);
65 PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr);
66 PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr);
67 PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr);
72 -- For each *_bucket table: transfer every bucket belonging to src_usr
73 -- into the custody of dest_usr.
75 -- In order to avoid colliding with an existing bucket owned by
76 -- the destination user, append the source user's id (in parenthesese)
77 -- to the name. If you still get a collision, add successive
78 -- spaces to the name and keep trying until you succeed.
82 FROM container.biblio_record_entry_bucket
85 suffix := ' (' || src_usr || ')';
88 UPDATE container.biblio_record_entry_bucket
89 SET owner = dest_usr, name = name || suffix
90 WHERE id = bucket_row.id;
91 EXCEPTION WHEN unique_violation THEN
92 suffix := suffix || ' ';
101 FROM container.call_number_bucket
102 WHERE owner = src_usr
104 suffix := ' (' || src_usr || ')';
107 UPDATE container.call_number_bucket
108 SET owner = dest_usr, name = name || suffix
109 WHERE id = bucket_row.id;
110 EXCEPTION WHEN unique_violation THEN
111 suffix := suffix || ' ';
120 FROM container.copy_bucket
121 WHERE owner = src_usr
123 suffix := ' (' || src_usr || ')';
126 UPDATE container.copy_bucket
127 SET owner = dest_usr, name = name || suffix
128 WHERE id = bucket_row.id;
129 EXCEPTION WHEN unique_violation THEN
130 suffix := suffix || ' ';
139 FROM container.user_bucket
140 WHERE owner = src_usr
142 suffix := ' (' || src_usr || ')';
145 UPDATE container.user_bucket
146 SET owner = dest_usr, name = name || suffix
147 WHERE id = bucket_row.id;
148 EXCEPTION WHEN unique_violation THEN
149 suffix := suffix || ' ';
156 UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr;
159 -- transfer queues the same way we transfer buckets (see above)
163 WHERE owner = src_usr
165 suffix := ' (' || src_usr || ')';
168 UPDATE vandelay.queue
169 SET owner = dest_usr, name = name || suffix
170 WHERE id = queue_row.id;
171 EXCEPTION WHEN unique_violation THEN
172 suffix := suffix || ' ';
180 PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr);
181 PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr);
182 UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr;
183 UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr;
184 UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr;
187 UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr;
188 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
189 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
190 UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr;
192 UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr;
193 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
194 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
195 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
197 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
198 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
199 UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr;
200 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
201 UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr;
204 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
205 UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr;
206 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
208 -- transfer picklists the same way we transfer buckets (see above)
212 WHERE owner = src_usr
214 suffix := ' (' || src_usr || ')';
218 SET owner = dest_usr, name = name || suffix
219 WHERE id = picklist_row.id;
220 EXCEPTION WHEN unique_violation THEN
221 suffix := suffix || ' ';
228 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
229 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
230 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
231 UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr;
232 UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr;
233 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
234 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
235 UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr;
238 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
239 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
240 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
241 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
242 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
243 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
246 UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr;
247 UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr;
250 -- It's not uncommon to define the reporter schema in a replica
251 -- DB only, so don't assume these tables exist in the write DB.
253 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
254 EXCEPTION WHEN undefined_table THEN
258 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
259 EXCEPTION WHEN undefined_table THEN
263 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
264 EXCEPTION WHEN undefined_table THEN
268 -- transfer folders the same way we transfer buckets (see above)
271 FROM reporter.template_folder
272 WHERE owner = src_usr
274 suffix := ' (' || src_usr || ')';
277 UPDATE reporter.template_folder
278 SET owner = dest_usr, name = name || suffix
279 WHERE id = folder_row.id;
280 EXCEPTION WHEN unique_violation THEN
281 suffix := suffix || ' ';
287 EXCEPTION WHEN undefined_table THEN
291 -- transfer folders the same way we transfer buckets (see above)
294 FROM reporter.report_folder
295 WHERE owner = src_usr
297 suffix := ' (' || src_usr || ')';
300 UPDATE reporter.report_folder
301 SET owner = dest_usr, name = name || suffix
302 WHERE id = folder_row.id;
303 EXCEPTION WHEN unique_violation THEN
304 suffix := suffix || ' ';
310 EXCEPTION WHEN undefined_table THEN
314 -- transfer folders the same way we transfer buckets (see above)
317 FROM reporter.output_folder
318 WHERE owner = src_usr
320 suffix := ' (' || src_usr || ')';
323 UPDATE reporter.output_folder
324 SET owner = dest_usr, name = name || suffix
325 WHERE id = folder_row.id;
326 EXCEPTION WHEN unique_violation THEN
327 suffix := suffix || ' ';
333 EXCEPTION WHEN undefined_table THEN
337 -- Finally, delete the source user
338 DELETE FROM actor.usr WHERE id = src_usr;
344 CREATE OR REPLACE FUNCTION actor.usr_purge_data(
346 specified_dest_usr IN INTEGER
350 renamable_row RECORD;
354 IF specified_dest_usr IS NULL THEN
355 dest_usr := 1; -- Admin user on stock installs
357 dest_usr := specified_dest_usr;
361 UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
362 UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
363 UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
364 UPDATE acq.lineitem SET selector = dest_usr WHERE selector = src_usr;
365 UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr;
366 UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr;
367 UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
368 DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;
370 -- Update with a rename to avoid collisions
374 WHERE owner = src_usr
376 suffix := ' (' || src_usr || ')';
380 SET owner = dest_usr, name = name || suffix
381 WHERE id = renamable_row.id;
382 EXCEPTION WHEN unique_violation THEN
383 suffix := suffix || ' ';
390 UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
391 UPDATE acq.picklist SET editor = dest_usr WHERE editor = src_usr;
392 UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr;
393 UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr;
394 UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr;
395 UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
396 UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
397 UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;
400 DELETE FROM action.circulation WHERE usr = src_usr;
401 UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr;
402 UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr;
403 UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr;
404 UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr;
405 UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr;
406 DELETE FROM action.hold_request WHERE usr = src_usr;
407 UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
408 UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
409 DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
410 UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
411 DELETE FROM action.survey_response WHERE usr = src_usr;
412 UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
413 DELETE FROM action.usr_circ_history WHERE usr = src_usr;
416 DELETE FROM actor.card WHERE usr = src_usr;
417 DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
419 -- The following update is intended to avoid transient violations of a foreign
420 -- key constraint, whereby actor.usr_address references itself. It may not be
421 -- necessary, but it does no harm.
422 UPDATE actor.usr_address SET replaces = NULL
423 WHERE usr = src_usr AND replaces IS NOT NULL;
424 DELETE FROM actor.usr_address WHERE usr = src_usr;
425 DELETE FROM actor.usr_note WHERE usr = src_usr;
426 UPDATE actor.usr_note SET creator = dest_usr WHERE creator = src_usr;
427 DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
428 UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
429 DELETE FROM actor.usr_setting WHERE usr = src_usr;
430 DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
431 UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
434 UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr;
435 UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr;
436 UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr;
437 UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr;
438 UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr;
439 UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr;
442 DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
443 DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
444 UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
445 UPDATE auditor.asset_call_number_history SET editor = dest_usr WHERE editor = src_usr;
446 UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
447 UPDATE auditor.asset_copy_history SET editor = dest_usr WHERE editor = src_usr;
448 UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
449 UPDATE auditor.biblio_record_entry_history SET editor = dest_usr WHERE editor = src_usr;
452 UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
453 UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
454 UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
455 UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;
458 -- Update buckets with a rename to avoid collisions
461 FROM container.biblio_record_entry_bucket
462 WHERE owner = src_usr
464 suffix := ' (' || src_usr || ')';
467 UPDATE container.biblio_record_entry_bucket
468 SET owner = dest_usr, name = name || suffix
469 WHERE id = renamable_row.id;
470 EXCEPTION WHEN unique_violation THEN
471 suffix := suffix || ' ';
480 FROM container.call_number_bucket
481 WHERE owner = src_usr
483 suffix := ' (' || src_usr || ')';
486 UPDATE container.call_number_bucket
487 SET owner = dest_usr, name = name || suffix
488 WHERE id = renamable_row.id;
489 EXCEPTION WHEN unique_violation THEN
490 suffix := suffix || ' ';
499 FROM container.copy_bucket
500 WHERE owner = src_usr
502 suffix := ' (' || src_usr || ')';
505 UPDATE container.copy_bucket
506 SET owner = dest_usr, name = name || suffix
507 WHERE id = renamable_row.id;
508 EXCEPTION WHEN unique_violation THEN
509 suffix := suffix || ' ';
518 FROM container.user_bucket
519 WHERE owner = src_usr
521 suffix := ' (' || src_usr || ')';
524 UPDATE container.user_bucket
525 SET owner = dest_usr, name = name || suffix
526 WHERE id = renamable_row.id;
527 EXCEPTION WHEN unique_violation THEN
528 suffix := suffix || ' ';
535 DELETE FROM container.user_bucket_item WHERE target_user = src_usr;
538 DELETE FROM money.billable_xact WHERE usr = src_usr;
539 DELETE FROM money.collections_tracker WHERE usr = src_usr;
540 UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;
543 DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
544 DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
545 DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
546 DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;
549 -- Update with a rename to avoid collisions
553 FROM reporter.output_folder
554 WHERE owner = src_usr
556 suffix := ' (' || src_usr || ')';
559 UPDATE reporter.output_folder
560 SET owner = dest_usr, name = name || suffix
561 WHERE id = renamable_row.id;
562 EXCEPTION WHEN unique_violation THEN
563 suffix := suffix || ' ';
569 EXCEPTION WHEN undefined_table THEN
574 UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
575 EXCEPTION WHEN undefined_table THEN
579 -- Update with a rename to avoid collisions
583 FROM reporter.report_folder
584 WHERE owner = src_usr
586 suffix := ' (' || src_usr || ')';
589 UPDATE reporter.report_folder
590 SET owner = dest_usr, name = name || suffix
591 WHERE id = renamable_row.id;
592 EXCEPTION WHEN unique_violation THEN
593 suffix := suffix || ' ';
599 EXCEPTION WHEN undefined_table THEN
604 UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
605 EXCEPTION WHEN undefined_table THEN
610 UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
611 EXCEPTION WHEN undefined_table THEN
615 -- Update with a rename to avoid collisions
619 FROM reporter.template_folder
620 WHERE owner = src_usr
622 suffix := ' (' || src_usr || ')';
625 UPDATE reporter.template_folder
626 SET owner = dest_usr, name = name || suffix
627 WHERE id = renamable_row.id;
628 EXCEPTION WHEN unique_violation THEN
629 suffix := suffix || ' ';
635 EXCEPTION WHEN undefined_table THEN
640 -- Update with a rename to avoid collisions
644 WHERE owner = src_usr
646 suffix := ' (' || src_usr || ')';
649 UPDATE vandelay.queue
650 SET owner = dest_usr, name = name || suffix
651 WHERE id = renamable_row.id;
652 EXCEPTION WHEN unique_violation THEN
653 suffix := suffix || ' ';
660 -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
661 -- can access the information before deletion.
665 mailing_address = NULL,
666 billing_address = NULL
677 -- UNDO (minus user purge/merge changes)
681 DROP VIEW auditor.acq_invoice_lifecycle;
682 ALTER TABLE acq.invoice ADD COLUMN complete BOOLEAN NOT NULL DEFAULT FALSE;
683 ALTER TABLE auditor.acq_invoice_history
684 ADD COLUMN complete BOOLEAN NOT NULL DEFAULT FALSE;
685 UPDATE acq.invoice SET complete = TRUE where close_date IS NOT NULL;
686 UPDATE auditor.acq_invoice_history
687 SET complete = TRUE where close_date IS NOT NULL;
688 SET CONSTRAINTS ALL IMMEDIATE; -- or get pending triggers error.
689 ALTER TABLE acq.invoice DROP COLUMN close_date, DROP COLUMN closed_by;
690 ALTER TABLE auditor.acq_invoice_history
691 DROP COLUMN close_date, DROP COLUMN closed_by;
692 SELECT auditor.update_auditors();