From 9919e637427b096a3c890bcc53b6bfb07f9b7bd5 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 6 Feb 2017 16:37:18 -0500 Subject: [PATCH] JBAS-826 Revert custom hold counts part II Remove all traces of the custom holds-per-bib count code. Signed-off-by: Bill Erickson --- .../schema/deploy/revert-custom-hold-counts.sql | 21 +++ .../schema/revert/revert-custom-hold-counts.sql | 207 +++++++++++++++++++++ KCLS/sql/schema/sqitch.plan | 2 +- .../schema/verify/revert-custom-hold-counts.sql | 7 + .../perlmods/lib/OpenILS/Application/Circ/Holds.pm | 7 - 5 files changed, 236 insertions(+), 8 deletions(-) create mode 100644 KCLS/sql/schema/deploy/revert-custom-hold-counts.sql create mode 100644 KCLS/sql/schema/revert/revert-custom-hold-counts.sql create mode 100644 KCLS/sql/schema/verify/revert-custom-hold-counts.sql diff --git a/KCLS/sql/schema/deploy/revert-custom-hold-counts.sql b/KCLS/sql/schema/deploy/revert-custom-hold-counts.sql new file mode 100644 index 0000000000..327ca48e0a --- /dev/null +++ b/KCLS/sql/schema/deploy/revert-custom-hold-counts.sql @@ -0,0 +1,21 @@ +-- Deploy kcls-evergreen:revert-custom-hold-counts to pg +-- requires: backstage-exports-continued + +BEGIN; + +DROP TRIGGER insert_hold_row_trigger ON biblio.record_entry; + +DROP TRIGGER modify_hold_count_trigger ON action.hold_request; + +DROP FUNCTION action.insert_hold_row(); + +DROP FUNCTION action.modify_hold_count(); + +DROP FUNCTION public.temp_update_holds(BIGINT, TEXT); + +DROP FUNCTION action.get_hold_count(INTEGER); + +DROP TABLE action.hold_count; + + +COMMIT; diff --git a/KCLS/sql/schema/revert/revert-custom-hold-counts.sql b/KCLS/sql/schema/revert/revert-custom-hold-counts.sql new file mode 100644 index 0000000000..599147436a --- /dev/null +++ b/KCLS/sql/schema/revert/revert-custom-hold-counts.sql @@ -0,0 +1,207 @@ +-- Revert kcls-evergreen:revert-custom-hold-counts from pg + +BEGIN; + +CREATE TABLE action.hold_count ( + record_entry_id bigint NOT NULL, + holds_count integer +); + +CREATE FUNCTION action.get_hold_count(bib_record_id integer) RETURNS bigint + LANGUAGE plpgsql COST 500 + AS $$ +DECLARE + counter INT; + the_count INT; +BEGIN + SELECT holds_count INTO counter FROM action.hold_count WHERE action.hold_count.record_entry_id = bib_record_id; + + IF FOUND THEN + RETURN counter; + ELSE + SELECT count("ahr".id ) INTO the_count + FROM action.hold_request AS "ahr" + WHERE ( + ( + ( + ( "ahr".target IN (SELECT "acp".id AS "id" + FROM asset.copy AS "acp" + INNER JOIN asset.call_number AS "acn" + ON ( "acn".id = "acp".call_number ) + INNER JOIN biblio.record_entry AS "bre" + ON ( "bre".id = "acn".record AND "bre".id = bib_record_id ) ) + AND "ahr".hold_type IN ('C') + ) + ) + OR ( + ( "ahr".target IN (SELECT "acn".id AS "id" + FROM asset.call_number AS "acn" + INNER JOIN biblio.record_entry AS "bre" + ON ( "bre".id = "acn".record AND "bre".id = bib_record_id ) ) + AND "ahr".hold_type = 'V' + ) + ) + OR ( + ( "ahr".target = bib_record_id AND "ahr".hold_type = 'T' ) + ) + ) + AND "ahr".cancel_time IS NULL + AND "ahr".fulfillment_time IS NULL + ); + RETURN the_count; + END IF; + +END; +$$; + +CREATE FUNCTION public.temp_update_holds(arg bigint, arg2 text) RETURNS integer + LANGUAGE plpgsql COST 500 + AS $$ +BEGIN + + UPDATE action.hold_count AS "main" + SET holds_count = (SELECT count("ahr".id ) AS "count" + FROM action.hold_request AS "ahr" + WHERE ( + ( + ( + ( "ahr".target IN (SELECT "acp".id AS "id" + FROM asset.copy AS "acp" + INNER JOIN asset.call_number AS "acn" + ON ( "acn".id = "acp".call_number ) + INNER JOIN biblio.record_entry AS "bre" + ON ( "bre".id = "acn".record AND "bre".id = "main".record_entry_id ) ) + AND "ahr".hold_type IN ('C') + ) + ) + OR ( + ( "ahr".target IN (SELECT "acn".id AS "id" + FROM asset.call_number AS "acn" + INNER JOIN biblio.record_entry AS "bre" + ON ( "bre".id = "acn".record AND "bre".id = "main".record_entry_id ) ) + AND "ahr".hold_type = 'V' + ) + ) + OR ( + ( "ahr".target = "main".record_entry_id AND "ahr".hold_type = 'T' ) + ) + ) + AND "ahr".cancel_time IS NULL + AND "ahr".fulfillment_time IS NULL + )) + WHERE + -- For hold type T (title) + (("main".record_entry_id = arg) AND (arg2 = 'T')) + OR + -- For hold type C (copy) + (("main".record_entry_id = (SELECT "acn".record + FROM asset.copy AS "acp" + INNER JOIN asset.call_number AS "acn" + ON ( "acn".id = "acp".call_number ) + WHERE "acp".id = new.target)) AND (arg2 = 'C')) + OR + -- For hold type V (volume) + (("main".record_entry_id = (SELECT "acn".record + FROM asset.call_number AS "acn" + WHERE "acn".id = arg) AND (arg2 = 'V')) + ); + RETURN 1; +END +$$; + + +CREATE FUNCTION action.modify_hold_count() RETURNS trigger + LANGUAGE plpgsql + AS $$ +-- on delete, decrement the hold_count +-- on insert, increment the hold count +-- treat the update as delete + insert +DECLARE + oldrecord BIGINT; + newrecord BIGINT; + newholdscount INTEGER; + hold_type TEXT; + oldksip BOOLEAN; + newskip BOOLEAN; + insert_update BOOLEAN; + update_delete BOOLEAN; +BEGIN + IF TG_OP = 'UPDATE' + AND NOT (NEW.cancel_time IS DISTINCT FROM OLD.cancel_time + OR NEW.fulfillment_time IS DISTINCT FROM OLD.fulfillment_time) + AND (NEW.target = OLD.target) + THEN + -- nothing to do if we don't update the properties that lead to the hold count changes + RETURN NULL; + END IF; + + -- skip if fulfillment_time or cancel_time is not null + insert_update = (TG_OP = 'INSERT' OR TG_OP = 'UPDATE' ) AND (NEW.fulfillment_time IS NULL AND NEW.cancel_time IS NULL); + update_delete = (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND (OLD.fulfillment_time IS NULL AND OLD.cancel_time IS NULL); + + IF TG_OP = 'DELETE' THEN + hold_type = OLD.hold_type; + ELSE + hold_type = NEW.hold_type; + END IF; + + CASE (hold_type) + WHEN 'T' THEN + IF insert_update THEN + newrecord = NEW.target; + END IF; + IF update_delete THEN + oldrecord = OLD.target; + END IF; + WHEN 'V' THEN + IF insert_update THEN + SELECT INTO newrecord acn.record FROM asset.call_number acn WHERE acn.id = NEW.target; + END IF; + IF update_delete THEN + SELECT INTO oldrecord acn.record FROM asset.call_number acn WHERE acn.id = OLD.target; + END IF; + WHEN 'C' THEN + IF insert_update THEN + SELECT INTO newrecord acn.record FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) WHERE acp.id = NEW.target; + END IF; + IF update_delete THEN + SELECT INTO oldrecord acn.record FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) WHERE acp.id = OLD.target; + END IF; + END CASE; + + IF insert_update + THEN +-- RAISE NOTICE 'RECORD ENTRY TO UPDATE (+1) % HOLD TYPE %', newrecord, hold_type; + UPDATE action.hold_count SET holds_count = holds_count + 1 WHERE record_entry_id = newrecord RETURNING holds_count INTO newholdscount; +-- RAISE NOTICE 'NEW HOLDS COUNT FOR %: %', newrecord, newholdscount; + END IF; + IF update_delete + THEN +-- RAISE NOTICE 'RECORD ENTRY TO UPDATE (-1) % HOLD TYPE %', oldrecord, hold_type; + UPDATE action.hold_count SET holds_count = holds_count - 1 WHERE record_entry_id = oldrecord RETURNING holds_count INTO newholdscount; +-- RAISE NOTICE 'NEW HOLDS COUNT FOR %: %', oldrecord, newholdscount; + END IF; + + RETURN NULL; + +END; +$$; + + +CREATE FUNCTION action.insert_hold_row() RETURNS trigger + LANGUAGE plpgsql COST 500 + AS $$ +BEGIN + INSERT INTO action.hold_count (record_entry_id, holds_count) values(new.id, 0); + RETURN NULL; +END +$$; + + +CREATE TRIGGER modify_hold_count_trigger AFTER INSERT OR DELETE OR UPDATE + ON action.hold_request FOR EACH ROW EXECUTE PROCEDURE action.modify_hold_count(); + +CREATE TRIGGER insert_hold_row_trigger AFTER INSERT + ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE action.insert_hold_row(); + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 7426d73847..3c516ce9c6 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -44,4 +44,4 @@ pg-9.6-prep [2.7-to-2.9-upgrade] 2017-03-20T15:59:10Z Bill Erickson,,, # Remove custom hold queue position func patron-co-history-stage-1 [sip-act-type-freegalsip] 2016-09-01T19:36:46Z Bill Erickson # Patron checkout history base tables and migration func 2.7-to-2.9-upgrade-part-2 [2.7-to-2.9-upgrade] 2016-12-12T15:19:07Z Bill Erickson # 2.7 to 2.9 Upgrade Secondary Updates -revert-custom-qstats [backstage-exports-continued] 2017-02-06T22:09:42Z Bill Erickson # Remove custom hold queue position func +revert-custom-hold-counts [backstage-exports-continued] 2017-02-06T21:33:32Z Bill Erickson # Remove custom hold count materialized data diff --git a/KCLS/sql/schema/verify/revert-custom-hold-counts.sql b/KCLS/sql/schema/verify/revert-custom-hold-counts.sql new file mode 100644 index 0000000000..9845c3bb6b --- /dev/null +++ b/KCLS/sql/schema/verify/revert-custom-hold-counts.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:revert-custom-hold-counts on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm index 2493890fd6..9eb82676c6 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Circ/Holds.pm @@ -4400,13 +4400,6 @@ sub rec_hold_count { return new_editor()->json_query($query)->[0]->{count}; } -# moved custom code into separate function to reduce merge conflicts -sub rec_hold_count_kcls { - my($self, $conn, $target_id) = @_; - my $result = new_editor()->json_query({from => ['action.get_hold_count', $target_id]})->[0]; - return [values %$result]->[0]; -} - # A helper function to calculate a hold's expiration time at a given # org_unit. Takes the org_unit as an argument and returns either the # hold expire time as an ISO8601 string or undef if there is no hold -- 2.11.0