--Upgrade Script for 3.0.11 to 3.0.12 \set eg_version '''3.0.12''' BEGIN; INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.0.12', :eg_version); SELECT evergreen.upgrade_deps_block_check('1130', :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; -- 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; -- 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; -- Finally, delete the source user DELETE FROM actor.usr WHERE id = src_usr; END; $$ LANGUAGE plpgsql; SELECT evergreen.upgrade_deps_block_check('1133', :eg_version); \qecho Applying a unique constraint to action.transit_copy. This will \qecho only effect newly created transits. Admins are encouraged to manually \qecho remove any existing duplicate transits by applying values for cancel_time \qecho or dest_recv_time, or by deleting the offending transits. Below is a \qecho query to locate duplicate transits. Note dupes may exist accross \qecho parent (action.transit_copy) and child tables (action.hold_transit_copy, \qecho action.reservation_transit_copy) \qecho \qecho WITH dupe_transits AS ( \qecho SELECT COUNT(*), target_copy FROM action.transit_copy \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL \qecho GROUP BY 2 HAVING COUNT(*) > 1 \qecho ) SELECT atc.* \qecho FROM dupe_transits \qecho JOIN action.transit_copy atc USING (target_copy) \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL; \qecho /* Unique indexes are not inherited by child tables, so they will not prevent duplicate inserts on action.transit_copy and action.hold_transit_copy, for example. Use check constraints instead to enforce unique-per-copy transits accross all transit types. */ -- Create an index for speedy check constraint lookups. CREATE INDEX active_transit_for_copy ON action.transit_copy (target_copy) WHERE dest_recv_time IS NULL AND cancel_time IS NULL; -- Check for duplicate transits across all transit types CREATE OR REPLACE FUNCTION action.copy_transit_is_unique() RETURNS TRIGGER AS $func$ BEGIN PERFORM * FROM action.transit_copy WHERE target_copy = NEW.target_copy AND dest_recv_time IS NULL AND cancel_time IS NULL; IF FOUND THEN RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy; END IF; RETURN NULL; END; $func$ LANGUAGE PLPGSQL STABLE; -- Apply constraint to all transit tables CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check AFTER INSERT ON action.transit_copy FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check AFTER INSERT ON action.hold_transit_copy FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check AFTER INSERT ON action.reservation_transit_copy FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); /* -- UNDO DROP TRIGGER transit_copy_is_unique_check ON action.transit_copy; DROP TRIGGER hold_transit_copy_is_unique_check ON action.hold_transit_copy; DROP TRIGGER reservation_transit_copy_is_unique_check ON action.reservation_transit_copy; DROP INDEX action.active_transit_for_copy; */ COMMIT;