74 lines
2.5 KiB
SQL
74 lines
2.5 KiB
SQL
-- name: EnqueueScrobble :execrows
|
|
-- Idempotent: if a row exists for this play_event_id, ON CONFLICT does nothing
|
|
-- and the affected-rows count is 0. Caller (MaybeEnqueue) doesn't treat 0 as
|
|
-- an error.
|
|
INSERT INTO scrobble_queue (user_id, play_event_id, status)
|
|
VALUES ($1, $2, 'pending')
|
|
ON CONFLICT (play_event_id) DO NOTHING;
|
|
|
|
-- name: ListPendingScrobbles :many
|
|
-- Worker pulls up to N pending rows whose next_attempt_at has passed.
|
|
-- Joins play_events + tracks/albums/artists + users so the worker can
|
|
-- assemble the LB Listen payload in one round-trip.
|
|
SELECT
|
|
sq.id AS queue_id,
|
|
sq.user_id AS user_id,
|
|
sq.play_event_id AS play_event_id,
|
|
sq.attempts AS attempts,
|
|
u.listenbrainz_token AS lb_token,
|
|
u.listenbrainz_enabled AS lb_enabled,
|
|
pe.started_at AS started_at,
|
|
t.title AS track_title,
|
|
t.duration_ms AS track_duration_ms,
|
|
t.mbid AS track_mbid,
|
|
al.title AS album_title,
|
|
al.mbid AS album_mbid,
|
|
ar.name AS artist_name,
|
|
ar.mbid AS artist_mbid
|
|
FROM scrobble_queue sq
|
|
JOIN users u ON u.id = sq.user_id
|
|
JOIN play_events pe ON pe.id = sq.play_event_id
|
|
JOIN tracks t ON t.id = pe.track_id
|
|
JOIN albums al ON al.id = t.album_id
|
|
JOIN artists ar ON ar.id = t.artist_id
|
|
WHERE sq.status = 'pending'
|
|
AND sq.next_attempt_at <= now()
|
|
ORDER BY sq.next_attempt_at
|
|
LIMIT $1;
|
|
|
|
-- name: MarkScrobbleSent :exec
|
|
-- Successful submit. Delete the queue row AND stamp play_events.scrobbled_at
|
|
-- in one statement via a CTE so partial failure is impossible.
|
|
WITH del AS (
|
|
DELETE FROM scrobble_queue
|
|
WHERE scrobble_queue.id = $1
|
|
RETURNING play_event_id
|
|
)
|
|
UPDATE play_events
|
|
SET scrobbled_at = now()
|
|
WHERE play_events.id = (SELECT play_event_id FROM del);
|
|
|
|
-- name: RescheduleScrobble :exec
|
|
-- After a transient failure: increment attempts, schedule next attempt,
|
|
-- record the error.
|
|
UPDATE scrobble_queue
|
|
SET attempts = attempts + 1,
|
|
next_attempt_at = $2,
|
|
last_error = $3
|
|
WHERE id = $1;
|
|
|
|
-- name: MarkScrobbleRetryAfter :exec
|
|
-- After a 429: schedule next attempt per LB's Retry-After header, but do NOT
|
|
-- increment attempts (the server told us to wait, not that we failed).
|
|
UPDATE scrobble_queue
|
|
SET next_attempt_at = $2
|
|
WHERE id = $1;
|
|
|
|
-- name: MarkScrobbleFailed :exec
|
|
-- After a permanent failure (4xx, exhausted retries, auth) — mark failed
|
|
-- and keep the row for diagnostics.
|
|
UPDATE scrobble_queue
|
|
SET status = 'failed',
|
|
last_error = $2
|
|
WHERE id = $1;
|