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