Stamping upgrade scripts and refifying baseline schema

[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 1405.schema.add_cancel_info_to_hold_requests.sql

BEGIN;

SELECT evergreen.upgrade_deps_block_check('1405', :eg_version);

ALTER TABLE action.hold_request 
ADD COLUMN canceled_by INT REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
ADD COLUMN canceling_ws INT REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED;

CREATE INDEX hold_request_canceled_by_idx ON action.hold_request (canceled_by);
CREATE INDEX hold_request_canceling_ws_idx ON action.hold_request (canceling_ws);

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_message 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_request SET canceled_by = dest_usr WHERE canceled_by = 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 STRING_AGG(kw.parts, ' ') FROM keywords kw
    ) WHERE id = dest_usr;

    -- Finally, delete the source user
    PERFORM actor.usr_delete(src_usr,dest_usr);

END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION actor.usr_purge_data(
        src_usr  IN INTEGER,
        specified_dest_usr IN INTEGER
) RETURNS VOID AS $$
DECLARE
        suffix TEXT;
        renamable_row RECORD;
        dest_usr INTEGER;
BEGIN

        IF specified_dest_usr IS NULL THEN
                dest_usr := 1; -- Admin user on stock installs
        ELSE
                dest_usr := specified_dest_usr;
        END IF;

    -- action_trigger.event (even doing this, event_output may--and probably does--contain PII and should have a retention/removal policy)
    UPDATE action_trigger.event SET context_user = dest_usr WHERE context_user = src_usr;

        -- acq.*
        UPDATE acq.fund_allocation SET allocator = dest_usr WHERE allocator = src_usr;
        UPDATE acq.lineitem SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.lineitem SET editor = dest_usr WHERE editor = src_usr;
        UPDATE acq.lineitem SET selector = dest_usr WHERE selector = 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.invoice SET closed_by = dest_usr WHERE closed_by = src_usr;
        DELETE FROM acq.lineitem_usr_attr_definition WHERE usr = src_usr;

        -- Update with a rename to avoid collisions
        FOR renamable_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 = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        UPDATE acq.picklist SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.picklist SET editor = dest_usr WHERE editor = 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.purchase_order SET owner = dest_usr WHERE owner = src_usr;
        UPDATE acq.purchase_order SET creator = dest_usr WHERE creator = src_usr;
        UPDATE acq.purchase_order SET editor = dest_usr WHERE editor = src_usr;
        UPDATE acq.claim_event SET creator = dest_usr WHERE creator = src_usr;

        -- action.*
        DELETE FROM action.circulation 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.hold_notification SET notify_staff = dest_usr WHERE notify_staff = 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_request SET canceled_by = dest_usr WHERE canceled_by = src_usr;
        DELETE FROM action.hold_request WHERE usr = src_usr;
        UPDATE action.in_house_use SET staff = dest_usr WHERE staff = src_usr;
        UPDATE action.non_cat_in_house_use SET staff = dest_usr WHERE staff = src_usr;
        DELETE FROM action.non_cataloged_circulation WHERE patron = src_usr;
        UPDATE action.non_cataloged_circulation SET staff = dest_usr WHERE staff = src_usr;
        DELETE FROM action.survey_response WHERE usr = src_usr;
        UPDATE action.fieldset SET owner = dest_usr WHERE owner = src_usr;
        DELETE FROM action.usr_circ_history WHERE usr = src_usr;
        UPDATE action.curbside SET notes = NULL WHERE patron = src_usr;

        -- actor.*
        DELETE FROM actor.card WHERE usr = src_usr;
        DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr = src_usr;
        DELETE FROM actor.usr_privacy_waiver WHERE usr = src_usr;
        DELETE FROM actor.usr_message WHERE usr = src_usr;

        -- The following update is intended to avoid transient violations of a foreign
        -- key constraint, whereby actor.usr_address references itself.  It may not be
        -- necessary, but it does no harm.
        UPDATE actor.usr_address SET replaces = NULL
                WHERE usr = src_usr AND replaces IS NOT NULL;
        DELETE FROM actor.usr_address WHERE usr = src_usr;
        DELETE FROM actor.usr_org_unit_opt_in WHERE usr = src_usr;
        UPDATE actor.usr_org_unit_opt_in SET staff = dest_usr WHERE staff = src_usr;
        DELETE FROM actor.usr_setting WHERE usr = src_usr;
        DELETE FROM actor.usr_standing_penalty WHERE usr = src_usr;
        UPDATE actor.usr_message SET title = 'purged', message = 'purged', read_date = NOW() WHERE usr = src_usr;
        DELETE FROM actor.usr_message WHERE usr = src_usr;
        UPDATE actor.usr_standing_penalty SET staff = dest_usr WHERE staff = src_usr;
        UPDATE actor.usr_message SET editor = dest_usr WHERE editor = src_usr;

        -- asset.*
        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;
        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;

        -- auditor.*
        DELETE FROM auditor.actor_usr_address_history WHERE id = src_usr;
        DELETE FROM auditor.actor_usr_history WHERE id = src_usr;
        UPDATE auditor.asset_call_number_history SET creator = dest_usr WHERE creator = src_usr;
        UPDATE auditor.asset_call_number_history SET editor  = dest_usr WHERE editor  = src_usr;
        UPDATE auditor.asset_copy_history SET creator = dest_usr WHERE creator = src_usr;
        UPDATE auditor.asset_copy_history SET editor  = dest_usr WHERE editor  = src_usr;
        UPDATE auditor.biblio_record_entry_history SET creator = dest_usr WHERE creator = src_usr;
        UPDATE auditor.biblio_record_entry_history SET editor  = dest_usr WHERE editor  = src_usr;

        -- biblio.*
        UPDATE biblio.record_entry SET creator = dest_usr WHERE creator = src_usr;
        UPDATE biblio.record_entry SET editor = dest_usr WHERE editor = src_usr;
        UPDATE biblio.record_note SET creator = dest_usr WHERE creator = src_usr;
        UPDATE biblio.record_note SET editor = dest_usr WHERE editor = src_usr;

        -- container.*
        -- Update buckets with a rename to avoid collisions
        FOR renamable_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 = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        FOR renamable_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 = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        FOR renamable_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 = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        FOR renamable_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 = renamable_row.id;
                        EXCEPTION WHEN unique_violation THEN
                                suffix := suffix || ' ';
                                CONTINUE;
                        END;
                        EXIT;
                END LOOP;
        END LOOP;

        DELETE FROM container.user_bucket_item WHERE target_user = src_usr;

        -- money.*
        DELETE FROM money.billable_xact WHERE usr = src_usr;
        DELETE FROM money.collections_tracker WHERE usr = src_usr;
        UPDATE money.collections_tracker SET collector = dest_usr WHERE collector = src_usr;

        -- permission.*
        DELETE FROM permission.usr_grp_map WHERE usr = src_usr;
        DELETE FROM permission.usr_object_perm_map WHERE usr = src_usr;
        DELETE FROM permission.usr_perm_map WHERE usr = src_usr;
        DELETE FROM permission.usr_work_ou_map WHERE usr = src_usr;

        -- reporter.*
        -- Update with a rename to avoid collisions
        BEGIN
                FOR renamable_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 = renamable_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
                UPDATE reporter.report SET owner = dest_usr WHERE owner = src_usr;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        -- Update with a rename to avoid collisions
        BEGIN
                FOR renamable_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 = renamable_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
                UPDATE reporter.schedule SET runner = dest_usr WHERE runner = src_usr;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        BEGIN
                UPDATE reporter.template SET owner = dest_usr WHERE owner = src_usr;
        EXCEPTION WHEN undefined_table THEN
                -- do nothing
        END;

        -- Update with a rename to avoid collisions
        BEGIN
                FOR renamable_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 = renamable_row.id;
                                EXCEPTION WHEN unique_violation THEN
                                        suffix := suffix || ' ';
                                        CONTINUE;
                                END;
                                EXIT;
                        END LOOP;
                END LOOP;
        EXCEPTION WHEN undefined_table THEN
        -- do nothing
        END;

        -- vandelay.*
        -- Update with a rename to avoid collisions
        FOR renamable_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 = renamable_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;

    -- NULL-ify addresses last so other cleanup (e.g. circ anonymization)
    -- can access the information before deletion.
        UPDATE actor.usr SET
                active = FALSE,
                card = NULL,
                mailing_address = NULL,
                billing_address = NULL
        WHERE id = src_usr;

END;
$$ LANGUAGE plpgsql;

COMMIT;