From 32affa540b8acb978df4f0f6342e929f9347dd01 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Sun, 11 Mar 2018 12:33:29 -0400 Subject: [PATCH] JBAS-1997 Hold queue position stored procedure Primarily created for use by BC, but could be used in other contexts. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/hold-queue-pos-func.sql | 40 ++++++++++++++++++++++++++ KCLS/sql/schema/revert/hold-queue-pos-func.sql | 9 ++++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/hold-queue-pos-func.sql | 7 +++++ 4 files changed, 57 insertions(+) create mode 100644 KCLS/sql/schema/deploy/hold-queue-pos-func.sql create mode 100644 KCLS/sql/schema/revert/hold-queue-pos-func.sql create mode 100644 KCLS/sql/schema/verify/hold-queue-pos-func.sql diff --git a/KCLS/sql/schema/deploy/hold-queue-pos-func.sql b/KCLS/sql/schema/deploy/hold-queue-pos-func.sql new file mode 100644 index 0000000000..d63e29b35a --- /dev/null +++ b/KCLS/sql/schema/deploy/hold-queue-pos-func.sql @@ -0,0 +1,40 @@ +-- Deploy kcls-evergreen:hold-queue-pos-func to pg +-- requires: at-purge-interval-data + +BEGIN; + +-- BC version would be something like: +-- SELECT $1 AS request_no, action.hold_request_queue_pos($1) AS bib_queue_ord; +-- OR +-- SELECT id AS request_no, action.hold_request_queue_pos(id) AS bib_queue_ord +-- FROM action.hold_request WHERE id IN ($1); + + + +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 + 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 + RETURN queue_pos; + END IF; + queue_pos = queue_pos + 1; + END LOOP; + RETURN -1; -- No such hold +END; +$FUNK$ LANGUAGE PLPGSQL; + +COMMIT; diff --git a/KCLS/sql/schema/revert/hold-queue-pos-func.sql b/KCLS/sql/schema/revert/hold-queue-pos-func.sql new file mode 100644 index 0000000000..513df47854 --- /dev/null +++ b/KCLS/sql/schema/revert/hold-queue-pos-func.sql @@ -0,0 +1,9 @@ +-- Revert kcls-evergreen:hold-queue-pos-func from pg + +BEGIN; + +-- XXX Add DDLs here. + +DROP FUNCTION IF EXISTS action.hold_request_queue_pos (hold_id BIGINT); + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 4077e91bd4..f3f574a9d5 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -59,4 +59,5 @@ browse-mattype-filter [2.9-to-2.10-upgrade] 2017-11-28T20:39:59Z Bill Erickson,, auth-prop-partial-matches [2.9-to-2.10-upgrade-reingest] 2017-09-29T15:47:04Z Bill Erickson,,, # Authority propagation partial matches at-purge-interval [2.9-to-2.10-upgrade-reingest] 2017-10-23T15:31:00Z Bill Erickson,,, # A/T Purge schema changes at-purge-interval-data [at-purge-interval] 2017-10-23T15:31:23Z Bill Erickson,,, # A/T Purge data cleanup +hold-queue-pos-func [at-purge-interval-data] 2018-03-11T16:31:04Z Bill Erickson,,, # Hold queue position function 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 diff --git a/KCLS/sql/schema/verify/hold-queue-pos-func.sql b/KCLS/sql/schema/verify/hold-queue-pos-func.sql new file mode 100644 index 0000000000..075a2b4915 --- /dev/null +++ b/KCLS/sql/schema/verify/hold-queue-pos-func.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:hold-queue-pos-func on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0