package storage import ( "context" "database/sql" "errors" "fmt" "strings" "time" "github.com/uptrace/bun" "github.com/uptrace/bun/dialect" "git.kmalloc.org/squid/sonda/internal/library" "git.kmalloc.org/squid/sonda/internal/streaming" ) type artistModel struct { bun.BaseModel `bun:"table:artists"` ID int64 `bun:"id,pk,autoincrement"` Name string `bun:"name,notnull"` NameKey string `bun:"name_key,notnull"` SearchKey string `bun:"search_key,notnull"` Discovered bool `bun:"discovered,notnull"` // named by a provider, never seen in a file } type albumModel struct { bun.BaseModel `bun:"table:albums"` ID int64 `bun:"id,pk,autoincrement"` Title string `bun:"title,notnull"` TitleKey string `bun:"title_key,notnull"` SearchKey string `bun:"search_key,notnull"` ArtistID int64 `bun:"artist_id,nullzero"` MBID string `bun:"mbid,nullzero"` // the MusicBrainz release group id, from the files' tags } type trackModel struct { bun.BaseModel `bun:"table:tracks"` ID int64 `bun:"id,pk,autoincrement"` Title string `bun:"title,notnull"` TitleKey string `bun:"title_key,notnull"` SearchKey string `bun:"search_key,notnull"` DurationMs int64 `bun:"duration_ms,nullzero"` TrackNo int `bun:"track_no,nullzero"` DiscNo int `bun:"disc_no,nullzero"` AlbumID int64 `bun:"album_id,nullzero"` Year int `bun:"year,nullzero"` } type trackArtistModel struct { bun.BaseModel `bun:"table:track_artists"` TrackID int64 `bun:"track_id,pk"` ArtistID int64 `bun:"artist_id,pk"` Role string `bun:"role,pk"` } type audioFileModel struct { bun.BaseModel `bun:"table:audio_files"` ID int64 `bun:"id,pk,autoincrement"` TrackID int64 `bun:"track_id,notnull"` LibraryID int64 `bun:"library_id,notnull"` RelPath string `bun:"rel_path,notnull"` Format string `bun:"format,notnull"` SizeBytes int64 `bun:"size_bytes,notnull"` MTimeUnix int64 `bun:"mtime_unix,notnull"` SampleRate int `bun:"sample_rate,nullzero"` BitRate int `bun:"bitrate,nullzero"` // What the file says about its loudness (its ReplayGain tags); NULL for what it does not say. RGTrackGain sql.NullFloat64 `bun:"rg_track_gain"` RGAlbumGain sql.NullFloat64 `bun:"rg_album_gain"` RGTrackPeak sql.NullFloat64 `bun:"rg_track_peak"` RGAlbumPeak sql.NullFloat64 `bun:"rg_album_peak"` // ContentHash is the SHA-256 of the file as it was when it was read for duplicates; empty until then. ContentHash sql.NullString `bun:"content_hash"` MissingAt sql.NullInt64 `bun:"missing_at"` } type libraryModel struct { bun.BaseModel `bun:"table:libraries"` ID int64 `bun:"id,pk,autoincrement"` Name string `bun:"name,notnull"` Path string `bun:"path,notnull"` Shared bool `bun:"shared,notnull"` CreatedAt int64 `bun:"created_at,notnull"` } func (m libraryModel) toLibrary() library.Library { return library.Library{ID: m.ID, Name: m.Name, Path: m.Path, Shared: m.Shared, CreatedAt: time.Unix(m.CreatedAt, 0)} } // LibraryRepo stores the scanned catalog and the libraries themselves. It // implements library.Store, library.LibraryStore and streaming.Source. type LibraryRepo struct{ db *bun.DB } // NewLibraryRepo builds a LibraryRepo. func NewLibraryRepo(db *bun.DB) *LibraryRepo { return &LibraryRepo{db: db} } // ExistingFiles lists the files indexed in a library, including missing ones. func (r *LibraryRepo) ExistingFiles(ctx context.Context, libraryID int64) ([]library.ExistingFile, error) { var rows []audioFileModel if err := r.db.NewSelect().Model(&rows).Where("library_id = ?", libraryID).Scan(ctx); err != nil { return nil, fmt.Errorf("select audio files: %w", err) } out := make([]library.ExistingFile, len(rows)) for i, row := range rows { out[i] = library.ExistingFile{ ID: row.ID, FileInfo: library.FileInfo{RelPath: row.RelPath, Size: row.SizeBytes, MTimeUnix: row.MTimeUnix}, Missing: row.MissingAt.Valid, } } return out, nil } // SaveFile creates or updates one file with its track, album and artists in a // single transaction. A MusicBrainz id is only stored when the file's own tags // say so; it is never invented. func (r *LibraryRepo) SaveFile(ctx context.Context, libraryID int64, f library.ParsedFile, existingID int64) (bool, error) { var added bool err := r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { // Artists are created and matched here and by discovery: take the turn once, // before any write, so two transactions cannot wait for each other. if err := lockArtistWrites(ctx, tx); err != nil { return err } artistIDs := make([]int64, 0, len(f.Artists)) seen := map[int64]bool{} for i, name := range f.Artists { mbid := "" if len(f.ArtistMBIDs) == len(f.Artists) { mbid = f.ArtistMBIDs[i] } id, err := findOrCreateArtist(ctx, tx, name, mbid) if err != nil { return err } if !seen[id] { seen[id] = true artistIDs = append(artistIDs, id) } } var albumID int64 if f.Album != "" { albumArtistID, err := findOrCreateArtist(ctx, tx, firstNonEmpty(f.AlbumArtist, library.UnknownArtist), f.AlbumArtistMBID) if err != nil { return err } if albumID, err = findOrCreateAlbum(ctx, tx, f.Album, albumArtistID, f.AlbumMBID); err != nil { return err } } track := &trackModel{ Title: f.Title, TitleKey: matchKey(f.Title), SearchKey: searchKey(f.Title), DurationMs: f.DurationMs, TrackNo: f.TrackNo, DiscNo: f.DiscNo, AlbumID: albumID, Year: f.Year, } if existingID == 0 { if _, err := tx.NewInsert().Model(track).Exec(ctx); err != nil { return fmt.Errorf("insert track: %w", err) } file := newAudioFile(libraryID, f, track.ID) if _, err := tx.NewInsert().Model(file).Exec(ctx); err != nil { return fmt.Errorf("insert audio file: %w", err) } added = true if err := clearAlbumCovers(ctx, tx, albumID); err != nil { return err } if err := replaceGenres(ctx, tx, track.ID, f.Genres); err != nil { return err } return replaceCredits(ctx, tx, track.ID, artistIDs) } var file audioFileModel if err := tx.NewSelect().Model(&file).Where("id = ?", existingID).Scan(ctx); err != nil { return fmt.Errorf("load audio file %d: %w", existingID, err) } track.ID = file.TrackID // The album it leaves, if it moves, and the one it is in now: their covers may change. var oldAlbum sql.NullInt64 if err := tx.QueryRowContext(ctx, "SELECT album_id FROM tracks WHERE id = ?", track.ID).Scan(&oldAlbum); err != nil { return fmt.Errorf("read the track's album: %w", err) } if err := clearAlbumCovers(ctx, tx, albumID, oldAlbum.Int64); err != nil { return err } if _, err := tx.NewUpdate().Model(track). Column("title", "title_key", "search_key", "duration_ms", "track_no", "disc_no", "album_id", "year"). WherePK().Exec(ctx); err != nil { return fmt.Errorf("update track: %w", err) } // A track that moved to another album may have left the first with none: that one gives // up the id it holds, which the album the track went to can then take. if oldAlbum.Valid && oldAlbum.Int64 != albumID { if err := releaseEmptyAlbumID(ctx, tx, oldAlbum.Int64); err != nil { return err } if err := claimAlbumID(ctx, tx, albumID, f.AlbumMBID); err != nil { return err } } // A hash says what the file held when it was read: a file that changed has no hash any more. if file.SizeBytes != f.Size || file.MTimeUnix != f.MTimeUnix { file.ContentHash = sql.NullString{} } file.Format, file.SizeBytes, file.MTimeUnix = f.Format, f.Size, f.MTimeUnix file.SampleRate, file.BitRate = f.SampleRate, f.BitRate file.setReplayGain(f.ReplayGain) file.MissingAt = sql.NullInt64{} if _, err := tx.NewUpdate().Model(&file). Column("format", "size_bytes", "mtime_unix", "sample_rate", "bitrate", "content_hash", "missing_at", "rg_track_gain", "rg_album_gain", "rg_track_peak", "rg_album_peak"). WherePK().Exec(ctx); err != nil { return fmt.Errorf("update audio file: %w", err) } if err := replaceGenres(ctx, tx, track.ID, f.Genres); err != nil { return err } return replaceCredits(ctx, tx, track.ID, artistIDs) }) if err != nil { return false, err } return added, nil } func newAudioFile(libraryID int64, f library.ParsedFile, trackID int64) *audioFileModel { file := &audioFileModel{ TrackID: trackID, LibraryID: libraryID, RelPath: f.RelPath, Format: f.Format, SizeBytes: f.Size, MTimeUnix: f.MTimeUnix, SampleRate: f.SampleRate, BitRate: f.BitRate, } file.setReplayGain(f.ReplayGain) return file } // setReplayGain keeps what the file says about its loudness; what it does not say is NULL. func (f *audioFileModel) setReplayGain(g library.ReplayGain) { f.RGTrackGain, f.RGAlbumGain = nullFloat(g.TrackGain), nullFloat(g.AlbumGain) f.RGTrackPeak, f.RGAlbumPeak = nullFloat(g.TrackPeak), nullFloat(g.AlbumPeak) } func nullFloat(v *float64) sql.NullFloat64 { if v == nil { return sql.NullFloat64{} } return sql.NullFloat64{Float64: *v, Valid: true} } // RenameFile points an indexed file at a new relative path and clears its missing flag. func (r *LibraryRepo) RenameFile(ctx context.Context, id int64, newRelPath string) error { _, err := r.db.NewUpdate().Model((*audioFileModel)(nil)). Set("rel_path = ?", newRelPath). Set("missing_at = NULL"). Where("id = ?", id).Exec(ctx) if err != nil { return fmt.Errorf("rename audio file: %w", err) } return nil } // MarkMissing flags files that were not found. Rows and tracks are kept. func (r *LibraryRepo) MarkMissing(ctx context.Context, ids []int64, at time.Time) error { if len(ids) == 0 { return nil } _, err := r.db.NewUpdate().Model((*audioFileModel)(nil)). Set("missing_at = ?", at.Unix()). Where("id IN (?)", bun.In(ids)).Exec(ctx) if err != nil { return fmt.Errorf("mark missing: %w", err) } return nil } // Restore clears the missing flag of files that reappeared. func (r *LibraryRepo) Restore(ctx context.Context, ids []int64) error { if len(ids) == 0 { return nil } _, err := r.db.NewUpdate().Model((*audioFileModel)(nil)). Set("missing_at = NULL"). Where("id IN (?)", bun.In(ids)).Exec(ctx) if err != nil { return fmt.Errorf("restore files: %w", err) } return nil } // PlayableFile returns the file to play for a track: the oldest one that was // not flagged missing, with the path of its library. It returns // streaming.ErrNotFound when the track does not exist or has no such file. It // implements streaming.Source. func (r *LibraryRepo) PlayableFile(ctx context.Context, trackID int64) (streaming.File, error) { return r.playable(ctx, trackID, false) } // Shared is the source of what friends may play: the same, but only files of libraries that are shared. func (r *LibraryRepo) Shared() streaming.Source { return sharedFiles{r} } type sharedFiles struct{ r *LibraryRepo } func (s sharedFiles) PlayableFile(ctx context.Context, trackID int64) (streaming.File, error) { return s.r.playable(ctx, trackID, true) } func (r *LibraryRepo) playable(ctx context.Context, trackID int64, sharedOnly bool) (streaming.File, error) { var row struct { Path string `bun:"path"` RelPath string `bun:"rel_path"` } q := r.db.NewSelect(). TableExpr("audio_files AS f"). ColumnExpr("l.path AS path, f.rel_path AS rel_path"). Join("JOIN libraries AS l ON l.id = f.library_id"). Where("f.track_id = ?", trackID). Where("f.missing_at IS NULL") if sharedOnly { q = q.Where("l.shared = ?", true) } err := q.OrderExpr("f.id").Limit(1).Scan(ctx, &row) if errors.Is(err, sql.ErrNoRows) { return streaming.File{}, streaming.ErrNotFound } if err != nil { return streaming.File{}, fmt.Errorf("select playable file: %w", err) } return streaming.File{Root: row.Path, RelPath: row.RelPath}, nil } // ---- libraries ---- // ListLibraries returns every library, oldest first. func (r *LibraryRepo) ListLibraries(ctx context.Context) ([]library.Library, error) { var rows []libraryModel if err := r.db.NewSelect().Model(&rows).OrderExpr("id").Scan(ctx); err != nil { return nil, fmt.Errorf("list libraries: %w", err) } out := make([]library.Library, len(rows)) for i, row := range rows { out[i] = row.toLibrary() } return out, nil } // GetLibrary returns one library, or library.ErrLibraryNotFound. func (r *LibraryRepo) GetLibrary(ctx context.Context, id int64) (library.Library, error) { var row libraryModel err := r.db.NewSelect().Model(&row).Where("id = ?", id).Limit(1).Scan(ctx) if errors.Is(err, sql.ErrNoRows) { return library.Library{}, library.ErrLibraryNotFound } if err != nil { return library.Library{}, fmt.Errorf("select library: %w", err) } return row.toLibrary(), nil } // CreateLibrary adds a library. Names are unique case-insensitively, paths exactly. func (r *LibraryRepo) CreateLibrary(ctx context.Context, name, path string) (library.Library, error) { row := &libraryModel{Name: name, Path: path, CreatedAt: time.Now().Unix()} if _, err := r.db.NewInsert().Model(row).Exec(ctx); err != nil { if isUniqueViolation(err) { return library.Library{}, library.ErrLibraryExists } return library.Library{}, fmt.Errorf("insert library: %w", err) } return row.toLibrary(), nil } // UpdateLibrary changes the name and path of a library. Its files keep their // identity because they point at the library by id. func (r *LibraryRepo) UpdateLibrary(ctx context.Context, id int64, name, path string) (library.Library, error) { res, err := r.db.NewUpdate().Model((*libraryModel)(nil)). Set("name = ?", name).Set("path = ?", path).Where("id = ?", id).Exec(ctx) if err != nil { if isUniqueViolation(err) { return library.Library{}, library.ErrLibraryExists } return library.Library{}, fmt.Errorf("update library: %w", err) } if n, _ := res.RowsAffected(); n == 0 { return library.Library{}, library.ErrLibraryNotFound } return r.GetLibrary(ctx, id) } // SetLibraryShared says whether friends may read a library. func (r *LibraryRepo) SetLibraryShared(ctx context.Context, id int64, shared bool) (library.Library, error) { res, err := r.db.NewUpdate().Model((*libraryModel)(nil)).Set("shared = ?", shared).Where("id = ?", id).Exec(ctx) if err != nil { return library.Library{}, fmt.Errorf("share library: %w", err) } if n, _ := res.RowsAffected(); n == 0 { return library.Library{}, library.ErrLibraryNotFound } return r.GetLibrary(ctx, id) } // DeleteLibrary removes a library and, through the foreign key, its indexed // files. Tracks stay, so listening history is kept. Music on disk is untouched. func (r *LibraryRepo) DeleteLibrary(ctx context.Context, id int64) error { res, err := r.db.NewDelete().Model((*libraryModel)(nil)).Where("id = ?", id).Exec(ctx) if err != nil { return fmt.Errorf("delete library: %w", err) } if n, _ := res.RowsAffected(); n == 0 { return library.ErrLibraryNotFound } return nil } // RefreshMatchKeys recomputes the match keys and the search keys of every artist, album and // track whose stored key differs from the current rule. It is idempotent and cheap when nothing // changed, so it can run at every start: rows created before migration 0003 only have an // approximate key from SQL lower(), and those from before migration 0016 have no search key. func (r *LibraryRepo) RefreshMatchKeys(ctx context.Context) error { return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { return refreshKeys(ctx, tx, []keyedTable{ {"artists", "name", "name_key"}, {"tracks", "title", "title_key"}, {"albums", "title", "title_key"}, }) }) } // keyedTable is a table with a text column and the keys made from it. type keyedTable struct{ table, text, matchKey string } func refreshKeys(ctx context.Context, tx bun.Tx, tables []keyedTable) error { for _, t := range tables { rows, err := tx.QueryContext(ctx, "SELECT id, "+t.text+", "+t.matchKey+", search_key FROM "+t.table) if err != nil { return fmt.Errorf("list %s: %w", t.table, err) } var fixes []keyFix for rows.Next() { var ( id int64 text, match, current string ) if err := rows.Scan(&id, &text, &match, ¤t); err != nil { _ = rows.Close() return err } m, s := matchKey(text), searchKey(text) if m != match || s != current { fixes = append(fixes, keyFix{id: id, match: m, find: s}) } } if err := rows.Err(); err != nil { _ = rows.Close() return err } _ = rows.Close() for start := 0; start < len(fixes); start += keyBatch { batch := fixes[start:min(start+keyBatch, len(fixes))] if err := updateKeys(ctx, tx, t, batch); err != nil { return err } } } return nil } // keyBatch is how many rows one UPDATE fixes: 500 rows are 1500 parameters, far below the limit // of both databases, and one round trip instead of 500 on PostgreSQL. const keyBatch = 500 // updateKeys fixes a batch of rows. On PostgreSQL it is one statement, and the rows come as a VALUES list, whose // columns are called column1, column2 and column3. func updateKeys(ctx context.Context, tx bun.Tx, t keyedTable, batch []keyFix) error { if tx.Dialect().Name() == dialect.SQLite { // The file is local: there is no round trip to save, and one UPDATE per row is faster // than the join with a VALUES list. for _, f := range batch { if _, err := tx.ExecContext(ctx, "UPDATE "+t.table+" SET "+t.matchKey+" = ?, search_key = ? WHERE id = ?", f.match, f.find, f.id); err != nil { return fmt.Errorf("update %s key: %w", t.table, err) } } return nil } var b strings.Builder args := make([]any, 0, len(batch)*3) for i, f := range batch { if i > 0 { b.WriteString(", ") } b.WriteString("(CAST(? AS BIGINT), CAST(? AS TEXT), CAST(? AS TEXT))") args = append(args, f.id, f.match, f.find) } q := "UPDATE " + t.table + " SET " + t.matchKey + " = v.column2, search_key = v.column3 FROM (VALUES " + b.String() + ") AS v WHERE " + t.table + ".id = v.column1" if _, err := tx.ExecContext(ctx, q, args...); err != nil { return fmt.Errorf("update %s keys: %w", t.table, err) } return nil } // keyFix is the new keys of one row. type keyFix struct { id int64 match, find string } // ---- helpers (run inside a transaction) ---- // findOrCreateArtist returns the artist a file belongs to, creating it if needed. // The caller holds the artist lock (lockArtistWrites) for the whole transaction: // taking it here, after other writes, could deadlock with discovery. // // mbid is what the file's own tags say ("" when they say nothing). // // With an id, two artists are the same when the ids are equal; another id under // the same name is another artist. A real artist of that name that has no id yet // receives the id, even when a discovered artist already holds it: that one // lets go of an id nobody checked, and the next answer from the provider lands // on the real artist. A discovered artist holding exactly this id is confirmed // by the file and becomes the user's with its data kept. // // Without an id the name decides, and a real artist is preferred to a // discovered one. A discovered artist adopted this way loses the provider's id, // and with it everything that was read under that id, since it is not known to // be about the user's artist. func findOrCreateArtist(ctx context.Context, tx bun.Tx, name, mbid string) (int64, error) { key := matchKey(name) mbid = strings.ToLower(strings.TrimSpace(mbid)) type row struct { ID int64 Discovered bool MBID string } lookup := func(where string, args ...any) (row, bool, error) { var ( r row m sql.NullString ) err := tx.QueryRowContext(ctx, "SELECT id, discovered, mbid FROM artists WHERE "+where+" ORDER BY discovered, id LIMIT 1", args...). Scan(&r.ID, &r.Discovered, &m) r.MBID = m.String if errors.Is(err, sql.ErrNoRows) { return r, false, nil } return r, err == nil, err } exec := func(what, query string, args ...any) error { if _, err := tx.ExecContext(ctx, query, args...); err != nil { return fmt.Errorf("%s: %w", what, err) } return nil } if mbid != "" { byID, haveID, err := lookup("mbid = ?", mbid) if err != nil { return 0, fmt.Errorf("find artist: %w", err) } if haveID && !byID.Discovered { return byID.ID, nil // a real artist already has this id, whatever its spelling } byName, haveName, err := lookup("name_key = ? AND (mbid IS NULL OR mbid = '')", key) if err != nil { return 0, fmt.Errorf("find artist: %w", err) } switch { case haveName && !byName.Discovered: if haveID { if err := exec("release discovered id", "UPDATE artists SET mbid = NULL WHERE id = ?", byID.ID); err != nil { return 0, err } } return byName.ID, exec("set artist id", "UPDATE artists SET mbid = ? WHERE id = ?", mbid, byName.ID) case haveID: return byID.ID, exec("confirm discovered artist", "UPDATE artists SET name = ?, name_key = ?, search_key = ?, discovered = ? WHERE id = ?", name, key, searchKey(name), false, byID.ID) case haveName: return byName.ID, exec("adopt discovered artist", "UPDATE artists SET name = ?, name_key = ?, search_key = ?, discovered = ?, mbid = ? WHERE id = ?", name, key, searchKey(name), false, mbid, byName.ID) } var id int64 if err := tx.QueryRowContext(ctx, "INSERT INTO artists (mbid, name, name_key, search_key, discovered) VALUES (?, ?, ?, ?, ?) RETURNING id", mbid, name, key, searchKey(name), false).Scan(&id); err != nil { return 0, fmt.Errorf("insert artist: %w", err) } return id, nil } found, ok, err := lookup("name_key = ?", key) if err != nil { return 0, fmt.Errorf("find artist: %w", err) } if !ok { a := artistModel{Name: name, NameKey: key, SearchKey: searchKey(name)} if _, err := tx.NewInsert().Model(&a).Exec(ctx); err != nil { return 0, fmt.Errorf("insert artist: %w", err) } return a.ID, nil } if found.Discovered { if err := exec("adopt discovered artist", "UPDATE artists SET name = ?, name_key = ?, search_key = ?, discovered = ?, mbid = NULL WHERE id = ?", name, key, searchKey(name), false, found.ID); err != nil { return 0, err } if found.MBID != "" { // What was read under the provider's id is not known to be about this artist. for _, q := range []string{ "DELETE FROM artist_tags WHERE artist_id = ?", "DELETE FROM provider_fetches WHERE artist_id = ?", "DELETE FROM artist_similar WHERE artist_id = ?", } { if err := exec("forget what was read with the dropped id", q, found.ID); err != nil { return 0, err } } } } return found.ID, nil } // replaceGenres makes the genres of a track those the file says now. The reader // already cleaned them; cleaning is idempotent, so a store used another way is safe too. func replaceGenres(ctx context.Context, tx bun.Tx, trackID int64, genres []string) error { if _, err := tx.ExecContext(ctx, "DELETE FROM track_genres WHERE track_id = ?", trackID); err != nil { return fmt.Errorf("clear genres: %w", err) } for _, g := range library.CleanGenres(genres) { if _, err := tx.ExecContext(ctx, "INSERT INTO track_genres (track_id, genre) VALUES (?, ?)", trackID, g); err != nil { return fmt.Errorf("insert genre: %w", err) } } return nil } // claimAlbumID gives an album the release group id a file carries, when it has none and no // other album owns the id (it is unique). It never replaces an id the album has, and a // refusal is not an error. func claimAlbumID(ctx context.Context, tx bun.Tx, albumID int64, mbid string) error { if mbid == "" || albumID == 0 { return nil } if _, err := tx.ExecContext(ctx, "UPDATE albums SET mbid = ? WHERE id = ? AND mbid IS NULL AND NOT EXISTS (SELECT 1 FROM albums o WHERE o.mbid = ?)", mbid, albumID, mbid); err != nil { return fmt.Errorf("set album id: %w", err) } return nil } // releaseEmptyAlbumID takes the release group id off an album that has no track any more. // Albums are never deleted, so otherwise the id would stay on that empty album for good and // the album the tracks moved to (a title that was fixed) could never have it. func releaseEmptyAlbumID(ctx context.Context, tx bun.Tx, albumID int64) error { if albumID == 0 { return nil } if _, err := tx.ExecContext(ctx, "UPDATE albums SET mbid = NULL WHERE id = ? AND mbid IS NOT NULL AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = ?)", albumID, albumID); err != nil { return fmt.Errorf("release album id: %w", err) } return nil } func findOrCreateAlbum(ctx context.Context, tx bun.Tx, title string, artistID int64, mbid string) (int64, error) { var a albumModel err := tx.NewSelect().Model(&a). Where("title_key = ?", matchKey(title)).Where("artist_id = ?", artistID). OrderExpr("id").Limit(1).Scan(ctx) if err == nil { // An album that has no id takes the one a file brings (a rescan after tagging). One that // has an id keeps it, and an id that another album already has (it is unique) is not given twice. if err := claimAlbumID(ctx, tx, a.ID, mbid); err != nil { return 0, err } return a.ID, nil } if !errors.Is(err, sql.ErrNoRows) { return 0, fmt.Errorf("find album: %w", err) } a = albumModel{Title: title, TitleKey: matchKey(title), SearchKey: searchKey(title), ArtistID: artistID} if mbid != "" { var taken bool if err := tx.QueryRowContext(ctx, "SELECT EXISTS (SELECT 1 FROM albums WHERE mbid = ?)", mbid).Scan(&taken); err != nil { return 0, fmt.Errorf("check album id: %w", err) } if !taken { a.MBID = mbid } } if _, err := tx.NewInsert().Model(&a).Exec(ctx); err != nil { return 0, fmt.Errorf("insert album: %w", err) } return a.ID, nil } func replaceCredits(ctx context.Context, tx bun.Tx, trackID int64, artistIDs []int64) error { if _, err := tx.NewDelete().Model((*trackArtistModel)(nil)).Where("track_id = ?", trackID).Exec(ctx); err != nil { return fmt.Errorf("clear credits: %w", err) } for _, id := range artistIDs { row := &trackArtistModel{TrackID: trackID, ArtistID: id, Role: "main"} if _, err := tx.NewInsert().Model(row).Exec(ctx); err != nil { return fmt.Errorf("insert credit: %w", err) } } return nil } // matchKey is the case-insensitive key used to match artist and album names. // It is computed here, not in SQL, so SQLite and PostgreSQL agree on Unicode. func matchKey(s string) string { return strings.ToLower(strings.TrimSpace(s)) } func firstNonEmpty(values ...string) string { for _, v := range values { if v != "" { return v } } return "" }