From 6320dc8b23380faafa5e1fb621424cf695ef9174 Mon Sep 17 00:00:00 2001 From: Michele Morgan Date: Fri, 25 Feb 2022 15:48:01 -0500 Subject: [PATCH] LP#1482757: stamp upgrade script Signed-off-by: Michele Morgan --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- .../XXXX.function.biblio.extract_located_uris.sql | 163 --------------------- 2 files changed, 1 insertion(+), 164 deletions(-) delete mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 41636c7d65..745b986415 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -92,7 +92,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1310', :eg_version); -- Dyrcona/jboyer/sandbergja +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1311', :eg_version); -- miker/Dyrcona/jvwoolf/mmorgan CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, 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 deleted file mode 100644 index edcb152494..0000000000 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.biblio.extract_located_uris.sql +++ /dev/null @@ -1,163 +0,0 @@ -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; - 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