SPB Git

Repository Public

LibraryStore.swift 40.5 KB 878 lines swift

1// ORIX DJ2// File: Packages/ORIXDJCore/Sources/ORIXLibrary/LibraryStore.swift3// Description: Thread-safe SQLite store of the music library: schema migrations, corruption recovery, tracks, queries and change notifications.4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai78import Foundation9import SQLite31011/// The persistent library. One SQLite connection guarded by a recursive lock: every public method is12/// synchronous, safe to call from any thread, and throws `LibraryError`.13public final class LibraryStore: @unchecked Sendable {14    /// Schema version written by this build (`PRAGMA user_version`).15    public static var schemaVersion: Int { migrations.count }1617    /// `true` when `init(url:)` found a corrupt database, moved it aside and started a fresh one.18    public let recoveredFromCorruption: Bool19    /// Where the corrupt database was moved, when `recoveredFromCorruption` is `true`.20    public let corruptBackupURL: URL?21    /// The database file, `nil` for an in-memory store.22    public let url: URL?2324    private let lock = NSRecursiveLock()25    private let db: SQLiteDatabase26    private var writeDepth = 02728    private let observerLock = NSLock()29    private var observers: [UUID: AsyncStream<Void>.Continuation] = [:]30    private var changeCounter: UInt64 = 03132    static let tagSeparator = "\u{1F}"3334    // MARK: Opening3536    /// Opens (or creates) the library at `url`. A corrupt file is renamed `<name>.corrupt-<timestamp>` and a37    /// fresh database is created; check `recoveredFromCorruption`.38    public convenience init(url: URL) throws {39        try self.init(fileURL: url, migrationLimit: nil)40    }4142    /// `inMemory: true` opens a private in-memory database; `false` a private temporary on-disk database43    /// that SQLite deletes on close. Neither persists.44    public init(inMemory: Bool = true) throws {45        let database = try SQLiteDatabase(path: inMemory ? ":memory:" : "")46        try LibraryStore.configure(database, checkIntegrity: false)47        try LibraryStore.migrate(database, limit: nil)48        db = database49        url = nil50        recoveredFromCorruption = false51        corruptBackupURL = nil52    }5354    /// `migrationLimit` stops the migrations early; only tests use it to build an old-schema database.55    init(fileURL: URL, migrationLimit: Int?) throws {56        do {57            try FileManager.default.createDirectory(at: fileURL.deletingLastPathComponent(), withIntermediateDirectories: true)58        } catch {59            throw LibraryError.io("cannot create \(fileURL.deletingLastPathComponent().path): \(error.localizedDescription)")60        }61        var recovered = false62        var backup: URL?63        var database: SQLiteDatabase64        do {65            database = try LibraryStore.open(fileURL, migrationLimit: migrationLimit)66        } catch let LibraryError.sqlite(code, _) where LibraryStore.isCorruption(code) {67            backup = try LibraryStore.moveAside(fileURL)68            recovered = true69            database = try LibraryStore.open(fileURL, migrationLimit: migrationLimit)70        }71        db = database72        url = fileURL73        recoveredFromCorruption = recovered74        corruptBackupURL = backup75    }7677    deinit {78        db.close()79        for observer in observers.values { observer.finish() }80    }8182    /// Closes the database. Later calls throw `LibraryError.closed`.83    public func close() {84        lock.lock()85        db.close()86        lock.unlock()87        observerLock.lock()88        let current = observers89        observers.removeAll()90        observerLock.unlock()91        for observer in current.values { observer.finish() }92    }9394    private static func isCorruption(_ code: Int32) -> Bool {95        let primary = code & 0xFF96        return primary == SQLITE_CORRUPT || primary == SQLITE_NOTADB97    }9899    private static func open(_ fileURL: URL, migrationLimit: Int?) throws -> SQLiteDatabase {100        let database = try SQLiteDatabase(path: fileURL.path)101        do {102            try configure(database, checkIntegrity: true)103            try migrate(database, limit: migrationLimit)104        } catch {105            database.close()106            throw error107        }108        return database109    }110111    private static func configure(_ database: SQLiteDatabase, checkIntegrity: Bool) throws {112        _ = try database.query("PRAGMA journal_mode = WAL") { $0.string(0) }113        try database.execute("PRAGMA foreign_keys = ON; PRAGMA synchronous = NORMAL;")114        if checkIntegrity {115            let result = try database.query("PRAGMA quick_check(1)") { $0.string(0) }116            if result.first?.lowercased() != "ok" {117                throw LibraryError.sqlite(code: SQLITE_CORRUPT, message: result.first ?? "integrity check failed")118            }119        }120    }121122    private static func moveAside(_ fileURL: URL) throws -> URL {123        let formatter = DateFormatter()124        formatter.locale = Locale(identifier: "en_US_POSIX")125        formatter.dateFormat = "yyyyMMdd-HHmmss"126        let stamp = formatter.string(from: Date())127        let manager = FileManager.default128        var target = URL(fileURLWithPath: fileURL.path + ".corrupt-" + stamp)129        var attempt = 2130        while manager.fileExists(atPath: target.path) {131            target = URL(fileURLWithPath: fileURL.path + ".corrupt-" + stamp + "-\(attempt)")132            attempt += 1133        }134        do {135            try manager.moveItem(at: fileURL, to: target)136        } catch {137            throw LibraryError.io("cannot move the corrupt library aside: \(error.localizedDescription)")138        }139        for suffix in ["-wal", "-shm"] {140            let side = fileURL.path + suffix141            if manager.fileExists(atPath: side) {142                try? manager.moveItem(atPath: side, toPath: target.path + suffix)143            }144        }145        return target146    }147148    // MARK: Migrations149150    /// Append only. Entry `n` (0-based) upgrades `user_version` n → n + 1.151    static let migrations: [String] = [152        // 1 — music library.153        """154        CREATE TABLE tracks (155            id INTEGER PRIMARY KEY AUTOINCREMENT,156            path TEXT NOT NULL UNIQUE,157            bookmark BLOB,158            title TEXT NOT NULL DEFAULT '', artist TEXT NOT NULL DEFAULT '', album TEXT NOT NULL DEFAULT '',159            genre TEXT NOT NULL DEFAULT '', year INTEGER NOT NULL DEFAULT 0, comment TEXT NOT NULL DEFAULT '',160            duration REAL NOT NULL DEFAULT 0, sample_rate REAL NOT NULL DEFAULT 0, channels INTEGER NOT NULL DEFAULT 0,161            bit_rate INTEGER NOT NULL DEFAULT 0, format TEXT NOT NULL DEFAULT '', file_size INTEGER NOT NULL DEFAULT 0,162            date_added REAL NOT NULL, date_modified REAL NOT NULL, last_played REAL,163            play_count INTEGER NOT NULL DEFAULT 0, rating INTEGER NOT NULL DEFAULT 0, favorite INTEGER NOT NULL DEFAULT 0,164            tags TEXT NOT NULL DEFAULT '[]',165            bpm REAL, bpm_confidence REAL, grid_first_beat REAL, grid_downbeat_index INTEGER,166            key TEXT, camelot TEXT, key_confidence REAL, loudness_lufs REAL, peak_dbfs REAL, gain_db REAL,167            analysis_version INTEGER NOT NULL DEFAULT 0, missing INTEGER NOT NULL DEFAULT 0,168            cues TEXT NOT NULL DEFAULT '[]', loops TEXT NOT NULL DEFAULT '[]',169            title_f TEXT NOT NULL DEFAULT '', artist_f TEXT NOT NULL DEFAULT '', album_f TEXT NOT NULL DEFAULT '',170            genre_f TEXT NOT NULL DEFAULT '', tags_index TEXT NOT NULL DEFAULT '', search_text TEXT NOT NULL DEFAULT ''171        );172        CREATE INDEX tracks_title ON tracks(title_f);173        CREATE INDEX tracks_artist ON tracks(artist_f);174        CREATE INDEX tracks_bpm ON tracks(bpm);175        CREATE INDEX tracks_date_added ON tracks(date_added);176        CREATE TABLE track_analysis (177            track_id INTEGER PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,178            data BLOB NOT NULL179        );180        CREATE TABLE track_artwork (181            track_id INTEGER PRIMARY KEY REFERENCES tracks(id) ON DELETE CASCADE,182            data BLOB NOT NULL183        );184        CREATE TABLE crates (185            id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, sort_index INTEGER NOT NULL DEFAULT 0186        );187        CREATE TABLE crate_tracks (188            crate_id INTEGER NOT NULL REFERENCES crates(id) ON DELETE CASCADE,189            track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,190            PRIMARY KEY (crate_id, track_id)191        ) WITHOUT ROWID;192        CREATE INDEX crate_tracks_track ON crate_tracks(track_id);193        CREATE TABLE playlists (194            id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, sort_index INTEGER NOT NULL DEFAULT 0195        );196        CREATE TABLE playlist_tracks (197            id INTEGER PRIMARY KEY AUTOINCREMENT,198            playlist_id INTEGER NOT NULL REFERENCES playlists(id) ON DELETE CASCADE,199            track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,200            position INTEGER NOT NULL201        );202        CREATE INDEX playlist_tracks_order ON playlist_tracks(playlist_id, position);203        CREATE INDEX playlist_tracks_track ON playlist_tracks(track_id);204        CREATE TABLE smart_crates (205            id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, sort_index INTEGER NOT NULL DEFAULT 0,206            match TEXT NOT NULL DEFAULT 'all', rules TEXT NOT NULL DEFAULT '[]'207        );208        CREATE TABLE sessions (209            id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL DEFAULT '', started_at REAL NOT NULL, ended_at REAL210        );211        CREATE TABLE history (212            id INTEGER PRIMARY KEY AUTOINCREMENT,213            track_id INTEGER NOT NULL REFERENCES tracks(id) ON DELETE CASCADE,214            played_at REAL NOT NULL,215            deck TEXT NOT NULL DEFAULT '',216            session_id INTEGER REFERENCES sessions(id) ON DELETE CASCADE217        );218        CREATE INDEX history_played ON history(played_at);219        CREATE INDEX history_session ON history(session_id, played_at);220        CREATE INDEX history_track ON history(track_id);221        CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT NOT NULL) WITHOUT ROWID;222        """,223        // 2 — automation scripts with version history, named JSON documents.224        """225        CREATE TABLE scripts (226            id INTEGER PRIMARY KEY AUTOINCREMENT,227            name TEXT NOT NULL,228            source TEXT NOT NULL DEFAULT '',229            tags TEXT NOT NULL DEFAULT '[]',230            favorite INTEGER NOT NULL DEFAULT 0,231            created_at REAL NOT NULL,232            updated_at REAL NOT NULL,233            origin TEXT NOT NULL DEFAULT 'user',234            search_text TEXT NOT NULL DEFAULT ''235        );236        CREATE UNIQUE INDEX scripts_name ON scripts(name COLLATE NOCASE);237        CREATE TABLE script_versions (238            id INTEGER PRIMARY KEY AUTOINCREMENT,239            script_id INTEGER NOT NULL REFERENCES scripts(id) ON DELETE CASCADE,240            version INTEGER NOT NULL,241            source TEXT NOT NULL,242            created_at REAL NOT NULL,243            UNIQUE (script_id, version)244        );245        CREATE TABLE documents (246            id INTEGER PRIMARY KEY AUTOINCREMENT,247            kind TEXT NOT NULL,248            name TEXT NOT NULL,249            json TEXT NOT NULL,250            updated_at REAL NOT NULL,251            UNIQUE (kind, name)252        );253        """,254    ]255256    private static func migrate(_ database: SQLiteDatabase, limit: Int?) throws {257        let current = Int(try database.scalarInt("PRAGMA user_version"))258        guard current <= migrations.count else {259            throw LibraryError.unsupportedSchema(found: current, supported: migrations.count)260        }261        let target = min(limit ?? migrations.count, migrations.count)262        guard current < target else { return }263        for version in current..<target {264            try database.transaction {265                try database.execute(migrations[version])266                try database.execute("PRAGMA user_version = \(version + 1)")267            }268        }269    }270271    /// The `user_version` of the open database.272    public func currentSchemaVersion() throws -> Int {273        try read { Int(try $0.scalarInt("PRAGMA user_version")) }274    }275276    // MARK: Serialised access277278    func read<T>(_ body: (SQLiteDatabase) throws -> T) throws -> T {279        lock.lock()280        defer { lock.unlock() }281        do {282            return try body(db)283        } catch let error as LibraryError {284            throw error285        } catch {286            throw LibraryError.io(String(describing: error))287        }288    }289290    /// Runs `body` in a transaction and notifies observers once the outermost write succeeded.291    func write<T>(_ body: (SQLiteDatabase) throws -> T) throws -> T {292        lock.lock()293        writeDepth += 1294        let value: T295        do {296            value = try db.transaction { try body(db) }297        } catch {298            writeDepth -= 1299            lock.unlock()300            if let error = error as? LibraryError { throw error }301            throw LibraryError.io(String(describing: error))302        }303        writeDepth -= 1304        let outermost = writeDepth == 0305        lock.unlock()306        if outermost { notifyChange() }307        return value308    }309310    // MARK: Change notifications311312    /// Incremented after every successful write.313    public var changeCount: UInt64 {314        observerLock.lock()315        defer { observerLock.unlock() }316        return changeCounter317    }318319    /// Yields after every successful write (coalesced: a slow consumer gets one pending event, not a backlog).320    /// The stream finishes when the store is closed or deallocated.321    public func changes() -> AsyncStream<Void> {322        let id = UUID()323        let (stream, continuation) = AsyncStream<Void>.makeStream(bufferingPolicy: .bufferingNewest(1))324        observerLock.lock()325        observers[id] = continuation326        observerLock.unlock()327        continuation.onTermination = { [weak self] _ in328            guard let self else { return }329            self.observerLock.lock()330            self.observers[id] = nil331            self.observerLock.unlock()332        }333        return stream334    }335336    private func notifyChange() {337        observerLock.lock()338        changeCounter &+= 1339        let current = Array(observers.values)340        observerLock.unlock()341        for observer in current { observer.yield() }342    }343344    // MARK: Track encoding345346    private static let trackColumns: [String] = [347        "path", "bookmark", "title", "artist", "album", "genre", "year", "comment",348        "duration", "sample_rate", "channels", "bit_rate", "format", "file_size",349        "date_added", "date_modified", "last_played", "play_count", "rating", "favorite", "tags",350        "bpm", "bpm_confidence", "grid_first_beat", "grid_downbeat_index",351        "key", "camelot", "key_confidence", "loudness_lufs", "peak_dbfs", "gain_db",352        "analysis_version", "missing", "cues", "loops",353        "title_f", "artist_f", "album_f", "genre_f", "tags_index", "search_text",354    ]355    private static let readableColumnCount = 35356357    /// "t.id, t.path, …" — the columns decoded by `decodeTrack`.358    static let trackSelect: String = (["id"] + trackColumns.prefix(readableColumnCount)).map { "t." + $0 }.joined(separator: ", ")359360    private static let insertTrackSQL: String =361        "INSERT INTO tracks (" + trackColumns.joined(separator: ", ") + ") VALUES ("362        + Array(repeating: "?", count: trackColumns.count).joined(separator: ", ") + ")"363364    private static let updateTrackSQL: String =365        "UPDATE tracks SET " + trackColumns.map { $0 + " = ?" }.joined(separator: ", ") + " WHERE id = ?"366367    /// Orders by folded name (accented names sort with their base letter), then id.368    static func sortedByName<T>(_ items: [T], name: KeyPath<T, String>, id: KeyPath<T, Int64>) -> [T] {369        let keyed: [(key: String, item: T)] = items.map { (LibraryText.fold($0[keyPath: name]), $0) }370        let ordered = keyed.sorted { a, b in371            if a.key != b.key { return a.key < b.key }372            return a.item[keyPath: id] < b.item[keyPath: id]373        }374        return ordered.map(\.item)375    }376377    static func encodeJSON<T: Encodable>(_ value: [T]) -> String {378        guard !value.isEmpty, let data = try? JSONEncoder().encode(value) else { return "[]" }379        return String(decoding: data, as: UTF8.self)380    }381382    static func decodeJSON<T: Decodable>(_ text: String, as type: [T].Type = [T].self) -> [T] {383        guard text != "[]", !text.isEmpty else { return [] }384        return (try? JSONDecoder().decode([T].self, from: Data(text.utf8))) ?? []385    }386387    static func normalisedTags(_ tags: [String]) -> [String] {388        var seen = Set<String>()389        var result: [String] = []390        for tag in tags {391            let cleaned = tag.replacingOccurrences(of: tagSeparator, with: " ").trimmingCharacters(in: .whitespacesAndNewlines)392            guard !cleaned.isEmpty, seen.insert(LibraryText.fold(cleaned)).inserted else { continue }393            result.append(cleaned)394        }395        return result396    }397398    static func normalisedCues(_ cues: [CuePoint]) -> [CuePoint] {399        var byIndex: [Int: CuePoint] = [:]400        // Index -1 is the deck's main cue point; 0...7 are the hot cues.401        for cue in cues where (-1...7).contains(cue.index) && cue.seconds.isFinite { byIndex[cue.index] = cue }402        return byIndex.values.sorted { $0.index < $1.index }403    }404405    /// Clamps and cleans the user-editable fields so that what is stored is what is returned.406    static func normalised(_ track: Track) -> Track {407        var track = track408        track.rating = min(5, max(0, track.rating))409        track.playCount = max(0, track.playCount)410        track.tags = normalisedTags(track.tags)411        track.cues = normalisedCues(track.cues)412        track.format = track.format.lowercased()413        return track414    }415416    private static func tagsIndex(_ tags: [String]) -> String {417        tags.isEmpty ? "" : tagSeparator + tags.map(LibraryText.fold).joined(separator: tagSeparator) + tagSeparator418    }419420    private static func values(_ t: Track) -> [SQLiteValue] {421        let search = LibraryText.fold([t.title, t.artist, t.album, t.genre, t.tags.joined(separator: " "), t.comment].joined(separator: " "))422        return [423            .text(t.path), .optional(t.bookmark), .text(t.title), .text(t.artist), .text(t.album), .text(t.genre),424            .int(t.year), .text(t.comment),425            .real(t.duration), .real(t.sampleRate), .int(t.channels), .int(t.bitRate), .text(t.format), .integer(t.fileSize),426            .date(t.dateAdded), .date(t.dateModified), .optional(t.lastPlayed), .int(t.playCount), .int(t.rating),427            .bool(t.favorite), .text(encodeJSON(t.tags)),428            .optional(t.bpm), .optional(t.bpmConfidence), .optional(t.gridFirstBeat), .optional(t.gridDownbeatIndex),429            .optional(t.key), .optional(t.camelot), .optional(t.keyConfidence), .optional(t.loudnessLUFS),430            .optional(t.peakDBFS), .optional(t.gainDB),431            .int(t.analysisVersion), .bool(t.missing), .text(encodeJSON(t.cues)), .text(encodeJSON(t.savedLoops)),432            .text(LibraryText.fold(t.title)), .text(LibraryText.fold(t.artist)), .text(LibraryText.fold(t.album)),433            .text(LibraryText.fold(t.genre)), .text(tagsIndex(t.tags)), .text(search),434        ]435    }436437    static func decodeTrack(_ r: SQLiteRow) -> Track {438        Track(439            id: r.int64(0), path: r.string(1), bookmark: r.optionalData(2),440            title: r.string(3), artist: r.string(4), album: r.string(5), genre: r.string(6),441            year: r.int(7), comment: r.string(8),442            duration: r.double(9), sampleRate: r.double(10), channels: r.int(11), bitRate: r.int(12),443            format: r.string(13), fileSize: r.int64(14),444            dateAdded: r.date(15), dateModified: r.date(16), lastPlayed: r.optionalDate(17),445            playCount: r.int(18), rating: r.int(19), favorite: r.bool(20), tags: decodeJSON(r.string(21)),446            bpm: r.optionalDouble(22), bpmConfidence: r.optionalDouble(23),447            gridFirstBeat: r.optionalDouble(24), gridDownbeatIndex: r.optionalInt(25),448            key: r.optionalString(26), camelot: r.optionalString(27), keyConfidence: r.optionalDouble(28),449            loudnessLUFS: r.optionalDouble(29), peakDBFS: r.optionalDouble(30), gainDB: r.optionalDouble(31),450            analysisVersion: r.int(32), missing: r.bool(33),451            cues: decodeJSON(r.string(34)), savedLoops: decodeJSON(r.string(35))452        )453    }454455    private func loadTrack(_ id: Int64, _ db: SQLiteDatabase) throws -> Track? {456        try db.first("SELECT \(LibraryStore.trackSelect) FROM tracks t WHERE t.id = ?", [.integer(id)], map: LibraryStore.decodeTrack)457    }458459    private func requireTrack(_ id: Int64, _ db: SQLiteDatabase) throws -> Track {460        guard let track = try loadTrack(id, db) else { throw LibraryError.notFound("track \(id)") }461        return track462    }463464    private func store(_ track: Track, _ db: SQLiteDatabase) throws {465        let changed = try db.run(LibraryStore.updateTrackSQL, LibraryStore.values(track) + [.integer(track.id)])466        guard changed == 1 else { throw LibraryError.notFound("track \(track.id)") }467    }468469    private func mutate(_ id: Int64, _ change: (inout Track) -> Void) throws -> Track {470        try write { db in471            var track = try requireTrack(id, db)472            change(&track)473            track = LibraryStore.normalised(track)474            try store(track, db)475            return track476        }477    }478479    // MARK: Tracks480481    /// Inserts the track, or refreshes the existing row that has the same `path`.482    ///483    /// On refresh the file facts (bookmark, tags read from the file, duration, format, size, modification date)484    /// are replaced and `missing` is cleared, while everything the user or the analyser produced is kept:485    /// id, dateAdded, lastPlayed, playCount, rating, favorite, tags, cues, loops and the analysis. Tag BPM/key of486    /// the incoming track are only taken while the stored track is not analysed. Use `updateTrack` to overwrite a row.487    /// `artwork` (JPEG thumbnail), when given, replaces the stored artwork.488    @discardableResult489    public func upsertTrack(_ track: Track, artwork: Data? = nil) throws -> Track {490        try write { db in491            let stored = try upsert(track, db)492            if let artwork { try setArtwork(artwork, for: stored.id, db) }493            return stored494        }495    }496497    /// `upsertTrack` for many tracks in one transaction (all or nothing). Returns the stored rows in input order.498    @discardableResult499    public func upsertTracks(_ tracks: [Track]) throws -> [Track] {500        try write { db in try tracks.map { try upsert($0, db) } }501    }502503    private func upsert(_ incoming: Track, _ db: SQLiteDatabase) throws -> Track {504        guard !incoming.path.isEmpty else { throw LibraryError.invalidArgument("a track needs a file path") }505        let existingID = try db.first("SELECT id FROM tracks WHERE path = ?", [.text(incoming.path)]) { $0.int64(0) }506        guard let existingID else {507            var track = LibraryStore.normalised(incoming)508            try db.run(LibraryStore.insertTrackSQL, LibraryStore.values(track))509            track.id = db.lastInsertRowID510            return track511        }512        var track = try requireTrack(existingID, db)513        let new = LibraryStore.normalised(incoming)514        track.bookmark = new.bookmark ?? track.bookmark515        track.title = new.title; track.artist = new.artist; track.album = new.album; track.genre = new.genre516        track.year = new.year; track.comment = new.comment517        track.duration = new.duration; track.sampleRate = new.sampleRate; track.channels = new.channels518        track.bitRate = new.bitRate; track.format = new.format; track.fileSize = new.fileSize519        track.dateModified = new.dateModified520        track.missing = false521        if track.analysisVersion == 0 {522            if let bpm = new.bpm { track.bpm = bpm }523            if let key = new.key { track.key = key }524            if let camelot = new.camelot { track.camelot = camelot }525        }526        try store(track, db)527        return track528    }529530    /// Overwrites every field of the row `track.id` (rating is clamped to 0...5, cues to pads 0...7).531    @discardableResult532    public func updateTrack(_ track: Track) throws -> Track {533        try write { db in534            let cleaned = LibraryStore.normalised(track)535            do {536                try store(cleaned, db)537            } catch let LibraryError.sqlite(code, _) where code & 0xFF == SQLITE_CONSTRAINT {538                throw LibraryError.conflict("another track already uses \(track.path)")539            }540            return cleaned541        }542    }543544    public func track(id: Int64) throws -> Track? {545        try read { try loadTrack(id, $0) }546    }547548    public func track(path: String) throws -> Track? {549        try read {550            try $0.first("SELECT \(LibraryStore.trackSelect) FROM tracks t WHERE t.path = ?", [.text(path)], map: LibraryStore.decodeTrack)551        }552    }553554    public func tracks(ids: [Int64]) throws -> [Track] {555        try read { db in try ids.compactMap { try loadTrack($0, db) } }556    }557558    @discardableResult559    public func setRating(trackID: Int64, rating: Int) throws -> Track {560        try mutate(trackID) { $0.rating = rating }561    }562563    @discardableResult564    public func setFavorite(trackID: Int64, favorite: Bool) throws -> Track {565        try mutate(trackID) { $0.favorite = favorite }566    }567568    @discardableResult569    public func setTags(trackID: Int64, tags: [String]) throws -> Track {570        try mutate(trackID) { $0.tags = tags }571    }572573    /// Replaces the hot cues (pads outside 0...7 are dropped, one cue per pad).574    @discardableResult575    public func setCues(trackID: Int64, cues: [CuePoint]) throws -> Track {576        try mutate(trackID) { $0.cues = cues }577    }578579    @discardableResult580    public func setSavedLoops(trackID: Int64, loops: [SavedLoop]) throws -> Track {581        try mutate(trackID) { $0.savedLoops = loops }582    }583584    /// Stores an analysis result: every analysis field of the track is replaced by the given value585    /// (including `nil`); the blob is replaced only when `analysis.data` is not `nil`.586    @discardableResult587    public func setAnalysis(trackID: Int64, _ analysis: TrackAnalysis) throws -> Track {588        try write { db in589            let track = try mutate(trackID) {590                $0.bpm = analysis.bpm; $0.bpmConfidence = analysis.bpmConfidence591                $0.gridFirstBeat = analysis.gridFirstBeat; $0.gridDownbeatIndex = analysis.gridDownbeatIndex592                $0.key = analysis.key; $0.camelot = analysis.camelot; $0.keyConfidence = analysis.keyConfidence593                $0.loudnessLUFS = analysis.loudnessLUFS; $0.peakDBFS = analysis.peakDBFS; $0.gainDB = analysis.gainDB594                $0.analysisVersion = max(0, analysis.analysisVersion)595            }596            if let data = analysis.data {597                try db.run("INSERT INTO track_analysis (track_id, data) VALUES (?, ?) ON CONFLICT(track_id) DO UPDATE SET data = excluded.data",598                           [.integer(trackID), .blob(data)])599            }600            return track601        }602    }603604    /// Edits the beat grid without touching the rest of the analysis.605    @discardableResult606    public func setBeatGrid(trackID: Int64, bpm: Double?, firstBeat: Double?, downbeatIndex: Int?) throws -> Track {607        try mutate(trackID) { $0.bpm = bpm; $0.gridFirstBeat = firstBeat; $0.gridDownbeatIndex = downbeatIndex }608    }609610    /// The opaque analysis payload, loaded on demand.611    public func analysisBlob(for trackID: Int64) throws -> Data? {612        try read { try $0.first("SELECT data FROM track_analysis WHERE track_id = ?", [.integer(trackID)]) { $0.data(0) } }613    }614615    /// Stores (or with `nil` removes) the analysis payload.616    public func setAnalysisBlob(_ data: Data?, for trackID: Int64) throws {617        try write { db in618            _ = try requireTrack(trackID, db)619            if let data {620                try db.run("INSERT INTO track_analysis (track_id, data) VALUES (?, ?) ON CONFLICT(track_id) DO UPDATE SET data = excluded.data",621                           [.integer(trackID), .blob(data)])622            } else {623                try db.run("DELETE FROM track_analysis WHERE track_id = ?", [.integer(trackID)])624            }625        }626    }627628    /// The JPEG artwork thumbnail, loaded on demand.629    public func artwork(for trackID: Int64) throws -> Data? {630        try read { try $0.first("SELECT data FROM track_artwork WHERE track_id = ?", [.integer(trackID)]) { $0.data(0) } }631    }632633    /// Stores (or with `nil` removes) the artwork thumbnail.634    public func setArtwork(_ data: Data?, for trackID: Int64) throws {635        try write { db in636            _ = try requireTrack(trackID, db)637            try setArtwork(data, for: trackID, db)638        }639    }640641    private func setArtwork(_ data: Data?, for trackID: Int64, _ db: SQLiteDatabase) throws {642        if let data, !data.isEmpty {643            try db.run("INSERT INTO track_artwork (track_id, data) VALUES (?, ?) ON CONFLICT(track_id) DO UPDATE SET data = excluded.data",644                       [.integer(trackID), .blob(data)])645        } else {646            try db.run("DELETE FROM track_artwork WHERE track_id = ?", [.integer(trackID)])647        }648    }649650    /// Removes tracks from the library (and from crates, playlists, history). Audio files are never touched.651    /// Returns the number of removed rows.652    @discardableResult653    public func removeTracks(ids: [Int64]) throws -> Int {654        try write { db in655            var removed = 0656            for id in ids { removed += try db.run("DELETE FROM tracks WHERE id = ?", [.integer(id)]) }657            return removed658        }659    }660661    /// Flags tracks whose media cannot be found (or clears the flag).662    public func markMissing(trackIDs: [Int64], missing: Bool = true) throws {663        try write { db in664            for id in trackIDs { try db.run("UPDATE tracks SET missing = ? WHERE id = ?", [.bool(missing), .integer(id)]) }665        }666    }667668    /// Points a track at a new file location, refreshes its bookmark, size and modification date and clears669    /// `missing`. Throws `.conflict` when another track already uses that path, `.io` when the file does not exist.670    @discardableResult671    public func relocate(trackID: Int64, to url: URL) throws -> Track {672        let path = url.standardizedFileURL.path673        guard FileManager.default.fileExists(atPath: path) else { throw LibraryError.io("no file at \(path)") }674        let attributes = try? FileManager.default.attributesOfItem(atPath: path)675        let bookmark = try? url.bookmarkData()676        return try write { db in677            let other = try db.first("SELECT id FROM tracks WHERE path = ? AND id <> ?", [.text(path), .integer(trackID)]) { $0.int64(0) }678            if other != nil { throw LibraryError.conflict("another track already uses \(path)") }679            var track = try requireTrack(trackID, db)680            track.path = path681            track.bookmark = bookmark ?? track.bookmark682            track.missing = false683            if let size = attributes?[.size] as? NSNumber { track.fileSize = size.int64Value }684            if let modified = attributes?[.modificationDate] as? Date { track.dateModified = modified }685            let ext = url.pathExtension.lowercased()686            if !ext.isEmpty { track.format = ext }687            try store(track, db)688            return track689        }690    }691692    /// Records one play: bumps `playCount`, sets `lastPlayed` and appends a history entry.693    @discardableResult694    public func recordPlay(trackID: Int64, deck: String = "", sessionID: Int64? = nil, at date: Date = Date()) throws -> HistoryEntry {695        try write { db in696            let changed = try db.run("UPDATE tracks SET play_count = play_count + 1, last_played = ? WHERE id = ?",697                                     [.date(date), .integer(trackID)])698            guard changed == 1 else { throw LibraryError.notFound("track \(trackID)") }699            if let sessionID, try db.scalarInt("SELECT COUNT(*) FROM sessions WHERE id = ?", [.integer(sessionID)]) == 0 {700                throw LibraryError.notFound("session \(sessionID)")701            }702            try db.run("INSERT INTO history (track_id, played_at, deck, session_id) VALUES (?, ?, ?, ?)",703                       [.integer(trackID), .date(date), .text(deck), sessionID.map { .integer($0) } ?? .null])704            return HistoryEntry(id: db.lastInsertRowID, trackID: trackID, playedAt: date, deck: deck, sessionID: sessionID)705        }706    }707708    // MARK: Queries709710    private struct BuiltQuery {711        var from: String712        var conditions: [String] = []713        var parameters: [SQLiteValue] = []714        var naturalOrder: String715    }716717    private func build(_ query: TrackQuery, _ db: SQLiteDatabase) throws -> BuiltQuery {718        var built: BuiltQuery719        switch query.scope {720        case .all:721            built = BuiltQuery(from: "tracks t", naturalOrder: "t.title_f, t.artist_f, t.id")722        case let .crate(id):723            built = BuiltQuery(from: "crate_tracks ct JOIN tracks t ON t.id = ct.track_id", naturalOrder: "t.title_f, t.artist_f, t.id")724            built.conditions.append("ct.crate_id = ?")725            built.parameters.append(.integer(id))726        case let .playlist(id):727            built = BuiltQuery(from: "playlist_tracks pt JOIN tracks t ON t.id = pt.track_id", naturalOrder: "pt.position, pt.id")728            built.conditions.append("pt.playlist_id = ?")729            built.parameters.append(.integer(id))730        case let .smartCrate(id):731            guard let crate = try loadSmartCrate(id, db) else { throw LibraryError.notFound("smart crate \(id)") }732            let predicate = try crate.predicate()733            built = BuiltQuery(from: "tracks t", naturalOrder: "t.title_f, t.artist_f, t.id")734            built.conditions.append("(" + predicate.sql + ")")735            built.parameters.append(contentsOf: predicate.parameters)736        case .favorites:737            built = BuiltQuery(from: "tracks t", naturalOrder: "t.title_f, t.artist_f, t.id")738            built.conditions.append("t.favorite = 1")739        case let .history(sessionID):740            built = BuiltQuery(from: "history h JOIN tracks t ON t.id = h.track_id", naturalOrder: "h.played_at DESC, h.id DESC")741            if let sessionID {742                built.conditions.append("h.session_id = ?")743                built.parameters.append(.integer(sessionID))744            }745        case .unanalysed:746            built = BuiltQuery(from: "tracks t", naturalOrder: "t.title_f, t.artist_f, t.id")747            built.conditions.append("t.analysis_version = 0")748        case .missing:749            built = BuiltQuery(from: "tracks t", naturalOrder: "t.title_f, t.artist_f, t.id")750            built.conditions.append("t.missing = 1")751        }752        for word in LibraryText.fold(query.search).split(whereSeparator: { $0.isWhitespace }) {753            built.conditions.append("t.search_text LIKE ? ESCAPE '\\'")754            built.parameters.append(.text("%" + LibraryText.likeEscaped(String(word)) + "%"))755        }756        return built757    }758759    private static func orderClause(_ key: TrackQuery.SortKey, ascending: Bool) -> String {760        let d = ascending ? "ASC" : "DESC"761        switch key {762        case .title: return "t.title_f \(d), t.artist_f \(d), t.id \(d)"763        case .artist: return "t.artist_f \(d), t.album_f \(d), t.title_f \(d), t.id \(d)"764        case .album: return "t.album_f \(d), t.title_f \(d), t.id \(d)"765        case .genre: return "t.genre_f \(d), t.artist_f \(d), t.title_f \(d), t.id \(d)"766        case .bpm: return "t.bpm IS NULL, t.bpm \(d), t.id \(d)"767        case .key:768            return "(t.camelot IS NULL OR t.camelot = '') AND (t.key IS NULL OR t.key = ''), "769                + "(t.camelot IS NULL OR t.camelot = ''), CAST(t.camelot AS INTEGER) \(d), UPPER(t.camelot) \(d), t.key \(d), t.id \(d)"770        case .duration: return "t.duration \(d), t.id \(d)"771        case .rating: return "t.rating \(d), t.title_f, t.id"772        case .dateAdded: return "t.date_added \(d), t.id \(d)"773        case .lastPlayed: return "t.last_played IS NULL, t.last_played \(d), t.id \(d)"774        case .playCount: return "t.play_count \(d), t.title_f, t.id"775        }776    }777778    /// List query. Never loads artwork nor analysis blobs. Unset values (no BPM, never played, no key) sort last779    /// in both directions.780    public func tracks(query: TrackQuery = TrackQuery()) throws -> [Track] {781        try read { db in782            let built = try build(query, db)783            var sql = "SELECT \(LibraryStore.trackSelect) FROM \(built.from)"784            if !built.conditions.isEmpty { sql += " WHERE " + built.conditions.joined(separator: " AND ") }785            sql += " ORDER BY " + (query.sort.map { LibraryStore.orderClause($0, ascending: query.ascending) } ?? built.naturalOrder)786            sql += " LIMIT ? OFFSET ?"787            let parameters = built.parameters + [.int(query.limit.map { max(0, $0) } ?? -1), .int(max(0, query.offset))]788            return try db.query(sql, parameters, map: LibraryStore.decodeTrack)789        }790    }791792    /// Number of rows `tracks(query:)` would return without limit/offset.793    public func count(query: TrackQuery = TrackQuery()) throws -> Int {794        try read { db in795            let built = try build(query, db)796            var sql = "SELECT COUNT(*) FROM \(built.from)"797            if !built.conditions.isEmpty { sql += " WHERE " + built.conditions.joined(separator: " AND ") }798            return Int(try db.scalarInt(sql, built.parameters))799        }800    }801802    // MARK: Settings803804    /// Stores a setting; `nil` removes it.805    public func setSetting(_ key: String, value: String?) throws {806        try write { db -> Void in807            if let value {808                try db.run("INSERT INTO settings (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",809                           [.text(key), .text(value)])810            } else {811                try db.run("DELETE FROM settings WHERE key = ?", [.text(key)])812            }813        }814    }815816    public func setting(_ key: String) throws -> String? {817        try read { try $0.first("SELECT value FROM settings WHERE key = ?", [.text(key)]) { $0.string(0) } }818    }819820    public func allSettings() throws -> [String: String] {821        try read { db in822            let pairs = try db.query("SELECT key, value FROM settings") { ($0.string(0), $0.string(1)) }823            return Dictionary(pairs, uniquingKeysWith: { _, last in last })824        }825    }826827    // MARK: Documents828829    /// Creates or replaces the JSON document `name` of `kind`. `json` must be valid JSON.830    @discardableResult831    public func saveDocument(kind: String, name: String, json: String) throws -> LibraryDocument {832        guard !kind.isEmpty, !name.isEmpty else { throw LibraryError.invalidArgument("a document needs a kind and a name") }833        guard (try? JSONSerialization.jsonObject(with: Data(json.utf8), options: [.fragmentsAllowed])) != nil else {834            throw LibraryError.invalidArgument("document \(kind)/\(name) is not valid JSON")835        }836        return try write { db in837            let now = Date()838            try db.run("""839                INSERT INTO documents (kind, name, json, updated_at) VALUES (?, ?, ?, ?)840                ON CONFLICT(kind, name) DO UPDATE SET json = excluded.json, updated_at = excluded.updated_at841                """, [.text(kind), .text(name), .text(json), .date(now)])842            let id = try db.scalarInt("SELECT id FROM documents WHERE kind = ? AND name = ?", [.text(kind), .text(name)])843            return LibraryDocument(id: id, kind: kind, name: name, json: json, updatedAt: now)844        }845    }846847    private static func decodeDocument(_ r: SQLiteRow) -> LibraryDocument {848        LibraryDocument(id: r.int64(0), kind: r.string(1), name: r.string(2), json: r.string(3), updatedAt: r.date(4))849    }850851    /// Documents of a kind, by name.852    public func documents(kind: String) throws -> [LibraryDocument] {853        try read {854            let rows = try $0.query("SELECT id, kind, name, json, updated_at FROM documents WHERE kind = ?", [.text(kind)],855                                    map: LibraryStore.decodeDocument)856            return LibraryStore.sortedByName(rows, name: \.name, id: \.id)857        }858    }859860    public func document(kind: String, name: String) throws -> LibraryDocument? {861        try read {862            try $0.first("SELECT id, kind, name, json, updated_at FROM documents WHERE kind = ? AND name = ?",863                         [.text(kind), .text(name)], map: LibraryStore.decodeDocument)864        }865    }866867    /// Returns `false` when there was nothing to delete.868    @discardableResult869    public func deleteDocument(kind: String, name: String) throws -> Bool {870        try write { try $0.run("DELETE FROM documents WHERE kind = ? AND name = ?", [.text(kind), .text(name)]) > 0 }871    }872873    @discardableResult874    public func deleteDocument(id: Int64) throws -> Bool {875        try write { try $0.run("DELETE FROM documents WHERE id = ?", [.integer(id)]) > 0 }876    }877}878
Files main
Menu

Theme