From 227cf7b9fc034c3a0f99b39ed5d4bae16b6f38d1 Mon Sep 17 00:00:00 2001
From: Jason Stephenson <jason@sigio.com>
Date: Wed, 17 Nov 2021 16:20:48 -0500
Subject: [PATCH] More XPath fixes to fixup

---
 Open-ILS/src/sql/Pg/011.schema.authority.sql       |  6 +-
 ...unctions.lp1937294-support-newer-postgresql.sql | 84 ++++++++++++++++++++++
 2 files changed, 87 insertions(+), 3 deletions(-)

diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql
index fa61fbbfe3..e7d9166129 100644
--- a/Open-ILS/src/sql/Pg/011.schema.authority.sql
+++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql
@@ -341,10 +341,10 @@ BEGIN
         first_sf := TRUE;
 
         FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
-            FOR sf_node IN SELECT unnest(oils_xpath('./*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
+            FOR sf_node IN SELECT unnest(oils_xpath('//*/*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
 
                 tmp_text := oils_xpath_string('.', sf_node);
-                sf := oils_xpath_string('./@code', sf_node);
+                sf := oils_xpath_string('//./@code', sf_node);
 
                 IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
 
@@ -353,7 +353,7 @@ BEGIN
                         COALESCE(
                             NULLIF(
                                 REGEXP_REPLACE(
-                                    oils_xpath_string('./@ind'||nfi_used, tag_node),
+                                    oils_xpath_string('//./@ind'||nfi_used, tag_node),
                                     $$\D+$$,
                                     '',
                                     'g'
diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.functions.lp1937294-support-newer-postgresql.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.functions.lp1937294-support-newer-postgresql.sql
index c82900ad89..3d5e42395b 100644
--- a/Open-ILS/src/sql/Pg/upgrade/XXXX.functions.lp1937294-support-newer-postgresql.sql
+++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.functions.lp1937294-support-newer-postgresql.sql
@@ -562,4 +562,88 @@ BEGIN
 END;
 $f$ STABLE LANGUAGE PLPGSQL;
 
+CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
+DECLARE
+    acsaf           authority.control_set_authority_field%ROWTYPE;
+    tag_used        TEXT;
+    nfi_used        TEXT;
+    sf              TEXT;
+    sf_node         TEXT;
+    tag_node        TEXT;
+    thes_code       TEXT;
+    cset            INT;
+    heading_text    TEXT;
+    tmp_text        TEXT;
+    first_sf        BOOL;
+    auth_id         INT DEFAULT COALESCE(NULLIF(oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', marcxml), ''), '0')::INT; 
+BEGIN
+    SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
+
+    IF cset IS NULL THEN
+        SELECT  control_set INTO cset
+          FROM  authority.control_set_authority_field
+          WHERE tag IN ( SELECT  UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marcxml::XML)::TEXT[]))
+          LIMIT 1;
+    END IF;
+
+    heading_text := '';
+    FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
+        tag_used := acsaf.tag;
+        nfi_used := acsaf.nfi;
+        first_sf := TRUE;
+
+        FOR tag_node IN SELECT unnest(oils_xpath('//*[@tag="'||tag_used||'"]',marcxml)) LOOP
+            FOR sf_node IN SELECT unnest(oils_xpath('//*/*[contains("'||acsaf.sf_list||'",@code)]',tag_node)) LOOP
+
+                tmp_text := oils_xpath_string('.', sf_node);
+                sf := oils_xpath_string('//./@code', sf_node);
+
+                IF first_sf AND tmp_text IS NOT NULL AND nfi_used IS NOT NULL THEN
+
+                    tmp_text := SUBSTRING(
+                        tmp_text FROM
+                        COALESCE(
+                            NULLIF(
+                                REGEXP_REPLACE(
+                                    oils_xpath_string('//./@ind'||nfi_used, tag_node),
+                                    $$\D+$$,
+                                    '',
+                                    'g'
+                                ),
+                                ''
+                            )::INT,
+                            0
+                        ) + 1
+                    );
+
+                END IF;
+
+                first_sf := FALSE;
+
+                IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
+                    heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
+                END IF;
+            END LOOP;
+
+            EXIT WHEN heading_text <> '';
+        END LOOP;
+
+        EXIT WHEN heading_text <> '';
+    END LOOP;
+
+    IF heading_text <> '' THEN
+        IF no_thesaurus IS TRUE THEN
+            heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
+        ELSE
+            thes_code := authority.extract_thesaurus(marcxml);
+            heading_text := tag_used || '_' || COALESCE(nfi_used,'-') || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
+        END IF;
+    ELSE
+        heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
+    END IF;
+
+    RETURN heading_text;
+END;
+$func$ LANGUAGE PLPGSQL STABLE STRICT;
+
 COMMIT;
-- 
2.11.0