From: Mike Rylander Date: Mon, 11 Mar 2013 19:41:00 +0000 (-0400) Subject: Stamping upgrade script for null SVF attribute value fix X-Git-Tag: sprint4-merge-nov22~3344 X-Git-Url: https://git.evergreen-ils.org/?p=working%2FEvergreen.git;a=commitdiff_plain;h=01529e4464381cfb1867083c32158f64afa9320e Stamping upgrade script for null SVF attribute value fix Signed-off-by: Mike Rylander --- diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index f7495018fa..b485fb7d4f 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -90,7 +90,7 @@ 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 ('0765', :eg_version); -- berick/kmlussier +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0766', :eg_version); -- gmc/miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/0766.function.handle_null_svf_during_import.sql b/Open-ILS/src/sql/Pg/upgrade/0766.function.handle_null_svf_during_import.sql new file mode 100644 index 0000000000..6b6398857d --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0766.function.handle_null_svf_during_import.sql @@ -0,0 +1,104 @@ +-- Evergreen DB patch XXXX.handle_null_svf_during_import.sql +-- +-- Prevent applying a normalization function to a null SVF +-- attribute value from breaking record import. +-- +BEGIN; + + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0766', :eg_version); + +CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$ +DECLARE + transformed_xml TEXT; + prev_xfrm TEXT; + normalizer RECORD; + xfrm config.xml_transform%ROWTYPE; + attr_value TEXT; + new_attrs HSTORE := ''::HSTORE; + attr_def config.record_attr_definition%ROWTYPE; +BEGIN + + FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP + + IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection + SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value + FROM vandelay.flatten_marc(xml) AS x + WHERE x.tag LIKE attr_def.tag + AND CASE + WHEN attr_def.sf_list IS NOT NULL + THEN POSITION(x.subfield IN attr_def.sf_list) > 0 + ELSE TRUE + END + GROUP BY x.tag + ORDER BY x.tag + LIMIT 1; + + ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field + attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field); + + ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression + + SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format; + + -- See if we can skip the XSLT ... it's expensive + IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN + -- Can't skip the transform + IF xfrm.xslt <> '---' THEN + transformed_xml := oils_xslt_process(xml,xfrm.xslt); + ELSE + transformed_xml := xml; + END IF; + + prev_xfrm := xfrm.name; + END IF; + + IF xfrm.name IS NULL THEN + -- just grab the marcxml (empty) transform + SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1; + prev_xfrm := xfrm.name; + END IF; + + attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]); + + ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map + SELECT m.value::TEXT INTO attr_value + FROM vandelay.marc21_physical_characteristics(xml) v + JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value) + WHERE v.subfield = attr_def.phys_char_sf + LIMIT 1; -- Just in case ... + + END IF; + + -- apply index normalizers to attr_value + FOR normalizer IN + SELECT n.func AS func, + n.param_count AS param_count, + m.params AS params + FROM config.index_normalizer n + JOIN config.record_attr_index_norm_map m ON (m.norm = n.id) + WHERE attr = attr_def.name + ORDER BY m.pos LOOP + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_nullable( attr_value ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO attr_value; + + END LOOP; + + -- Add the new value to the hstore + new_attrs := new_attrs || hstore( attr_def.name, attr_value ); + + END LOOP; + + RETURN new_attrs; +END; +$_$ LANGUAGE PLPGSQL; + + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.handle_null_svf_during_import.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.handle_null_svf_during_import.sql deleted file mode 100644 index 953d3b3642..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.handle_null_svf_during_import.sql +++ /dev/null @@ -1,104 +0,0 @@ --- Evergreen DB patch XXXX.handle_null_svf_during_import.sql --- --- Prevent applying a normalization function to a null SVF --- attribute value from breaking record import. --- -BEGIN; - - --- check whether patch can be applied -SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); - -CREATE OR REPLACE FUNCTION vandelay.extract_rec_attrs ( xml TEXT, attr_defs TEXT[]) RETURNS hstore AS $_$ -DECLARE - transformed_xml TEXT; - prev_xfrm TEXT; - normalizer RECORD; - xfrm config.xml_transform%ROWTYPE; - attr_value TEXT; - new_attrs HSTORE := ''::HSTORE; - attr_def config.record_attr_definition%ROWTYPE; -BEGIN - - FOR attr_def IN SELECT * FROM config.record_attr_definition WHERE name IN (SELECT * FROM UNNEST(attr_defs)) ORDER BY format LOOP - - IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection - SELECT ARRAY_TO_STRING(ARRAY_ACCUM(x.value), COALESCE(attr_def.joiner,' ')) INTO attr_value - FROM vandelay.flatten_marc(xml) AS x - WHERE x.tag LIKE attr_def.tag - AND CASE - WHEN attr_def.sf_list IS NOT NULL - THEN POSITION(x.subfield IN attr_def.sf_list) > 0 - ELSE TRUE - END - GROUP BY x.tag - ORDER BY x.tag - LIMIT 1; - - ELSIF attr_def.fixed_field IS NOT NULL THEN -- a named fixed field, see config.marc21_ff_pos_map.fixed_field - attr_value := vandelay.marc21_extract_fixed_field(xml, attr_def.fixed_field); - - ELSIF attr_def.xpath IS NOT NULL THEN -- and xpath expression - - SELECT INTO xfrm * FROM config.xml_transform WHERE name = attr_def.format; - - -- See if we can skip the XSLT ... it's expensive - IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN - -- Can't skip the transform - IF xfrm.xslt <> '---' THEN - transformed_xml := oils_xslt_process(xml,xfrm.xslt); - ELSE - transformed_xml := xml; - END IF; - - prev_xfrm := xfrm.name; - END IF; - - IF xfrm.name IS NULL THEN - -- just grab the marcxml (empty) transform - SELECT INTO xfrm * FROM config.xml_transform WHERE xslt = '---' LIMIT 1; - prev_xfrm := xfrm.name; - END IF; - - attr_value := oils_xpath_string(attr_def.xpath, transformed_xml, COALESCE(attr_def.joiner,' '), ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]); - - ELSIF attr_def.phys_char_sf IS NOT NULL THEN -- a named Physical Characteristic, see config.marc21_physical_characteristic_*_map - SELECT m.value::TEXT INTO attr_value - FROM vandelay.marc21_physical_characteristics(xml) v - JOIN config.marc21_physical_characteristic_value_map m ON (m.id = v.value) - WHERE v.subfield = attr_def.phys_char_sf - LIMIT 1; -- Just in case ... - - END IF; - - -- apply index normalizers to attr_value - FOR normalizer IN - SELECT n.func AS func, - n.param_count AS param_count, - m.params AS params - FROM config.index_normalizer n - JOIN config.record_attr_index_norm_map m ON (m.norm = n.id) - WHERE attr = attr_def.name - ORDER BY m.pos LOOP - EXECUTE 'SELECT ' || normalizer.func || '(' || - quote_nullable( attr_value ) || - CASE - WHEN normalizer.param_count > 0 - THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') - ELSE '' - END || - ')' INTO attr_value; - - END LOOP; - - -- Add the new value to the hstore - new_attrs := new_attrs || hstore( attr_def.name, attr_value ); - - END LOOP; - - RETURN new_attrs; -END; -$_$ LANGUAGE PLPGSQL; - - -COMMIT;