From c919228a7234c1fdfa9f9cfbae245fc30731eb85 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 14 Oct 2016 11:17:15 -0400 Subject: [PATCH] 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 --- remove_ou_data/01_easy_deletes.sql | 84 +++++++++++++++++ remove_ou_data/02_remove_patron_extras.sql | 66 +++++++++++++ remove_ou_data/03_remove_holds.sql | 46 +++++++++ remove_ou_data/04_remove_circ.sql | 46 +++++++++ remove_ou_data/05_remove_billings.sql | 112 +++++++++++++++++++++++ remove_ou_data/06_remove_copies.sql | 62 +++++++++++++ remove_ou_data/07_remove_volumes.sql | 46 +++++++++ remove_ou_data/08_remove_bibs_stage_1.sql | 34 +++++++ remove_ou_data/08_remove_bibs_stage_2.sql | 94 +++++++++++++++++++ remove_ou_data/08_remove_bibs_stage_3.sql | 39 ++++++++ remove_ou_data/09_clear_vandelay_reports.sql | 44 +++++++++ remove_ou_data/10_remove_workstations.sql | 43 +++++++++ remove_ou_data/11_update_editors.sql | 40 ++++++++ remove_ou_data/12_remove_more_patron_extras.sql | 29 ++++++ remove_ou_data/13_remove_transits.sql | 46 +++++++++ remove_ou_data/14_remove_ou_extras.sql | 71 ++++++++++++++ remove_ou_data/15_clear_auditors.sql | 41 ++++++++ remove_ou_data/README | 20 ++++ 18 files changed, 963 insertions(+), 0 deletions(-) create mode 100644 remove_ou_data/01_easy_deletes.sql create mode 100644 remove_ou_data/02_remove_patron_extras.sql create mode 100644 remove_ou_data/03_remove_holds.sql create mode 100644 remove_ou_data/04_remove_circ.sql create mode 100644 remove_ou_data/05_remove_billings.sql create mode 100644 remove_ou_data/06_remove_copies.sql create mode 100644 remove_ou_data/07_remove_volumes.sql create mode 100644 remove_ou_data/08_remove_bibs_stage_1.sql create mode 100644 remove_ou_data/08_remove_bibs_stage_2.sql create mode 100644 remove_ou_data/08_remove_bibs_stage_3.sql create mode 100644 remove_ou_data/09_clear_vandelay_reports.sql create mode 100644 remove_ou_data/10_remove_workstations.sql create mode 100644 remove_ou_data/11_update_editors.sql create mode 100644 remove_ou_data/12_remove_more_patron_extras.sql create mode 100644 remove_ou_data/13_remove_transits.sql create mode 100644 remove_ou_data/14_remove_ou_extras.sql create mode 100644 remove_ou_data/15_clear_auditors.sql create mode 100644 remove_ou_data/README diff --git a/remove_ou_data/01_easy_deletes.sql b/remove_ou_data/01_easy_deletes.sql new file mode 100644 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 +-- +-- 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 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 -- 1.7.2.5