Forward-port 2.11.2 Upgrade Script
authorDan Wells <dbw2@calvin.edu>
Wed, 15 Feb 2017 22:25:49 +0000 (17:25 -0500)
committerDan Wells <dbw2@calvin.edu>
Wed, 15 Feb 2017 22:25:49 +0000 (17:25 -0500)
Signed-off-by: Dan Wells <dbw2@calvin.edu>
Open-ILS/src/sql/Pg/version-upgrade/2.11.1-2.11.2-upgrade-db.sql [new file with mode: 0644]

diff --git a/Open-ILS/src/sql/Pg/version-upgrade/2.11.1-2.11.2-upgrade-db.sql b/Open-ILS/src/sql/Pg/version-upgrade/2.11.1-2.11.2-upgrade-db.sql
new file mode 100644 (file)
index 0000000..e7cadc4
--- /dev/null
@@ -0,0 +1,85 @@
+--Upgrade Script for 2.11.1 to 2.11.2
+\set eg_version '''2.11.2'''
+BEGIN;
+INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.11.2', :eg_version);
+
+SELECT evergreen.upgrade_deps_block_check('1004', :eg_version); 
+
+CREATE OR REPLACE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        INSERT INTO reporter.hold_request_record (id, target, hold_type, bib_record)
+        SELECT  NEW.id,
+                NEW.target,
+                NEW.hold_type,
+                CASE
+                    WHEN NEW.hold_type = 'T'
+                        THEN NEW.target
+                    WHEN NEW.hold_type = 'I'
+                        THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
+                    WHEN NEW.hold_type = 'V'
+                        THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
+                    WHEN NEW.hold_type IN ('C','R','F')
+                        THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
+                    WHEN NEW.hold_type = 'M'
+                        THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
+                    WHEN NEW.hold_type = 'P'
+                        THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
+                END AS bib_record;
+    ELSIF TG_OP = 'UPDATE' AND (OLD.target <> NEW.target OR OLD.hold_type <> NEW.hold_type) THEN
+        UPDATE  reporter.hold_request_record
+          SET   target = NEW.target,
+                hold_type = NEW.hold_type,
+                bib_record = CASE
+                    WHEN NEW.hold_type = 'T'
+                        THEN NEW.target
+                    WHEN NEW.hold_type = 'I'
+                        THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = NEW.target)
+                    WHEN NEW.hold_type = 'V'
+                        THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = NEW.target)
+                    WHEN NEW.hold_type IN ('C','R','F')
+                        THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = NEW.target)
+                    WHEN NEW.hold_type = 'M'
+                        THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = NEW.target)
+                    WHEN NEW.hold_type = 'P'
+                        THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = NEW.target)
+                END
+         WHERE  id = NEW.id;
+    END IF;
+    RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+
+TRUNCATE TABLE reporter.hold_request_record;
+INSERT INTO reporter.hold_request_record 
+SELECT  id,
+        target,
+        hold_type,
+        CASE
+                WHEN hold_type = 'T'
+                        THEN target
+                WHEN hold_type = 'I'
+                        THEN (SELECT ssub.record_entry FROM serial.subscription ssub JOIN serial.issuance si ON (si.subscription = ssub.id) WHERE si.id = ahr.target)
+                WHEN hold_type = 'V'
+                        THEN (SELECT cn.record FROM asset.call_number cn WHERE cn.id = ahr.target)
+                WHEN hold_type IN ('C','R','F')
+                        THEN (SELECT cn.record FROM asset.call_number cn JOIN asset.copy cp ON (cn.id = cp.call_number) WHERE cp.id = ahr.target)
+                WHEN hold_type = 'M'
+                        THEN (SELECT mr.master_record FROM metabib.metarecord mr WHERE mr.id = ahr.target)
+                WHEN hold_type = 'P'
+                        THEN (SELECT bmp.record FROM biblio.monograph_part bmp WHERE bmp.id = ahr.target)
+        END AS bib_record
+  FROM  action.hold_request ahr;
+REINDEX TABLE reporter.hold_request_record;
+
+
+ANALYZE reporter.hold_request_record;
+
+
+SELECT evergreen.upgrade_deps_block_check('1005', :eg_version);
+
+CREATE INDEX action_aged_circulation_parent_circ_idx ON action.aged_circulation (parent_circ);
+
+COMMIT;