From fdacf5d6dbd075ae925b9c852b2316ef61c90f4c Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 17 Feb 2011 10:41:57 -0500 Subject: [PATCH] Add label sortkey and normalization thereof for sorting part labels --- Open-ILS/src/sql/Pg/010.schema.biblio.sql | 40 ++++++++++++++++++++++++++++--- 1 file changed, 37 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/sql/Pg/010.schema.biblio.sql b/Open-ILS/src/sql/Pg/010.schema.biblio.sql index ce208b2db8..71830b9b89 100644 --- a/Open-ILS/src/sql/Pg/010.schema.biblio.sql +++ b/Open-ILS/src/sql/Pg/010.schema.biblio.sql @@ -80,10 +80,44 @@ CREATE INDEX biblio_record_note_creator_idx ON biblio.record_note ( creator ); CREATE INDEX biblio_record_note_editor_idx ON biblio.record_note ( editor ); CREATE TABLE biblio.monograph_part ( - id SERIAL PRIMARY KEY, - record BIGINT NOT NULL REFERENCES biblio.record_entry (id), - label TEXT NOT NULL, + id SERIAL PRIMARY KEY, + record BIGINT NOT NULL REFERENCES biblio.record_entry (id), + label TEXT NOT NULL, + label_sortkey TEXT NOT NULL, CONSTRAINT record_label_unique UNIQUE (record,label) ); +CREATE OR REPLACE FUNCTION lpad_number_substrings( TEXT, TEXT, INT ) RETURNS TEXT AS $$ + my $string = shift; + my $pad = shift; + my $len = shift; + my $find = $len - 1; + + while ($string =~ /(?:^|\D)(\d{1,$find})(?:$|\D)/) { + my $padded = $1; + $padded = $pad x ($len - length($padded)) . $padded + $string =~ s/$1/$padded/sg; + } + + return $string; +$$ LANUGAGE PLPERLU; + +CREATE OR REPLACE FUNCTION biblio.normalize_biblio_monograph_part_sortkey () RETURNS TRIGGER AS $$ +BEGIN + NEW.label_sortkey := REGEXP_REPLACE( + lpad_number_substrings( + naco_normalize(NEW.label), + '0', + 10 + ), + E'\\s+', + '', + 'g' + ); + RETURN NEW +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER norm_sort_label BEFORE INSERT OR UPDATE ON biblio.monograph_part FOR EACH ROW EXECUTE PROCEDURE biblio.normalize_biblio_monograph_part_sortkey(); + COMMIT; -- 2.11.0