Repository Public
1// SP Music iOS2// File: Core/Sources/SPMusicCore/Persistence/Database.swift3// Description: Local library database: catalogue, account data, downloads, pins, history, outbox.4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai78import Foundation910public enum DownloadState: String, Sendable, Codable {11 case queued // waiting for a transfer slot or for an allowed network12 case preparing // the server is producing the AAC rendition13 case running, paused, failed, done14}1516public struct DownloadRecord: Sendable, Hashable, Identifiable {17 public var trackId: String18 public var variant: AudioVariant19 public var state: DownloadState20 public var bytesTotal: Int6421 public var bytesDone: Int6422 public var sha256: String?23 public var assetId: String?24 public var ext: String25 public var error: String?26 public var attempts: Int27 public var addedAt: Date28 public var completedAt: Date?29 public var id: String { trackId }30 public var fraction: Double { state == .done ? 1 : (bytesTotal > 0 ? min(1, Double(bytesDone) / Double(bytesTotal)) : 0) }31}3233public enum PinKind: String, Sendable, Codable { case track, album, playlist, library }34public struct Pin: Sendable, Hashable { public var kind: PinKind; public var id: String }3536public enum AlbumSort: String, Sendable, CaseIterable { case artist, title, recent, year }37public enum TrackSort: String, Sendable, CaseIterable { case title, artist, recent }3839public struct LibraryCounts: Sendable, Equatable {40 public var tracks = 0, albums = 0, artists = 0, playlists = 0, downloadedTracks = 0, downloadedAlbums = 041 public init(tracks: Int = 0, albums: Int = 0, artists: Int = 0, playlists: Int = 0, downloadedTracks: Int = 0, downloadedAlbums: Int = 0) {42 self.tracks = tracks; self.albums = albums; self.artists = artists; self.playlists = playlists; self.downloadedTracks = downloadedTracks; self.downloadedAlbums = downloadedAlbums43 }44}45public struct SearchResults: Sendable, Equatable {46 public var artists: [Artist] = [], albums: [Album] = [], tracks: [Track] = []47 public init(artists: [Artist] = [], albums: [Album] = [], tracks: [Track] = []) { self.artists = artists; self.albums = albums; self.tracks = tracks }48 public var isEmpty: Bool { artists.isEmpty && albums.isEmpty && tracks.isEmpty } }49public struct OutboxItem: Sendable { public var seq: Int64; public var kind: String; public var payload: Data }5051/// The device's own copy of the library: catalogue, the account's lists and favourites, what is kept offline,52/// listening history and the changes waiting to be sent. Everything the app shows comes from here, so every screen53/// works without a connection.54public actor Database {55 private let db: SQLite5657 public init(url: URL?) throws {58 let db = try SQLite(path: url?.path ?? ":memory:")59 try Self.migrate(db)60 self.db = db61 }6263 private static func migrate(_ db: SQLite) throws {64 let v = try db.query("PRAGMA user_version") { $0.int(0) }.first ?? 065 if v < 1 {66 try db.transaction {67 try db.exec("""68 CREATE TABLE artists(id TEXT PRIMARY KEY, name TEXT NOT NULL, norm TEXT NOT NULL);69 CREATE TABLE albums(id TEXT PRIMARY KEY, title TEXT NOT NULL, norm TEXT NOT NULL, year INTEGER, artwork_id TEXT, artist_id TEXT NOT NULL, genre TEXT, created_at REAL);70 CREATE TABLE tracks(id TEXT PRIMARY KEY, title TEXT NOT NULL, norm TEXT NOT NULL, track_no INTEGER, disc_no INTEGER NOT NULL, duration_ms INTEGER NOT NULL, year INTEGER,71 codec TEXT NOT NULL, container TEXT NOT NULL, sample_rate INTEGER, bit_depth INTEGER, channels INTEGER, bitrate INTEGER, compat_state TEXT NOT NULL,72 artist_id TEXT NOT NULL, album_id TEXT NOT NULL, genre TEXT, lyrics_state TEXT, created_at REAL,73 file_id TEXT, file_size INTEGER, file_sha TEXT, file_mime TEXT, compat_id TEXT, compat_size INTEGER, compat_mime TEXT, removed INTEGER NOT NULL DEFAULT 0);74 CREATE INDEX tracks_album ON tracks(album_id, disc_no, track_no);75 CREATE INDEX tracks_artist ON tracks(artist_id);76 CREATE TABLE fav_tracks(id TEXT PRIMARY KEY, created_at REAL);77 CREATE TABLE fav_albums(id TEXT PRIMARY KEY, created_at REAL);78 CREATE TABLE playlists(id TEXT PRIMARY KEY, name TEXT NOT NULL, updated_at REAL);79 CREATE TABLE playlist_entries(entry_id TEXT PRIMARY KEY, playlist_id TEXT NOT NULL, track_id TEXT NOT NULL, position INTEGER NOT NULL);80 CREATE INDEX playlist_entries_list ON playlist_entries(playlist_id, position);81 CREATE TABLE downloads(track_id TEXT PRIMARY KEY, variant TEXT NOT NULL, state TEXT NOT NULL, bytes_total INTEGER NOT NULL DEFAULT 0, bytes_done INTEGER NOT NULL DEFAULT 0,82 sha256 TEXT, asset_id TEXT, ext TEXT NOT NULL, error TEXT, attempts INTEGER NOT NULL DEFAULT 0, added_at REAL NOT NULL, completed_at REAL);83 CREATE INDEX downloads_state ON downloads(state, added_at);84 CREATE TABLE pins(kind TEXT NOT NULL, id TEXT NOT NULL, created_at REAL NOT NULL, PRIMARY KEY(kind, id));85 CREATE TABLE pin_members(kind TEXT NOT NULL, id TEXT NOT NULL, track_id TEXT NOT NULL, excluded INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(kind, id, track_id));86 CREATE INDEX pin_members_track ON pin_members(track_id);87 CREATE TABLE lyrics(track_id TEXT PRIMARY KEY, json BLOB NOT NULL);88 CREATE TABLE plays(id TEXT PRIMARY KEY, track_id TEXT NOT NULL, played_at REAL NOT NULL, listened_ms INTEGER NOT NULL, completed INTEGER NOT NULL, source TEXT NOT NULL);89 CREATE INDEX plays_recent ON plays(played_at DESC);90 CREATE TABLE outbox(seq INTEGER PRIMARY KEY AUTOINCREMENT, kind TEXT NOT NULL, payload BLOB NOT NULL);91 CREATE TABLE kv(key TEXT PRIMARY KEY, value BLOB NOT NULL);92 PRAGMA user_version = 1;93 """)94 }95 }96 }9798 // MARK: Key/value99100 public func value(_ key: String) -> Data? { (try? db.query("SELECT value FROM kv WHERE key = ?", [.text(key)]) { $0.blob(0) })?.first ?? nil }101 public func setValue(_ key: String, _ data: Data?) {102 if let data { try? db.run("INSERT INTO kv(key, value) VALUES(?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", [.text(key), .blob(data)]) }103 else { try? db.run("DELETE FROM kv WHERE key = ?", [.text(key)]) }104 }105 public func string(_ key: String) -> String? { value(key).flatMap { String(data: $0, encoding: .utf8) } }106 public func setString(_ key: String, _ s: String?) { setValue(key, s?.data(using: .utf8)) }107108 // MARK: Catalogue — writes (synchronisation)109110 public var catalogRevision: Int64 { Int64(string("sync.rev") ?? "") ?? 0 }111112 /// Applies one page of the incremental feed in a single transaction and stores its revision as the new cursor.113 public func apply(_ page: SyncPage) throws {114 try db.transaction {115 for a in page.artists {116 try db.run("INSERT INTO artists(id, name, norm) VALUES(?,?,?) ON CONFLICT(id) DO UPDATE SET name = excluded.name, norm = excluded.norm", [.text(a.id), .text(a.name), .text(normalized(a.name))])117 }118 for a in page.albums {119 try db.run("""120 INSERT INTO albums(id, title, norm, year, artwork_id, artist_id, genre, created_at) VALUES(?,?,?,?,?,?,?,?)121 ON CONFLICT(id) DO UPDATE SET title = excluded.title, norm = excluded.norm, year = excluded.year, artwork_id = excluded.artwork_id, artist_id = excluded.artist_id, genre = excluded.genre, created_at = excluded.created_at122 """, [.text(a.id), .text(a.title), .text(normalized(a.title)), .i(a.year), .t(a.artworkId), .text(a.artistId), .t(a.genre), .d(a.createdAt)])123 }124 for t in page.tracks {125 try db.run("""126 INSERT INTO tracks(id, title, norm, track_no, disc_no, duration_ms, year, codec, container, sample_rate, bit_depth, channels, bitrate, compat_state, artist_id, album_id, genre, lyrics_state, created_at,127 file_id, file_size, file_sha, file_mime, compat_id, compat_size, compat_mime, removed) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)128 ON CONFLICT(id) DO UPDATE SET title = excluded.title, norm = excluded.norm, track_no = excluded.track_no, disc_no = excluded.disc_no, duration_ms = excluded.duration_ms, year = excluded.year,129 codec = excluded.codec, container = excluded.container, sample_rate = excluded.sample_rate, bit_depth = excluded.bit_depth, channels = excluded.channels, bitrate = excluded.bitrate,130 compat_state = excluded.compat_state, artist_id = excluded.artist_id, album_id = excluded.album_id, genre = excluded.genre, lyrics_state = excluded.lyrics_state, created_at = excluded.created_at,131 file_id = excluded.file_id, file_size = excluded.file_size, file_sha = excluded.file_sha, file_mime = excluded.file_mime,132 compat_id = excluded.compat_id, compat_size = excluded.compat_size, compat_mime = excluded.compat_mime, removed = 0133 """, [.text(t.id), .text(t.title), .text(normalized(t.title)), .i(t.trackNo), .i(t.discNo), .i(t.durationMs), .i(t.year), .text(t.codec), .text(t.container),134 .i(t.sampleRate), .i(t.bitDepth), .i(t.channels), .i(t.bitrate), .text(t.compatState), .text(t.artistId), .text(t.albumId), .t(t.genre), .t(t.lyricsState), .d(t.createdAt),135 .t(t.file?.assetId), .i(t.file?.sizeBytes), .t(t.file?.sha256), .t(t.file?.mime), .t(t.compat?.assetId), .i(t.compat?.sizeBytes), .t(t.compat?.mime)])136 }137 // A track deleted on the server disappears here too — unless its file is on this device: the owner's138 // downloads are never removed behind his back; the track stays, marked as removed from the server.139 for d in page.deleted where d.kind == "track" {140 try db.run("UPDATE tracks SET removed = 1 WHERE id = ? AND EXISTS (SELECT 1 FROM downloads d WHERE d.track_id = tracks.id AND d.state = 'done')", [.text(d.id)])141 try db.run("DELETE FROM tracks WHERE id = ? AND removed = 0", [.text(d.id)])142 try db.run("DELETE FROM downloads WHERE track_id = ? AND state <> 'done'", [.text(d.id)])143 try db.run("DELETE FROM playlist_entries WHERE track_id = ? AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.id = playlist_entries.track_id)", [.text(d.id)])144 try db.run("DELETE FROM fav_tracks WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.id = fav_tracks.id)", [.text(d.id)])145 }146 for d in page.deleted where d.kind == "album" {147 try db.run("DELETE FROM albums WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = albums.id)", [.text(d.id)])148 }149 for d in page.deleted where d.kind == "artist" {150 try db.run("DELETE FROM artists WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = artists.id) AND NOT EXISTS (SELECT 1 FROM albums a WHERE a.artist_id = artists.id)", [.text(d.id)])151 }152 try db.run("INSERT INTO kv(key, value) VALUES('sync.rev', ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value", [.blob(Data(String(page.rev).utf8))])153 }154 }155156 /// Forgets the catalogue cursor (next synchronisation reads everything again). Rows are kept until then.157 public func resetCatalogCursor() { setString("sync.rev", nil) }158159 /// After a full read (since = 0): rows the server no longer has and that no tombstone announced.160 public func pruneCatalog(keepingTracks ids: Set<String>, albums: Set<String>, artists: Set<String>) throws {161 try db.transaction {162 for id in try db.query("SELECT id FROM tracks WHERE removed = 0") { $0.text(0) } where !ids.contains(id) {163 try db.run("UPDATE tracks SET removed = 1 WHERE id = ? AND EXISTS (SELECT 1 FROM downloads d WHERE d.track_id = tracks.id AND d.state = 'done')", [.text(id)])164 try db.run("DELETE FROM tracks WHERE id = ? AND removed = 0", [.text(id)])165 }166 for id in try db.query("SELECT id FROM albums") { $0.text(0) } where !albums.contains(id) {167 try db.run("DELETE FROM albums WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = albums.id)", [.text(id)])168 }169 for id in try db.query("SELECT id FROM artists") { $0.text(0) } where !artists.contains(id) {170 try db.run("DELETE FROM artists WHERE id = ? AND NOT EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = artists.id) AND NOT EXISTS (SELECT 1 FROM albums a WHERE a.artist_id = artists.id)", [.text(id)])171 }172 }173 }174175 /// Replaces the account's lists and favourites with the server's answer. Favourite changes still waiting in the176 /// outbox are re-applied on top by the caller.177 public func apply(_ me: UserSync) throws {178 try db.transaction {179 try db.exec("DELETE FROM fav_tracks; DELETE FROM fav_albums; DELETE FROM playlists; DELETE FROM playlist_entries;")180 for f in me.favoriteTracks { try db.run("INSERT OR REPLACE INTO fav_tracks(id, created_at) VALUES(?,?)", [.text(f.id), .d(f.createdAt)]) }181 for f in me.favoriteAlbums { try db.run("INSERT OR REPLACE INTO fav_albums(id, created_at) VALUES(?,?)", [.text(f.id), .d(f.createdAt)]) }182 for p in me.playlists {183 try db.run("INSERT INTO playlists(id, name, updated_at) VALUES(?,?,?)", [.text(p.id), .text(p.name), .d(p.updatedAt)])184 for (i, e) in p.entries.enumerated() {185 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), .i(i)])186 }187 }188 }189 }190191 public func setFavorite(kind: String, id: String, on: Bool) {192 let table = kind == "album" ? "fav_albums" : "fav_tracks"193 if on { try? db.run("INSERT OR REPLACE INTO \(table)(id, created_at) VALUES(?,?)", [.text(id), .d(Date())]) }194 else { try? db.run("DELETE FROM \(table) WHERE id = ?", [.text(id)]) }195 }196197 // MARK: Catalogue — reads198199 private static let trackColumns = """200 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, t.compat_state,201 t.artist_id, coalesce(ar.name, ''), t.album_id, coalesce(al.title, ''), coalesce(aar.name, ''), al.artwork_id, t.genre, t.lyrics_state,202 t.file_id, t.file_size, t.file_sha, t.file_mime, t.compat_id, t.compat_size, t.compat_mime,203 EXISTS (SELECT 1 FROM fav_tracks f WHERE f.id = t.id), t.removed204 """205 private static let trackFrom = "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"206 private static let isDownloaded = "EXISTS (SELECT 1 FROM downloads d WHERE d.track_id = t.id AND d.state = 'done')"207208 private static func track(_ r: SQLRow, entryAt: Int32? = nil) -> Track {209 let file = r.optText(21).map { AssetInfo(assetId: $0, sizeBytes: r.int64(22), sha256: r.optText(23), mime: r.text(24)) }210 let compat = r.optText(25).map { AssetInfo(assetId: $0, sizeBytes: r.int64(26), sha256: nil, mime: r.text(27)) }211 return Track(id: r.text(0), title: r.text(1), trackNo: r.optInt(2), discNo: r.int(3), durationMs: r.int(4), year: r.optInt(5), codec: r.text(6), container: r.text(7),212 sampleRate: r.optInt(8), bitDepth: r.optInt(9), channels: r.optInt(10), bitrate: r.optInt(11), compatState: r.text(12),213 artistId: r.text(13), artistName: r.text(14), albumId: r.text(15), albumTitle: r.text(16), albumArtistName: r.text(17), artworkId: r.optText(18), genre: r.optText(19),214 lyricsState: r.optText(20), file: file, compat: compat, favorite: r.bool(28), removedRemotely: r.bool(29), entryId: entryAt.flatMap { r.optText($0) })215 }216217 private func tracks(where clause: String, _ params: [SQLValue] = [], order: String, limit: Int? = nil) -> [Track] {218 let sql = "SELECT \(Self.trackColumns) \(Self.trackFrom) WHERE \(clause) ORDER BY \(order)" + (limit.map { " LIMIT \($0)" } ?? "")219 return (try? db.query(sql, params) { Self.track($0) }) ?? []220 }221222 public func allTracks(sort: TrackSort = .artist, downloadedOnly: Bool = false) -> [Track] {223 let order = switch sort {224 case .title: "t.norm"225 case .recent: "t.created_at DESC, t.norm"226 case .artist: "coalesce(ar.norm, ''), coalesce(al.norm, ''), t.disc_no, t.track_no"227 }228 return tracks(where: downloadedOnly ? Self.isDownloaded : "1", order: order)229 }230 public func tracks(albumId: String, downloadedOnly: Bool = false) -> [Track] {231 tracks(where: "t.album_id = ?" + (downloadedOnly ? " AND \(Self.isDownloaded)" : ""), [.text(albumId)], order: "t.disc_no, t.track_no IS NULL, t.track_no, t.norm")232 }233 public func tracks(artistId: String, downloadedOnly: Bool = false) -> [Track] {234 tracks(where: "t.artist_id = ?" + (downloadedOnly ? " AND \(Self.isDownloaded)" : ""), [.text(artistId)], order: "al.year DESC, coalesce(al.norm, ''), t.disc_no, t.track_no")235 }236 /// In the order of `ids`; unknown identifiers are dropped.237 public func tracks(ids: [String]) -> [Track] {238 guard !ids.isEmpty else { return [] }239 var by: [String: Track] = [:]240 for chunk in stride(from: 0, to: ids.count, by: 400).map({ Array(ids[$0..<min($0 + 400, ids.count)]) }) {241 let marks = Array(repeating: "?", count: chunk.count).joined(separator: ",")242 for t in tracks(where: "t.id IN (\(marks))", chunk.map { .text($0) }, order: "t.id") { by[t.id] = t }243 }244 return ids.compactMap { by[$0] }245 }246 public func track(id: String) -> Track? { tracks(ids: [id]).first }247 public func favoriteTracks(downloadedOnly: Bool = false) -> [Track] {248 let sql = "SELECT \(Self.trackColumns) \(Self.trackFrom) JOIN fav_tracks fv ON fv.id = t.id WHERE \(downloadedOnly ? Self.isDownloaded : "1") ORDER BY fv.created_at DESC"249 return (try? db.query(sql) { Self.track($0) }) ?? []250 }251 public func playlistTracks(id: String, downloadedOnly: Bool = false) -> [Track] {252 let sql = "SELECT \(Self.trackColumns), pe.entry_id \(Self.trackFrom) JOIN playlist_entries pe ON pe.track_id = t.id WHERE pe.playlist_id = ? \(downloadedOnly ? "AND \(Self.isDownloaded)" : "") ORDER BY pe.position"253 return (try? db.query(sql, [.text(id)]) { Self.track($0, entryAt: 30) }) ?? []254 }255 public func recentlyPlayed(limit: Int = 30, downloadedOnly: Bool = false) -> [Track] {256 let sql = """257 SELECT \(Self.trackColumns) \(Self.trackFrom) JOIN (SELECT track_id, max(played_at) AS at FROM plays GROUP BY track_id) p ON p.track_id = t.id258 WHERE \(downloadedOnly ? Self.isDownloaded : "1") ORDER BY p.at DESC LIMIT \(limit)259 """260 return (try? db.query(sql) { Self.track($0) }) ?? []261 }262 public func recentlyAdded(limit: Int = 20, downloadedOnly: Bool = false) -> [Track] {263 tracks(where: downloadedOnly ? Self.isDownloaded : "1", order: "t.created_at DESC, coalesce(al.norm, ''), t.disc_no, t.track_no", limit: limit)264 }265266 private static func albumSQL(downloadedOnly: Bool) -> String {267 let dl = downloadedOnly ? " AND \(isDownloaded)" : ""268 return """269 SELECT al.id, al.title, al.year, al.artwork_id, al.artist_id, coalesce(ar.name, ''), al.genre, al.created_at,270 (SELECT count(*) FROM tracks t WHERE t.album_id = al.id\(dl)), (SELECT coalesce(sum(t.duration_ms), 0) FROM tracks t WHERE t.album_id = al.id\(dl)),271 EXISTS (SELECT 1 FROM fav_albums f WHERE f.id = al.id)272 FROM albums al LEFT JOIN artists ar ON ar.id = al.artist_id273 """274 }275 private static func album(_ r: SQLRow) -> Album {276 Album(id: r.text(0), title: r.text(1), year: r.optInt(2), artworkId: r.optText(3), artistId: r.text(4), artistName: r.text(5), genre: r.optText(6), createdAt: r.optDate(7),277 trackCount: r.int(8), durationMs: r.int(9), favorite: r.bool(10))278 }279 private static let albumHasDownload = "EXISTS (SELECT 1 FROM tracks t JOIN downloads d ON d.track_id = t.id AND d.state = 'done' WHERE t.album_id = al.id)"280281 public func albums(sort: AlbumSort = .artist, downloadedOnly: Bool = false, limit: Int? = nil) -> [Album] {282 let order = switch sort {283 case .artist: "coalesce(ar.norm, ''), al.year IS NULL, al.year, al.norm"284 case .title: "al.norm"285 case .recent: "al.created_at DESC, al.norm"286 case .year: "al.year IS NULL, al.year DESC, al.norm"287 }288 let sql = Self.albumSQL(downloadedOnly: downloadedOnly) + " WHERE \(downloadedOnly ? Self.albumHasDownload : "EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id)") ORDER BY \(order)" + (limit.map { " LIMIT \($0)" } ?? "")289 return (try? db.query(sql) { Self.album($0) }) ?? []290 }291 public func album(id: String, downloadedOnly: Bool = false) -> Album? {292 (try? db.query(Self.albumSQL(downloadedOnly: downloadedOnly) + " WHERE al.id = ?", [.text(id)]) { Self.album($0) })?.first293 }294 public func albums(artistId: String, downloadedOnly: Bool = false) -> [Album] {295 let sql = Self.albumSQL(downloadedOnly: downloadedOnly) + """296 WHERE (al.artist_id = ?1 OR EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND t.artist_id = ?1)) \(downloadedOnly ? "AND \(Self.albumHasDownload)" : "")297 ORDER BY al.year IS NULL, al.year DESC, al.norm298 """299 return (try? db.query(sql, [.text(artistId)]) { Self.album($0) }) ?? []300 }301 public func favoriteAlbums() -> [Album] {302 (try? db.query(Self.albumSQL(downloadedOnly: false) + " JOIN fav_albums fa ON fa.id = al.id ORDER BY fa.created_at DESC") { Self.album($0) }) ?? []303 }304305 private static func artistSQL(downloadedOnly: Bool) -> String {306 let dl = downloadedOnly ? " AND \(isDownloaded)" : ""307 return """308 SELECT a.id, a.name, (SELECT count(DISTINCT t.album_id) FROM tracks t WHERE t.artist_id = a.id\(dl)), (SELECT count(*) FROM tracks t WHERE t.artist_id = a.id\(dl)),309 (SELECT al.artwork_id FROM albums al WHERE al.artwork_id IS NOT NULL AND (al.artist_id = a.id OR EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id AND t.artist_id = a.id)) ORDER BY al.year DESC LIMIT 1)310 FROM artists a311 """312 }313 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.optText(4)) }314 public func artists(downloadedOnly: Bool = false) -> [Artist] {315 let cond = "EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = a.id\(downloadedOnly ? " AND \(Self.isDownloaded)" : ""))"316 return (try? db.query(Self.artistSQL(downloadedOnly: downloadedOnly) + " WHERE \(cond) ORDER BY a.norm") { Self.artist($0) }) ?? []317 }318 public func artist(id: String, downloadedOnly: Bool = false) -> Artist? {319 (try? db.query(Self.artistSQL(downloadedOnly: downloadedOnly) + " WHERE a.id = ?", [.text(id)]) { Self.artist($0) })?.first320 }321322 public func playlists() -> [Playlist] {323 let rows = (try? db.query("SELECT p.id, p.name, p.updated_at, (SELECT count(*) FROM playlist_entries e JOIN tracks t ON t.id = e.track_id WHERE e.playlist_id = p.id) FROM playlists p ORDER BY p.updated_at DESC") {324 Playlist(id: $0.text(0), name: $0.text(1), updatedAt: $0.optDate(2), trackCount: $0.int(3), artworkIds: [])325 }) ?? []326 return rows.map { p in327 var p = p328 p.artworkIds = (try? db.query("""329 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_id330 WHERE e.playlist_id = ? AND al.artwork_id IS NOT NULL GROUP BY al.artwork_id ORDER BY min(e.position) LIMIT 4331 """, [.text(p.id)]) { $0.text(0) }) ?? []332 return p333 }334 }335 public func playlist(id: String) -> Playlist? { playlists().first { $0.id == id } }336337 public func search(_ query: String, downloadedOnly: Bool = false) -> SearchResults {338 let needle = normalized(query)339 guard !needle.isEmpty else { return SearchResults() }340 let like = "%" + needle.replacingOccurrences(of: "\\", with: "\\\\").replacingOccurrences(of: "%", with: "\\%").replacingOccurrences(of: "_", with: "\\_") + "%"341 let p: [SQLValue] = [.text(like)]342 let dlT = downloadedOnly ? " AND \(Self.isDownloaded)" : ""343 let artists = (try? db.query(Self.artistSQL(downloadedOnly: downloadedOnly) + " WHERE a.norm LIKE ?1 ESCAPE '\\' AND EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = a.id\(dlT)) ORDER BY a.norm LIMIT 12", p) { Self.artist($0) }) ?? []344 let albums = (try? db.query(Self.albumSQL(downloadedOnly: downloadedOnly) + """345 WHERE (al.norm LIKE ?1 ESCAPE '\\' OR coalesce(ar.norm, '') LIKE ?1 ESCAPE '\\') AND \(downloadedOnly ? Self.albumHasDownload : "1")346 ORDER BY (al.norm LIKE ?1 ESCAPE '\\') DESC, al.norm LIMIT 18347 """, p) { Self.album($0) }) ?? []348 let tracks = (try? db.query("""349 SELECT \(Self.trackColumns) \(Self.trackFrom)350 WHERE (t.norm LIKE ?1 ESCAPE '\\' OR coalesce(ar.norm, '') LIKE ?1 ESCAPE '\\' OR coalesce(al.norm, '') LIKE ?1 ESCAPE '\\' OR lower(coalesce(t.genre, '')) LIKE ?1 ESCAPE '\\')\(dlT)351 ORDER BY (t.norm LIKE ?1 ESCAPE '\\') DESC, t.norm LIMIT 60352 """, p) { Self.track($0) }) ?? []353 return SearchResults(artists: artists, albums: albums, tracks: tracks)354 }355356 public func counts() -> LibraryCounts {357 let n: (String) -> Int = { sql in ((try? self.db.query(sql) { $0.int(0) })?.first) ?? 0 }358 return LibraryCounts(tracks: n("SELECT count(*) FROM tracks"), albums: n("SELECT count(*) FROM albums al WHERE EXISTS (SELECT 1 FROM tracks t WHERE t.album_id = al.id)"),359 artists: n("SELECT count(*) FROM artists a WHERE EXISTS (SELECT 1 FROM tracks t WHERE t.artist_id = a.id)"), playlists: n("SELECT count(*) FROM playlists"),360 downloadedTracks: n("SELECT count(*) FROM downloads WHERE state = 'done'"),361 downloadedAlbums: n("SELECT count(DISTINCT t.album_id) FROM tracks t JOIN downloads d ON d.track_id = t.id AND d.state = 'done'"))362 }363 public func allTrackIds() -> [String] { (try? db.query("SELECT id FROM tracks WHERE removed = 0") { $0.text(0) }) ?? [] }364 public func trackIds(albumId: String) -> [String] { (try? db.query("SELECT id FROM tracks WHERE album_id = ? AND removed = 0 ORDER BY disc_no, track_no", [.text(albumId)]) { $0.text(0) }) ?? [] }365 public func trackIds(playlistId: String) -> [String] { (try? db.query("SELECT e.track_id FROM playlist_entries e JOIN tracks t ON t.id = e.track_id WHERE e.playlist_id = ? ORDER BY e.position", [.text(playlistId)]) { $0.text(0) }) ?? [] }366367 // MARK: Downloads368369 private static let downloadColumns = "track_id, variant, state, bytes_total, bytes_done, sha256, asset_id, ext, error, attempts, added_at, completed_at"370 private static func download(_ r: SQLRow) -> DownloadRecord {371 DownloadRecord(trackId: r.text(0), variant: AudioVariant(rawValue: r.text(1)) ?? .original, state: DownloadState(rawValue: r.text(2)) ?? .failed, bytesTotal: r.int64(3), bytesDone: r.int64(4),372 sha256: r.optText(5), assetId: r.optText(6), ext: r.text(7), error: r.optText(8), attempts: r.int(9), addedAt: r.optDate(10) ?? Date(), completedAt: r.optDate(11))373 }374 public func downloads(states: [DownloadState]? = nil) -> [DownloadRecord] {375 let cond = states.map { "WHERE state IN (" + $0.map { "'\($0.rawValue)'" }.joined(separator: ",") + ")" } ?? ""376 return (try? db.query("SELECT \(Self.downloadColumns) FROM downloads \(cond) ORDER BY added_at, track_id") { Self.download($0) }) ?? []377 }378 public func download(trackId: String) -> DownloadRecord? {379 (try? db.query("SELECT \(Self.downloadColumns) FROM downloads WHERE track_id = ?", [.text(trackId)]) { Self.download($0) })?.first380 }381 public func upsertDownload(_ d: DownloadRecord) {382 try? db.run("""383 INSERT INTO downloads(track_id, variant, state, bytes_total, bytes_done, sha256, asset_id, ext, error, attempts, added_at, completed_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)384 ON CONFLICT(track_id) DO UPDATE SET variant = excluded.variant, state = excluded.state, bytes_total = excluded.bytes_total, bytes_done = excluded.bytes_done, sha256 = excluded.sha256,385 asset_id = excluded.asset_id, ext = excluded.ext, error = excluded.error, attempts = excluded.attempts, completed_at = excluded.completed_at386 """, [.text(d.trackId), .text(d.variant.rawValue), .text(d.state.rawValue), .int(d.bytesTotal), .int(d.bytesDone), .t(d.sha256), .t(d.assetId), .text(d.ext), .t(d.error), .i(d.attempts), .d(d.addedAt), .d(d.completedAt)])387 }388 public func deleteDownload(trackId: String) { try? db.run("DELETE FROM downloads WHERE track_id = ?", [.text(trackId)]) }389 public func downloadedBytes() -> Int64 { ((try? db.query("SELECT coalesce(sum(bytes_total), 0) FROM downloads WHERE state = 'done'") { $0.int64(0) })?.first) ?? 0 }390 /// Artwork still needed offline: the covers of every album that has at least one downloaded or wanted track.391 public func artworkIdsInUse() -> Set<String> {392 Set((try? db.query("SELECT DISTINCT al.artwork_id FROM downloads d JOIN tracks t ON t.id = d.track_id JOIN albums al ON al.id = t.album_id WHERE al.artwork_id IS NOT NULL") { $0.text(0) }) ?? [])393 }394395 // MARK: Pins — what the listener asked to keep offline396397 public func pins() -> [Pin] { (try? db.query("SELECT kind, id FROM pins ORDER BY created_at") { Pin(kind: PinKind(rawValue: $0.text(0)) ?? .track, id: $0.text(1)) }) ?? [] }398 public func isPinned(_ kind: PinKind, _ id: String) -> Bool { !((try? db.query("SELECT 1 FROM pins WHERE kind = ? AND id = ?", [.text(kind.rawValue), .text(id)]) { _ in 1 }) ?? []).isEmpty }399400 /// Records a pin and its member tracks. Returns the tracks that were not yet wanted by this pin.401 @discardableResult402 public func setPin(_ kind: PinKind, _ id: String, members: [String]) -> [String] {403 (try? db.transaction { () -> [String] in404 try db.run("INSERT OR IGNORE INTO pins(kind, id, created_at) VALUES(?,?,?)", [.text(kind.rawValue), .text(id), .d(Date())])405 let known = Set(try db.query("SELECT track_id FROM pin_members WHERE kind = ? AND id = ?", [.text(kind.rawValue), .text(id)]) { $0.text(0) })406 var added: [String] = []407 for t in members where !known.contains(t) {408 try db.run("INSERT INTO pin_members(kind, id, track_id, excluded) VALUES(?,?,?,0)", [.text(kind.rawValue), .text(id), .text(t)]); added.append(t)409 }410 // Tracks that left the album or the playlist are no longer wanted by it.411 let now = Set(members)412 for t in known where !now.contains(t) { try db.run("DELETE FROM pin_members WHERE kind = ? AND id = ? AND track_id = ?", [.text(kind.rawValue), .text(id), .text(t)]) }413 return added414 }) ?? []415 }416 public func removePin(_ kind: PinKind, _ id: String) {417 try? db.transaction {418 try db.run("DELETE FROM pins WHERE kind = ? AND id = ?", [.text(kind.rawValue), .text(id)])419 try db.run("DELETE FROM pin_members WHERE kind = ? AND id = ?", [.text(kind.rawValue), .text(id)])420 }421 }422 /// The listener removed one track by hand: no pin wants it any more (an album stays pinned for its other tracks).423 public func excludeFromPins(trackId: String) {424 try? db.transaction {425 try db.run("DELETE FROM pins WHERE kind = 'track' AND id = ?", [.text(trackId)])426 try db.run("DELETE FROM pin_members WHERE kind = 'track' AND track_id = ?", [.text(trackId)])427 try db.run("UPDATE pin_members SET excluded = 1 WHERE track_id = ?", [.text(trackId)])428 }429 }430 public func includeInPins(trackId: String) { try? db.run("UPDATE pin_members SET excluded = 0 WHERE track_id = ?", [.text(trackId)]) }431 /// How many pins still want this track.432 public func references(trackId: String) -> Int { ((try? db.query("SELECT count(*) FROM pin_members WHERE track_id = ? AND excluded = 0", [.text(trackId)]) { $0.int(0) })?.first) ?? 0 }433 /// Downloads (in any state) that no pin wants any more.434 public func unreferencedDownloads() -> [String] {435 (try? db.query("SELECT d.track_id FROM downloads d WHERE NOT EXISTS (SELECT 1 FROM pin_members m WHERE m.track_id = d.track_id AND m.excluded = 0)") { $0.text(0) }) ?? []436 }437 /// Wanted tracks that have no download row yet.438 public func wantedWithoutDownload() -> [String] {439 (try? db.query("SELECT DISTINCT m.track_id FROM pin_members m JOIN tracks t ON t.id = m.track_id WHERE m.excluded = 0 AND t.removed = 0 AND NOT EXISTS (SELECT 1 FROM downloads d WHERE d.track_id = m.track_id)") { $0.text(0) }) ?? []440 }441 public func pinMembers(_ kind: PinKind, _ id: String) -> [String] {442 (try? db.query("SELECT track_id FROM pin_members WHERE kind = ? AND id = ? AND excluded = 0", [.text(kind.rawValue), .text(id)]) { $0.text(0) }) ?? []443 }444 public func removeAllPins() { try? db.exec("DELETE FROM pins; DELETE FROM pin_members;") }445446 // MARK: Lyrics kept offline447448 public func lyrics(trackId: String) -> Lyrics? {449 ((try? db.query("SELECT json FROM lyrics WHERE track_id = ?", [.text(trackId)]) { $0.blob(0) })?.first ?? nil).flatMap { try? JSONDecoder().decode(Lyrics.self, from: $0) }450 }451 public func setLyrics(trackId: String, _ l: Lyrics?) {452 if let l, let data = try? JSONEncoder().encode(l) { try? db.run("INSERT OR REPLACE INTO lyrics(track_id, json) VALUES(?,?)", [.text(trackId), .blob(data)]) }453 else { try? db.run("DELETE FROM lyrics WHERE track_id = ?", [.text(trackId)]) }454 }455456 // MARK: History and outbox457458 public func addPlay(_ e: PlayEvent) {459 try? db.run("INSERT OR IGNORE INTO plays(id, track_id, played_at, listened_ms, completed, source) VALUES(?,?,?,?,?,?)",460 [.text(e.id), .text(e.trackId), .d(e.playedAt), .i(e.listenedMs), .b(e.completed), .text(e.source)])461 }462 public func playCount() -> Int { ((try? db.query("SELECT count(*) FROM plays") { $0.int(0) })?.first) ?? 0 }463464 public func enqueue(kind: String, payload: Data) { try? db.run("INSERT INTO outbox(kind, payload) VALUES(?,?)", [.text(kind), .blob(payload)]) }465 public func outbox(limit: Int = 200) -> [OutboxItem] {466 (try? db.query("SELECT seq, kind, payload FROM outbox ORDER BY seq LIMIT \(limit)") { OutboxItem(seq: $0.int64(0), kind: $0.text(1), payload: $0.blob(2) ?? Data()) }) ?? []467 }468 public func removeOutbox(seqs: [Int64]) { for s in seqs { try? db.run("DELETE FROM outbox WHERE seq = ?", [.int(s)]) } }469 public func outboxCount() -> Int { ((try? db.query("SELECT count(*) FROM outbox") { $0.int(0) })?.first) ?? 0 }470471 /// Signing out on a shared device: the account's own data goes, the catalogue and the downloaded files stay.472 public func clearAccountData() {473 try? db.exec("DELETE FROM fav_tracks; DELETE FROM fav_albums; DELETE FROM playlists; DELETE FROM playlist_entries; DELETE FROM plays; DELETE FROM outbox; DELETE FROM kv WHERE key LIKE 'playback.%' OR key LIKE 'account.%';")474 }475}476