From: Mike Rylander Date: Fri, 12 Aug 2011 19:06:01 +0000 (-0400) Subject: Stamped upgrade script for "For vandelay bib matching, make 020, 022, 024 tags case... X-Git-Tag: sprint4-merge-nov22~5263 X-Git-Url: https://git.evergreen-ils.org/?p=working%2FEvergreen.git;a=commitdiff_plain;h=809bf25bd57b35a3ba44429f243cae6c5b28bb0b Stamped upgrade script for "For vandelay bib matching, make 020, 022, 024 tags case-insensitive" 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 8276096a43..1ad0290e15 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -86,7 +86,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 ('0596', :eg_version); -- berick/senator +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0597', :eg_version); -- miker/senator CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/0597.schema.vandelay.bib_match_isxn_caseless.sql b/Open-ILS/src/sql/Pg/upgrade/0597.schema.vandelay.bib_match_isxn_caseless.sql new file mode 100644 index 0000000000..15d31c7084 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0597.schema.vandelay.bib_match_isxn_caseless.sql @@ -0,0 +1,96 @@ +-- Evergreen DB patch XXXX.schema.vandelay.bib_match_isxn_caseless.sql + +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0597', :eg_version); + +CREATE INDEX metabib_full_rec_isxn_caseless_idx + ON metabib.real_full_rec (LOWER(value)) + WHERE tag IN ('020', '022', '024'); + + +CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore( + record_xml TEXT +) RETURNS HSTORE AS $$ +BEGIN + RETURN (SELECT + HSTORE( + ARRAY_ACCUM(tag || (COALESCE(subfield, ''))), + ARRAY_ACCUM(value) + ) + FROM ( + SELECT + tag, subfield, + CASE WHEN tag IN ('020', '022', '024') THEN -- caseless + ARRAY_ACCUM(LOWER(value))::TEXT + ELSE + ARRAY_ACCUM(value)::TEXT + END AS value + FROM vandelay.flatten_marc(record_xml) + GROUP BY tag, subfield ORDER BY tag, subfield + ) subquery + ); +END; +$$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow( + node vandelay.match_set_point +) RETURNS VOID AS $$ +DECLARE + jrow TEXT; + my_alias TEXT; + op TEXT; + tagkey TEXT; + caseless BOOL; +BEGIN + -- remember $1 is tags_rstore, and $2 is svf_rstore + + IF node.negate THEN + op := '<>'; + ELSE + op := '='; + END IF; + + caseless := FALSE; + + IF node.tag IS NOT NULL THEN + caseless := (node.tag IN ('020', '022', '024')); + tagkey := node.tag; + IF node.subfield IS NOT NULL THEN + tagkey := tagkey || node.subfield; + END IF; + END IF; + + my_alias := 'n' || node.id::TEXT; + + jrow := 'LEFT JOIN (SELECT *, ' || node.quality || + ' AS quality FROM metabib.'; + IF node.tag IS NOT NULL THEN + jrow := jrow || 'full_rec) ' || my_alias || ' ON (' || + my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' || + node.tag || ''''; + IF node.subfield IS NOT NULL THEN + jrow := jrow || ' AND ' || my_alias || '.subfield = ''' || + node.subfield || ''''; + END IF; + jrow := jrow || ' AND ('; + + IF caseless THEN + jrow := jrow || 'LOWER(' || my_alias || '.value) ' || op; + ELSE + jrow := jrow || my_alias || '.value ' || op; + END IF; + + jrow := jrow || ' ANY(($1->''' || tagkey || ''')::TEXT[])))'; + ELSE -- svf + jrow := jrow || 'record_attr) ' || my_alias || ' ON (' || + my_alias || '.id = bre.id AND (' || + my_alias || '.attrs->''' || node.svf || + ''' ' || op || ' $2->''' || node.svf || '''))'; + END IF; + INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow); +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.vandelay.bib_match_isxn_caseless.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.vandelay.bib_match_isxn_caseless.sql deleted file mode 100644 index d0a662f5c4..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.vandelay.bib_match_isxn_caseless.sql +++ /dev/null @@ -1,96 +0,0 @@ --- Evergreen DB patch XXXX.schema.vandelay.bib_match_isxn_caseless.sql - -BEGIN; - --- check whether patch can be applied -SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); - -CREATE INDEX metabib_full_rec_isxn_caseless_idx - ON metabib.real_full_rec (LOWER(value)) - WHERE tag IN ('020', '022', '024'); - - -CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore( - record_xml TEXT -) RETURNS HSTORE AS $$ -BEGIN - RETURN (SELECT - HSTORE( - ARRAY_ACCUM(tag || (COALESCE(subfield, ''))), - ARRAY_ACCUM(value) - ) - FROM ( - SELECT - tag, subfield, - CASE WHEN tag IN ('020', '022', '024') THEN -- caseless - ARRAY_ACCUM(LOWER(value))::TEXT - ELSE - ARRAY_ACCUM(value)::TEXT - END AS value - FROM vandelay.flatten_marc(record_xml) - GROUP BY tag, subfield ORDER BY tag, subfield - ) subquery - ); -END; -$$ LANGUAGE PLPGSQL; - -CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow( - node vandelay.match_set_point -) RETURNS VOID AS $$ -DECLARE - jrow TEXT; - my_alias TEXT; - op TEXT; - tagkey TEXT; - caseless BOOL; -BEGIN - -- remember $1 is tags_rstore, and $2 is svf_rstore - - IF node.negate THEN - op := '<>'; - ELSE - op := '='; - END IF; - - caseless := FALSE; - - IF node.tag IS NOT NULL THEN - caseless := (node.tag IN ('020', '022', '024')); - tagkey := node.tag; - IF node.subfield IS NOT NULL THEN - tagkey := tagkey || node.subfield; - END IF; - END IF; - - my_alias := 'n' || node.id::TEXT; - - jrow := 'LEFT JOIN (SELECT *, ' || node.quality || - ' AS quality FROM metabib.'; - IF node.tag IS NOT NULL THEN - jrow := jrow || 'full_rec) ' || my_alias || ' ON (' || - my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' || - node.tag || ''''; - IF node.subfield IS NOT NULL THEN - jrow := jrow || ' AND ' || my_alias || '.subfield = ''' || - node.subfield || ''''; - END IF; - jrow := jrow || ' AND ('; - - IF caseless THEN - jrow := jrow || 'LOWER(' || my_alias || '.value) ' || op; - ELSE - jrow := jrow || my_alias || '.value ' || op; - END IF; - - jrow := jrow || ' ANY(($1->''' || tagkey || ''')::TEXT[])))'; - ELSE -- svf - jrow := jrow || 'record_attr) ' || my_alias || ' ON (' || - my_alias || '.id = bre.id AND (' || - my_alias || '.attrs->''' || node.svf || - ''' ' || op || ' $2->''' || node.svf || '''))'; - END IF; - INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow); -END; -$$ LANGUAGE PLPGSQL; - -COMMIT;