From ab9ba5a1dcb53e593f06ac4d09c6db15c2f74777 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 6 Oct 2021 11:48:59 -0400 Subject: [PATCH] LP#1846354: add missing function updates to schema update Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- .../upgrade/1304.schema.update_actor_usr_funcs.sql | 459 +++++++++++++++++++++ 2 files changed, 460 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/1304.schema.update_actor_usr_funcs.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index dbb1d616c1..1e69f67be2 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -92,7 +92,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 ('1303', :eg_version); -- Dyrcona / jweston / gmcharlt +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1304', :eg_version); -- phasefx/csharp/gmcharlt CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/1304.schema.update_actor_usr_funcs.sql b/Open-ILS/src/sql/Pg/upgrade/1304.schema.update_actor_usr_funcs.sql new file mode 100644 index 0000000000..60f73442c8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/1304.schema.update_actor_usr_funcs.sql @@ -0,0 +1,459 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('1304', :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; + + -- 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; + 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; + + -- 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; + + -- 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; + +CREATE OR REPLACE FUNCTION actor.usr_delete( + src_usr IN INTEGER, + dest_usr IN INTEGER +) RETURNS VOID AS $$ +DECLARE + old_profile actor.usr.profile%type; + old_home_ou actor.usr.home_ou%type; + new_profile actor.usr.profile%type; + new_home_ou actor.usr.home_ou%type; + new_name text; + new_dob actor.usr.dob%type; +BEGIN + SELECT + id || '-PURGED-' || now(), + profile, + home_ou, + dob + INTO + new_name, + old_profile, + old_home_ou, + new_dob + FROM + actor.usr + WHERE + id = src_usr; + -- + -- Quit if no such user + -- + IF old_profile IS NULL THEN + RETURN; + END IF; + -- + perform actor.usr_purge_data( src_usr, dest_usr ); + -- + -- Find the root grp_tree and the root org_unit. This would be simpler if we + -- could assume that there is only one root. Theoretically, someday, maybe, + -- there could be multiple roots, so we take extra trouble to get the right ones. + -- + SELECT + id + INTO + new_profile + FROM + permission.grp_ancestors( old_profile ) + WHERE + parent is null; + -- + SELECT + id + INTO + new_home_ou + FROM + actor.org_unit_ancestors( old_home_ou ) + WHERE + parent_ou is null; + -- + -- Truncate date of birth + -- + IF new_dob IS NOT NULL THEN + new_dob := date_trunc( 'year', new_dob ); + END IF; + -- + UPDATE + actor.usr + SET + card = NULL, + profile = new_profile, + usrname = new_name, + email = NULL, + passwd = random()::text, + standing = DEFAULT, + ident_type = + ( + SELECT MIN( id ) + FROM config.identification_type + ), + ident_value = NULL, + ident_type2 = NULL, + ident_value2 = NULL, + net_access_level = DEFAULT, + photo_url = NULL, + prefix = NULL, + first_given_name = new_name, + second_given_name = NULL, + family_name = new_name, + suffix = NULL, + alias = NULL, + guardian = NULL, + day_phone = NULL, + evening_phone = NULL, + other_phone = NULL, + mailing_address = NULL, + billing_address = NULL, + home_ou = new_home_ou, + dob = new_dob, + active = FALSE, + master_account = DEFAULT, + super_user = DEFAULT, + barred = FALSE, + deleted = TRUE, + juvenile = DEFAULT, + usrgroup = 0, + claims_returned_count = DEFAULT, + credit_forward_balance = DEFAULT, + last_xact_id = DEFAULT, + pref_prefix = NULL, + pref_first_given_name = NULL, + pref_second_given_name = NULL, + pref_family_name = NULL, + pref_suffix = NULL, + name_keywords = NULL, + create_date = now(), + expire_date = now() + WHERE + id = src_usr; +END; +$$ LANGUAGE plpgsql; + +COMMIT; -- 2.11.0