3 INSERT INTO config.upgrade_log (version) VALUES ('0364'); -- dbs
5 CREATE TABLE asset.call_number_class (
6 id bigserial PRIMARY KEY,
8 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic'
11 INSERT INTO asset.call_number_class (name, normalizer) VALUES
12 ('Generic', 'asset.label_normalizer_generic'),
13 ('Dewey (DDC)', 'asset.label_normalizer_dewey'),
14 ('Library of Congress (LC)', 'asset.label_normalizer_lc')
17 ALTER TABLE auditor.asset_call_number_history ADD COLUMN label_class BIGINT;
18 ALTER TABLE auditor.asset_call_number_history ADD COLUMN label_sortkey TEXT;
19 ALTER TABLE asset.call_number ADD COLUMN label_class BIGINT DEFAULT 1 NOT NULL REFERENCES asset.call_number_class(id) DEFERRABLE INITIALLY DEFERRED;
20 ALTER TABLE asset.call_number ADD COLUMN label_sortkey TEXT;
21 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(label_sortkey);
23 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
27 sortkey := NEW.label_sortkey;
29 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
30 quote_literal( NEW.label ) || ')'
31 FROM asset.call_number_class acnc
32 WHERE acnc.id = NEW.label_class
35 NEW.label_sortkey = sortkey;
39 $func$ LANGUAGE PLPGSQL;
41 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
42 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
43 # thus could probably be considered a derived work, although nothing was
44 # directly copied - but to err on the safe side of providing attribution:
45 # Copyright (C) 2007 LibLime
46 # Licensed under the GPL v2 or later
51 # Converts the callnumber to uppercase
52 # Strips spaces from start and end of the call number
53 # Converts anything other than letters, digits, and periods into underscores
54 # Collapses multiple underscores into a single underscore
55 my $callnum = uc(shift);
58 $callnum =~ s/[^A-Z0-9_.]/_/g;
59 $callnum =~ s/_{2,}/_/g;
62 $func$ LANGUAGE PLPERLU;
64 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
65 # Derived from the Koha C4::ClassSortRoutine::Dewey module
66 # Copyright (C) 2007 LibLime
67 # Licensed under the GPL v2 or later
76 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
77 my @tokens = split /\.|\s+/, $init;
78 my $digit_group_count = 0;
79 for (my $i = 0; $i <= $#tokens; $i++) {
80 if ($tokens[$i] =~ /^\d+$/) {
82 if (2 == $digit_group_count) {
83 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
84 $tokens[$i] =~ tr/ /0/;
88 my $key = join("_", @tokens);
89 $key =~ s/[^\p{IsAlnum}_]//g;
93 $func$ LANGUAGE PLPERLU;
95 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
99 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
100 # The author hopes to upload it to CPAN some day, which would make our lives easier
101 use Library::CallNumber::LC;
103 my $callnum = Library::CallNumber::LC->new(shift);
104 return $callnum->normalize();
106 $func$ LANGUAGE PLPERLU;