From df0d763642e5841baaac36d09b1fa9df525b2b67 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Fri, 6 May 2016 11:29:39 -0400 Subject: [PATCH] LP#1549505: update baseline database schema Signed-off-by: Galen Charlton Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 + Open-ILS/src/sql/Pg/220.schema.rating.sql | 905 ++++++++++++++++++ .../src/sql/Pg/300.schema.staged_search.sql | 10 +- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 2 - Open-ILS/src/sql/Pg/reporter-schema.sql | 92 +- Open-ILS/src/sql/Pg/sql_file_manifest | 2 + 6 files changed, 991 insertions(+), 22 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/220.schema.rating.sql diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index c79793a321..5ce80d320a 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -432,6 +432,8 @@ CREATE INDEX hold_request_current_copy_before_cap_idx ON action.hold_request (cu CREATE UNIQUE INDEX hold_request_capture_protect_idx ON action.hold_request (current_copy) WHERE current_copy IS NOT NULL AND capture_time IS NOT NULL AND cancel_time IS NULL AND fulfillment_time IS NULL; CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time); CREATE INDEX hold_request_open_captured_shelf_lib_idx ON action.hold_request (current_shelf_lib) WHERE capture_time IS NOT NULL AND fulfillment_time IS NULL AND (pickup_lib <> current_shelf_lib); +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); CREATE TABLE action.hold_request_note ( diff --git a/Open-ILS/src/sql/Pg/220.schema.rating.sql b/Open-ILS/src/sql/Pg/220.schema.rating.sql new file mode 100644 index 0000000000..460fc36fdd --- /dev/null +++ b/Open-ILS/src/sql/Pg/220.schema.rating.sql @@ -0,0 +1,905 @@ +DROP SCHEMA IF EXISTS rating CASCADE; + +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; + +CREATE SCHEMA rating; + +CREATE TABLE rating.popularity_parameter ( + id INT PRIMARY KEY, + name TEXT NOT NULL UNIQUE, -- i18n + description TEXT, + func TEXT, + require_horizon BOOL NOT NULL DEFAULT FALSE, + require_importance BOOL NOT NULL DEFAULT FALSE, + require_percentile BOOL NOT NULL DEFAULT FALSE +); + +INSERT INTO rating.popularity_parameter (id,name,func,require_horizon,require_importance,require_percentile) VALUES + (1,'Holds Filled Over Time','rating.holds_filled_over_time',TRUE,FALSE,TRUE), + (2,'Holds Requested Over Time','rating.holds_placed_over_time',TRUE,FALSE,TRUE), + (3,'Current Hold Count','rating.current_hold_count',FALSE,FALSE,TRUE), + (4,'Circulations Over Time','rating.circs_over_time',TRUE,FALSE,TRUE), + (5,'Current Circulation Count','rating.current_circ_count',FALSE,FALSE,TRUE), + (6,'Out/Total Ratio','rating.checked_out_total_ratio',FALSE,FALSE,TRUE), + (7,'Holds/Total Ratio','rating.holds_total_ratio',FALSE,FALSE,TRUE), + (8,'Holds/Holdable Ratio','rating.holds_holdable_ratio',FALSE,FALSE,TRUE), + (9,'Percent of Time Circulating','rating.percent_time_circulating',FALSE,FALSE,TRUE), + (10,'Bibliographic Record Age (days, newer is better)','rating.bib_record_age',FALSE,FALSE,TRUE), + (11,'Publication Age (days, newer is better)','rating.bib_pub_age',FALSE,FALSE,TRUE), + (12,'On-line Bib has attributes','rating.generic_fixed_rating_by_uri',FALSE,FALSE,FALSE), + (13,'Bib has attributes and copies','rating.generic_fixed_rating_by_copy',FALSE,FALSE,FALSE), + (14,'Bib has attributes and copies or URIs','rating.generic_fixed_rating_by_copy_or_uri',FALSE,FALSE,FALSE), + (15,'Bib has attributes','rating.generic_fixed_rating_global',FALSE,FALSE,FALSE); + +CREATE TABLE rating.badge ( + id SERIAL PRIMARY KEY, + name TEXT NOT NULL, + description TEXT, + scope INT NOT NULL REFERENCES actor.org_unit (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + weight INT NOT NULL DEFAULT 1, + horizon_age INTERVAL, + importance_age INTERVAL, + importance_interval INTERVAL NOT NULL DEFAULT '1 day', + importance_scale NUMERIC CHECK (importance_scale IS NULL OR importance_scale > 0.0), + recalc_interval INTERVAL NOT NULL DEFAULT '1 month', + attr_filter TEXT, + src_filter INT REFERENCES config.bib_source (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + circ_mod_filter TEXT REFERENCES config.circ_modifier (code) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + loc_grp_filter INT REFERENCES asset.copy_location_group (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + popularity_parameter INT NOT NULL REFERENCES rating.popularity_parameter (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + fixed_rating INT CHECK (fixed_rating IS NULL OR fixed_rating BETWEEN -5 AND 5), + percentile NUMERIC CHECK (percentile IS NULL OR (percentile >= 50.0 AND percentile < 100.0)), + discard INT NOT NULL DEFAULT 0, + last_calc TIMESTAMPTZ, + CONSTRAINT unique_name_scope UNIQUE (name,scope) +); + +CREATE TABLE rating.record_badge_score ( + id BIGSERIAL PRIMARY KEY, + record BIGINT NOT NULL REFERENCES biblio.record_entry (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + badge INT NOT NULL REFERENCES rating.badge (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, + score INT NOT NULL CHECK (score BETWEEN -5 AND 5), + CONSTRAINT unique_record_badge UNIQUE (record,badge) +); +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.badge_with_orgs 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 b.*, + s.orgs + FROM rating.badge b + JOIN org_scope s ON (b.scope = s.id); + +CREATE OR REPLACE FUNCTION rating.precalc_src_filter(src INT) + RETURNS INT AS $f$ +DECLARE + cnt INT := 0; +BEGIN + + 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; + + 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 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_copy_or_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_or_uri_list; + RETURN cnt; +END; +$f$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$ +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 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 setup_only THEN + RETURN; + END IF; + + -- 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, $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; + + RETURN; + END IF; + -- else, calculate! + + -- Make a session-local scratchpad for calculating scores + CREATE TEMP TABLE record_score_scratchpad ( + bib BIGINT, + value NUMERIC + ) ON COMMIT DROP; + + -- Gather raw values + EXECUTE $e$ + INSERT INTO record_score_scratchpad (bib, value) + SELECT * FROM $e$ || param.func || $e$($1)$e$ + USING badge_id; + + IF badge_row.discard > 0 OR badge_row.percentile IS NOT NULL THEN + -- To speed up discard-common + CREATE INDEX record_score_scratchpad_score_idx ON record_score_scratchpad (value); + ANALYZE record_score_scratchpad; + END IF; + + IF badge_row.discard > 0 THEN -- Remove common low values (trim the long tail) + DELETE FROM record_score_scratchpad WHERE value IN ( + SELECT DISTINCT value FROM record_score_scratchpad ORDER BY value LIMIT badge_row.discard + ); + END IF; + + IF badge_row.percentile IS NOT NULL THEN -- Cut population down to exceptional records + DELETE FROM record_score_scratchpad WHERE value <= ( + SELECT value FROM ( + SELECT value, + CUME_DIST() OVER (ORDER BY value) AS p + FROM record_score_scratchpad + ) x WHERE p < badge_row.percentile / 100.0 ORDER BY p DESC LIMIT 1 + ); + END IF; + + + -- And, finally, push new data in + DELETE FROM rating.record_badge_score WHERE badge = badge_id; + INSERT INTO rating.record_badge_score (badge, record, score) + SELECT badge_id, + bib, + GREATEST(ROUND((CUME_DIST() OVER (ORDER BY value)) * 5), 1) AS value + FROM record_score_scratchpad; + + DROP TABLE record_score_scratchpad; + + -- Now, finally-finally, mark the badge as recalculated + UPDATE rating.badge SET last_calc = NOW() WHERE id = badge_id; + + RETURN; +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_with_orgs%ROWTYPE; + iage INT := 1; + iint INT := NULL; + iscale NUMERIC := NULL; +BEGIN + + 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.', + badge.name, + 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; + END IF; + + -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE! + iscale := COALESCE(badge.importance_scale, 1.0); + + RETURN QUERY + SELECT bib, + SUM( holds * GREATEST( iscale * (iage - hage), 1.0 )) + FROM ( + SELECT f.id AS bib, + (1 + EXTRACT(EPOCH FROM AGE(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_bib_list f ON (f.id = rhrr.bib_record) + WHERE h.fulfillment_time >= NOW() - badge.horizon_age + AND h.request_lib = ANY (badge.orgs) + GROUP BY 1, 2 + ) x + GROUP BY 1; +END; +$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_with_orgs%ROWTYPE; + iage INT := 1; + iint INT := NULL; + iscale NUMERIC := NULL; +BEGIN + + 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.', + badge.name, + 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; + END IF; + + -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE! + iscale := COALESCE(badge.importance_scale, 1.0); + + RETURN QUERY + SELECT bib, + SUM( holds * GREATEST( iscale * (iage - hage), 1.0 )) + FROM ( + SELECT f.id AS bib, + (1 + EXTRACT(EPOCH FROM AGE(h.request_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_bib_list f ON (f.id = rhrr.bib_record) + WHERE h.request_time >= NOW() - badge.horizon_age + AND h.request_lib = ANY (badge.orgs) + GROUP BY 1, 2 + ) x + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.current_hold_count(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT rhrr.bib_record AS bib, + COUNT(DISTINCT h.id)::NUMERIC AS holds + FROM action.hold_request h + JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id) + JOIN action.hold_copy_map m ON (m.hold = h.id) + JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy) + WHERE h.fulfillment_time IS NULL + 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_with_orgs%ROWTYPE; + iage INT := 1; + iint INT := NULL; + iscale NUMERIC := NULL; +BEGIN + + 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.', + badge.name, + badge.id; + END IF; + + PERFORM rating.precalc_bibs_by_copy(badge_id); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_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; + END IF; + + -- if iscale is smaller than 1, scaling slope will be shallow ... BEWARE! + iscale := COALESCE(badge.importance_scale, 1.0); + + RETURN QUERY + SELECT bib, + SUM( circs * GREATEST( iscale * (iage - cage), 1.0 )) + FROM ( + SELECT cn.record AS bib, + (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage, + COUNT(c.id)::INT AS circs + FROM action.circulation c + JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy) + JOIN asset.copy cp ON (cp.id = c.target_copy) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE c.xact_start >= NOW() - badge.horizon_age + 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 + GROUP BY 1, 2 + ) x + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.current_circ_count(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT cn.record AS bib, + COUNT(c.id)::NUMERIC AS circs + FROM action.circulation c + JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy) + JOIN asset.copy cp ON (cp.id = c.target_copy) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE c.checkin_time IS NULL + AND cn.owning_lib = ANY (badge.orgs) + GROUP BY 1; + +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.checked_out_total_ratio(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_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 precalc_copy_filter_bib_list c ON (cp.id = c.copy) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + WHERE cn.owning_lib = ANY (badge.orgs) + ) x + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.holds_total_ratio(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT cn.record AS bib, + COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC + FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + JOIN action.hold_copy_map m ON (m.target_copy = cp.id) + WHERE cn.owning_lib = ANY (badge.orgs) + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.holds_holdable_ratio(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; + + RETURN QUERY + SELECT cn.record AS bib, + COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC + FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) + 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 action.hold_copy_map m ON (m.target_copy = cp.id) + 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; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.bib_record_age(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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 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(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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 precalc_bib_list pop + JOIN metabib.record_sorter s ON ( + s.source = pop.id + AND s.attr = 'pubdate' + AND s.value ~ '^\d+$' + ) + WHERE s.value::INT <= EXTRACT(YEAR FROM NOW())::INT; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.percent_time_circulating(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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); + + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( + SELECT id FROM precalc_filter_bib_list + INTERSECT + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_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, + SUM( -- time copy spent circulating + EXTRACT( + EPOCH FROM + AGE( + COALESCE(circ.checkin_time, circ.stop_fines_time, NOW()), + circ.xact_start + ) + ) + )::NUMERIC AS circ_time + FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) + JOIN asset.call_number cn ON (cn.id = cp.call_number) + LEFT JOIN action.all_circulation circ ON ( + circ.target_copy = cp.id + AND stop_fines NOT IN ( + 'LOST', + 'LONGOVERDUE', + 'CLAIMSRETURNED', + 'LONGOVERDUE' + ) + AND NOT ( + checkin_time IS NULL AND + stop_fines = 'MAXFINES' + ) + ) + WHERE cn.owning_lib = ANY (badge.orgs) + AND cp.active_date IS NOT NULL + -- Next line requires that copies with no circs (circ.id IS NULL) also not be deleted + AND ((circ.id IS NULL AND NOT cp.deleted) OR circ.id IS NOT NULL) + GROUP BY 1,2,3 + ) x + GROUP BY 1; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +CREATE OR REPLACE FUNCTION rating.generic_fixed_rating_by_copy(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +BEGIN + PERFORM rating.precalc_bibs_by_uri(badge_id); + RETURN QUERY + SELECT id, 1.0 FROM precalc_bib_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_by_copy_or_uri(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +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_list) + UNION + (SELECT id, 1.0 FROM precalc_bib_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(badge_id INT) + RETURNS TABLE (record BIGINT, value NUMERIC) AS $f$ +BEGIN + RETURN QUERY + SELECT id, 1.0 FROM precalc_bib_filter_bib_list; +END; +$f$ LANGUAGE PLPGSQL STRICT; + +COMMIT; + diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql index d5a5f0bc06..8aa18be873 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -61,7 +61,7 @@ CREATE TABLE search.relevance_adjustment ( ); CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( field, bump_type ); -CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT ); +CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT, badges TEXT, popularity NUMERIC ); CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT ); CREATE OR REPLACE FUNCTION search.query_parser_fts ( @@ -190,6 +190,8 @@ BEGIN current_res.id = core_result.id; current_res.rel = core_result.rel; + current_res.badges = core_result.badges; + current_res.popularity = core_result.popularity; tmp_int := 1; IF metarecord THEN @@ -225,6 +227,8 @@ BEGIN current_res.id = core_result.id; current_res.rel = core_result.rel; + current_res.badges = core_result.badges; + current_res.popularity = core_result.popularity; tmp_int := 1; IF metarecord THEN @@ -393,6 +397,8 @@ BEGIN current_res.id = core_result.id; current_res.rel = core_result.rel; + current_res.badges = core_result.badges; + current_res.popularity = core_result.popularity; tmp_int := 1; IF metarecord THEN @@ -416,6 +422,8 @@ BEGIN current_res.id = NULL; current_res.rel = NULL; current_res.record = NULL; + current_res.badges = NULL; + current_res.popularity = NULL; current_res.total = total_count; current_res.checked = check_count; current_res.deleted = deleted_count; diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index feb6cb433a..c66bf6028d 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -11712,7 +11712,6 @@ INSERT INTO config.global_flag (name, label, value, enabled) VALUES ( TRUE ); -/* To be added when upgrade scripts are pulled into the baseline INSERT INTO config.global_flag (name, label, value, enabled) VALUES ( 'opac.default_sort', oils_i18n_gettext( @@ -11736,7 +11735,6 @@ INSERT INTO config.global_flag (name, label, value, enabled) VALUES ( '2.0', TRUE ); -*/ INSERT INTO config.usr_setting_type (name,opac_visible,label,description,datatype) VALUES ( diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 2e910f93d9..dce360389a 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -257,25 +257,79 @@ SELECT id, END AS "type" FROM action.circulation; -CREATE OR REPLACE VIEW reporter.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; +-- rhrr needs to be a real table, so it can be fast. To that end, we use +-- a materialized view updated via a trigger. +CREATE TABLE reporter.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; + +CREATE UNIQUE INDEX reporter_hold_request_record_pkey_idx ON reporter.hold_request_record (id); +CREATE INDEX reporter_hold_request_record_bib_record_idx ON reporter.hold_request_record (bib_record); + +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 $$ +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; + END IF; + RETURN NEW; +END; +$$ LANGUAGE PLPGSQL; + +CREATE TRIGGER reporter_hold_request_record_trigger AFTER INSERT OR UPDATE ON action.hold_request + FOR EACH ROW EXECUTE PROCEDURE reporter.hold_request_record_mapper(); CREATE OR REPLACE VIEW reporter.xact_billing_totals AS SELECT b.xact, diff --git a/Open-ILS/src/sql/Pg/sql_file_manifest b/Open-ILS/src/sql/Pg/sql_file_manifest index 639736b66d..9b16ab1f2d 100644 --- a/Open-ILS/src/sql/Pg/sql_file_manifest +++ b/Open-ILS/src/sql/Pg/sql_file_manifest @@ -39,6 +39,8 @@ FTS_CONFIG_FILE 200.schema.acq.sql 201.acq.audit-functions.sql +220.schema.rating.sql + 300.schema.staged_search.sql 400.schema.action_trigger.sql -- 2.43.2