From 892a7bf054f511ddf3b9fd3fa855d8b500ce25c0 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 19 May 2017 15:33:03 -0400 Subject: [PATCH] LP#1482757: Be sure to remove all orphaned URI As reported by Michele Morgan, URIs can be orphaned in some cases, such as when a location drops one of several 856 entries. This commit tracks objects at the mapping level, to identify disused URIs even where the call number level is not changed. 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 | 44 ++++++++++-------- ...X.function.biblio.extract_located_uris.sql | 46 +++++++++++-------- 2 files changed, 51 insertions(+), 39 deletions(-) diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 1231af4fa3..8489999245 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1411,7 +1411,9 @@ DECLARE 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[]; orphaned_uri_list INT[]; BEGIN @@ -1491,9 +1493,11 @@ BEGIN 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_map_owner_list := current_map_owner_list || uri_cn_id + current_uri_map_list := current_uri_map_list || uri_map_id; + current_map_owner_list := current_map_owner_list || uri_cn_id; END LOOP; @@ -1504,30 +1508,32 @@ BEGIN -- Clear any orphaned URIs, URI mappings and call -- numbers for this bib that weren't mapped above. - FOR uri_cn_id IN + FOR current_map IN + SELECT m.id + FROM asset.uri_call_number_map m + JOIN asset.call_number cn ON (cn.id = m.call_number) + WHERE cn.record = bib_id + AND cn.label = '##URI##' + AND NOT cn.deleted + AND NOT (m.id = ANY (current_uri_map_list)) + 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; + + DELETE FROM asset.call_number 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)) - 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; 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 index dacf1786c6..6e5823c2d2 100644 --- 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 @@ -14,7 +14,9 @@ DECLARE 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[]; orphaned_uri_list INT[]; BEGIN @@ -94,9 +96,11 @@ BEGIN 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_map_owner_list := current_map_owner_list || uri_cn_id + current_uri_map_list := current_uri_map_list || uri_map_id; + current_map_owner_list := current_map_owner_list || uri_cn_id; END LOOP; @@ -107,30 +111,32 @@ BEGIN -- Clear any orphaned URIs, URI mappings and call -- numbers for this bib that weren't mapped above. - FOR uri_cn_id IN - SELECT id + FOR current_map IN + SELECT m.id + FROM asset.uri_call_number_map m + JOIN asset.call_number cn ON (cn.id = m.call_number) + WHERE cn.record = bib_id + AND cn.label = '##URI##' + AND NOT cn.deleted + AND NOT (m.id = ANY (current_uri_map_list)) + 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; + + DELETE FROM asset.call_number 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)) - 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; -- 2.43.2