From 6d621d264fab6aa3e49ecad1a11eb1858400810f 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 97d19ce19b..376dbd544a 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1501,27 +1501,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