bvandeusen
5c386dc73e
Three failures from the prior push: 1. internal/api/library.go and 3 other callers of q.GetArtistByID broke because T2 modified the query to use an explicit column list, making sqlc generate GetArtistByIDRow instead of returning dbq.Artist. dbq.Artist already has every column added by migration 0018 (artist_thumb_path / artist_fanart_path / artist_art_source / artist_art_sources_version), so the explicit column list was unnecessary work. Revert the SELECT to SELECT *, regenerate sqlc, GetArtistByIDRow disappears, all callers compile again. 2. internal/tracks/service_test.go missed the dataDir parameter added to NewService in T9. Add "" as the 4th arg to every test call site (tests don't exercise the artist-art cleanup path; the empty dataDir is fine). 3. integrations.test.ts:356's mock.calls.filter callback had a too-narrow type annotation that svelte-check rejected. Relax to any[] to match what mock.calls actually is.
123 lines
4.4 KiB
SQL
123 lines
4.4 KiB
SQL
-- name: UpsertArtist :one
|
|
-- Insert or update by mbid when present; otherwise by (name, sort_name).
|
|
-- Callers pass the canonical sort_name; scanner is responsible for derivation.
|
|
INSERT INTO artists (name, sort_name, mbid)
|
|
VALUES ($1, $2, $3)
|
|
ON CONFLICT (mbid) WHERE mbid IS NOT NULL
|
|
DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
sort_name = EXCLUDED.sort_name,
|
|
updated_at = now()
|
|
RETURNING *;
|
|
|
|
-- name: GetArtistByID :one
|
|
-- Lookup an artist row by its UUID. Returns the full row; consumers
|
|
-- pick the columns they need.
|
|
SELECT * FROM artists WHERE id = $1;
|
|
|
|
-- name: GetArtistByName :one
|
|
SELECT * FROM artists WHERE name = $1 LIMIT 1;
|
|
|
|
-- name: ListArtists :many
|
|
SELECT * FROM artists ORDER BY sort_name;
|
|
|
|
-- name: SearchArtists :many
|
|
SELECT * FROM artists
|
|
WHERE name ILIKE '%' || $1 || '%'
|
|
ORDER BY sort_name
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: ListArtistsAlpha :many
|
|
SELECT * FROM artists ORDER BY sort_name, name, id LIMIT $1 OFFSET $2;
|
|
|
|
-- name: ListArtistsNewest :many
|
|
-- Secondary id tiebreaker keeps pagination stable when created_at ties.
|
|
SELECT * FROM artists ORDER BY created_at DESC, id DESC LIMIT $1 OFFSET $2;
|
|
|
|
-- name: CountArtists :one
|
|
SELECT COUNT(*) FROM artists;
|
|
|
|
-- name: CountArtistsMatching :one
|
|
SELECT COUNT(*) FROM artists WHERE name ILIKE '%' || $1::text || '%';
|
|
|
|
-- name: GetArtistsByIDs :many
|
|
-- Batched lookup used by M5c suggestion attribution to resolve top-3
|
|
-- contributing seed UUIDs back to artist names in one round-trip.
|
|
SELECT * FROM artists WHERE id = ANY($1::uuid[]);
|
|
|
|
-- name: ListArtistsAlphaWithCovers :many
|
|
-- M6a: alpha-sorted artist list with derived cover_album_id (most-recent
|
|
-- album whose cover_art_path is set). Replaces ListArtistsAlpha for the
|
|
-- /api/artists?sort=alpha branch — the new column is appended, so the
|
|
-- alpha branch is purely additive on the wire. album_count is computed
|
|
-- in the same query to drop the old N+1 in handleListArtists.
|
|
SELECT sqlc.embed(artists),
|
|
cov.id AS cover_album_id,
|
|
cnt.album_count::bigint AS album_count
|
|
FROM artists
|
|
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 artists.sort_name, artists.name, artists.id
|
|
LIMIT $1 OFFSET $2;
|
|
|
|
-- name: DeleteArtistIfEmpty :one
|
|
-- M7 #372: deletes the artist row only when it has no remaining albums
|
|
-- AND no remaining tracks (defensive — tracks can in principle exist
|
|
-- without an album, though the scanner doesn't write that shape today).
|
|
DELETE FROM artists a
|
|
WHERE a.id = $1
|
|
AND NOT EXISTS (SELECT 1 FROM albums al WHERE al.artist_id = a.id)
|
|
AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = a.id)
|
|
RETURNING a.id;
|
|
|
|
-- name: SetArtistMbidIfNull :exec
|
|
-- M7 #379: heal MBID on existing artist rows during rescan. Idempotent —
|
|
-- only updates when the existing mbid is NULL, so concurrent scans don't
|
|
-- overwrite an existing value with a stale one.
|
|
UPDATE artists
|
|
SET mbid = $2,
|
|
updated_at = now()
|
|
WHERE id = $1 AND mbid IS NULL;
|
|
|
|
-- name: ListArtistsMissingArt :many
|
|
-- M7 cover-sources: returns artist rows the artist-art enricher should
|
|
-- attempt. Eligible: artist_art_source IS NULL OR (= 'none' AND stale
|
|
-- artist_art_sources_version). MBID-less artists are returned but the
|
|
-- enricher skips them at the row.mbid == nil guard inside the chain.
|
|
SELECT a.id
|
|
FROM artists a
|
|
WHERE a.artist_art_source IS NULL
|
|
OR (a.artist_art_source = 'none' AND a.artist_art_sources_version != $1)
|
|
ORDER BY a.created_at
|
|
LIMIT $2;
|
|
|
|
-- name: SetArtistArtWithVersion :exec
|
|
-- M7 cover-sources: records a successful or settled artist-art
|
|
-- enrichment with the current sources_version stamp. thumb_path /
|
|
-- fanart_path are NULL on a 'none' result, or independently NULL if
|
|
-- the provider returned only one of the two image types (e.g.
|
|
-- TheAudioDB had a thumb but no fanart for this artist).
|
|
UPDATE artists
|
|
SET artist_thumb_path = $2,
|
|
artist_fanart_path = $3,
|
|
artist_art_source = $4,
|
|
artist_art_sources_version = $5,
|
|
updated_at = now()
|
|
WHERE id = $1;
|
|
|
|
-- name: ClearArtistArtNone :exec
|
|
-- M7 cover-sources: parallel of ClearAlbumCoverNone. Used by the
|
|
-- eligibility loop to flip stale 'none' rows back to NULL.
|
|
UPDATE artists
|
|
SET artist_art_source = NULL,
|
|
updated_at = now()
|
|
WHERE id = $1
|
|
AND artist_art_source = 'none';
|