From b1fa4aac8bcaf00761652f75868183a348b75314 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Tue, 13 Oct 2009 19:06:25 +0000
Subject: [PATCH] add a replacement oils_tsearch2() trigger function that will
use any configured in-db indexing normalizers
git-svn-id: svn://svn.open-ils.org/ILS/trunk@14402 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
Open-ILS/src/sql/Pg/002.schema.config.sql | 31 ++++++++++++++++++-
.../0034.schema.in-db-indexing-normalization.sql | 36 ++++++++++++++++++++++
2 files changed, 66 insertions(+), 1 deletion(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/0034.schema.in-db-indexing-normalization.sql
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index d6178e00b8..ec27259ec8 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -51,7 +51,7 @@ CREATE TABLE config.upgrade_log (
install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
-INSERT INTO config.upgrade_log (version) VALUES ('0033'); -- miker
+INSERT INTO config.upgrade_log (version) VALUES ('0034'); -- miker
CREATE TABLE config.bib_source (
id SERIAL PRIMARY KEY,
@@ -595,6 +595,35 @@ CREATE TABLE config.metabib_field_index_norm_map (
pos INT NOT NULL DEFAULT 0
);
+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,
+ 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
+ ORDER BY m.pos
+ LOOP
+ EXECUTE 'SELECT ' || normalizer.func || '(' || quote_literal( value ) || ',' || BTRIM(normalizer.params,'[]') || ')' 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;
diff --git a/Open-ILS/src/sql/Pg/upgrade/0034.schema.in-db-indexing-normalization.sql b/Open-ILS/src/sql/Pg/upgrade/0034.schema.in-db-indexing-normalization.sql
new file mode 100644
index 0000000000..e73b3f2ada
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0034.schema.in-db-indexing-normalization.sql
@@ -0,0 +1,36 @@
+BEGIN;
+
+INSERT INTO config.upgrade_log (version) VALUES ('0034'); -- 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,
+ 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
+ ORDER BY m.pos
+ LOOP
+ EXECUTE 'SELECT ' || normalizer.func || '(' || quote_literal( value ) || ',' || BTRIM(normalizer.params,'[]') || ')' 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