Stations made from the music, random and without end, with nothing to set up: the
mixes the listener has (genres, favourites, rediscover, new to you), everything,
one for each decade that has 30 songs by 4 artists, and the radio of an artist (their
songs, four times as likely, and the songs of the two genres most of theirs have).
Every time a station is asked for it picks other songs, with the same weights as the
mixes (taste, hearts, nothing skipped lately).
- mixes.Service.Radios and Radio; Filter has All and years, the store has Decades
- GET /radio and GET /radio/{id}, in the OpenAPI spec
- OpenSubsonic: each station is a playlist (rd-<id>, "Radio · Rock") that has other
songs every time it is opened, for apps that cannot play a radio that never ends
364 lines
11 KiB
Go
364 lines
11 KiB
Go
package storage
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
"strings"
|
|
"time"
|
|
|
|
"github.com/uptrace/bun"
|
|
|
|
"git.kmalloc.org/squid/sonda/internal/catalog"
|
|
"git.kmalloc.org/squid/sonda/internal/mixes"
|
|
)
|
|
|
|
// MixRepo reads what the mixes are made of. It implements mixes.Store and never writes.
|
|
type MixRepo struct {
|
|
db *bun.DB
|
|
cat *CatalogRepo // reads the details of the songs
|
|
taste *TasteRepo
|
|
}
|
|
|
|
// NewMixRepo builds a MixRepo.
|
|
func NewMixRepo(db *bun.DB) *MixRepo {
|
|
return &MixRepo{db: db, cat: NewCatalogRepo(db), taste: NewTasteRepo(db)}
|
|
}
|
|
|
|
// Genres returns every genre of the playable songs as the files spell it, with the number of
|
|
// songs and of main artists.
|
|
func (r *MixRepo) Genres(ctx context.Context) ([]mixes.Genre, error) {
|
|
vis, _ := visible("t", 0)
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT g.genre, COUNT(DISTINCT t.id), COUNT(DISTINCT ta.artist_id)
|
|
FROM track_genres g
|
|
JOIN tracks t ON t.id = g.track_id
|
|
LEFT JOIN track_artists ta ON ta.track_id = t.id AND ta.role = 'main'
|
|
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 := []mixes.Genre{}
|
|
for rows.Next() {
|
|
var g mixes.Genre
|
|
if err := rows.Scan(&g.Name, &g.Tracks, &g.Artists); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, g)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// GenreArtists returns the names of the artists with most playable songs among the genres, to show
|
|
// what a mix has: the names that stand for nobody ("Unknown Artist") are not shown.
|
|
func (r *MixRepo) GenreArtists(ctx context.Context, genres []string, limit int) ([]string, error) {
|
|
if len(genres) == 0 || limit <= 0 {
|
|
return []string{}, nil
|
|
}
|
|
vis, _ := visible("t", 0)
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT a.name FROM track_genres g
|
|
JOIN tracks t ON t.id = g.track_id
|
|
JOIN track_artists ta ON ta.track_id = t.id AND ta.role = 'main'
|
|
JOIN artists a ON a.id = ta.artist_id
|
|
WHERE g.genre IN (?) AND a.name NOT IN ('Unknown Artist', 'Various Artists') AND `+vis+`
|
|
GROUP BY a.id, a.name ORDER BY COUNT(DISTINCT t.id) DESC, a.id LIMIT ?`, bun.In(genres), limit)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read genre artists: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
out := []string{}
|
|
for rows.Next() {
|
|
var name string
|
|
if err := rows.Scan(&name); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, name)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// ArtistGenres returns, for each artist, how many of their playable songs have each genre.
|
|
func (r *MixRepo) ArtistGenres(ctx context.Context, artistIDs []int64) (map[int64]map[string]int, error) {
|
|
out := map[int64]map[string]int{}
|
|
if len(artistIDs) == 0 {
|
|
return out, nil
|
|
}
|
|
vis, _ := visible("t", 0)
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT ta.artist_id, g.genre, COUNT(DISTINCT t.id)
|
|
FROM track_artists ta
|
|
JOIN tracks t ON t.id = ta.track_id
|
|
JOIN track_genres g ON g.track_id = t.id
|
|
WHERE ta.artist_id IN (?) AND ta.role = 'main' AND `+vis+`
|
|
GROUP BY ta.artist_id, g.genre`, bun.In(artistIDs))
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read artist genres: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
var (
|
|
id int64
|
|
genre string
|
|
n int
|
|
)
|
|
if err := rows.Scan(&id, &genre, &n); err != nil {
|
|
return nil, err
|
|
}
|
|
if out[id] == nil {
|
|
out[id] = map[string]int{}
|
|
}
|
|
out[id][genre] = n
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// filterCondition is the condition on a song (alias t) for a filter: it has one of the genres or is by
|
|
// one of the artists (or every song, for All), and is of the years when they are set. ok is false when the filter
|
|
// asks for nothing.
|
|
func filterCondition(f mixes.Filter) (cond string, args []any, ok bool) {
|
|
years := f.MinYear > 0 || f.MaxYear > 0
|
|
var or []string
|
|
if f.All {
|
|
or = append(or, "1 = 1")
|
|
}
|
|
if len(f.Genres) > 0 {
|
|
or = append(or, "EXISTS (SELECT 1 FROM track_genres g WHERE g.track_id = t.id AND g.genre IN (?))")
|
|
args = append(args, bun.In(f.Genres))
|
|
}
|
|
if len(f.ArtistIDs) > 0 {
|
|
or = append(or, "EXISTS (SELECT 1 FROM track_artists x WHERE x.track_id = t.id AND x.role = 'main' AND x.artist_id IN (?))")
|
|
args = append(args, bun.In(f.ArtistIDs))
|
|
}
|
|
if len(or) == 0 && !years {
|
|
return "", nil, false
|
|
}
|
|
cond = "1 = 1"
|
|
if len(or) > 0 {
|
|
cond = "(" + strings.Join(or, " OR ") + ")"
|
|
}
|
|
// The years narrow the choice, whatever else it is.
|
|
if years {
|
|
lo, hi := f.MinYear, f.MaxYear
|
|
if hi == 0 {
|
|
hi = 9999
|
|
}
|
|
cond += " AND t.year BETWEEN ? AND ?"
|
|
args = append(args, lo, hi)
|
|
}
|
|
return cond, args, true
|
|
}
|
|
|
|
// Pool returns the playable songs that have one of the genres or are by one of the artists, with
|
|
// their main artist (the one with the lowest id when there are several), in the order of their ids.
|
|
func (r *MixRepo) Pool(ctx context.Context, f mixes.Filter) ([]mixes.TrackRef, error) {
|
|
cond, args, ok := filterCondition(f)
|
|
if !ok {
|
|
return []mixes.TrackRef{}, nil
|
|
}
|
|
vis, _ := visible("t", 0)
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT t.id, COALESCE((SELECT MIN(ta.artist_id) FROM track_artists ta WHERE ta.track_id = t.id AND ta.role = 'main'), 0)
|
|
FROM tracks t WHERE `+vis+` AND `+cond+` ORDER BY t.id`, args...)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read the pool: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
out := []mixes.TrackRef{}
|
|
for rows.Next() {
|
|
var ref mixes.TrackRef
|
|
if err := rows.Scan(&ref.TrackID, &ref.ArtistID); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, ref)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// Covers returns up to limit albums that have a cover and playable songs of the filter, the album with
|
|
// most of them first, one for each artist (an album with no artist stands for itself).
|
|
func (r *MixRepo) Covers(ctx context.Context, f mixes.Filter, limit int) ([]int64, error) {
|
|
cond, args, ok := filterCondition(f)
|
|
if !ok || limit <= 0 {
|
|
return []int64{}, nil
|
|
}
|
|
vis, _ := visible("t", 0)
|
|
// More albums than wanted are read: the ones of an artist that already has one are dropped below.
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT a.id, a.artist_id, COUNT(DISTINCT t.id) AS n
|
|
FROM albums a
|
|
JOIN images i ON i.kind = 'album' AND i.owner_id = a.id AND i.status = 'ok'
|
|
JOIN tracks t ON t.album_id = a.id
|
|
WHERE `+vis+` AND `+cond+`
|
|
GROUP BY a.id, a.artist_id ORDER BY n DESC, a.id LIMIT ?`, append(args, limit*10)...)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read covers: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
out := []int64{}
|
|
seen := map[int64]bool{}
|
|
for rows.Next() {
|
|
var (
|
|
id, n int64
|
|
artist sql.NullInt64
|
|
)
|
|
if err := rows.Scan(&id, &artist, &n); err != nil {
|
|
return nil, err
|
|
}
|
|
if artist.Valid {
|
|
if seen[artist.Int64] {
|
|
continue
|
|
}
|
|
seen[artist.Int64] = true
|
|
}
|
|
if len(out) < limit {
|
|
out = append(out, id)
|
|
}
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// History returns what the user did with each song they did anything with. A play counts as a
|
|
// listen when it was completed or lasted minListen; a skip counts as a skip only when it came
|
|
// before the listener had heard that much (as in the taste model).
|
|
func (r *MixRepo) History(ctx context.Context, userID int64, minListen time.Duration) (map[int64]mixes.History, error) {
|
|
ms := minListen.Milliseconds()
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT track_id,
|
|
SUM(CASE WHEN event_type = 'complete' OR duration_played_ms >= ? THEN 1 ELSE 0 END),
|
|
MAX(CASE WHEN event_type = 'complete' OR duration_played_ms >= ? THEN occurred_at END),
|
|
MAX(CASE WHEN event_type = 'skip' AND duration_played_ms < ? THEN occurred_at END)
|
|
FROM listening_events WHERE user_id = ? GROUP BY track_id`, ms, ms, ms, userID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read history: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
out := map[int64]mixes.History{}
|
|
for rows.Next() {
|
|
var (
|
|
id int64
|
|
plays int64
|
|
last, skipp sql.NullInt64
|
|
)
|
|
if err := rows.Scan(&id, &plays, &last, &skipp); err != nil {
|
|
return nil, err
|
|
}
|
|
h := mixes.History{Plays: int(plays)}
|
|
if last.Valid {
|
|
h.LastPlayed = time.Unix(last.Int64, 0)
|
|
}
|
|
if skipp.Valid {
|
|
h.SkippedAt = time.Unix(skipp.Int64, 0)
|
|
}
|
|
out[id] = h
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// ArtistNames returns the names of the artists that exist.
|
|
func (r *MixRepo) ArtistNames(ctx context.Context, ids []int64) (map[int64]string, error) {
|
|
return r.taste.ArtistNames(ctx, ids)
|
|
}
|
|
|
|
// Tracks returns the playable songs among ids, in the order given (a song asked twice comes twice).
|
|
func (r *MixRepo) Tracks(ctx context.Context, ids []int64) ([]catalog.Track, error) {
|
|
if len(ids) == 0 {
|
|
return []catalog.Track{}, nil
|
|
}
|
|
vis, _ := visible("t", 0)
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT t.id, t.title, COALESCE(t.duration_ms, 0), COALESCE(t.track_no, 0), COALESCE(t.disc_no, 0), t.album_id
|
|
FROM tracks t WHERE t.id IN (?) AND `+vis, bun.In(ids))
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read songs: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
byID := map[int64]catalog.Track{}
|
|
albumOf := map[int64]int64{}
|
|
var found []int64
|
|
page := catalog.Page[catalog.Track]{}
|
|
for rows.Next() {
|
|
var (
|
|
t catalog.Track
|
|
albumID sql.NullInt64
|
|
)
|
|
if err := rows.Scan(&t.ID, &t.Title, &t.DurationMs, &t.TrackNo, &t.DiscNo, &albumID); err != nil {
|
|
return nil, err
|
|
}
|
|
t.Artists = []catalog.ArtistRef{}
|
|
if albumID.Valid {
|
|
albumOf[t.ID] = albumID.Int64
|
|
}
|
|
page.Items = append(page.Items, t)
|
|
found = append(found, t.ID)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
if len(found) > 0 {
|
|
if err := r.cat.attachTrackDetails(ctx, &page, found, albumOf, 0); err != nil {
|
|
return nil, err
|
|
}
|
|
}
|
|
for _, t := range page.Items {
|
|
byID[t.ID] = t
|
|
}
|
|
out := make([]catalog.Track, 0, len(ids))
|
|
for _, id := range ids {
|
|
if t, ok := byID[id]; ok {
|
|
out = append(out, t)
|
|
}
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
// Hearts returns, for the songs that are favourites of the user or are on a favourite album, which of the two. It lists
|
|
// songs that can no longer be played too: they are never offered, so it does no harm.
|
|
func (r *MixRepo) Hearts(ctx context.Context, userID int64) (map[int64]mixes.Heart, error) {
|
|
out := map[int64]mixes.Heart{}
|
|
for _, q := range []struct {
|
|
sql string
|
|
mark func(*mixes.Heart)
|
|
}{
|
|
{`SELECT f.item_id FROM favorites f WHERE f.user_id = ? AND f.kind = 'track'`, func(h *mixes.Heart) { h.Track = true }},
|
|
{`SELECT t.id FROM favorites f JOIN tracks t ON t.album_id = f.item_id WHERE f.user_id = ? AND f.kind = 'album'`, func(h *mixes.Heart) { h.Album = true }},
|
|
} {
|
|
rows, err := r.db.QueryContext(ctx, q.sql, userID)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read favourites: %w", err)
|
|
}
|
|
ids, err := scanIDs(rows)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
for _, id := range ids {
|
|
h := out[id]
|
|
q.mark(&h)
|
|
out[id] = h
|
|
}
|
|
}
|
|
return out, nil
|
|
}
|
|
|
|
// Decades returns every decade that the playable songs with a year are from, with their songs and artists.
|
|
func (r *MixRepo) Decades(ctx context.Context) ([]mixes.Decade, error) {
|
|
vis, _ := visible("t", 0)
|
|
rows, err := r.db.QueryContext(ctx,
|
|
`SELECT (t.year / 10) * 10, COUNT(DISTINCT t.id),
|
|
COUNT(DISTINCT (SELECT MIN(ta.artist_id) FROM track_artists ta WHERE ta.track_id = t.id AND ta.role = 'main'))
|
|
FROM tracks t WHERE t.year > 0 AND `+vis+` GROUP BY (t.year / 10) * 10 ORDER BY 1`)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("read decades: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
out := []mixes.Decade{}
|
|
for rows.Next() {
|
|
var d mixes.Decade
|
|
if err := rows.Scan(&d.Start, &d.Tracks, &d.Artists); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, d)
|
|
}
|
|
return out, rows.Err()
|
|
}
|