From d2561f3c7c0ca28aa6911fc816995974b5fad90e Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 30 Jan 2014 14:23:13 -0500 Subject: [PATCH] Convert the holdable_formats field on unfilled MR holds to the new style Signed-off-by: Mike Rylander --- .../ZZZZ.schema.convert-MR-holdable_formats.sql | 39 ++++++++++++++++++++++ 1 file changed, 39 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.convert-MR-holdable_formats.sql diff --git a/Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.convert-MR-holdable_formats.sql b/Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.convert-MR-holdable_formats.sql new file mode 100644 index 0000000000..b2e4028dc2 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/ZZZZ.schema.convert-MR-holdable_formats.sql @@ -0,0 +1,39 @@ + +BEGIN; + +-- First, explode the field into constituent parts +WITH format_parts_array AS ( + SELECT a.id, + STRING_TO_ARRAY(a.holdable_formats, '-') AS parts + FROM action.hold_request a + WHERE a.hold_type = 'M' + AND a.fulfillment_time IS NULL +), format_parts_wide AS ( + SELECT id, + regexp_split_to_array(parts[1], '') AS item_type, + regexp_split_to_array(parts[2], '') AS item_form, + parts[3] AS item_lang + FROM format_parts_array +), converted_formats_flat AS ( + SELECT id, + CASE WHEN ARRAY_LENGTH(item_type,1) > 0 + THEN '"0":[{"_attr":"item_type","_val":"' || ARRAY_TO_STRING(item_type,'"},{"_attr":"item_type","_val":"') || '"}]' + ELSE '"0":""' + END AS item_type, + CASE WHEN ARRAY_LENGTH(item_form,1) > 0 + THEN '"1":[{"_attr":"item_form","_val":"' || ARRAY_TO_STRING(item_form,'"},{"_attr":"item_form","_val":"') || '"}]' + ELSE '"1":""' + END AS item_form, + CASE WHEN item_lang <> '' + THEN '"2":[{"_attr":"item_lang","_val":"' || item_lang ||'"}]' + ELSE '"2":""' + END AS item_lang + FROM format_parts_wide +) UPDATE action.hold_request SET holdable_formats = '{' || + converted_formats_flat.item_type || ',' || + converted_formats_flat.item_form || ',' || + converted_formats_flat.item_lang || '}' + FROM converted_formats_flat WHERE converted_formats_flat.id = action.hold_request.id; + +COMMIT; + -- 2.11.0