sonda/internal/storage/subsonic.go
tgiachi 74f683d26e feat: favourites of songs, albums and artists, in the API and in apps
A person can mark songs, albums and artists as favourites. They are theirs
alone: never shown to a friend, never sent anywhere. Only what can be played is
listed.

- migration 0035 favorites, internal/favorites, storage.FavoritesRepo
- PUT/DELETE /me/favorites/{kind}/{id}, GET /me/favorites/{kind} and
  GET /me/favorites/ids, in the OpenAPI spec
- catalog.Filter IDs now also reads artists by id
- OpenSubsonic: star, unstar, getStarred2, the starred date on songs, albums and
  artists, and getAlbumList2 of type starred
2026-11-16 10:51:07 +01:00

271 lines
9.2 KiB
Go

package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"path"
"strings"
"time"
"github.com/uptrace/bun"
"github.com/uptrace/bun/dialect"
"git.kmalloc.org/squid/sonda/internal/opensubsonic"
)
// SubsonicRepo reads what the OpenSubsonic API needs and the catalogue does not carry. It implements
// opensubsonic.Extras and opensubsonic.Lists. Only files that are not missing count, like everywhere else.
type SubsonicRepo struct {
db *bun.DB
// minListen is how long a play must last to count as a listen.
minListen time.Duration
}
// NewSubsonicRepo builds a SubsonicRepo. minListen is how long a play must last to count as a listen.
func NewSubsonicRepo(db *bun.DB, minListen time.Duration) *SubsonicRepo {
return &SubsonicRepo{db: db, minListen: minListen}
}
// Tracks returns the extras of the songs that can be played, with three queries whatever the number of songs.
func (r *SubsonicRepo) Tracks(ctx context.Context, ids []int64) (map[int64]opensubsonic.TrackExtra, error) {
out := map[int64]opensubsonic.TrackExtra{}
if len(ids) == 0 {
return out, nil
}
rows, err := r.db.QueryContext(ctx,
`SELECT f.track_id, f.id, f.rel_path, f.size_bytes, COALESCE(f.bitrate, 0), f.mtime_unix, COALESCE(t.year, 0)
FROM audio_files f JOIN tracks t ON t.id = f.track_id
WHERE f.track_id IN (?) AND f.missing_at IS NULL ORDER BY f.id`, bun.In(ids))
if err != nil {
return nil, fmt.Errorf("read the files of songs: %w", err)
}
defer rows.Close()
for rows.Next() {
var (
trackID, fileID, size, mtime int64
rel string
bitrate, year int
)
if err := rows.Scan(&trackID, &fileID, &rel, &size, &bitrate, &mtime, &year); err != nil {
return nil, err
}
if _, seen := out[trackID]; seen { // the first file that can be played says it
continue
}
out[trackID] = opensubsonic.TrackExtra{
Year: year, Size: size, BitrateKbps: bitrate,
Suffix: strings.TrimPrefix(strings.ToLower(path.Ext(rel)), "."), AddedAt: time.Unix(mtime, 0),
}
}
if err := rows.Err(); err != nil {
return nil, err
}
genres, err := r.genresOf(ctx, `SELECT g.track_id, g.genre FROM track_genres g WHERE g.track_id IN (?) ORDER BY g.track_id, g.genre`, ids)
if err != nil {
return nil, err
}
for id, g := range genres {
if e, ok := out[id]; ok {
e.Genres = g
out[id] = e
}
}
return out, nil
}
// Albums returns the extras of the albums that can be played.
func (r *SubsonicRepo) Albums(ctx context.Context, ids []int64) (map[int64]opensubsonic.AlbumExtra, error) {
out := map[int64]opensubsonic.AlbumExtra{}
if len(ids) == 0 {
return out, nil
}
rows, err := r.db.QueryContext(ctx,
`SELECT t.album_id, COALESCE(MIN(CASE WHEN t.year > 0 THEN t.year END), 0), MIN(f.mtime_unix)
FROM tracks t JOIN audio_files f ON f.track_id = t.id AND f.missing_at IS NULL
WHERE t.album_id IN (?) GROUP BY t.album_id`, bun.In(ids))
if err != nil {
return nil, fmt.Errorf("read the albums: %w", err)
}
defer rows.Close()
for rows.Next() {
var id, year, mtime int64
if err := rows.Scan(&id, &year, &mtime); err != nil {
return nil, err
}
out[id] = opensubsonic.AlbumExtra{Year: int(year), AddedAt: time.Unix(mtime, 0)}
}
if err := rows.Err(); err != nil {
return nil, err
}
genres, err := r.genresOf(ctx,
`SELECT DISTINCT t.album_id, g.genre FROM track_genres g JOIN tracks t ON t.id = g.track_id
WHERE t.album_id IN (?) ORDER BY t.album_id, g.genre`, ids)
if err != nil {
return nil, err
}
for id, g := range genres {
if e, ok := out[id]; ok {
e.Genres = g
out[id] = e
}
}
return out, nil
}
// genresOf runs a query of (id, genre) rows, in order, and gathers the genres of each id.
func (r *SubsonicRepo) genresOf(ctx context.Context, query string, ids []int64) (map[int64][]string, error) {
rows, err := r.db.QueryContext(ctx, query, bun.In(ids))
if err != nil {
return nil, fmt.Errorf("read genres: %w", err)
}
defer rows.Close()
out := map[int64][]string{}
for rows.Next() {
var id int64
var genre string
if err := rows.Scan(&id, &genre); err != nil {
return nil, err
}
out[id] = append(out[id], genre)
}
return out, rows.Err()
}
// Genres returns every genre of the songs that can be played, with the songs and the albums that have it.
func (r *SubsonicRepo) Genres(ctx context.Context) ([]opensubsonic.Genre, error) {
vis, _ := visible("t", 0)
rows, err := r.db.QueryContext(ctx,
`SELECT g.genre, COUNT(DISTINCT t.id), COUNT(DISTINCT t.album_id)
FROM track_genres g JOIN tracks t ON t.id = g.track_id
WHERE `+vis+` GROUP BY g.genre ORDER BY g.genre`)
if err != nil {
return nil, fmt.Errorf("read genres: %w", err)
}
defer rows.Close()
out := []opensubsonic.Genre{}
for rows.Next() {
var g opensubsonic.Genre
if err := rows.Scan(&g.Name, &g.Songs, &g.Albums); err != nil {
return nil, err
}
out = append(out, g)
}
return out, rows.Err()
}
// ErrUnknownList is a kind of list of albums that is not known.
var ErrUnknownList = errors.New("unknown kind of list")
// albumYear is the earliest year of the songs of an album, NULL when none has one.
const albumYear = "(SELECT MIN(ty.year) FROM tracks ty WHERE ty.album_id = al.id AND ty.year > 0)"
// AlbumIDs returns the ids of a page of the albums that can be played, in the order asked for.
func (r *SubsonicRepo) AlbumIDs(ctx context.Context, q opensubsonic.AlbumQuery) ([]int64, error) {
vis, visArgs := visible("t", q.LibraryID)
var w where
w.add("EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND "+vis+")", visArgs...)
join, order := "", ""
var joinArgs []any
switch q.Type {
case opensubsonic.ListRandom:
order = "RANDOM()"
case opensubsonic.ListByName:
order = r.binary("al.title_key") + ", al.id"
case opensubsonic.ListByArtist:
join = "LEFT JOIN artists ar ON ar.id = al.artist_id"
order = r.binary("COALESCE(ar.name_key, '')") + ", " + r.binary("al.title_key") + ", al.id"
case opensubsonic.ListNewest:
order = `COALESCE((SELECT MAX(af.mtime_unix) FROM audio_files af JOIN tracks tt ON tt.id = af.track_id
WHERE tt.album_id = al.id AND af.missing_at IS NULL), 0) DESC, al.id DESC`
case opensubsonic.ListByYear:
from, to := q.FromYear, q.ToYear
dir := "ASC"
if from > to {
from, to, dir = to, from, "DESC"
}
w.add(albumYear+" BETWEEN ? AND ?", from, to)
order = albumYear + " " + dir + ", " + r.binary("al.title_key") + ", al.id"
case opensubsonic.ListByGenre:
w.add("EXISTS (SELECT 1 FROM tracks tg JOIN track_genres g ON g.track_id = tg.id WHERE tg.album_id = al.id AND g.genre = ?)", strings.ToLower(q.Genre))
order = r.binary("al.title_key") + ", al.id"
case opensubsonic.ListFrequent, opensubsonic.ListRecent:
// Only the albums the listener listened to, by how many listens or by the last one.
measure, dir := "COUNT(*)", "DESC"
if q.Type == opensubsonic.ListRecent {
measure = "MAX(e.occurred_at)"
}
join = `JOIN (SELECT tt.album_id AS album_id, ` + measure + ` AS score FROM listening_events e JOIN tracks tt ON tt.id = e.track_id
WHERE e.user_id = ? AND ` + counted + ` AND tt.album_id IS NOT NULL GROUP BY tt.album_id) lis ON lis.album_id = al.id`
joinArgs = []any{q.UserID, r.minListen.Milliseconds()}
order = "lis.score " + dir + ", al.id DESC"
case opensubsonic.ListStarred:
// The favourite albums of the listener, the newest favourite first.
join = "JOIN favorites fav ON fav.item_id = al.id AND fav.kind = 'album' AND fav.user_id = ?"
joinArgs = []any{q.UserID}
order = "fav.created_at DESC, al.id DESC"
default:
return nil, fmt.Errorf("%w: %q", ErrUnknownList, q.Type)
}
args := append(append([]any{}, joinArgs...), w.args...)
args = append(args, q.Size, q.Offset)
rows, err := r.db.QueryContext(ctx,
"SELECT al.id FROM albums al "+join+" WHERE "+w.sql()+" ORDER BY "+order+" LIMIT ? OFFSET ?", args...)
if err != nil {
return nil, fmt.Errorf("list albums: %w", err)
}
return scanIDs(rows)
}
// SongIDs returns the ids of a page of the songs that can be played: by album, or in any order.
func (r *SubsonicRepo) SongIDs(ctx context.Context, q opensubsonic.SongQuery) ([]int64, error) {
vis, visArgs := visible("t", q.LibraryID)
var w where
w.add(vis, visArgs...)
if q.Genre != "" {
w.add("EXISTS (SELECT 1 FROM track_genres g WHERE g.track_id = t.id AND g.genre = ?)", strings.ToLower(q.Genre))
}
if q.FromYear > 0 {
w.add("t.year >= ?", q.FromYear)
}
if q.ToYear > 0 {
w.add("t.year <= ?", q.ToYear)
}
order := r.binary("COALESCE(al.title_key, '')") + ", COALESCE(t.album_id, 0), COALESCE(t.disc_no, 0), COALESCE(t.track_no, 0), t.id"
if q.Random {
order = "RANDOM()"
}
rows, err := r.db.QueryContext(ctx,
"SELECT t.id FROM tracks t LEFT JOIN albums al ON al.id = t.album_id WHERE "+w.sql()+" ORDER BY "+order+" LIMIT ? OFFSET ?",
append(w.args, q.Size, q.Offset)...)
if err != nil {
return nil, fmt.Errorf("list songs: %w", err)
}
return scanIDs(rows)
}
func scanIDs(rows *sql.Rows) ([]int64, error) {
defer rows.Close()
out := []int64{}
for rows.Next() {
var id int64
if err := rows.Scan(&id); err != nil {
return nil, err
}
out = append(out, id)
}
return out, rows.Err()
}
// binary orders by plain byte value, so that SQLite and PostgreSQL order alike (see CatalogRepo.binary).
func (r *SubsonicRepo) binary(expr string) string {
if r.db.Dialect().Name() == dialect.PG {
return expr + ` COLLATE "C"`
}
return expr
}