OU data removal script

This is a set of SQL scripts to be run, in order, to completely
remove data belonging to an OU and its descendants in an Evergreen
database.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>

diff --git a/remove_ou_data/01_easy_deletes.sql b/remove_ou_data/01_easy_deletes.sql
new file mode 100644 (file)
index 0000000..e6d58ec
--- /dev/null
+++ b/remove_ou_data/01_easy_deletes.sql
@@ -0,0 +1,84 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+BEGIN;
+
+DELETE FROM acq.distribution_formula_entry WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM acq.fund_allocation_percent WHERE org IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM action.fieldset WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM action.in_house_use WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM action.non_cataloged_circulation WHERE circ_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM action.non_cat_in_house_use WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.address_alert WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.hours_of_operation WHERE id IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_lasso_map WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_unit_closed WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_unit_custom_tree_node WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_unit_proximity_adjustment WHERE hold_pickup_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_unit_proximity_adjustment WHERE hold_request_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_unit_proximity_adjustment WHERE item_circ_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_unit_proximity_adjustment WHERE item_owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.org_unit_setting WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.search_filter_group WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.stat_cat_entry_default WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.stat_cat_entry WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.toolbar WHERE org IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.usr_org_unit_opt_in WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM actor.usr_standing_penalty WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM asset.copy_template WHERE circ_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM asset.copy_template WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM booking.reservation WHERE pickup_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM booking.reservation WHERE request_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM booking.resource_attr WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM booking.resource_attr_value WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM booking.resource WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM booking.resource_type WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.barcode_completion WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.circ_limit_set WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.circ_matrix_matchpoint WHERE copy_circ_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.circ_matrix_matchpoint WHERE copy_owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.circ_matrix_matchpoint WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.circ_matrix_matchpoint WHERE user_home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.filter_dialog_filter_set WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.floating_group_member WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.hold_matrix_matchpoint WHERE item_circ_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.hold_matrix_matchpoint WHERE item_owning_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.hold_matrix_matchpoint WHERE pickup_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.hold_matrix_matchpoint WHERE request_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.hold_matrix_matchpoint WHERE user_home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.idl_field_doc WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.org_unit_setting_type_log WHERE org IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.remote_account WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.weight_assoc WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM config.z3950_source_credentials WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM money.collections_tracker WHERE location IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM permission.grp_penalty_threshold WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM permission.usr_work_ou_map WHERE work_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM serial.distribution WHERE holding_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM serial.record_entry WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM serial.subscription WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM url_verify.session WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM vandelay.import_bib_trash_group WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM vandelay.import_item_attr_definition WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM vandelay.match_set WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM vandelay.merge_profile WHERE owner IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+COMMIT;

diff --git a/remove_ou_data/02_remove_patron_extras.sql b/remove_ou_data/02_remove_patron_extras.sql
new file mode 100644 (file)
index 0000000..bc9b96a
--- /dev/null
+++ b/remove_ou_data/02_remove_patron_extras.sql
@@ -0,0 +1,66 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+ALTER TABLE actor.usr_address DISABLE TRIGGER audit_actor_usr_address_update_trigger;
+ALTER TABLE actor.usr DISABLE TRIGGER audit_actor_usr_update_trigger;
+
+CREATE INDEX tmp_addr_replaces ON actor.usr_address(replaces);
+BEGIN;
+
+DELETE FROM actor.usr_note WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.stat_cat_entry_usr_map WHERE target_usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.usr_activity WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.usr_org_unit_opt_in WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.usr_password_reset WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.usr_saved_search WHERE owner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.usr_setting WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.usr_standing_penalty WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM actor.card WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+UPDATE actor.usr SET card = NULL
+WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM actor.usr_address WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+UPDATE actor.usr SET mailing_address = NULL, billing_address = NULL
+WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+COMMIT;
+
+DROP INDEX actor.tmp_addr_replaces;
+ALTER TABLE actor.usr_address ENABLE TRIGGER audit_actor_usr_address_update_trigger;
+ALTER TABLE actor.usr ENABLE TRIGGER audit_actor_usr_update_trigger;

diff --git a/remove_ou_data/03_remove_holds.sql b/remove_ou_data/03_remove_holds.sql
new file mode 100644 (file)
index 0000000..76dfa14
--- /dev/null
+++ b/remove_ou_data/03_remove_holds.sql
@@ -0,0 +1,46 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+ALTER TABLE action.hold_request DISABLE TRIGGER action_hold_request_aging_tgr;
+
+BEGIN;
+
+DELETE FROM action.hold_request WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM action.hold_request WHERE current_shelf_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM action.hold_request WHERE pickup_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM action.aged_hold_request WHERE pickup_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM action.aged_hold_request WHERE request_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+-- FIXME: hold placed by EXAMPLE staff for non-EXAMPLE patron
+DELETE FROM action.hold_request WHERE requestor IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+COMMIT;
+
+ALTER TABLE action.hold_request ENABLE TRIGGER action_hold_request_aging_tgr;

diff --git a/remove_ou_data/04_remove_circ.sql b/remove_ou_data/04_remove_circ.sql
new file mode 100644 (file)
index 0000000..c34da32
--- /dev/null
+++ b/remove_ou_data/04_remove_circ.sql
@@ -0,0 +1,46 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+ALTER TABLE action.circulation DISABLE TRIGGER action_circulation_aging_tgr;
+ALTER TABLE action.circulation DISABLE TRIGGER age_parent_circ;
+
+BEGIN;
+
+DELETE FROM action.circulation WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM action.circulation WHERE circ_staff IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM action.circulation WHERE circ_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM action.aged_circulation WHERE circ_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM action.aged_circulation WHERE copy_circ_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+DELETE FROM action.aged_circulation WHERE copy_owning_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+COMMIT;
+
+ALTER TABLE action.circulation ENABLE TRIGGER action_circulation_aging_tgr;
+ALTER TABLE action.circulation ENABLE TRIGGER age_parent_circ;

diff --git a/remove_ou_data/05_remove_billings.sql b/remove_ou_data/05_remove_billings.sql
new file mode 100644 (file)
index 0000000..ba1f66a
--- /dev/null
+++ b/remove_ou_data/05_remove_billings.sql
@@ -0,0 +1,112 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+ALTER TABLE money.cash_payment DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.goods_payment DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.credit_payment DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.check_payment DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.credit_card_payment DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.forgive_payment DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.payment DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.billing DISABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.grocery DISABLE TRIGGER mat_summary_remove_tgr;
+
+BEGIN;
+
+DELETE FROM money.credit_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.credit_card_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.check_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.cash_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.goods_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.work_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.forgive_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.bnm_desk_payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.payment
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.billing
+WHERE xact IN (
+SELECT usr FROM money.billable_xact WHERE usr IN 
+    (SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del))
+);
+
+DELETE FROM money.grocery WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM money.billable_xact WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM money.materialized_billable_xact_summary WHERE usr IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+COMMIT;
+
+ALTER TABLE money.cash_payment ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.goods_payment ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.credit_payment ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.check_payment ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.credit_card_payment ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.forgive_payment ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.payment ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.billing ENABLE TRIGGER mat_summary_del_tgr;
+ALTER TABLE money.grocery ENABLE TRIGGER mat_summary_remove_tgr;

diff --git a/remove_ou_data/06_remove_copies.sql b/remove_ou_data/06_remove_copies.sql
new file mode 100644 (file)
index 0000000..d37b113
--- /dev/null
+++ b/remove_ou_data/06_remove_copies.sql
@@ -0,0 +1,62 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+ALTER TABLE asset.copy DISABLE RULE protect_copy_delete;
+ALTER TABLE asset.copy DISABLE TRIGGER audit_asset_copy_update_trigger;
+CREATE INDEX tmp_import_as ON vandelay.import_item(imported_as);
+
+BEGIN;
+
+-- NOTE: no FK
+DELETE FROM asset.opac_visible_copies WHERE copy_id IN (
+  SELECT id FROM asset.copy WHERE circ_lib IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+-- NOTE: no FK
+DELETE FROM asset.copy_part_map WHERE target_copy IN (
+  SELECT id FROM asset.copy WHERE circ_lib IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+
+-- NOTE: no FK
+DELETE FROM asset.stat_cat_entry_copy_map WHERE owning_copy IN (
+  SELECT id FROM asset.copy WHERE circ_lib IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+
+DELETE FROM asset.copy_note WHERE owning_copy IN (
+  SELECT id FROM asset.copy WHERE circ_lib IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+
+DELETE FROM vandelay.import_item WHERE imported_as IN (
+  SELECT id FROM asset.copy WHERE circ_lib IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+
+DELETE FROM asset.copy WHERE circ_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+COMMIT;
+
+DROP INDEX vandelay.tmp_import_as;
+ALTER TABLE asset.copy ENABLE RULE protect_copy_delete;
+ALTER TABLE asset.copy ENABLE TRIGGER audit_asset_copy_update_trigger;

diff --git a/remove_ou_data/07_remove_volumes.sql b/remove_ou_data/07_remove_volumes.sql
new file mode 100644 (file)
index 0000000..03f8754
--- /dev/null
+++ b/remove_ou_data/07_remove_volumes.sql
@@ -0,0 +1,46 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+
+ALTER TABLE asset.call_number DISABLE RULE protect_cn_delete;
+ALTER TABLE asset.call_number DISABLE TRIGGER audit_asset_call_number_update_trigger;
+
+BEGIN;
+
+DELETE FROM asset.uri_call_number_map WHERE call_number IN (
+    SELECT id FROM asset.call_number WHERE owning_lib IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+
+CREATE TABLE esi.albemarle_volume_bibs AS SELECT DISTINCT record
+FROM asset.call_number WHERE owning_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM asset.call_number WHERE owning_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+
+COMMIT;
+
+ALTER TABLE asset.call_number ENABLE RULE protect_cn_delete;
+ALTER TABLE asset.call_number ENABLE TRIGGER audit_asset_call_number_update_trigger;
+
+CREATE INDEX alb_vol_bib_idx ON esi.albemarle_volume_bibs(record);

diff --git a/remove_ou_data/08_remove_bibs_stage_1.sql b/remove_ou_data/08_remove_bibs_stage_1.sql
new file mode 100644 (file)
index 0000000..a81c9ae
--- /dev/null
+++ b/remove_ou_data/08_remove_bibs_stage_1.sql
@@ -0,0 +1,34 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+ALTER TABLE biblio.record_entry DISABLE TRIGGER audit_biblio_record_entry_update_trigger;
+
+BEGIN;
+
+DELETE FROM biblio.record_entry WHERE id IN (
+    SELECT record FROM esi.albemarle_volume_bibs x 
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+COMMIT;
+
+ALTER TABLE biblio.record_entry ENABLE TRIGGER audit_biblio_record_entry_update_trigger;
+

diff --git a/remove_ou_data/08_remove_bibs_stage_2.sql b/remove_ou_data/08_remove_bibs_stage_2.sql
new file mode 100644 (file)
index 0000000..93d1746
--- /dev/null
+++ b/remove_ou_data/08_remove_bibs_stage_2.sql
@@ -0,0 +1,94 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+BEGIN;
+
+DELETE FROM authority.bib_linking WHERE bib IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM metabib.browse_entry_def_map WHERE source IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM acq.lineitem WHERE eg_bib_id IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM biblio.monograph_part WHERE record IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM biblio.peer_bib_copy_map WHERE peer_record IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM acq.lineitem WHERE queued_record IN (
+    SELECT id FROM vandelay.queued_bib_record WHERE imported_as IN
+    (
+        SELECT record FROM esi.albemarle_volume_bibs x
+        WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+    )
+);
+
+DELETE FROM acq.acq_lineitem_history WHERE queued_record IN (
+    SELECT id FROM vandelay.queued_bib_record WHERE imported_as IN
+    (
+        SELECT record FROM esi.albemarle_volume_bibs x
+        WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+    )
+);
+
+DELETE FROM vandelay.queued_bib_record WHERE imported_as IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM metabib.record_attr_vector_list WHERE source IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM metabib.record_sorter WHERE source IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+DELETE FROM booking.resource_type WHERE record IN 
+(
+    SELECT record FROM esi.albemarle_volume_bibs x
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+COMMIT;

diff --git a/remove_ou_data/08_remove_bibs_stage_3.sql b/remove_ou_data/08_remove_bibs_stage_3.sql
new file mode 100644 (file)
index 0000000..205d88a
--- /dev/null
+++ b/remove_ou_data/08_remove_bibs_stage_3.sql
@@ -0,0 +1,39 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+ALTER TABLE biblio.record_entry DISABLE TRIGGER audit_biblio_record_entry_update_trigger;
+ALTER TABLE biblio.record_entry DISABLE TRIGGER bbb_simple_rec_trigger;
+ALTER TABLE biblio.record_entry DISABLE RULE protect_bib_rec_delete;
+CREATE INDEX tmp_bib_idx1 ON acq.lineitem (eg_bib_id);
+
+BEGIN;
+
+DELETE FROM biblio.record_entry WHERE id IN (
+    SELECT record FROM esi.albemarle_volume_bibs x 
+    WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
+);
+
+COMMIT;
+
+ALTER TABLE biblio.record_entry ENABLE RULE protect_bib_rec_delete;
+ALTER TABLE biblio.record_entry ENABLE TRIGGER bbb_simple_rec_trigger;
+ALTER TABLE biblio.record_entry ENABLE TRIGGER audit_biblio_record_entry_update_trigger;
+DROP INDEX acq.tmp_bib_idx1;

diff --git a/remove_ou_data/09_clear_vandelay_reports.sql b/remove_ou_data/09_clear_vandelay_reports.sql
new file mode 100644 (file)
index 0000000..5fdcb8e
--- /dev/null
+++ b/remove_ou_data/09_clear_vandelay_reports.sql
@@ -0,0 +1,44 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+BEGIN;
+
+DELETE FROM vandelay.queue WHERE owner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM reporter.report_folder WHERE owner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM reporter.report WHERE owner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM reporter.output_folder WHERE owner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM reporter.schedule WHERE runner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+DELETE FROM reporter.template WHERE owner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+DELETE FROM reporter.template_folder WHERE owner IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+COMMIT;

diff --git a/remove_ou_data/10_remove_workstations.sql b/remove_ou_data/10_remove_workstations.sql
new file mode 100644 (file)
index 0000000..8a77460
--- /dev/null
+++ b/remove_ou_data/10_remove_workstations.sql
@@ -0,0 +1,43 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+CREATE INDEX tmp_ws_indx1 ON action.circulation (workstation);
+CREATE INDEX tmp_ws_indx2 ON action.circulation (checkin_workstation);
+CREATE INDEX tmp_ws_indx3 ON money.bnm_desk_payment(cash_drawer);
+ALTER TABLE action.circulation DISABLE TRIGGER action_circulation_target_copy_trig;
+
+BEGIN;
+
+UPDATE action.circulation SET checkin_lib = NULL, checkin_workstation = NULL
+WHERE checkin_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from
+ actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM actor.workstation WHERE owning_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from
+ actor.org_unit where shortname = :ou_to_del);
+
+COMMIT;
+
+DROP INDEX action.tmp_ws_indx1;
+DROP INDEX action.tmp_ws_indx2;
+DROP INDEX money.tmp_ws_indx3;
+ALTER TABLE action.circulation ENABLE TRIGGER action_circulation_target_copy_trig;

diff --git a/remove_ou_data/11_update_editors.sql b/remove_ou_data/11_update_editors.sql
new file mode 100644 (file)
index 0000000..a2e8241
--- /dev/null
+++ b/remove_ou_data/11_update_editors.sql
@@ -0,0 +1,40 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+BEGIN;
+
+UPDATE asset.copy SET editor = 1 WHERE editor IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+UPDATE asset.call_number SET editor = 1 WHERE editor IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+UPDATE asset.copy SET creator = 1 WHERE creator IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+UPDATE asset.call_number SET creator = 1 WHERE creator IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+UPDATE biblio.record_entry SET creator = 1 WHERE creator IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+UPDATE biblio.record_entry SET editor = 1 WHERE editor IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+COMMIT;
+

diff --git a/remove_ou_data/12_remove_more_patron_extras.sql b/remove_ou_data/12_remove_more_patron_extras.sql
new file mode 100644 (file)
index 0000000..a9a2dc2
--- /dev/null
+++ b/remove_ou_data/12_remove_more_patron_extras.sql
@@ -0,0 +1,29 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+BEGIN;
+
+DELETE FROM action.non_cataloged_circulation WHERE patron IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+DELETE FROM action.non_cataloged_circulation WHERE staff IN
+(SELECT id FROM actor.usr WHERE home_ou IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+
+COMMIT;

diff --git a/remove_ou_data/13_remove_transits.sql b/remove_ou_data/13_remove_transits.sql
new file mode 100644 (file)
index 0000000..0c5ca50
--- /dev/null
+++ b/remove_ou_data/13_remove_transits.sql
@@ -0,0 +1,46 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+CREATE INDEX tmp_hop_idx ON action.transit_copy (prev_hop);
+BEGIN;
+
+DELETE FROM action.hold_transit_copy WHERE source IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+AND dest IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM action.transit_copy WHERE source IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+AND dest IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM action.hold_transit_copy WHERE source IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+OR dest IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM action.transit_copy WHERE source IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+OR dest IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+COMMIT;
+DROP  INDEX action.tmp_hop_idx;

diff --git a/remove_ou_data/14_remove_ou_extras.sql b/remove_ou_data/14_remove_ou_extras.sql
new file mode 100644 (file)
index 0000000..781090a
--- /dev/null
+++ b/remove_ou_data/14_remove_ou_extras.sql
@@ -0,0 +1,71 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+create index tmp_idx on money.billing(btype);
+ALTER TABLE action.circulation DISABLE TRIGGER action_circulation_target_copy_trig;
+
+BEGIN;
+
+DELETE FROM actor.org_address WHERE org_unit IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+UPDATE actor.org_unit SET ill_address = NULL, holds_address = NULL, mailing_address = NULL, billing_address = NULL
+WHERE id IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+UPDATE action.circulation SET copy_location = 1
+WHERE copy_location IN (SELECT id FROM asset.copy_location WHERE owning_lib IN 
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del));
+DELETE FROM asset.copy_location WHERE owning_lib IN
+(SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+DELETE FROM money.billing
+WHERE btype IN (
+    SELECT id FROM config.billing_type
+    WHERE owner IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+
+DELETE FROM action_trigger.event
+WHERE event_def IN (
+    SELECT id FROM action_trigger.event_definition
+    WHERE owner IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+DELETE FROM action_trigger.environment
+WHERE event_def IN (
+    SELECT id FROM action_trigger.event_definition
+    WHERE owner IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+DELETE FROM action_trigger.event_params
+WHERE event_def IN (
+    SELECT id FROM action_trigger.event_definition
+    WHERE owner IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)
+);
+DELETE FROM action_trigger.event_definition
+    WHERE owner IN
+    (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del);
+
+COMMIT;
+DROP INDEX money.tmp_idx;
+ALTER TABLE action.circulation ENABLE TRIGGER action_circulation_target_copy_trig;

diff --git a/remove_ou_data/15_clear_auditors.sql b/remove_ou_data/15_clear_auditors.sql
new file mode 100644 (file)
index 0000000..ab941c3
--- /dev/null
+++ b/remove_ou_data/15_clear_auditors.sql
@@ -0,0 +1,41 @@
+-- Copyright 2015, Equinox Software, Inc.
+-- Author: Galen Charlton <gmc@esilibrary.com>
+--
+-- This program is free software; you can redistribute it and/or
+-- modify it under the terms of the GNU General Public License
+-- as published by the Free Software Foundation; either version 2
+-- of the License, or (at your option) any later version.
+--
+-- This program is distributed in the hope that it will be useful,
+-- but WITHOUT ANY WARRANTY; without even the implied warranty of
+-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+-- GNU General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License
+-- along with this program; if not, write to the Free Software
+-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+
+\set ou_to_del ''''EXAMPLE''''
+\set ECHO all
+\timing
+
+BEGIN;
+
+-- FIXME auditor.actor_usr_address_history;
+DELETE FROM auditor.actor_usr_history WHERE home_ou IN (
+    SELECT (actor.org_unit_descendants(id)).id
+    FROM actor.org_unit
+    WHERE shortname = :ou_to_del
+);
+DELETE FROM auditor.asset_copy_history WHERE circ_lib IN (
+    SELECT (actor.org_unit_descendants(id)).id
+    FROM actor.org_unit
+    WHERE shortname = :ou_to_del
+);
+DELETE FROM auditor.asset_call_number_history WHERE owning_lib IN (
+    SELECT (actor.org_unit_descendants(id)).id
+    FROM actor.org_unit
+    WHERE shortname = :ou_to_del
+);
+
+COMMIT;

diff --git a/remove_ou_data/README b/remove_ou_data/README
new file mode 100644 (file)
index 0000000..c740409
--- /dev/null
+++ b/remove_ou_data/README
@@ -0,0 +1,20 @@
+This is a set of SQL scripts to be run, in order, to completely
+remove data belonging to an OU and its descendants in an Evergreen
+database.  Each script uses a variable called ou_to_del that should
+be set to the shortname of the OU to remove.
+
+Generate script to delete users:
+
+SELECT 'DELETE FROM actor.usr WHERE id = ' || id || ';'
+FROM actor.usr WHERE home_ou IN (
+    SELECT (actor.org_unit_descendants(id)).id FROM actor.org_unit
+    WHERE shortname = 'EXAMPLE'
+);
+
+After running through the scripts, it should be possible to
+remove the actor.org_unit entries themselves.
+
+TODO:
+
+-- remove event output that is no longer associated with any events
+-- remvoe user address auditor entries