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.
425 lines
15 KiB
Go
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
|
|
})
|
|
}
|