From cb0daa7b946f5fd90628b8b270da1731822d9ca7 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Fri, 12 Aug 2011 11:35:16 -0400 Subject: [PATCH] For vandelay bib matching, make 020, 022, 024 tags case-insensitive For user convenience. Signed-off-by: Lebbeous Fogle-Weekley Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 25 ++++- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 4 + ...chema.vandelay.bib_match_isxn_caseless.sql | 96 +++++++++++++++++++ 3 files changed, 122 insertions(+), 3 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.vandelay.bib_match_isxn_caseless.sql diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index b2780a5f56..d6258bc6f4 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -549,7 +549,13 @@ BEGIN ARRAY_ACCUM(value) ) FROM ( - SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value + 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 @@ -625,14 +631,20 @@ DECLARE 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; @@ -651,8 +663,15 @@ BEGIN jrow := jrow || ' AND ' || my_alias || '.subfield = ''' || node.subfield || ''''; END IF; - jrow := jrow || ' AND (' || my_alias || '.value ' || op || - ' ANY(($1->''' || tagkey || ''')::TEXT[])))'; + 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 (' || diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index f8c3d1618f..c815d88d4e 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -237,6 +237,10 @@ CREATE INDEX metabib_full_rec_value_idx ON metabib.real_full_rec (substring(valu CREATE INDEX metabib_full_rec_value_tpo_index ON metabib.real_full_rec (substring(value,1,1024) text_pattern_ops); CREATE INDEX metabib_full_rec_record_idx ON metabib.real_full_rec (record); CREATE INDEX metabib_full_rec_index_vector_idx ON metabib.real_full_rec USING GIST (index_vector); +CREATE INDEX metabib_full_rec_isxn_caseless_idx + ON metabib.real_full_rec (LOWER(value)) + WHERE tag IN ('020', '022', '024'); + CREATE TRIGGER metabib_full_rec_fti_trigger BEFORE UPDATE OR INSERT ON metabib.real_full_rec 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 new file mode 100644 index 0000000000..d0a662f5c4 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.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('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; -- 2.43.2