From d23425bd213bfcd4e6b91889b1e0b3b364f49784 Mon Sep 17 00:00:00 2001 From: miker Date: Wed, 27 Apr 2011 20:25:10 +0000 Subject: [PATCH] fix the new deps stuff git-svn-id: svn://svn.open-ils.org/ILS/trunk@20351 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 46 +++++++++++++------ Open-ILS/src/sql/Pg/build-db.sh | 4 +- .../0526.schema.upgrade-dep-tracking.sql | 46 +++++++++++++------ 3 files changed, 68 insertions(+), 28 deletions(-) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 5795a88640..91e46e46fa 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -61,11 +61,31 @@ CREATE TABLE config.upgrade_log ( CREATE TABLE config.db_patch_dependencies ( db_patch TEXT PRIMARY KEY, supersedes TEXT[], - deprecates TEXT[], - CONSTRAINT supersede_once EXCLUDE USING GIST ( supersedes WITH && ), - CONSTRAINT deprecate_once EXCLUDE USING GIST ( deprecates WITH && ) + deprecates TEXT[] ); +CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$ +DECLARE + fld TEXT; + cnt INT; +BEGIN + fld := TG_ARGV[1]; + EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW; + IF cnt > 0 THEN + RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME; + END IF; + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER no_overlapping_sups + BEFORE INSERT OR UPDATE ON config.db_patch_dependencies + FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes'); + +CREATE TRIGGER no_overlapping_deps + BEFORE INSERT OR UPDATE ON config.db_patch_dependencies + FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); + INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0526', :eg_version); -- miker CREATE TABLE config.bib_source ( @@ -808,7 +828,7 @@ CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_pat SELECT DISTINCT l.version FROM config.upgrade_log l JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates) - WHERE d.db_patch = my_db_patch + WHERE d.db_patch = $1 $$ LANGUAGE SQL; -- List applied db patches that are superseded by (and block the application of) my_db_patch @@ -816,33 +836,33 @@ CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_pat SELECT DISTINCT l.version FROM config.upgrade_log l JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes) - WHERE d.db_patch = my_db_patch + WHERE d.db_patch = $1 $$ LANGUAGE SQL; -- List applied db patches that deprecates (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$ +CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$ SELECT db_patch FROM config.db_patch_dependencies - WHERE my_db_patch::TEXT[] && deprecates + WHERE ARRAY[$1]::TEXT[] && deprecates $$ LANGUAGE SQL; -- List applied db patches that supersedes (and block the application of) my_db_patch CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$ SELECT db_patch FROM config.db_patch_dependencies - WHERE my_db_patch::TEXT[] && supersedes + WHERE ARRAY[$1]::TEXT[] && supersedes $$ LANGUAGE SQL; -- Make sure that no deprecated or superseded db patches are currently applied CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$ SELECT COUNT(*) = 0 - FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( my_db_patch ) + FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 ) UNION - SELECT * FROM evergreen.upgrade_list_applied_supersedes( my_db_patch ) + SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 ) UNION - SELECT * FROM evergreen.upgrade_list_applied_deprecated( my_db_patch ) + SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 ) UNION - SELECT * FROM evergreen.upgrade_list_applied_superseded( my_db_patch ))x + SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x $$ LANGUAGE SQL; -- Raise an exception if there are, in fact, dep/sup confilct @@ -865,6 +885,6 @@ Upgrade script % can not be applied: INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to); RETURN TRUE; END; -$$ LANGUAGE SQL; +$$ LANGUAGE PLPGSQL; COMMIT; diff --git a/Open-ILS/src/sql/Pg/build-db.sh b/Open-ILS/src/sql/Pg/build-db.sh index 6ed4588191..2b3540ca09 100755 --- a/Open-ILS/src/sql/Pg/build-db.sh +++ b/Open-ILS/src/sql/Pg/build-db.sh @@ -96,9 +96,9 @@ cat sql_file_manifest | while read sql_file; do export PGHOST PGPORT PGDATABASE PGUSER PGPASSWORD # Hide most of the harmless messages that obscure real problems if [ -z "$VERBOSE" ]; then - psql -f $sql_file 2>&1 | grep -v NOTICE | grep -v "^INSERT" + psql -v eg_version=NULL -f $sql_file 2>&1 | grep -v NOTICE | grep -v "^INSERT" else - psql -f $sql_file + psql -v eg_version=NULL -f $sql_file fi if [ $? != 0 ]; then cat < 0 THEN + RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME; + END IF; + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER no_overlapping_sups + BEFORE INSERT OR UPDATE ON config.db_patch_dependencies + FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes'); + +CREATE TRIGGER no_overlapping_deps + BEFORE INSERT OR UPDATE ON config.db_patch_dependencies + FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); + ALTER TABLE config.upgrade_log ADD COLUMN applied_to TEXT; @@ -18,7 +38,7 @@ CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_pat SELECT DISTINCT l.version FROM config.upgrade_log l JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates) - WHERE d.db_patch = my_db_patch + WHERE d.db_patch = $1 $$ LANGUAGE SQL; -- List applied db patches that are superseded by (and block the application of) my_db_patch @@ -26,33 +46,33 @@ CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_pat SELECT DISTINCT l.version FROM config.upgrade_log l JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes) - WHERE d.db_patch = my_db_patch + WHERE d.db_patch = $1 $$ LANGUAGE SQL; -- List applied db patches that deprecates (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$ +CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$ SELECT db_patch FROM config.db_patch_dependencies - WHERE my_db_patch::TEXT[] && deprecates + WHERE ARRAY[$1]::TEXT[] && deprecates $$ LANGUAGE SQL; -- List applied db patches that supersedes (and block the application of) my_db_patch CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$ SELECT db_patch FROM config.db_patch_dependencies - WHERE my_db_patch::TEXT[] && supersedes + WHERE ARRAY[$1]::TEXT[] && supersedes $$ LANGUAGE SQL; -- Make sure that no deprecated or superseded db patches are currently applied CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$ SELECT COUNT(*) = 0 - FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( my_db_patch ) + FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 ) UNION - SELECT * FROM evergreen.upgrade_list_applied_supersedes( my_db_patch ) + SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 ) UNION - SELECT * FROM evergreen.upgrade_list_applied_deprecated( my_db_patch ) + SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 ) UNION - SELECT * FROM evergreen.upgrade_list_applied_superseded( my_db_patch ))x + SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x $$ LANGUAGE SQL; -- Raise an exception if there are, in fact, dep/sup confilct @@ -75,6 +95,6 @@ Upgrade script % can not be applied: INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to); RETURN TRUE; END; -$$ LANGUAGE SQL; +$$ LANGUAGE PLPGSQL; COMMIT; -- 2.43.2