LP#1394356 Patron purge circs retain post code
authorBill Erickson <berickxx@gmail.com>
Fri, 28 Nov 2014 21:29:57 +0000 (16:29 -0500)
committerBen Shum <bshum@biblio.org>
Tue, 16 Dec 2014 20:14:59 +0000 (15:14 -0500)
When purging a patron, clear the address data after circulation
anonymization to ensure the usr_post_code is retained.

Signed-off-by: Bill Erickson <berickxx@gmail.com>
Signed-off-by: Ben Shum <bshum@biblio.org>
Open-ILS/src/sql/Pg/999.functions.global.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron_purge_post_code.sql [new file with mode: 0644]

index a339b1a..d724afc 100644 (file)
@@ -379,13 +379,6 @@ BEGIN
                dest_usr := specified_dest_usr;
        END IF;
 
-       UPDATE actor.usr SET
-               active = FALSE,
-               card = NULL,
-               mailing_address = NULL,
-               billing_address = NULL
-       WHERE id = 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;
@@ -684,6 +677,15 @@ BEGIN
                END LOOP;
        END LOOP;
 
+    -- 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;
 
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron_purge_post_code.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron_purge_post_code.sql
new file mode 100644 (file)
index 0000000..a418b09
--- /dev/null
@@ -0,0 +1,332 @@
+BEGIN;
+
+-- SELECT evergreen.upgrade_deps_block_check('TODO', :eg_version);
+
+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;
+
+       -- 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;
+       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;
+       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;
+
+       -- actor.*
+       DELETE FROM actor.card WHERE usr = src_usr;
+       DELETE FROM actor.stat_cat_entry_usr_map WHERE target_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_note WHERE usr = src_usr;
+       UPDATE actor.usr_note SET creator = dest_usr WHERE creator = 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_standing_penalty SET staff = dest_usr WHERE staff = 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;
+
+    -- 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;
+