From f119790a843b81f68abec44c76001455e129f038 Mon Sep 17 00:00:00 2001 From: dbs Date: Fri, 7 Jan 2011 14:00:14 +0000 Subject: [PATCH] When modifying existing indexes, ensure subject|complete drops into ID 16 Other parts of the upgrade script assumed that subject|complete would be ID = 16, so we might as well try and make it conform. This means turning config.bump_metabib_field() into a two-argument function so that we can provide an explicit target ID for the modified index. git-svn-id: svn://svn.open-ils.org/ILS/trunk@19133 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql | 35 +++++++++++++++++++--------- 1 file changed, 24 insertions(+), 11 deletions(-) diff --git a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql index bc4f9aabaa..89945ef2c4 100644 --- a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql @@ -15517,35 +15517,48 @@ INSERT INTO config.metabib_class ( name, label ) VALUES ( 'identifier', oils_i18 -- sequence value to leave room for additional stock indexes in subsequent -- releases (hello!), so custom added indexes will conflict with these. --- The following function just adds 100 to the ID of an existing custom --- index (and any references to that index). So this could break if a site +-- The following function changes the ID of an existing custom index +-- (and any references to that index) to the target ID; if no target ID +-- is supplied, then it adds 100 to the source ID. So this could break if a site -- has custom indexes at both 16 and 116, for example - but if that's the -- case anywhere, I'm throwing my hands up in surrender: -CREATE OR REPLACE FUNCTION config.bump_metabib_field(custom_id BIGINT) RETURNS INT AS $func$ +CREATE OR REPLACE FUNCTION config.modify_metabib_field(source INT, target INT) RETURNS INT AS $func$ DECLARE f_class TEXT; check_id INT; + target_id INT; BEGIN - SELECT field_class INTO f_class FROM config.metabib_field WHERE id = custom_id; + SELECT field_class INTO f_class FROM config.metabib_field WHERE id = source; IF NOT FOUND THEN RETURN 0; END IF; - SELECT id FROM config.metabib_field INTO check_id WHERE id = custom_id + 100; + IF target IS NULL THEN + target_id = source + 100; + ELSE + target_id = target; + END IF; + SELECT id FROM config.metabib_field INTO check_id WHERE id = target; IF FOUND THEN - RAISE NOTICE 'Cannot bump config.metabib_field.id from % to %; the target ID already exists.', custom_id, custom_id + 100; + RAISE NOTICE 'Cannot bump config.metabib_field.id from % to %; the target ID already exists.', source, target; RETURN 0; END IF; - UPDATE config.metabib_field SET id = id + 100 WHERE id = custom_id; - EXECUTE ' UPDATE metabib.' || f_class || '_field_entry SET field = field + 100 WHERE field = ' || custom_id; - UPDATE config.metabib_field_index_norm_map SET field = field + 100 WHERE field = custom_id; - UPDATE search.relevance_adjustment SET field = field + 100 WHERE field = custom_id; + UPDATE config.metabib_field SET id = target WHERE id = source; + EXECUTE ' UPDATE metabib.' || f_class || '_field_entry SET field = ' || target || ' WHERE field = ' || source; + UPDATE config.metabib_field_index_norm_map SET field = target WHERE field = source; + UPDATE search.relevance_adjustment SET field = target WHERE field = source; RETURN 1; END; $func$ LANGUAGE PLPGSQL; -- Now update those custom indexes -SELECT config.bump_metabib_field(id) FROM config.metabib_field WHERE id > 15 and id < 100; +SELECT config.modify_metabib_field(id, NULL) + FROM config.metabib_field + WHERE id > 15 AND id < 100 AND field_class || name <> 'subjectcomplete'; + +SELECT config.modify_metabib_field(id, 16) + FROM config.metabib_field + WHERE id <> 16 AND field_class || name = 'subjectcomplete'; -- And bump the config.metabib_field sequence to a minimum of 100 to avoid problems in the future SELECT setval('config.metabib_field_id_seq', GREATEST(100, (SELECT MAX(id) + 1 FROM config.metabib_field))); -- 2.11.0