44 lines
5.2 KiB
MySQL
44 lines
5.2 KiB
MySQL
|
-- let the server know if the client is network testing. If so, then also remove them from work
|
||
|
|
ALTER TABLE connections ADD COLUMN is_network_testing BOOLEAN DEFAULT FALSE NOT NULL;
|
||
|
|
|
||
|
|
DROP FUNCTION IF EXISTS get_work (my_client_id VARCHAR(64), mylocidispid BIGINT, myaddr BIGINT, return_rows INT, stale_score INTERVAL);
|
||
|
|
CREATE FUNCTION get_work (my_client_id VARCHAR(64), mylocidispid BIGINT, myaddr BIGINT, return_rows INT, stale_score INTERVAL) RETURNS TABLE (client_id VARCHAR(64)) VOLATILE AS $$
|
||
|
|
BEGIN
|
||
|
|
RETURN QUERY WITH
|
||
|
|
scorable_locations AS (
|
||
|
|
SELECT DISTINCT locidispid FROM connections WHERE client_type = 'client' AND connections.client_id != my_client_id AND addr != myaddr AND udp_reachable AND is_network_testing = FALSE AND NOW() > scoring_timeout AND connections.music_session_id IS NULL AND
|
||
|
|
locidispid NOT IN (SELECT DISTINCT blocidispid FROM most_recent_scores WHERE alocidispid = mylocidispid AND (current_timestamp - score_dt) < stale_score) AND
|
||
|
|
locidispid/1000000 IN (SELECT locid FROM geoiplocations WHERE geog && st_buffer((SELECT geog FROM geoiplocations WHERE locid = mylocidispid/1000000), 4023360))
|
||
|
|
)
|
||
|
|
|
||
|
|
SELECT tmp.client_id FROM (SELECT connections.client_id, random() AS r, row_number() OVER (PARTITION BY connections.locidispid) AS rownum FROM connections, scorable_locations
|
||
|
|
WHERE connections.locidispid = scorable_locations.locidispid AND client_type = 'client' AND connections.client_id != my_client_id AND addr != myaddr AND udp_reachable AND NOW() > scoring_timeout AND connections.music_session_id IS NULL ) tmp WHERE rownum <= 1 ORDER BY r LIMIT return_rows;
|
||
|
|
|
||
|
|
RETURN;
|
||
|
|
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
|
||
|
|
DROP FUNCTION IF EXISTS get_work_summary (stale_score INTERVAL);
|
||
|
|
CREATE FUNCTION get_work_summary (stale_score INTERVAL) RETURNS TABLE (work_count BIGINT, client_id VARCHAR(64), email VARCHAR, first_name VARCHAR, last_name VARCHAR, user_id VARCHAR(64), udp_reachable BOOLEAN, in_timeout BOOLEAN, in_session BOOLEAN, scoring_failures INT, scoring_failures_offset INT, scoring_timeout_occurrences INT, is_network_testing BOOLEAN) VOLATILE AS $$
|
||
|
|
BEGIN
|
||
|
|
RETURN QUERY
|
||
|
|
SELECT SUM(CASE WHEN tmp.test_client_id IS NULL OR tmp.in_session OR tmp.in_timeout OR tmp.udp_reachable = FALSE OR tmp.is_network_testing = TRUE THEN 0 ELSE 1 END) AS work_count, tmp.client_id AS client_id, users.email, users.first_name, users.last_name, users.id AS user_id, tmp.udp_reachable, tmp.in_timeout, tmp.in_session, tmp.scoring_failures, tmp.scoring_failures_offset, tmp.scoring_timeout_occurrences, tmp.is_network_testing FROM
|
||
|
|
(SELECT connections.client_type, scorable_locations.client_id AS test_client_id, connections.client_id AS client_id, connections.user_id AS user_id, connections.udp_reachable, connections.scoring_timeout > NOW() as in_timeout, connections.music_session_id IS NOT NULL AS in_session, connections.scoring_failures, connections.scoring_failures_offset, connections.scoring_timeout_occurrences, connections.is_network_testing, scorable_locations.client_id IS NULL AS same_client, row_number() OVER (PARTITION BY connections.locidispid) AS rownum FROM connections LEFT OUTER JOIN scorable_locations(connections.client_id, connections.locidispid, connections.addr, stale_score)
|
||
|
|
ON connections.locidispid != scorable_locations.locidispid) tmp INNER JOIN users ON tmp.user_id = users.id WHERE tmp.client_type = 'client' GROUP BY tmp.client_id, users.email, users.first_name, users.last_name, users.id, tmp.same_client, tmp.udp_reachable, tmp.in_timeout, tmp.in_session, tmp.scoring_failures, tmp.scoring_failures_offset, tmp.scoring_timeout_occurrences, tmp.is_network_testing ORDER BY work_count DESC;
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
DROP FUNCTION IF EXISTS get_work_summary_no_agg(stale_score INTERVAL);
|
||
|
|
-- useful for debugging get_work_summary
|
||
|
|
CREATE FUNCTION get_work_summary_no_agg (stale_score INTERVAL) RETURNS TABLE (client_id VARCHAR(64), test_client_id VARCHAR(64), email VARCHAR, first_name VARCHAR, last_name VARCHAR, user_id VARCHAR(64), udp_reachable BOOLEAN, in_timeout BOOLEAN, scoring_failures INT, scoring_failures_offset INT, scoring_timeout_occurrences INT, is_network_testing BOOLEAN) VOLATILE AS $$
|
||
|
|
BEGIN
|
||
|
|
RETURN QUERY
|
||
|
|
SELECT tmp.client_id AS client_id, tmp.test_client_id, users.email, users.first_name, users.last_name, users.id AS user_id, tmp.udp_reachable, tmp.in_timeout, tmp.scoring_failures, tmp.scoring_failures_offset, tmp.scoring_timeout_occurrences, tmp.is_network_testing FROM
|
||
|
|
(SELECT scorable_locations.client_id AS test_client_id, connections.client_id AS client_id, connections.user_id AS user_id, connections.udp_reachable, connections.scoring_timeout > NOW() as in_timeout, connections.scoring_failures, connections.scoring_failures_offset, connections.scoring_timeout_occurrences, connections.is_network_testing, scorable_locations.client_id IS NULL AS same_client, row_number() OVER (PARTITION BY connections.locidispid) AS rownum FROM connections LEFT OUTER JOIN scorable_locations(connections.client_id, connections.locidispid, connections.addr, stale_score)
|
||
|
|
ON connections.locidispid != scorable_locations.locidispid AND connections.client_type = 'client') tmp INNER JOIN users ON tmp.user_id = users.id ORDER BY tmp.client_id;
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|