bvandeusen
63b25e65ad
GET /api/artists/{id}/similar — in-library artists ranked by similarity
score (deduped across sources), ArtistRef list with cover + album count.
GET /api/artists/{id}/top-tracks — current user's most-played tracks for
the artist (skips excluded, quarantine filtered).
295 lines
8.3 KiB
Go
295 lines
8.3 KiB
Go
// Code generated by sqlc. DO NOT EDIT.
|
|
// versions:
|
|
// sqlc v1.31.1
|
|
// source: similarity.sql
|
|
|
|
package dbq
|
|
|
|
import (
|
|
"context"
|
|
|
|
"github.com/jackc/pgx/v5/pgtype"
|
|
)
|
|
|
|
const getArtistsByMBIDs = `-- name: GetArtistsByMBIDs :many
|
|
SELECT id, mbid FROM artists WHERE mbid = ANY($1::text[])
|
|
`
|
|
|
|
type GetArtistsByMBIDsRow struct {
|
|
ID pgtype.UUID
|
|
Mbid *string
|
|
}
|
|
|
|
func (q *Queries) GetArtistsByMBIDs(ctx context.Context, dollar_1 []string) ([]GetArtistsByMBIDsRow, error) {
|
|
rows, err := q.db.Query(ctx, getArtistsByMBIDs, dollar_1)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var items []GetArtistsByMBIDsRow
|
|
for rows.Next() {
|
|
var i GetArtistsByMBIDsRow
|
|
if err := rows.Scan(&i.ID, &i.Mbid); err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, i)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
const getTracksByMBIDs = `-- name: GetTracksByMBIDs :many
|
|
SELECT id, mbid FROM tracks WHERE mbid = ANY($1::text[])
|
|
`
|
|
|
|
type GetTracksByMBIDsRow struct {
|
|
ID pgtype.UUID
|
|
Mbid *string
|
|
}
|
|
|
|
// Bulk in-library lookup: maps a slice of MBIDs back to local track IDs.
|
|
func (q *Queries) GetTracksByMBIDs(ctx context.Context, dollar_1 []string) ([]GetTracksByMBIDsRow, error) {
|
|
rows, err := q.db.Query(ctx, getTracksByMBIDs, dollar_1)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var items []GetTracksByMBIDsRow
|
|
for rows.Next() {
|
|
var i GetTracksByMBIDsRow
|
|
if err := rows.Scan(&i.ID, &i.Mbid); err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, i)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
const listPlayedArtistsNeedingSimilarity = `-- name: ListPlayedArtistsNeedingSimilarity :many
|
|
SELECT DISTINCT ar.id, ar.mbid
|
|
FROM artists ar
|
|
JOIN tracks t ON t.artist_id = ar.id
|
|
JOIN play_events pe ON pe.track_id = t.id
|
|
WHERE ar.mbid IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM artist_similarity asim
|
|
WHERE asim.artist_a_id = ar.id
|
|
AND asim.source = 'listenbrainz'
|
|
AND asim.fetched_at > now() - interval '7 days'
|
|
)
|
|
ORDER BY ar.id
|
|
LIMIT $1
|
|
`
|
|
|
|
type ListPlayedArtistsNeedingSimilarityRow struct {
|
|
ID pgtype.UUID
|
|
Mbid *string
|
|
}
|
|
|
|
func (q *Queries) ListPlayedArtistsNeedingSimilarity(ctx context.Context, limit int32) ([]ListPlayedArtistsNeedingSimilarityRow, error) {
|
|
rows, err := q.db.Query(ctx, listPlayedArtistsNeedingSimilarity, limit)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var items []ListPlayedArtistsNeedingSimilarityRow
|
|
for rows.Next() {
|
|
var i ListPlayedArtistsNeedingSimilarityRow
|
|
if err := rows.Scan(&i.ID, &i.Mbid); err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, i)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
const listPlayedTracksNeedingSimilarity = `-- name: ListPlayedTracksNeedingSimilarity :many
|
|
SELECT DISTINCT t.id, t.mbid
|
|
FROM tracks t
|
|
JOIN play_events pe ON pe.track_id = t.id
|
|
WHERE t.mbid IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM track_similarity ts
|
|
WHERE ts.track_a_id = t.id
|
|
AND ts.source = 'listenbrainz'
|
|
AND ts.fetched_at > now() - interval '7 days'
|
|
)
|
|
ORDER BY t.id
|
|
LIMIT $1
|
|
`
|
|
|
|
type ListPlayedTracksNeedingSimilarityRow struct {
|
|
ID pgtype.UUID
|
|
Mbid *string
|
|
}
|
|
|
|
// Tracks with at least one play, an MBID, and no fresh listenbrainz row
|
|
// (no row at all, OR fetched_at older than 7 days). Used by the worker
|
|
// to find work each tick. Bounded by $1.
|
|
func (q *Queries) ListPlayedTracksNeedingSimilarity(ctx context.Context, limit int32) ([]ListPlayedTracksNeedingSimilarityRow, error) {
|
|
rows, err := q.db.Query(ctx, listPlayedTracksNeedingSimilarity, limit)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var items []ListPlayedTracksNeedingSimilarityRow
|
|
for rows.Next() {
|
|
var i ListPlayedTracksNeedingSimilarityRow
|
|
if err := rows.Scan(&i.ID, &i.Mbid); err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, i)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
const listSimilarArtistsForArtist = `-- name: ListSimilarArtistsForArtist :many
|
|
SELECT artists.id, artists.name, artists.sort_name, artists.mbid, artists.created_at, artists.updated_at, artists.artist_thumb_path, artists.artist_fanart_path, artists.artist_art_source, artists.artist_art_sources_version,
|
|
cov.id AS cover_album_id,
|
|
cnt.album_count::bigint AS album_count
|
|
FROM (
|
|
SELECT artist_b_id, max(score) AS sim_score
|
|
FROM artist_similarity
|
|
WHERE artist_a_id = $1
|
|
GROUP BY artist_b_id
|
|
) s
|
|
JOIN artists ON artists.id = s.artist_b_id
|
|
LEFT JOIN LATERAL (
|
|
SELECT id FROM albums
|
|
WHERE artist_id = artists.id AND cover_art_path IS NOT NULL
|
|
ORDER BY created_at DESC LIMIT 1
|
|
) cov ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT count(*) AS album_count
|
|
FROM albums WHERE artist_id = artists.id
|
|
) cnt ON true
|
|
ORDER BY s.sim_score DESC, artists.sort_name
|
|
LIMIT $2
|
|
`
|
|
|
|
type ListSimilarArtistsForArtistParams struct {
|
|
SeedArtistID pgtype.UUID
|
|
ResultLimit int32
|
|
}
|
|
|
|
type ListSimilarArtistsForArtistRow struct {
|
|
Artist Artist
|
|
CoverAlbumID pgtype.UUID
|
|
AlbumCount int64
|
|
}
|
|
|
|
// In-library artists similar to a seed artist, ranked by best similarity
|
|
// score across sources (deduped per candidate). cover_album_id + album_count
|
|
// mirror ListArtistsAlphaWithCovers so the strip renders identical cards.
|
|
func (q *Queries) ListSimilarArtistsForArtist(ctx context.Context, arg ListSimilarArtistsForArtistParams) ([]ListSimilarArtistsForArtistRow, error) {
|
|
rows, err := q.db.Query(ctx, listSimilarArtistsForArtist, arg.SeedArtistID, arg.ResultLimit)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var items []ListSimilarArtistsForArtistRow
|
|
for rows.Next() {
|
|
var i ListSimilarArtistsForArtistRow
|
|
if err := rows.Scan(
|
|
&i.Artist.ID,
|
|
&i.Artist.Name,
|
|
&i.Artist.SortName,
|
|
&i.Artist.Mbid,
|
|
&i.Artist.CreatedAt,
|
|
&i.Artist.UpdatedAt,
|
|
&i.Artist.ArtistThumbPath,
|
|
&i.Artist.ArtistFanartPath,
|
|
&i.Artist.ArtistArtSource,
|
|
&i.Artist.ArtistArtSourcesVersion,
|
|
&i.CoverAlbumID,
|
|
&i.AlbumCount,
|
|
); err != nil {
|
|
return nil, err
|
|
}
|
|
items = append(items, i)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
return items, nil
|
|
}
|
|
|
|
const upsertArtistSimilarity = `-- name: UpsertArtistSimilarity :exec
|
|
INSERT INTO artist_similarity (artist_a_id, artist_b_id, score, source, fetched_at)
|
|
VALUES ($1, $2, $3, 'listenbrainz', now())
|
|
ON CONFLICT (artist_a_id, artist_b_id, source)
|
|
DO UPDATE SET score = EXCLUDED.score, fetched_at = EXCLUDED.fetched_at
|
|
`
|
|
|
|
type UpsertArtistSimilarityParams struct {
|
|
ArtistAID pgtype.UUID
|
|
ArtistBID pgtype.UUID
|
|
Score float64
|
|
}
|
|
|
|
func (q *Queries) UpsertArtistSimilarity(ctx context.Context, arg UpsertArtistSimilarityParams) error {
|
|
_, err := q.db.Exec(ctx, upsertArtistSimilarity, arg.ArtistAID, arg.ArtistBID, arg.Score)
|
|
return err
|
|
}
|
|
|
|
const upsertArtistSimilarityUnmatched = `-- name: UpsertArtistSimilarityUnmatched :exec
|
|
INSERT INTO artist_similarity_unmatched (
|
|
seed_artist_id, candidate_mbid, candidate_name, score, source
|
|
) VALUES ($1, $2, $3, $4, $5)
|
|
ON CONFLICT (seed_artist_id, candidate_mbid, source) DO UPDATE SET
|
|
candidate_name = EXCLUDED.candidate_name,
|
|
score = EXCLUDED.score,
|
|
fetched_at = now()
|
|
`
|
|
|
|
type UpsertArtistSimilarityUnmatchedParams struct {
|
|
SeedArtistID pgtype.UUID
|
|
CandidateMbid string
|
|
CandidateName string
|
|
Score float64
|
|
Source string
|
|
}
|
|
|
|
// Persists an out-of-library similar-artist MBID. Idempotent on
|
|
// (seed_artist_id, candidate_mbid, source) — re-fetches refresh the
|
|
// name/score and bump fetched_at.
|
|
func (q *Queries) UpsertArtistSimilarityUnmatched(ctx context.Context, arg UpsertArtistSimilarityUnmatchedParams) error {
|
|
_, err := q.db.Exec(ctx, upsertArtistSimilarityUnmatched,
|
|
arg.SeedArtistID,
|
|
arg.CandidateMbid,
|
|
arg.CandidateName,
|
|
arg.Score,
|
|
arg.Source,
|
|
)
|
|
return err
|
|
}
|
|
|
|
const upsertTrackSimilarity = `-- name: UpsertTrackSimilarity :exec
|
|
INSERT INTO track_similarity (track_a_id, track_b_id, score, source, fetched_at)
|
|
VALUES ($1, $2, $3, 'listenbrainz', now())
|
|
ON CONFLICT (track_a_id, track_b_id, source)
|
|
DO UPDATE SET score = EXCLUDED.score, fetched_at = EXCLUDED.fetched_at
|
|
`
|
|
|
|
type UpsertTrackSimilarityParams struct {
|
|
TrackAID pgtype.UUID
|
|
TrackBID pgtype.UUID
|
|
Score float64
|
|
}
|
|
|
|
func (q *Queries) UpsertTrackSimilarity(ctx context.Context, arg UpsertTrackSimilarityParams) error {
|
|
_, err := q.db.Exec(ctx, upsertTrackSimilarity, arg.TrackAID, arg.TrackBID, arg.Score)
|
|
return err
|
|
}
|