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