70 lines
3.8 KiB
MySQL
70 lines
3.8 KiB
MySQL
|
-- my_audio_latency can have a special value of -1, which means 'unknown'.
|
||
|
|
CREATE OR REPLACE FUNCTION sms_index (my_user_id VARCHAR, my_locidispid BIGINT, my_audio_latency INTEGER) RETURNS VOID STRICT VOLATILE AS $$
|
||
|
|
BEGIN
|
||
|
|
-- output table to hold tagged music sessions with latency
|
||
|
|
CREATE TEMPORARY TABLE sms_music_session_tmp (music_session_id VARCHAR(64) NOT NULL, tag INTEGER, latency INTEGER) ON COMMIT DROP;
|
||
|
|
|
||
|
|
-- populate sms_music_session_tmp as all music sessions
|
||
|
|
-- XXX: we should pass in enough info to match pagination/query to reduce the impact of this step
|
||
|
|
INSERT INTO sms_music_session_tmp SELECT DISTINCT id, NULL::INTEGER AS tag, NULL::INTEGER AS latency
|
||
|
|
FROM music_sessions where scheduled_start IS NULL OR scheduled_start > (NOW() - (interval '15 minute'));
|
||
|
|
|
||
|
|
-- tag accepted rsvp as 1
|
||
|
|
UPDATE sms_music_session_tmp q SET tag = 1 FROM rsvp_slots s, rsvp_requests_rsvp_slots rrs, rsvp_requests r WHERE
|
||
|
|
q.music_session_id = s.music_session_id AND
|
||
|
|
s.id = rrs.rsvp_slot_id AND
|
||
|
|
rrs.rsvp_request_id = r.id AND
|
||
|
|
r.user_id = my_user_id AND
|
||
|
|
rrs.chosen = TRUE AND
|
||
|
|
q.tag is NULL;
|
||
|
|
|
||
|
|
-- tag invitation as 2
|
||
|
|
UPDATE sms_music_session_tmp q SET tag = 2 FROM invitations i WHERE
|
||
|
|
q.music_session_id = i.music_session_id AND
|
||
|
|
i.receiver_id = my_user_id AND
|
||
|
|
q.tag IS NULL;
|
||
|
|
|
||
|
|
-- musician access as 3
|
||
|
|
UPDATE sms_music_session_tmp q SET tag = 3 FROM music_sessions m WHERE
|
||
|
|
q.music_session_id = m.id AND
|
||
|
|
m.open_rsvps = TRUE AND
|
||
|
|
q.tag IS NULL;
|
||
|
|
|
||
|
|
-- delete anything not tagged
|
||
|
|
DELETE FROM sms_music_session_tmp WHERE tag IS NULL;
|
||
|
|
|
||
|
|
-- output table to hold users involved in the sms_music_session_tmp sessions and their latency
|
||
|
|
CREATE TEMPORARY TABLE sms_users_tmp (music_session_id VARCHAR(64), user_id VARCHAR(64) NOT NULL, latency INTEGER) ON COMMIT DROP;
|
||
|
|
|
||
|
|
IF my_audio_latency > -1 THEN
|
||
|
|
-- populate sms_users_tmp with users that have an approved RSVP for sessions in the sms_music_session_tmp table, accompanied with full latency and music session
|
||
|
|
INSERT INTO sms_users_tmp SELECT q.music_session_id, users.id, (s.score+my_audio_latency+users.last_jam_audio_latency)/2 AS latency
|
||
|
|
FROM sms_music_session_tmp q
|
||
|
|
INNER JOIN rsvp_slots ON rsvp_slots.music_session_id = q.music_session_id
|
||
|
|
INNER JOIN rsvp_requests_rsvp_slots ON rsvp_requests_rsvp_slots.rsvp_slot_id = rsvp_slots.id
|
||
|
|
INNER JOIN rsvp_requests ON rsvp_requests.id = rsvp_requests_rsvp_slots.rsvp_request_id
|
||
|
|
INNER JOIN users ON rsvp_requests.user_id = users.id
|
||
|
|
LEFT OUTER JOIN current_scores s ON s.alocidispid = users.last_jam_locidispid
|
||
|
|
WHERE
|
||
|
|
s.blocidispid = my_locidispid AND
|
||
|
|
rsvp_requests_rsvp_slots.chosen = TRUE;
|
||
|
|
|
||
|
|
-- populate sms_users_tmp with invited users for session in the sms_music_session_tmp table, accompanied with full latency and music session
|
||
|
|
-- specify NULL for music_session_id, because we don't want RSVP users to affect the AVG computed for each session later
|
||
|
|
INSERT INTO sms_users_tmp SELECT NULL, users.id, (s.score+my_audio_latency+users.last_jam_audio_latency)/2 AS latency
|
||
|
|
FROM sms_music_session_tmp q
|
||
|
|
INNER JOIN invitations ON invitations.music_session_id = q.music_session_id
|
||
|
|
INNER JOIN users ON invitations.receiver_id = users.id
|
||
|
|
LEFT OUTER JOIN current_scores s ON s.alocidispid = users.last_jam_locidispid
|
||
|
|
WHERE
|
||
|
|
s.blocidispid = my_locidispid AND
|
||
|
|
users.id NOT IN (SELECT user_id FROM sms_users_tmp);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- calculate the average latency
|
||
|
|
UPDATE sms_music_session_tmp q SET latency = (select AVG(u.latency) FROM sms_users_tmp u WHERE
|
||
|
|
q.music_session_id = u.music_session_id);
|
||
|
|
|
||
|
|
RETURN;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|