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
271 lines
9.2 KiB
Go
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
|
|
}
|