Support mulitiple subfield-9 values in a single 856 tag

git-svn-id: svn://svn.open-ils.org/ILS/trunk@20100 dcc99617-32d9-48b4-a31d-7c20da2025e4

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 4b6edac..d9951c5 100644 (file)
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log (
     install_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 );
 
-INSERT INTO config.upgrade_log (version) VALUES ('0516'); -- berick
+INSERT INTO config.upgrade_log (version) VALUES ('0517'); -- miker
 
 CREATE TABLE config.bib_source (
        id              SERIAL  PRIMARY KEY,

diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql
index ad41652..c4099a0 100644 (file)
--- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql
+++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql
@@ -810,6 +810,7 @@ DECLARE
     uri_label       TEXT;
     uri_href        TEXT;
     uri_use         TEXT;
+    uri_owner_list  TEXT[];
     uri_owner       TEXT;
     uri_owner_id    INT;
     uri_id          INT;
@@ -824,40 +825,54 @@ BEGIN
             uri_xml     := uris[i];
 
             uri_href    := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
-            CONTINUE WHEN uri_href IS NULL;
-
             uri_label   := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
-            CONTINUE WHEN uri_label IS NULL;
-
-            uri_owner   := (oils_xpath('//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',uri_xml))[1];
-            CONTINUE WHEN uri_owner IS NULL;
-
             uri_use     := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()|//*[@code="u"]/text()',uri_xml))[1];
-
-            uri_owner := REGEXP_REPLACE(uri_owner, $re$^.*?\((\w+)\).*$$re$, E'\\1');
-
-            SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
-            CONTINUE WHEN NOT FOUND;
-
-            -- now we look for a matching uri
-            SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
-            IF NOT FOUND THEN -- create one
-                INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
+            CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL;
+
+            -- Get the distinct list of libraries wanting to use 
+            SELECT  ARRAY_ACCUM(
+                        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
                 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;
+                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;
 
-            -- 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;
+                 FOR uri_owner IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
+                    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;
+    
+                END LOOP;
 
-            -- 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;
 
         END LOOP;

diff --git a/Open-ILS/src/sql/Pg/upgrade/0517.schema.multiple-9s-in-856.sql b/Open-ILS/src/sql/Pg/upgrade/0517.schema.multiple-9s-in-856.sql
new file mode 100644 (file)
index 0000000..e044794
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0517.schema.multiple-9s-in-856.sql
@@ -0,0 +1,85 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0517'); --miker
+
+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;
+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()|//*[@code="u"]/text()',uri_xml))[1];
+            uri_use     := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()|//*[@code="u"]/text()',uri_xml))[1];
+            CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL;
+
+            -- Get the distinct list of libraries wanting to use 
+            SELECT  ARRAY_ACCUM(
+                        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
+                SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
+                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;
+
+                 FOR uri_owner IN 1 .. ARRAY_UPPER(uri_owner_list, 1) LOOP
+                    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;
+
+                END LOOP;
+
+            END IF;
+
+        END LOOP;
+    END IF;
+
+    RETURN;
+END;
+$func$ LANGUAGE PLPGSQL;
+
+COMMIT;
+