From b5579dee0f48f43157ad995669e42a8bf2cc1c80 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Wed, 31 Aug 2011 12:13:33 -0400 Subject: [PATCH] Address LP#837517, Vandelay ISxN matching Signed-off-by: Mike Rylander Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 45 +++++--- ...613.schema.vandelay_isxn_normalization.sql | 107 ++++++++++++++++++ 3 files changed, 137 insertions(+), 17 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0613.schema.vandelay_isxn_normalization.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 1594725bf2..c6ec8b7f1c 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 ('0612', :eg_version); -- miker/senator +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0613', :eg_version); -- miker/senator CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 2d03b75590..4b6c52577b 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -541,7 +541,7 @@ $$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore( record_xml TEXT -) RETURNS HSTORE AS $$ +) RETURNS HSTORE AS $func$ BEGIN RETURN (SELECT HSTORE( @@ -549,19 +549,24 @@ BEGIN 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) + SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value + FROM (SELECT tag, + subfield, + CASE WHEN tag = '020' THEN -- caseless -- isbn + LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%') + WHEN tag = '022' THEN -- caseless -- issn + LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%') + WHEN tag = '024' THEN -- caseless -- upc (other) + LOWER(value || '%') + ELSE + value + END AS value + FROM vandelay.flatten_marc(record_xml)) x GROUP BY tag, subfield ORDER BY tag, subfield ) subquery ); END; -$$ LANGUAGE PLPGSQL; +$func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set( match_set_id INTEGER @@ -635,12 +640,6 @@ DECLARE 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 @@ -651,6 +650,20 @@ BEGIN END IF; END IF; + IF node.negate THEN + IF caseless THEN + op := 'NOT LIKE'; + ELSE + op := '<>'; + END IF; + ELSE + IF caseless THEN + op := 'LIKE'; + ELSE + op := '='; + END IF; + END IF; + my_alias := 'n' || node.id::TEXT; jrow := 'LEFT JOIN (SELECT *, ' || node.quality || diff --git a/Open-ILS/src/sql/Pg/upgrade/0613.schema.vandelay_isxn_normalization.sql b/Open-ILS/src/sql/Pg/upgrade/0613.schema.vandelay_isxn_normalization.sql new file mode 100644 index 0000000000..4fa42a7b7f --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0613.schema.vandelay_isxn_normalization.sql @@ -0,0 +1,107 @@ +-- Evergreen DB patch 0613.schema.vandelay_isxn_normalization.sql +-- +BEGIN; + + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('0613', :eg_version); + +CREATE OR REPLACE FUNCTION vandelay.flatten_marc_hstore( + record_xml TEXT +) RETURNS HSTORE AS $func$ +BEGIN + RETURN (SELECT + HSTORE( + ARRAY_ACCUM(tag || (COALESCE(subfield, ''))), + ARRAY_ACCUM(value) + ) + FROM ( + SELECT tag, subfield, ARRAY_ACCUM(value)::TEXT AS value + FROM (SELECT tag, + subfield, + CASE WHEN tag = '020' THEN -- caseless -- isbn + LOWER((REGEXP_MATCHES(value,$$^(\S{10,17})$$))[1] || '%') + WHEN tag = '022' THEN -- caseless -- issn + LOWER((REGEXP_MATCHES(value,$$^(\S{4}[- ]?\S{4})$$))[1] || '%') + WHEN tag = '024' THEN -- caseless -- upc (other) + LOWER(value || '%') + ELSE + value + END AS value + FROM vandelay.flatten_marc(record_xml)) x + GROUP BY tag, subfield ORDER BY tag, subfield + ) subquery + ); +END; +$func$ 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 + + 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; + + IF node.negate THEN + IF caseless THEN + op := 'NOT LIKE'; + ELSE + op := '<>'; + END IF; + ELSE + IF caseless THEN + op := 'LIKE'; + ELSE + op := '='; + 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