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 <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 1231af4..8489999 100644 (file)
--- 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 dacf178..6e5823c 100644 (file)
--- 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;