BEGIN; INSERT INTO config.upgrade_log (version) VALUES ('0198'); -- Scott McKellar CREATE TABLE acq.claim_type ( id SERIAL PRIMARY KEY, org_unit INT NOT NULL REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED, code TEXT NOT NULL, description TEXT NOT NULL, CONSTRAINT claim_type_once_per_org UNIQUE ( org_unit, code ) ); CREATE TABLE acq.claim_event_type ( id SERIAL PRIMARY KEY, org_unit INT NOT NULL REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED, code TEXT NOT NULL, description TEXT NOT NULL, library_initiated BOOL NOT NULL DEFAULT FALSE, CONSTRAINT event_type_once_per_org UNIQUE ( org_unit, code ) ); CREATE TABLE acq.claim ( id SERIAL PRIMARY KEY, type INT NOT NULL REFERENCES acq.claim_type DEFERRABLE INITIALLY DEFERRED, lineitem_detail BIGINT NOT NULL REFERENCES acq.lineitem_detail DEFERRABLE INITIALLY DEFERRED ); CREATE INDEX claim_lid_idx ON acq.claim( lineitem_detail ); CREATE TABLE acq.claim_event ( id BIGSERIAL PRIMARY KEY, type INT NOT NULL REFERENCES acq.claim_event_type DEFERRABLE INITIALLY DEFERRED, claim SERIAL NOT NULL REFERENCES acq.claim DEFERRABLE INITIALLY DEFERRED, event_date TIMESTAMPTZ NOT NULL DEFAULT now(), creator INT NOT NULL REFERENCES actor.usr DEFERRABLE INITIALLY DEFERRED, note TEXT ); CREATE INDEX claim_event_claim_date_idx ON acq.claim_event( claim, event_date ); 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.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; -- 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;