From 290a336fde9810cfd1be0df364634d629c77dc67 Mon Sep 17 00:00:00 2001 From: Galen Charlton 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 Signed-off-by: Mike Rylander --- 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 ++-- .../Pg/upgrade/XXXX.schema.copy_alerts.sql | 18 ++++++++++++++- .../YYYY.data.stock_copy_alert_types.sql | 4 ++-- .../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.43.2