From 340e026918ecca3ab7dfef6a67160ed516cca835 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Tue, 31 May 2011 13:19:08 -0400 Subject: [PATCH] Make patch-checking function able to raise a notice There's probably a more elegant way to do this, but basic testing with the following at least didn't throw an error: SELECT evergreen.upgrade_deps_block_check('XXXX', NULL); Also note that the CREATE OR REPLACE function calls will fail on an existing database due to the changed return type of the functions - so if this gets moved into its own upgrade script, DROP FUNCTION calls will be required. Signed-off-by: Dan Scott Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/002.schema.config.sql | 21 ++++++++++++++------- .../Pg/upgrade/0526.schema.upgrade-dep-tracking.sql | 15 +++++++++++---- 2 files changed, 25 insertions(+), 11 deletions(-) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 822623b927..b98e2de3a6 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -823,20 +823,22 @@ BEGIN END; $$ LANGUAGE PLPGSQL; +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 TEXT AS $$ +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 + 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 TEXT AS $$ +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 + WHERE d.db_patch = $1 $$ LANGUAGE SQL; -- List applied db patches that deprecates (and block the application of) my_db_patch @@ -865,10 +867,15 @@ CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_pat SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x $$ LANGUAGE SQL; --- Raise an exception if there are, in fact, dep/sup confilct +-- Raise an exception if there are, in fact, dep/sup conflict 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 % @@ -876,8 +883,8 @@ Upgrade script % can not be applied: 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)), + deprecates, + supersedes, evergreen.upgrade_list_applied_deprecated(my_db_patch), evergreen.upgrade_list_applied_superseded(my_db_patch); END IF; diff --git a/Open-ILS/src/sql/Pg/upgrade/0526.schema.upgrade-dep-tracking.sql b/Open-ILS/src/sql/Pg/upgrade/0526.schema.upgrade-dep-tracking.sql index 42de0788d2..d455ee4c3f 100644 --- a/Open-ILS/src/sql/Pg/upgrade/0526.schema.upgrade-dep-tracking.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0526.schema.upgrade-dep-tracking.sql @@ -33,8 +33,10 @@ CREATE TRIGGER no_overlapping_deps ALTER TABLE config.upgrade_log ADD COLUMN applied_to TEXT; +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 TEXT AS $$ +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) @@ -42,7 +44,7 @@ CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_pat $$ 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 TEXT AS $$ +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) @@ -77,8 +79,13 @@ $$ 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 % @@ -86,8 +93,8 @@ Upgrade script % can not be applied: 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)), + deprecates, + supersedes, evergreen.upgrade_list_applied_deprecated(my_db_patch), evergreen.upgrade_list_applied_superseded(my_db_patch); END IF; -- 2.11.0