SPB Git

Repository Public

LibraryStore.swift 49.6 KB 694 lines swift

1// Author: Simon-Pierre Boucher — contact@spboucher.ai2// File: Core/Persistence/LibraryStore.swift3// Description: The local library database: catalogue, playlists, favourites, downloads, pending operations, history, search.4import Foundation56public struct DownloadItem: Identifiable, Hashable, Sendable {7    public var id: String { trackID }8    public var trackID: String9    public var title: String10    public var artistName: String11    public var albumTitle: String12    public var artworkID: String?13    public var state: DownloadState14    public var bytesReceived: Int6415    public var bytesTotal: Int6416    public var error: String?17    public var queuedAt: Date18    public var completedAt: Date?19    public var relativePath: String?20    public var assetID: String?21}2223public struct PendingOp: Sendable, Identifiable {24    public enum Kind: String, Sendable { case favorite, playlistCreate, playlistRename, playlistDelete, playlistAdd, playlistSet }25    public var id: Int6426    public var kind: Kind27    public var payload: [String: String]28    public var list: [String]29    public var attempts: Int30}3132public struct LibraryStats: Sendable, Equatable {33    public var artists = 0, albums = 0, tracks = 034    public var downloadedTracks = 0, downloadedAlbums = 035    public var downloadedBytes: Int64 = 036    public var totalBytes: Int64 = 037    public init() {}38}3940public enum PinKind: String, Sendable { case album, playlist, library }41public enum AlbumSort: String, Sendable, CaseIterable { case artist, title, year, recent }42public enum TrackSort: String, Sendable, CaseIterable { case artist, title, recent }4344/// The local copy of the library: catalogue, the account's playlists and favourites, downloads, pending changes,45/// listening history. One SQLite file per account. It never needs the network: everything the interface shows46/// offline is read from here.47public actor LibraryStore {48    private let db: SQLiteConnection49    public nonisolated let path: String50    private var changeContinuations: [UUID: AsyncStream<Void>.Continuation] = [:]5152    public init(path: String) throws {53        self.path = path54        try FileManager.default.createDirectory(atPath: (path as NSString).deletingLastPathComponent, withIntermediateDirectories: true)55        db = try SQLiteConnection(path: path)56        try Self.migrate(db)57    }5859    public func close() { db.close() }6061    /// Yields after every change to the store; the interface reloads what it shows.62    public func changes() -> AsyncStream<Void> {63        let id = UUID()64        return AsyncStream(bufferingPolicy: .bufferingNewest(1)) { c in65            changeContinuations[id] = c66            c.onTermination = { [weak self] _ in Task { await self?.dropContinuation(id) } }67        }68    }69    private func dropContinuation(_ id: UUID) { changeContinuations[id] = nil }70    private func changed() { for c in changeContinuations.values { c.yield() } }7172    // MARK: Schema7374    private static let migrations: [String] = [75        """76        CREATE TABLE artists (id TEXT PRIMARY KEY, name TEXT NOT NULL, name_norm TEXT NOT NULL, rev INTEGER NOT NULL DEFAULT 0);77        CREATE TABLE albums (id TEXT PRIMARY KEY, title TEXT NOT NULL, title_norm TEXT NOT NULL, year INTEGER, artwork_id TEXT, artist_id TEXT NOT NULL,78            genre TEXT, created_at REAL NOT NULL, rev INTEGER NOT NULL DEFAULT 0);79        CREATE INDEX albums_artist ON albums (artist_id);80        CREATE TABLE tracks (id TEXT PRIMARY KEY, title TEXT NOT NULL, title_norm TEXT NOT NULL, track_no INTEGER, disc_no INTEGER NOT NULL DEFAULT 1,81            duration_ms INTEGER NOT NULL DEFAULT 0, year INTEGER, codec TEXT NOT NULL, container TEXT NOT NULL, sample_rate INTEGER, bit_depth INTEGER,82            channels INTEGER, bitrate INTEGER, web_playable TEXT, compat_state TEXT, artist_id TEXT NOT NULL, album_id TEXT NOT NULL, genre TEXT,83            lyrics_state TEXT, created_at REAL NOT NULL, asset_id TEXT, sha256 TEXT, size_bytes INTEGER NOT NULL DEFAULT 0, mime TEXT, compat_size INTEGER,84            removed_remote INTEGER NOT NULL DEFAULT 0, rev INTEGER NOT NULL DEFAULT 0);85        CREATE INDEX tracks_album ON tracks (album_id, disc_no, track_no);86        CREATE INDEX tracks_artist ON tracks (artist_id);87        CREATE TABLE playlists (id TEXT PRIMARY KEY, name TEXT NOT NULL, created_at REAL NOT NULL, updated_at REAL NOT NULL, base_updated_at TEXT,88            pending_create INTEGER NOT NULL DEFAULT 0, deleted INTEGER NOT NULL DEFAULT 0);89        CREATE TABLE playlist_entries (entry_id TEXT PRIMARY KEY, playlist_id TEXT NOT NULL, track_id TEXT NOT NULL, position INTEGER NOT NULL);90        CREATE INDEX playlist_entries_list ON playlist_entries (playlist_id, position);91        CREATE TABLE favorite_tracks (track_id TEXT PRIMARY KEY, created_at REAL NOT NULL);92        CREATE TABLE favorite_albums (album_id TEXT PRIMARY KEY, created_at REAL NOT NULL);93        CREATE TABLE downloads (track_id TEXT PRIMARY KEY, state TEXT NOT NULL, bytes_received INTEGER NOT NULL DEFAULT 0, bytes_total INTEGER NOT NULL DEFAULT 0,94            rel_path TEXT, asset_id TEXT, sha256 TEXT, error TEXT, attempts INTEGER NOT NULL DEFAULT 0, queued_at REAL NOT NULL, completed_at REAL);95        CREATE INDEX downloads_state ON downloads (state, queued_at);96        CREATE TABLE pins (kind TEXT NOT NULL, id TEXT NOT NULL, PRIMARY KEY (kind, id));97        CREATE TABLE ops (id INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT NOT NULL, payload TEXT NOT NULL, created_at REAL NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, last_error TEXT);98        CREATE TABLE plays (id TEXT PRIMARY KEY, track_id TEXT NOT NULL, played_at REAL NOT NULL, listened_ms INTEGER NOT NULL DEFAULT 0, completed INTEGER NOT NULL DEFAULT 0,99            source TEXT NOT NULL DEFAULT 'stream', synced INTEGER NOT NULL DEFAULT 0);100        CREATE INDEX plays_recent ON plays (played_at DESC);101        CREATE TABLE lyrics (track_id TEXT PRIMARY KEY, json TEXT NOT NULL, fetched_at REAL NOT NULL);102        CREATE TABLE loudness (asset_id TEXT PRIMARY KEY, lufs REAL NOT NULL, peak REAL NOT NULL);103        CREATE TABLE notices (id INTEGER PRIMARY KEY AUTOINCREMENT, at REAL NOT NULL, text TEXT NOT NULL);104        CREATE TABLE kv (key TEXT PRIMARY KEY, value TEXT NOT NULL);105        CREATE VIRTUAL TABLE search USING fts5(title, artist, album, genre, tokenize = 'unicode61 remove_diacritics 2');106        """,107    ]108109    private static func migrate(_ db: SQLiteConnection) throws {110        let version = try db.scalarInt("PRAGMA user_version")111        for (i, sql) in migrations.enumerated() where i >= version {112            try db.transaction { try db.exec(sql); try db.exec("PRAGMA user_version = \(i + 1)") }113        }114    }115116    // MARK: Key-value117118    public func value(_ key: String) -> String? { (try? db.scalarText("SELECT value FROM kv WHERE key = ?", [.text(key)])) ?? nil }119    public func setValue(_ key: String, _ value: String?) {120        if let value { _ = try? db.run("INSERT INTO kv (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", [.text(key), .text(value)]) }121        else { _ = try? db.run("DELETE FROM kv WHERE key = ?", [.text(key)]) }122    }123    public var libraryRevision: Int64 { Int64(value("library.rev") ?? "0") ?? 0 }124125    // MARK: Applying what the cluster sends126127    /// Applies one page of catalogue changes. Idempotent: the same page applied twice leaves the same state.128    public func apply(_ d: WireLibraryDelta) throws {129        try db.transaction {130            for a in d.artists {131                try db.run("INSERT INTO artists (id, name, name_norm, rev) VALUES (?,?,?,?) ON CONFLICT(id) DO UPDATE SET name = excluded.name, name_norm = excluded.name_norm, rev = excluded.rev",132                           [.text(a.id), .text(a.name), .text(TextNorm.norm(a.name)), .int(a.rev)])133            }134            for a in d.albums {135                try db.run("""136                    INSERT INTO albums (id, title, title_norm, year, artwork_id, artist_id, genre, created_at, rev) VALUES (?,?,?,?,?,?,?,?,?)137                    ON CONFLICT(id) DO UPDATE SET title = excluded.title, title_norm = excluded.title_norm, year = excluded.year, artwork_id = excluded.artwork_id,138                      artist_id = excluded.artist_id, genre = excluded.genre, created_at = excluded.created_at, rev = excluded.rev139                    """, [.text(a.id), .text(a.title), .text(TextNorm.norm(a.title)), .of(a.year), .of(a.artworkId), .text(a.artistId), .of(a.genre), .of(a.createdAt), .int(a.rev)])140            }141            for t in d.tracks {142                try db.run("""143                    INSERT INTO tracks (id, title, title_norm, track_no, disc_no, duration_ms, year, codec, container, sample_rate, bit_depth, channels, bitrate, web_playable,144                      compat_state, artist_id, album_id, genre, lyrics_state, created_at, asset_id, sha256, size_bytes, mime, compat_size, removed_remote, rev)145                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,?)146                    ON CONFLICT(id) DO UPDATE SET title = excluded.title, title_norm = excluded.title_norm, track_no = excluded.track_no, disc_no = excluded.disc_no,147                      duration_ms = excluded.duration_ms, year = excluded.year, codec = excluded.codec, container = excluded.container, sample_rate = excluded.sample_rate,148                      bit_depth = excluded.bit_depth, channels = excluded.channels, bitrate = excluded.bitrate, web_playable = excluded.web_playable,149                      compat_state = excluded.compat_state, artist_id = excluded.artist_id, album_id = excluded.album_id, genre = excluded.genre,150                      lyrics_state = excluded.lyrics_state, created_at = excluded.created_at, asset_id = excluded.asset_id, sha256 = excluded.sha256,151                      size_bytes = excluded.size_bytes, mime = excluded.mime, compat_size = excluded.compat_size, removed_remote = 0, rev = excluded.rev152                    """, [.text(t.id), .text(t.title), .text(TextNorm.norm(t.title)), .of(t.trackNo), .int(Int64(t.discNo)), .int(Int64(t.durationMs)), .of(t.year), .text(t.codec),153                          .text(t.container), .of(t.sampleRate), .of(t.bitDepth), .of(t.channels), .of(t.bitrate), .text(t.webPlayable), .text(t.compatState), .text(t.artistId),154                          .text(t.albumId), .of(t.genre), .of(t.lyricsState), .of(t.createdAt), .of(t.file?.assetId), .of(t.file?.sha256), .int(t.file?.sizeBytes ?? 0),155                          .of(t.file?.mime), .of(t.compat?.sizeBytes), .int(t.rev)])156                // Lyrics cached for a track whose lyrics changed on the cluster are fetched again on demand.157                try db.run("DELETE FROM lyrics WHERE track_id = ?", [.text(t.id)])158            }159            for x in d.deleted where x.kind == "track" { try removeTrackLocked(x.id) }160            for x in d.deleted where x.kind == "album" { try db.run("DELETE FROM albums WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks WHERE album_id = ?)", [.text(x.id), .text(x.id)]) }161            for x in d.deleted where x.kind == "artist" {162                try db.run("DELETE FROM artists WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks WHERE artist_id = ?) AND NOT EXISTS (SELECT 1 FROM albums WHERE artist_id = ?)",163                           [.text(x.id), .text(x.id), .text(x.id)])164            }165            try reindexLocked(trackIDs: d.tracks.map(\.id), albumIDs: d.albums.map(\.id), artistIDs: d.artists.map(\.id))166            try markStaleDownloadsLocked()167            try db.run("INSERT INTO kv (key, value) VALUES ('library.rev', ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", [.text(String(d.rev))])168        }169        changed()170    }171172    /// A track deleted on the cluster: its row goes, unless its file was downloaded — then both are kept and the173    /// track is flagged, so nothing the listener chose to keep disappears on its own.174    private func removeTrackLocked(_ id: String) throws {175        let kept = try db.scalarInt("SELECT count(*) FROM downloads WHERE track_id = ? AND state = 'done'", [.text(id)]) > 0176        if kept { try db.run("UPDATE tracks SET removed_remote = 1 WHERE id = ?", [.text(id)]); return }177        try db.run("DELETE FROM search WHERE rowid = (SELECT rowid FROM tracks WHERE id = ?)", [.text(id)])178        try db.run("DELETE FROM tracks WHERE id = ?", [.text(id)])179        try db.run("DELETE FROM downloads WHERE track_id = ?", [.text(id)])180        try db.run("DELETE FROM playlist_entries WHERE track_id = ?", [.text(id)])181        try db.run("DELETE FROM favorite_tracks WHERE track_id = ?", [.text(id)])182    }183184    /// After a complete resynchronisation: whatever the cluster did not send no longer exists there.185    public func prune(keepingArtists artists: Set<String>, albums: Set<String>, tracks: Set<String>) throws {186        try db.transaction {187            for id in try db.query("SELECT id FROM tracks WHERE removed_remote = 0", [], { $0.text(0) }) where !tracks.contains(id) { try removeTrackLocked(id) }188            for id in try db.query("SELECT id FROM albums", [], { $0.text(0) }) where !albums.contains(id) {189                try db.run("DELETE FROM albums WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks WHERE album_id = ?)", [.text(id), .text(id)])190            }191            for id in try db.query("SELECT id FROM artists", [], { $0.text(0) }) where !artists.contains(id) {192                try db.run("DELETE FROM artists WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks WHERE artist_id = ?) AND NOT EXISTS (SELECT 1 FROM albums WHERE artist_id = ?)",193                           [.text(id), .text(id), .text(id)])194            }195        }196        changed()197    }198199    private func reindexLocked(trackIDs: [String], albumIDs: [String], artistIDs: [String]) throws {200        var ids = Set(trackIDs)201        for a in albumIDs { ids.formUnion(try db.query("SELECT id FROM tracks WHERE album_id = ?", [.text(a)]) { $0.text(0) }) }202        for a in artistIDs {203            ids.formUnion(try db.query("SELECT id FROM tracks WHERE artist_id = ? OR album_id IN (SELECT id FROM albums WHERE artist_id = ?)", [.text(a), .text(a)]) { $0.text(0) })204        }205        // The index row of a track has the track's rowid: replacing or deleting it is a key lookup, whatever the library's size.206        for id in ids {207            try db.run("DELETE FROM search WHERE rowid = (SELECT rowid FROM tracks WHERE id = ?)", [.text(id)])208            try db.run("""209                INSERT INTO search (rowid, title, artist, album, genre)210                SELECT t.rowid, t.title, coalesce(ar.name, '') || ' ' || coalesce(aar.name, ''), coalesce(al.title, ''), coalesce(t.genre, '')211                FROM tracks t LEFT JOIN artists ar ON ar.id = t.artist_id LEFT JOIN albums al ON al.id = t.album_id LEFT JOIN artists aar ON aar.id = al.artist_id WHERE t.id = ?212                """, [.text(id)])213        }214    }215216    /// A downloaded file whose original was replaced on the cluster is fetched again (the old file stays until the new one is verified).217    private func markStaleDownloadsLocked() throws {218        try db.run("""219            UPDATE downloads SET state = 'queued', bytes_received = 0, error = NULL, attempts = 0220            WHERE state = 'done' AND asset_id IS NOT NULL AND EXISTS (SELECT 1 FROM tracks t WHERE t.id = downloads.track_id AND t.removed_remote = 0 AND t.asset_id IS NOT NULL AND t.asset_id <> downloads.asset_id)221            """)222    }223224    public func counts() -> WireCounts {225        WireCounts(artists: (try? db.scalarInt("SELECT count(*) FROM artists")) ?? 0,226                   albums: (try? db.scalarInt("SELECT count(*) FROM albums")) ?? 0,227                   tracks: (try? db.scalarInt("SELECT count(*) FROM tracks WHERE removed_remote = 0")) ?? 0)228    }229230    public func removedTrackCount() -> Int { (try? db.scalarInt("SELECT count(*) FROM tracks WHERE removed_remote = 1")) ?? 0 }231    public func albumArtworkIDs() -> [String] { (try? db.query("SELECT DISTINCT artwork_id FROM albums WHERE artwork_id IS NOT NULL", []) { $0.text(0) }) ?? [] }232233    /// Replaces the account's playlists and favourites with the cluster's. Only called when no local change is waiting.234    public func apply(_ s: WireUserSnapshot) throws {235        try db.transaction {236            try db.run("DELETE FROM playlist_entries WHERE playlist_id IN (SELECT id FROM playlists WHERE pending_create = 0)")237            try db.run("DELETE FROM playlists WHERE pending_create = 0")238            for p in s.playlists {239                try db.run("INSERT INTO playlists (id, name, created_at, updated_at, base_updated_at) VALUES (?,?,?,?,?)",240                           [.text(p.id), .text(p.name), .of(p.createdAt), .of(p.updatedAt), .text(APIClient.iso(p.updatedAt))])241                for e in p.entries {242                    try db.run("INSERT OR REPLACE INTO playlist_entries (entry_id, playlist_id, track_id, position) VALUES (?,?,?,?)", [.text(e.entryId), .text(p.id), .text(e.trackId), .int(Int64(e.position))])243                }244            }245            try db.run("DELETE FROM favorite_tracks"); try db.run("DELETE FROM favorite_albums")246            for f in s.favoriteTracks { try db.run("INSERT OR REPLACE INTO favorite_tracks (track_id, created_at) VALUES (?,?)", [.text(f.id), .of(f.createdAt)]) }247            for f in s.favoriteAlbums { try db.run("INSERT OR REPLACE INTO favorite_albums (album_id, created_at) VALUES (?,?)", [.text(f.id), .of(f.createdAt)]) }248            try db.run("DELETE FROM pins WHERE kind = 'playlist' AND id NOT IN (SELECT id FROM playlists)")249        }250        changed()251    }252253    // MARK: Reading — tracks254255    private static let trackSelect = """256        SELECT t.id, t.title, t.track_no, t.disc_no, t.duration_ms, t.year, t.codec, t.container, t.sample_rate, t.bit_depth, t.channels, t.bitrate,257          t.artist_id, coalesce(ar.name, ''), t.album_id, coalesce(al.title, ''), coalesce(aar.name, ''), al.artwork_id, t.genre, t.lyrics_state,258          t.asset_id, t.sha256, t.size_bytes, t.mime, t.compat_size, EXISTS (SELECT 1 FROM favorite_tracks f WHERE f.track_id = t.id),259          d.state, d.rel_path, t.removed_remote260        FROM tracks t LEFT JOIN artists ar ON ar.id = t.artist_id LEFT JOIN albums al ON al.id = t.album_id LEFT JOIN artists aar ON aar.id = al.artist_id261          LEFT JOIN downloads d ON d.track_id = t.id262        """263    private static func track(_ r: SQLRow, offset o: Int32 = 0) -> Track {264        Track(id: r.text(o), title: r.text(o + 1), trackNo: r.optInt(o + 2), discNo: r.int(o + 3), durationMs: r.int(o + 4), year: r.optInt(o + 5), codec: r.text(o + 6),265              container: r.text(o + 7), sampleRate: r.optInt(o + 8), bitDepth: r.optInt(o + 9), channels: r.optInt(o + 10), bitrate: r.optInt(o + 11), artistID: r.text(o + 12),266              artistName: r.text(o + 13), albumID: r.text(o + 14), albumTitle: r.text(o + 15), albumArtistName: r.text(o + 16), artworkID: r.string(o + 17), genre: r.string(o + 18),267              lyricsState: r.string(o + 19), assetID: r.string(o + 20), sha256: r.string(o + 21), sizeBytes: r.int64(o + 22), mime: r.string(o + 23), hasCompatRendition: r.optInt(o + 24) != nil, compatSizeBytes: r.optInt(o + 24).map(Int64.init),268              favorite: r.bool(o + 25), downloadState: r.string(o + 26).flatMap(DownloadState.init(rawValue:)), localRelativePath: r.string(o + 27), removedRemotely: r.bool(o + 28))269    }270    private static let onlyDownloaded = " d.state = 'done' "271272    public func tracks(sort: TrackSort = .artist, downloadedOnly: Bool = false, limit: Int = 100_000, offset: Int = 0) -> [Track] {273        let order = switch sort {274        case .artist: "aar.name_norm, al.year, al.title_norm, t.disc_no, t.track_no"275        case .title: "t.title_norm"276        case .recent: "t.created_at DESC, al.title_norm, t.disc_no, t.track_no"277        }278        return (try? db.query(Self.trackSelect + (downloadedOnly ? " WHERE" + Self.onlyDownloaded : "") + " ORDER BY \(order) LIMIT ? OFFSET ?", [.int(Int64(limit)), .int(Int64(offset))]) { Self.track($0) }) ?? []279    }280    public func track(_ id: String) -> Track? { (try? db.query(Self.trackSelect + " WHERE t.id = ?", [.text(id)]) { Self.track($0) })?.first }281    public func tracks(ids: [String]) -> [Track] {282        var by: [String: Track] = [:]283        for chunk in stride(from: 0, to: ids.count, by: 400).map({ Array(ids[$0..<min($0 + 400, ids.count)]) }) {284            let marks = Array(repeating: "?", count: chunk.count).joined(separator: ",")285            for t in (try? db.query(Self.trackSelect + " WHERE t.id IN (\(marks))", chunk.map { .text($0) }) { Self.track($0) }) ?? [] { by[t.id] = t }286        }287        return ids.compactMap { by[$0] }288    }289    public func albumTracks(_ albumID: String, downloadedOnly: Bool = false) -> [Track] {290        (try? db.query(Self.trackSelect + " WHERE t.album_id = ?" + (downloadedOnly ? " AND" + Self.onlyDownloaded : "") + " ORDER BY t.disc_no, t.track_no IS NULL, t.track_no, t.title_norm", [.text(albumID)]) { Self.track($0) }) ?? []291    }292    public func artistTracks(_ artistID: String, downloadedOnly: Bool = false) -> [Track] {293        (try? db.query(Self.trackSelect + " WHERE t.artist_id = ?" + (downloadedOnly ? " AND" + Self.onlyDownloaded : "") + " ORDER BY al.year DESC, al.title_norm, t.disc_no, t.track_no LIMIT 1000", [.text(artistID)]) { Self.track($0) }) ?? []294    }295    public func favoriteTracks(downloadedOnly: Bool = false) -> [Track] {296        (try? db.query(Self.trackSelect + " JOIN favorite_tracks fv ON fv.track_id = t.id" + (downloadedOnly ? " WHERE" + Self.onlyDownloaded : "") + " ORDER BY fv.created_at DESC") { Self.track($0) }) ?? []297    }298    public func trackCount(downloadedOnly: Bool = false) -> Int {299        (try? db.scalarInt(downloadedOnly ? "SELECT count(*) FROM downloads WHERE state = 'done'" : "SELECT count(*) FROM tracks")) ?? 0300    }301    public func allTrackIDs() -> [String] { (try? db.query("SELECT id FROM tracks WHERE removed_remote = 0", []) { $0.text(0) }) ?? [] }302303    // MARK: Reading — albums and artists304305    private static let albumSelect = """306        SELECT al.id, al.title, al.year, al.artwork_id, al.artist_id, coalesce(aar.name, ''), al.genre, al.created_at,307          (SELECT count(*) FROM tracks t WHERE t.album_id = al.id),308          (SELECT coalesce(sum(t.duration_ms), 0) FROM tracks t WHERE t.album_id = al.id),309          EXISTS (SELECT 1 FROM favorite_albums f WHERE f.album_id = al.id),310          (SELECT count(*) FROM tracks t JOIN downloads d ON d.track_id = t.id AND d.state = 'done' WHERE t.album_id = al.id) AS dl311        FROM albums al LEFT JOIN artists aar ON aar.id = al.artist_id312        """313    private static func album(_ r: SQLRow) -> Album {314        Album(id: r.text(0), title: r.text(1), year: r.optInt(2), artworkID: r.string(3), artistID: r.text(4), artistName: r.text(5), genre: r.string(6), createdAt: r.date(7),315              trackCount: r.int(8), durationMs: r.int(9), favorite: r.bool(10), downloadedCount: r.int(11))316    }317    public func albums(sort: AlbumSort = .artist, downloadedOnly: Bool = false, limit: Int = 100_000) -> [Album] {318        let order = switch sort {319        case .artist: "aar.name_norm, al.year, al.title_norm"320        case .title: "al.title_norm"321        case .year: "al.year DESC, al.title_norm"322        case .recent: "al.created_at DESC, al.title_norm"323        }324        return (try? db.query(Self.albumSelect + (downloadedOnly ? " WHERE dl > 0" : "") + " ORDER BY \(order) LIMIT ?", [.int(Int64(limit))]) { Self.album($0) }) ?? []325    }326    public func album(_ id: String) -> Album? { (try? db.query(Self.albumSelect + " WHERE al.id = ?", [.text(id)]) { Self.album($0) })?.first }327    public func favoriteAlbums() -> [Album] {328        (try? db.query(Self.albumSelect + " JOIN favorite_albums fa ON fa.album_id = al.id ORDER BY fa.created_at DESC") { Self.album($0) }) ?? []329    }330    public func artistAlbums(_ artistID: String, downloadedOnly: Bool = false) -> [Album] {331        (try? db.query(Self.albumSelect + " WHERE (al.artist_id = ? OR EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND t.artist_id = ?))" + (downloadedOnly ? " AND dl > 0" : "") + " ORDER BY al.year DESC, al.title_norm",332                       [.text(artistID), .text(artistID)]) { Self.album($0) }) ?? []333    }334335    private static let artistSelect = """336        SELECT a.id, a.name, (SELECT count(*) FROM albums al WHERE al.artist_id = a.id), (SELECT count(*) FROM tracks t WHERE t.artist_id = a.id),337          (SELECT al.artwork_id FROM albums al WHERE al.artist_id = a.id AND al.artwork_id IS NOT NULL ORDER BY al.year DESC LIMIT 1)338        FROM artists a339        """340    private static func artist(_ r: SQLRow) -> Artist { Artist(id: r.text(0), name: r.text(1), albumCount: r.int(2), trackCount: r.int(3), artworkID: r.string(4)) }341    public func artists(downloadedOnly: Bool = false) -> [Artist] {342        let filter = downloadedOnly343            ? " WHERE EXISTS (SELECT 1 FROM tracks t JOIN downloads d ON d.track_id = t.id AND d.state = 'done' LEFT JOIN albums al ON al.id = t.album_id WHERE t.artist_id = a.id OR al.artist_id = a.id)"344            : " WHERE EXISTS (SELECT 1 FROM albums al WHERE al.artist_id = a.id) OR EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = a.id)"345        return (try? db.query(Self.artistSelect + filter + " ORDER BY a.name_norm") { Self.artist($0) }) ?? []346    }347    public func artist(_ id: String) -> Artist? { (try? db.query(Self.artistSelect + " WHERE a.id = ?", [.text(id)]) { Self.artist($0) })?.first }348349    // MARK: Search (works offline; FTS5 with accent folding and prefix matching)350351    public struct SearchResults: Sendable { public var artists: [Artist] = []; public var albums: [Album] = []; public var tracks: [Track] = []352        public var isEmpty: Bool { artists.isEmpty && albums.isEmpty && tracks.isEmpty }353        public init() {} }354355    public func search(_ query: String, downloadedOnly: Bool = false) -> SearchResults {356        var out = SearchResults()357        let needle = TextNorm.norm(query)358        let tokens = needle.split(whereSeparator: { !($0.isLetter || $0.isNumber) }).map(String.init)359        guard !tokens.isEmpty else { return out }360        let match = tokens.map { "\"\($0)\"*" }.joined(separator: " ")361        let like = "%" + needle.replacingOccurrences(of: "\\", with: "\\\\").replacingOccurrences(of: "%", with: "\\%").replacingOccurrences(of: "_", with: "\\_") + "%"362        out.tracks = (try? db.query(Self.trackSelect + " JOIN search s ON s.rowid = t.rowid WHERE search MATCH ?" + (downloadedOnly ? " AND" + Self.onlyDownloaded : "") + " ORDER BY bm25(search, 10, 4, 3, 1) LIMIT 80",363                                    [.text(match)]) { Self.track($0) }) ?? []364        out.albums = (try? db.query(Self.albumSelect + " WHERE (al.title_norm LIKE ? ESCAPE '\\' OR aar.name_norm LIKE ? ESCAPE '\\')" + (downloadedOnly ? " AND dl > 0" : "") + " ORDER BY (al.title_norm LIKE ? ESCAPE '\\') DESC, al.title_norm LIMIT 24",365                                    [.text(like), .text(like), .text(like)]) { Self.album($0) }) ?? []366        var artists = (try? db.query(Self.artistSelect + " WHERE a.name_norm LIKE ? ESCAPE '\\' ORDER BY a.name_norm LIMIT 12", [.text(like)]) { Self.artist($0) }) ?? []367        if downloadedOnly { let ok = Set(self.artists(downloadedOnly: true).map(\.id)); artists.removeAll { !ok.contains($0.id) } }368        out.artists = artists369        return out370    }371372    // MARK: Playlists and favourites (local first; each change is queued for the cluster)373374    public func playlists() -> [Playlist] {375        let rows = (try? db.query("""376            SELECT p.id, p.name, p.updated_at, (SELECT count(*) FROM playlist_entries e WHERE e.playlist_id = p.id),377              (SELECT count(*) FROM playlist_entries e JOIN downloads d ON d.track_id = e.track_id AND d.state = 'done' WHERE e.playlist_id = p.id),378              p.pending_create, EXISTS (SELECT 1 FROM pins WHERE kind = 'playlist' AND id = p.id)379            FROM playlists p WHERE p.deleted = 0 ORDER BY p.updated_at DESC380            """) { Playlist(id: $0.text(0), name: $0.text(1), updatedAt: $0.date(2), trackCount: $0.int(3), artworkIDs: [], downloadedCount: $0.int(4), pendingCreation: $0.bool(5), pinned: $0.bool(6)) }) ?? []381        return rows.map { p in382            var p = p383            let arts = (try? db.query("SELECT al.artwork_id FROM playlist_entries e JOIN tracks t ON t.id = e.track_id JOIN albums al ON al.id = t.album_id WHERE e.playlist_id = ? AND al.artwork_id IS NOT NULL ORDER BY e.position",384                                      [.text(p.id)]) { $0.text(0) }) ?? []385            var seen = Set<String>(); p.artworkIDs = arts.filter { seen.insert($0).inserted }.prefix(4).map { $0 }386            return p387        }388    }389    public func playlist(_ id: String) -> Playlist? { playlists().first { $0.id == id } }390    public func playlistEntries(_ id: String) -> [PlaylistEntry] {391        let sql = Self.trackSelect.replacingOccurrences(of: "SELECT t.id,", with: "SELECT e.entry_id, t.id,") + " JOIN playlist_entries e ON e.track_id = t.id WHERE e.playlist_id = ? ORDER BY e.position"392        return (try? db.query(sql, [.text(id)]) { PlaylistEntry(id: $0.text(0), track: Self.track($0, offset: 1)) }) ?? []393    }394395    private func enqueue(_ kind: PendingOp.Kind, _ payload: [String: String], list: [String] = []) throws {396        var p: [String: Any] = payload; p["list"] = list397        let json = String(data: try JSONSerialization.data(withJSONObject: p), encoding: .utf8) ?? "{}"398        try db.run("INSERT INTO ops (kind, payload, created_at) VALUES (?,?,?)", [.text(kind.rawValue), .text(json), .of(Date())])399    }400    private func dropOps(kinds: [PendingOp.Kind], playlistID: String) throws {401        for op in pendingOps() where kinds.contains(op.kind) && op.payload["playlistID"] == playlistID { try db.run("DELETE FROM ops WHERE id = ?", [.int(op.id)]) }402    }403    private func isPendingCreate(_ id: String) -> Bool { ((try? db.scalarInt("SELECT pending_create FROM playlists WHERE id = ?", [.text(id)])) ?? 0) == 1 }404    private func touchPlaylist(_ id: String) throws { try db.run("UPDATE playlists SET updated_at = ? WHERE id = ?", [.of(Date()), .text(id)]) }405406    public func setFavorite(kind: String, id: String, on: Bool) throws {407        try db.transaction {408            let table = kind == "album" ? "favorite_albums" : "favorite_tracks", col = kind == "album" ? "album_id" : "track_id"409            if on { try db.run("INSERT OR REPLACE INTO \(table) (\(col), created_at) VALUES (?,?)", [.text(id), .of(Date())]) }410            else { try db.run("DELETE FROM \(table) WHERE \(col) = ?", [.text(id)]) }411            // Only the last intention for a given item matters.412            for op in pendingOps() where op.kind == .favorite && op.payload["kind"] == kind && op.payload["id"] == id { try db.run("DELETE FROM ops WHERE id = ?", [.int(op.id)]) }413            try enqueue(.favorite, ["kind": kind, "id": id, "on": on ? "1" : "0"])414        }415        changed()416    }417418    @discardableResult419    public func createPlaylist(name: String, trackIDs: [String] = []) throws -> String {420        let id = "local-" + UUID().uuidString.lowercased()421        try db.transaction {422            try db.run("INSERT INTO playlists (id, name, created_at, updated_at, pending_create) VALUES (?,?,?,?,1)", [.text(id), .text(name), .of(Date()), .of(Date())])423            for (i, t) in trackIDs.enumerated() {424                try db.run("INSERT INTO playlist_entries (entry_id, playlist_id, track_id, position) VALUES (?,?,?,?)", [.text("local-" + UUID().uuidString.lowercased()), .text(id), .text(t), .int(Int64(i))])425            }426            try enqueue(.playlistCreate, ["playlistID": id])427        }428        changed()429        return id430    }431432    public func renamePlaylist(_ id: String, name: String) throws {433        try db.transaction {434            try db.run("UPDATE playlists SET name = ? WHERE id = ?", [.text(name), .text(id)]); try touchPlaylist(id)435            if !isPendingCreate(id) { try dropOps(kinds: [.playlistRename], playlistID: id); try enqueue(.playlistRename, ["playlistID": id, "name": name]) }436        }437        changed()438    }439440    public func deletePlaylist(_ id: String) throws {441        try db.transaction {442            try dropOps(kinds: [.playlistRename, .playlistAdd, .playlistSet, .playlistCreate], playlistID: id)443            if isPendingCreate(id) {444                try db.run("DELETE FROM playlist_entries WHERE playlist_id = ?", [.text(id)]); try db.run("DELETE FROM playlists WHERE id = ?", [.text(id)])445            } else {446                try db.run("UPDATE playlists SET deleted = 1 WHERE id = ?", [.text(id)])447                try enqueue(.playlistDelete, ["playlistID": id])448            }449            try db.run("DELETE FROM pins WHERE kind = 'playlist' AND id = ?", [.text(id)])450        }451        changed()452    }453454    public func addToPlaylist(_ id: String, trackIDs: [String]) throws {455        guard !trackIDs.isEmpty else { return }456        try db.transaction {457            var pos = try db.scalarInt("SELECT coalesce(max(position), -1) FROM playlist_entries WHERE playlist_id = ?", [.text(id)])458            for t in trackIDs {459                pos += 1460                try db.run("INSERT INTO playlist_entries (entry_id, playlist_id, track_id, position) VALUES (?,?,?,?)", [.text("local-" + UUID().uuidString.lowercased()), .text(id), .text(t), .int(Int64(pos))])461            }462            try touchPlaylist(id)463            if !isPendingCreate(id) { try enqueue(.playlistAdd, ["playlistID": id], list: trackIDs) }464        }465        changed()466    }467468    /// New order of a playlist, given as its entry ids; entries left out are removed.469    public func setPlaylistEntries(_ id: String, entryIDs: [String]) throws {470        try db.transaction {471            let keep = Set(entryIDs)472            for e in try db.query("SELECT entry_id FROM playlist_entries WHERE playlist_id = ?", [.text(id)], { $0.text(0) }) where !keep.contains(e) {473                try db.run("DELETE FROM playlist_entries WHERE entry_id = ?", [.text(e)])474            }475            for (i, e) in entryIDs.enumerated() { try db.run("UPDATE playlist_entries SET position = ? WHERE entry_id = ? AND playlist_id = ?", [.int(Int64(i)), .text(e), .text(id)]) }476            try touchPlaylist(id)477            if !isPendingCreate(id) {478                // The desired content supersedes earlier additions and orders that were still waiting.479                try dropOps(kinds: [.playlistAdd, .playlistSet], playlistID: id)480                let desired = try db.query("SELECT track_id FROM playlist_entries WHERE playlist_id = ? ORDER BY position", [.text(id)]) { $0.text(0) }481                try enqueue(.playlistSet, ["playlistID": id], list: desired)482            }483        }484        changed()485    }486487    // MARK: Pending operations (changes made locally, waiting for the cluster)488489    public func pendingOps() -> [PendingOp] {490        (try? db.query("SELECT id, kind, payload, attempts FROM ops ORDER BY id") { r -> PendingOp? in491            guard let kind = PendingOp.Kind(rawValue: r.text(1)), let obj = try? JSONSerialization.jsonObject(with: Data(r.text(2).utf8)) as? [String: Any] else { return nil }492            var payload: [String: String] = [:]493            for (k, v) in obj { if let s = v as? String { payload[k] = s } }494            return PendingOp(id: r.int64(0), kind: kind, payload: payload, list: obj["list"] as? [String] ?? [], attempts: r.int(3))495        }.compactMap { $0 }) ?? []496    }497    public func pendingOpCount() -> Int { (try? db.scalarInt("SELECT count(*) FROM ops")) ?? 0 }498    public func removeOp(_ id: Int64) { _ = try? db.run("DELETE FROM ops WHERE id = ?", [.int(id)]) }499    public func failOp(_ id: Int64, error: String) { _ = try? db.run("UPDATE ops SET attempts = attempts + 1, last_error = ? WHERE id = ?", [.text(error), .int(id)]) }500501    public struct LocalPlaylist: Sendable { public var id: String; public var name: String; public var baseUpdatedAt: String?; public var trackIDs: [String] }502    public func localPlaylist(_ id: String) -> LocalPlaylist? {503        guard let row = (try? db.query("SELECT name, base_updated_at FROM playlists WHERE id = ?", [.text(id)]) { ($0.text(0), $0.string(1)) })?.first else { return nil }504        let tracks = (try? db.query("SELECT track_id FROM playlist_entries WHERE playlist_id = ? ORDER BY position", [.text(id)]) { $0.text(0) }) ?? []505        return LocalPlaylist(id: id, name: row.0, baseUpdatedAt: row.1, trackIDs: tracks)506    }507    /// The cluster created the playlist: its real identifier replaces the local one everywhere, pending operations included.508    public func adoptPlaylistID(local: String, remote: String, updatedAt: Date?) throws {509        try db.transaction {510            try db.run("UPDATE playlists SET id = ?, pending_create = 0, base_updated_at = ? WHERE id = ?", [.text(remote), .of(updatedAt.map(APIClient.iso)), .text(local)])511            try db.run("UPDATE playlist_entries SET playlist_id = ? WHERE playlist_id = ?", [.text(remote), .text(local)])512            try db.run("UPDATE pins SET id = ? WHERE kind = 'playlist' AND id = ?", [.text(remote), .text(local)])513            try db.run("UPDATE ops SET payload = replace(payload, ?, ?)", [.text(local), .text(remote)])514        }515        changed()516    }517    public func setPlaylistBase(_ id: String, updatedAt: Date?) {518        _ = try? db.run("UPDATE playlists SET base_updated_at = ? WHERE id = ?", [.of(updatedAt.map(APIClient.iso)), .text(id)])519    }520    /// Removes a playlist that the cluster no longer has (or whose deletion it confirmed).521    public func forgetPlaylist(_ id: String) {522        _ = try? db.run("DELETE FROM playlist_entries WHERE playlist_id = ?", [.text(id)]); _ = try? db.run("DELETE FROM playlists WHERE id = ?", [.text(id)])523        changed()524    }525    public func restorePlaylist(_ id: String) { _ = try? db.run("UPDATE playlists SET deleted = 0 WHERE id = ?", [.text(id)]); changed() }526527    public func addNotice(_ text: String) { _ = try? db.run("INSERT INTO notices (at, text) VALUES (?,?)", [.of(Date()), .text(text)]); changed() }528    public func takeNotices() -> [String] {529        let n = (try? db.query("SELECT text FROM notices ORDER BY id") { $0.text(0) }) ?? []530        if !n.isEmpty { _ = try? db.run("DELETE FROM notices") }531        return n532    }533534    // MARK: Downloads535536    private static let downloadSelect = """537        SELECT d.track_id, coalesce(t.title, ''), coalesce(ar.name, ''), coalesce(al.title, ''), al.artwork_id, d.state, d.bytes_received,538          CASE WHEN d.bytes_total > 0 THEN d.bytes_total ELSE coalesce(t.size_bytes, 0) END, d.error, d.queued_at, d.completed_at, d.rel_path, d.asset_id539        FROM downloads d LEFT JOIN tracks t ON t.id = d.track_id LEFT JOIN artists ar ON ar.id = t.artist_id LEFT JOIN albums al ON al.id = t.album_id540        """541    private static func download(_ r: SQLRow) -> DownloadItem {542        DownloadItem(trackID: r.text(0), title: r.text(1), artistName: r.text(2), albumTitle: r.text(3), artworkID: r.string(4), state: DownloadState(rawValue: r.text(5)) ?? .failed,543                     bytesReceived: r.int64(6), bytesTotal: r.int64(7), error: r.string(8), queuedAt: r.date(9), completedAt: r.optDouble(10).map { Date(timeIntervalSince1970: $0) },544                     relativePath: r.string(11), assetID: r.string(12))545    }546    public func downloads(states: [DownloadState]? = nil, limit: Int = 100_000) -> [DownloadItem] {547        let filter = states.map { " WHERE d.state IN (" + $0.map { "'\($0.rawValue)'" }.joined(separator: ",") + ")" } ?? ""548        return (try? db.query(Self.downloadSelect + filter + " ORDER BY CASE d.state WHEN 'downloading' THEN 0 WHEN 'queued' THEN 1 WHEN 'paused' THEN 2 WHEN 'failed' THEN 3 ELSE 4 END, coalesce(d.completed_at, 0) DESC, d.queued_at, d.rowid LIMIT ?",549                              [.int(Int64(limit))]) { Self.download($0) }) ?? []550    }551    public func download(_ trackID: String) -> DownloadItem? { (try? db.query(Self.downloadSelect + " WHERE d.track_id = ?", [.text(trackID)]) { Self.download($0) })?.first }552    public func nextQueuedDownloads(limit: Int) -> [DownloadItem] {553        (try? db.query(Self.downloadSelect + " WHERE d.state = 'queued' ORDER BY d.queued_at, d.rowid LIMIT ?", [.int(Int64(limit))]) { Self.download($0) }) ?? []554    }555556    /// Queues tracks for download. A track already downloaded, queued or in progress is left as it is. Returns what was newly queued.557    @discardableResult558    public func enqueueDownloads(_ trackIDs: [String]) -> [String] {559        var added: [String] = []560        let now = Date()561        try? db.transaction {562            for (i, id) in trackIDs.enumerated() {563                guard let t = track(id), !t.removedRemotely, t.externalFileURL == nil else { continue }564                let existing = try db.scalarText("SELECT state FROM downloads WHERE track_id = ?", [.text(id)])565                if let existing, existing != DownloadState.failed.rawValue, existing != DownloadState.paused.rawValue { continue }566                try db.run("""567                    INSERT INTO downloads (track_id, state, bytes_total, queued_at) VALUES (?, 'queued', ?, ?)568                    ON CONFLICT(track_id) DO UPDATE SET state = 'queued', error = NULL, attempts = 0569                    """, [.text(id), .int(t.sizeBytes), .double(now.timeIntervalSince1970 + Double(i) * 0.0001)])570                added.append(id)571            }572        }573        if !added.isEmpty { changed() }574        return added575    }576    public func setDownloadState(_ trackID: String, _ state: DownloadState, error: String? = nil, countAttempt: Bool = false) {577        _ = try? db.run("UPDATE downloads SET state = ?, error = ?, attempts = attempts + ? WHERE track_id = ?", [.text(state.rawValue), .of(error), .int(countAttempt ? 1 : 0), .text(trackID)])578        changed()579    }580    public func setDownloadProgress(_ trackID: String, received: Int64, total: Int64) {581        _ = try? db.run("UPDATE downloads SET bytes_received = ?, bytes_total = ? WHERE track_id = ?", [.int(received), .int(total), .text(trackID)])582    }583    public func downloadAttempts(_ trackID: String) -> Int { (try? db.scalarInt("SELECT attempts FROM downloads WHERE track_id = ?", [.text(trackID)])) ?? 0 }584    public func completeDownload(_ trackID: String, relativePath: String, assetID: String?, sha256: String?, size: Int64) {585        _ = try? db.run("UPDATE downloads SET state = 'done', rel_path = ?, asset_id = ?, sha256 = ?, bytes_received = ?, bytes_total = ?, error = NULL, completed_at = ? WHERE track_id = ?",586                        [.text(relativePath), .of(assetID), .of(sha256), .int(size), .int(size), .of(Date()), .text(trackID)])587        changed()588    }589    /// Forgets a download and returns the relative path of its file, for the caller to delete.590    @discardableResult591    public func removeDownload(_ trackID: String) -> String? {592        let rel = (try? db.scalarText("SELECT rel_path FROM downloads WHERE track_id = ?", [.text(trackID)])) ?? nil593        _ = try? db.run("DELETE FROM downloads WHERE track_id = ?", [.text(trackID)])594        // A track kept only because of its file goes with it.595        if ((try? db.scalarInt("SELECT removed_remote FROM tracks WHERE id = ?", [.text(trackID)])) ?? 0) == 1 {596            _ = try? db.run("DELETE FROM search WHERE rowid = (SELECT rowid FROM tracks WHERE id = ?)", [.text(trackID)]); _ = try? db.run("DELETE FROM tracks WHERE id = ?", [.text(trackID)])597        }598        changed()599        return rel600    }601    /// Downloads interrupted by a quit or a crash go back to the queue; their partial files are resumed.602    public func requeueInterruptedDownloads() { _ = try? db.run("UPDATE downloads SET state = 'queued' WHERE state = 'downloading'"); changed() }603    public func isRelativePathUsed(_ rel: String, byOtherThan trackID: String) -> Bool {604        ((try? db.scalarInt("SELECT count(*) FROM downloads WHERE rel_path = ? AND track_id <> ?", [.text(rel), .text(trackID)])) ?? 0) > 0605    }606    public func downloadedFiles() -> [(trackID: String, relativePath: String, size: Int64)] {607        (try? db.query("SELECT track_id, rel_path, bytes_total FROM downloads WHERE state = 'done' AND rel_path IS NOT NULL") { ($0.text(0), $0.text(1), $0.int64(2)) }) ?? []608    }609610    public func setPinned(_ kind: PinKind, id: String, _ on: Bool) {611        if on { _ = try? db.run("INSERT OR IGNORE INTO pins (kind, id) VALUES (?,?)", [.text(kind.rawValue), .text(id)]) }612        else { _ = try? db.run("DELETE FROM pins WHERE kind = ? AND id = ?", [.text(kind.rawValue), .text(id)]) }613        changed()614    }615    public func isPinned(_ kind: PinKind, id: String) -> Bool { ((try? db.scalarInt("SELECT count(*) FROM pins WHERE kind = ? AND id = ?", [.text(kind.rawValue), .text(id)])) ?? 0) > 0 }616    /// Tracks of every collection kept offline that have no download yet (a track added to a pinned playlist, a new album track…).617    public func missingPinnedTrackIDs() -> [String] {618        (try? db.query("""619            SELECT DISTINCT t.id FROM tracks t WHERE t.removed_remote = 0 AND NOT EXISTS (SELECT 1 FROM downloads d WHERE d.track_id = t.id) AND (620              EXISTS (SELECT 1 FROM pins WHERE kind = 'library')621              OR EXISTS (SELECT 1 FROM pins WHERE kind = 'album' AND id = t.album_id)622              OR EXISTS (SELECT 1 FROM pins p JOIN playlist_entries e ON e.playlist_id = p.id WHERE p.kind = 'playlist' AND e.track_id = t.id))623            """) { $0.text(0) }) ?? []624    }625626    public func stats() -> LibraryStats {627        var s = LibraryStats()628        s.artists = artists().count629        s.albums = (try? db.scalarInt("SELECT count(*) FROM albums")) ?? 0630        s.tracks = (try? db.scalarInt("SELECT count(*) FROM tracks")) ?? 0631        s.totalBytes = Int64((try? db.scalarInt("SELECT coalesce(sum(size_bytes), 0) FROM tracks WHERE removed_remote = 0")) ?? 0)632        s.downloadedTracks = (try? db.scalarInt("SELECT count(*) FROM downloads WHERE state = 'done'")) ?? 0633        s.downloadedBytes = Int64((try? db.scalarInt("SELECT coalesce(sum(bytes_total), 0) FROM downloads WHERE state = 'done'")) ?? 0)634        s.downloadedAlbums = (try? db.scalarInt("SELECT count(DISTINCT t.album_id) FROM downloads d JOIN tracks t ON t.id = d.track_id WHERE d.state = 'done'")) ?? 0635        return s636    }637    /// Bytes still to fetch to have these tracks offline (what is already downloaded is not counted).638    public func bytesNeeded(for trackIDs: [String]) -> Int64 { tracks(ids: trackIDs).filter { !$0.isDownloaded }.reduce(0) { $0 + $1.sizeBytes } }639640    // MARK: Listening history641642    public func addPlay(_ p: PlayRecord) {643        _ = try? db.run("INSERT OR IGNORE INTO plays (id, track_id, played_at, listened_ms, completed, source, synced) VALUES (?,?,?,?,?,?,0)",644                        [.text(p.id), .text(p.trackID), .of(p.playedAt), .int(Int64(p.listenedMs)), .of(p.completed), .text(p.source)])645        changed()646    }647    public func unsyncedPlays(limit: Int = 400) -> [PlayRecord] {648        (try? db.query("SELECT id, track_id, played_at, listened_ms, completed, source FROM plays WHERE synced = 0 ORDER BY played_at LIMIT ?", [.int(Int64(limit))]) {649            PlayRecord(id: $0.text(0), trackID: $0.text(1), playedAt: $0.date(2), listenedMs: $0.int(3), completed: $0.bool(4), source: $0.text(5)) }) ?? []650    }651    public func markPlaysSynced(_ ids: [String]) { for id in ids { _ = try? db.run("UPDATE plays SET synced = 1 WHERE id = ?", [.text(id)]) } }652    public func mergeServerPlays(_ plays: [WirePlay]) {653        try? db.transaction {654            for p in plays {655                try db.run("INSERT OR IGNORE INTO plays (id, track_id, played_at, listened_ms, completed, source, synced) VALUES (?,?,?,?,?,?,1)",656                           [.text(p.id), .text(p.trackId), .of(p.playedAt), .int(Int64(p.listenedMs)), .of(p.completed), .text(p.source)])657            }658        }659        changed()660    }661    public func recentlyPlayed(limit: Int = 30, downloadedOnly: Bool = false) -> [Track] {662        let ids = (try? db.query("SELECT track_id FROM plays GROUP BY track_id ORDER BY max(played_at) DESC LIMIT ?", [.int(Int64(limit * 2))]) { $0.text(0) }) ?? []663        return Array(tracks(ids: ids).filter { !downloadedOnly || $0.isDownloaded }.prefix(limit))664    }665    public func history(limit: Int = 200) -> [(play: PlayRecord, track: Track)] {666        let plays = (try? db.query("SELECT id, track_id, played_at, listened_ms, completed, source FROM plays ORDER BY played_at DESC LIMIT ?", [.int(Int64(limit))]) {667            PlayRecord(id: $0.text(0), trackID: $0.text(1), playedAt: $0.date(2), listenedMs: $0.int(3), completed: $0.bool(4), source: $0.text(5)) }) ?? []668        let by = Dictionary(uniqueKeysWithValues: tracks(ids: Array(Set(plays.map(\.trackID)))).map { ($0.id, $0) })669        return plays.compactMap { p in by[p.trackID].map { (p, $0) } }670    }671    public func playCount() -> Int { (try? db.scalarInt("SELECT count(*) FROM plays")) ?? 0 }672673    // MARK: Lyrics and loudness caches674675    public func cachedLyrics(_ trackID: String) -> Lyrics? {676        guard let json = (try? db.scalarText("SELECT json FROM lyrics WHERE track_id = ?", [.text(trackID)])) ?? nil else { return nil }677        return try? JSONDecoder().decode(Lyrics.self, from: Data(json.utf8))678    }679    public func cacheLyrics(_ trackID: String, _ l: Lyrics) {680        guard let data = try? JSONEncoder().encode(l), let json = String(data: data, encoding: .utf8) else { return }681        _ = try? db.run("INSERT OR REPLACE INTO lyrics (track_id, json, fetched_at) VALUES (?,?,?)", [.text(trackID), .text(json), .of(Date())])682    }683    public func loudness(assetID: String) -> (lufs: Double, peak: Double)? {684        (try? db.query("SELECT lufs, peak FROM loudness WHERE asset_id = ?", [.text(assetID)]) { ($0.double(0), $0.double(1)) })?.first685    }686    public func setLoudness(assetID: String, lufs: Double, peak: Double) {687        _ = try? db.run("INSERT OR REPLACE INTO loudness (asset_id, lufs, peak) VALUES (?,?,?)", [.text(assetID), .double(lufs), .double(peak)])688    }689    public func downloadedAssetsWithoutLoudness(limit: Int = 50) -> [(trackID: String, assetID: String, relativePath: String)] {690        (try? db.query("SELECT d.track_id, d.asset_id, d.rel_path FROM downloads d WHERE d.state = 'done' AND d.asset_id IS NOT NULL AND d.rel_path IS NOT NULL AND NOT EXISTS (SELECT 1 FROM loudness l WHERE l.asset_id = d.asset_id) LIMIT ?",691                       [.int(Int64(limit))]) { ($0.text(0), $0.text(1), $0.text(2)) }) ?? []692    }693}694
Files main
Menu

Theme