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;