From bdae9864b7992597a469fbbc8ef91fe7bf87b50b Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 17 Jan 2017 14:46:36 -0500 Subject: [PATCH] LP#1657237: Properly constrain matview trigger function The function maintaining the reporter.hold_request_record table was performing an unconstrained update when a hold was moved. This fixes that. To test: [1] Apply the patch, the perform an asset merge that would change the target of a hold request. Verify that reporter.hold_request_record is properly update. Signed-off-by: Mike Rylander Signed-off-by: Galen Charlton Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/reporter-schema.sql | 5 +- .../XXXX.function.hold-move-trigger-bug.sql | 49 +++++++++++++++++++ 2 files changed, 52 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.hold-move-trigger-bug.sql diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index dce360389a..985ca6fb47 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -284,7 +284,7 @@ CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_reques ALTER TABLE reporter.hold_request_record ADD PRIMARY KEY USING INDEX reporter_hold_request_record_pkey_idx; -CREATE FUNCTION reporter.hold_request_record_mapper () RETURNS TRIGGER AS $$ +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) @@ -322,7 +322,8 @@ BEGIN 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; + END + WHERE id = NEW.id; END IF; RETURN NEW; END; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.hold-move-trigger-bug.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.hold-move-trigger-bug.sql new file mode 100644 index 0000000000..919283649a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.hold-move-trigger-bug.sql @@ -0,0 +1,49 @@ +BEGIN; + +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; + +COMMIT; + -- 2.43.2