From b7e454b73970bda780caf3830ba844ece0379bee Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Tue, 21 Jul 2020 15:52:31 -0400 Subject: [PATCH] handling foreign owned reports of templates to be deleted when they aren't scheduled in the future --- remove_ou_data/04_remove_circ.sql | 2 +- remove_ou_data/09_remove_bibs_stage_2.sql | 17 +++++++++++++++++ remove_ou_data/10_clear_vandelay_reports.sql | 9 +++++++-- 3 files changed, 25 insertions(+), 3 deletions(-) diff --git a/remove_ou_data/04_remove_circ.sql b/remove_ou_data/04_remove_circ.sql index ed11560..5898f25 100644 --- a/remove_ou_data/04_remove_circ.sql +++ b/remove_ou_data/04_remove_circ.sql @@ -51,7 +51,7 @@ AND org_unit IN (SELECT (actor.org_unit_descendants(id)).id from actor.org_unit DELETE FROM action.usr_circ_history WHERE id IN (SELECT a.id FROM action.usr_circ_history a LEFT JOIN action.circulation b ON b.id = a.source_circ WHERE b.id IS NULL); -DELETE FROM action.circulation_limit_group_map WHERE circ IN (SELECT a.id FROM action.circulation_limit_group_map a LEFT JOIN action.circulation b ON b.id = a.circ WHERE b.id IS NULL); +DELETE FROM action.circulation_limit_group_map WHERE circ IN (SELECT a.circ FROM action.circulation_limit_group_map a LEFT JOIN action.circulation b ON b.id = a.circ WHERE b.id IS NULL); COMMIT; diff --git a/remove_ou_data/09_remove_bibs_stage_2.sql b/remove_ou_data/09_remove_bibs_stage_2.sql index 86c6e48..510ce02 100644 --- a/remove_ou_data/09_remove_bibs_stage_2.sql +++ b/remove_ou_data/09_remove_bibs_stage_2.sql @@ -20,6 +20,8 @@ \set ECHO all \timing +ALTER TABLE biblio.monograph_part DISABLE RULE protect_mono_part_delete; + BEGIN; DELETE FROM authority.bib_linking WHERE bib IN @@ -92,4 +94,19 @@ DELETE FROM booking.resource_type WHERE record IN WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record) ); +DELETE FROM metabib.display_entry 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 metabib.real_full_rec 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; + + +ALTER TABLE biblio.monograph_part ENABLE RULE protect_mono_part_delete; diff --git a/remove_ou_data/10_clear_vandelay_reports.sql b/remove_ou_data/10_clear_vandelay_reports.sql index 2a44d2e..652d0a9 100644 --- a/remove_ou_data/10_clear_vandelay_reports.sql +++ b/remove_ou_data/10_clear_vandelay_reports.sql @@ -27,12 +27,17 @@ 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 +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.report WHERE owner IN +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 template IN + (SELECT id 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)) + ) AND id NOT IN (SELECT report FROM reporter.schedule WHERE complete_time IS NULL); + 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)); -- 1.7.2.5