From 3424b298bdc7a347aa70587939190f7d53c60a01 Mon Sep 17 00:00:00 2001 From: Ben Shum Date: Tue, 16 Dec 2014 15:15:57 -0500 Subject: [PATCH] LP#1394356: Stamping upgrade script for patron purge post code fix Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- .../XXXX.schema.patron_purge_post_code.sql | 332 ------------------ 2 files changed, 1 insertion(+), 333 deletions(-) delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron_purge_post_code.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index a62ee63717..e9f599d56f 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -91,7 +91,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0900', :eg_version); -- miker/bshum +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0901', :eg_version); -- berick/bshum CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, 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 deleted file mode 100644 index a418b09972..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.patron_purge_post_code.sql +++ /dev/null @@ -1,332 +0,0 @@ -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; - -- 2.43.2