Repository Public
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