...
The new feature was implemented to make it possible to browse call-numbers by their types. It was implemented by using references to call-number types and changing logic for calculating shelving orderĀ that is used for call-number sorting.
Instructions
Step 1
Release info:
Required: from Orchid to Poppy/CSP1/CSP2/CSP3
Not required: from Poppy to CSP1/CSP2/CSP3, from CSP1 to CSP2/CSP3, from CSP2 to CSP3
Run time:
Before upgrade
Precondition:
...
After upgrade, before reindex
Release info:
Requires upgrade to Poppy CSP3 before running scripts
Action:
Run script:
Code Block | ||||
---|---|---|---|---|
| ||||
-- Change search path for the script SET search_path = <tenant>_mod_inventory_storage; CREATE OR REPLACE FUNCTION dewey_call_number(call_number text) RETURNS text AS $$ DECLARE matches text[]; class_digits text; class_decimal text; cutter text; other text; BEGIN matches = regexp_match(call_number, '^(\d+)(\.\d+)? *\.?(?:([A-Z]\d{1,3}(?:[A-Z]+)?) *(.*)|(.*))$'); IF matches IS NULL THEN RETURN numerically_sortable(trim2null(call_number)); END IF; class_digits = matches[1]; class_decimal = matches[2]; cutter = matches[3]; other = numerically_sortable(trim2null(concat(trim2null(matches[4]), trim2null(matches[5])))); RETURN concat_ws(' ', concat(sortable_number(class_digits), class_decimal), cutter, other ); END; $$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT; CREATE OR REPLACE FUNCTION lc_nlm_call_number(call_number text) RETURNS text AS $$ DECLARE matches text[]; classification text; classLetters text; classDigits text; classDecimal text; everythingElse text; classSuffix text; cutter text; BEGIN call_number = upper(call_number); matches = regexp_match(call_number, '^(([A-Z]+) *(?:(\d+)(\.\d+)?)?)(.*)$'); IF matches IS NULL THEN RETURN trim(cutter_shelf_key(trim2null(call_number))); END IF; classification = trim(matches[1]); classLetters = trim(matches[2]); classDigits = trim(matches[3]); classDecimal = trim(matches[4]); everythingElse = matches[5]; IF classDigits IS NULL THEN RETURN NULL; END IF; IF length(everythingElse) > 0 THEN -- combining greedy and non-greedy: -- https://www.postgresql.org/docs/current/functions-matching.html#POSIX-MATCHING-RULES matches = regexp_match(everythingElse, '(?:(.*?)(\.?[A-Z]\d+|^\.[A-Z]| \.[A-Z])(.*)){1,1}'); IF matches IS NULL THEN classSuffix = trim2null(everythingElse); ELSE classSuffix = trim2null(matches[1]); cutter = trim(matches[2] || matches[3]); END IF; END IF; classSuffix = numerically_sortable(classSuffix); IF substr(classSuffix, 1, 1) BETWEEN 'A' AND 'Z' THEN classSuffix = '_' || classSuffix; END IF; cutter = cutter_shelf_key(cutter); return trim(concat_ws(' ', classLetters, concat(length(classDigits), classDigits, classDecimal), trim2null(classSuffix), trim(cutter) )); END; $$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT; CREATE OR REPLACE FUNCTION su_doc_call_number(call_number text) RETURNS text AS $$ DECLARE matches text[]; BEGIN matches = regexp_match(upper(call_number), '^([A-Z]+)\s*(\d+)(\.(?:[A-Z]+\d*|\d+))(/(?:[A-Z]+(?:\d+(?:-\d+)?)?|\d+(?:-\d+)?))?:?(.*)$'); IF matches IS NULL THEN RETURN su_doc_part(trim2null(upper(call_number))); END IF; RETURN concat_ws(' ', matches[1], su_doc_part(matches[2]), su_doc_part(matches[3]), su_doc_part(matches[4]), su_doc_part(matches[5])); END; $$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT; CREATE OR REPLACE FUNCTION sortable_number(n text) RETURNS text AS $$ DECLARE int_part text; BEGIN n = regexp_replace(n, '^0+', ''); int_part = split_part(n, '.', 1); RETURN concat(length(int_part), n); END; $$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT; CREATE OR REPLACE FUNCTION cutter_shelf_key(s text) RETURNS text AS $$ DECLARE chunk text; matches text[]; cutter text; suffix text; result text; BEGIN FOREACH chunk IN ARRAY regexp_split_to_array(s, '(?=\.?[A-Z][0-9])') LOOP matches = regexp_match(chunk, '([A-Z][0-9]+)(.*)'); IF matches IS NULL THEN -- before the first cutter IF result IS NULL THEN result = trim2null(numerically_sortable(chunk)); ELSE result = concat_ws(' ', result, trim2null(numerically_sortable(chunk))); END IF; ELSE cutter = matches[1]; suffix = trim2null(numerically_sortable(matches[2])); result = concat_ws(' ', result, cutter, suffix); END IF; END LOOP; RETURN result; END; $$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE STRICT; |
Step 2 (Option 1)
...
Release info:
Requires upgrade to Poppy CSP3 before running scripts
Run time:
After upgrade, before reindex
...
Tested on database with 9M rows in item table.
Execution time: TBD
Step 2 (Option 2)
...
Release info:
Requires upgrade to Poppy CSP3 before running scripts
Run time:
After upgrade, before reindex
...