From 637a5affd8dedba14e305e71fb3ec13d76815898 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Tue, 15 Jun 2010 18:57:12 +0000
Subject: [PATCH] support pre-indexing normalizers for facet value
 normalization (negative pos value in the field-norm map)

git-svn-id: svn://svn.open-ils.org/ILS/trunk@16721 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
 Open-ILS/src/sql/Pg/002.schema.config.sql          | 32 ++++++++--
 .../0309.schema.pre-index_vector-normalizers.sql   | 72 ++++++++++++++++++++++
 2 files changed, 100 insertions(+), 4 deletions(-)
 create mode 100644 Open-ILS/src/sql/Pg/upgrade/0309.schema.pre-index_vector-normalizers.sql

diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 0d1ebd629..593c25b0d 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 ('0308'); -- miker
+INSERT INTO config.upgrade_log (version) VALUES ('0309'); -- miker
 
 CREATE TABLE config.bib_source (
 	id		SERIAL	PRIMARY KEY,
@@ -750,12 +750,36 @@ DECLARE
     normalizer      RECORD;
     value           TEXT := '';
 BEGIN
+
+    value := NEW.value;
+
+    IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
+        FOR normalizer IN
+            SELECT  n.func AS func,
+                    n.param_count AS param_count,
+                    m.params AS params
+              FROM  config.index_normalizer n
+                    JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
+              WHERE field = NEW.field AND m.pos < 0
+              ORDER BY m.pos LOOP
+                EXECUTE 'SELECT ' || normalizer.func || '(' ||
+                    quote_literal( value ) ||
+                    CASE
+                        WHEN normalizer.param_count > 0
+                            THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
+                            ELSE ''
+                        END ||
+                    ')' INTO value;
+
+        END LOOP;
+
+        NEW.value := value;
+    END IF;
+
     IF NEW.index_vector = ''::tsvector THEN
         RETURN NEW;
     END IF;
 
-    value := NEW.value;
-
     IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
         FOR normalizer IN
             SELECT  n.func AS func,
@@ -763,7 +787,7 @@ BEGIN
                     m.params AS params
               FROM  config.index_normalizer n
                     JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
-              WHERE field = NEW.field
+              WHERE field = NEW.field AND m.pos >= 0
               ORDER BY m.pos LOOP
                 EXECUTE 'SELECT ' || normalizer.func || '(' ||
                     quote_literal( value ) ||
diff --git a/Open-ILS/src/sql/Pg/upgrade/0309.schema.pre-index_vector-normalizers.sql b/Open-ILS/src/sql/Pg/upgrade/0309.schema.pre-index_vector-normalizers.sql
new file mode 100644
index 000000000..174b9a6c0
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0309.schema.pre-index_vector-normalizers.sql
@@ -0,0 +1,72 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0309'); --miker
+
+CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
+DECLARE
+    normalizer      RECORD;
+    value           TEXT := '';
+BEGIN
+
+    value := NEW.value;
+
+    IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
+        FOR normalizer IN
+            SELECT  n.func AS func,
+                    n.param_count AS param_count,
+                    m.params AS params
+              FROM  config.index_normalizer n
+                    JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
+              WHERE field = NEW.field AND m.pos < 0
+              ORDER BY m.pos LOOP
+                EXECUTE 'SELECT ' || normalizer.func || '(' ||
+                    quote_literal( value ) ||
+                    CASE
+                        WHEN normalizer.param_count > 0
+                            THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
+                            ELSE ''
+                        END ||
+                    ')' INTO value;
+
+        END LOOP;
+
+        NEW.value := value;
+    END IF;
+
+    IF NEW.index_vector = ''::tsvector THEN
+        RETURN NEW;
+    END IF;
+
+    IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
+        FOR normalizer IN
+            SELECT  n.func AS func,
+                    n.param_count AS param_count,
+                    m.params AS params
+              FROM  config.index_normalizer n
+                    JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
+              WHERE field = NEW.field AND m.pos >= 0
+              ORDER BY m.pos LOOP
+                EXECUTE 'SELECT ' || normalizer.func || '(' ||
+                    quote_literal( value ) ||
+                    CASE
+                        WHEN normalizer.param_count > 0
+                            THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
+                            ELSE ''
+                        END ||
+                    ')' INTO value;
+
+        END LOOP;
+    END IF;
+
+    IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
+        NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
+    ELSE
+        NEW.index_vector = to_tsvector(TG_ARGV[0], value);
+    END IF;
+
+    RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+
+COMMIT;
+
-- 
2.11.0