From 90a14c48e275b32bdf088b98ad41707b464bd939 Mon Sep 17 00:00:00 2001 From: Rogan Hamby Date: Fri, 14 Sep 2018 10:48:02 -0400 Subject: [PATCH] LP#1722827: trim spaces from 856$9 when parsing locating URIs This patch ensures that any leading and trailing spaces and non-breaking-spaces in the 856$9 are ignored when extracting the org unit shortname. As a consequence, extraneous leading/trailing whitespace in the $9 will not prevent the located URI from being displayed in the appropriate places. Signed-off-by: Rogan Hamby Signed-off-by: Meg Stroup Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 2 +- ...lp1722827_trim_spaces_from_located_uris.pg | 35 ++++++ ....metabib.trim_spaces_from_located_uris.sql | 111 ++++++++++++++++++ 3 files changed, 147 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/t/lp1722827_trim_spaces_from_located_uris.pg create mode 100644 Open-ILS/src/sql/Pg/upgrade/xxxx.metabib.trim_spaces_from_located_uris.sql diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index dab8316f78..0c82fc89d9 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1470,7 +1470,7 @@ BEGIN FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP uri_owner := uri_owner_list[j]; - SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner; + SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = BTRIM(REPLACE(uri_owner,chr(160),'')); CONTINUE WHEN NOT FOUND; -- we need a call number to link through diff --git a/Open-ILS/src/sql/Pg/t/lp1722827_trim_spaces_from_located_uris.pg b/Open-ILS/src/sql/Pg/t/lp1722827_trim_spaces_from_located_uris.pg new file mode 100644 index 0000000000..11c2fb9774 --- /dev/null +++ b/Open-ILS/src/sql/Pg/t/lp1722827_trim_spaces_from_located_uris.pg @@ -0,0 +1,35 @@ +BEGIN; + +SELECT plan(4); + +SELECT is( + (SELECT COUNT(id) FROM asset.call_number WHERE record = 248 AND label = '##URI##')::INTEGER, + 0::INTEGER, + 'no URI added to bib yet so count should be 0' +); + +UPDATE biblio.record_entry SET marc = REPLACE(marc,'','http://evergreen-ils.orgCONS') WHERE id = 248; + +SELECT is( + (SELECT COUNT(id) FROM asset.call_number WHERE record = 248 AND deleted = FALSE AND label = '##URI##')::INTEGER, + 1::INTEGER, + 'normally valid URI added without spaces should have 1' +); + +UPDATE biblio.record_entry SET marc = REPLACE(marc,'CONS','' || chr(160) || 'CONS ') WHERE id = 248; + +SELECT is ( + (SELECT COUNT(id) FROM asset.call_number WHERE record = 248 AND deleted = FALSE AND label = '##URI##')::INTEGER, + 1::INTEGER, + 'part 1/2 test for URI creation with spaces, new one should exist despite spaces being added' +); + +SELECT is ( + (SELECT COUNT(id) FROM asset.call_number WHERE record = 248 AND deleted = TRUE AND label = '##URI##')::INTEGER, + 1::INTEGER, + 'part 2/2 test for URI creation with spaces, old URI tag should be deleted now' +); + +SELECT * FROM finish(); +ROLLBACK; + diff --git a/Open-ILS/src/sql/Pg/upgrade/xxxx.metabib.trim_spaces_from_located_uris.sql b/Open-ILS/src/sql/Pg/upgrade/xxxx.metabib.trim_spaces_from_located_uris.sql new file mode 100644 index 0000000000..8eb227b6ac --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/xxxx.metabib.trim_spaces_from_located_uris.sql @@ -0,0 +1,111 @@ +CREATE OR REPLACE FUNCTION biblio.extract_located_uris( bib_id BIGINT, marcxml TEXT, editor_id INT ) RETURNS VOID AS $func$ +DECLARE + uris TEXT[]; + uri_xml TEXT; + uri_label TEXT; + uri_href TEXT; + uri_use TEXT; + uri_owner_list TEXT[]; + uri_owner TEXT; + uri_owner_id INT; + uri_id INT; + uri_cn_id INT; + uri_map_id INT; +BEGIN + + -- Clear any URI mappings and call numbers for this bib. + -- This leads to acn / auricnm inflation, but also enables + -- old acn/auricnm's to go away and for bibs to be deleted. + FOR uri_cn_id IN SELECT id FROM asset.call_number WHERE record = bib_id AND label = '##URI##' AND NOT deleted LOOP + DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id; + DELETE FROM asset.call_number WHERE id = uri_cn_id; + END LOOP; + + uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',marcxml); + IF ARRAY_UPPER(uris,1) > 0 THEN + FOR i IN 1 .. ARRAY_UPPER(uris, 1) LOOP + -- First we pull info out of the 856 + uri_xml := uris[i]; + + uri_href := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1]; + uri_label := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()',uri_xml))[1]; + uri_use := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1]; + + IF uri_label IS NULL THEN + uri_label := uri_href; + END IF; + CONTINUE WHEN uri_href IS NULL; + + -- Get the distinct list of libraries wanting to use + SELECT ARRAY_AGG( + DISTINCT REGEXP_REPLACE( + x, + $re$^.*?\((\w+)\).*$$re$, + E'\\1' + ) + ) INTO uri_owner_list + FROM UNNEST( + oils_xpath( + '//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()', + uri_xml + ) + )x; + + IF ARRAY_UPPER(uri_owner_list,1) > 0 THEN + + -- look for a matching uri + IF uri_use IS NULL THEN + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active + ORDER BY id LIMIT 1; + IF NOT FOUND THEN -- create one + INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction IS NULL AND active; + END IF; + ELSE + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active + ORDER BY id LIMIT 1; + IF NOT FOUND THEN -- create one + INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use); + SELECT id INTO uri_id + FROM asset.uri + WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active; + END IF; + END IF; + + FOR j IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP + uri_owner := uri_owner_list[j]; + + SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = BTRIM(REPLACE(uri_owner,chr(160),'')); + CONTINUE WHEN NOT FOUND; + + -- we need a call number to link through + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + IF NOT FOUND THEN + INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label) + VALUES (uri_owner_id, bib_id, 'now', 'now', editor_id, editor_id, '##URI##'); + SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = bib_id AND label = '##URI##' AND NOT deleted; + END IF; + + -- now, link them if they're not already + SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id; + IF NOT FOUND THEN + INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id); + END IF; + + END LOOP; + + END IF; + + END LOOP; + END IF; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + -- 2.43.2