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 }