SPB Git

Repository Public

Database.swift 35.4 KB 476 lines swift

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
Files main
Menu

Theme