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.
190 lines
7.5 KiB
Go
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
|
|
}
|