233 lines
14 KiB
PL/PgSQL
233 lines
14 KiB
PL/PgSQL
-- check that the music_sessions does not currently have an active_music_sessions
|
|
CREATE OR REPLACE FUNCTION sms_index (my_user_id VARCHAR, my_locidispid BIGINT, my_audio_latency INTEGER, session_id VARCHAR, include_pending BOOLEAN DEFAULT FALSE) 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;
|
|
|
|
IF session_id = 'any' THEN
|
|
-- 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 old = FALSE AND (scheduled_start IS NULL OR scheduled_start > (NOW() - (interval '15 minute')))
|
|
AND canceled = FALSE AND description != 'Jam Track Session'
|
|
AND id NOT IN (SELECT id FROM active_music_sessions);
|
|
|
|
-- 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;
|
|
|
|
ELSE
|
|
INSERT INTO sms_music_session_tmp SELECT DISTINCT id, NULL::INTEGER AS tag, NULL::INTEGER AS latency
|
|
FROM music_sessions
|
|
WHERE music_sessions.id = session_id;
|
|
END IF;
|
|
|
|
-- 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, full_score INTEGER, audio_latency INTEGER, internet_score INTEGER) ON COMMIT DROP;
|
|
|
|
IF my_audio_latency > -1 THEN
|
|
|
|
IF include_pending 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 DISTINCT q.music_session_id, users.id, s.full_score AS full_score, s.a_audio_latency, s.score
|
|
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.a_userid = users.id
|
|
WHERE
|
|
s.b_userid = my_user_id;
|
|
|
|
-- 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.full_score AS full_score, s.a_audio_latency, s.score
|
|
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.a_userid = users.id
|
|
WHERE
|
|
s.b_userid = my_user_id AND
|
|
users.id NOT IN (SELECT user_id FROM sms_users_tmp);
|
|
ELSE
|
|
-- 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 DISTINCT q.music_session_id, users.id, s.full_score AS full_score, s.a_audio_latency, s.score
|
|
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.a_userid = users.id
|
|
WHERE
|
|
s.b_userid = my_user_id AND
|
|
rsvp_requests_rsvp_slots.chosen = TRUE AND
|
|
rsvp_requests.canceled != TRUE;
|
|
END IF;
|
|
END IF;
|
|
|
|
-- calculate the average latency
|
|
UPDATE sms_music_session_tmp q SET latency = (select AVG(u.full_score) FROM sms_users_tmp u WHERE
|
|
q.music_session_id = u.music_session_id);
|
|
|
|
RETURN;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- check that the music_sessions does not currently have an active_music_sessions
|
|
CREATE OR REPLACE FUNCTION sms_index_test (my_user_id VARCHAR, my_locidispid BIGINT, my_audio_latency INTEGER, session_id VARCHAR, include_pending BOOLEAN DEFAULT FALSE) 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;
|
|
|
|
IF session_id = 'any' THEN
|
|
-- 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')))
|
|
AND canceled = FALSE AND description != 'Jam Track Session'
|
|
AND id NOT IN (SELECT id FROM active_music_sessions) ;
|
|
|
|
-- 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;
|
|
|
|
ELSE
|
|
INSERT INTO sms_music_session_tmp SELECT DISTINCT id, NULL::INTEGER AS tag, NULL::INTEGER AS latency
|
|
FROM music_sessions
|
|
WHERE music_sessions.id = session_id;
|
|
END IF;
|
|
|
|
-- 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, full_score INTEGER, audio_latency INTEGER, internet_score INTEGER) ON COMMIT DROP;
|
|
|
|
IF my_audio_latency > -1 THEN
|
|
|
|
IF include_pending 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 DISTINCT q.music_session_id, users.id, CAST(NULL AS INTEGER), CAST(NULL AS INTEGER), CAST(NULL AS INTEGER)
|
|
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;
|
|
|
|
-- 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, CAST(NULL AS INTEGER), CAST(NULL AS INTEGER), CAST(NULL AS INTEGER)
|
|
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
|
|
WHERE
|
|
users.id NOT IN (SELECT user_id FROM sms_users_tmp);
|
|
ELSE
|
|
-- 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 DISTINCT q.music_session_id, users.id, CAST(NULL AS INTEGER), CAST(NULL AS INTEGER), CAST(NULL AS INTEGER)
|
|
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
|
|
WHERE
|
|
rsvp_requests_rsvp_slots.chosen = TRUE AND
|
|
rsvp_requests.canceled != TRUE;
|
|
END IF;
|
|
END IF;
|
|
|
|
-- calculate the average latency
|
|
--UPDATE sms_music_session_tmp q SET latency = (select AVG(u.full_score) FROM sms_users_tmp u WHERE
|
|
-- q.music_session_id = u.music_session_id);
|
|
|
|
RETURN;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- XXXX TODO: TURN THESE ON AFTER PRODUCTION IS UPDATED
|
|
CREATE INDEX index_rsvp_requests_rsvp_slots_on_rsvp_request_id ON rsvp_requests_rsvp_slots USING btree(rsvp_request_id);
|
|
CREATE INDEX index_rsvp_requests_rsvp_slots_on_rsvp_slot_id ON rsvp_requests_rsvp_slots USING btree(rsvp_slot_id);
|
|
CREATE INDEX index_rsvp_requests_rsvp_slots_on_chosen ON rsvp_requests_rsvp_slots USING btree(chosen);
|
|
CREATE INDEX index_rsvp_slots_on_music_session_id ON rsvp_slots USING btree(music_session_id);
|
|
CREATE INDEX index_rsvp_requests_user_id ON rsvp_requests USING btree(user_id);
|
|
CREATE INDEX index_rsvp_requests_canceled ON rsvp_requests USING btree(canceled);
|
|
CREATE INDEX index_invitations_on_receiver_id ON invitations USING btree(receiver_id);
|
|
CREATE INDEX index_invitations_on_music_session_id ON invitations USING btree(music_session_id);
|
|
|
|
CREATE INDEX index_affiliate_traffic_totals_on_day ON affiliate_traffic_totals USING btree(day);
|
|
CREATE INDEX index_affiliate_traffic_totals_on_affiliate_partner_id ON affiliate_traffic_totals USING btree(affiliate_partner_id);
|
|
CREATE INDEX index_users_on_affiliate_referral_id ON users USING btree(affiliate_referral_id);
|
|
CREATE INDEX index_users_on_created_at ON users USING btree(created_at);
|
|
CREATE INDEX index_share_tokens_on_shareable_id ON share_tokens USING btree(shareable_id);
|
|
CREATE INDEX index_music_sessions_on_create_type ON music_sessions USING btree(create_type);
|
|
CREATE INDEX index_music_sessions_on_scheduled_start ON music_sessions USING btree(scheduled_start);
|
|
CREATE INDEX index_music_sessions_on_canceled ON music_sessions USING btree(canceled);
|
|
CREATE INDEX index_music_sessions_on_session_removed_at ON music_sessions USING btree(session_removed_at);
|
|
CREATE INDEX index_music_sessions_on_started_at ON music_sessions USING btree(started_at);
|
|
|
|
CREATE INDEX index_recordings_on_first_quick_mix_id ON recordings USING btree(first_quick_mix_id);
|
|
CREATE INDEX index_recordings_on_has_final_mix ON recordings USING btree(has_final_mix);
|
|
CREATE INDEX index_quick_mixes_on_recording_id ON quick_mixes USING btree(recording_id);
|
|
CREATE INDEX index_quick_mixes_on_cleaned ON quick_mixes USING btree(cleaned);
|
|
CREATE INDEX index_quick_mixes_on_completed ON quick_mixes USING btree(completed);
|
|
CREATE INDEX index_recordings_on_deleted ON recordings USING btree(deleted);
|
|
CREATE INDEX index_recordings_on_all_discarded ON recordings USING btree(all_discarded);
|
|
CREATE INDEX index_claimed_recordings_on_is_public ON claimed_recordings USING btree(is_public);
|
|
CREATE INDEX index_claimed_recordings_on_recording_id ON claimed_recordings USING btree(recording_id);
|
|
|
|
|
|
CREATE INDEX index_charges_on_type ON charges USING btree(type);
|
|
CREATE INDEX index_charges_on_billing_should_retry ON charges USING btree(billing_should_retry);
|
|
CREATE INDEX index_lesson_sessions_on_charge_id ON lesson_sessions USING btree(charge_id);
|
|
CREATE INDEX index_music_sessions_on_lesson_session_id ON music_sessions USING btree(lesson_session_id);
|
|
CREATE INDEX index_lesson_sessions_on_status ON lesson_sessions USING btree(status);
|
|
CREATE INDEX index_lesson_sessions_on_sent_counter_reminder ON lesson_sessions USING btree(sent_counter_reminder);
|
|
|
|
|
|
-- update music_sessions set canceled = true WHERE (scheduled_start IS NULL OR scheduled_start > (NOW() - (interval '15 minute'))) AND canceled = FALSE AND description != 'Jam Track Session' AND id NOT IN (SELECT id FROM active_music_sessions) AND id NOT IN (select distinct on(name, user_id) id FROM music_sessions WHERE (scheduled_start IS NULL OR scheduled_start > (NOW() - (interval '15 minute'))) AND canceled = FALSE AND description != 'Jam Track Session' AND id NOT IN (SELECT id FROM active_music_sessions) order by name, user_id);
|
|
|
|
|
|
|
|
-- select distinct on(name, user_id) name, description, scheduled_start FROM music_sessions WHERE (scheduled_start IS NULL OR scheduled_start > (NOW() - (interval '15 minute'))) AND canceled = FALSE AND description != 'Jam Track Session' AND id NOT IN (SELECT id FROM active_music_sessions) order by name, user_id;
|
|
|
|
-- get count
|
|
-- SELECT count(id) FROM music_sessions WHERE (scheduled_start IS NULL OR scheduled_start > (NOW() - (interval '15 minute'))) AND canceled = FALSE AND description != 'Jam Track Session' AND id NOT IN (SELECT id FROM active_music_sessions) |