From 290a336fde9810cfd1be0df364634d629c77dc67 Mon Sep 17 00:00:00 2001
From: Galen Charlton <gmc@equinoxinitiative.org>
Date: Thu, 22 Feb 2018 17:02:11 -0500
Subject: [PATCH] LP#1676608: provide DB update script to convert legacy copy
alert messages
Legacy copy alert messages are moved to new normal checkout and
normal checkin copy alerts. This patch also converts the foreign
key relationship from asset.copy_alert to asset.copy to a "fake"
one using a constraint trigger.
Signed-off-by: Galen Charlton <gmc@equinoxinitiative.org>
Signed-off-by: Mike Rylander <mrylander@gmail.com>
---
Open-ILS/src/sql/Pg/040.schema.asset.sql | 2 +-
Open-ILS/src/sql/Pg/800.fkeys.sql | 16 +++++++++++++++
Open-ILS/src/sql/Pg/950.data.seed-values.sql | 4 ++--
.../src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql | 18 ++++++++++++++++-
.../upgrade/YYYY.data.stock_copy_alert_types.sql | 4 ++--
.../upgrade/ZZZA.data.move_legacy_copy_alerts.sql | 23 ++++++++++++++++++++++
6 files changed, 61 insertions(+), 6 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/ZZZA.data.move_legacy_copy_alerts.sql
diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql
index d17dcfaa9c..24dda3c41b 100644
--- a/Open-ILS/src/sql/Pg/040.schema.asset.sql
+++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql
@@ -1065,7 +1065,7 @@ CREATE TABLE actor.copy_alert_suppress (
CREATE TABLE asset.copy_alert (
id bigserial primary key,
alert_type int not null references config.copy_alert_type (id) on delete cascade,
- copy bigint not null references asset.copy (id) on delete cascade,
+ copy bigint not null,
temp bool not null default false,
create_time timestamptz not null default now(),
create_staff bigint not null references actor.usr (id) on delete set null,
diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql
index 5835ee3bdd..c396119c55 100644
--- a/Open-ILS/src/sql/Pg/800.fkeys.sql
+++ b/Open-ILS/src/sql/Pg/800.fkeys.sql
@@ -157,6 +157,22 @@ BEGIN
END;
$f$ LANGUAGE PLPGSQL VOLATILE COST 50;
+CREATE OR REPLACE FUNCTION evergreen.asset_copy_alert_copy_inh_fkey() RETURNS TRIGGER AS $f$
+BEGIN
+ PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
+ IF NOT FOUND THEN
+ RAISE foreign_key_violation USING MESSAGE = FORMAT(
+ $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
+ );
+ END IF;
+ RETURN NEW;
+END;
+$f$ LANGUAGE PLPGSQL VOLATILE COST 50;
+
+CREATE CONSTRAINT TRIGGER inherit_asset_copy_alert_copy_fkey
+ AFTER UPDATE OR INSERT ON asset.copy_alert
+ DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_alert_copy_inh_fkey();
+
CREATE CONSTRAINT TRIGGER inherit_asset_copy_tag_copy_map_copy_fkey
AFTER UPDATE OR INSERT ON asset.copy_tag_copy_map
DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_tag_copy_map_copy_inh_fkey();
diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql
index 44dba2d010..ea801150c1 100644
--- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql
+++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql
@@ -17211,9 +17211,9 @@ VALUES (
-- staff-usable alert types with no location awareness
INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
-VALUES (1, 1, FALSE, 'Normal checkout', 'NORMAL', 'CHECKOUT', FALSE);
+VALUES (1, 1, TRUE, 'Normal checkout', 'NORMAL', 'CHECKOUT', FALSE);
INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
-VALUES (2, 1, FALSE, 'Normal checkin', 'NORMAL', 'CHECKIN', FALSE);
+VALUES (2, 1, TRUE, 'Normal checkin', 'NORMAL', 'CHECKIN', FALSE);
INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
VALUES (3, 1, FALSE, 'Normal renewal', 'NORMAL', 'CHECKIN', TRUE);
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql
index db4cd25cc0..993b6790b2 100644
--- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.copy_alerts.sql
@@ -69,6 +69,22 @@ CREATE TABLE config.copy_alert_type (
);
SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
+CREATE OR REPLACE FUNCTION evergreen.asset_copy_alert_copy_inh_fkey() RETURNS TRIGGER AS $f$
+BEGIN
+ PERFORM 1 FROM asset.copy WHERE id = NEW.copy;
+ IF NOT FOUND THEN
+ RAISE foreign_key_violation USING MESSAGE = FORMAT(
+ $$Referenced asset.copy id not found, copy:%s$$, NEW.copy
+ );
+ END IF;
+ RETURN NEW;
+END;
+$f$ LANGUAGE PLPGSQL VOLATILE COST 50;
+
+CREATE CONSTRAINT TRIGGER inherit_asset_copy_alert_copy_fkey
+ AFTER UPDATE OR INSERT ON asset.copy_alert
+ DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_alert_copy_inh_fkey();
+
CREATE TABLE actor.copy_alert_suppress (
id serial primary key,
org int not null references actor.org_unit (id) on delete cascade,
@@ -78,7 +94,7 @@ CREATE TABLE actor.copy_alert_suppress (
CREATE TABLE asset.copy_alert (
id bigserial primary key,
alert_type int not null references config.copy_alert_type (id) on delete cascade,
- copy bigint not null references asset.copy (id) on delete cascade,
+ copy bigint not null,
temp bool not null default false,
create_time timestamptz not null default now(),
create_staff bigint not null references actor.usr (id) on delete set null,
diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.data.stock_copy_alert_types.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.stock_copy_alert_types.sql
index cc09016d9c..ae79a9ca13 100644
--- a/Open-ILS/src/sql/Pg/upgrade/YYYY.data.stock_copy_alert_types.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.data.stock_copy_alert_types.sql
@@ -2,9 +2,9 @@ BEGIN;
-- staff-usable alert types with no location awareness
INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
-VALUES (1, 1, FALSE, 'Normal checkout', 'NORMAL', 'CHECKOUT', FALSE);
+VALUES (1, 1, TRUE, 'Normal checkout', 'NORMAL', 'CHECKOUT', FALSE);
INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
-VALUES (2, 1, FALSE, 'Normal checkin', 'NORMAL', 'CHECKIN', FALSE);
+VALUES (2, 1, TRUE, 'Normal checkin', 'NORMAL', 'CHECKIN', FALSE);
INSERT INTO config.copy_alert_type (id, scope_org, active, name, state, event, in_renew)
VALUES (3, 1, FALSE, 'Normal renewal', 'NORMAL', 'CHECKIN', TRUE);
diff --git a/Open-ILS/src/sql/Pg/upgrade/ZZZA.data.move_legacy_copy_alerts.sql b/Open-ILS/src/sql/Pg/upgrade/ZZZA.data.move_legacy_copy_alerts.sql
new file mode 100644
index 0000000000..f430cbfb45
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/ZZZA.data.move_legacy_copy_alerts.sql
@@ -0,0 +1,23 @@
+BEGIN;
+
+--- SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
+
+\qecho Copying copy alert messages to normal checkout copy alerts...
+INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff)
+SELECT 1, id, alert_message, 1
+FROM asset.copy
+WHERE alert_message IS NOT NULL
+AND alert_message <> '';
+
+\qecho Copying copy alert messages to normal checkin copy alerts...
+INSERT INTO asset.copy_alert (alert_type, copy, note, create_staff)
+SELECT 2, id, alert_message, 1
+FROM asset.copy
+WHERE alert_message IS NOT NULL
+AND alert_message <> '';
+
+\qecho Clearing legacy copy alert field; this may take a while
+UPDATE asset.copy SET alert_message = NULL
+WHERE alert_message IS NOT NULL;
+
+COMMIT;
--
2.11.0