package storage import ( "context" "database/sql" "fmt" "strings" "time" "github.com/uptrace/bun" "git.kmalloc.org/squid/sonda/internal/affinity" "git.kmalloc.org/squid/sonda/internal/feedback" ) // AffinityRepo reads the plays of a person and keeps what was learned from them. It implements affinity.Store, // affinity.Library and affinity.Wishes. type AffinityRepo struct{ db *bun.DB } // NewAffinityRepo builds an AffinityRepo. func NewAffinityRepo(db *bun.DB) *AffinityRepo { return &AffinityRepo{db: db} } // counted is the condition "this listening event counts as a listen" (see POST /listens), with its one argument. const counted = "(e.event_type = 'complete' OR e.duration_played_ms >= ?)" // Plays gives every play of a person that counts as listened, once for each artist credited on it: the listening // events of the library and the plays kept from imports, leaving out the kept ones that have a listening event now. func (r *AffinityRepo) Plays(ctx context.Context, userID int64, minPlayed time.Duration) ([]affinity.Play, error) { var plays []affinity.Play rows, err := r.db.QueryContext(ctx, `SELECT a.name, e.occurred_at FROM listening_events e JOIN track_artists ta ON ta.track_id = e.track_id JOIN artists a ON a.id = ta.artist_id WHERE e.user_id = ? AND `+counted, userID, minPlayed.Milliseconds()) if err != nil { return nil, fmt.Errorf("read listens: %w", err) } defer rows.Close() for rows.Next() { var ( name string at int64 ) if err := rows.Scan(&name, &at); err != nil { return nil, err } plays = append(plays, affinity.Play{Key: searchKey(name), Name: name, At: time.Unix(at, 0)}) } if err := rows.Err(); err != nil { return nil, err } away, err := r.db.QueryContext(ctx, `SELECT o.artist, o.artist_key, o.played_at FROM outside_plays o WHERE o.user_id = ? AND NOT EXISTS (SELECT 1 FROM listening_events e WHERE e.user_id = o.user_id AND e.idempotency_key = o.idempotency_key)`, userID) if err != nil { return nil, fmt.Errorf("read outside plays: %w", err) } defer away.Close() for away.Next() { var ( name, key string at int64 ) if err := away.Scan(&name, &key, &at); err != nil { return nil, err } plays = append(plays, affinity.Play{Key: key, Name: name, At: time.Unix(at, 0)}) } return plays, away.Err() } // PlayCount says how many plays Plays would give, without reading them. func (r *AffinityRepo) PlayCount(ctx context.Context, userID int64, minPlayed time.Duration) (int, error) { var events, away int if err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM listening_events e JOIN track_artists ta ON ta.track_id = e.track_id WHERE e.user_id = ? AND `+counted, userID, minPlayed.Milliseconds()).Scan(&events); err != nil { return 0, fmt.Errorf("count listens: %w", err) } if err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM outside_plays o WHERE o.user_id = ? AND NOT EXISTS (SELECT 1 FROM listening_events e WHERE e.user_id = o.user_id AND e.idempotency_key = o.idempotency_key)`, userID).Scan(&away); err != nil { return 0, fmt.Errorf("count outside plays: %w", err) } return events + away, nil } // affinityChunk is how many rows one statement inserts: at most seven columns each. const affinityChunk = 500 type affinityArtistRow struct { bun.BaseModel `bun:"table:affinity_artists"` UserID int64 `bun:"user_id,pk"` ArtistKey string `bun:"artist_key,pk"` Name string `bun:"name,notnull"` Plays int `bun:"plays,notnull"` Sessions int `bun:"sessions,notnull"` LastPlayedAt int64 `bun:"last_played_at,notnull"` Weight float64 `bun:"weight,notnull"` } type affinityLinkRow struct { bun.BaseModel `bun:"table:affinity_links"` UserID int64 `bun:"user_id,pk"` AKey string `bun:"a_key,pk"` BKey string `bun:"b_key,pk"` Score float64 `bun:"score,notnull"` } type affinityBuildRow struct { bun.BaseModel `bun:"table:affinity_builds"` UserID int64 `bun:"user_id,pk"` BuiltAt int64 `bun:"built_at,notnull"` Plays int `bun:"plays,notnull"` Sessions int `bun:"sessions,notnull"` Artists int `bun:"artists,notnull"` Links int `bun:"links,notnull"` } // Save replaces what was learned about a person with a new model, in one transaction. The plays kept from imports // that have a listening event now are removed: the event says it better. func (r *AffinityRepo) Save(ctx context.Context, userID int64, at time.Time, m affinity.Model) error { return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { for _, table := range []string{"affinity_links", "affinity_artists", "affinity_builds"} { if _, err := tx.ExecContext(ctx, "DELETE FROM "+table+" WHERE user_id = ?", userID); err != nil { return fmt.Errorf("clear %s: %w", table, err) } } for start := 0; start < len(m.Artists); start += affinityChunk { end := min(start+affinityChunk, len(m.Artists)) rows := make([]affinityArtistRow, 0, end-start) for _, a := range m.Artists[start:end] { rows = append(rows, affinityArtistRow{UserID: userID, ArtistKey: a.Key, Name: a.Name, Plays: a.Plays, Sessions: a.Sessions, LastPlayedAt: a.Last.Unix(), Weight: a.Weight}) } if _, err := tx.NewInsert().Model(&rows).Exec(ctx); err != nil { return fmt.Errorf("insert affinity artists: %w", err) } } for start := 0; start < len(m.Links); start += affinityChunk { end := min(start+affinityChunk, len(m.Links)) rows := make([]affinityLinkRow, 0, end-start) for _, l := range m.Links[start:end] { rows = append(rows, affinityLinkRow{UserID: userID, AKey: l.A, BKey: l.B, Score: l.Score}) } if _, err := tx.NewInsert().Model(&rows).Exec(ctx); err != nil { return fmt.Errorf("insert affinity links: %w", err) } } build := &affinityBuildRow{UserID: userID, BuiltAt: at.Unix(), Plays: m.Plays, Sessions: m.Sessions, Artists: len(m.Artists), Links: len(m.Links)} if _, err := tx.NewInsert().Model(build).Exec(ctx); err != nil { return fmt.Errorf("insert affinity build: %w", err) } if _, err := tx.ExecContext(ctx, `DELETE FROM outside_plays WHERE user_id = ? AND idempotency_key IN (SELECT idempotency_key FROM listening_events WHERE user_id = ?)`, userID, userID); err != nil { return fmt.Errorf("drop outside plays that are listens now: %w", err) } return nil }) } // Build says when the model of a person was built and from how much; ok is false when it never was. func (r *AffinityRepo) Build(ctx context.Context, userID int64) (b affinity.Built, ok bool, err error) { var row affinityBuildRow err = r.db.NewSelect().Model(&row).Where("user_id = ?", userID).Scan(ctx) if err == sql.ErrNoRows { return b, false, nil } if err != nil { return b, false, fmt.Errorf("read affinity build: %w", err) } return affinity.Built{BuiltAt: time.Unix(row.BuiltAt, 0), Plays: row.Plays, Sessions: row.Sessions, Artists: row.Artists, Links: row.Links}, true, nil } // Seeds gives the artists a person plays the most (by weight), best first. func (r *AffinityRepo) Seeds(ctx context.Context, userID int64, limit int) ([]affinity.Artist, error) { var rows []affinityArtistRow if err := r.db.NewSelect().Model(&rows).Where("user_id = ?", userID).OrderExpr("weight DESC, artist_key").Limit(int64(limit)).Scan(ctx); err != nil { return nil, fmt.Errorf("read affinity seeds: %w", err) } return artistsOf(rows), nil } // Artists gives what is known of the artists with these keys. func (r *AffinityRepo) Artists(ctx context.Context, userID int64, keys []string) (map[string]affinity.Artist, error) { out := map[string]affinity.Artist{} for start := 0; start < len(keys); start += affinityChunk { end := min(start+affinityChunk, len(keys)) var rows []affinityArtistRow if err := r.db.NewSelect().Model(&rows).Where("user_id = ?", userID).Where("artist_key IN (?)", bun.In(keys[start:end])).Scan(ctx); err != nil { return nil, fmt.Errorf("read affinity artists: %w", err) } for _, a := range artistsOf(rows) { out[a.Key] = a } } return out, nil } func artistsOf(rows []affinityArtistRow) []affinity.Artist { out := make([]affinity.Artist, 0, len(rows)) for _, row := range rows { out = append(out, affinity.Artist{Key: row.ArtistKey, Name: row.Name, Plays: row.Plays, Sessions: row.Sessions, Last: time.Unix(row.LastPlayedAt, 0), Weight: row.Weight}) } return out } // Neighbours gives every link that has one of these artists at one end. func (r *AffinityRepo) Neighbours(ctx context.Context, userID int64, keys []string) ([]affinity.Link, error) { if len(keys) == 0 { return nil, nil } var rows []affinityLinkRow if err := r.db.NewSelect().Model(&rows).Where("user_id = ?", userID). WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery { return q.Where("a_key IN (?)", bun.In(keys)).WhereOr("b_key IN (?)", bun.In(keys)) }).Scan(ctx); err != nil { return nil, fmt.Errorf("read affinity links: %w", err) } out := make([]affinity.Link, 0, len(rows)) for _, row := range rows { out = append(out, affinity.Link{A: row.AKey, B: row.BKey, Score: row.Score}) } return out, nil } // LibraryArtists says, for these search keys, which artists exist: the id, and whether there is music to play. When // several artists share a key the one with music comes first. func (r *AffinityRepo) LibraryArtists(ctx context.Context, keys []string) (map[string]affinity.LibraryArtist, error) { out := map[string]affinity.LibraryArtist{} for start := 0; start < len(keys); start += affinityChunk { end := min(start+affinityChunk, len(keys)) rows, err := r.db.QueryContext(ctx, "SELECT a.id, a.name, a.search_key, "+playableArtist("a.id")+" FROM artists a WHERE a.search_key IN (?) ORDER BY a.id", bun.In(keys[start:end])) if err != nil { return nil, fmt.Errorf("find artists: %w", err) } for rows.Next() { var la affinity.LibraryArtist var key string if err := rows.Scan(&la.ID, &la.Name, &key, &la.Playable); err != nil { rows.Close() return nil, err } if have, ok := out[key]; !ok || (la.Playable && !have.Playable) { out[key] = la } } if err := rows.Err(); err != nil { rows.Close() return nil, err } rows.Close() } return out, nil } // Wishlist puts an artist on the wishlist of a person by name: the artist that already has this name (the one with // music first), or a new one that exists only because of this, like the ones a provider names. It gives the id. func (r *AffinityRepo) Wishlist(ctx context.Context, userID int64, name string, at time.Time) (int64, error) { var id int64 err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { if err := lockArtistWrites(ctx, tx); err != nil { return err } var one int if err := tx.QueryRowContext(ctx, "SELECT 1 FROM users WHERE id = ?", userID).Scan(&one); err != nil { return mapNoRows(err, feedback.ErrUserNotFound, "check user") } key := searchKey(name) rows, err := tx.QueryContext(ctx, "SELECT a.id, "+playableArtist("a.id")+" FROM artists a WHERE a.search_key = ? ORDER BY a.id", key) if err != nil { return fmt.Errorf("find artist: %w", err) } var playable bool for rows.Next() { var cand int64 var p bool if err := rows.Scan(&cand, &p); err != nil { rows.Close() return err } if id == 0 || (p && !playable) { id, playable = cand, p } } if err := rows.Err(); err != nil { rows.Close() return err } rows.Close() if id == 0 { if id, err = insertDiscovered(ctx, tx, strings.TrimSpace(name), ""); err != nil { return err } } if _, err := tx.ExecContext(ctx, `INSERT INTO artist_feedback (user_id, artist_id, kind, created_at) VALUES (?, ?, ?, ?) ON CONFLICT (user_id, artist_id) DO UPDATE SET created_at = CASE WHEN artist_feedback.kind = excluded.kind THEN artist_feedback.created_at ELSE excluded.created_at END, kind = excluded.kind`, userID, id, string(feedback.Wishlist), at.Unix()); err != nil { return fmt.Errorf("write feedback: %w", err) } return nil }) if err != nil { return 0, err } return id, nil }