From e35463f5591bff73eb23b984f3cc449e5922a889 Mon Sep 17 00:00:00 2001 From: Michele Morgan Date: Fri, 25 Feb 2022 15:17:29 -0500 Subject: [PATCH] LP#1482757: stamp upgrade script Signed-off-by: Michele Morgan --- .../1311.function.biblio.extract_located_uris.sql | 165 +++++++++++++++++++++ 1 file changed, 165 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/1311.function.biblio.extract_located_uris.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/1311.function.biblio.extract_located_uris.sql b/Open-ILS/src/sql/Pg/upgrade/1311.function.biblio.extract_located_uris.sql new file mode 100644 index 0000000000..35e1128408 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/1311.function.biblio.extract_located_uris.sql @@ -0,0 +1,165 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('1311', :eg_version); + +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; + current_uri INT; + current_map INT; + uri_map_count INT; + current_uri_map_list INT[]; + current_map_owner_list INT[]; + +BEGIN + + 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); + SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id; + END IF; + + current_uri_map_list := current_uri_map_list || uri_map_id; + current_map_owner_list := current_map_owner_list || uri_cn_id; + + END LOOP; + + END IF; + + END LOOP; + END IF; + + -- Clear any orphaned URIs, URI mappings and call + -- numbers for this bib that weren't mapped above. + FOR current_map IN + SELECT m.id + FROM asset.uri_call_number_map m + LEFT JOIN asset.call_number cn ON (cn.id = m.call_number) + WHERE cn.record = bib_id + AND cn.label = '##URI##' + AND (NOT (m.id = ANY (current_uri_map_list)) + OR current_uri_map_list is NULL) + LOOP + SELECT uri INTO current_uri FROM asset.uri_call_number_map WHERE id = current_map; + DELETE FROM asset.uri_call_number_map WHERE id = current_map; + + SELECT COUNT(*) INTO uri_map_count FROM asset.uri_call_number_map WHERE uri = current_uri; + IF uri_map_count = 0 THEN + DELETE FROM asset.uri WHERE id = current_uri; + END IF; + END LOOP; + + UPDATE asset.call_number + SET deleted = TRUE, edit_date = now(), editor = editor_id + WHERE id IN ( + SELECT id + FROM asset.call_number + WHERE record = bib_id + AND label = '##URI##' + AND NOT deleted + AND (NOT (id = ANY (current_map_owner_list)) + OR current_map_owner_list is NULL) + ); + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +-- Remove existing orphaned URIs from the database. +DELETE FROM asset.uri +WHERE id IN +( +SELECT uri.id +FROM asset.uri +LEFT JOIN asset.uri_call_number_map +ON uri_call_number_map.uri = uri.id +LEFT JOIN serial.item +ON item.uri = uri.id +WHERE uri_call_number_map IS NULL +AND item IS NULL +); + +COMMIT; + -- 2.11.0