sonda/internal/storage/catalog.go
tgiachi 4ec3ae3bb0 fix(search): empty folded queries, compatibility forms, final sigma, start-up log
- A search text that has nothing left once its accents are taken off matches
  nothing instead of everything.
- The search key folds compatibility forms (the ligature "fi", full-width
  letters) and the Greek final sigma.
- The start-up refresh of the keys logs when it starts and how long it took, since
  it can take a while on a big library the first time.
- More tests: a retitled track is found by its new title, a refresh fixes both
  keys and keeps "Beyoncé" and "Beyonce" apart, more cases for the key.
2026-11-12 10:24:44 +01:00

514 lines
15 KiB
Go

package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"strings"
"github.com/uptrace/bun"
"github.com/uptrace/bun/dialect"
"codeberg.org/squidd/sonda/internal/catalog"
)
// CatalogRepo reads the catalog. It implements catalog.Store.
//
// Everything listed is a track with at least one audio file that is not flagged
// missing ("visible"). Lists run a fixed number of queries: one count, one page
// and a few batched lookups for counts and related items, never one per row.
// The SQL is written to behave the same on SQLite and PostgreSQL.
type CatalogRepo struct{ db *bun.DB }
// NewCatalogRepo builds a CatalogRepo.
func NewCatalogRepo(db *bun.DB) *CatalogRepo { return &CatalogRepo{db: db} }
// ---- query helpers ----
// where collects AND-ed conditions together with their arguments, in order.
type where struct {
parts []string
args []any
}
func (w *where) add(sql string, args ...any) {
w.parts = append(w.parts, sql)
w.args = append(w.args, args...)
}
func (w *where) sql() string {
if len(w.parts) == 0 {
return "1=1"
}
return strings.Join(w.parts, " AND ")
}
// visible is the condition "this track has a playable file", optionally inside
// one library. alias is the name of the tracks table in the surrounding query.
func visible(alias string, libraryID int64) (string, []any) {
q := "EXISTS (SELECT 1 FROM audio_files f WHERE f.track_id = " + alias + ".id AND f.missing_at IS NULL"
var args []any
if libraryID != 0 {
q += " AND f.library_id = ?"
args = append(args, libraryID)
}
return q + ")", args
}
// likePattern turns a search text into a LIKE pattern for the search keys (no accents). The
// wildcard characters of the text are escaped with '!', so "100%" only matches
// a literal percent sign.
func likePattern(q string) string {
esc := strings.NewReplacer("!", "!!", "%", "!%", "_", "!_").Replace(searchKey(q))
return "%" + esc + "%"
}
// addSearch adds the condition for a search text on a column of search keys. A text that has
// nothing left once folded (only accents) matches nothing, where a pattern made of it would match
// everything; a text of blanks only is no search at all and matches everything.
func addSearch(w *where, column, q string) {
if strings.TrimSpace(q) != "" && searchKey(q) == "" {
w.add("1 = 0")
return
}
w.add(column+" LIKE ? ESCAPE '!'", likePattern(q))
}
// binary orders by plain byte value. PostgreSQL would otherwise follow the
// database locale while SQLite compares bytes, and listings would differ.
func (r *CatalogRepo) binary(expr string) string {
if r.db.Dialect().Name() == dialect.PG {
return expr + ` COLLATE "C"`
}
return expr
}
func (r *CatalogRepo) count(ctx context.Context, query string, args ...any) (int, error) {
var n int
if err := r.db.QueryRowContext(ctx, query, args...).Scan(&n); err != nil {
return 0, fmt.Errorf("count: %w", err)
}
return n, nil
}
// pairs runs a query that returns (id, number) rows and collects them in a map.
func (r *CatalogRepo) pairs(ctx context.Context, query string, args ...any) (map[int64]int64, error) {
rows, err := r.db.QueryContext(ctx, query, args...)
if err != nil {
return nil, err
}
defer rows.Close()
out := map[int64]int64{}
for rows.Next() {
var id, n int64
if err := rows.Scan(&id, &n); err != nil {
return nil, err
}
out[id] = n
}
return out, rows.Err()
}
// ---- artists ----
// ListArtists lists artists with a playable credited track or a playable album.
func (r *CatalogRepo) ListArtists(ctx context.Context, f catalog.Filter) (catalog.Page[catalog.Artist], error) {
return r.listArtists(ctx, f, 0)
}
// GetArtist returns one artist, or catalog.ErrNotFound.
func (r *CatalogRepo) GetArtist(ctx context.Context, id int64) (catalog.Artist, error) {
p, err := r.listArtists(ctx, catalog.Filter{Limit: 1}, id)
if err != nil {
return catalog.Artist{}, err
}
if len(p.Items) > 0 {
return p.Items[0], nil
}
// Not in the library, but maybe named by a discovery provider: such an artist
// can be shown, with nothing to play. The lists never include it.
a := catalog.Artist{}
err = r.db.QueryRowContext(ctx,
"SELECT a.id, a.name FROM artists a WHERE a.id = ? AND "+discoveredArtist("a.id"), id).Scan(&a.ID, &a.Name)
if errors.Is(err, sql.ErrNoRows) {
return catalog.Artist{}, catalog.ErrNotFound
}
if err != nil {
return catalog.Artist{}, fmt.Errorf("find discovered artist: %w", err)
}
return a, nil
}
func (r *CatalogRepo) listArtists(ctx context.Context, f catalog.Filter, id int64) (catalog.Page[catalog.Artist], error) {
page := catalog.Page[catalog.Artist]{Items: []catalog.Artist{}, Limit: f.Limit, Offset: f.Offset}
vis, visArgs := visible("t", f.LibraryID)
var w where
// An artist counts when credited on a playable track, or when it is the
// album artist of an album with a playable track (compilations).
w.add(`(EXISTS (SELECT 1 FROM track_artists ta JOIN tracks t ON t.id = ta.track_id WHERE ta.artist_id = a.id AND `+vis+`)
OR EXISTS (SELECT 1 FROM albums al JOIN tracks t ON t.album_id = al.id WHERE al.artist_id = a.id AND `+vis+`))`,
append(append([]any{}, visArgs...), visArgs...)...)
if id != 0 {
w.add("a.id = ?", id)
}
if f.Query != "" {
addSearch(&w, "a.search_key", f.Query)
}
total, err := r.count(ctx, "SELECT COUNT(*) FROM artists a WHERE "+w.sql(), w.args...)
if err != nil {
return page, err
}
page.Total = total
if f.Offset >= total {
return page, nil
}
rows, err := r.db.QueryContext(ctx,
"SELECT a.id, a.name FROM artists a WHERE "+w.sql()+" ORDER BY "+r.binary("a.name_key")+", a.id LIMIT ? OFFSET ?",
append(append([]any{}, w.args...), f.Limit, f.Offset)...)
if err != nil {
return page, fmt.Errorf("list artists: %w", err)
}
defer rows.Close()
var ids []int64
for rows.Next() {
var a catalog.Artist
if err := rows.Scan(&a.ID, &a.Name); err != nil {
return page, err
}
page.Items = append(page.Items, a)
ids = append(ids, a.ID)
}
if err := rows.Err(); err != nil {
return page, err
}
if len(ids) == 0 {
return page, nil
}
tracks, err := r.pairs(ctx,
`SELECT ta.artist_id, COUNT(DISTINCT t.id) FROM track_artists ta JOIN tracks t ON t.id = ta.track_id
WHERE ta.artist_id IN (?) AND `+vis+` GROUP BY ta.artist_id`,
append([]any{bun.In(ids)}, visArgs...)...)
if err != nil {
return page, fmt.Errorf("count artist tracks: %w", err)
}
albums, err := r.pairs(ctx,
`SELECT x.artist_id, COUNT(DISTINCT x.album_id) FROM (
SELECT ta.artist_id AS artist_id, t.album_id AS album_id
FROM track_artists ta JOIN tracks t ON t.id = ta.track_id
WHERE ta.artist_id IN (?) AND t.album_id IS NOT NULL AND `+vis+`
UNION
SELECT al.artist_id AS artist_id, al.id AS album_id FROM albums al
WHERE al.artist_id IN (?) AND EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND `+vis+`)
) x GROUP BY x.artist_id`,
append(append(append([]any{bun.In(ids)}, visArgs...), bun.In(ids)), visArgs...)...)
if err != nil {
return page, fmt.Errorf("count artist albums: %w", err)
}
for i := range page.Items {
page.Items[i].TrackCount = int(tracks[page.Items[i].ID])
page.Items[i].AlbumCount = int(albums[page.Items[i].ID])
}
return page, nil
}
// ---- albums ----
// ListAlbums lists albums that have a playable track.
func (r *CatalogRepo) ListAlbums(ctx context.Context, f catalog.Filter) (catalog.Page[catalog.Album], error) {
return r.listAlbums(ctx, f, 0)
}
// GetAlbum returns one album, or catalog.ErrNotFound.
func (r *CatalogRepo) GetAlbum(ctx context.Context, id int64) (catalog.Album, error) {
p, err := r.listAlbums(ctx, catalog.Filter{Limit: 1}, id)
if err != nil {
return catalog.Album{}, err
}
if len(p.Items) == 0 {
return catalog.Album{}, catalog.ErrNotFound
}
return p.Items[0], nil
}
func (r *CatalogRepo) listAlbums(ctx context.Context, f catalog.Filter, id int64) (catalog.Page[catalog.Album], error) {
page := catalog.Page[catalog.Album]{Items: []catalog.Album{}, Limit: f.Limit, Offset: f.Offset}
vis, visArgs := visible("t", f.LibraryID)
var w where
w.add("EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND "+vis+")", visArgs...)
if id != 0 {
w.add("al.id = ?", id)
}
if f.ArtistID != 0 {
// Same meaning as an artist's album_count: albums the artist owns, plus
// albums where the artist is credited on a playable track.
w.add(`(al.artist_id = ? OR EXISTS (SELECT 1 FROM tracks t JOIN track_artists ta ON ta.track_id = t.id
WHERE t.album_id = al.id AND ta.artist_id = ? AND `+vis+`))`,
append([]any{f.ArtistID, f.ArtistID}, visArgs...)...)
}
if f.Query != "" {
addSearch(&w, "al.search_key", f.Query)
}
total, err := r.count(ctx, "SELECT COUNT(*) FROM albums al WHERE "+w.sql(), w.args...)
if err != nil {
return page, err
}
page.Total = total
if f.Offset >= total {
return page, nil
}
rows, err := r.db.QueryContext(ctx,
`SELECT al.id, al.title, al.artist_id, ar.name FROM albums al
LEFT JOIN artists ar ON ar.id = al.artist_id
WHERE `+w.sql()+` ORDER BY `+r.binary("al.title_key")+`, al.id LIMIT ? OFFSET ?`,
append(append([]any{}, w.args...), f.Limit, f.Offset)...)
if err != nil {
return page, fmt.Errorf("list albums: %w", err)
}
defer rows.Close()
var ids []int64
for rows.Next() {
var (
a catalog.Album
artistID sql.NullInt64
artistName sql.NullString
)
if err := rows.Scan(&a.ID, &a.Title, &artistID, &artistName); err != nil {
return page, err
}
if artistID.Valid {
a.Artist = &catalog.ArtistRef{ID: artistID.Int64, Name: artistName.String}
}
page.Items = append(page.Items, a)
ids = append(ids, a.ID)
}
if err := rows.Err(); err != nil {
return page, err
}
if len(ids) == 0 {
return page, nil
}
type totals struct {
tracks int
ms int64
}
sums := map[int64]totals{}
cr, err := r.db.QueryContext(ctx,
`SELECT t.album_id, COUNT(*), CAST(COALESCE(SUM(t.duration_ms), 0) AS BIGINT) FROM tracks t
WHERE t.album_id IN (?) AND `+vis+` GROUP BY t.album_id`,
append([]any{bun.In(ids)}, visArgs...)...)
if err != nil {
return page, fmt.Errorf("count album tracks: %w", err)
}
defer cr.Close()
for cr.Next() {
var albumID, n, ms int64
if err := cr.Scan(&albumID, &n, &ms); err != nil {
return page, err
}
sums[albumID] = totals{tracks: int(n), ms: ms}
}
if err := cr.Err(); err != nil {
return page, err
}
for i := range page.Items {
page.Items[i].TrackCount = sums[page.Items[i].ID].tracks
page.Items[i].DurationMs = sums[page.Items[i].ID].ms
}
return page, nil
}
// ---- tracks ----
// ListTracks lists playable tracks, grouped by album (two albums with the same
// title stay apart because the album id is part of the order).
func (r *CatalogRepo) ListTracks(ctx context.Context, f catalog.Filter) (catalog.Page[catalog.Track], error) {
return r.listTracks(ctx, f, 0)
}
// GetTrack returns one track, or catalog.ErrNotFound.
func (r *CatalogRepo) GetTrack(ctx context.Context, id int64) (catalog.Track, error) {
p, err := r.listTracks(ctx, catalog.Filter{Limit: 1}, id)
if err != nil {
return catalog.Track{}, err
}
if len(p.Items) == 0 {
return catalog.Track{}, catalog.ErrNotFound
}
return p.Items[0], nil
}
func (r *CatalogRepo) listTracks(ctx context.Context, f catalog.Filter, id int64) (catalog.Page[catalog.Track], error) {
page := catalog.Page[catalog.Track]{Items: []catalog.Track{}, Limit: f.Limit, Offset: f.Offset}
vis, visArgs := visible("t", f.LibraryID)
var w where
w.add(vis, visArgs...)
if id != 0 {
w.add("t.id = ?", id)
}
if f.AlbumID != 0 {
w.add("t.album_id = ?", f.AlbumID)
}
if f.ArtistID != 0 {
w.add("EXISTS (SELECT 1 FROM track_artists ta WHERE ta.track_id = t.id AND ta.artist_id = ?)", f.ArtistID)
}
if f.Query != "" {
addSearch(&w, "t.search_key", f.Query)
}
total, err := r.count(ctx, "SELECT COUNT(*) FROM tracks t WHERE "+w.sql(), w.args...)
if err != nil {
return page, err
}
page.Total = total
if f.Offset >= total {
return page, nil
}
rows, err := r.db.QueryContext(ctx,
`SELECT t.id, t.title, COALESCE(t.duration_ms, 0), COALESCE(t.track_no, 0), COALESCE(t.disc_no, 0), t.album_id
FROM tracks t LEFT JOIN albums al ON al.id = t.album_id
WHERE `+w.sql()+`
ORDER BY `+r.binary("COALESCE(al.title_key, '')")+`, COALESCE(t.album_id, 0), COALESCE(t.disc_no, 0), COALESCE(t.track_no, 0),
`+r.binary("t.title_key")+`, t.id
LIMIT ? OFFSET ?`,
append(append([]any{}, w.args...), f.Limit, f.Offset)...)
if err != nil {
return page, fmt.Errorf("list tracks: %w", err)
}
defer rows.Close()
albumOf := map[int64]int64{} // track id -> album id
var ids []int64
for rows.Next() {
var (
t catalog.Track
albumID sql.NullInt64
)
if err := rows.Scan(&t.ID, &t.Title, &t.DurationMs, &t.TrackNo, &t.DiscNo, &albumID); err != nil {
return page, err
}
t.Artists = []catalog.ArtistRef{}
if albumID.Valid {
albumOf[t.ID] = albumID.Int64
}
page.Items = append(page.Items, t)
ids = append(ids, t.ID)
}
if err := rows.Err(); err != nil {
return page, err
}
if len(ids) == 0 {
return page, nil
}
return page, r.attachTrackDetails(ctx, &page, ids, albumOf, f.LibraryID)
}
// attachTrackDetails fills album, credits and format for a page of tracks with
// three queries, whatever the page size.
func (r *CatalogRepo) attachTrackDetails(ctx context.Context, page *catalog.Page[catalog.Track], ids []int64, albumOf map[int64]int64, libraryID int64) error {
albums := map[int64]catalog.AlbumRef{}
if len(albumOf) > 0 {
var albumIDs []int64
seen := map[int64]bool{}
for _, a := range albumOf {
if !seen[a] {
seen[a] = true
albumIDs = append(albumIDs, a)
}
}
rows, err := r.db.QueryContext(ctx, "SELECT id, title FROM albums WHERE id IN (?)", bun.In(albumIDs))
if err != nil {
return fmt.Errorf("load albums: %w", err)
}
defer rows.Close()
for rows.Next() {
var a catalog.AlbumRef
if err := rows.Scan(&a.ID, &a.Title); err != nil {
return err
}
albums[a.ID] = a
}
if err := rows.Err(); err != nil {
return err
}
}
credits := map[int64][]catalog.ArtistRef{}
crows, err := r.db.QueryContext(ctx,
`SELECT ta.track_id, a.id, a.name FROM track_artists ta JOIN artists a ON a.id = ta.artist_id
WHERE ta.track_id IN (?) ORDER BY ta.track_id, a.id`, bun.In(ids))
if err != nil {
return fmt.Errorf("load credits: %w", err)
}
defer crows.Close()
for crows.Next() {
var trackID int64
var a catalog.ArtistRef
if err := crows.Scan(&trackID, &a.ID, &a.Name); err != nil {
return err
}
credits[trackID] = append(credits[trackID], a)
}
if err := crows.Err(); err != nil {
return err
}
formats := map[int64]string{}
fq := "SELECT f.track_id, f.format FROM audio_files f WHERE f.track_id IN (?) AND f.missing_at IS NULL"
fargs := []any{bun.In(ids)}
if libraryID != 0 {
fq += " AND f.library_id = ?"
fargs = append(fargs, libraryID)
}
frows, err := r.db.QueryContext(ctx, fq+" ORDER BY f.id", fargs...)
if err != nil {
return fmt.Errorf("load formats: %w", err)
}
defer frows.Close()
for frows.Next() {
var trackID int64
var format string
if err := frows.Scan(&trackID, &format); err != nil {
return err
}
if _, ok := formats[trackID]; !ok { // the first playable file wins
formats[trackID] = normalizeFormat(format)
}
}
if err := frows.Err(); err != nil {
return err
}
for i := range page.Items {
t := &page.Items[i]
if albumID, ok := albumOf[t.ID]; ok {
if ref, ok := albums[albumID]; ok {
a := ref
t.Album = &a
}
}
if c := credits[t.ID]; c != nil {
t.Artists = c
}
t.Format = formats[t.ID]
}
return nil
}
// normalizeFormat shows TagLib's "mpeg" as the familiar "mp3".
func normalizeFormat(f string) string {
if f == "mpeg" {
return "mp3"
}
return f
}