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 <mrylander@gmail.com>
Signed-off-by: Michele Morgan <mmorgan@noblenet.org>
Signed-off-by: Jason Stephenson <jason@sigio.com>
Signed-off-by: Michele Morgan <mmorgan@noblenet.org>

diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql
index 7cb4e8c..1231af4 100644 (file)
--- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql
+++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql
@@ -1410,16 +1410,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
@@ -1497,6 +1493,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;
@@ -1504,6 +1502,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 (file)
index 0000000..dacf178
--- /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;
+