-- Evergreen DB patch 0819.schema.acn_dewey_normalizer.sql -- -- Fixes Dewey call number sorting (per LP# 1150939) -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0819', :eg_version); CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$ # Derived from the Koha C4::ClassSortRoutine::Dewey module # Copyright (C) 2007 LibLime # Licensed under the GPL v2 or later use strict; use warnings; my $init = uc(shift); $init =~ s/^\s+//; $init =~ s/\s+$//; $init =~ s!/!!g; $init =~ s/^([\p{IsAlpha}]+)/$1 /; my @tokens = split /\.|\s+/, $init; my $digit_group_count = 0; my $first_digit_group_idx; for (my $i = 0; $i <= $#tokens; $i++) { if ($tokens[$i] =~ /^\d+$/) { $digit_group_count++; if ($digit_group_count == 1) { $first_digit_group_idx = $i; } if (2 == $digit_group_count) { $tokens[$i] = sprintf("%-15.15s", $tokens[$i]); $tokens[$i] =~ tr/ /0/; } } } # Pad the first digit_group if there was only one if (1 == $digit_group_count) { $tokens[$first_digit_group_idx] .= '_000000000000000' } my $key = join("_", @tokens); $key =~ s/[^\p{IsAlnum}_]//g; return $key; $func$ LANGUAGE PLPERLU; -- regenerate sort keys for any dewey call numbers UPDATE asset.call_number SET id = id WHERE label_class = 2; COMMIT;