LP#1787274: Stamping upgrade script for no dupe transits
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 1133.schema.no-dupe-transits.sql
1
2 BEGIN;
3
4 SELECT evergreen.upgrade_deps_block_check('1133', :eg_version);
5
6 \qecho Applying a unique constraint to action.transit_copy.  This will
7 \qecho only effect newly created transits.  Admins are encouraged to manually 
8 \qecho remove any existing duplicate transits by applying values for cancel_time
9 \qecho or dest_recv_time, or by deleting the offending transits. Below is a
10 \qecho query to locate duplicate transits.  Note dupes may exist accross
11 \qecho parent (action.transit_copy) and child tables (action.hold_transit_copy,
12 \qecho action.reservation_transit_copy)
13 \qecho 
14 \qecho    WITH dupe_transits AS (
15 \qecho        SELECT COUNT(*), target_copy FROM action.transit_copy
16 \qecho        WHERE dest_recv_time IS NULL AND cancel_time IS NULL
17 \qecho        GROUP BY 2 HAVING COUNT(*) > 1
18 \qecho    ) SELECT atc.* 
19 \qecho        FROM dupe_transits
20 \qecho        JOIN action.transit_copy atc USING (target_copy)
21 \qecho        WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
22 \qecho
23
24 /* 
25 Unique indexes are not inherited by child tables, so they will not prevent
26 duplicate inserts on action.transit_copy and action.hold_transit_copy,
27 for example.  Use check constraints instead to enforce unique-per-copy
28 transits accross all transit types.
29 */
30
31 -- Create an index for speedy check constraint lookups.
32 CREATE INDEX active_transit_for_copy 
33     ON action.transit_copy (target_copy)
34     WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
35
36 -- Check for duplicate transits across all transit types
37 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique() 
38     RETURNS TRIGGER AS $func$
39 BEGIN
40     PERFORM * FROM action.transit_copy 
41         WHERE target_copy = NEW.target_copy 
42               AND dest_recv_time IS NULL 
43               AND cancel_time IS NULL;
44     IF FOUND THEN
45         RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
46     END IF;
47     RETURN NULL;
48 END;
49 $func$ LANGUAGE PLPGSQL STABLE;
50
51 -- Apply constraint to all transit tables
52 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
53     AFTER INSERT ON action.transit_copy
54     FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
55
56 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
57     AFTER INSERT ON action.hold_transit_copy
58     FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
59
60 CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check
61     AFTER INSERT ON action.reservation_transit_copy
62     FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
63
64 /*
65 -- UNDO
66 DROP TRIGGER transit_copy_is_unique_check ON action.transit_copy;
67 DROP TRIGGER hold_transit_copy_is_unique_check ON action.hold_transit_copy;
68 DROP TRIGGER reservation_transit_copy_is_unique_check ON action.reservation_transit_copy;
69 DROP INDEX action.active_transit_for_copy;
70 */
71
72 COMMIT;
73