LP 1786534: Don't merge a user with itself.

It's possible to merge a user with itself using the actor.usr_merge
database function.  This does not produce a useful result.  One ends
up with a user that is flagged deleted, has a null card field, and is
likely to have their addresses and other cards deleted from the
database.

A simple check at the top of the database function to bail if the
src_usr and dest_usr are the same prevents this from happening.  That
is the change that this commit makes.

Signed-off-by: Jason Stephenson <jason@sigio.com>
Signed-off-by: Michele Morgan <mmorgan@noblenet.org>
Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>

diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql
index 5b2bc74..68e4748 100644 (file)
--- a/Open-ILS/src/sql/Pg/999.functions.global.sql
+++ b/Open-ILS/src/sql/Pg/999.functions.global.sql
@@ -51,6 +51,12 @@ DECLARE
        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;

diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor_usr_merge-bail-on-same-user.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor_usr_merge-bail-on-same-user.sql
new file mode 100644 (file)
index 0000000..9f295c9
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.actor_usr_merge-bail-on-same-user.sql
@@ -0,0 +1,327 @@
+BEGIN;
+
+-- SELECT evergreen.upgrade_deps_block_check('XXXX', :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 probably 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;
+
+COMMIT;