From 60a20d54c5e690bb927a56f84b86c76d7181ce22 Mon Sep 17 00:00:00 2001 From: Michele Morgan Date: Wed, 29 Jul 2020 16:40:06 -0400 Subject: [PATCH] LP#1482757: Delete URIs and call numbers when all 856 fields are removed This patch assures that URIs and call numbers are deleted as appropriate when all 856 fields are removed from the MARC. Also provides for tracking editor and edit_date when call numbers are deleted. Signed-off-by: Michele Morgan Signed-off-by: Jason Stephenson Signed-off-by: Michele Morgan --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 14 +++++++++----- .../XXXX.function.biblio.extract_located_uris.sql | 16 ++++++++++------ 2 files changed, 19 insertions(+), 11 deletions(-) diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 232bf8857b..a5c806cecf 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1511,27 +1511,31 @@ BEGIN 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) + 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)) + 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 + 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 ( + 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)) + AND (NOT (id = ANY (current_map_owner_list)) + OR current_map_owner_list is NULL) ); RETURN; 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 0737a35047..2e82c2e2a4 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 @@ -81,7 +81,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 @@ -114,10 +114,11 @@ BEGIN 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) + 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)) + 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; @@ -128,13 +129,16 @@ BEGIN END IF; END LOOP; - DELETE FROM asset.call_number WHERE id IN ( - SELECT id + 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)) + AND (NOT (id = ANY (current_map_owner_list)) + OR current_map_owner_list is NULL) ); RETURN; -- 2.11.0