From 70e2231df33d758b564e1cb18bacd4d958953db6 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Sun, 25 Mar 2018 15:12:19 -0400 Subject: [PATCH] JBAS-2005 BC hold queue position uses 'rhrr' class Queue is now defined as holds which target the same bib record, not necessarily those that share a targetable copy. Signed-off-by: Bill Erickson --- .../schema/deploy/hold-queue-pos-materialized.sql | 31 ++++++++++++++++++ .../schema/revert/hold-queue-pos-materialized.sql | 38 ++++++++++++++++++++++ KCLS/sql/schema/sqitch.plan | 1 + .../schema/verify/hold-queue-pos-materialized.sql | 7 ++++ 4 files changed, 77 insertions(+) create mode 100644 KCLS/sql/schema/deploy/hold-queue-pos-materialized.sql create mode 100644 KCLS/sql/schema/revert/hold-queue-pos-materialized.sql create mode 100644 KCLS/sql/schema/verify/hold-queue-pos-materialized.sql diff --git a/KCLS/sql/schema/deploy/hold-queue-pos-materialized.sql b/KCLS/sql/schema/deploy/hold-queue-pos-materialized.sql new file mode 100644 index 0000000000..5a4eba3b18 --- /dev/null +++ b/KCLS/sql/schema/deploy/hold-queue-pos-materialized.sql @@ -0,0 +1,31 @@ +-- Deploy kcls-evergreen:hold-queue-pos-materialized to pg +-- requires: 2.10-to-2.12-upgrade + +BEGIN; + +DROP FUNCTION action.hold_request_queue_pos(bigint); + +CREATE OR REPLACE FUNCTION action.hold_request_queue_pos(hold_id BIGINT) +RETURNS BIGINT AS $$ + SELECT subq.queue_position FROM ( + WITH related_holds AS ( + SELECT + ahr.id AS hold_id, + COALESCE(ahr.cut_in_line, FALSE) AS cut_in_line, + ahr.request_time + FROM reporter.hold_request_record rhrr + JOIN reporter.hold_request_record rhrr_related + ON (rhrr_related.bib_record = rhrr.bib_record) + JOIN action.hold_request ahr ON (ahr.id = rhrr_related.id) + WHERE rhrr.id = $1 + AND ahr.cancel_time IS NULL + AND ahr.fulfillment_time IS NULL + ) SELECT ROW_NUMBER() OVER ( + ORDER BY cut_in_line DESC, request_time + ) AS queue_position, hold_id FROM related_holds + ) subq WHERE subq.hold_id = $1 +$$ LANGUAGE SQL; + +COMMIT; + + diff --git a/KCLS/sql/schema/revert/hold-queue-pos-materialized.sql b/KCLS/sql/schema/revert/hold-queue-pos-materialized.sql new file mode 100644 index 0000000000..6b883fb247 --- /dev/null +++ b/KCLS/sql/schema/revert/hold-queue-pos-materialized.sql @@ -0,0 +1,38 @@ +-- Revert kcls-evergreen:hold-queue-pos-materialized from pg + +BEGIN; + +DROP FUNCTION action.hold_request_queue_pos(bigint); + +CREATE OR REPLACE FUNCTION action.hold_request_queue_pos (hold_id BIGINT) + RETURNS INTEGER AS +$FUNK$ +DECLARE + related_id INTEGER; + queue_pos INTEGER DEFAULT 1; +BEGIN + --SET enable_seqscan = false; + FOR related_id IN SELECT id FROM ( + SELECT ahr.id, ahr.cut_in_line, ahr.request_time + FROM action.hold_request ahr + JOIN action.hold_copy_map ahcm1 ON (ahcm1.hold = ahr.id) + JOIN action.hold_copy_map ahcm2 + ON (ahcm2.target_copy = ahcm1.target_copy) + WHERE ahcm2.hold = hold_id + GROUP BY 1, 2, 3 + ORDER BY COALESCE (ahr.cut_in_line, FALSE) DESC, ahr.request_time + ) related_holds LOOP + IF related_id = hold_id THEN + --RESET enable_seqscan; + RETURN queue_pos; + END IF; + queue_pos = queue_pos + 1; + END LOOP; + --RESET enable_seqscan; + RETURN -1; -- No such hold +END; +$FUNK$ LANGUAGE PLPGSQL; + + + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 2c081f3c89..3d3e64ab28 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -62,3 +62,4 @@ at-purge-interval-data [at-purge-interval] 2017-10-23T15:31:23Z Bill Erickson,,, hold-queue-pos-func [at-purge-interval-data] 2018-03-11T16:31:04Z Bill Erickson,,, # Hold queue position function outreach-si-profile [at-purge-interval-data] 2018-02-06T15:18:44Z Bill Erickson,,, # New Outreach SI profile 2.10-to-2.12-upgrade [at-purge-interval-data] 2018-01-29T21:03:38Z Bill Erickson,,, # KCLS 2.10 to 2.12 SQL Upgrade +hold-queue-pos-materialized [2.10-to-2.12-upgrade] 2018-03-25T18:50:51Z Bill Erickson,,, # materialized hold queue posistions diff --git a/KCLS/sql/schema/verify/hold-queue-pos-materialized.sql b/KCLS/sql/schema/verify/hold-queue-pos-materialized.sql new file mode 100644 index 0000000000..2547cccbef --- /dev/null +++ b/KCLS/sql/schema/verify/hold-queue-pos-materialized.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:hold-queue-pos-materialized on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0