sonda/internal/storage/discovery.go
tgiachi 271c88743c feat(search): search ignores accents
- A search key apart from the key that identifies artists, albums and tracks:
  lower case, accents taken off (Latin, Greek and Cyrillic marks only, so kana
  and Hangul stay intact), and letters like ß, æ, ø, ł written plain.
- GET /artists, /albums and /tracks look in it, so "bjork" finds "Björk" and
  "strasse" finds "Straße".
- "Beyoncé" and "Beyonce" stay two artists: the identity key keeps accents.
- Migration 0016 adds the column on SQLite and PostgreSQL; RefreshMatchKeys
  fills it for existing rows at start.
- golang.org/x/text is now a direct dependency.
2026-11-12 10:17:21 +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"
"codeberg.org/squidd/sonda/internal/about"
"codeberg.org/squidd/sonda/internal/discovery"
"codeberg.org/squidd/sonda/internal/images"
"codeberg.org/squidd/sonda/internal/providers"
"codeberg.org/squidd/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
}