From f4647696cc218a7c146fd327aa378d6e7ac4f77e Mon Sep 17 00:00:00 2001 From: Jason Stephenson Date: Fri, 5 Jan 2018 11:43:44 -0500 Subject: [PATCH] Lp 1741087: Create Missing Functions From 1081 Upgrade Script Add a db upgrade script to create the functions that failed to be created by the code in the 1081 upgrade script. This upgrade script also drops and recreates the triggers that were created in the 1081 upgrade. The code for the new functions and triggers is based on what was manually added to the 070.schema.container.sql and 800.fkeys.sql base schema files. Signed-off-by: Jason Stephenson --- .../sql/Pg/upgrade/XXXX.schema.fix-lp1741087.sql | 59 ++++++++++++++++++++++ 1 file changed, 59 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-lp1741087.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-lp1741087.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-lp1741087.sql new file mode 100644 index 0000000000..04a633c280 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fix-lp1741087.sql @@ -0,0 +1,59 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.container_copy_bucket_item_target_copy_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.target_copy; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, target_copy:%s$$, NEW.target_copy + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +DROP TRIGGER IF EXISTS inherit_copy_bucket_item_target_copy_fkey ON container.copy_bucket_item; + +CREATE CONSTRAINT TRIGGER inherit_copy_bucket_item_target_copy_fkey + AFTER UPDATE OR INSERT OR DELETE ON container.copy_bucket_item + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.container_copy_bucket_item_target_copy_inh_fkey(); + +CREATE OR REPLACE FUNCTION evergreen.vandelay_import_item_imported_as_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.imported_as; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, imported_as:%s$$, NEW.imported_as + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +DROP TRIGGER IF EXISTS inherit_import_item_imported_as_fkey ON vandelay.import_item; + +CREATE CONSTRAINT TRIGGER inherit_import_item_imported_as_fkey + AFTER UPDATE OR INSERT OR DELETE ON vandelay.import_item + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.vandelay_import_item_imported_as_inh_fkey(); + +CREATE OR REPLACE FUNCTION evergreen.asset_copy_note_owning_copy_inh_fkey() RETURNS TRIGGER AS $f$ +BEGIN + PERFORM 1 FROM asset.copy WHERE id = NEW.owning_copy; + IF NOT FOUND THEN + RAISE foreign_key_violation USING MESSAGE = FORMAT( + $$Referenced asset.copy id not found, owning_copy:%s$$, NEW.owning_copy + ); + END IF; + RETURN NEW; +END; +$f$ LANGUAGE PLPGSQL VOLATILE COST 50; + +DROP TRIGGER IF EXISTS inherit_asset_copy_note_copy_fkey ON asset.copy_note; + +CREATE CONSTRAINT TRIGGER inherit_asset_copy_note_copy_fkey + AFTER UPDATE OR INSERT OR DELETE ON asset.copy_note + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_note_owning_copy_inh_fkey(); + +COMMIT; -- 2.11.0