From 631c3e9e3691c3a5176ee96d84dcb0a63e30d415 Mon Sep 17 00:00:00 2001 From: Jason Boyer Date: Thu, 12 Aug 2021 07:39:06 -0400 Subject: [PATCH] LP1813191: Dots Need Chaperones Regular expressions are fun. Signed-off-by: Jason Boyer Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/reporter-schema.sql | 8 ++++---- Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql | 9 +++++---- 2 files changed, 9 insertions(+), 8 deletions(-) diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index fb427ca16a..ec1b6c8737 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -235,20 +235,20 @@ $$ LANGUAGE SQL; CREATE OR REPLACE VIEW reporter.asset_call_number_dewey AS SELECT id AS call_number, call_number_dewey(label) AS dewey, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9.]$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) ELSE NULL::text END AS dewey_block_tens, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9]*$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) ELSE NULL::text END AS dewey_block_hundreds, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9]*$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN (btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) || '-'::text) || btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision) + 9::double precision, '000'::text)) ELSE NULL::text END AS dewey_range_tens, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9]*$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN (btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) || '-'::text) || btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision) + 99::double precision, '000'::text)) ELSE NULL::text diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql index e877290d36..a2a6a8c5ad 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.dewey_ranges.sql @@ -5,20 +5,20 @@ SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); CREATE OR REPLACE VIEW reporter.asset_call_number_dewey AS SELECT id AS call_number, call_number_dewey(label) AS dewey, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9.]$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) ELSE NULL::text END AS dewey_block_tens, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9]*$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) ELSE NULL::text END AS dewey_block_hundreds, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9]*$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN (btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision), '000'::text)) || '-'::text) || btrim(to_char(10::double precision * floor(call_number_dewey(label)::double precision / 10::double precision) + 9::double precision, '000'::text)) ELSE NULL::text END AS dewey_range_tens, - CASE WHEN call_number_dewey(label) ~ '^[0-9]+.?[0-9]*$'::text + CASE WHEN call_number_dewey(label) ~ '^[0-9]+\.?[0-9]*$'::text THEN (btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision), '000'::text)) || '-'::text) || btrim(to_char(100::double precision * floor(call_number_dewey(label)::double precision / 100::double precision) + 99::double precision, '000'::text)) ELSE NULL::text @@ -27,3 +27,4 @@ CREATE OR REPLACE VIEW reporter.asset_call_number_dewey AS WHERE call_number_dewey(label) ~ '^[0-9]'::text; COMMIT; + -- 2.11.0