From 77dc8e6dee1fc477e7d0b900ddfc9f0ef2997d14 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Fri, 13 Aug 2010 00:58:26 +0000
Subject: [PATCH] protect authority linking trigger against broken $0 values

git-svn-id: svn://svn.open-ils.org/ILS/trunk@17203 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Pg/002.schema.config.sql              |  2 +-
 Open-ILS/src/sql/Pg/030.schema.metabib.sql             |  3 ++-
 .../upgrade/0370.schema.protect-authority-linking.sql  | 18 ++++++++++++++++++
 3 files changed, 21 insertions(+), 2 deletions(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/0370.schema.protect-authority-linking.sql

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 17bb7fbf0c..09450547a5 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log (
     install_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
 );
 
-INSERT INTO config.upgrade_log (version) VALUES ('0369'); -- miker
+INSERT INTO config.upgrade_log (version) VALUES ('0370'); -- 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 fc17c38c3e..c787062425 100644
--- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql
+++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql
@@ -901,7 +901,8 @@ CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT
                 y.authority
           FROM (    SELECT  DISTINCT $1 AS bib,
                             BTRIM(remove_paren_substring(x))::BIGINT AS authority
-                      FROM  explode_array(oils_xpath('//*[@code="0"]/text()',$2)) x
+                      FROM  explode_array(oils_xpath('//*[@code="0"]/text()',$2)) x(txt)
+                      WHERE BTRIM(remove_paren_substring(x)) ~ $re$^\d+$$re$
                 ) y JOIN authority.record_entry r ON r.id = y.authority;
     SELECT $1;
 $func$ LANGUAGE SQL;
diff --git a/Open-ILS/src/sql/Pg/upgrade/0370.schema.protect-authority-linking.sql b/Open-ILS/src/sql/Pg/upgrade/0370.schema.protect-authority-linking.sql
new file mode 100644
index 0000000000..7109fbd8af
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0370.schema.protect-authority-linking.sql
@@ -0,0 +1,18 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0370');
+
+CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT) RETURNS BIGINT AS $func$
+    DELETE FROM authority.bib_linking WHERE bib = $1;
+    INSERT INTO authority.bib_linking (bib, authority)
+        SELECT  y.bib,
+                y.authority
+          FROM (    SELECT  DISTINCT $1 AS bib,
+                            BTRIM(remove_paren_substring(x))::BIGINT AS authority
+                      FROM  explode_array(oils_xpath('//*[@code="0"]/text()',$2)) x(txt)
+                      WHERE BTRIM(remove_paren_substring(x)) ~ $re$^\d+$$re$
+                ) y JOIN authority.record_entry r ON r.id = y.authority;
+    SELECT $1;
+$func$ LANGUAGE SQL;
+
+COMMIT;
-- 
2.11.0