From 99d66d7c60897449721345e0a429350b6cbd7771 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 2 Mar 2016 13:57:42 -0500 Subject: [PATCH] LP#1549505: Precalculate full bib population in temp tables Signed-off-by: Mike Rylander --- .../Pg/upgrade/XXXX.schema.statisitcal-ratings.sql | 732 +++++++++++++++------ 1 file changed, 516 insertions(+), 216 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql index 232c06d0c6..2b3e1adfff 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql @@ -1,14 +1,59 @@ BEGIN; +-- Create these so that the queries in the UDFs will validate +CREATE TEMP TABLE precalc_filter_bib_list ( + id BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_bib_filter_bib_list ( + id BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_src_filter_bib_list ( + id BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_copy_filter_bib_list ( + id BIGINT, + copy BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ( + id BIGINT, + copy BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_location_filter_bib_list ( + id BIGINT, + copy BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_attr_filter_bib_list ( + id BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_bibs_by_copy_list ( + id BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_bibs_by_uri_list ( + id BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ( + id BIGINT +) ON COMMIT DROP; + +CREATE TEMP TABLE precalc_bib_list ( + id BIGINT +) ON COMMIT DROP; + -- rhrr needs to be a real table, so it can be fast. To that end, we use -- a materialized view updated via a trigger. DROP VIEW reporter.hold_request_record; -CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS - SELECT source AS id FROM metabib.record_attr_vector_list LIMIT 0; - CREATE TABLE reporter.hold_request_record AS SELECT id, target, @@ -150,7 +195,7 @@ CREATE TABLE rating.record_badge_score ( CREATE INDEX record_badge_score_badge_idx ON rating.record_badge_score (badge); CREATE INDEX record_badge_score_record_idx ON rating.record_badge_score (record); -CREATE OR REPLACE VIEW rating.generic_bib_population_global AS +CREATE OR REPLACE VIEW rating.badge_with_orgs AS WITH org_scope AS ( SELECT id, array_agg(tree) AS orgs @@ -160,96 +205,192 @@ CREATE OR REPLACE VIEW rating.generic_bib_population_global AS ) x GROUP BY 1 ) - SELECT DISTINCT ON (b.id) - b.id AS bib, - badge_data.id AS badge, + SELECT b.*, s.orgs - FROM biblio.record_entry b - JOIN rating.badge badge_data ON ( - badge_data.circ_mod_filter IS NULL - AND (badge_data.src_filter IS NULL OR b.source = badge_data.src_filter) - ) - JOIN org_scope s ON (badge_data.scope = s.id); + FROM rating.badge b + JOIN org_scope s ON (b.scope = s.id); -CREATE OR REPLACE VIEW rating.generic_bib_population_by_copy AS - WITH org_scope AS ( - SELECT id, - array_agg(tree) AS orgs - FROM (SELECT id, - (actor.org_unit_descendants(id)).id AS tree - FROM actor.org_unit - ) x - GROUP BY 1 - ) - SELECT DISTINCT ON (b.id) - b.id AS bib, - badge_data.id AS badge, - s.orgs - FROM biblio.record_entry b - JOIN asset.call_number cn ON (cn.record = b.id AND NOT cn.deleted) - JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted) - JOIN org_scope s ON (cn.owning_lib = ANY (s.orgs)) - JOIN rating.badge badge_data ON ( - badge_data.scope = s.id - AND (badge_data.src_filter IS NULL OR b.source = badge_data.src_filter) - AND (badge_data.circ_mod_filter IS NULL OR cp.circ_modifier = badge_data.circ_mod_filter) - ) - LEFT JOIN asset.copy_location_group_map lg ON (cp.location = lg.location) - WHERE (badge_data.loc_grp_filter IS NULL OR lg.lgroup = badge_data.loc_grp_filter); +CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; +BEGIN -CREATE OR REPLACE VIEW rating.generic_bib_population_by_uri AS - WITH org_scope AS ( - SELECT id, - array_agg(tree) AS orgs - FROM (SELECT id, - (actor.org_unit_descendants(id)).id AS tree - FROM actor.org_unit - ) x - GROUP BY 1 - ) - SELECT DISTINCT ON (b.id) - b.id AS bib, - badge_data.id AS badge, - s.orgs - FROM biblio.record_entry b - JOIN asset.call_number cn ON (cn.record = b.id AND cn.label = '##URI##' AND NOT cn.deleted) - JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id) - JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active) - JOIN org_scope s ON (cn.owning_lib = ANY (s.orgs)) - JOIN rating.badge badge_data ON ( - badge_data.scope = s.id - AND badge_data.circ_mod_filter IS NULL - AND (badge_data.src_filter IS NULL OR b.source = badge_data.src_filter) - ); + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_src_filter_bib_list; + IF src IS NOT NULL THEN + CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS + SELECT id FROM biblio.record_entry + WHERE source = src AND NOT deleted; + ELSE + CREATE TEMP TABLE precalc_src_filter_bib_list ON COMMIT DROP AS + SELECT id FROM biblio.record_entry + WHERE id > 0 AND NOT deleted; + END IF; -CREATE OR REPLACE VIEW rating.generic_bib_population_by_copy_or_uri AS - WITH org_scope AS ( - SELECT id, - array_agg(tree) AS orgs - FROM (SELECT id, - (actor.org_unit_descendants(id)).id AS tree - FROM actor.org_unit - ) x - GROUP BY 1 - ) - SELECT DISTINCT ON (b.id) - b.id AS bib, - badge_data.id AS badge, - s.orgs - FROM biblio.record_entry b - JOIN asset.call_number cn ON (cn.record = b.id AND NOT cn.deleted) - JOIN org_scope s ON (cn.owning_lib = ANY (s.orgs)) - JOIN rating.badge badge_data ON ( - badge_data.scope = s.id - AND (badge_data.src_filter IS NULL OR b.source = badge_data.src_filter) - ) - LEFT JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted) - LEFT JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id) - LEFT JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active) - LEFT JOIN asset.copy_location_group_map lg ON (cp.location = lg.location) - WHERE (badge_data.loc_grp_filter IS NULL OR lg.lgroup = badge_data.loc_grp_filter) - AND (uri.id IS NOT NULL OR cp.id IS NOT NULL) - AND (badge_data.circ_mod_filter IS NULL OR cp.circ_modifier = badge_data.circ_mod_filter); + SELECT count(*) INTO cnt FROM precalc_src_filter_bib_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION rating.precalc_circ_mod_filter(cm TEXT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; +BEGIN + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_circ_mod_filter_bib_list; + IF cm IS NOT NULL THEN + CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS + SELECT cn.record AS id, + cp.id AS copy + FROM asset.call_number cn + JOIN asset.copy cp ON (cn.id = cp.call_number) + WHERE cp.circ_modifier = cm + AND NOT cp.deleted; + ELSE + CREATE TEMP TABLE precalc_circ_mod_filter_bib_list ON COMMIT DROP AS + SELECT cn.record AS id, + cp.id AS copy + FROM asset.call_number cn + JOIN asset.copy cp ON (cn.id = cp.call_number) + WHERE NOT cp.deleted; + END IF; + + SELECT count(*) INTO cnt FROM precalc_circ_mod_filter_bib_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION rating.precalc_location_filter(loc INT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; +BEGIN + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_location_filter_bib_list; + IF loc IS NOT NULL THEN + CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS + SELECT cn.record AS id, + cp.id AS copy + FROM asset.call_number cn + JOIN asset.copy cp ON (cn.id = cp.call_number) + JOIN asset.copy_location_group_map lg ON (cp.location = lg.location) + WHERE cp.location = cm + AND lg.lgroup = loc + AND NOT cp.deleted; + ELSE + CREATE TEMP TABLE precalc_location_filter_bib_list ON COMMIT DROP AS + SELECT cn.record AS id, + cp.id AS copy + FROM asset.call_number cn + JOIN asset.copy cp ON (cn.id = cp.call_number) + WHERE NOT cp.deleted; + END IF; + + SELECT count(*) INTO cnt FROM precalc_location_filter_bib_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; + +-- all or limited... +CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; + afilter TEXT; +BEGIN + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_attr_filter_bib_list; + IF attr_filter IS NOT NULL THEN + afilter := metabib.compile_composite_attr(attr_filter); + CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS + SELECT source AS id FROM metabib.record_attr_vector_list + WHERE vlist @@ metabib.compile_composite_attr(attr_filter); + ELSE + CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS + SELECT source AS id FROM metabib.record_attr_vector_list; + END IF; + + SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy(badge_id INT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; + badge_row rating.badge_with_orgs%ROWTYPE; + base TEXT; + whr TEXT; +BEGIN + + SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id; + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bibs_by_copy_list; + CREATE TEMP TABLE precalc_bibs_by_copy_list ON COMMIT DROP AS + SELECT DISTINCT cn.record AS id + FROM asset.call_number cn + JOIN asset.copy cp ON (cp.call_number = cn.id AND NOT cp.deleted) + JOIN precalc_copy_filter_bib_list f ON (cp.id = f.copy) + WHERE cn.owning_lib = ANY (badge_row.orgs) + AND NOT cn.deleted; + + SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_uri(badge_id INT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; + badge_row rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge_row FROM rating.badge_with_orgs WHERE id = badge_id; + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bibs_by_uri_list; + CREATE TEMP TABLE precalc_bibs_by_uri_list ON COMMIT DROP AS + SELECT DISTINCT record AS id + FROM asset.call_number cn + JOIN asset.uri_call_number_map urim ON (urim.call_number = cn.id) + JOIN asset.uri uri ON (urim.uri = uri.id AND uri.active) + WHERE cn.owning_lib = ANY (badge_row.orgs) + AND cn.label = '##URI##' + AND NOT cn.deleted; + + SELECT count(*) INTO cnt FROM precalc_bibs_by_uri_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION rating.precalc_bibs_by_copy_or_uri(badge_id INT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; +BEGIN + + PERFORM rating.precalc_bibs_by_copy(badge_id); + PERFORM rating.precalc_bibs_by_uri(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bibs_by_uri_list; + CREATE TEMP TABLE precalc_bibs_by_copy_or_uri_list ON COMMIT DROP AS + SELECT id FROM precalc_bibs_by_copy_list + UNION + SELECT id FROM precalc_bibs_by_uri_list; + + SELECT count(*) INTO cnt FROM precalc_bibs_by_copy_uri_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT ) RETURNS VOID AS $f$ @@ -257,20 +398,48 @@ DECLARE badge_row rating.badge%ROWTYPE; param rating.popularity_parameter%ROWTYPE; BEGIN + SET LOCAL client_min_messages = error; -- Find what we're doing SELECT * INTO badge_row FROM rating.badge WHERE id = badge_id; SELECT * INTO param FROM rating.popularity_parameter WHERE id = badge_row.popularity_parameter; - -- Calculate the attribute filter set, or all bibs if none + -- Calculate the filtered bib set, or all bibs if none PERFORM rating.precalc_attr_filter(badge_row.attr_filter); + PERFORM rating.precalc_src_filter(badge_row.src_filter); + PERFORM rating.precalc_circ_mod_filter(badge_row.circ_mod_filter); + PERFORM rating.precalc_location_filter(badge_row.loc_grp_filter); + + -- Bring the bib-level filter lists together + DROP TABLE IF EXISTS precalc_bib_filter_bib_list; + CREATE TEMP TABLE precalc_bib_filter_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_attr_filter_bib_list + INTERSECT + SELECT id FROM precalc_src_filter_bib_list; + + -- Bring the copy-level filter lists together. We're keeping this for bib_by_copy filtering later. + DROP TABLE IF EXISTS precalc_copy_filter_bib_list; + CREATE TEMP TABLE precalc_copy_filter_bib_list ON COMMIT DROP AS + SELECT id, copy FROM precalc_circ_mod_filter_bib_list + INTERSECT + SELECT id, copy FROM precalc_location_filter_bib_list; + + -- Bring the collapsed filter lists together + DROP TABLE IF EXISTS precalc_filter_bib_list; + CREATE TEMP TABLE precalc_filter_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_bib_filter_bib_list + INTERSECT + SELECT id FROM precalc_copy_filter_bib_list; + + CREATE INDEX precalc_filter_bib_list_idx + ON precalc_filter_bib_list (id); -- If it's a fixed-rating badge, just do it ... IF badge_row.fixed_rating IS NOT NULL THEN DELETE FROM rating.record_badge_score WHERE badge = badge_id; EXECUTE $e$ INSERT INTO rating.record_badge_score (record, badge, score) - SELECT record, $1, value FROM $e$ || param.func || $e$($1)$e$ + SELECT record, $1, $2 FROM $e$ || param.func || $e$($1)$e$ USING badge_id, badge_row.fixed_rating; UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id; @@ -331,44 +500,16 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; --- all or limited... -CREATE OR REPLACE FUNCTION rating.precalc_attr_filter(attr_filter TEXT) - RETURNS INT AS $f$ -DECLARE - cnt INT := 0; - afilter TEXT := 0; -BEGIN - - SET client_min_messages = error; - DROP TABLE IF EXISTS precalc_attr_filter_bib_list; - IF attr_filter IS NOT NULL THEN - afilter := metabib.compile_composite_attr(attr_filter); - CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS - SELECT source AS id FROM metabib.record_attr_vector_list - WHERE vlist @@ metabib.compile_composite_attr(attr_filter); - ELSE - CREATE TEMP TABLE precalc_attr_filter_bib_list ON COMMIT DROP AS - SELECT source AS id FROM metabib.record_attr_vector_list; - END IF; - - CREATE INDEX precalc_attr_filter_bib_list_idx - ON precalc_attr_filter_bib_list (id); - - SELECT count(*) INTO cnt FROM precalc_attr_filter_bib_list; - RETURN cnt; -END; -$f$ LANGUAGE PLPGSQL; - CREATE OR REPLACE FUNCTION rating.holds_filled_over_time(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ DECLARE - badge rating.badge%ROWTYPE; + badge rating.badge_with_orgs%ROWTYPE; iage INT := 1; iint INT := NULL; iscale NUMERIC := NULL; BEGIN - SELECT * INTO badge FROM rating.badge WHERE id = badge_id; + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; IF badge.horizon_age IS NULL THEN RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.', @@ -376,6 +517,15 @@ BEGIN badge.id; END IF; + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + iint := EXTRACT(EPOCH FROM badge.importance_interval); IF badge.importance_age IS NOT NULL THEN iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT; @@ -388,31 +538,30 @@ BEGIN SELECT bib, SUM( holds * GREATEST( iscale * (iage - hage), 1.0 )) FROM ( - SELECT pop.bib AS bib, + SELECT f.id AS bib, (1 + EXTRACT(EPOCH FROM h.fulfillment_time) / iint)::INT AS hage, COUNT(h.id)::INT AS holds FROM action.hold_request h JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id) - JOIN precalc_attr_filter_bib_list f ON (f.id = rhrr.bib_record) - JOIN rating.generic_bib_population_by_copy pop ON (rhrr.bib_record = pop.bib AND pop.badge = badge.id) + JOIN precalc_bib_list f ON (f.id = rhrr.bib_record) WHERE h.fulfillment_time >= NOW() - badge.horizon_age - AND h.request_lib = ANY (pop.orgs) + AND h.request_lib = ANY (badge.orgs) GROUP BY 1, 2 ) x GROUP BY 1; END; -$f$ LANGUAGE PLPGSQL STABLE STRICT; +$f$ LANGUAGE PLPGSQL STRICT; CREATE OR REPLACE FUNCTION rating.holds_placed_over_time(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ DECLARE - badge rating.badge%ROWTYPE; + badge rating.badge_with_orgs%ROWTYPE; iage INT := 1; iint INT := NULL; iscale NUMERIC := NULL; BEGIN - SELECT * INTO badge FROM rating.badge WHERE id = badge_id; + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; IF badge.horizon_age IS NULL THEN RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.', @@ -420,6 +569,15 @@ BEGIN badge.id; END IF; + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + iint := EXTRACT(EPOCH FROM badge.importance_interval); IF badge.importance_age IS NOT NULL THEN iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT; @@ -432,44 +590,59 @@ BEGIN SELECT bib, SUM( holds * GREATEST( iscale * (iage - hage), 1.0 )) FROM ( - SELECT pop.bib AS bib, + SELECT f.id AS bib, (1 + EXTRACT(EPOCH FROM h.fulfillment_time) / iint)::INT AS hage, COUNT(h.id)::INT AS holds FROM action.hold_request h JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id) - JOIN precalc_attr_filter_bib_list f ON (f.id = rhrr.bib_record) - JOIN rating.generic_bib_population_by_copy pop ON (rhrr.bib_record = pop.bib AND pop.badge = badge.id) + JOIN precalc_bib_list f ON (f.id = rhrr.bib_record) WHERE h.request_time >= NOW() - badge.horizon_age - AND h.request_lib = ANY (pop.orgs) + AND h.request_lib = ANY (badge.orgs) GROUP BY 1, 2 ) x GROUP BY 1; END; -$f$ LANGUAGE PLPGSQL STABLE STRICT; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.current_hold_count(INT) +CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT pop.bib AS bib, +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + + RETURN QUERY + SELECT f.id AS bib, COUNT(h.id)::NUMERIC AS holds FROM action.hold_request h JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id) - JOIN precalc_attr_filter_bib_list f ON (f.id = rhrr.bib_record) - JOIN rating.generic_bib_population_by_copy pop ON (rhrr.bib_record = pop.bib AND pop.badge = $1) + JOIN precalc_bib_list f ON (f.id = rhrr.bib_record) WHERE h.fulfillment_time IS NULL - AND h.request_lib = ANY (pop.orgs) - GROUP BY 1 -$f$ LANGUAGE SQL STABLE STRICT; + AND h.request_lib = ANY (badge.orgs) + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; CREATE OR REPLACE FUNCTION rating.circs_over_time(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ DECLARE - badge rating.badge%ROWTYPE; + badge rating.badge_with_orgs%ROWTYPE; iage INT := 1; iint INT := NULL; iscale NUMERIC := NULL; BEGIN - SELECT * INTO badge FROM rating.badge WHERE id = badge_id; + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; IF badge.horizon_age IS NULL THEN RAISE EXCEPTION 'Badge "%" with id % requires a horizon age but has none.', @@ -477,6 +650,15 @@ BEGIN badge.id; END IF; + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + iint := EXTRACT(EPOCH FROM badge.importance_interval); IF badge.importance_age IS NOT NULL THEN iage := (EXTRACT(EPOCH FROM badge.importance_age) / iint)::INT; @@ -495,10 +677,9 @@ BEGIN FROM action.circulation c JOIN asset.copy cp ON (cp.id = c.target_copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_attr_filter_bib_list f ON (f.id = cn.record) - JOIN rating.generic_bib_population_by_copy pop ON (cn.record = pop.bib AND pop.badge = badge.id) + JOIN precalc_bib_list f ON (f.id = cn.record) WHERE c.xact_start >= NOW() - badge.horizon_age - AND cn.owning_lib = ANY (pop.orgs) + AND cn.owning_lib = ANY (badge.orgs) AND c.phone_renewal IS FALSE -- we don't count renewals AND c.desk_renewal IS FALSE AND c.opac_renewal IS FALSE @@ -506,97 +687,207 @@ BEGIN ) x GROUP BY 1; END; -$f$ LANGUAGE PLPGSQL STABLE STRICT; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.current_circ_count(INT) +CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT pop.bib AS bib, +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + + RETURN QUERY + SELECT f.id AS bib, COUNT(c.id)::NUMERIC AS circs FROM action.circulation c JOIN asset.copy cp ON (cp.id = c.target_copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_attr_filter_bib_list f ON (f.id = cn.record) - JOIN rating.generic_bib_population_by_copy pop ON (cn.record = pop.bib AND pop.badge = $1) + JOIN precalc_bib_list f ON (f.id = cn.record) WHERE c.checkin_time IS NULL - AND cn.owning_lib = ANY (pop.orgs) - GROUP BY 1 -$f$ LANGUAGE SQL STABLE STRICT; + AND cn.owning_lib = ANY (badge.orgs) + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(INT) +CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT bib, +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + + RETURN QUERY + SELECT bib, SUM(checked_out)::NUMERIC / SUM(total)::NUMERIC FROM (SELECT cn.record AS bib, (cp.status = 1)::INT AS checked_out, 1 AS total FROM asset.copy cp JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_attr_filter_bib_list f ON (f.id = cn.record) - JOIN rating.generic_bib_population_by_copy pop ON (cn.record = pop.bib AND pop.badge = $1) - WHERE cn.owning_lib = ANY (pop.orgs) + JOIN precalc_bib_list f ON (f.id = cn.record) + WHERE cn.owning_lib = ANY (badge.orgs) ) x - GROUP BY 1 -$f$ LANGUAGE SQL STABLE STRICT; + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.holds_total_ratio(INT) +CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT cn.record AS bib, +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + + RETURN QUERY + SELECT cn.record AS bib, COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC FROM asset.copy cp JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_attr_filter_bib_list f ON (f.id = cn.record) - JOIN rating.generic_bib_population_by_copy pop ON (cn.record = pop.bib AND pop.badge = $1) + JOIN precalc_bib_list f ON (f.id = cn.record) LEFT JOIN action.hold_copy_map m ON (m.target_copy = cp.id) - WHERE cn.owning_lib = ANY (pop.orgs) - GROUP BY 1 -$f$ LANGUAGE SQL STABLE STRICT; + WHERE cn.owning_lib = ANY (badge.orgs) + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(INT) +CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT cn.record AS bib, +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + + RETURN QUERY + SELECT cn.record AS bib, COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC FROM asset.copy cp JOIN asset.copy_location cl ON (cl.id = cp.location) JOIN config.copy_status cs ON (cs.id = cp.status) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_attr_filter_bib_list f ON (f.id = cn.record) - JOIN rating.generic_bib_population_by_copy pop ON (cn.record = pop.bib AND pop.badge = $1) + JOIN precalc_bib_list f ON (f.id = cn.record) LEFT JOIN action.hold_copy_map m ON (m.target_copy = cp.id) - WHERE cn.owning_lib = ANY (pop.orgs) + WHERE cn.owning_lib = ANY (badge.orgs) AND cp.holdable IS TRUE AND cl.holdable IS TRUE AND cs.holdable IS TRUE - GROUP BY 1 -$f$ LANGUAGE SQL STABLE STRICT; + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.bib_record_age(INT) +CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT pop.bib AS bib, +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_or_uri_list; + + RETURN QUERY + SELECT b.id, 1.0 / EXTRACT(EPOCH FROM AGE(b.create_date))::NUMERIC + 1.0 - FROM rating.generic_bib_population_by_copy_or_uri pop - JOIN biblio.record_entry b ON (b.id = pop.bib) - JOIN precalc_attr_filter_bib_list f ON (f.id = b.id) - WHERE pop.badge = $1 -$f$ LANGUAGE SQL STABLE STRICT; + FROM precalc_bib_list pop + JOIN biblio.record_entry b ON (b.id = pop.id); +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.bib_pub_age(INT) +CREATE OR REPLACE FUNCTION rating.bib_pub_age(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT pop.bib AS bib, +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_or_uri_list; + + RETURN QUERY + SELECT pop.id AS bib, s.value::NUMERIC - FROM rating.generic_bib_population_by_copy_or_uri pop - JOIN precalc_attr_filter_bib_list f ON (f.id = pop.bib) + FROM precalc_bib_list pop JOIN metabib.record_sorter s ON ( - s.source = pop.bib + s.source = pop.id AND s.attr = 'pubdate' AND s.value ~ '^\d+$' - ) - WHERE pop.badge = $1 -$f$ LANGUAGE SQL STABLE STRICT; + ); +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.percent_time_circulating(INT) +CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ - SELECT bib, - SUM(circ_time)::NUMERIC / SUM(age)::NUMERIC +DECLARE + badge rating.badge_with_orgs%ROWTYPE; +BEGIN + + SELECT * INTO badge FROM rating.badge_with_orgs WHERE id = badge_id; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + SET LOCAL client_min_messages = error; + DROP TABLE IF EXISTS precalc_bib_list; + CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list; + + RETURN QUERY + SELECT bib, + SUM(COALESCE(circ_time,0))::NUMERIC / SUM(age)::NUMERIC FROM (SELECT cn.record AS bib, cp.id, EXTRACT( EPOCH FROM AGE(cp.active_date) ) + 1 AS age, @@ -611,38 +902,47 @@ CREATE OR REPLACE FUNCTION rating.percent_time_circulating(INT) )::NUMERIC AS circ_time FROM asset.copy cp JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_attr_filter_bib_list f ON (f.id = cn.record) - JOIN rating.generic_bib_population_by_copy pop ON (cn.record = pop.bib AND pop.badge = $1) + JOIN precalc_bib_list f ON (f.id = cn.record) LEFT JOIN action.all_circulation circ ON (circ.target_copy = cp.id) - WHERE cn.owning_lib = ANY (pop.orgs) + WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1,2,3 ) x - GROUP BY 1 -$f$ LANGUAGE SQL STABLE STRICT; + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(INT) +CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ -SELECT bib, 1.0 - FROM rating.generic_bib_population_by_copy pop - JOIN precalc_attr_filter_bib_list f ON (f.id = pop.bib) - WHERE pop.badge = $1; -$f$ LANGUAGE SQL STABLE STRICT; +BEGIN + PERFORM rating.precalc_bibs_by_copy(badge_id); + RETURN QUERY + SELECT id, 1.0 FROM precalc_filter_bib_list + INTERSECT + SELECT id, 1.0 FROM precalc_bibs_by_copy_list; +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(INT) +CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_uri(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ -SELECT bib, 1.0 - FROM rating.generic_bib_population_by_uri pop - JOIN precalc_attr_filter_bib_list f ON (f.id = pop.bib) - WHERE pop.badge = $1; -$f$ LANGUAGE SQL STABLE STRICT; +BEGIN + PERFORM rating.precalc_bibs_by_uri(badge_id); + RETURN QUERY + SELECT id, 1.0 FROM precalc_filter_bib_list + INTERSECT + SELECT id, 1.0 FROM precalc_bibs_by_uri_list; +END; +$f$ LANGUAGE PLPGSQL STRICT; -CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(INT) +CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_global(badge_id INT) RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ -SELECT bib, 1.0 - FROM rating.generic_bib_population_global pop - JOIN precalc_attr_filter_bib_list f ON (f.id = pop.bib) - WHERE pop.badge = $1; -$f$ LANGUAGE SQL STABLE STRICT; +BEGIN + PERFORM rating.precalc_bibs_by_copy_or_uri(badge_id); + RETURN QUERY + SELECT id, 1.0 FROM precalc_filter_bib_list + INTERSECT + SELECT id, 1.0 FROM precalc_bibs_by_copy_or_uri_list; +END; +$f$ LANGUAGE PLPGSQL STRICT; CREATE INDEX hold_fulfillment_time_idx ON action.hold_request (fulfillment_time) WHERE fulfillment_time IS NOT NULL; CREATE INDEX hold_request_time_idx ON action.hold_request (request_time); -- 2.11.0