From ae1b7b964127a1b3ef682c51d9a89bc2b1c5c08e Mon Sep 17 00:00:00 2001
From: dbs <dbs@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Thu, 17 Feb 2011 03:27:53 +0000
Subject: [PATCH] Teach maintain_901 trigger to respect the "Use record ID for
 TCN" global flag

Rather than relying on the Perl layer to set the correct TCN on the way in,
do it as part of the in-database indexing to ensure that we have consistent
results no matter how the record gets into the database.


git-svn-id: svn://svn.open-ils.org/ILS/trunk@19458 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Pg/002.functions.config.sql  | 11 +++
 Open-ILS/src/sql/Pg/002.schema.config.sql     |  2 +-
 .../0488.function.maintain_901_tcn.sql        | 68 +++++++++++++++++++
 3 files changed, 80 insertions(+), 1 deletion(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/0488.function.maintain_901_tcn.sql

diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql
index 1a967db55d..ec2dbf268c 100644
--- a/Open-ILS/src/sql/Pg/002.functions.config.sql
+++ b/Open-ILS/src/sql/Pg/002.functions.config.sql
@@ -420,10 +420,21 @@ CREATE OR REPLACE FUNCTION oils_json_to_text( TEXT ) RETURNS TEXT AS $f$
 $f$ LANGUAGE PLPERLU;
 
 CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$
+DECLARE
+    use_id_for_tcn BOOLEAN;
 BEGIN
     -- Remove any existing 901 fields before we insert the authoritative one
     NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield\s*[^<>]*?\s*tag="901".+?</datafield>', '', 'g');
+
     IF TG_TABLE_SCHEMA = 'biblio' THEN
+        -- Set TCN value to record ID?
+        SELECT enabled FROM config.global_flag INTO use_id_for_tcn
+            WHERE name = 'cat.bib.use_id_for_tcn';
+
+        IF use_id_for_tcn = 't' THEN
+            NEW.tcn_value := NEW.id;
+        END IF;
+
         NEW.marc := REGEXP_REPLACE(
             NEW.marc,
             E'(</(?:[^:]*?:)?record>)',
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 839cf8bc32..6b0db4dbcb 100644
--- 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 ('0487'); -- miker (for tsbere)
+INSERT INTO config.upgrade_log (version) VALUES ('0488'); -- dbs
 
 CREATE TABLE config.bib_source (
 	id		SERIAL	PRIMARY KEY,
diff --git a/Open-ILS/src/sql/Pg/upgrade/0488.function.maintain_901_tcn.sql b/Open-ILS/src/sql/Pg/upgrade/0488.function.maintain_901_tcn.sql
new file mode 100644
index 0000000000..8301db8926
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0488.function.maintain_901_tcn.sql
@@ -0,0 +1,68 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0488'); -- dbs
+
+CREATE OR REPLACE FUNCTION maintain_901 () RETURNS TRIGGER AS $func$
+DECLARE
+    use_id_for_tcn BOOLEAN;
+BEGIN
+    -- Remove any existing 901 fields before we insert the authoritative one
+    NEW.marc := REGEXP_REPLACE(NEW.marc, E'<datafield\s*[^<>]*?\s*tag="901".+?</datafield>', '', 'g');
+
+    IF TG_TABLE_SCHEMA = 'biblio' THEN
+        -- Set TCN value to record ID?
+        SELECT enabled FROM config.global_flag INTO use_id_for_tcn
+            WHERE name = 'cat.bib.use_id_for_tcn';
+
+        IF use_id_for_tcn = 't' THEN
+            NEW.tcn_value := NEW.id;
+        END IF;
+
+        NEW.marc := REGEXP_REPLACE(
+            NEW.marc,
+            E'(</(?:[^:]*?:)?record>)',
+            E'<datafield tag="901" ind1=" " ind2=" ">' ||
+                '<subfield code="a">' || NEW.tcn_value || E'</subfield>' ||
+                '<subfield code="b">' || NEW.tcn_source || E'</subfield>' ||
+                '<subfield code="c">' || NEW.id || E'</subfield>' ||
+                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
+                CASE WHEN NEW.owner IS NOT NULL THEN '<subfield code="o">' || NEW.owner || E'</subfield>' ELSE '' END ||
+                CASE WHEN NEW.share_depth IS NOT NULL THEN '<subfield code="d">' || NEW.share_depth || E'</subfield>' ELSE '' END ||
+             E'</datafield>\\1'
+        );
+    ELSIF TG_TABLE_SCHEMA = 'authority' THEN
+        NEW.marc := REGEXP_REPLACE(
+            NEW.marc,
+            E'(</(?:[^:]*?:)?record>)',
+            E'<datafield tag="901" ind1=" " ind2=" ">' ||
+                '<subfield code="c">' || NEW.id || E'</subfield>' ||
+                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
+             E'</datafield>\\1'
+        );
+    ELSIF TG_TABLE_SCHEMA = 'serial' THEN
+        NEW.marc := REGEXP_REPLACE(
+            NEW.marc,
+            E'(</(?:[^:]*?:)?record>)',
+            E'<datafield tag="901" ind1=" " ind2=" ">' ||
+                '<subfield code="c">' || NEW.id || E'</subfield>' ||
+                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
+                '<subfield code="o">' || NEW.owning_lib || E'</subfield>' ||
+                CASE WHEN NEW.record IS NOT NULL THEN '<subfield code="r">' || NEW.record || E'</subfield>' ELSE '' END ||
+             E'</datafield>\\1'
+        );
+    ELSE
+        NEW.marc := REGEXP_REPLACE(
+            NEW.marc,
+            E'(</(?:[^:]*?:)?record>)',
+            E'<datafield tag="901" ind1=" " ind2=" ">' ||
+                '<subfield code="c">' || NEW.id || E'</subfield>' ||
+                '<subfield code="t">' || TG_TABLE_SCHEMA || E'</subfield>' ||
+             E'</datafield>\\1'
+        );
+    END IF;
+
+    RETURN NEW;
+END;
+$func$ LANGUAGE PLPGSQL;
+
+COMMIT;
-- 
2.43.2