Forward-port 2.11.2 Upgrade Script
[Evergreen.git] / Open-ILS / src / sql / Pg / version-upgrade / 2.11.1-2.11.2-upgrade-db.sql
1 --Upgrade Script for 2.11.1 to 2.11.2
2 \set eg_version '''2.11.2'''
3 BEGIN;
4 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.11.2', :eg_version);
5
6 SELECT evergreen.upgrade_deps_block_check('1004', :eg_version); 
7
8 CREATE OR REPLACE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
9 BEGIN
10     IF TG_OP = 'INSERT' THEN
11         INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
12         SELECT  NEW.id,
13                 NEW.target,
14                 NEW.hold_type,
15                 CASE
16                     WHEN NEW.hold_type = 'T'
17                         THEN NEW.target
18                     WHEN NEW.hold_type = 'I'
19                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
20                     WHEN NEW.hold_type = 'V'
21                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
22                     WHEN NEW.hold_type IN ('C','R','F')
23                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
24                     WHEN NEW.hold_type = 'M'
25                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
26                     WHEN NEW.hold_type = 'P'
27                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
28                 END AS bib_record;
29     ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
30         UPDATE  reporter.hold_request_record
31           SET   target = NEW.target,
32                 hold_type = NEW.hold_type,
33                 bib_record = CASE
34                     WHEN NEW.hold_type = 'T'
35                         THEN NEW.target
36                     WHEN NEW.hold_type = 'I'
37                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
38                     WHEN NEW.hold_type = 'V'
39                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
40                     WHEN NEW.hold_type IN ('C','R','F')
41                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
42                     WHEN NEW.hold_type = 'M'
43                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
44                     WHEN NEW.hold_type = 'P'
45                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
46                 END
47          WHERE  id = NEW.id;
48     END IF;
49     RETURN NEW;
50 END;
51 $$ LANGUAGE PLPGSQL;
52
53 TRUNCATE TABLE reporter.hold_request_record;
54  
55 INSERT INTO reporter.hold_request_record 
56 SELECT  id,
57         target,
58         hold_type,
59         CASE
60                 WHEN hold_type = 'T'
61                         THEN target
62                 WHEN hold_type = 'I'
63                         THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
64                 WHEN hold_type = 'V'
65                         THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
66                 WHEN hold_type IN ('C','R','F')
67                         THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
68                 WHEN hold_type = 'M'
69                         THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
70                 WHEN hold_type = 'P'
71                         THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
72         END AS bib_record
73   FROM  action.hold_request ahr;
74  
75 REINDEX TABLE reporter.hold_request_record;
76
77
78 ANALYZE reporter.hold_request_record;
79
80
81 SELECT evergreen.upgrade_deps_block_check('1005', :eg_version);
82
83 CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
84
85 COMMIT;