From 8171d5c3d47229590b98bd7d67c6d6aec05b9fd4 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 27 Sep 2017 12:30:44 -0400 Subject: [PATCH 1/1] LP#1719726: ensure asset.copy_tag_copy_map has fake FK Signed-off-by: Galen Charlton Signed-off-by: Mike Rylander Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/040.schema.asset.sql | 3 +-- Open-ILS/src/sql/Pg/800.fkeys.sql | 16 ++++++++++++++++ 2 files changed, 17 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 0faba5f015..f9948cb747 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -977,8 +977,7 @@ CREATE TRIGGER asset_copy_tag_fti_trigger CREATE TABLE asset.copy_tag_copy_map ( id BIGSERIAL PRIMARY KEY, - copy BIGINT REFERENCES asset.copy (id) - ON UPDATE CASCADE ON DELETE CASCADE, + copy BIGINT, tag INTEGER REFERENCES asset.copy_tag (id) ON UPDATE CASCADE ON DELETE CASCADE ); diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index 1ee6ead440..13129f5002 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -145,6 +145,22 @@ 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(); +CREATE OR REPLACE FUNCTION evergreen.asset_copy_tag_copy_map_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_tag_copy_map_copy_fkey + AFTER UPDATE OR INSERT OR DELETE ON asset.copy_tag_copy_map + DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE evergreen.asset_copy_tag_copy_map_copy_inh_fkey(); + ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE asset.call_number ADD CONSTRAINT asset_call_number_owning_lib_fkey FOREIGN KEY (owning_lib) REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED; -- 2.43.2