From 234d6c986f42cb7c245205bb299fa2c9d204e52a Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Fri, 9 Feb 2018 09:44:19 -0500 Subject: [PATCH] start of serial script in remove ou --- remove_ou_data/07_remove_serials.sql | 65 +++++++++++++ remove_ou_data/07_remove_volumes.sql | 53 ----------- remove_ou_data/08_remove_bibs_stage_1.sql | 35 ------- remove_ou_data/08_remove_bibs_stage_2.sql | 95 ------------------- remove_ou_data/08_remove_bibs_stage_3.sql | 40 -------- remove_ou_data/08_remove_volumes.sql | 53 +++++++++++ remove_ou_data/09_clear_vandelay_reports.sql | 58 ------------ remove_ou_data/09_remove_bibs_stage_1.sql | 35 +++++++ remove_ou_data/09_remove_bibs_stage_2.sql | 95 +++++++++++++++++++ remove_ou_data/09_remove_bibs_stage_3.sql | 40 ++++++++ remove_ou_data/10_clear_vandelay_reports.sql | 58 ++++++++++++ remove_ou_data/10_remove_workstations.sql | 43 --------- remove_ou_data/11_remove_workstations.sql | 43 +++++++++ remove_ou_data/11_update_editors.sql | 40 -------- remove_ou_data/12_remove_more_patron_extras.sql | 31 ------ remove_ou_data/12_update_editors.sql | 40 ++++++++ remove_ou_data/13_remove_more_patron_extras.sql | 31 ++++++ remove_ou_data/13_remove_transits.sql | 46 --------- remove_ou_data/14_remove_ou_extras.sql | 73 --------------- remove_ou_data/14_remove_transits.sql | 46 +++++++++ remove_ou_data/15_clear_auditors.sql | 45 --------- remove_ou_data/15_remove_ou_extras.sql | 73 +++++++++++++++ remove_ou_data/16_clear_auditors.sql | 45 +++++++++ remove_ou_data/16_remove_acq.sql | 30 ------ remove_ou_data/17_delete_usrs_stage_1.sql | 42 --------- remove_ou_data/17_delete_usrs_stage_3.sql | 29 ------ remove_ou_data/17_remove_acq.sql | 30 ++++++ remove_ou_data/18_delete_usrs_stage_1.sql | 42 +++++++++ remove_ou_data/18_delete_usrs_stage_3.sql | 29 ++++++ remove_ou_data/18_vauum_analyze.sql | 113 ----------------------- remove_ou_data/19_vacuum_analyze.sql | 113 +++++++++++++++++++++++ 31 files changed, 838 insertions(+), 773 deletions(-) create mode 100644 remove_ou_data/07_remove_serials.sql delete mode 100644 remove_ou_data/07_remove_volumes.sql delete mode 100644 remove_ou_data/08_remove_bibs_stage_1.sql delete mode 100644 remove_ou_data/08_remove_bibs_stage_2.sql delete mode 100644 remove_ou_data/08_remove_bibs_stage_3.sql create mode 100644 remove_ou_data/08_remove_volumes.sql delete mode 100644 remove_ou_data/09_clear_vandelay_reports.sql create mode 100644 remove_ou_data/09_remove_bibs_stage_1.sql create mode 100644 remove_ou_data/09_remove_bibs_stage_2.sql create mode 100644 remove_ou_data/09_remove_bibs_stage_3.sql create mode 100644 remove_ou_data/10_clear_vandelay_reports.sql delete mode 100644 remove_ou_data/10_remove_workstations.sql create mode 100644 remove_ou_data/11_remove_workstations.sql delete mode 100644 remove_ou_data/11_update_editors.sql delete mode 100644 remove_ou_data/12_remove_more_patron_extras.sql create mode 100644 remove_ou_data/12_update_editors.sql create mode 100644 remove_ou_data/13_remove_more_patron_extras.sql delete mode 100644 remove_ou_data/13_remove_transits.sql delete mode 100644 remove_ou_data/14_remove_ou_extras.sql create mode 100644 remove_ou_data/14_remove_transits.sql delete mode 100644 remove_ou_data/15_clear_auditors.sql create mode 100644 remove_ou_data/15_remove_ou_extras.sql create mode 100644 remove_ou_data/16_clear_auditors.sql delete mode 100644 remove_ou_data/16_remove_acq.sql delete mode 100644 remove_ou_data/17_delete_usrs_stage_1.sql delete mode 100644 remove_ou_data/17_delete_usrs_stage_3.sql create mode 100644 remove_ou_data/17_remove_acq.sql create mode 100644 remove_ou_data/18_delete_usrs_stage_1.sql create mode 100644 remove_ou_data/18_delete_usrs_stage_3.sql delete mode 100644 remove_ou_data/18_vauum_analyze.sql create mode 100644 remove_ou_data/19_vacuum_analyze.sql diff --git a/remove_ou_data/07_remove_serials.sql b/remove_ou_data/07_remove_serials.sql new file mode 100644 index 0000000..5dd041a --- /dev/null +++ b/remove_ou_data/07_remove_serials.sql @@ -0,0 +1,65 @@ +-- 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 serial.record_entry DISABLE RULE protect_mfhd_delete; + +BEGIN; + +DELETE FROM serial.basic_summary WHERE distribution IN +(SELECT id 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.distribution_note WHERE distribution IN +(SELECT id 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.index_summary WHERE distribution IN +(SELECT id 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.item WHERE stream IN +(SELECT id FROM serial.stream WHERE distribution IN +(SELECT id 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.item_note WHERE id IN +(SELECT id FROM serial.item WHERE stream IN (SELECT id FROM serial.stream WHERE distribution IN +(SELECT id 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.stream WHERE distribution IN +(SELECT id 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.supplement_summary WHERE distribution IN +(SELECT id 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.distribution WHERE holding_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del); + +UPDATE serial.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)); + +UPDATE serial.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 serial.record_entry SET owning_lib = 1 +WHERE owning_lib IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del) +AND id IN (SELECT record_entry FROM serial.distribution WHERE holding_lib NOT 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); + +COMMIT; + +ALTER TABLE serial.record_entry ENABLE RULE protect_mfhd_delete; diff --git a/remove_ou_data/07_remove_volumes.sql b/remove_ou_data/07_remove_volumes.sql deleted file mode 100644 index 71fc03a..0000000 --- a/remove_ou_data/07_remove_volumes.sql +++ /dev/null @@ -1,53 +0,0 @@ --- 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 vol_del_table ORGUNIT_volume_bibs -\set ECHO all -\timing - -DROP TABLE IF EXISTS esi.:vol_del_table; - -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.:vol_del_table 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); - -DELETE FROM asset.call_number_prefix 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_suffix 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 org_vol_bib_idx ON esi.:vol_del_table(record); diff --git a/remove_ou_data/08_remove_bibs_stage_1.sql b/remove_ou_data/08_remove_bibs_stage_1.sql deleted file mode 100644 index e38c059..0000000 --- a/remove_ou_data/08_remove_bibs_stage_1.sql +++ /dev/null @@ -1,35 +0,0 @@ --- 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 vol_del_table ORGUNIT_volume_bibs -\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.:vol_del_table 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 deleted file mode 100644 index 86c6e48..0000000 --- a/remove_ou_data/08_remove_bibs_stage_2.sql +++ /dev/null @@ -1,95 +0,0 @@ --- 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 vol_del_table ORGUNIT_volume_bibs -\set ECHO all -\timing - -BEGIN; - -DELETE FROM authority.bib_linking WHERE bib IN -( - SELECT record FROM esi.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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 deleted file mode 100644 index e8e08f9..0000000 --- a/remove_ou_data/08_remove_bibs_stage_3.sql +++ /dev/null @@ -1,40 +0,0 @@ --- 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 vol_del_table ORGUNIT_volume_bibs -\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.:vol_del_table 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/08_remove_volumes.sql b/remove_ou_data/08_remove_volumes.sql new file mode 100644 index 0000000..71fc03a --- /dev/null +++ b/remove_ou_data/08_remove_volumes.sql @@ -0,0 +1,53 @@ +-- 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 vol_del_table ORGUNIT_volume_bibs +\set ECHO all +\timing + +DROP TABLE IF EXISTS esi.:vol_del_table; + +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.:vol_del_table 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); + +DELETE FROM asset.call_number_prefix 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_suffix 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 org_vol_bib_idx ON esi.:vol_del_table(record); diff --git a/remove_ou_data/09_clear_vandelay_reports.sql b/remove_ou_data/09_clear_vandelay_reports.sql deleted file mode 100644 index b68bef9..0000000 --- a/remove_ou_data/09_clear_vandelay_reports.sql +++ /dev/null @@ -1,58 +0,0 @@ --- 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 - -INSERT INTO reporter.template_folder (owner,name) values (1,'saved_cons_templates'); -INSERT INTO reporter.output_folder (owner,name) values (1,'saved_cons_output'); - -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)); - -UPDATE reporter.template a SET owner = 1, folder = (SELECT id FROM reporter.template_folder WHERE name ~* 'saved_cons_templates' and owner = 1) -FROM (SELECT id, template FROM reporter.report WHERE owner = 1) x -WHERE x.template = a.id AND a.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)); - -UPDATE reporter.schedule SET folder = (SELECT id FROM reporter.output_folder WHERE owner = 1 AND name = 'saved_cons_output') WHERE folder IN -(SELECT id FROM reporter.output_folder WHERE share_with IN - (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)); -DELETE FROM reporter.output_folder WHERE share_with 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/09_remove_bibs_stage_1.sql b/remove_ou_data/09_remove_bibs_stage_1.sql new file mode 100644 index 0000000..e38c059 --- /dev/null +++ b/remove_ou_data/09_remove_bibs_stage_1.sql @@ -0,0 +1,35 @@ +-- 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 vol_del_table ORGUNIT_volume_bibs +\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.:vol_del_table 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/09_remove_bibs_stage_2.sql b/remove_ou_data/09_remove_bibs_stage_2.sql new file mode 100644 index 0000000..86c6e48 --- /dev/null +++ b/remove_ou_data/09_remove_bibs_stage_2.sql @@ -0,0 +1,95 @@ +-- 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 vol_del_table ORGUNIT_volume_bibs +\set ECHO all +\timing + +BEGIN; + +DELETE FROM authority.bib_linking WHERE bib IN +( + SELECT record FROM esi.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table 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.:vol_del_table x + WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record) +); + +COMMIT; diff --git a/remove_ou_data/09_remove_bibs_stage_3.sql b/remove_ou_data/09_remove_bibs_stage_3.sql new file mode 100644 index 0000000..e8e08f9 --- /dev/null +++ b/remove_ou_data/09_remove_bibs_stage_3.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 vol_del_table ORGUNIT_volume_bibs +\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.:vol_del_table 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/10_clear_vandelay_reports.sql b/remove_ou_data/10_clear_vandelay_reports.sql new file mode 100644 index 0000000..b68bef9 --- /dev/null +++ b/remove_ou_data/10_clear_vandelay_reports.sql @@ -0,0 +1,58 @@ +-- 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 + +INSERT INTO reporter.template_folder (owner,name) values (1,'saved_cons_templates'); +INSERT INTO reporter.output_folder (owner,name) values (1,'saved_cons_output'); + +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)); + +UPDATE reporter.template a SET owner = 1, folder = (SELECT id FROM reporter.template_folder WHERE name ~* 'saved_cons_templates' and owner = 1) +FROM (SELECT id, template FROM reporter.report WHERE owner = 1) x +WHERE x.template = a.id AND a.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)); + +UPDATE reporter.schedule SET folder = (SELECT id FROM reporter.output_folder WHERE owner = 1 AND name = 'saved_cons_output') WHERE folder IN +(SELECT id FROM reporter.output_folder WHERE share_with IN + (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del)); +DELETE FROM reporter.output_folder WHERE share_with 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 deleted file mode 100644 index 8a77460..0000000 --- a/remove_ou_data/10_remove_workstations.sql +++ /dev/null @@ -1,43 +0,0 @@ --- 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_remove_workstations.sql b/remove_ou_data/11_remove_workstations.sql new file mode 100644 index 0000000..8a77460 --- /dev/null +++ b/remove_ou_data/11_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 deleted file mode 100644 index a2e8241..0000000 --- a/remove_ou_data/11_update_editors.sql +++ /dev/null @@ -1,40 +0,0 @@ --- 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 deleted file mode 100644 index b3a64de..0000000 --- a/remove_ou_data/12_remove_more_patron_extras.sql +++ /dev/null @@ -1,31 +0,0 @@ --- 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)); -DELETE FROM action.usr_circ_history 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; diff --git a/remove_ou_data/12_update_editors.sql b/remove_ou_data/12_update_editors.sql new file mode 100644 index 0000000..a2e8241 --- /dev/null +++ b/remove_ou_data/12_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/13_remove_more_patron_extras.sql b/remove_ou_data/13_remove_more_patron_extras.sql new file mode 100644 index 0000000..b3a64de --- /dev/null +++ b/remove_ou_data/13_remove_more_patron_extras.sql @@ -0,0 +1,31 @@ +-- 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)); +DELETE FROM action.usr_circ_history 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; diff --git a/remove_ou_data/13_remove_transits.sql b/remove_ou_data/13_remove_transits.sql deleted file mode 100644 index 0c5ca50..0000000 --- a/remove_ou_data/13_remove_transits.sql +++ /dev/null @@ -1,46 +0,0 @@ --- 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 deleted file mode 100644 index 08bab80..0000000 --- a/remove_ou_data/14_remove_ou_extras.sql +++ /dev/null @@ -1,73 +0,0 @@ --- 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; -ALTER TABLE asset.copy_location DISABLE RULE protect_copy_location_delete; - -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 asset.copy_location ENABLE RULE protect_copy_location_delete; -ALTER TABLE action.circulation ENABLE TRIGGER action_circulation_target_copy_trig; diff --git a/remove_ou_data/14_remove_transits.sql b/remove_ou_data/14_remove_transits.sql new file mode 100644 index 0000000..0c5ca50 --- /dev/null +++ b/remove_ou_data/14_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/15_clear_auditors.sql b/remove_ou_data/15_clear_auditors.sql deleted file mode 100644 index 685d1cb..0000000 --- a/remove_ou_data/15_clear_auditors.sql +++ /dev/null @@ -1,45 +0,0 @@ --- 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 -); -DELETE FROM auditor.actor_usr_address_history WHERE usr IN ( - SELECT id - FROM actor.usr - WHERE home_ou = (SELECT id FROM actor.org_unit WHERE shortname = :ou_to_del) -); -COMMIT; diff --git a/remove_ou_data/15_remove_ou_extras.sql b/remove_ou_data/15_remove_ou_extras.sql new file mode 100644 index 0000000..08bab80 --- /dev/null +++ b/remove_ou_data/15_remove_ou_extras.sql @@ -0,0 +1,73 @@ +-- 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; +ALTER TABLE asset.copy_location DISABLE RULE protect_copy_location_delete; + +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 asset.copy_location ENABLE RULE protect_copy_location_delete; +ALTER TABLE action.circulation ENABLE TRIGGER action_circulation_target_copy_trig; diff --git a/remove_ou_data/16_clear_auditors.sql b/remove_ou_data/16_clear_auditors.sql new file mode 100644 index 0000000..685d1cb --- /dev/null +++ b/remove_ou_data/16_clear_auditors.sql @@ -0,0 +1,45 @@ +-- 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 +); +DELETE FROM auditor.actor_usr_address_history WHERE usr IN ( + SELECT id + FROM actor.usr + WHERE home_ou = (SELECT id FROM actor.org_unit WHERE shortname = :ou_to_del) +); +COMMIT; diff --git a/remove_ou_data/16_remove_acq.sql b/remove_ou_data/16_remove_acq.sql deleted file mode 100644 index d88d1b7..0000000 --- a/remove_ou_data/16_remove_acq.sql +++ /dev/null @@ -1,30 +0,0 @@ --- 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.lineitem WHERE picklist IN (SELECT id FROM acq.picklist WHERE org_unit IN - (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del) -); - -DELETE FROM acq.picklist WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del); - -COMMIT; diff --git a/remove_ou_data/17_delete_usrs_stage_1.sql b/remove_ou_data/17_delete_usrs_stage_1.sql deleted file mode 100644 index 25e36ff..0000000 --- a/remove_ou_data/17_delete_usrs_stage_1.sql +++ /dev/null @@ -1,42 +0,0 @@ --- 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 - --- creates an output file for serially deleting users, --- makes it easier than finding each problem stopping a batch --- creates a stage 2 inbetween 1 and 3 - -\t -\psql format unaligned -\o 17_delete_usrs_stage_2.sql - -SELECT 'ALTER TABLE actor.usr DISABLE RULE protect_user_delete;'; -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 = :ou_to_del -); -SELECT 'ALTER TABLE actor.usr ENABLE RULE protect_user_delete;'; - -\o -\pset format aligned -\t - - diff --git a/remove_ou_data/17_delete_usrs_stage_3.sql b/remove_ou_data/17_delete_usrs_stage_3.sql deleted file mode 100644 index 09cc9d2..0000000 --- a/remove_ou_data/17_delete_usrs_stage_3.sql +++ /dev/null @@ -1,29 +0,0 @@ --- 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 - -SELECT id -FROM actor.usr WHERE home_ou IN ( - SELECT (actor.org_unit_descendants(id)).id FROM actor.org_unit - WHERE shortname IN (SELECT shortname FROM esi.ous_to_del) -); - - --- find the problems and clear them up diff --git a/remove_ou_data/17_remove_acq.sql b/remove_ou_data/17_remove_acq.sql new file mode 100644 index 0000000..d88d1b7 --- /dev/null +++ b/remove_ou_data/17_remove_acq.sql @@ -0,0 +1,30 @@ +-- 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.lineitem WHERE picklist IN (SELECT id FROM acq.picklist WHERE org_unit IN + (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del) +); + +DELETE FROM acq.picklist WHERE org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit where shortname = :ou_to_del); + +COMMIT; diff --git a/remove_ou_data/18_delete_usrs_stage_1.sql b/remove_ou_data/18_delete_usrs_stage_1.sql new file mode 100644 index 0000000..95fdd58 --- /dev/null +++ b/remove_ou_data/18_delete_usrs_stage_1.sql @@ -0,0 +1,42 @@ +-- 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 + +-- creates an output file for serially deleting users, +-- makes it easier than finding each problem stopping a batch +-- creates a stage 2 inbetween 1 and 3 + +\t +\psql format unaligned +\o 18_delete_usrs_stage_2.sql + +SELECT 'ALTER TABLE actor.usr DISABLE RULE protect_user_delete;'; +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 = :ou_to_del +); +SELECT 'ALTER TABLE actor.usr ENABLE RULE protect_user_delete;'; + +\o +\pset format aligned +\t + + diff --git a/remove_ou_data/18_delete_usrs_stage_3.sql b/remove_ou_data/18_delete_usrs_stage_3.sql new file mode 100644 index 0000000..09cc9d2 --- /dev/null +++ b/remove_ou_data/18_delete_usrs_stage_3.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 + +SELECT id +FROM actor.usr WHERE home_ou IN ( + SELECT (actor.org_unit_descendants(id)).id FROM actor.org_unit + WHERE shortname IN (SELECT shortname FROM esi.ous_to_del) +); + + +-- find the problems and clear them up diff --git a/remove_ou_data/18_vauum_analyze.sql b/remove_ou_data/18_vauum_analyze.sql deleted file mode 100644 index 1d60fb1..0000000 --- a/remove_ou_data/18_vauum_analyze.sql +++ /dev/null @@ -1,113 +0,0 @@ -VACUUM ANALYZE VERBOSE acq.acq_lineitem_history ; -VACUUM ANALYZE VERBOSE acq.distribution_formula_entry ; -VACUUM ANALYZE VERBOSE acq.fund_allocation_percent ; -VACUUM ANALYZE VERBOSE acq.lineitem ; -VACUUM ANALYZE VERBOSE action.aged_circulation ; -VACUUM ANALYZE VERBOSE action.aged_hold_request ; -VACUUM ANALYZE VERBOSE action.circulation ; -VACUUM ANALYZE VERBOSE action.fieldset ; -VACUUM ANALYZE VERBOSE action.hold_request ; -VACUUM ANALYZE VERBOSE action.hold_transit_copy ; -VACUUM ANALYZE VERBOSE action.in_house_use ; -VACUUM ANALYZE VERBOSE action.non_cataloged_circulation ; -VACUUM ANALYZE VERBOSE action.non_cat_in_house_use ; -VACUUM ANALYZE VERBOSE action.transit_copy ; -VACUUM ANALYZE VERBOSE action_trigger.environment; -VACUUM ANALYZE VERBOSE action_trigger.event; -VACUUM ANALYZE VERBOSE action_trigger.event_definition; -VACUUM ANALYZE VERBOSE action_trigger.event_params; -VACUUM ANALYZE VERBOSE actor.address_alert ; -VACUUM ANALYZE VERBOSE actor.card ; -VACUUM ANALYZE VERBOSE actor.hours_of_operation ; -VACUUM ANALYZE VERBOSE actor.org_address ; -VACUUM ANALYZE VERBOSE actor.org_lasso_map ; -VACUUM ANALYZE VERBOSE actor.org_unit ; -VACUUM ANALYZE VERBOSE actor.org_unit_closed ; -VACUUM ANALYZE VERBOSE actor.org_unit_custom_tree_node ; -VACUUM ANALYZE VERBOSE actor.org_unit_proximity_adjustment ; -VACUUM ANALYZE VERBOSE actor.org_unit_setting ; -VACUUM ANALYZE VERBOSE actor.search_filter_group ; -VACUUM ANALYZE VERBOSE actor.stat_cat ; -VACUUM ANALYZE VERBOSE actor.stat_cat_entry ; -VACUUM ANALYZE VERBOSE actor.stat_cat_entry_default ; -VACUUM ANALYZE VERBOSE actor.stat_cat_entry_usr_map ; -VACUUM ANALYZE VERBOSE actor.toolbar ; -VACUUM ANALYZE VERBOSE actor.usr ; -VACUUM ANALYZE VERBOSE actor.usr_activity ; -VACUUM ANALYZE VERBOSE actor.usr_address ; -VACUUM ANALYZE VERBOSE actor.usr_message ; -VACUUM ANALYZE VERBOSE actor.usr_note ; -VACUUM ANALYZE VERBOSE actor.usr_org_unit_opt_in ; -VACUUM ANALYZE VERBOSE actor.usr_password_reset ; -VACUUM ANALYZE VERBOSE actor.usr_saved_search ; -VACUUM ANALYZE VERBOSE actor.usr_setting ; -VACUUM ANALYZE VERBOSE actor.usr_standing_penalty ; -VACUUM ANALYZE VERBOSE actor.workstation ; -VACUUM ANALYZE VERBOSE asset.call_number ; -VACUUM ANALYZE VERBOSE asset.copy ; -VACUUM ANALYZE VERBOSE asset.copy_location ; -VACUUM ANALYZE VERBOSE asset.copy_note ; -VACUUM ANALYZE VERBOSE asset.copy_part_map ; -VACUUM ANALYZE VERBOSE asset.copy_template ; -VACUUM ANALYZE VERBOSE asset.opac_visible_copies ; -VACUUM ANALYZE VERBOSE asset.stat_cat_entry_copy_map ; -VACUUM ANALYZE VERBOSE asset.uri_call_number_map ; -VACUUM ANALYZE VERBOSE auditor.actor_usr_address_history ; -VACUUM ANALYZE VERBOSE auditor.actor_usr_history ; -VACUUM ANALYZE VERBOSE auditor.asset_call_number_history ; -VACUUM ANALYZE VERBOSE auditor.asset_copy_history ; -VACUUM ANALYZE VERBOSE authority.bib_linking ; -VACUUM ANALYZE VERBOSE biblio.monograph_part ; -VACUUM ANALYZE VERBOSE biblio.peer_bib_copy_map ; -VACUUM ANALYZE VERBOSE biblio.record_entry ; -VACUUM ANALYZE VERBOSE booking.reservation ; -VACUUM ANALYZE VERBOSE booking.resource ; -VACUUM ANALYZE VERBOSE booking.resource_attr ; -VACUUM ANALYZE VERBOSE booking.resource_attr_value ; -VACUUM ANALYZE VERBOSE booking.resource_type ; -VACUUM ANALYZE VERBOSE config.barcode_completion ; -VACUUM ANALYZE VERBOSE config.circ_limit_set ; -VACUUM ANALYZE VERBOSE config.circ_matrix_matchpoint ; -VACUUM ANALYZE VERBOSE config.filter_dialog_filter_set ; -VACUUM ANALYZE VERBOSE config.floating_group_member ; -VACUUM ANALYZE VERBOSE config.hold_matrix_matchpoint ; -VACUUM ANALYZE VERBOSE config.idl_field_doc ; -VACUUM ANALYZE VERBOSE config.org_unit_setting_type_log ; -VACUUM ANALYZE VERBOSE config.remote_account ; -VACUUM ANALYZE VERBOSE config.weight_assoc ; -VACUUM ANALYZE VERBOSE config.z3950_source_credentials ; -VACUUM ANALYZE VERBOSE metabib.browse_entry_def_map ; -VACUUM ANALYZE VERBOSE metabib.record_attr_vector_list ; -VACUUM ANALYZE VERBOSE metabib.record_sorter ; -VACUUM ANALYZE VERBOSE money.billable_xact ; -VACUUM ANALYZE VERBOSE money.billing; -VACUUM ANALYZE VERBOSE money.bnm_desk_payment; -VACUUM ANALYZE VERBOSE money.cash_payment; -VACUUM ANALYZE VERBOSE money.check_payment; -VACUUM ANALYZE VERBOSE money.collections_tracker ; -VACUUM ANALYZE VERBOSE money.credit_card_payment; -VACUUM ANALYZE VERBOSE money.credit_payment; -VACUUM ANALYZE VERBOSE money.forgive_payment; -VACUUM ANALYZE VERBOSE money.goods_payment; -VACUUM ANALYZE VERBOSE money.grocery ; -VACUUM ANALYZE VERBOSE money.materialized_billable_xact_summary ; -VACUUM ANALYZE VERBOSE money.payment; -VACUUM ANALYZE VERBOSE money.work_payment; -VACUUM ANALYZE VERBOSE permission.grp_penalty_threshold ; -VACUUM ANALYZE VERBOSE permission.usr_work_ou_map ; -VACUUM ANALYZE VERBOSE reporter.output_folder ; -VACUUM ANALYZE VERBOSE reporter.report ; -VACUUM ANALYZE VERBOSE reporter.report_folder ; -VACUUM ANALYZE VERBOSE reporter.schedule ; -VACUUM ANALYZE VERBOSE reporter.template ; -VACUUM ANALYZE VERBOSE reporter.template_folder ; -VACUUM ANALYZE VERBOSE serial.distribution ; -VACUUM ANALYZE VERBOSE serial.record_entry ; -VACUUM ANALYZE VERBOSE serial.subscription ; -VACUUM ANALYZE VERBOSE vandelay.import_bib_trash_group ; -VACUUM ANALYZE VERBOSE vandelay.import_item ; -VACUUM ANALYZE VERBOSE vandelay.import_item_attr_definition ; -VACUUM ANALYZE VERBOSE vandelay.match_set ; -VACUUM ANALYZE VERBOSE vandelay.merge_profile ; -VACUUM ANALYZE VERBOSE vandelay.queue ; -VACUUM ANALYZE VERBOSE vandelay.queued_bib_record ; diff --git a/remove_ou_data/19_vacuum_analyze.sql b/remove_ou_data/19_vacuum_analyze.sql new file mode 100644 index 0000000..1d60fb1 --- /dev/null +++ b/remove_ou_data/19_vacuum_analyze.sql @@ -0,0 +1,113 @@ +VACUUM ANALYZE VERBOSE acq.acq_lineitem_history ; +VACUUM ANALYZE VERBOSE acq.distribution_formula_entry ; +VACUUM ANALYZE VERBOSE acq.fund_allocation_percent ; +VACUUM ANALYZE VERBOSE acq.lineitem ; +VACUUM ANALYZE VERBOSE action.aged_circulation ; +VACUUM ANALYZE VERBOSE action.aged_hold_request ; +VACUUM ANALYZE VERBOSE action.circulation ; +VACUUM ANALYZE VERBOSE action.fieldset ; +VACUUM ANALYZE VERBOSE action.hold_request ; +VACUUM ANALYZE VERBOSE action.hold_transit_copy ; +VACUUM ANALYZE VERBOSE action.in_house_use ; +VACUUM ANALYZE VERBOSE action.non_cataloged_circulation ; +VACUUM ANALYZE VERBOSE action.non_cat_in_house_use ; +VACUUM ANALYZE VERBOSE action.transit_copy ; +VACUUM ANALYZE VERBOSE action_trigger.environment; +VACUUM ANALYZE VERBOSE action_trigger.event; +VACUUM ANALYZE VERBOSE action_trigger.event_definition; +VACUUM ANALYZE VERBOSE action_trigger.event_params; +VACUUM ANALYZE VERBOSE actor.address_alert ; +VACUUM ANALYZE VERBOSE actor.card ; +VACUUM ANALYZE VERBOSE actor.hours_of_operation ; +VACUUM ANALYZE VERBOSE actor.org_address ; +VACUUM ANALYZE VERBOSE actor.org_lasso_map ; +VACUUM ANALYZE VERBOSE actor.org_unit ; +VACUUM ANALYZE VERBOSE actor.org_unit_closed ; +VACUUM ANALYZE VERBOSE actor.org_unit_custom_tree_node ; +VACUUM ANALYZE VERBOSE actor.org_unit_proximity_adjustment ; +VACUUM ANALYZE VERBOSE actor.org_unit_setting ; +VACUUM ANALYZE VERBOSE actor.search_filter_group ; +VACUUM ANALYZE VERBOSE actor.stat_cat ; +VACUUM ANALYZE VERBOSE actor.stat_cat_entry ; +VACUUM ANALYZE VERBOSE actor.stat_cat_entry_default ; +VACUUM ANALYZE VERBOSE actor.stat_cat_entry_usr_map ; +VACUUM ANALYZE VERBOSE actor.toolbar ; +VACUUM ANALYZE VERBOSE actor.usr ; +VACUUM ANALYZE VERBOSE actor.usr_activity ; +VACUUM ANALYZE VERBOSE actor.usr_address ; +VACUUM ANALYZE VERBOSE actor.usr_message ; +VACUUM ANALYZE VERBOSE actor.usr_note ; +VACUUM ANALYZE VERBOSE actor.usr_org_unit_opt_in ; +VACUUM ANALYZE VERBOSE actor.usr_password_reset ; +VACUUM ANALYZE VERBOSE actor.usr_saved_search ; +VACUUM ANALYZE VERBOSE actor.usr_setting ; +VACUUM ANALYZE VERBOSE actor.usr_standing_penalty ; +VACUUM ANALYZE VERBOSE actor.workstation ; +VACUUM ANALYZE VERBOSE asset.call_number ; +VACUUM ANALYZE VERBOSE asset.copy ; +VACUUM ANALYZE VERBOSE asset.copy_location ; +VACUUM ANALYZE VERBOSE asset.copy_note ; +VACUUM ANALYZE VERBOSE asset.copy_part_map ; +VACUUM ANALYZE VERBOSE asset.copy_template ; +VACUUM ANALYZE VERBOSE asset.opac_visible_copies ; +VACUUM ANALYZE VERBOSE asset.stat_cat_entry_copy_map ; +VACUUM ANALYZE VERBOSE asset.uri_call_number_map ; +VACUUM ANALYZE VERBOSE auditor.actor_usr_address_history ; +VACUUM ANALYZE VERBOSE auditor.actor_usr_history ; +VACUUM ANALYZE VERBOSE auditor.asset_call_number_history ; +VACUUM ANALYZE VERBOSE auditor.asset_copy_history ; +VACUUM ANALYZE VERBOSE authority.bib_linking ; +VACUUM ANALYZE VERBOSE biblio.monograph_part ; +VACUUM ANALYZE VERBOSE biblio.peer_bib_copy_map ; +VACUUM ANALYZE VERBOSE biblio.record_entry ; +VACUUM ANALYZE VERBOSE booking.reservation ; +VACUUM ANALYZE VERBOSE booking.resource ; +VACUUM ANALYZE VERBOSE booking.resource_attr ; +VACUUM ANALYZE VERBOSE booking.resource_attr_value ; +VACUUM ANALYZE VERBOSE booking.resource_type ; +VACUUM ANALYZE VERBOSE config.barcode_completion ; +VACUUM ANALYZE VERBOSE config.circ_limit_set ; +VACUUM ANALYZE VERBOSE config.circ_matrix_matchpoint ; +VACUUM ANALYZE VERBOSE config.filter_dialog_filter_set ; +VACUUM ANALYZE VERBOSE config.floating_group_member ; +VACUUM ANALYZE VERBOSE config.hold_matrix_matchpoint ; +VACUUM ANALYZE VERBOSE config.idl_field_doc ; +VACUUM ANALYZE VERBOSE config.org_unit_setting_type_log ; +VACUUM ANALYZE VERBOSE config.remote_account ; +VACUUM ANALYZE VERBOSE config.weight_assoc ; +VACUUM ANALYZE VERBOSE config.z3950_source_credentials ; +VACUUM ANALYZE VERBOSE metabib.browse_entry_def_map ; +VACUUM ANALYZE VERBOSE metabib.record_attr_vector_list ; +VACUUM ANALYZE VERBOSE metabib.record_sorter ; +VACUUM ANALYZE VERBOSE money.billable_xact ; +VACUUM ANALYZE VERBOSE money.billing; +VACUUM ANALYZE VERBOSE money.bnm_desk_payment; +VACUUM ANALYZE VERBOSE money.cash_payment; +VACUUM ANALYZE VERBOSE money.check_payment; +VACUUM ANALYZE VERBOSE money.collections_tracker ; +VACUUM ANALYZE VERBOSE money.credit_card_payment; +VACUUM ANALYZE VERBOSE money.credit_payment; +VACUUM ANALYZE VERBOSE money.forgive_payment; +VACUUM ANALYZE VERBOSE money.goods_payment; +VACUUM ANALYZE VERBOSE money.grocery ; +VACUUM ANALYZE VERBOSE money.materialized_billable_xact_summary ; +VACUUM ANALYZE VERBOSE money.payment; +VACUUM ANALYZE VERBOSE money.work_payment; +VACUUM ANALYZE VERBOSE permission.grp_penalty_threshold ; +VACUUM ANALYZE VERBOSE permission.usr_work_ou_map ; +VACUUM ANALYZE VERBOSE reporter.output_folder ; +VACUUM ANALYZE VERBOSE reporter.report ; +VACUUM ANALYZE VERBOSE reporter.report_folder ; +VACUUM ANALYZE VERBOSE reporter.schedule ; +VACUUM ANALYZE VERBOSE reporter.template ; +VACUUM ANALYZE VERBOSE reporter.template_folder ; +VACUUM ANALYZE VERBOSE serial.distribution ; +VACUUM ANALYZE VERBOSE serial.record_entry ; +VACUUM ANALYZE VERBOSE serial.subscription ; +VACUUM ANALYZE VERBOSE vandelay.import_bib_trash_group ; +VACUUM ANALYZE VERBOSE vandelay.import_item ; +VACUUM ANALYZE VERBOSE vandelay.import_item_attr_definition ; +VACUUM ANALYZE VERBOSE vandelay.match_set ; +VACUUM ANALYZE VERBOSE vandelay.merge_profile ; +VACUUM ANALYZE VERBOSE vandelay.queue ; +VACUUM ANALYZE VERBOSE vandelay.queued_bib_record ; -- 1.7.2.5