From 59c773e00d7c94984918004c5efd6f08011ab239 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Tue, 30 Jun 2015 08:53:21 -0400 Subject: [PATCH] LP1464765: Speed Up Number Padding for Sort Keys [New commit message by Galen Charlton:] This patch was originally written to fix a problem with normalization of various labels, but the patches for bug 1155313 fixed the function problem. However, testing shows that this version of evergreen.lpad_number_substrings() is about a third faster, so this patch is being merged to get the speed improvement and the additional tests. Thanks to Thomas Berezansky for a much simplified approach. Signed-off-by: Jason Boyer Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/002.functions.config.sql | 7 +--- .../src/sql/Pg/t/lpad_number_substrings.pg | 39 ++++++++++--------- .../upgrade/XXXX.function.lpad_number_fix.sql | 27 +++++++++++++ 3 files changed, 48 insertions(+), 25 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.lpad_number_fix.sql diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql index 527e89bf3e..d52c6f9684 100644 --- a/Open-ILS/src/sql/Pg/002.functions.config.sql +++ b/Open-ILS/src/sql/Pg/002.functions.config.sql @@ -583,13 +583,8 @@ CREATE OR REPLACE FUNCTION evergreen.lpad_number_substrings( TEXT, TEXT, INT ) R my $string = shift; # Source string my $pad = shift; # string to fill. Typically '0'. This should be a single character. my $len = shift; # length of resultant padded field - my $find = $len - 1; - while ($string =~ /(^|\D)(\d{1,$find})($|\D)/) { - my $padded = $2; - $padded = $pad x ($len - length($padded)) . $padded; - $string = $` . $1 . $padded . $3 . $'; - } + $string =~ s/([0-9]+)/$pad x ($len - length($1)) . $1/eg; return $string; $$ LANGUAGE PLPERLU; diff --git a/Open-ILS/src/sql/Pg/t/lpad_number_substrings.pg b/Open-ILS/src/sql/Pg/t/lpad_number_substrings.pg index 3aafe13671..7a2697df06 100644 --- a/Open-ILS/src/sql/Pg/t/lpad_number_substrings.pg +++ b/Open-ILS/src/sql/Pg/t/lpad_number_substrings.pg @@ -1,23 +1,6 @@ - --- Turn off echo and keep things quiet. -\set ECHO -\set QUIET 1 - --- Format the output for nice TAP. -\pset format unaligned -\pset tuples_only true -\pset pager - --- Revert all changes on failure. -\set ON_ERROR_ROLLBACK 1 -\set ON_ERROR_STOP true -\set QUIET 1 - --- Load the TAP functions. +-- Start transaction and plan the tests. BEGIN; - --- Plan the tests. -SELECT plan(5); +SELECT plan(11); -- Run the tests. SELECT is(evergreen.lpad_number_substrings( @@ -34,8 +17,26 @@ SELECT is(evergreen.lpad_number_substrings( SELECT is(evergreen.lpad_number_substrings( 'Vol 5a-15','0',5), 'Vol 00005a-00015','Mixed format'); + +SELECT isnt(evergreen.lpad_number_substrings( + '2015 01 Jan','0',5),'02000015 00001 Jan','Matching Substring Error 1'); + +SELECT isnt(evergreen.lpad_number_substrings( + '2015 02 Feb','0',5),'00002015 00002 Feb','Matching Substring Error 2'); + +SELECT is(evergreen.lpad_number_substrings( + '2015 01 Jan','0',5),'02015 00001 Jan','Matching Substrings 1'); + +SELECT is(evergreen.lpad_number_substrings( + '2015 02 Feb','0',5),'02015 00002 Feb','Matching Substrings 2'); +SELECT is(evergreen.lpad_number_substrings( + '0001 001 1 01','0',5),'00001 00001 00001 00001','Matching Substrings 3'); + +SELECT is(evergreen.lpad_number_substrings( + '123456','0',5),'123456','Longer than Padding Length'); -- Finish the tests and clean up. SELECT * FROM finish(); ROLLBACK; + diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.lpad_number_fix.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.lpad_number_fix.sql new file mode 100644 index 0000000000..dc15874887 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.lpad_number_fix.sql @@ -0,0 +1,27 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION evergreen.lpad_number_substrings( TEXT, TEXT, INT ) RETURNS TEXT AS $$ + my $string = shift; # Source string + my $pad = shift; # string to fill. Typically '0'. This should be a single character. + my $len = shift; # length of resultant padded field + + $string =~ s/([0-9]+)/$pad x ($len - length($1)) . $1/eg; + + return $string; +$$ LANGUAGE PLPERLU; + + +-- Correct any potentially incorrectly padded sortkeys + +UPDATE biblio.monograph_part SET label = label; + +UPDATE asset.call_number_prefix SET label = label; + +-- asset.call_number.label_sortkey doesn't make use of this function + +UPDATE asset.call_number_suffix SET label = label; + +COMMIT; + -- 2.43.2