From 9cd336e470045c83619afe3ca9c173d4d95c9404 Mon Sep 17 00:00:00 2001
From: miker <miker@dcc99617-32d9-48b4-a31d-7c20da2025e4>
Date: Fri, 21 May 2010 15:24:39 +0000
Subject: [PATCH] use proper JSON, re ' vs "
git-svn-id: svn://svn.open-ils.org/ILS/trunk@16472 dcc99617-32d9-48b4-a31d-7c20da2025e4
---
Open-ILS/src/sql/Pg/002.schema.config.sql | 72 +++++++++++-----------
.../upgrade/0271.schema.ingest-normalizer-json.sql | 53 ++++++++++++++++
2 files changed, 89 insertions(+), 36 deletions(-)
create mode 100644 Open-ILS/src/sql/Pg/upgrade/0271.schema.ingest-normalizer-json.sql
diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql
index 970af1832d..919120040d 100644
--- a/Open-ILS/src/sql/Pg/002.schema.config.sql
+++ b/Open-ILS/src/sql/Pg/002.schema.config.sql
@@ -65,7 +65,7 @@ CREATE TABLE config.upgrade_log (
install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
-INSERT INTO config.upgrade_log (version) VALUES ('0270'); -- Scott McKellar
+INSERT INTO config.upgrade_log (version) VALUES ('0271'); -- miker
CREATE TABLE config.bib_source (
id SERIAL PRIMARY KEY,
@@ -737,43 +737,43 @@ CREATE TABLE config.metabib_field_index_norm_map (
CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
DECLARE
- normalizer RECORD;
- value TEXT := '';
+ normalizer RECORD;
+ value TEXT := '';
BEGIN
- 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,
- 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
- ORDER BY m.pos
- LOOP
- EXECUTE 'SELECT ' || normalizer.func || '(' ||
- quote_literal( value ) ||
- CASE
- WHEN normalizer.param_count > 0 THEN ',' || BTRIM(normalizer.params,'[]')
- 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;
-
+ 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,
+ 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
+ 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;
diff --git a/Open-ILS/src/sql/Pg/upgrade/0271.schema.ingest-normalizer-json.sql b/Open-ILS/src/sql/Pg/upgrade/0271.schema.ingest-normalizer-json.sql
new file mode 100644
index 0000000000..34c679c5f5
--- /dev/null
+++ b/Open-ILS/src/sql/Pg/upgrade/0271.schema.ingest-normalizer-json.sql
@@ -0,0 +1,53 @@
+BEGIN;
+
+-- Create new expression type for IS [NOT] NULL
+
+INSERT INTO config.upgrade_log (version) VALUES ('0271'); -- miker
+
+UPDATE config.metabib_field_index_norm_map
+ SET params = REPLACE(params,E'\'','"')
+ WHERE params IS NOT NULL AND params <> '';
+
+CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
+DECLARE
+ normalizer RECORD;
+ value TEXT := '';
+BEGIN
+ 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,
+ 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
+ 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