don't break up title and author when ingesting acquisitions MARC

Note that this can be done more cleanly once oils_xpath groks
XPATH 2.0 functions, in particular string-join.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>

git-svn-id: svn://svn.open-ils.org/ILS/trunk@18574 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 32b31c1..3ceb7f0 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 ('0454'); -- dbs
+INSERT INTO config.upgrade_log (version) VALUES ('0455'); -- gmc
 
 CREATE TABLE config.bib_source (
        id              SERIAL  PRIMARY KEY,

diff --git a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql
index a2216a0..5dbe400 100644 (file)
--- a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql
+++ b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql
@@ -14777,53 +14777,64 @@ CREATE TABLE acq.claim_policy_action (
 
 CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $function$
 DECLARE
-    value       TEXT;
-    atype       TEXT;
-    prov        INT;
-    pos         INT;
-    adef        RECORD;
-    xpath_string    TEXT;
+       value           TEXT;
+       atype           TEXT;
+       prov            INT;
+       pos             INT;
+       adef            RECORD;
+       xpath_string    TEXT;
 BEGIN
-    FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
- 
-        SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
- 
-        IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
-            IF (atype = 'lineitem_provider_attr_definition') THEN
-                SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
-                CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
-            END IF;
- 
-            IF (atype = 'lineitem_provider_attr_definition') THEN
-                SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
-            ELSIF (atype = 'lineitem_marc_attr_definition') THEN
-                SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
-            ELSIF (atype = 'lineitem_generated_attr_definition') THEN
-                SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
-            END IF;
- 
+       FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
+
+               SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
+
+               IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
+                       IF (atype = 'lineitem_provider_attr_definition') THEN
+                               SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
+                               CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
+                       END IF;
+                       
+                       IF (atype = 'lineitem_provider_attr_definition') THEN
+                               SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
+                       ELSIF (atype = 'lineitem_marc_attr_definition') THEN
+                               SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
+                       ELSIF (atype = 'lineitem_generated_attr_definition') THEN
+                               SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
+                       END IF;
+
             xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,'');
- 
-            pos := 1;
- 
-            LOOP
-                SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
- 
-                IF (value IS NOT NULL AND value <> '') THEN
-                    INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
-                        VALUES (NEW.id, adef.id, atype, adef.code, value);
-                ELSE
-                    EXIT;
+
+            IF (adef.code = 'title' OR adef.code = 'author') THEN
+                -- title and author should not be split
+                -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use
+                -- string-join in the xpath and remove this special case
+                       SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
+                       IF (value IS NOT NULL AND value <> '') THEN
+                                   INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
+                                   VALUES (NEW.id, adef.id, atype, adef.code, value);
                 END IF;
- 
-                pos := pos + 1;
-            END LOOP;
- 
-        END IF;
- 
-    END LOOP;
- 
-    RETURN NULL;
+            ELSE
+                pos := 1;
+
+                LOOP
+                           SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
+
+                           IF (value IS NOT NULL AND value <> '') THEN
+                                   INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
+                                           VALUES (NEW.id, adef.id, atype, adef.code, value);
+                    ELSE
+                        EXIT;
+                               END IF;
+
+                    pos := pos + 1;
+                END LOOP;
+            END IF;
+
+               END IF;
+
+       END LOOP;
+
+       RETURN NULL;
 END;
 $function$ LANGUAGE PLPGSQL;
 

diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql
index bb08fbe..6184c01 100644 (file)
--- a/Open-ILS/src/sql/Pg/200.schema.acq.sql
+++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql
@@ -1073,20 +1073,31 @@ BEGIN
 
             xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,'');
 
-            pos := 1;
-
-            LOOP
-                       SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
-
+            IF (adef.code = 'title' OR adef.code = 'author') THEN
+                -- title and author should not be split
+                -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use
+                -- string-join in the xpath and remove this special case
+                       SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
                        IF (value IS NOT NULL AND value <> '') THEN
-                               INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
-                                       VALUES (NEW.id, adef.id, atype, adef.code, value);
-                ELSE
-                    EXIT;
-                           END IF;
-
-                pos := pos + 1;
-            END LOOP;
+                                   INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
+                                   VALUES (NEW.id, adef.id, atype, adef.code, value);
+                END IF;
+            ELSE
+                pos := 1;
+
+                LOOP
+                           SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
+
+                           IF (value IS NOT NULL AND value <> '') THEN
+                                   INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
+                                           VALUES (NEW.id, adef.id, atype, adef.code, value);
+                    ELSE
+                        EXIT;
+                               END IF;
+
+                    pos := pos + 1;
+                END LOOP;
+            END IF;
 
                END IF;
 

diff --git a/Open-ILS/src/sql/Pg/upgrade/0455.schema.acq-tweak-lineitem-attr-splitting.sql b/Open-ILS/src/sql/Pg/upgrade/0455.schema.acq-tweak-lineitem-attr-splitting.sql
new file mode 100644 (file)
index 0000000..ba34c3e
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0455.schema.acq-tweak-lineitem-attr-splitting.sql
@@ -0,0 +1,68 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0455'); -- gmc
+
+CREATE OR REPLACE FUNCTION public.ingest_acq_marc ( ) RETURNS TRIGGER AS $function$
+DECLARE
+       value           TEXT;
+       atype           TEXT;
+       prov            INT;
+       pos             INT;
+       adef            RECORD;
+       xpath_string    TEXT;
+BEGIN
+       FOR adef IN SELECT *,tableoid FROM acq.lineitem_attr_definition LOOP
+
+               SELECT relname::TEXT INTO atype FROM pg_class WHERE oid = adef.tableoid;
+
+               IF (atype NOT IN ('lineitem_usr_attr_definition','lineitem_local_attr_definition')) THEN
+                       IF (atype = 'lineitem_provider_attr_definition') THEN
+                               SELECT provider INTO prov FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
+                               CONTINUE WHEN NEW.provider IS NULL OR prov <> NEW.provider;
+                       END IF;
+                       
+                       IF (atype = 'lineitem_provider_attr_definition') THEN
+                               SELECT xpath INTO xpath_string FROM acq.lineitem_provider_attr_definition WHERE id = adef.id;
+                       ELSIF (atype = 'lineitem_marc_attr_definition') THEN
+                               SELECT xpath INTO xpath_string FROM acq.lineitem_marc_attr_definition WHERE id = adef.id;
+                       ELSIF (atype = 'lineitem_generated_attr_definition') THEN
+                               SELECT xpath INTO xpath_string FROM acq.lineitem_generated_attr_definition WHERE id = adef.id;
+                       END IF;
+
+            xpath_string := REGEXP_REPLACE(xpath_string,$re$//?text\(\)$$re$,'');
+
+            IF (adef.code = 'title' OR adef.code = 'author') THEN
+                -- title and author should not be split
+                -- FIXME: once oils_xpath can grok XPATH 2.0 functions, we can use
+                -- string-join in the xpath and remove this special case
+                       SELECT extract_acq_marc_field(id, xpath_string, adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
+                       IF (value IS NOT NULL AND value <> '') THEN
+                                   INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
+                                   VALUES (NEW.id, adef.id, atype, adef.code, value);
+                END IF;
+            ELSE
+                pos := 1;
+
+                LOOP
+                           SELECT extract_acq_marc_field(id, xpath_string || '[' || pos || ']', adef.remove) INTO value FROM acq.lineitem WHERE id = NEW.id;
+
+                           IF (value IS NOT NULL AND value <> '') THEN
+                                   INSERT INTO acq.lineitem_attr (lineitem, definition, attr_type, attr_name, attr_value)
+                                           VALUES (NEW.id, adef.id, atype, adef.code, value);
+                    ELSE
+                        EXIT;
+                               END IF;
+
+                    pos := pos + 1;
+                END LOOP;
+            END IF;
+
+               END IF;
+
+       END LOOP;
+
+       RETURN NULL;
+END;
+$function$ LANGUAGE PLPGSQL;
+
+COMMIT;