BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0172'); -- Scott McKellar CREATE TABLE acq.provider_note ( id SERIAL PRIMARY KEY, provider INT NOT NULL REFERENCES acq.provider (id) DEFERRABLE INITIALLY DEFERRED, creator INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, editor INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED, create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), edit_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(), value TEXT NOT NULL ); CREATE INDEX acq_pro_note_pro_idx ON acq.provider_note ( provider ); CREATE INDEX acq_pro_note_creator_idx ON acq.provider_note ( creator ); CREATE INDEX acq_pro_note_editor_idx ON acq.provider_note ( editor ); CREATE OR REPLACE FUNCTION actor.usr_purge_data( src_usr IN INTEGER, dest_usr IN INTEGER ) RETURNS VOID AS $$ DECLARE suffix TEXT; renamable_row RECORD; BEGIN 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; 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.provider_note SET creator = dest_usr WHERE creator = src_usr; UPDATE acq.provider_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; -- 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; -- 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; END; $$ LANGUAGE plpgsql; COMMIT;