sonda/internal/storage/playlists.go
tgiachi f900a22904 feat(playlists): a user keeps playlists of tracks
Each user can make playlists: a name (1 to 200 characters), an optional
description and an ordered list of tracks, where a track may appear more than once
(each place has its own item id). The API makes, lists, renames and deletes
playlists, and adds tracks (at the end or at a position), lists them, moves one and
removes one; positions count from 0 without gaps.

A playlist is its owner's alone: somebody else's answers playlist_not_found (404),
as one that does not exist. A track whose files were deleted or moved stays in the
playlist and is listed with available false. Deleting a user deletes their
playlists. Limits: 1,000 playlists per user, 10,000 tracks per playlist, 500 tracks
per request.

Changes of one playlist are serialised by locking its row, so two requests at once
keep the positions unique on PostgreSQL too. Migration 0019, OpenAPI spec, contract
test, README and the web API types follow. The web app comes separately.

Closes #89
2026-11-12 16:55:25 +01:00

377 lines
13 KiB
Go

package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"time"
"github.com/uptrace/bun"
"codeberg.org/squidd/sonda/internal/catalog"
"codeberg.org/squidd/sonda/internal/playlists"
)
type playlistRow struct {
bun.BaseModel `bun:"table:playlists"`
ID int64 `bun:"id,pk,autoincrement"`
UserID int64 `bun:"user_id,notnull"`
Name string `bun:"name,notnull"`
Description string `bun:"description,notnull"`
CreatedAt int64 `bun:"created_at,notnull"`
UpdatedAt int64 `bun:"updated_at,notnull"`
}
type playlistItemRow struct {
bun.BaseModel `bun:"table:playlist_items"`
ID int64 `bun:"id,pk,autoincrement"`
PlaylistID int64 `bun:"playlist_id,notnull"`
TrackID int64 `bun:"track_id,notnull"`
Position int `bun:"position,notnull"`
AddedAt int64 `bun:"added_at,notnull"`
}
// PlaylistRepo stores playlists. It implements playlists.Store.
type PlaylistRepo struct {
db *bun.DB
cat *CatalogRepo // reads the tracks of the items
}
// NewPlaylistRepo builds a PlaylistRepo.
func NewPlaylistRepo(db *bun.DB) *PlaylistRepo { return &PlaylistRepo{db: db, cat: NewCatalogRepo(db)} }
// summarySelect reads playlists with the number of their items and how long they are, over all
// the items, available or not.
const summarySelect = `SELECT p.id, p.name, p.description, p.created_at, p.updated_at,
COUNT(i.id), COALESCE(SUM(t.duration_ms), 0)
FROM playlists p
LEFT JOIN playlist_items i ON i.playlist_id = p.id
LEFT JOIN tracks t ON t.id = i.track_id`
func scanSummary(rows interface{ Scan(...any) error }) (playlists.Playlist, error) {
var (
p playlists.Playlist
created, updated int64
count, durationSum int64
)
if err := rows.Scan(&p.ID, &p.Name, &p.Description, &created, &updated, &count, &durationSum); err != nil {
return playlists.Playlist{}, err
}
p.CreatedAt, p.UpdatedAt = time.Unix(created, 0), time.Unix(updated, 0)
p.TrackCount, p.DurationMs = int(count), durationSum
return p, nil
}
// summary reads one playlist of a user, or playlists.ErrNotFound.
func summary(ctx context.Context, db bun.IDB, userID, id int64) (playlists.Playlist, error) {
p, err := scanSummary(db.QueryRowContext(ctx, summarySelect+" WHERE p.id = ? AND p.user_id = ? GROUP BY p.id", id, userID))
if errors.Is(err, sql.ErrNoRows) {
return playlists.Playlist{}, playlists.ErrNotFound
}
if err != nil {
return playlists.Playlist{}, fmt.Errorf("read playlist: %w", err)
}
return p, nil
}
// touch sets the updated time of a playlist of the user and so takes its row lock, which is what
// serialises two changes of the same playlist on PostgreSQL. It gives ErrNotFound when the
// playlist is not the user's.
func touch(ctx context.Context, tx bun.Tx, userID, id int64, now time.Time) error {
res, err := tx.ExecContext(ctx, "UPDATE playlists SET updated_at = ? WHERE id = ? AND user_id = ?", now.Unix(), id, userID)
if err != nil {
return fmt.Errorf("lock playlist: %w", err)
}
if n, _ := res.RowsAffected(); n == 0 {
return playlists.ErrNotFound
}
return nil
}
func countItems(ctx context.Context, tx bun.Tx, id int64) (int, error) {
var n int
if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM playlist_items WHERE playlist_id = ?", id).Scan(&n); err != nil {
return 0, fmt.Errorf("count items: %w", err)
}
return n, nil
}
// Create makes an empty playlist.
func (r *PlaylistRepo) Create(ctx context.Context, userID int64, name, description string, now time.Time) (playlists.Playlist, error) {
var out playlists.Playlist
err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
// The row of the user is the lock that makes the count below hold for two requests at once.
res, err := tx.ExecContext(ctx, "UPDATE users SET is_admin = is_admin WHERE id = ?", userID)
if err != nil {
return fmt.Errorf("lock user: %w", err)
}
if n, _ := res.RowsAffected(); n == 0 {
return playlists.ErrNotFound // the account is gone
}
var have int
if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM playlists WHERE user_id = ?", userID).Scan(&have); err != nil {
return fmt.Errorf("count playlists: %w", err)
}
if have >= playlists.MaxPlaylists {
return fmt.Errorf("%w: a user can have %d playlists", playlists.ErrTooMany, playlists.MaxPlaylists)
}
row := &playlistRow{UserID: userID, Name: name, Description: description, CreatedAt: now.Unix(), UpdatedAt: now.Unix()}
if _, err := tx.NewInsert().Model(row).Exec(ctx); err != nil {
return fmt.Errorf("insert playlist: %w", err)
}
out, err = summary(ctx, tx, userID, row.ID)
return err
})
return out, err
}
// List returns a page of the user's playlists, the one changed last first.
func (r *PlaylistRepo) List(ctx context.Context, userID int64, limit, offset int) ([]playlists.Playlist, int, error) {
var total int
if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM playlists WHERE user_id = ?", userID).Scan(&total); err != nil {
return nil, 0, fmt.Errorf("count playlists: %w", err)
}
out := []playlists.Playlist{}
if offset >= total {
return out, total, nil
}
rows, err := r.db.QueryContext(ctx,
summarySelect+" WHERE p.user_id = ? GROUP BY p.id ORDER BY p.updated_at DESC, p.id DESC LIMIT ? OFFSET ?", userID, limit, offset)
if err != nil {
return nil, 0, fmt.Errorf("list playlists: %w", err)
}
defer rows.Close()
for rows.Next() {
p, err := scanSummary(rows)
if err != nil {
return nil, 0, err
}
out = append(out, p)
}
return out, total, rows.Err()
}
// Get returns a playlist of the user.
func (r *PlaylistRepo) Get(ctx context.Context, userID, id int64) (playlists.Playlist, error) {
return summary(ctx, r.db, userID, id)
}
// Update changes the name and/or the description that are not nil.
func (r *PlaylistRepo) Update(ctx context.Context, userID, id int64, name, description *string, now time.Time) (playlists.Playlist, error) {
var out playlists.Playlist
err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
q := tx.NewUpdate().Model((*playlistRow)(nil)).Set("updated_at = ?", now.Unix()).Where("id = ?", id).Where("user_id = ?", userID)
if name != nil {
q = q.Set("name = ?", *name)
}
if description != nil {
q = q.Set("description = ?", *description)
}
res, err := q.Exec(ctx)
if err := affectedOrNotFoundPlaylist(res, err); err != nil {
return err
}
out, err = summary(ctx, tx, userID, id)
return err
})
return out, err
}
func affectedOrNotFoundPlaylist(res sql.Result, err error) error {
if err != nil {
return fmt.Errorf("change playlist: %w", err)
}
if n, _ := res.RowsAffected(); n == 0 {
return playlists.ErrNotFound
}
return nil
}
// Delete removes a playlist and, through the foreign key, its items.
func (r *PlaylistRepo) Delete(ctx context.Context, userID, id int64) error {
res, err := r.db.ExecContext(ctx, "DELETE FROM playlists WHERE id = ? AND user_id = ?", id, userID)
return affectedOrNotFoundPlaylist(res, err)
}
// Items returns a page of the playlist in order. The tracks are read with the same three queries
// as any list of tracks, whatever the page size, and also when a track has no file to play.
func (r *PlaylistRepo) Items(ctx context.Context, userID, id int64, limit, offset int) ([]playlists.Item, int, error) {
p, err := summary(ctx, r.db, userID, id)
if err != nil {
return nil, 0, err
}
items := []playlists.Item{}
if offset >= p.TrackCount {
return items, p.TrackCount, nil
}
rows, err := r.db.QueryContext(ctx,
`SELECT i.id, i.position, i.added_at, t.id, t.title, COALESCE(t.duration_ms, 0), COALESCE(t.track_no, 0),
COALESCE(t.disc_no, 0), t.album_id,
EXISTS (SELECT 1 FROM audio_files f WHERE f.track_id = t.id AND f.missing_at IS NULL)
FROM playlist_items i JOIN tracks t ON t.id = i.track_id
WHERE i.playlist_id = ? ORDER BY i.position, i.id LIMIT ? OFFSET ?`, id, limit, offset)
if err != nil {
return nil, 0, fmt.Errorf("list items: %w", err)
}
defer rows.Close()
albumOf := map[int64]int64{}
page := catalog.Page[catalog.Track]{}
var trackIDs []int64
seen := map[int64]bool{}
for rows.Next() {
var (
it playlists.Item
added int64
albumID sql.NullInt64
)
t := &it.Track
if err := rows.Scan(&it.ID, &it.Position, &added, &t.ID, &t.Title, &t.DurationMs, &t.TrackNo, &t.DiscNo, &albumID, &it.Available); err != nil {
return nil, 0, err
}
it.AddedAt = time.Unix(added, 0)
t.Artists = []catalog.ArtistRef{}
if albumID.Valid {
albumOf[t.ID] = albumID.Int64
}
if !seen[t.ID] {
seen[t.ID] = true
trackIDs = append(trackIDs, t.ID)
}
items = append(items, it)
page.Items = append(page.Items, *t)
}
if err := rows.Err(); err != nil {
return nil, 0, err
}
if err := r.cat.attachTrackDetails(ctx, &page, trackIDs, albumOf, 0); err != nil {
return nil, 0, err
}
for i := range items {
items[i].Track = page.Items[i]
}
return items, p.TrackCount, nil
}
// AddTracks puts the tracks at position (at the end when nil), in the order given.
func (r *PlaylistRepo) AddTracks(ctx context.Context, userID, id int64, trackIDs []int64, position *int, now time.Time) (playlists.Playlist, error) {
var out playlists.Playlist
err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
if err := touch(ctx, tx, userID, id, now); err != nil {
return err
}
have, err := countItems(ctx, tx, id)
if err != nil {
return err
}
at := have
if position != nil {
at = *position
}
if at > have {
return fmt.Errorf("%w: the playlist has %d tracks", playlists.ErrPosition, have)
}
if have+len(trackIDs) > playlists.MaxItems {
return fmt.Errorf("%w: a playlist can have %d tracks", playlists.ErrTooMany, playlists.MaxItems)
}
distinct := map[int64]bool{}
var ids []int64
for _, t := range trackIDs {
if !distinct[t] {
distinct[t] = true
ids = append(ids, t)
}
}
var known int
if err := tx.QueryRowContext(ctx, "SELECT COUNT(*) FROM tracks WHERE id IN (?)", bun.In(ids)).Scan(&known); err != nil {
return fmt.Errorf("check tracks: %w", err)
}
if known != len(ids) {
return playlists.ErrTrackNotFound
}
if at < have {
if _, err := tx.ExecContext(ctx, "UPDATE playlist_items SET position = position + ? WHERE playlist_id = ? AND position >= ?", len(trackIDs), id, at); err != nil {
return fmt.Errorf("make room: %w", err)
}
}
rows := make([]playlistItemRow, len(trackIDs))
for i, t := range trackIDs {
rows[i] = playlistItemRow{PlaylistID: id, TrackID: t, Position: at + i, AddedAt: now.Unix()}
}
if _, err := tx.NewInsert().Model(&rows).Exec(ctx); err != nil {
return fmt.Errorf("insert items: %w", err)
}
out, err = summary(ctx, tx, userID, id)
return err
})
return out, err
}
// RemoveItem takes an item out and closes the gap.
func (r *PlaylistRepo) RemoveItem(ctx context.Context, userID, id, itemID int64, now time.Time) error {
return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
if err := touch(ctx, tx, userID, id, now); err != nil {
return err
}
at, err := itemPosition(ctx, tx, id, itemID)
if err != nil {
return err
}
if _, err := tx.ExecContext(ctx, "DELETE FROM playlist_items WHERE id = ?", itemID); err != nil {
return fmt.Errorf("delete item: %w", err)
}
if _, err := tx.ExecContext(ctx, "UPDATE playlist_items SET position = position - 1 WHERE playlist_id = ? AND position > ?", id, at); err != nil {
return fmt.Errorf("close the gap: %w", err)
}
return nil
})
}
func itemPosition(ctx context.Context, tx bun.Tx, id, itemID int64) (int, error) {
var at int
err := tx.QueryRowContext(ctx, "SELECT position FROM playlist_items WHERE id = ? AND playlist_id = ?", itemID, id).Scan(&at)
if errors.Is(err, sql.ErrNoRows) {
return 0, playlists.ErrItemNotFound
}
if err != nil {
return 0, fmt.Errorf("read item: %w", err)
}
return at, nil
}
// MoveItem puts an item at a position.
func (r *PlaylistRepo) MoveItem(ctx context.Context, userID, id, itemID int64, position int, now time.Time) error {
return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
if err := touch(ctx, tx, userID, id, now); err != nil {
return err
}
from, err := itemPosition(ctx, tx, id, itemID)
if err != nil {
return err
}
have, err := countItems(ctx, tx, id)
if err != nil {
return err
}
if position >= have {
return fmt.Errorf("%w: the playlist has %d tracks", playlists.ErrPosition, have)
}
switch {
case from < position:
_, err = tx.ExecContext(ctx, "UPDATE playlist_items SET position = position - 1 WHERE playlist_id = ? AND position > ? AND position <= ?", id, from, position)
case from > position:
_, err = tx.ExecContext(ctx, "UPDATE playlist_items SET position = position + 1 WHERE playlist_id = ? AND position >= ? AND position < ?", id, position, from)
default:
return nil
}
if err != nil {
return fmt.Errorf("shift items: %w", err)
}
if _, err := tx.ExecContext(ctx, "UPDATE playlist_items SET position = ? WHERE id = ?", position, itemID); err != nil {
return fmt.Errorf("move item: %w", err)
}
return nil
})
}