-- Evergreen DB patch 0658.schema.acn_dewey_sort_fix.sql -- -- Fixes Dewey call number sorting (per LP# 902667) -- BEGIN; -- check whether patch can be applied SELECT evergreen.upgrade_deps_block_check('0658', :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; for (my $i = 0; $i <= $#tokens; $i++) { if ($tokens[$i] =~ /^\d+$/) { $digit_group_count++; 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[0] .= '_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;