sonda/internal/storage/discovery.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

581 lines
21 KiB
Go

package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"strings"
"time"
"github.com/uptrace/bun"
"github.com/uptrace/bun/dialect"
"git.kmalloc.org/squid/sonda/internal/about"
"git.kmalloc.org/squid/sonda/internal/discovery"
"git.kmalloc.org/squid/sonda/internal/images"
"git.kmalloc.org/squid/sonda/internal/providers"
"git.kmalloc.org/squid/sonda/internal/providers/lastfm"
)
// What provider_fetches records a lookup as.
const (
kindSimilar = "similar"
kindTags = "tags"
)
// Answers recorded in provider_fetches.
const (
fetchOK = "ok"
fetchNotFound = "not_found"
)
// DiscoveryRepo keeps what the discovery providers said. It implements
// discovery.Store.
type DiscoveryRepo struct{ db *bun.DB }
// NewDiscoveryRepo builds a DiscoveryRepo.
func NewDiscoveryRepo(db *bun.DB) *DiscoveryRepo { return &DiscoveryRepo{db: db} }
// playableArtist is the condition "this artist can be played": credited on a
// track with a playable file, or the album artist of an album with one
// (compilations). expr is the artist id in the surrounding query.
func playableArtist(expr string) string {
vis, _ := visible("t", 0)
return `(EXISTS (SELECT 1 FROM track_artists ta JOIN tracks t ON t.id = ta.track_id WHERE ta.artist_id = ` + expr + ` AND ` + vis + `)
OR EXISTS (SELECT 1 FROM albums al JOIN tracks t ON t.album_id = al.id WHERE al.artist_id = ` + expr + ` AND ` + vis + `))`
}
// discoveredArtist is the condition "a provider named this artist as similar to
// another". Such an artist can have no files at all. A mark alone does not make an
// artist visible to everybody: that would show what other users marked.
func discoveredArtist(expr string) string {
return "EXISTS (SELECT 1 FROM artist_similar s WHERE s.similar_id = " + expr + ")"
}
// Artist returns an artist that can be looked up: one with something playable,
// or one a provider already named. Anything else is discovery.ErrNotFound.
func (r *DiscoveryRepo) Artist(ctx context.Context, id int64) (discovery.Artist, error) {
var (
a discovery.Artist
mbid sql.NullString
)
err := r.db.QueryRowContext(ctx,
"SELECT a.id, a.name, a.mbid FROM artists a WHERE a.id = ? AND ("+playableArtist("a.id")+" OR "+discoveredArtist("a.id")+")", id).
Scan(&a.ID, &a.Name, &mbid)
if errors.Is(err, sql.ErrNoRows) {
return discovery.Artist{}, discovery.ErrNotFound
}
if err != nil {
return discovery.Artist{}, fmt.Errorf("find artist: %w", err)
}
a.MBID = mbid.String
return a, nil
}
// Similar reads what was stored about an artist, best first, at most limit items.
func (r *DiscoveryRepo) Similar(ctx context.Context, id int64, provider string, limit int) (discovery.Snapshot, error) {
var (
snap discovery.Snapshot
status string
when int64
)
err := r.db.QueryRowContext(ctx,
"SELECT status, retrieved_at FROM provider_fetches WHERE provider = ? AND kind = ? AND artist_id = ?",
provider, kindSimilar, id).Scan(&status, &when)
switch {
case errors.Is(err, sql.ErrNoRows):
return snap, nil
case err != nil:
return snap, fmt.Errorf("read fetch ledger: %w", err)
}
snap.Fetched, snap.NotFound, snap.RetrievedAt = true, status == fetchNotFound, time.Unix(when, 0)
rows, err := r.db.QueryContext(ctx,
`SELECT a.id, a.name, s.rank, s.score, `+playableArtist("a.id")+`
FROM artist_similar s JOIN artists a ON a.id = s.similar_id
WHERE s.artist_id = ? AND s.provider = ? ORDER BY s.rank, a.id LIMIT ?`,
id, provider, limit)
if err != nil {
return snap, fmt.Errorf("read similar artists: %w", err)
}
defer rows.Close()
snap.Items = []discovery.Item{}
for rows.Next() {
var it discovery.Item
if err := rows.Scan(&it.Artist.ID, &it.Artist.Name, &it.Rank, &it.Score, &it.InLibrary); err != nil {
return snap, err
}
snap.Items = append(snap.Items, it)
}
return snap, rows.Err()
}
// SaveSimilar replaces what is stored about an artist and records the answer.
//
// Each candidate is matched to an artist that is already known, or becomes a new
// artist without files. Two artists are the same when their MusicBrainz ids are
// equal, or when their names match and at least one side has no id: an id that
// is missing never says "different", and one that differs always does. An id
// from a provider is never written onto an existing artist, because nobody
// checked it. Artists created here are kept when the list is replaced later.
func (r *DiscoveryRepo) SaveSimilar(ctx context.Context, id int64, provider string, at time.Time, found bool, cands []lastfm.Similar) error {
return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
// Two requests can name the same new artist at once; nothing in the schema
// forbids two rows with one name, so they take turns.
if err := lockArtistWrites(ctx, tx); err != nil {
return err
}
if err := requireArtist(ctx, tx, id); err != nil {
return err
}
ids, err := resolveArtists(ctx, tx, id, cands)
if err != nil {
return err
}
// Remember who was named before, to remove the artists nobody names now.
var before []int64
if err := tx.NewRaw("SELECT similar_id FROM artist_similar WHERE artist_id = ? AND provider = ?", id, provider).Scan(ctx, &before); err != nil {
return fmt.Errorf("read the old list: %w", err)
}
if _, err := tx.ExecContext(ctx, "DELETE FROM artist_similar WHERE artist_id = ? AND provider = ?", id, provider); err != nil {
return fmt.Errorf("clear similar artists: %w", err)
}
rank := 0
for i, c := range cands {
if ids[i] == 0 {
continue // the artist itself, or already listed above
}
rank++
if _, err := tx.ExecContext(ctx,
"INSERT INTO artist_similar (artist_id, provider, similar_id, rank, score) VALUES (?, ?, ?, ?, ?)",
id, provider, ids[i], rank, c.Match); err != nil {
return fmt.Errorf("insert similar artist: %w", err)
}
}
status := fetchOK
if !found {
status = fetchNotFound
}
if _, err := tx.ExecContext(ctx,
`INSERT INTO provider_fetches (provider, kind, artist_id, status, retrieved_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT (provider, kind, artist_id) DO UPDATE SET status = excluded.status, retrieved_at = excluded.retrieved_at`,
provider, kindSimilar, id, status, at.Unix()); err != nil {
return fmt.Errorf("record fetch: %w", err)
}
if len(before) > 0 {
if _, err := removeUnnamedArtists(ctx, tx, true, "id IN (?) AND ", bun.In(before)); err != nil {
return err
}
}
return nil
})
}
// removeUnnamedArtists deletes the discovered artists that no list names, among
// those matching the extra condition first, and then, over and over until nothing
// more goes, among all of them: removing an artist removes the list it was the
// source of, which can leave the artists only that list named without anyone
// naming them. Real artists (any that a file ever matched) are never touched.
// With keepMarked, neither are the artists some user marked (wishlist or not
// interested): they would come back as another row, with another id, the next
// time a provider names them. Taking a provider's data back does not keep them.
// Tags and records of a removed artist go with it. The caller holds the artist
// lock, so nothing is adopted or named while this runs.
func removeUnnamedArtists(ctx context.Context, tx bun.Tx, keepMarked bool, extra string, args ...any) (int, error) {
total := 0
for pass := 0; pass < maxCleanupPasses; pass++ {
cond, condArgs := "", []any(nil)
if pass == 0 {
cond, condArgs = extra, args
}
keep := ""
if keepMarked {
keep = " AND NOT EXISTS (SELECT 1 FROM artist_feedback f WHERE f.artist_id = artists.id)"
}
res, err := tx.ExecContext(ctx,
"DELETE FROM artists WHERE "+cond+`discovered = ?
AND NOT EXISTS (SELECT 1 FROM artist_similar s WHERE s.similar_id = artists.id)`+keep,
append(condArgs, true)...)
if err != nil {
return total, fmt.Errorf("remove artists nobody names: %w", err)
}
n, _ := res.RowsAffected()
total += int(n)
if n == 0 {
break // nothing went, so nothing is left unnamed by it
}
}
return total, nil
}
// maxCleanupPasses bounds how deep a chain of discovered artists is followed.
const maxCleanupPasses = 20
// resolveArtists returns the artist id of each candidate, creating the ones that
// are not known. A candidate that is the artist itself, or an artist already
// seen earlier in the list, gets 0.
func resolveArtists(ctx context.Context, tx bun.Tx, self int64, cands []lastfm.Similar) ([]int64, error) {
type known struct {
id int64
mbid string
}
var mbids, keys []string
for _, c := range cands {
if m := cleanMBID(c.MBID); m != "" {
mbids = append(mbids, m)
}
keys = append(keys, matchKey(c.Name))
}
byMBID := map[string]int64{}
byKey := map[string][]known{}
if len(cands) > 0 {
// Two conditions with an empty list would match everything or nothing,
// depending on the database: always send at least one value.
if len(mbids) == 0 {
mbids = []string{""}
}
rows, err := tx.QueryContext(ctx,
"SELECT id, mbid, name_key FROM artists WHERE mbid IN (?) OR name_key IN (?) ORDER BY id",
bun.In(mbids), bun.In(keys))
if err != nil {
return nil, fmt.Errorf("find artists: %w", err)
}
defer rows.Close()
for rows.Next() {
var (
id int64
mbid sql.NullString
nameKey string
)
if err := rows.Scan(&id, &mbid, &nameKey); err != nil {
return nil, err
}
if mbid.String != "" {
byMBID[strings.ToLower(mbid.String)] = id
}
byKey[nameKey] = append(byKey[nameKey], known{id, strings.ToLower(mbid.String)})
}
if err := rows.Err(); err != nil {
return nil, err
}
}
out := make([]int64, len(cands))
used := map[int64]bool{self: true}
for i, c := range cands {
mbid, key := cleanMBID(c.MBID), matchKey(c.Name)
var id int64
if mbid != "" {
id = byMBID[mbid]
}
if id == 0 {
for _, k := range byKey[key] {
if mbid == "" || k.mbid == "" {
id = k.id
break
}
}
}
if id == 0 {
var err error
if id, err = insertDiscovered(ctx, tx, c.Name, mbid); err != nil {
return nil, err
}
if mbid != "" {
byMBID[mbid] = id
}
byKey[key] = append(byKey[key], known{id, mbid})
}
if used[id] {
continue
}
used[id] = true
out[i] = id
}
return out, nil
}
// insertDiscovered creates an artist that exists only because a provider named it.
func insertDiscovered(ctx context.Context, tx bun.Tx, name, mbid string) (int64, error) {
var m sql.NullString
if mbid != "" {
m = sql.NullString{String: mbid, Valid: true}
}
var id int64
err := tx.QueryRowContext(ctx,
"INSERT INTO artists (mbid, name, name_key, search_key, discovered) VALUES (?, ?, ?, ?, ?) RETURNING id", m, name, matchKey(name), searchKey(name), true).Scan(&id)
if err != nil {
return 0, fmt.Errorf("insert artist: %w", err)
}
return id, nil
}
// requireArtist returns discovery.ErrNotFound when the artist is gone, as it can be
// when it was removed while a provider was being asked about it.
func requireArtist(ctx context.Context, tx bun.Tx, id int64) error {
var one int
err := tx.QueryRowContext(ctx, "SELECT 1 FROM artists WHERE id = ?", id).Scan(&one)
if errors.Is(err, sql.ErrNoRows) {
return discovery.ErrNotFound
}
if err != nil {
return fmt.Errorf("check artist: %w", err)
}
return nil
}
// artistWritesLock is the key of the PostgreSQL advisory lock that serializes the
// creation of artists. SQLite needs none: its transactions already run one at a time.
const artistWritesLock = 0x534f4e4441 // "SONDA"
// lockArtistWrites waits for any other transaction that is creating artists and
// holds the turn until this one ends.
func lockArtistWrites(ctx context.Context, tx bun.Tx) error {
if tx.Dialect().Name() != dialect.PG {
return nil
}
if _, err := tx.ExecContext(ctx, "SELECT pg_advisory_xact_lock(?)", artistWritesLock); err != nil {
return fmt.Errorf("lock artist writes: %w", err)
}
return nil
}
// cleanMBID lower-cases an id the way it is stored.
func cleanMBID(s string) string { return strings.ToLower(strings.TrimSpace(s)) }
// Tags reads the tags a provider gave to an artist, heaviest first.
func (r *DiscoveryRepo) Tags(ctx context.Context, id int64, provider string) (discovery.TagSnapshot, error) {
var (
snap discovery.TagSnapshot
status string
when int64
)
err := r.db.QueryRowContext(ctx,
"SELECT status, retrieved_at FROM provider_fetches WHERE provider = ? AND kind = ? AND artist_id = ?",
provider, kindTags, id).Scan(&status, &when)
switch {
case errors.Is(err, sql.ErrNoRows):
return snap, nil
case err != nil:
return snap, fmt.Errorf("read fetch ledger: %w", err)
}
snap.Fetched, snap.NotFound, snap.RetrievedAt = true, status == fetchNotFound, time.Unix(when, 0)
rows, err := r.db.QueryContext(ctx,
"SELECT tag, weight, genre FROM artist_tags WHERE artist_id = ? AND provider = ? ORDER BY weight DESC, tag", id, provider)
if err != nil {
return snap, fmt.Errorf("read tags: %w", err)
}
defer rows.Close()
snap.Tags = []discovery.Tag{}
for rows.Next() {
var t discovery.Tag
if err := rows.Scan(&t.Name, &t.Weight, &t.Genre); err != nil {
return snap, err
}
snap.Tags = append(snap.Tags, t)
}
return snap, rows.Err()
}
// SaveTags replaces the tags a provider gave to an artist and records the answer.
func (r *DiscoveryRepo) SaveTags(ctx context.Context, id int64, provider string, at time.Time, found bool, tags []discovery.Tag) error {
return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
// Artists can be removed by a purge or a cleanup: take the same turn they do.
if err := lockArtistWrites(ctx, tx); err != nil {
return err
}
if err := requireArtist(ctx, tx, id); err != nil {
return err
}
if _, err := tx.ExecContext(ctx, "DELETE FROM artist_tags WHERE artist_id = ? AND provider = ?", id, provider); err != nil {
return fmt.Errorf("clear tags: %w", err)
}
seen := map[string]bool{}
for _, t := range tags {
if t.Name == "" || seen[t.Name] {
continue
}
seen[t.Name] = true
if _, err := tx.ExecContext(ctx,
"INSERT INTO artist_tags (artist_id, provider, tag, weight, genre) VALUES (?, ?, ?, ?, ?)",
id, provider, t.Name, t.Weight, t.Genre); err != nil {
return fmt.Errorf("insert tag: %w", err)
}
}
status := fetchOK
if !found {
status = fetchNotFound
}
if _, err := tx.ExecContext(ctx,
`INSERT INTO provider_fetches (provider, kind, artist_id, status, retrieved_at) VALUES (?, ?, ?, ?, ?)
ON CONFLICT (provider, kind, artist_id) DO UPDATE SET status = excluded.status, retrieved_at = excluded.retrieved_at`,
provider, kindTags, id, status, at.Unix()); err != nil {
return fmt.Errorf("record fetch: %w", err)
}
return nil
})
}
// MaxFileGenres bounds the genres returned for one artist.
const MaxFileGenres = 50
// Description returns what is stored about the description of an artist.
func (r *DiscoveryRepo) Description(ctx context.Context, id int64) (about.Snapshot, error) {
var (
snap about.Snapshot
status string
when int64
)
err := r.db.QueryRowContext(ctx,
"SELECT status, language, title, short, body, url, retrieved_at FROM artist_descriptions WHERE artist_id = ?", id).
Scan(&status, &snap.Description.Language, &snap.Description.Title, &snap.Description.Short,
&snap.Description.Text, &snap.Description.URL, &when)
switch {
case errors.Is(err, sql.ErrNoRows):
return about.Snapshot{}, nil
case err != nil:
return about.Snapshot{}, fmt.Errorf("read description: %w", err)
}
snap.Fetched, snap.None, snap.RetrievedAt = true, status == "none", time.Unix(when, 0)
if snap.None {
snap.Description = about.Description{}
}
return snap, nil
}
// SaveDescription replaces the description of an artist; nil records that there is
// none. An artist removed meanwhile is discovery.ErrNotFound.
func (r *DiscoveryRepo) SaveDescription(ctx context.Context, id int64, at time.Time, wikidataID string, d *about.Description) error {
return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
// Artists can be removed by a purge or a cleanup: take the same turn they do.
if err := lockArtistWrites(ctx, tx); err != nil {
return err
}
if err := requireArtist(ctx, tx, id); err != nil {
return err
}
status, desc := "none", about.Description{}
if d != nil {
status, desc = "ok", *d
}
if _, err := tx.ExecContext(ctx,
`INSERT INTO artist_descriptions (artist_id, status, wikidata_id, language, title, short, body, url, retrieved_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT (artist_id) DO UPDATE SET status = excluded.status, wikidata_id = excluded.wikidata_id,
language = excluded.language, title = excluded.title, short = excluded.short, body = excluded.body,
url = excluded.url, retrieved_at = excluded.retrieved_at`,
id, status, wikidataID, desc.Language, desc.Title, desc.Short, desc.Text, desc.URL, at.Unix()); err != nil {
return fmt.Errorf("save description: %w", err)
}
return nil
})
}
// FileGenres returns the genres of the artist's own playable tracks, each
// weighted by how many tracks have it, most first. It comes from the tags of the
// user's files and asks nobody. An artist without files has none.
func (r *DiscoveryRepo) FileGenres(ctx context.Context, artistID int64) ([]discovery.Tag, error) {
vis, visArgs := visible("t", 0)
name := "g.genre"
if r.db.Dialect().Name() == dialect.PG {
name += ` COLLATE "C"` // the same order as SQLite, whatever the database locale
}
rows, err := r.db.QueryContext(ctx,
`SELECT g.genre, COUNT(DISTINCT t.id) AS n
FROM track_genres g JOIN tracks t ON t.id = g.track_id JOIN track_artists ta ON ta.track_id = t.id
WHERE ta.artist_id = ? AND `+vis+`
GROUP BY g.genre ORDER BY n DESC, `+name+` LIMIT ?`,
append(append([]any{artistID}, visArgs...), MaxFileGenres)...)
if err != nil {
return nil, fmt.Errorf("read genres: %w", err)
}
defer rows.Close()
out := []discovery.Tag{}
for rows.Next() {
var (
t discovery.Tag
n int
)
if err := rows.Scan(&t.Name, &n); err != nil {
return nil, err
}
t.Weight, t.Genre = float64(n), true
out = append(out, t)
}
return out, rows.Err()
}
// Purge removes everything a provider gave: its similar-artist lists, its tags and
// the records of when it was asked. Then it removes the artists that only that
// data named and that nobody has files for. The user's own artists, music and
// listening history are not touched.
func (r *DiscoveryRepo) Purge(ctx context.Context, provider string) (providers.Purged, error) {
var out providers.Purged
err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
// Artists are removed here: take the turn the scanner and discovery take,
// or a file could be matched to an artist this transaction then deletes.
if err := lockArtistWrites(ctx, tx); err != nil {
return err
}
// Only the artists this provider's lists named are candidates for removal:
// another provider's data, or artists kept by a user's mark, are not its to take.
var named []int64
if err := tx.NewRaw("SELECT DISTINCT similar_id FROM artist_similar WHERE provider = ?", provider).Scan(ctx, &named); err != nil {
return fmt.Errorf("read what %s named: %w", provider, err)
}
for _, step := range []struct {
what string
query string
into *int
}{
{"similar artists", "DELETE FROM artist_similar WHERE provider = ?", &out.SimilarArtists},
{"tags", "DELETE FROM artist_tags WHERE provider = ?", &out.Tags},
{"records", "DELETE FROM provider_fetches WHERE provider = ?", new(int)},
} {
res, err := tx.ExecContext(ctx, step.query, provider)
if err != nil {
return fmt.Errorf("purge %s: %w", step.what, err)
}
n, _ := res.RowsAffected()
*step.into = int(n)
}
if provider == providers.CoverArtArchive { // the covers that came from the archive
res, err := tx.ExecContext(ctx, "DELETE FROM images WHERE kind = 'album' AND source = ?", images.SourceCoverArt)
if err != nil {
return fmt.Errorf("purge covers: %w", err)
}
n, _ := res.RowsAffected()
out.Images = int(n)
}
if provider == providers.Commons { // the pictures of artists are all Commons'
res, err := tx.ExecContext(ctx, "DELETE FROM images WHERE kind = 'artist'")
if err != nil {
return fmt.Errorf("purge pictures: %w", err)
}
n, _ := res.RowsAffected()
out.Images = int(n)
}
if provider == providers.Wikipedia { // the descriptions are all Wikipedia's
res, err := tx.ExecContext(ctx, "DELETE FROM artist_descriptions")
if err != nil {
return fmt.Errorf("purge descriptions: %w", err)
}
n, _ := res.RowsAffected()
out.Descriptions = int(n)
}
// In chunks: a provider can have named more artists than a query takes.
const chunk = 500
for start := 0; start < len(named); start += chunk {
end := min(start+chunk, len(named))
n, err := removeUnnamedArtists(ctx, tx, false, "id IN (?) AND ", bun.In(named[start:end]))
out.Artists += n
if err != nil {
return err
}
}
return nil
})
return out, err
}