From f291618315c693416e75bf23638a720553463558 Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Thu, 19 Apr 2012 21:42:28 -0400 Subject: [PATCH] mike's new hold queue approximation Signed-off-by: Lebbeous Fogle-Weekley --- Open-ILS/src/sql/Pg/500.view.cross-schema.sql | 78 ++++++++++++++++++++- .../XXXX.schema.simplified-hold-pull-list.sql | 80 ++++++++++++++++++++++ 2 files changed, 157 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simplified-hold-pull-list.sql diff --git a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql index f49defc818..2162ddc525 100644 --- a/Open-ILS/src/sql/Pg/500.view.cross-schema.sql +++ b/Open-ILS/src/sql/Pg/500.view.cross-schema.sql @@ -60,6 +60,82 @@ CREATE TABLE config.idl_field_doc ( ); CREATE UNIQUE INDEX idl_field_doc_identity ON config.idl_field_doc (fm_class,field,owner); +-- Begin hold queue approximation (the first view is cross-schema and the +-- rest depends on that). -COMMIT; +-- All hold queue things in Evergreen have always been approximations. There +-- is no actual holds queue. That's not how Evergreen holds work. + +-- This approximation is faster than the one it aims to replace. It's Mike +-- Rylander's idea and mostly his implementation. + +-- redundant with reporter.hold_request_record for now +-- needs attention someday re M-type holds. +CREATE OR REPLACE VIEW action.hold_request_record AS +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; + +-- a mat-view, trigger maintained (when the target changes), that maps bibs to holds of any type +CREATE TABLE action.materialized_hold_record_map AS + SELECT id, bib_record FROM action.hold_request_record; +CREATE INDEX hr_pkey_idx ON action.materialized_hold_record_map (id); +CREATE INDEX hr_rec_idx ON action.materialized_hold_record_map (bib_record); + +CREATE OR REPLACE FUNCTION action.materialize_hold_record_map() +RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP = 'INSERT' OR ( + NEW.target <> OLD.target OR NEW.hold_type <> OLD.hold_type + ) THEN + DELETE FROM action.materialized_hold_record_map WHERE id = NEW.id; + INSERT INTO action.materialized_hold_record_map (id, bib_record) + SELECT id, bib_record + FROM action.hold_request_record + WHERE id = NEW.id; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; +CREATE TRIGGER materialize_hold_record_map + AFTER INSERT OR UPDATE + ON action.hold_request + FOR EACH ROW EXECUTE PROCEDURE action.materialize_hold_record_map(); + +-- To join this usefully, join where +-- this_hold = AND other_hold = +CREATE VIEW action.hold_queue_approximation AS +SELECT h1.id AS this_hold, + h2.id AS other_hold, + ROW_NUMBER() OVER ( + PARTITION BY h1.id + ORDER BY + COALESCE(h2.cut_in_line, FALSE) DESC, + h2.request_time + ) AS queue_position, + COUNT(*) OVER (PARTITION BY h1.id) AS total_holds + FROM action.hold_request h1 + JOIN action.materialized_hold_record_map r1 USING (id) + JOIN action.materialized_hold_record_map r2 USING (bib_record) + JOIN action.hold_request h2 ON (h2.id = r2.id) ; + +-- End hold queue approximation + +COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simplified-hold-pull-list.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simplified-hold-pull-list.sql new file mode 100644 index 0000000000..7c355dc73a --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.simplified-hold-pull-list.sql @@ -0,0 +1,80 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +-- Begin hold queue approximation + +-- All hold queue things in Evergreen have always been approximations. There +-- is no actual holds queue. That's not how Evergreen holds work. + +-- This approximation is faster than the one it aims to replace. It's Mike +-- Rylander's idea and more or less his implementation. + + +CREATE OR REPLACE VIEW action.hold_request_record AS +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; + +-- a mat-view, trigger maintained (when the target changes), that maps bibs to holds of any type +CREATE TABLE action.materialized_hold_record_map AS + SELECT id, bib_record FROM action.hold_request_record; +CREATE INDEX hr_pkey_idx ON action.materialized_hold_record_map (id); +CREATE INDEX hr_rec_idx ON action.materialized_hold_record_map (bib_record); + +CREATE OR REPLACE FUNCTION action.materialize_hold_record_map() +RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP = 'INSERT' OR ( + NEW.target <> OLD.target OR NEW.hold_type <> OLD.hold_type + ) THEN + DELETE FROM action.materialized_hold_record_map WHERE id = NEW.id; + INSERT INTO action.materialized_hold_record_map (id, bib_record) + SELECT id, bib_record + FROM action.hold_request_record + WHERE id = NEW.id; + END IF; + + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER materialize_hold_record_map + AFTER INSERT OR UPDATE + ON action.hold_request + FOR EACH ROW EXECUTE PROCEDURE action.materialize_hold_record_map(); + +-- To join this usefully, join where +-- this_hold = AND other_hold = +CREATE VIEW action.hold_queue_approximation AS +SELECT h1.id AS this_hold, + h2.id AS other_hold, + ROW_NUMBER() OVER ( + PARTITION BY h1.id + ORDER BY + COALESCE(h2.cut_in_line, FALSE) DESC, + h2.request_time + ) AS queue_position, + COUNT(*) OVER (PARTITION BY h1.id) AS total_holds + FROM action.hold_request h1 + JOIN action.materialized_hold_record_map r1 USING (id) + JOIN action.materialized_hold_record_map r2 USING (bib_record) + JOIN action.hold_request h2 ON (h2.id = r2.id) ; + +-- End hold queue approximation +COMMIT; -- 2.11.0