From 8a6258d5580ddca3803737d29ccee8a760b3939c Mon Sep 17 00:00:00 2001 From: Chris Sharp Date: Sun, 17 Feb 2013 08:51:39 -0500 Subject: [PATCH] for whatever reason, dependency tracking is not added via the "normal" channels, so far as I can tell --- .../version-upgrade/pines-pre-2.1-2.2-upgrade.sql | 118 +++++++++++++++++++++ .../version-upgrade/pines-upgrade-dep-tracking.sql | 115 -------------------- 2 files changed, 118 insertions(+), 115 deletions(-) delete mode 100644 Open-ILS/src/sql/Pg/version-upgrade/pines-upgrade-dep-tracking.sql diff --git a/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql b/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql index 79d6d925a7..1bd077fdf3 100644 --- a/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql +++ b/Open-ILS/src/sql/Pg/version-upgrade/pines-pre-2.1-2.2-upgrade.sql @@ -49,3 +49,121 @@ ALTER TABLE biblio.record_entry ENABLE TRIGGER ALL; -- disable triggers on asset.copy ALTER TABLE asset.copy DISABLE TRIGGER ALL; + +-- enable dependency tracking + +-- DROP objects that might have existed from a prior run of 0526 +-- Yes this is ironic. +DROP TABLE IF EXISTS config.db_patch_dependencies; +ALTER TABLE config.upgrade_log DROP COLUMN applied_to; +DROP FUNCTION evergreen.upgrade_list_applied_deprecates(TEXT); +DROP FUNCTION evergreen.upgrade_list_applied_supersedes(TEXT); + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0526'); --miker + +CREATE TABLE config.db_patch_dependencies ( + db_patch TEXT PRIMARY KEY, + supersedes TEXT[], + 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'); + +ALTER TABLE config.upgrade_log + ADD COLUMN applied_to TEXT; + +-- Provide a named type for patching functions +CREATE TYPE evergreen.patch AS (patch TEXT); + +-- List applied db patches that are deprecated by (and block the application of) my_db_patch +CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$ + 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 = $1 +$$ LANGUAGE SQL; + +-- List applied db patches that are superseded by (and block the application of) my_db_patch +CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$ + 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 = $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_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$ + SELECT db_patch + FROM config.db_patch_dependencies + 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 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( $1 ) + UNION + SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 ) + UNION + SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 ) + UNION + SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x +$$ LANGUAGE SQL; + +-- Raise an exception if there are, in fact, dep/sup confilct +CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$ +DECLARE + deprecates TEXT; + supersedes TEXT; +BEGIN + IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN + SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch); + SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch); + RAISE EXCEPTION ' +Upgrade script % can not be applied: + applied deprecated scripts % + applied superseded scripts % + deprecated by % + superseded by %', + my_db_patch, + ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)), + ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)), + evergreen.upgrade_list_applied_deprecated(my_db_patch), + evergreen.upgrade_list_applied_superseded(my_db_patch); + END IF; + + INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to); + RETURN TRUE; +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/version-upgrade/pines-upgrade-dep-tracking.sql b/Open-ILS/src/sql/Pg/version-upgrade/pines-upgrade-dep-tracking.sql deleted file mode 100644 index 16ff50d700..0000000000 --- a/Open-ILS/src/sql/Pg/version-upgrade/pines-upgrade-dep-tracking.sql +++ /dev/null @@ -1,115 +0,0 @@ --- DROP objects that might have existed from a prior run of 0526 --- Yes this is ironic. -DROP TABLE IF EXISTS config.db_patch_dependencies; -ALTER TABLE config.upgrade_log DROP COLUMN applied_to; -DROP FUNCTION evergreen.upgrade_list_applied_deprecates(TEXT); -DROP FUNCTION evergreen.upgrade_list_applied_supersedes(TEXT); - -BEGIN; - -INSERT INTO config.upgrade_log (version) VALUES ('0526'); --miker - -CREATE TABLE config.db_patch_dependencies ( - db_patch TEXT PRIMARY KEY, - supersedes TEXT[], - 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'); - -ALTER TABLE config.upgrade_log - ADD COLUMN applied_to TEXT; - --- Provide a named type for patching functions -CREATE TYPE evergreen.patch AS (patch TEXT); - --- List applied db patches that are deprecated by (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$ - 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 = $1 -$$ LANGUAGE SQL; - --- List applied db patches that are superseded by (and block the application of) my_db_patch -CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$ - 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 = $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_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$ - SELECT db_patch - FROM config.db_patch_dependencies - 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 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( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 ) - UNION - SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x -$$ LANGUAGE SQL; - --- Raise an exception if there are, in fact, dep/sup confilct -CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$ -DECLARE - deprecates TEXT; - supersedes TEXT; -BEGIN - IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN - SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch); - SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch); - RAISE EXCEPTION ' -Upgrade script % can not be applied: - applied deprecated scripts % - applied superseded scripts % - deprecated by % - superseded by %', - my_db_patch, - ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)), - ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)), - evergreen.upgrade_list_applied_deprecated(my_db_patch), - evergreen.upgrade_list_applied_superseded(my_db_patch); - END IF; - - INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to); - RETURN TRUE; -END; -$$ LANGUAGE PLPGSQL; - -COMMIT; -- 2.11.0