package storage import ( "context" "database/sql" "errors" "fmt" "github.com/uptrace/bun" "git.kmalloc.org/squid/sonda/internal/duplicates" ) // DuplicatesRepo reads the files that may be copies of one song, keeps their fingerprints, and // removes a copy from the index. It implements duplicates.Store. type DuplicatesRepo struct{ db *bun.DB } // NewDuplicatesRepo builds a DuplicatesRepo. func NewDuplicatesRepo(db *bun.DB) *DuplicatesRepo { return &DuplicatesRepo{db: db} } type candidateRow struct { TrackID int64 `bun:"track_id"` FileID int64 `bun:"file_id"` Title string `bun:"title"` TitleKey string `bun:"title_key"` DurationMs int64 `bun:"duration_ms"` AlbumID int64 `bun:"album_id"` Album string `bun:"album"` Format string `bun:"format"` BitRate int `bun:"bitrate"` SizeBytes int64 `bun:"size_bytes"` Hash string `bun:"hash"` } type creditRow struct { TrackID int64 `bun:"track_id"` ArtistID int64 `bun:"artist_id"` Name string `bun:"name"` } // idsPerQuery keeps a list of ids well under what a database accepts in one statement. const idsPerQuery = 500 // Candidates returns the playable files whose title is the title of another playable file: the only // ones that can have a copy. A track that has no file that is there, or more than one, is left out. // What makes two of them the same song (the artists, the durations) is decided by duplicates.Build. func (r *DuplicatesRepo) Candidates(ctx context.Context) ([]duplicates.Candidate, error) { var rows []candidateRow err := r.db.NewRaw(` SELECT t.id AS track_id, f.id AS file_id, t.title AS title, t.title_key AS title_key, COALESCE(t.duration_ms, 0) AS duration_ms, COALESCE(t.album_id, 0) AS album_id, COALESCE(al.title, '') AS album, f.format AS format, COALESCE(f.bitrate, 0) AS bitrate, f.size_bytes AS size_bytes, COALESCE(f.content_hash, '') AS hash FROM tracks t JOIN audio_files f ON f.track_id = t.id AND f.missing_at IS NULL LEFT JOIN albums al ON al.id = t.album_id WHERE t.id IN (SELECT track_id FROM audio_files WHERE missing_at IS NULL GROUP BY track_id HAVING COUNT(*) = 1) AND t.title_key IN ( SELECT t2.title_key FROM tracks t2 JOIN audio_files f2 ON f2.track_id = t2.id AND f2.missing_at IS NULL GROUP BY t2.title_key HAVING COUNT(*) > 1) ORDER BY t.id`).Scan(ctx, &rows) if err != nil { return nil, fmt.Errorf("select the candidates: %w", err) } if len(rows) == 0 { return nil, nil } ids := make([]int64, len(rows)) for i, row := range rows { ids[i] = row.TrackID } artists, err := r.mainArtists(ctx, ids) if err != nil { return nil, err } out := make([]duplicates.Candidate, len(rows)) for i, row := range rows { out[i] = duplicates.Candidate{ TrackID: row.TrackID, FileID: row.FileID, Artists: artists[row.TrackID], Title: row.Title, TitleKey: row.TitleKey, DurationMs: row.DurationMs, AlbumID: row.AlbumID, Album: row.Album, Format: row.Format, BitRate: row.BitRate, SizeBytes: row.SizeBytes, Hash: row.Hash, } } return out, nil } // mainArtists reads the main artists of some tracks. func (r *DuplicatesRepo) mainArtists(ctx context.Context, trackIDs []int64) (map[int64][]duplicates.ArtistRef, error) { out := map[int64][]duplicates.ArtistRef{} for start := 0; start < len(trackIDs); start += idsPerQuery { chunk := trackIDs[start:min(start+idsPerQuery, len(trackIDs))] var rows []creditRow err := r.db.NewRaw(` SELECT ta.track_id AS track_id, a.id AS artist_id, a.name AS name FROM track_artists ta JOIN artists a ON a.id = ta.artist_id WHERE ta.role = 'main' AND ta.track_id IN (?) ORDER BY ta.track_id, a.id`, bun.In(chunk)).Scan(ctx, &rows) if err != nil { return nil, fmt.Errorf("select the artists of the candidates: %w", err) } for _, row := range rows { out[row.TrackID] = append(out[row.TrackID], duplicates.ArtistRef{ID: row.ArtistID, Name: row.Name}) } } return out, nil } type fileRow struct { FileID int64 `bun:"file_id"` TrackID int64 `bun:"track_id"` Root string `bun:"root"` RelPath string `bun:"rel_path"` SizeBytes int64 `bun:"size_bytes"` MTimeUnix int64 `bun:"mtime_unix"` Hash string `bun:"hash"` Missing bool `bun:"missing"` } func (row fileRow) record() duplicates.FileRecord { return duplicates.FileRecord{ FileID: row.FileID, TrackID: row.TrackID, Root: row.Root, RelPath: row.RelPath, SizeBytes: row.SizeBytes, MTimeUnix: row.MTimeUnix, Hash: row.Hash, Missing: row.Missing, } } const fileSelect = ` SELECT f.id AS file_id, f.track_id AS track_id, l.path AS root, f.rel_path AS rel_path, f.size_bytes AS size_bytes, f.mtime_unix AS mtime_unix, COALESCE(f.content_hash, '') AS hash, (f.missing_at IS NOT NULL) AS missing FROM audio_files f JOIN libraries l ON l.id = f.library_id` // FileOf returns the file of a track: the oldest one that is there, else the oldest one. It returns // duplicates.ErrTrackNotFound when the track has no file. func (r *DuplicatesRepo) FileOf(ctx context.Context, trackID int64) (duplicates.FileRecord, error) { var row fileRow err := r.db.NewRaw(fileSelect+` WHERE f.track_id = ? ORDER BY (f.missing_at IS NOT NULL), f.id LIMIT 1`, trackID).Scan(ctx, &row) if errors.Is(err, sql.ErrNoRows) { return duplicates.FileRecord{}, duplicates.ErrTrackNotFound } if err != nil { return duplicates.FileRecord{}, fmt.Errorf("select the file of a track: %w", err) } return row.record(), nil } // FileByID returns one file, or duplicates.ErrTrackNotFound. func (r *DuplicatesRepo) FileByID(ctx context.Context, fileID int64) (duplicates.FileRecord, error) { var row fileRow err := r.db.NewRaw(fileSelect+` WHERE f.id = ?`, fileID).Scan(ctx, &row) if errors.Is(err, sql.ErrNoRows) { return duplicates.FileRecord{}, duplicates.ErrTrackNotFound } if err != nil { return duplicates.FileRecord{}, fmt.Errorf("select a file: %w", err) } return row.record(), nil } // SaveHash keeps the fingerprint of a file, but only if the file is still the one that was read: the // size and date are what they were. It says whether it was kept. func (r *DuplicatesRepo) SaveHash(ctx context.Context, fileID, sizeBytes, mtimeUnix int64, hash string) (bool, error) { res, err := r.db.NewUpdate().Model((*audioFileModel)(nil)). Set("content_hash = ?", hash). Where("id = ?", fileID).Where("size_bytes = ?", sizeBytes).Where("mtime_unix = ?", mtimeUnix). Exec(ctx) if err != nil { return false, fmt.Errorf("save the hash: %w", err) } n, err := res.RowsAffected() if err != nil { return false, fmt.Errorf("save the hash: %w", err) } return n > 0, nil } // RemoveCopy takes a track out of the index in favour of another that stays: the listens and the // playlist items of the removed track move to the one that stays (they would be deleted with it // otherwise), and the track goes, with its file, credits and genres. The covers of the albums // involved are built again. It is one transaction. func (r *DuplicatesRepo) RemoveCopy(ctx context.Context, keepTrackID, removeTrackID int64) error { if keepTrackID == removeTrackID { return duplicates.ErrInvalid } return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error { albums := map[int64]int64{} for _, id := range []int64{keepTrackID, removeTrackID} { var album sql.NullInt64 err := tx.QueryRowContext(ctx, "SELECT album_id FROM tracks WHERE id = ?", id).Scan(&album) if errors.Is(err, sql.ErrNoRows) { return duplicates.ErrTrackNotFound } if err != nil { return fmt.Errorf("read a track: %w", err) } albums[id] = album.Int64 } if _, err := tx.ExecContext(ctx, "UPDATE listening_events SET track_id = ? WHERE track_id = ?", keepTrackID, removeTrackID); err != nil { return fmt.Errorf("move the listens: %w", err) } // Playlist items go the same way: an item has its own id and position, so the song may appear // twice in a playlist and nothing shifts. if _, err := tx.ExecContext(ctx, "UPDATE playlist_items SET track_id = ? WHERE track_id = ?", keepTrackID, removeTrackID); err != nil { return fmt.Errorf("move the playlist items: %w", err) } if _, err := tx.ExecContext(ctx, "DELETE FROM tracks WHERE id = ?", removeTrackID); err != nil { return fmt.Errorf("remove the track: %w", err) } return clearAlbumCovers(ctx, tx, albums[keepTrackID], albums[removeTrackID]) }) }