40 lines
1.8 KiB
SQL
40 lines
1.8 KiB
SQL
|
|
-- this is to make sure we don't delete any recordings for 7 days
|
|
UPDATE recordings SET updated_at = NOW();
|
|
|
|
ALTER TABLE recordings ADD COLUMN deleted BOOLEAN DEFAULT FALSE NOT NULL;
|
|
|
|
|
|
DROP VIEW user_syncs;
|
|
|
|
CREATE VIEW user_syncs AS
|
|
SELECT DISTINCT b.id AS recorded_track_id,
|
|
CAST(NULL as BIGINT) AS mix_id,
|
|
CAST(NULL as BIGINT) as quick_mix_id,
|
|
b.id AS unified_id,
|
|
a.user_id AS user_id,
|
|
b.fully_uploaded,
|
|
recordings.created_at AS created_at,
|
|
recordings.id AS recording_id
|
|
FROM recorded_tracks a INNER JOIN recordings ON a.recording_id = recordings.id AND duration IS NOT NULL AND all_discarded = FALSE AND deleted = FALSE INNER JOIN recorded_tracks b ON a.recording_id = b.recording_id
|
|
UNION ALL
|
|
SELECT CAST(NULL as BIGINT) AS recorded_track_id,
|
|
mixes.id AS mix_id,
|
|
CAST(NULL as BIGINT) AS quick_mix_id,
|
|
mixes.id AS unified_id,
|
|
claimed_recordings.user_id AS user_id,
|
|
NULL as fully_uploaded,
|
|
recordings.created_at AS created_at,
|
|
recordings.id AS recording_id
|
|
FROM mixes INNER JOIN recordings ON mixes.recording_id = recordings.id INNER JOIN claimed_recordings ON recordings.id = claimed_recordings.recording_id WHERE claimed_recordings.discarded = FALSE AND deleted = FALSE
|
|
UNION ALL
|
|
SELECT CAST(NULL as BIGINT) AS recorded_track_id,
|
|
CAST(NULL as BIGINT) AS mix_id,
|
|
quick_mixes.id AS quick_mix_id,
|
|
quick_mixes.id AS unified_id,
|
|
quick_mixes.user_id,
|
|
quick_mixes.fully_uploaded,
|
|
recordings.created_at AS created_at,
|
|
recordings.id AS recording_id
|
|
FROM quick_mixes INNER JOIN recordings ON quick_mixes.recording_id = recordings.id AND duration IS NOT NULL AND all_discarded = FALSE AND deleted = FALSE;
|