sonda/internal/storage/playlists.go
tgiachi a880b87a9e chore: move the module path to git.kmalloc.org/squid/sonda
Migrating hosting from Codeberg to git.kmalloc.org. Renames the
module path in go.mod, every internal import, and the OCI image
source label in the Dockerfile. Build and full test suite verified
green after the rename.
2026-11-15 17:30:29 +01:00

425 lines
15 KiB
Go

package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"time"
"github.com/uptrace/bun"
"git.kmalloc.org/squid/sonda/internal/catalog"
"git.kmalloc.org/squid/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
}
// attachCovers gives each playlist the albums that stand for it: the first albums that have a cover, in the order they
// appear in the playlist, each once, up to playlists.MaxCoverAlbums. Playlists with none get an empty list.
func attachCovers(ctx context.Context, db bun.IDB, ps []playlists.Playlist) error {
if len(ps) == 0 {
return nil
}
index := map[int64]int{}
ids := make([]int64, len(ps))
for i := range ps {
ps[i].AlbumIDs = []int64{}
index[ps[i].ID] = i
ids[i] = ps[i].ID
}
rows, err := db.QueryContext(ctx,
`SELECT playlist_id, album_id FROM (
SELECT g.playlist_id, g.album_id, ROW_NUMBER() OVER (PARTITION BY g.playlist_id ORDER BY g.first, g.album_id) AS n FROM (
SELECT i.playlist_id, t.album_id, MIN(i.position) AS first
FROM playlist_items i
JOIN tracks t ON t.id = i.track_id
JOIN images im ON im.kind = 'album' AND im.owner_id = t.album_id AND im.status = 'ok'
WHERE i.playlist_id IN (?) AND t.album_id IS NOT NULL
GROUP BY i.playlist_id, t.album_id) g) r
WHERE n <= ? ORDER BY playlist_id, n`, bun.In(ids), playlists.MaxCoverAlbums)
if err != nil {
return fmt.Errorf("read the albums of playlists: %w", err)
}
defer rows.Close()
for rows.Next() {
var playlist, album int64
if err := rows.Scan(&playlist, &album); err != nil {
return err
}
ps[index[playlist]].AlbumIDs = append(ps[index[playlist]].AlbumIDs, album)
}
return rows.Err()
}
// 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)
}
one := []playlists.Playlist{p}
if err := attachCovers(ctx, db, one); err != nil {
return playlists.Playlist{}, err
}
return one[0], 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)
}
if err := rows.Err(); err != nil {
return nil, 0, err
}
rows.Close()
if err := attachCovers(ctx, r.db, out); err != nil {
return nil, 0, err
}
return out, total, nil
}
// 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
})
}