From aeb734c75db65650be80c884f55e33ca049b58fa Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Tue, 29 Dec 2015 13:53:04 -0500 Subject: [PATCH] LP#1506534: Improve czifm z3950_attr_type Check Rather than use a CHECK constraint, this patch uses a CONSTRAINT TRIGGER, er, constraint to verify the z3950_attr_type field. This allows the trigger to be deferred as any other deferrable constraint and also allows parallel pg_restore jobs to complete properly. Signed-off-by: Jason Boyer Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/002.schema.config.sql | 30 ++++++++++------- .../sql/Pg/t/lp1506534_z3950_attr_check.pg | 21 ++++++++++++ .../XXXX.schema.config_z3950_attr_check.sql | 33 +++++++++++++++++++ 3 files changed, 73 insertions(+), 11 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/t/lp1506534_z3950_attr_check.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config_z3950_attr_check.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index c2ebbe6610..3d3d0c2923 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -1055,13 +1055,22 @@ ALTER TABLE config.best_hold_order ADD CHECK (( )); CREATE OR REPLACE FUNCTION - evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$ - SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1); -$func$ LANGUAGE SQL STRICT STABLE; + evergreen.z3950attr_name_is_valid() RETURNS TRIGGER AS $func$ + BEGIN + + PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type; + + IF FOUND THEN + RETURN NULL; + END IF; + + RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type; -COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$ -Results in TRUE if there exists at least one config.z3950_attr -with the provided name. Used by config.z3950_index_field_map + END; +$func$ LANGUAGE PLPGSQL STABLE; + +COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$ +Used by a config.z3950_index_field_map constraint trigger to verify z3950_attr_type maps. $$; @@ -1081,14 +1090,13 @@ CREATE TABLE config.z3950_index_field_map ( CONSTRAINT attr_or_attr_type CHECK ( z3950_attr IS NOT NULL OR z3950_attr_type IS NOT NULL - ), - -- ensure the selected z3950_attr_type refers to a valid attr name - CONSTRAINT valid_z3950_attr_type CHECK ( - z3950_attr_type IS NULL OR - evergreen.z3950_attr_name_is_valid(z3950_attr_type) ) ); +CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map + DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL) + EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid(); + CREATE TABLE config.marc_format ( id SERIAL PRIMARY KEY, code TEXT NOT NULL, diff --git a/Open-ILS/src/sql/Pg/t/lp1506534_z3950_attr_check.pg b/Open-ILS/src/sql/Pg/t/lp1506534_z3950_attr_check.pg new file mode 100644 index 0000000000..dce44d6d22 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/lp1506534_z3950_attr_check.pg @@ -0,0 +1,21 @@ +BEGIN; +SELECT plan(5); + +-- Is the proper constraint present? +SELECT is((SELECT COUNT(*) FROM pg_constraint WHERE conname='valid_z3950_attr_type' AND contype='c')::INTEGER, 0::INTEGER, 'CHECK constraint not present'); +SELECT has_trigger('config', 'z3950_index_field_map', 'valid_z3950_attr_type', 'CONSTRAINT TRIGGER present on config.z3950_index_field_map'); + +-- Does it work properly? +SET CONSTRAINTS config.valid_z3950_attr_type IMMEDIATE; +PREPARE busted_insert AS INSERT INTO config.z3950_index_field_map (label, metabib_field, z3950_attr_type) VALUES ('Title', 5, 'sadface'); +PREPARE ok_type_insert AS INSERT INTO config.z3950_index_field_map (label, metabib_field, z3950_attr_type) VALUES ('Title2', 5, 'title'); +PREPARE ok_attr_insert AS INSERT INTO config.z3950_index_field_map (label, metabib_field, z3950_attr) VALUES ('Title3', 5, 5); + +SELECT throws_ok('busted_insert', 'P0001', 'sadface is not a valid Z39.50 attribute type', 'Invalid Insert Test'); +SELECT lives_ok('ok_type_insert', 'Valid z3950_attr_type Insert'); +SELECT lives_ok('ok_attr_insert', 'Valid z3950_attr Insert, z3950_attr_type is NULL'); + +-- Finish the tests and clean up. +SELECT * FROM finish(); +ROLLBACK; + diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config_z3950_attr_check.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config_z3950_attr_check.sql new file mode 100644 index 0000000000..8267292f7a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.config_z3950_attr_check.sql @@ -0,0 +1,33 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +ALTER TABLE config.z3950_index_field_map DROP CONSTRAINT "valid_z3950_attr_type"; + +DROP FUNCTION evergreen.z3950_attr_name_is_valid(text); + +CREATE OR REPLACE FUNCTION evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$ +BEGIN + + PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type; + + IF FOUND THEN + RETURN NULL; + END IF; + + RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type; + +END; +$func$ LANGUAGE PLPGSQL STABLE; + +COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$ +Used by a config.z3950_index_field_map constraint trigger +to verify z3950_attr_type maps. +$$; + +CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map + DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL) + EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid(); + +COMMIT; + -- 2.43.2