From 859f11bb3acd63d286a3d9aa3e0dcefe29ba1eb8 Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Wed, 15 Feb 2017 17:25:49 -0500 Subject: [PATCH] Forward-port 2.11.2 Upgrade Script Signed-off-by: Dan Wells --- .../2.11.1-2.11.2-upgrade-db.sql | 85 +++++++++++++++++++ 1 file changed, 85 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/version-upgrade/2.11.1-2.11.2-upgrade-db.sql 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 index 0000000000..e7cadc4482 --- /dev/null +++ b/Open-ILS/src/sql/Pg/version-upgrade/2.11.1-2.11.2-upgrade-db.sql @@ -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; -- 2.43.2