From ac2becec97871997bb5e4b94127b4222ca9f2e61 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 11 Feb 2016 07:52:49 -0800 Subject: [PATCH] JBAS-944 2.7 sql upgrade dupe holds cleanup 2.7 adds an index that prevents 2 captured, unfulfilled, uncanceled holds from targeting the same copy. Before the index is created, cancel any such dupe-copy holds that have expired on the shelf and reset any such holds that are otherwise unfulfilled (i.e. in transit or on the holds shelf). Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql | 60 +++++++++++++++++++++++++++ 1 file changed, 60 insertions(+) diff --git a/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql b/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql index ea0057387c..57b875741a 100644 --- a/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql +++ b/KCLS/sql/schema/deploy/2.5-to-2.7-upgrade.sql @@ -8095,6 +8095,66 @@ ALTER TABLE acq.acq_lineitem_history DROP CONSTRAINT IF EXISTS acq_lineitem_hist SELECT evergreen.upgrade_deps_block_check('0920', :eg_version); +-- Clean up holds that will prevent the index on +-- current_copy from being created. +-- TODO: discuss optoins with team + +-- Cancel holds that already expired on the shelf. +-- These should have been canceled (cleared) already. +WITH dupe_copies AS ( + SELECT count(*), current_copy from action.hold_request + WHERE + current_copy IS NOT NULL + AND capture_time IS NOT NULL + AND cancel_time IS NULL + AND fulfillment_time IS NULL group by 2 having count(*) > 1 +) +UPDATE action.hold_request +SET cancel_time = NOW(), cancel_cause = 2 +WHERE + current_copy IN (SELECT current_copy FROM dupe_copies) + AND capture_time IS NOT NULL + AND cancel_time IS NULL + AND fulfillment_time IS NULL + AND DATE(shelf_expire_time) < CURRENT_DATE; + +-- Print holds that are about to be reset... +WITH dupe_copies AS ( + SELECT count(*), current_copy from action.hold_request + WHERE + current_copy IS NOT NULL + AND capture_time IS NOT NULL + AND cancel_time IS NULL + AND fulfillment_time IS NULL group by 2 having count(*) > 1 +) +SELECT ahr.id, ahr.usr, ahr.current_copy, acp.barcode, acp.status, ahr.shelf_expire_time +FROM action.hold_request ahr + JOIN asset.copy acp ON (acp.id = ahr.current_copy) + JOIN dupe_copies dupes ON (dupes.current_copy = ahr.current_copy) +WHERE + capture_time IS NOT NULL + AND cancel_time IS NULL + AND fulfillment_time IS NULL +ORDER BY ahr.current_copy; + +-- Reset valid holds that point to the same current copy. +-- We have no way of knowing which hold has the copy in hand. +WITH dupe_copies AS ( + SELECT count(*), current_copy FROM action.hold_request + WHERE + current_copy IS NOT NULL + AND capture_time IS NOT NULL + AND cancel_time IS NULL + AND fulfillment_time IS NULL group by 2 having count(*) > 1 +) +UPDATE action.hold_request +SET current_copy = NULL, prev_check_time = NULL +WHERE + current_copy IN (SELECT current_copy FROM dupe_copies) + AND capture_time IS NOT NULL + AND cancel_time IS NULL + AND fulfillment_time IS NULL; + CREATE UNIQUE INDEX hold_request_capture_protect_idx ON action.hold_request (current_copy) WHERE current_copy IS NOT NULL -- sometimes null in old/bad data -- 2.11.0