BEGIN; SELECT evergreen.upgrade_deps_block_check('1131', :eg_version); 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 $$ DECLARE suffix TEXT; bucket_row RECORD; picklist_row RECORD; queue_row RECORD; folder_row RECORD; BEGIN -- Bail if src_usr equals dest_usr because the result of merging a -- user with itself is not what you want. IF src_usr = dest_usr THEN RETURN; END IF; -- do some initial cleanup UPDATE actor.usr SET card = NULL WHERE id = src_usr; UPDATE actor.usr SET mailing_address = NULL WHERE id = src_usr; UPDATE actor.usr SET billing_address = NULL WHERE id = src_usr; -- actor.* IF del_cards THEN DELETE FROM actor.card where usr = src_usr; ELSE IF deactivate_cards THEN UPDATE actor.card SET active = 'f' WHERE usr = src_usr; END IF; UPDATE actor.card SET usr = dest_usr WHERE usr = src_usr; END IF; IF del_addrs THEN DELETE FROM actor.usr_address WHERE usr = src_usr; ELSE UPDATE actor.usr_address SET usr = dest_usr WHERE usr = src_usr; END IF; UPDATE actor.usr_note SET usr = dest_usr WHERE usr = src_usr; -- dupes are technically OK in actor.usr_standing_penalty, should manually delete them... UPDATE actor.usr_standing_penalty SET usr = dest_usr WHERE usr = src_usr; PERFORM actor.usr_merge_rows('actor.usr_org_unit_opt_in', 'usr', src_usr, dest_usr); PERFORM actor.usr_merge_rows('actor.usr_setting', 'usr', src_usr, dest_usr); -- permission.* PERFORM actor.usr_merge_rows('permission.usr_perm_map', 'usr', src_usr, dest_usr); PERFORM actor.usr_merge_rows('permission.usr_object_perm_map', 'usr', src_usr, dest_usr); PERFORM actor.usr_merge_rows('permission.usr_grp_map', 'usr', src_usr, dest_usr); PERFORM actor.usr_merge_rows('permission.usr_work_ou_map', 'usr', src_usr, dest_usr); -- container.* -- For each *_bucket table: transfer every bucket belonging to src_usr -- into the custody of dest_usr. -- -- In order to avoid colliding with an existing bucket owned by -- the destination user, append the source user's id (in parenthesese) -- to the name. If you still get a collision, add successive -- spaces to the name and keep trying until you succeed. -- FOR bucket_row in SELECT id, name FROM container.biblio_record_entry_bucket WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE container.biblio_record_entry_bucket SET owner = dest_usr, name = name || suffix WHERE id = bucket_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; FOR bucket_row in SELECT id, name FROM container.call_number_bucket WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE container.call_number_bucket SET owner = dest_usr, name = name || suffix WHERE id = bucket_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; FOR bucket_row in SELECT id, name FROM container.copy_bucket WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE container.copy_bucket SET owner = dest_usr, name = name || suffix WHERE id = bucket_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; FOR bucket_row in SELECT id, name FROM container.user_bucket WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE container.user_bucket SET owner = dest_usr, name = name || suffix WHERE id = bucket_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; UPDATE container.user_bucket_item SET target_user = dest_usr WHERE target_user = src_usr; -- vandelay.* -- transfer queues the same way we transfer buckets (see above) FOR queue_row in SELECT id, name FROM vandelay.queue WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE vandelay.queue SET owner = dest_usr, name = name || suffix WHERE id = queue_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; UPDATE vandelay.session_tracker SET usr = dest_usr WHERE usr = src_usr; -- money.* PERFORM actor.usr_merge_rows('money.collections_tracker', 'usr', src_usr, dest_usr); PERFORM actor.usr_merge_rows('money.collections_tracker', 'collector', src_usr, dest_usr); UPDATE money.billable_xact SET usr = dest_usr WHERE usr = src_usr; UPDATE money.billing SET voider = dest_usr WHERE voider = src_usr; UPDATE money.bnm_payment SET accepting_usr = dest_usr WHERE accepting_usr = src_usr; -- action.* UPDATE action.circulation SET usr = dest_usr WHERE usr = src_usr; UPDATE action.circulation SET circ_staff = dest_usr WHERE circ_staff = src_usr; UPDATE action.circulation SET checkin_staff = dest_usr WHERE checkin_staff = src_usr; UPDATE action.usr_circ_history SET usr = dest_usr WHERE usr = src_usr; UPDATE action.hold_request SET usr = dest_usr WHERE usr = src_usr; UPDATE action.hold_request SET fulfillment_staff = dest_usr WHERE fulfillment_staff = src_usr; UPDATE action.hold_request SET requestor = dest_usr WHERE requestor = src_usr; UPDATE action.hold_notification SET notify_staff = dest_usr WHERE notify_staff = src_usr; UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr; UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr; UPDATE action.non_cataloged_circulation SET patron = dest_usr WHERE patron = src_usr; UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr; UPDATE action.survey_response SET usr = dest_usr WHERE usr = src_usr; -- acq.* UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr; UPDATE acq.fund_transfer SET transfer_user = dest_usr WHERE transfer_user = src_usr; UPDATE acq.invoice SET closed_by = dest_usr WHERE closed_by = src_usr; -- transfer picklists the same way we transfer buckets (see above) FOR picklist_row in SELECT id, name FROM acq.picklist WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE acq.picklist SET owner = dest_usr, name = name || suffix WHERE id = picklist_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; UPDATE acq.purchase_order SET owner = dest_usr WHERE owner = src_usr; UPDATE acq.po_note SET creator = dest_usr WHERE creator = src_usr; UPDATE acq.po_note SET editor = dest_usr WHERE editor = src_usr; UPDATE acq.provider_note SET creator = dest_usr WHERE creator = src_usr; UPDATE acq.provider_note SET editor = dest_usr WHERE editor = src_usr; UPDATE acq.lineitem_note SET creator = dest_usr WHERE creator = src_usr; UPDATE acq.lineitem_note SET editor = dest_usr WHERE editor = src_usr; UPDATE acq.lineitem_usr_attr_definition SET usr = dest_usr WHERE usr = src_usr; -- asset.* UPDATE asset.copy SET creator = dest_usr WHERE creator = src_usr; UPDATE asset.copy SET editor = dest_usr WHERE editor = src_usr; UPDATE asset.copy_note SET creator = dest_usr WHERE creator = src_usr; UPDATE asset.call_number SET creator = dest_usr WHERE creator = src_usr; UPDATE asset.call_number SET editor = dest_usr WHERE editor = src_usr; UPDATE asset.call_number_note SET creator = dest_usr WHERE creator = src_usr; -- serial.* UPDATE serial.record_entry SET creator = dest_usr WHERE creator = src_usr; UPDATE serial.record_entry SET editor = dest_usr WHERE editor = src_usr; -- reporter.* -- It's not uncommon to define the reporter schema in a replica -- DB only, so don't assume these tables exist in the write DB. BEGIN UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr; EXCEPTION WHEN undefined_table THEN -- do nothing END; BEGIN UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr; EXCEPTION WHEN undefined_table THEN -- do nothing END; BEGIN UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr; EXCEPTION WHEN undefined_table THEN -- do nothing END; BEGIN -- transfer folders the same way we transfer buckets (see above) FOR folder_row in SELECT id, name FROM reporter.template_folder WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE reporter.template_folder SET owner = dest_usr, name = name || suffix WHERE id = folder_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; EXCEPTION WHEN undefined_table THEN -- do nothing END; BEGIN -- transfer folders the same way we transfer buckets (see above) FOR folder_row in SELECT id, name FROM reporter.report_folder WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE reporter.report_folder SET owner = dest_usr, name = name || suffix WHERE id = folder_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; EXCEPTION WHEN undefined_table THEN -- do nothing END; BEGIN -- transfer folders the same way we transfer buckets (see above) FOR folder_row in SELECT id, name FROM reporter.output_folder WHERE owner = src_usr LOOP suffix := ' (' || src_usr || ')'; LOOP BEGIN UPDATE reporter.output_folder SET owner = dest_usr, name = name || suffix WHERE id = folder_row.id; EXCEPTION WHEN unique_violation THEN suffix := suffix || ' '; CONTINUE; END; EXIT; END LOOP; END LOOP; EXCEPTION WHEN undefined_table THEN -- do nothing END; -- propagate preferred name values from the source user to the -- destination user, but only when values are not being replaced. WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr) UPDATE actor.usr SET pref_prefix = COALESCE(pref_prefix, (SELECT pref_prefix FROM susr)), pref_first_given_name = COALESCE(pref_first_given_name, (SELECT pref_first_given_name FROM susr)), pref_second_given_name = COALESCE(pref_second_given_name, (SELECT pref_second_given_name FROM susr)), pref_family_name = COALESCE(pref_family_name, (SELECT pref_family_name FROM susr)), pref_suffix = COALESCE(pref_suffix, (SELECT pref_suffix FROM susr)) WHERE id = dest_usr; -- Copy and deduplicate name keywords -- String -> array -> rows -> DISTINCT -> array -> string WITH susr AS (SELECT * FROM actor.usr WHERE id = src_usr), dusr AS (SELECT * FROM actor.usr WHERE id = dest_usr) UPDATE actor.usr SET name_keywords = ( WITH keywords AS ( SELECT DISTINCT UNNEST( REGEXP_SPLIT_TO_ARRAY( COALESCE((SELECT name_keywords FROM susr), '') || ' ' || COALESCE((SELECT name_keywords FROM dusr), ''), E'\\s+' ) ) AS parts ) SELECT ARRAY_TO_STRING(ARRAY_AGG(kw.parts), ' ') FROM keywords kw ) WHERE id = dest_usr; -- Finally, delete the source user DELETE FROM actor.usr WHERE id = src_usr; END; $$ LANGUAGE plpgsql; COMMIT;