From 1aea64df0480c23cf895d637c1846d2974da5f97 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Tue, 31 May 2011 17:30:31 -0400 Subject: [PATCH] Make database schema patch dependencies work more better Per a suggestion from Bill Erickson, move evergreen_patch into the evergreen schema (where we can simply call it evergreen.patch). Also, as we seem doomed to repeat ourselves, add a little bit of schema adjustment to the 0526 upgrade script so it can be applied retroactively. Signed-off-by: Dan Scott Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/000.functions.general.sql | 3 +++ Open-ILS/src/sql/Pg/002.schema.config.sql | 6 ++---- .../sql/Pg/upgrade/0526.schema.upgrade-dep-tracking.sql | 14 +++++++++++--- 3 files changed, 16 insertions(+), 7 deletions(-) diff --git a/Open-ILS/src/sql/Pg/000.functions.general.sql b/Open-ILS/src/sql/Pg/000.functions.general.sql index 2bf6340fff..52301a457f 100644 --- a/Open-ILS/src/sql/Pg/000.functions.general.sql +++ b/Open-ILS/src/sql/Pg/000.functions.general.sql @@ -26,4 +26,7 @@ CREATE OR REPLACE FUNCTION evergreen.xml_escape(str TEXT) RETURNS text AS $$ '>', '>'); $$ LANGUAGE SQL IMMUTABLE; +-- Provide a named type for patching functions +CREATE TYPE evergreen.patch AS (patch TEXT); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index b98e2de3a6..4bc5fad9d1 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -823,10 +823,8 @@ 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 evergreen_patch 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) @@ -834,7 +832,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 evergreen_patch 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) 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 d455ee4c3f..86b381de57 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 @@ -1,3 +1,10 @@ +-- 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 @@ -33,10 +40,11 @@ CREATE TRIGGER no_overlapping_deps ALTER TABLE config.upgrade_log ADD COLUMN applied_to TEXT; -CREATE TYPE evergreen_patch AS (patch 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 $$ +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) @@ -44,7 +52,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 evergreen_patch 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) -- 2.11.0