From 74f5aad1b82aeb3bf8799d3b4c79cd9530e6bf33 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 1 Jul 2011 11:50:25 -0400 Subject: [PATCH] Normalize facet values Spotted by Lebbeous Fogle-Weekley, facet values were not being normalized. This is due to changes in how facets are stored in 2.1+. This alternate implementation mirrors the way search_field index definitions work, which is a bit cleaner than inserting normalization into the field extraction process. Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 37 ++++++++++++++++ .../Pg/upgrade/0571.schema.facet_normalizer.sql | 51 ++++++++++++++++++++++ 2 files changed, 88 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0571.schema.facet_normalizer.sql diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 17a238332d..fb2c4cb7f4 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -135,6 +135,43 @@ CREATE INDEX metabib_facet_entry_field_idx ON metabib.facet_entry (field); CREATE INDEX metabib_facet_entry_value_idx ON metabib.facet_entry (SUBSTRING(value,1,1024)); CREATE INDEX metabib_facet_entry_source_idx ON metabib.facet_entry (source); +CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$ +DECLARE + normalizer RECORD; + facet_text TEXT; +BEGIN + facet_text := NEW.value; + + 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 m.field = NEW.field AND m.pos < 0 + ORDER BY m.pos LOOP + + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_literal( facet_text ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO facet_text; + + END LOOP; + + NEW.value = facet_text; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER facet_normalize_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE metabib.facet_normalize_trigger(); + CREATE OR REPLACE FUNCTION evergreen.facet_force_nfc() RETURNS TRIGGER AS $$ BEGIN NEW.value := force_unicode_normal_form(NEW.value,'NFC'); diff --git a/Open-ILS/src/sql/Pg/upgrade/0571.schema.facet_normalizer.sql b/Open-ILS/src/sql/Pg/upgrade/0571.schema.facet_normalizer.sql new file mode 100644 index 0000000000..d2966a1b4b --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0571.schema.facet_normalizer.sql @@ -0,0 +1,51 @@ +-- Evergreen DB patch 0571.schema.facet_normalizer.sql +-- +-- Alternate implementation of a regression fix for facet normalization +-- +BEGIN; + + +-- check whether patch can be applied +INSERT INTO config.upgrade_log (version) VALUES ('0571'); + +-- FIXME: add/check SQL statements to perform the upgrade +CREATE OR REPLACE FUNCTION metabib.facet_normalize_trigger () RETURNS TRIGGER AS $$ +DECLARE + normalizer RECORD; + facet_text TEXT; +BEGIN + facet_text := NEW.value; + + 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 m.field = NEW.field AND m.pos < 0 + ORDER BY m.pos LOOP + + EXECUTE 'SELECT ' || normalizer.func || '(' || + quote_literal( facet_text ) || + CASE + WHEN normalizer.param_count > 0 + THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'') + ELSE '' + END || + ')' INTO facet_text; + + END LOOP; + + NEW.value = facet_text; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER facet_normalize_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE metabib.facet_normalize_trigger(); + + + +COMMIT; -- 2.11.0