From: Rogan Hamby <rhamby@esilibrary.com>
Date: Mon, 17 Oct 2016 14:03:24 +0000 (-0400)
Subject: remove albermale references by putting in a variable and deleting usr messages
X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=commitdiff_plain;h=9e967d7a4207176f0778f0e296ab39d8223eb431;hp=0cefde660c085b23bc9a6120762ae221cec32bbb

remove albermale references by putting in a variable and deleting usr messages
---

diff --git a/remove_ou_data/02_remove_patron_extras.sql b/remove_ou_data/02_remove_patron_extras.sql
index bc9b96a..b993d7b 100644
--- a/remove_ou_data/02_remove_patron_extras.sql
+++ b/remove_ou_data/02_remove_patron_extras.sql
@@ -21,6 +21,7 @@
 
 ALTER TABLE actor.usr_address DISABLE TRIGGER audit_actor_usr_address_update_trigger;
 ALTER TABLE actor.usr DISABLE TRIGGER audit_actor_usr_update_trigger;
+ALTER TABLE actor.usr_message DISABLE RULE protect_usr_message_delete;
 
 CREATE INDEX tmp_addr_replaces ON actor.usr_address(replaces);
 BEGIN;
@@ -59,8 +60,12 @@ DELETE FROM actor.usr_address WHERE usr IN
 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);
 
+DELETE FROM actor.usr_message 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;
 
 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;
+ALTER TABLE actor.usr_message ENABLE RULE protect_usr_message_delete;
diff --git a/remove_ou_data/07_remove_volumes.sql b/remove_ou_data/07_remove_volumes.sql
index 03f8754..0447e2a 100644
--- a/remove_ou_data/07_remove_volumes.sql
+++ b/remove_ou_data/07_remove_volumes.sql
@@ -16,9 +16,11 @@
 -- 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;
@@ -30,7 +32,7 @@ DELETE FROM asset.uri_call_number_map WHERE call_number 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
+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);
 
@@ -43,4 +45,4 @@ 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);
+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
index a81c9ae..e38c059 100644
--- a/remove_ou_data/08_remove_bibs_stage_1.sql
+++ b/remove_ou_data/08_remove_bibs_stage_1.sql
@@ -16,6 +16,7 @@
 -- 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
 
@@ -24,7 +25,7 @@ ALTER TABLE biblio.record_entry DISABLE TRIGGER audit_biblio_record_entry_update
 BEGIN;
 
 DELETE FROM biblio.record_entry WHERE id IN (
-    SELECT record FROM esi.albemarle_volume_bibs x 
+    SELECT record FROM esi.:vol_del_table x 
     WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
 );
 
diff --git a/remove_ou_data/08_remove_bibs_stage_2.sql b/remove_ou_data/08_remove_bibs_stage_2.sql
index 93d1746..86c6e48 100644
--- a/remove_ou_data/08_remove_bibs_stage_2.sql
+++ b/remove_ou_data/08_remove_bibs_stage_2.sql
@@ -16,6 +16,7 @@
 -- 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
 
@@ -23,38 +24,38 @@ BEGIN;
 
 DELETE FROM authority.bib_linking WHERE bib IN 
 (
-    SELECT record FROM esi.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+        SELECT record FROM esi.:vol_del_table x
         WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
     )
 );
@@ -62,32 +63,32 @@ DELETE FROM acq.lineitem WHERE queued_record IN (
 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
+        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.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+    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.albemarle_volume_bibs x
+    SELECT record FROM esi.:vol_del_table x
     WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
 );
 
diff --git a/remove_ou_data/08_remove_bibs_stage_3.sql b/remove_ou_data/08_remove_bibs_stage_3.sql
index 205d88a..e8e08f9 100644
--- a/remove_ou_data/08_remove_bibs_stage_3.sql
+++ b/remove_ou_data/08_remove_bibs_stage_3.sql
@@ -16,6 +16,7 @@
 -- 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
 
@@ -27,7 +28,7 @@ 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 
+    SELECT record FROM esi.:vol_del_table x 
     WHERE NOT EXISTS (select 1 from asset.call_number where record = x.record)
 );