sonda/internal/storage/images.go
tgiachi a880b87a9e chore: move the module path to git.kmalloc.org/squid/sonda
Migrating hosting from Codeberg to git.kmalloc.org. Renames the
module path in go.mod, every internal import, and the OCI image
source label in the Dockerfile. Build and full test suite verified
green after the rename.
2026-11-15 17:30:29 +01:00

190 lines
7.5 KiB
Go

package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"time"
"github.com/uptrace/bun"
"git.kmalloc.org/squid/sonda/internal/images"
)
// maxAlbumFiles bounds how many files of an album are offered for a cover search.
const maxAlbumFiles = 20
// ImageRepo stores what is known about pictures. It implements images.Store and
// images.Source.
type ImageRepo struct{ db *bun.DB }
// NewImageRepo builds an ImageRepo.
func NewImageRepo(db *bun.DB) *ImageRepo { return &ImageRepo{db: db} }
// Get returns the record of one owner's picture, with ok=false when there is none.
func (r *ImageRepo) Get(ctx context.Context, kind string, ownerID int64) (images.Record, bool, error) {
var (
rec images.Record
at int64
)
err := r.db.QueryRowContext(ctx,
"SELECT status, source, etag, width, height, gen, author, license, license_url, page_url, checked_at FROM images WHERE kind = ? AND owner_id = ?",
kind, ownerID).Scan(&rec.Status, &rec.Source, &rec.ETag, &rec.Width, &rec.Height, &rec.Gen,
&rec.Credit.Author, &rec.Credit.License, &rec.Credit.LicenseURL, &rec.Credit.PageURL, &at)
if errors.Is(err, sql.ErrNoRows) {
return images.Record{}, false, nil
}
if err != nil {
return images.Record{}, false, fmt.Errorf("read picture record: %w", err)
}
rec.CheckedAt = time.Unix(at, 0)
return rec, true, nil
}
// Save stores the record unless the owner's files changed since the generation the
// record carries (see clearAlbumCovers), and says whether it did.
func (r *ImageRepo) Save(ctx context.Context, kind string, ownerID int64, rec images.Record) (bool, error) {
res, err := r.db.ExecContext(ctx,
`INSERT INTO images (kind, owner_id, status, source, etag, width, height, gen, author, license, license_url, page_url, checked_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT (kind, owner_id) DO UPDATE SET status = excluded.status, source = excluded.source, etag = excluded.etag,
width = excluded.width, height = excluded.height, author = excluded.author, license = excluded.license,
license_url = excluded.license_url, page_url = excluded.page_url, checked_at = excluded.checked_at
WHERE images.gen = excluded.gen`,
kind, ownerID, rec.Status, rec.Source, rec.ETag, rec.Width, rec.Height, rec.Gen,
rec.Credit.Author, rec.Credit.License, rec.Credit.LicenseURL, rec.Credit.PageURL, rec.CheckedAt.Unix())
if err != nil {
return false, fmt.Errorf("save picture record: %w", err)
}
n, err := res.RowsAffected()
if err != nil {
return false, fmt.Errorf("save picture record: %w", err)
}
return n > 0, nil
}
// AlbumFiles returns the files of the album that can be played, in play order, as
// the scanner stored them. An album that does not exist, or has nothing to play, is
// images.ErrAlbumNotFound.
func (r *ImageRepo) AlbumFiles(ctx context.Context, albumID int64) ([]images.File, error) {
rows, err := r.db.QueryContext(ctx,
`SELECT l.path, f.rel_path
FROM tracks t JOIN audio_files f ON f.track_id = t.id JOIN libraries l ON l.id = f.library_id
WHERE t.album_id = ? AND f.missing_at IS NULL
ORDER BY COALESCE(t.disc_no, 0), COALESCE(t.track_no, 0), t.id, f.id LIMIT ?`, albumID, maxAlbumFiles)
if err != nil {
return nil, fmt.Errorf("list album files: %w", err)
}
defer rows.Close()
var out []images.File
for rows.Next() {
var f images.File
if err := rows.Scan(&f.Root, &f.RelPath); err != nil {
return nil, err
}
out = append(out, f)
}
if err := rows.Err(); err != nil {
return nil, err
}
if len(out) == 0 {
return nil, images.ErrAlbumNotFound
}
return out, nil
}
// clearAlbumCovers marks the pictures of the albums out of date, so that they are
// looked for again: a file of the album was added or changed. The row is kept, with its
// generation moved, instead of deleted: a build that is running right now started from
// the old generation, and its result must not be saved over the change. Albums with id
// 0 are ignored.
func clearAlbumCovers(ctx context.Context, tx bun.Tx, albumIDs ...int64) error {
for _, id := range albumIDs {
if id == 0 {
continue
}
if _, err := tx.ExecContext(ctx,
`INSERT INTO images (kind, owner_id, status, gen, checked_at) VALUES ('album', ?, 'stale', 1, 0)
ON CONFLICT (kind, owner_id) DO UPDATE SET status = 'stale', source = '', etag = '', author = '', license = '',
license_url = '', page_url = '', gen = images.gen + 1`, id); err != nil {
return fmt.Errorf("forget the cover: %w", err)
}
}
return nil
}
// AlbumMBID returns the MusicBrainz release group id stored for an album, "" when its
// files had none, or images.ErrAlbumNotFound.
func (r *ImageRepo) AlbumMBID(ctx context.Context, albumID int64) (string, error) {
var m sql.NullString
err := r.db.QueryRowContext(ctx, "SELECT mbid FROM albums WHERE id = ?", albumID).Scan(&m)
if errors.Is(err, sql.ErrNoRows) {
return "", images.ErrAlbumNotFound
}
if err != nil {
return "", fmt.Errorf("read album id: %w", err)
}
return m.String, nil
}
// AlbumNames returns the name of the artist of an album ("" when it has none) and its title, or
// images.ErrAlbumNotFound.
func (r *ImageRepo) AlbumNames(ctx context.Context, albumID int64) (artist, title string, err error) {
var a sql.NullString
err = r.db.QueryRowContext(ctx,
"SELECT al.title, ar.name FROM albums al LEFT JOIN artists ar ON ar.id = al.artist_id WHERE al.id = ?", albumID).Scan(&title, &a)
if errors.Is(err, sql.ErrNoRows) {
return "", "", images.ErrAlbumNotFound
}
if err != nil {
return "", "", fmt.Errorf("read album names: %w", err)
}
return a.String, title, nil
}
// albumsToBuildWhere is the condition for "has something to play and no cover that is still
// good". A cover is good while it is younger than its time to live: a found one CoverTTL, "no
// cover" NoCoverTTL. A stale record (a file changed) and an album with no record are not good.
func albumsToBuildWhere(refresh bool, now time.Time) (string, []any) {
where := `EXISTS (SELECT 1 FROM tracks t JOIN audio_files f ON f.track_id = t.id WHERE t.album_id = a.id AND f.missing_at IS NULL)`
var args []any
if !refresh {
where += ` AND NOT EXISTS (SELECT 1 FROM images i WHERE i.kind = 'album' AND i.owner_id = a.id
AND ((i.status = 'ok' AND i.checked_at > ?) OR (i.status = 'none' AND i.checked_at > ?)))`
args = append(args, now.Add(-images.CoverTTL).Unix(), now.Add(-images.NoCoverTTL).Unix())
}
return where, args
}
// AlbumsToBuild returns up to limit ids greater than after, in order, of the albums that have
// something to play and no cover that is still good (all of those with refresh).
func (r *ImageRepo) AlbumsToBuild(ctx context.Context, after int64, limit int, refresh bool, now time.Time) ([]int64, error) {
where, args := albumsToBuildWhere(refresh, now)
rows, err := r.db.QueryContext(ctx,
"SELECT a.id FROM albums a WHERE a.id > ? AND "+where+" ORDER BY a.id LIMIT ?",
append(append([]any{after}, args...), limit)...)
if err != nil {
return nil, fmt.Errorf("list albums to build: %w", err)
}
defer rows.Close()
var out []int64
for rows.Next() {
var id int64
if err := rows.Scan(&id); err != nil {
return nil, err
}
out = append(out, id)
}
return out, rows.Err()
}
// CountAlbumsToBuild says how many albums AlbumsToBuild would list in all.
func (r *ImageRepo) CountAlbumsToBuild(ctx context.Context, refresh bool, now time.Time) (int, error) {
where, args := albumsToBuildWhere(refresh, now)
var n int
if err := r.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM albums a WHERE "+where, args...).Scan(&n); err != nil {
return 0, fmt.Errorf("count albums to build: %w", err)
}
return n, nil
}