From 87bc5e52e2958a1904bebd43747c31a24951e981 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Mon, 17 Sep 2018 18:16:42 -0400 Subject: [PATCH] LP#1787274 Active copy transit unique constraint Adds constraint triggers to action.*transit_copy tables to prevent creation of new transits for a copies when an existing transit of any type exists for the copy with a NULL dest_recv_time and cancel_time values (i.e. an open transit). Signed-off-by: Bill Erickson Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/090.schema.action.sql | 25 ++++++++ Open-ILS/src/sql/Pg/095.schema.booking.sql | 4 ++ .../Pg/upgrade/XXXX.schema.no-dupe-transits.sql | 73 ++++++++++++++++++++++ 3 files changed, 102 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no-dupe-transits.sql diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index c8b252bc0d..b2d2e185ac 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -557,7 +557,28 @@ CREATE TABLE action.transit_copy ( CREATE INDEX active_transit_dest_idx ON "action".transit_copy (dest); CREATE INDEX active_transit_source_idx ON "action".transit_copy (source); CREATE INDEX active_transit_cp_idx ON "action".transit_copy (target_copy); +CREATE INDEX active_transit_for_copy ON action.transit_copy (target_copy) + WHERE dest_recv_time IS NULL AND cancel_time IS NULL; +-- Check for duplicate transits across all transit types +CREATE OR REPLACE FUNCTION action.copy_transit_is_unique() + RETURNS TRIGGER AS $func$ +BEGIN + PERFORM * FROM action.transit_copy + WHERE target_copy = NEW.target_copy + AND dest_recv_time IS NULL + AND cancel_time IS NULL; + + IF FOUND THEN + RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy; + END IF; + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL STABLE; + +CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check + AFTER INSERT ON action.transit_copy + FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); CREATE TABLE action.hold_transit_copy ( hold INT REFERENCES action.hold_request (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED @@ -569,6 +590,10 @@ CREATE INDEX active_hold_transit_source_idx ON "action".hold_transit_copy (sourc CREATE INDEX active_hold_transit_cp_idx ON "action".hold_transit_copy (target_copy); CREATE INDEX hold_transit_copy_hold_idx on action.hold_transit_copy (hold); +CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check + AFTER INSERT ON action.hold_transit_copy + FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); + CREATE TABLE action.unfulfilled_hold_list ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/095.schema.booking.sql b/Open-ILS/src/sql/Pg/095.schema.booking.sql index 453057be98..974f3b9bba 100644 --- a/Open-ILS/src/sql/Pg/095.schema.booking.sql +++ b/Open-ILS/src/sql/Pg/095.schema.booking.sql @@ -166,4 +166,8 @@ CREATE INDEX active_reservation_transit_dest_idx ON "action".reservation_transit CREATE INDEX active_reservation_transit_source_idx ON "action".reservation_transit_copy (source); CREATE INDEX active_reservation_transit_cp_idx ON "action".reservation_transit_copy (target_copy); +CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check + AFTER INSERT ON action.reservation_transit_copy + FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no-dupe-transits.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no-dupe-transits.sql new file mode 100644 index 0000000000..0016a42f0f --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.no-dupe-transits.sql @@ -0,0 +1,73 @@ + +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXX', :eg_version); + +\qecho Applying a unique constraint to action.transit_copy. This will +\qecho only effect newly created transits. Admins are encouraged to manually +\qecho remove any existing duplicate transits by applying values for cancel_time +\qecho or dest_recv_time, or by deleting the offending transits. Below is a +\qecho query to locate duplicate transits. Note dupes may exist accross +\qecho parent (action.transit_copy) and child tables (action.hold_transit_copy, +\qecho action.reservation_transit_copy) +\qecho +\qecho WITH dupe_transits AS ( +\qecho SELECT COUNT(*), target_copy FROM action.transit_copy +\qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL +\qecho GROUP BY 2 HAVING COUNT(*) > 1 +\qecho ) SELECT atc.* +\qecho FROM dupe_transits +\qecho JOIN action.transit_copy atc USING (target_copy) +\qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL; +\qecho + +/* +Unique indexes are not inherited by child tables, so they will not prevent +duplicate inserts on action.transit_copy and action.hold_transit_copy, +for example. Use check constraints instead to enforce unique-per-copy +transits accross all transit types. +*/ + +-- Create an index for speedy check constraint lookups. +CREATE INDEX active_transit_for_copy + ON action.transit_copy (target_copy) + WHERE dest_recv_time IS NULL AND cancel_time IS NULL; + +-- Check for duplicate transits across all transit types +CREATE OR REPLACE FUNCTION action.copy_transit_is_unique() + RETURNS TRIGGER AS $func$ +BEGIN + PERFORM * FROM action.transit_copy + WHERE target_copy = NEW.target_copy + AND dest_recv_time IS NULL + AND cancel_time IS NULL; + IF FOUND THEN + RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy; + END IF; + RETURN NULL; +END; +$func$ LANGUAGE PLPGSQL STABLE; + +-- Apply constraint to all transit tables +CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check + AFTER INSERT ON action.transit_copy + FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); + +CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check + AFTER INSERT ON action.hold_transit_copy + FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); + +CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check + AFTER INSERT ON action.reservation_transit_copy + FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique(); + +/* +-- UNDO +DROP TRIGGER transit_copy_is_unique_check ON action.transit_copy; +DROP TRIGGER hold_transit_copy_is_unique_check ON action.hold_transit_copy; +DROP TRIGGER reservation_transit_copy_is_unique_check ON action.reservation_transit_copy; +DROP INDEX action.active_transit_for_copy; +*/ + +COMMIT; + -- 2.11.0