From c202ac944ca2d71f0f5eaedbc33a8205525d3c5c Mon Sep 17 00:00:00 2001
From: Galen Charlton <gmc@equinoxinitiative.org>
Date: Wed, 20 Sep 2017 22:27:22 -0400
Subject: [PATCH] forward-port 2.12.5-2.12.6 schema update
Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
---
.../version-upgrade/2.12.5-2.12.6-upgrade-db.sql | 327 +++++++++++++++++++++
1 file changed, 327 insertions(+)
create mode 100644 Open-ILS/src/sql/Pg/version-upgrade/2.12.5-2.12.6-upgrade-db.sql
diff --git a/Open-ILS/src/sql/Pg/version-upgrade/2.12.5-2.12.6-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/2.12.5-2.12.6-upgrade-db.sql
new file mode 100644
index 0000000000..cca63a2ee2
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/version-upgrade/2.12.5-2.12.6-upgrade-db.sql
@@ -0,0 +1,327 @@
+--Upgrade Script for 2.12.5 to 2.12.6
+\set eg_version '''2.12.6'''
+BEGIN;
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.12.6', :eg_version);
+
+SELECT evergreen.upgrade_deps_block_check('1055', :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
+
+ -- 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;
--
2.11.0