From b70314d5a296aa0888679b30fce4bbcf8f7a5503 Mon Sep 17 00:00:00 2001 From: miker Date: Tue, 29 Mar 2011 17:53:49 +0000 Subject: [PATCH] Force facet values to Unicode NFC to reduce spurious non-matches git-svn-id: svn://svn.open-ils.org/ILS/trunk@19899 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.functions.config.sql | 5 ++++ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 11 +++++++++ .../Pg/upgrade/0505.schema.force_facets_to_NFC.sql | 24 ++++++++++++++++++++ 4 files changed, 41 insertions(+), 1 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0505.schema.force_facets_to_NFC.sql diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql index 47461e2..afd2b2b 100644 --- a/Open-ILS/src/sql/Pg/002.functions.config.sql +++ b/Open-ILS/src/sql/Pg/002.functions.config.sql @@ -482,6 +482,11 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; +CREATE OR REPLACE FUNCTION force_unicode_normal_form(string TEXT, form TEXT) RETURNS TEXT AS $func$ +use Unicode::Normalize 'normalize'; +return normalize($_[1],$_[0]); # reverse the params +$func$ LANGUAGE PLPERLU; + CREATE OR REPLACE FUNCTION maintain_control_numbers() RETURNS TRIGGER AS $func$ use strict; use MARC::Record; diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 72ce6c5..9fb53d7 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0504'); -- miker for tsbere +INSERT INTO config.upgrade_log (version) VALUES ('0505'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index eaa48e9..864ea65 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -135,6 +135,17 @@ 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 facet_force_nfc() RETURNS TRIGGER AS $$ +BEGIN + NEW.value := force_unicode_normal_form(NEW.value,'NFC'); + RETURN NEW; +END; +$$ LANUAGE PLPGSQL; + +CREATE TRIGGER facet_force_nfc_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE facet_force_nfc(); + CREATE TABLE metabib.record_attr ( id BIGINT PRIMARY KEY REFERENCES biblio.record_entry (id) ON DELETE CASCADE, attrs HSTORE NOT NULL DEFAULT ''::HSTORE diff --git a/Open-ILS/src/sql/Pg/upgrade/0505.schema.force_facets_to_NFC.sql b/Open-ILS/src/sql/Pg/upgrade/0505.schema.force_facets_to_NFC.sql new file mode 100644 index 0000000..57e7933 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0505.schema.force_facets_to_NFC.sql @@ -0,0 +1,24 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0505'); --miker + +CREATE OR REPLACE FUNCTION force_unicode_normal_form(string TEXT, form TEXT) RETURNS TEXT AS $func$ +use Unicode::Normalize 'normalize'; +return normalize($_[1],$_[0]); # reverse the params +$func$ LANGUAGE PLPERLU; + +UPDATE metabib.facet_entry SET value = force_unicode_normal_form(value,'NFC'); + +CREATE OR REPLACE FUNCTION facet_force_nfc() RETURNS TRIGGER AS $$ +BEGIN + NEW.value := force_unicode_normal_form(NEW.value,'NFC'); + RETURN NEW; +END; +$$ LANUAGE PLPGSQL; + +CREATE TRIGGER facet_force_nfc_tgr + BEFORE UPDATE OR INSERT ON metabib.facet_entry + FOR EACH ROW EXECUTE PROCEDURE facet_force_nfc(); + +COMMIT; + -- 1.7.2.5