From 4dfa5ff6681358958fa609d2729ab099dab96e68 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 5 Aug 2016 08:56:05 -0400 Subject: [PATCH] LP#1482757: More careful Located URI remapping Instead of recreating all Located URI mappings, we will add any new ones and remember all those (existing and new) that are in use. Any existing maps that we don't see in this record, and whose URI is only used once previously and therefore about to be orphaned, we remove. Signed-off-by: Mike Rylander Signed-off-by: Michele Morgan Signed-off-by: Jason Stephenson Signed-off-by: Michele Morgan --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 41 ++++- ...X.function.biblio.extract_located_uris.sql | 140 ++++++++++++++++++ 2 files changed, 173 insertions(+), 8 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_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 2cede48920..7e160a64b6 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1400,16 +1400,12 @@ DECLARE uri_id INT; uri_cn_id INT; uri_map_id INT; + current_uri INT; + uri_map_count INT; + current_map_owner_list INT[]; + orphaned_uri_list 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 @@ -1487,6 +1483,8 @@ BEGIN INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id); END IF; + current_map_owner_list := current_map_owner_list || uri_cn_id + END LOOP; END IF; @@ -1494,6 +1492,33 @@ BEGIN END LOOP; END IF; + -- Clear any orphaned URIs, URI mappings and call + -- numbers for this bib that weren't mapped above. + FOR uri_cn_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)) + LOOP + -- Check for URIs to-be-orphaned URIs + FOR current_uri IN + SELECT uri + FROM asset.uri_call_number_map + WHERE call_number = uri_cn_id + LOOP + SELECT COUNT(*) INTO uri_map_count FROM asset.uri_call_number_map WHERE uri = current_uri; + IF uri_map_count = 1 THEN -- only one means it's the last + orphaned_uri_list := orphaned_uri_list || current_uri; + END IF; + END LOOP; + -- Remove links + DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id; + DELETE FROM asset.call_number WHERE id = uri_cn_id; + DELETE FROM asset.uri WHERE id = ANY (orphaned_uri_list); + END LOOP; + RETURN; END; $func$ LANGUAGE PLPGSQL; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql new file mode 100644 index 0000000000..dacf1786c6 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql @@ -0,0 +1,140 @@ +BEGIN; + +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; + uri_map_count INT; + current_map_owner_list INT[]; + orphaned_uri_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 = uri_owner; + 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; + + 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 uri_cn_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)) + LOOP + -- Check for URIs to-be-orphaned URIs + FOR current_uri IN + SELECT uri + FROM asset.uri_call_number_map + WHERE call_number = uri_cn_id + LOOP + SELECT COUNT(*) INTO uri_map_count FROM asset.uri_call_number_map WHERE uri = current_uri; + IF uri_map_count = 1 THEN -- only one means it's the last + orphaned_uri_list := orphaned_uri_list || current_uri; + END IF; + END LOOP; + -- Remove links + DELETE FROM asset.uri_call_number_map WHERE call_number = uri_cn_id; + DELETE FROM asset.call_number WHERE id = uri_cn_id; + DELETE FROM asset.uri WHERE id = ANY (orphaned_uri_list); + END LOOP; + + RETURN; +END; +$func$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.43.2