Repository Public
1// ORIX DJ2// File: Packages/ORIXDJCore/Tests/ORIXLibraryTests/LibraryStoreTests.swift3// Description: Tests of the SQLite library store: migrations, tracks, queries, collections, smart crates, history, settings, recovery, performance, concurrency.4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai78import Foundation9import Testing10@testable import ORIXLibrary1112@Suite struct MigrationTests {13 @Test func freshDatabaseIsAtCurrentSchemaAndPersistsAcrossReopen() throws {14 let temp = TempDirectory()15 let url = temp.file("library.sqlite")16 var id: Int64 = 017 do {18 let store = try LibraryStore(url: url)19 #expect(try store.currentSchemaVersion() == LibraryStore.schemaVersion)20 #expect(!store.recoveredFromCorruption)21 var track = sampleTrack("Persisted", bpm: 124)22 track.cues = [CuePoint(index: 2, seconds: 12.5, label: "Drop", colorHex: "#FF0000")]23 track.savedLoops = [SavedLoop(startSeconds: 30, lengthBeats: 8, label: "Loop")]24 track.tags = ["peak", "vocal"]25 id = try store.upsertTrack(track).id26 try store.setAnalysisBlob(Data([1, 2, 3]), for: id)27 try store.setSetting("masterVolume", value: "0.8")28 store.close()29 }30 let reopened = try LibraryStore(url: url)31 #expect(!reopened.recoveredFromCorruption)32 let track = try #require(try reopened.track(id: id))33 #expect(track.title == "Persisted")34 #expect(track.bpm == 124)35 #expect(track.cues == [CuePoint(index: 2, seconds: 12.5, label: "Drop", colorHex: "#FF0000")])36 #expect(track.savedLoops == [SavedLoop(startSeconds: 30, lengthBeats: 8, label: "Loop")])37 #expect(track.tags == ["peak", "vocal"])38 #expect(try reopened.analysisBlob(for: id) == Data([1, 2, 3]))39 #expect(try reopened.setting("masterVolume") == "0.8")40 }4142 @Test func oldSchemaIsUpgradedInPlaceKeepingData() throws {43 let temp = TempDirectory()44 let url = temp.file("library.sqlite")45 do {46 let old = try LibraryStore(fileURL: url, migrationLimit: 1)47 #expect(try old.currentSchemaVersion() == 1)48 try old.upsertTrack(sampleTrack("From v1"))49 // The scripts table belongs to schema 2.50 #expect(throws: LibraryError.self) { try old.createScript(name: "x", source: "y") }51 old.close()52 }53 let upgraded = try LibraryStore(url: url)54 #expect(try upgraded.currentSchemaVersion() == LibraryStore.schemaVersion)55 #expect(try upgraded.tracks().map(\.title) == ["From v1"])56 #expect(try upgraded.createScript(name: "x", source: "y").name == "x")57 }5859 @Test func newerSchemaIsRefusedAndNotTreatedAsCorruption() throws {60 let temp = TempDirectory()61 let url = temp.file("library.sqlite")62 do {63 let store = try LibraryStore(url: url)64 try store.write { try $0.execute("PRAGMA user_version = 999") }65 store.close()66 }67 #expect(expectLibraryError({ $0 == .unsupportedSchema(found: 999, supported: LibraryStore.schemaVersion) }) {68 _ = try LibraryStore(url: url)69 })70 let leftovers = try FileManager.default.contentsOfDirectory(atPath: temp.url.path).filter { $0.contains(".corrupt-") }71 #expect(leftovers.isEmpty)72 }7374 @Test func closedStoreThrows() throws {75 let store = try LibraryStore(inMemory: true)76 store.close()77 #expect(expectLibraryError({ $0 == .closed }) { _ = try store.tracks() })78 }79}8081@Suite struct CorruptionTests {82 @Test func garbageFileIsMovedAsideAndAFreshLibraryStarts() throws {83 let temp = TempDirectory()84 let url = temp.file("library.sqlite")85 let garbage = Data((0..<8192).map { UInt8(truncatingIfNeeded: $0 &* 31 &+ 7) })86 try garbage.write(to: url)8788 let store = try LibraryStore(url: url)89 #expect(store.recoveredFromCorruption)90 let backup = try #require(store.corruptBackupURL)91 #expect(backup.lastPathComponent.hasPrefix("library.sqlite.corrupt-"))92 #expect(try Data(contentsOf: backup) == garbage)93 #expect(try store.count() == 0)94 try store.upsertTrack(sampleTrack("After recovery"))95 store.close()9697 let again = try LibraryStore(url: url)98 #expect(!again.recoveredFromCorruption)99 #expect(try again.count() == 1)100 }101102 @Test func databaseDamagedInTheMiddleIsRecovered() throws {103 let temp = TempDirectory()104 let url = temp.file("library.sqlite")105 do {106 let store = try LibraryStore(url: url)107 try store.upsertTracks((0..<500).map { sampleTrack("Track \($0)") })108 store.close() // closing the last connection checkpoints the WAL into the main file109 }110 // Overwrite pages after the header with noise: the header stays valid, the b-trees do not.111 let handle = try FileHandle(forUpdating: url)112 let size = try handle.seekToEnd()113 #expect(size > 16_384)114 try handle.seek(toOffset: 4096)115 try handle.write(contentsOf: Data(repeating: 0xA7, count: Int(size) - 4096))116 try handle.close()117118 let store = try LibraryStore(url: url)119 #expect(store.recoveredFromCorruption)120 #expect(try store.count() == 0)121 }122}123124@Suite struct TrackTests {125 @Test func upsertIsUniqueOnPathAndKeepsUserData() throws {126 let store = try LibraryStore(inMemory: true)127 let first = try store.upsertTrack(sampleTrack("Original", bpm: 120))128 try store.setRating(trackID: first.id, rating: 4)129 try store.setFavorite(trackID: first.id, favorite: true)130 try store.setTags(trackID: first.id, tags: ["warmup", " Warmup ", "", "deep"])131 try store.recordPlay(trackID: first.id, deck: "A")132133 var rescanned = sampleTrack("Retagged", artist: "New Artist", bpm: 121, path: first.path)134 rescanned.rating = 1135 let second = try store.upsertTrack(rescanned)136 #expect(second.id == first.id)137 #expect(try store.count() == 1)138 #expect(second.title == "Retagged")139 #expect(second.artist == "New Artist")140 #expect(second.rating == 4)141 #expect(second.favorite)142 #expect(second.tags == ["warmup", "deep"])143 #expect(second.playCount == 1)144 #expect(second.bpm == 121) // not analysed yet, so the tag value wins145146 try store.setAnalysis(trackID: first.id, TrackAnalysis(bpm: 126.02, bpmConfidence: 0.9, gridFirstBeat: 0.12,147 gridDownbeatIndex: 0, key: "Am", camelot: "8A", keyConfidence: 0.7,148 loudnessLUFS: -9.5, peakDBFS: -0.3, gainDB: -4.5,149 analysisVersion: 3, data: Data([9, 9])))150 let third = try store.upsertTrack(rescanned)151 #expect(third.bpm == 126.02) // analysed: the tag value no longer overrides152 #expect(third.camelot == "8A")153 #expect(third.analysisVersion == 3)154 #expect(try store.analysisBlob(for: first.id) == Data([9, 9]))155 }156157 @Test func fieldSettersClampAndRoundTrip() throws {158 let store = try LibraryStore(inMemory: true)159 let id = try store.upsertTrack(sampleTrack("T")).id160 #expect(try store.setRating(trackID: id, rating: 9).rating == 5)161 #expect(try store.setRating(trackID: id, rating: -3).rating == 0)162 let cues = [CuePoint(index: 7, seconds: 90), CuePoint(index: 0, seconds: 1), CuePoint(index: 8, seconds: 5),163 CuePoint(index: 0, seconds: 2, label: "Intro")]164 let stored = try store.setCues(trackID: id, cues: cues)165 #expect(stored.cues.map(\.index) == [0, 7])166 #expect(stored.cues.first?.label == "Intro")167 #expect(try store.setSavedLoops(trackID: id, loops: [SavedLoop(startSeconds: 4, lengthBeats: 4)]).savedLoops.count == 1)168 #expect(try store.track(id: id) == store.tracks().first)169170 var edited = try #require(try store.track(id: id))171 edited.title = "Edited"; edited.year = 1999; edited.comment = "note"; edited.gainDB = -3.25172 try store.updateTrack(edited)173 #expect(try store.track(id: id) == edited)174175 #expect(expectLibraryError({ if case .notFound = $0 { return true } else { return false } }) {176 _ = try store.setRating(trackID: 12_345, rating: 1)177 })178 }179180 @Test func artworkAndAnalysisBlobsAreLazyAndCascade() throws {181 let store = try LibraryStore(inMemory: true)182 let id = try store.upsertTrack(sampleTrack("Art"), artwork: Data([0xFF, 0xD8, 0xFF])).id183 #expect(try store.artwork(for: id) == Data([0xFF, 0xD8, 0xFF]))184 #expect(try store.analysisBlob(for: id) == nil)185 try store.setAnalysisBlob(Data(repeating: 7, count: 100_000), for: id)186 #expect(try store.analysisBlob(for: id)?.count == 100_000)187 try store.setAnalysisBlob(nil, for: id)188 #expect(try store.analysisBlob(for: id) == nil)189 try store.setArtwork(nil, for: id)190 #expect(try store.artwork(for: id) == nil)191192 try store.setArtwork(Data([1]), for: id)193 #expect(try store.removeTracks(ids: [id]) == 1)194 #expect(try store.artwork(for: id) == nil)195 #expect(try store.read { try $0.scalarInt("SELECT COUNT(*) FROM track_artwork") } == 0)196 }197198 @Test func removeTracksNeverDeletesAudioFiles() throws {199 let temp = TempDirectory()200 let audio = temp.file("keep.wav")201 try AudioFixture.wav.write(to: audio)202 let store = try LibraryStore(inMemory: true)203 let id = try store.upsertTrack(sampleTrack("Keep", path: audio.path)).id204 try store.removeTracks(ids: [id])205 #expect(try store.count() == 0)206 #expect(FileManager.default.fileExists(atPath: audio.path))207 }208209 @Test func recordPlayHistoryAndSessions() throws {210 let store = try LibraryStore(inMemory: true)211 let a = try store.upsertTrack(sampleTrack("A")).id212 let b = try store.upsertTrack(sampleTrack("B")).id213 let session = try store.beginSession(name: "Friday")214 let t0 = Date(timeIntervalSinceReferenceDate: 800_000_000)215 try store.recordPlay(trackID: a, deck: "A", sessionID: session.id, at: t0)216 try store.recordPlay(trackID: b, deck: "B", sessionID: session.id, at: t0.addingTimeInterval(200))217 try store.recordPlay(trackID: a, deck: "A", sessionID: nil, at: t0.addingTimeInterval(400))218 try store.endSession(id: session.id)219220 let trackA = try #require(try store.track(id: a))221 #expect(trackA.playCount == 2)222 #expect(trackA.lastPlayed == t0.addingTimeInterval(400))223 #expect(try store.history().map(\.trackID) == [a, b, a])224 #expect(try store.history(sessionID: session.id).map(\.deck) == ["B", "A"])225 #expect(try store.tracks(query: TrackQuery(scope: .history(sessionID: session.id))).map(\.title) == ["B", "A"])226 #expect(try store.count(query: TrackQuery(scope: .history(sessionID: nil))) == 3)227 let sessions = try store.sessions()228 #expect(sessions.count == 1)229 #expect(sessions[0].entryCount == 2)230 #expect(sessions[0].endedAt != nil)231 #expect(expectLibraryError({ if case .notFound = $0 { return true } else { return false } }) {232 _ = try store.recordPlay(trackID: a, sessionID: 999)233 })234 #expect(try store.track(id: a)?.playCount == 2) // the failed play was rolled back235 }236237 @Test func scopesFavoritesUnanalysedMissing() throws {238 let store = try LibraryStore(inMemory: true)239 let ids = try store.upsertTracks(["A", "B", "C"].map { sampleTrack($0) }).map(\.id)240 try store.setFavorite(trackID: ids[0], favorite: true)241 try store.setAnalysis(trackID: ids[1], TrackAnalysis(bpm: 128, analysisVersion: 1))242 try store.markMissing(trackIDs: [ids[2]])243 #expect(try store.tracks(query: TrackQuery(scope: .favorites)).map(\.title) == ["A"])244 #expect(try store.tracks(query: TrackQuery(scope: .unanalysed)).map(\.title) == ["A", "C"])245 #expect(try store.tracks(query: TrackQuery(scope: .missing)).map(\.title) == ["C"])246 try store.markMissing(trackIDs: [ids[2]], missing: false)247 #expect(try store.count(query: TrackQuery(scope: .missing)) == 0)248 #expect(try store.tracks(query: TrackQuery(limit: 2, offset: 1)).map(\.title) == ["B", "C"])249 }250}251252@Suite struct SearchAndSortTests {253 private func populated() throws -> LibraryStore {254 let store = try LibraryStore(inMemory: true)255 var tracks = [256 sampleTrack("Halo", artist: "Beyoncé", album: "I Am… Sasha Fierce", genre: "Pop", bpm: 80),257 sampleTrack("Déjà Vu", artist: "Beyoncé", album: "B'Day", genre: "R&B", bpm: 106),258 sampleTrack("Around the World", artist: "Daft Punk", album: "Homework", genre: "House", bpm: 121.3),259 sampleTrack("Strobe", artist: "deadmau5", album: "For Lack of a Better Name", genre: "Progressive House", bpm: 128),260 sampleTrack("Ánimo", artist: "Zoé", album: "Reptilectric", genre: "Rock"),261 sampleTrack("100%_pure", artist: "Tester", album: "Edge_Cases", genre: "Test"),262 ]263 tracks[0].comment = "wedding set"264 tracks[3].tags = ["Peak Time", "long intro"]265 let stored = try store.upsertTracks(tracks)266 let camelots: [(String?, String?)] = [("10B", "D"), ("1A", "G#m"), ("2B", "F#"), ("12A", "C#m"), (nil, nil), (nil, "Em")]267 let durations: [Double] = [261, 240, 429, 637, 215, 10]268 let ratings = [5, 3, 4, 5, 0, 1]269 for (index, track) in stored.enumerated() {270 var edited = track271 edited.camelot = camelots[index].0272 edited.key = camelots[index].1273 edited.duration = durations[index]274 edited.rating = ratings[index]275 edited.dateAdded = Date(timeIntervalSinceReferenceDate: 700_000_000 + Double(index) * 86_400)276 try store.updateTrack(edited)277 }278 return store279 }280281 private func titles(_ store: LibraryStore, _ search: String) throws -> [String] {282 try store.tracks(query: TrackQuery(search: search)).map(\.title)283 }284285 @Test func searchIgnoresCaseAndDiacritics() throws {286 let store = try populated()287 #expect(try titles(store, "beyonce") == ["Déjà Vu", "Halo"])288 #expect(try titles(store, "BEYONCÉ") == ["Déjà Vu", "Halo"])289 #expect(try titles(store, "deja") == ["Déjà Vu"])290 #expect(try titles(store, "zoe animo") == ["Ánimo"])291 #expect(try titles(store, "ÄNIMO") == ["Ánimo"])292 }293294 @Test func searchWordsAreAndedAcrossFields() throws {295 let store = try populated()296 #expect(try titles(store, "beyonce halo") == ["Halo"])297 #expect(try titles(store, "house daft") == ["Around the World"])298 #expect(try titles(store, "wedding") == ["Halo"]) // comment299 #expect(try titles(store, "peak deadmau5") == ["Strobe"]) // tag + artist300 #expect(try titles(store, "homework") == ["Around the World"]) // album301 #expect(try titles(store, "beyonce strobe").isEmpty)302 #expect(try titles(store, " ").count == 6)303 #expect(try store.count(query: TrackQuery(search: "house")) == 2)304 }305306 @Test func searchTreatsLikeWildcardsAndQuotesLiterally() throws {307 let store = try populated()308 #expect(try titles(store, "100%") == ["100%_pure"])309 #expect(try titles(store, "%") == ["100%_pure"])310 #expect(try titles(store, "_") == ["100%_pure"])311 #expect(try titles(store, "b'day") == ["Déjà Vu"])312 #expect(try titles(store, "'; DROP TABLE tracks;--").isEmpty)313 #expect(try store.count() == 6)314 }315316 @Test func everySortKeyBothDirections() throws {317 let store = try populated()318 func sorted(_ key: TrackQuery.SortKey, _ ascending: Bool = true) throws -> [String] {319 try store.tracks(query: TrackQuery(sort: key, ascending: ascending)).map(\.title)320 }321 // Diacritic-insensitive: "Ánimo" sorts with the A's, "Déjà Vu" with the D's.322 #expect(try sorted(.title) == ["100%_pure", "Ánimo", "Around the World", "Déjà Vu", "Halo", "Strobe"])323 #expect(try sorted(.title, false) == ["Strobe", "Halo", "Déjà Vu", "Around the World", "Ánimo", "100%_pure"])324 #expect(try sorted(.artist) == ["Déjà Vu", "Halo", "Around the World", "Strobe", "100%_pure", "Ánimo"])325 #expect(try sorted(.album) == ["Déjà Vu", "100%_pure", "Strobe", "Around the World", "Halo", "Ánimo"])326 #expect(try sorted(.genre) == ["Around the World", "Halo", "Strobe", "Déjà Vu", "Ánimo", "100%_pure"])327 // No BPM sorts last in both directions.328 #expect(try sorted(.bpm).prefix(4) == ["Halo", "Déjà Vu", "Around the World", "Strobe"])329 #expect(try sorted(.bpm, false).prefix(4) == ["Strobe", "Around the World", "Déjà Vu", "Halo"])330 #expect(Set(try sorted(.bpm).suffix(2)) == ["Ánimo", "100%_pure"])331 #expect(Set(try sorted(.bpm, false).suffix(2)) == ["Ánimo", "100%_pure"])332 // Harmonic order 1A, 2B, 10B, 12A (numeric, not lexical), then key-only, then nothing.333 #expect(try sorted(.key) == ["Déjà Vu", "Around the World", "Halo", "Strobe", "100%_pure", "Ánimo"])334 #expect(try sorted(.key, false) == ["Strobe", "Halo", "Around the World", "Déjà Vu", "100%_pure", "Ánimo"])335 #expect(try sorted(.duration) == ["100%_pure", "Ánimo", "Déjà Vu", "Halo", "Around the World", "Strobe"])336 #expect(try sorted(.rating, false) == ["Halo", "Strobe", "Around the World", "Déjà Vu", "100%_pure", "Ánimo"])337 #expect(try sorted(.dateAdded, false) == ["100%_pure", "Ánimo", "Strobe", "Around the World", "Déjà Vu", "Halo"])338 #expect(try sorted(.playCount).count == 6)339340 let all = try store.tracks()341 let halo = try #require(all.first { $0.title == "Halo" })342 let strobe = try #require(all.first { $0.title == "Strobe" })343 try store.recordPlay(trackID: halo.id, at: Date(timeIntervalSinceReferenceDate: 10))344 try store.recordPlay(trackID: strobe.id, at: Date(timeIntervalSinceReferenceDate: 20))345 try store.recordPlay(trackID: strobe.id, at: Date(timeIntervalSinceReferenceDate: 30))346 #expect(try sorted(.lastPlayed, false).prefix(2) == ["Strobe", "Halo"])347 #expect(try sorted(.lastPlayed).prefix(2) == ["Halo", "Strobe"]) // never played stays last348 #expect(try sorted(.playCount, false).prefix(2) == ["Strobe", "Halo"])349 for key in TrackQuery.SortKey.allCases {350 #expect(try sorted(key).count == 6)351 #expect(try sorted(key, false).count == 6)352 }353 }354}355356@Suite struct CollectionTests {357 @Test func cratesMembershipIsASet() throws {358 let store = try LibraryStore(inMemory: true)359 let ids = try store.upsertTracks(["A", "B", "C"].map { sampleTrack($0) }).map(\.id)360 let crate = try store.createCrate(name: " Warm-up ")361 let other = try store.createCrate(name: "Peak")362 #expect(crate.name == "Warm-up")363 try store.addTracks([ids[2], ids[0], ids[0]], toCrate: crate.id)364 try store.addTracks([ids[0]], toCrate: crate.id)365 #expect(try store.tracks(query: TrackQuery(scope: .crate(crate.id))).map(\.title) == ["A", "C"])366 #expect(try store.crates().map(\.trackCount) == [2, 0])367 #expect(try store.crateIDs(containing: ids[0]) == [crate.id])368 try store.removeTracks([ids[0]], fromCrate: crate.id)369 #expect(try store.count(query: TrackQuery(scope: .crate(crate.id))) == 1)370 try store.renameCrate(id: crate.id, to: "Openers")371 try store.setCrateOrder([other.id, crate.id])372 #expect(try store.crates().map(\.name) == ["Peak", "Openers"])373 try store.removeTracks(ids: [ids[2]])374 #expect(try store.crates().map(\.trackCount) == [0, 0])375 try store.deleteCrate(id: crate.id)376 #expect(try store.crates().count == 1)377 #expect(try store.count() == 2)378 #expect(expectLibraryError({ if case .notFound = $0 { return true } else { return false } }) {379 try store.addTracks([987_654], toCrate: other.id)380 })381 #expect(expectLibraryError({ if case .invalidArgument = $0 { return true } else { return false } }) {382 _ = try store.createCrate(name: " ")383 })384 }385386 @Test func playlistCRUDAndOrdering() throws {387 let store = try LibraryStore(inMemory: true)388 let tracks = try store.upsertTracks(["A", "B", "C", "D", "E"].map { sampleTrack($0) })389 let id = Dictionary(uniqueKeysWithValues: tracks.map { ($0.title, $0.id) })390 let playlist = try store.createPlaylist(name: "Set")391 func order() throws -> [String] {392 try store.tracks(query: TrackQuery(scope: .playlist(playlist.id))).map(\.title)393 }394395 try store.addTracks([id["C"]!, id["A"]!, id["B"]!], toPlaylist: playlist.id)396 #expect(try order() == ["C", "A", "B"]) // insertion order, not alphabetical397 try store.addTracks([id["D"]!, id["E"]!], toPlaylist: playlist.id, at: 1)398 #expect(try order() == ["C", "D", "E", "A", "B"])399 try store.addTracks([id["C"]!], toPlaylist: playlist.id) // duplicates are allowed400 #expect(try order() == ["C", "D", "E", "A", "B", "C"])401 #expect(try store.playlistEntries(playlistID: playlist.id).map(\.position) == [0, 1, 2, 3, 4, 5])402 #expect(try store.playlists().first?.trackCount == 6)403404 // Same semantics as Array.move(fromOffsets:toOffset:).405 try store.movePlaylistEntries(playlistID: playlist.id, fromOffsets: IndexSet([0, 1]), toOffset: 4)406 #expect(try order() == ["E", "A", "C", "D", "B", "C"])407 try store.movePlaylistEntries(playlistID: playlist.id, fromOffsets: IndexSet(integer: 5), toOffset: 0)408 #expect(try order() == ["C", "E", "A", "C", "D", "B"])409 try store.movePlaylistEntries(playlistID: playlist.id, fromOffsets: IndexSet([1, 4]), toOffset: 6)410 #expect(try order() == ["C", "A", "C", "B", "E", "D"])411 try store.movePlaylistEntry(playlistID: playlist.id, from: 5, to: 0)412 #expect(try order() == ["D", "C", "A", "C", "B", "E"])413414 try store.removeFromPlaylist(playlist.id, atPositions: IndexSet([1, 5]))415 #expect(try order() == ["D", "A", "C", "B"])416 #expect(try store.playlistEntries(playlistID: playlist.id).map(\.position) == [0, 1, 2, 3])417 try store.removeTracks([id["A"]!], fromPlaylist: playlist.id)418 #expect(try order() == ["D", "C", "B"])419420 // An explicit sort key overrides the playlist order; search still applies.421 #expect(try store.tracks(query: TrackQuery(scope: .playlist(playlist.id), sort: .title)).map(\.title) == ["B", "C", "D"])422 #expect(try store.tracks(query: TrackQuery(search: "c", scope: .playlist(playlist.id))).map(\.title) == ["C"])423424 try store.removeTracks(ids: [id["C"]!]) // library removal cascades425 #expect(try order() == ["D", "B"])426 try store.setPlaylistTracks(playlistID: playlist.id, trackIDs: [id["B"]!, id["E"]!, id["D"]!])427 #expect(try order() == ["B", "E", "D"])428429 let second = try store.createPlaylist(name: "Other")430 try store.renamePlaylist(id: playlist.id, to: "Main set")431 try store.setPlaylistOrder([second.id, playlist.id])432 #expect(try store.playlists().map(\.name) == ["Other", "Main set"])433 try store.deletePlaylist(id: playlist.id)434 #expect(try store.playlists().map(\.name) == ["Other"])435 #expect(try store.read { try $0.scalarInt("SELECT COUNT(*) FROM playlist_tracks") } == 0)436 #expect(try store.count() == 4)437 }438439 @Test func settingsAndDocuments() throws {440 let store = try LibraryStore(inMemory: true)441 #expect(try store.setting("theme") == nil)442 try store.setSetting("theme", value: "dark")443 try store.setSetting("theme", value: "'; DROP TABLE settings;--")444 #expect(try store.setting("theme") == "'; DROP TABLE settings;--")445 try store.setSetting("crossfader", value: "0.5")446 #expect(try store.allSettings().count == 2)447 try store.setSetting("theme", value: nil)448 #expect(try store.setting("theme") == nil)449450 let json = #"{"effects":[{"id":"echo","mix":0.4}]}"#451 let saved = try store.saveDocument(kind: DocumentKind.effectChainPreset, name: "Dub", json: json)452 try store.saveDocument(kind: DocumentKind.effectChainPreset, name: "Air", json: "[]")453 try store.saveDocument(kind: DocumentKind.midiMapping, name: "Dub", json: "{}")454 let replaced = try store.saveDocument(kind: DocumentKind.effectChainPreset, name: "Dub", json: #"{"effects":[]}"#)455 #expect(replaced.id == saved.id)456 #expect(try store.documents(kind: DocumentKind.effectChainPreset).map(\.name) == ["Air", "Dub"])457 #expect(try store.document(kind: DocumentKind.effectChainPreset, name: "Dub")?.json == #"{"effects":[]}"#)458 #expect(try store.documents(kind: DocumentKind.midiMapping).count == 1)459 #expect(try store.documents(kind: DocumentKind.sessionRecovery).isEmpty)460 #expect(expectLibraryError({ if case .invalidArgument = $0 { return true } else { return false } }) {461 _ = try store.saveDocument(kind: DocumentKind.visualGraph, name: "Broken", json: "{not json")462 })463 #expect(try store.deleteDocument(kind: DocumentKind.effectChainPreset, name: "Dub"))464 #expect(try !store.deleteDocument(kind: DocumentKind.effectChainPreset, name: "Dub"))465 #expect(try store.deleteDocument(id: try #require(try store.document(kind: DocumentKind.midiMapping, name: "Dub")).id))466 }467468 @Test func changeCounterAndStream() async throws {469 let store = try LibraryStore(inMemory: true)470 let before = store.changeCount471 var iterator = store.changes().makeAsyncIterator()472 try store.upsertTracks([sampleTrack("A"), sampleTrack("B")]) // one transaction = one change473 #expect(store.changeCount == before + 1)474 await iterator.next()475 _ = try store.tracks()476 #expect(store.changeCount == before + 1) // reads do not count477 #expect(throws: LibraryError.self) { try store.setRating(trackID: 999, rating: 1) }478 #expect(store.changeCount == before + 1) // failed writes do not count479 store.close()480 #expect(await iterator.next() == nil) // the stream ends with the store481 }482}483484@Suite struct SmartCrateTests {485 static let injection = "'; DROP TABLE tracks;--"486487 private func populated() throws -> (LibraryStore, Date) {488 let store = try LibraryStore(inMemory: true)489 let now = Date()490 func make(_ title: String, artist: String, genre: String, bpm: Double?, year: Int, rating: Int, duration: Double,491 daysAgo: Double, format: String = "wav", key: String? = nil, camelot: String? = nil,492 favorite: Bool = false, tags: [String] = [], analysed: Bool = false, album: String = "") -> Track {493 var track = sampleTrack(title, artist: artist, album: album, genre: genre, bpm: bpm, path: "/m/\(title).\(format)")494 track.year = year; track.rating = rating; track.duration = duration; track.format = format495 track.key = key; track.camelot = camelot; track.favorite = favorite; track.tags = tags496 track.analysisVersion = analysed ? 2 : 0497 track.dateAdded = now.addingTimeInterval(-daysAgo * 86_400)498 return track499 }500 try store.upsertTracks([501 make("Alpha", artist: "Beyoncé", genre: "Pop", bpm: 100, year: 2008, rating: 5, duration: 200, daysAgo: 0.5,502 key: "Am", camelot: "8A", favorite: true, tags: ["vocal", "peak time"], analysed: true, album: "First"),503 make("Bravo", artist: "Daft Punk", genre: "House", bpm: 124, year: 1997, rating: 4, duration: 430, daysAgo: 10,504 format: "flac", key: "F#m", camelot: "11A", tags: ["classic"], analysed: true, album: "Homework"),505 make("Charlie", artist: "deadmau5", genre: "Progressive House", bpm: 128, year: 2009, rating: 3, duration: 630,506 daysAgo: 40, format: "mp3", key: "C", camelot: "8B", tags: ["peak"], album: "Second"),507 make("Delta", artist: "Unknown", genre: "", bpm: nil, year: 0, rating: 0, duration: 90, daysAgo: 400, format: "m4a"),508 make(Self.injection, artist: Self.injection, genre: Self.injection, bpm: 140, year: 2020, rating: 1, duration: 300,509 daysAgo: 3, tags: [Self.injection], album: "100% _wild_"),510 ])511 return (store, now)512 }513514 private func match(_ store: LibraryStore, _ rules: [SmartRule], _ mode: SmartCrate.Match = .all) throws -> Set<String> {515 Set(try store.tracks(matching: rules, match: mode).map(\.title))516 }517518 @Test func equalsAndNotEquals() throws {519 let (store, _) = try populated()520 let i = Self.injection521 #expect(try match(store, [SmartRule(.bpm, .equals, 124)]) == ["Bravo"])522 #expect(try match(store, [SmartRule(.bpm, .notEquals, 124)]) == ["Alpha", "Charlie", "Delta", i]) // unknown BPM ≠ 124523 #expect(try match(store, [SmartRule(field: .artist, comparison: .equals, value: "beyonce")]) == ["Alpha"])524 #expect(try match(store, [SmartRule(field: .artist, comparison: .notEquals, value: "BEYONCÉ")]).count == 4)525 #expect(try match(store, [SmartRule(field: .genre, comparison: .equals, value: "house")]) == ["Bravo"])526 #expect(try match(store, [SmartRule(field: .title, comparison: .equals, value: "charlie")]) == ["Charlie"])527 #expect(try match(store, [SmartRule(field: .album, comparison: .equals, value: "homework")]) == ["Bravo"])528 #expect(try match(store, [SmartRule(field: .key, comparison: .equals, value: "f#m")]) == ["Bravo"])529 #expect(try match(store, [SmartRule(field: .camelot, comparison: .equals, value: "8a")]) == ["Alpha"])530 #expect(try match(store, [SmartRule(field: .format, comparison: .equals, value: "FLAC")]) == ["Bravo"])531 #expect(try match(store, [SmartRule(field: .format, comparison: .notEquals, value: "wav")]) == ["Bravo", "Charlie", "Delta"])532 #expect(try match(store, [SmartRule(.rating, .equals, 5)]) == ["Alpha"])533 #expect(try match(store, [SmartRule(.year, .equals, 1997)]) == ["Bravo"])534 #expect(try match(store, [SmartRule(.playCount, .equals, 0)]).count == 5)535 #expect(try match(store, [SmartRule(.favorite, is: true)]) == ["Alpha"])536 #expect(try match(store, [SmartRule(field: .favorite, comparison: .notEquals, value: "yes")]).count == 4)537 #expect(try match(store, [SmartRule(.analysed, is: true)]) == ["Alpha", "Bravo"])538 #expect(try match(store, [SmartRule(.analysed, is: false)]) == ["Charlie", "Delta", i])539 // Tag equality is whole-tag: "peak" is not "peak time".540 #expect(try match(store, [SmartRule(field: .tag, comparison: .equals, value: "Peak")]) == ["Charlie"])541 #expect(try match(store, [SmartRule(field: .tag, comparison: .notEquals, value: "peak")]).count == 4)542 #expect(try match(store, [SmartRule(.dateAdded, .equals, 10)]) == ["Bravo"])543 #expect(try match(store, [SmartRule(.dateAdded, .notEquals, 10)]).count == 4)544 }545546 @Test func containsGreaterLessBetween() throws {547 let (store, _) = try populated()548 let i = Self.injection549 #expect(try match(store, [SmartRule(field: .genre, comparison: .contains, value: "HOUSE")]) == ["Bravo", "Charlie"])550 #expect(try match(store, [SmartRule(field: .artist, comparison: .contains, value: "yonc")]) == ["Alpha"])551 #expect(try match(store, [SmartRule(field: .tag, comparison: .contains, value: "peak")]) == ["Alpha", "Charlie"])552 #expect(try match(store, [SmartRule(field: .camelot, comparison: .contains, value: "8")]) == ["Alpha", "Charlie"])553 // LIKE wildcards in the value are literal.554 #expect(try match(store, [SmartRule(field: .album, comparison: .contains, value: "100%")]) == [i])555 #expect(try match(store, [SmartRule(field: .album, comparison: .contains, value: "_wild_")]) == [i])556 #expect(try match(store, [SmartRule(field: .album, comparison: .contains, value: "%")]) == [i])557558 #expect(try match(store, [SmartRule(.bpm, .greaterThan, 124)]) == ["Charlie", i])559 #expect(try match(store, [SmartRule(.bpm, .lessThan, 124)]) == ["Alpha"])560 #expect(try match(store, [SmartRule(.bpm, .between, 124, 128)]) == ["Bravo", "Charlie"])561 #expect(try match(store, [SmartRule(.bpm, .between, 128, 124)]) == ["Bravo", "Charlie"]) // bounds in any order562 #expect(try match(store, [SmartRule(.rating, .greaterThan, 3)]) == ["Alpha", "Bravo"])563 #expect(try match(store, [SmartRule(.year, .between, 2000, 2010)]) == ["Alpha", "Charlie"])564 #expect(try match(store, [SmartRule(.duration, .lessThan, 250)]) == ["Alpha", "Delta"])565 #expect(try match(store, [SmartRule(.duration, .greaterThan, 600)]) == ["Charlie"])566 #expect(try match(store, [SmartRule(.playCount, .lessThan, 1)]).count == 5)567 #expect(try match(store, [SmartRule(field: .title, comparison: .greaterThan, value: "bravo")]) == ["Charlie", "Delta"])568 #expect(try match(store, [SmartRule(field: .title, comparison: .lessThan, value: "alpha")]) == [i])569 #expect(try match(store, [SmartRule(field: .title, comparison: .between, value: "b", upperValue: "d")]) == ["Bravo", "Charlie"])570 // dateAdded is an age in days.571 #expect(try match(store, [SmartRule(.dateAdded, .lessThan, 7)]) == ["Alpha", i])572 #expect(try match(store, [SmartRule(.dateAdded, .greaterThan, 30)]) == ["Charlie", "Delta"])573 #expect(try match(store, [SmartRule(.dateAdded, .between, 5, 60)]) == ["Bravo", "Charlie"])574 }575576 @Test func allAnyAndStoredCrates() throws {577 let (store, _) = try populated()578 let rules = [SmartRule(field: .genre, comparison: .contains, value: "house"), SmartRule(.bpm, .greaterThan, 125)]579 #expect(try match(store, rules, .all) == ["Charlie"])580 #expect(try match(store, rules, .any) == ["Bravo", "Charlie", Self.injection])581 #expect(try match(store, []).count == 5)582583 var crate = try store.createSmartCrate(name: "House", match: .all, rules: rules)584 #expect(try store.tracks(query: TrackQuery(scope: .smartCrate(crate.id))).map(\.title) == ["Charlie"])585 crate.match = .any586 crate.name = "House or fast"587 try store.updateSmartCrate(crate)588 #expect(try store.smartCrates() == [crate])589 #expect(try store.count(query: TrackQuery(scope: .smartCrate(crate.id))) == 3)590 // Search and sort compose with the smart predicate.591 #expect(try store.tracks(query: TrackQuery(search: "daft", scope: .smartCrate(crate.id))).map(\.title) == ["Bravo"])592 #expect(try store.tracks(query: TrackQuery(scope: .smartCrate(crate.id), sort: .bpm, ascending: false)).first?.title == Self.injection)593 try store.deleteSmartCrate(id: crate.id)594 #expect(expectLibraryError({ if case .notFound = $0 { return true } else { return false } }) {595 _ = try store.tracks(query: TrackQuery(scope: .smartCrate(crate.id)))596 })597 }598599 @Test func injectionStringsAreBoundNotInterpolated() throws {600 let (store, _) = try populated()601 let i = Self.injection602 let attempts = [i, "x' OR '1'='1", "\"; DELETE FROM tracks; --", "1; DROP TABLE tracks", "') OR 1=1 --", "\\", "%' OR ''='"]603 for field in SmartRule.Field.allCases {604 for comparison in SmartRule.Comparison.allCases {605 for attempt in attempts {606 let rule = SmartRule(field: field, comparison: comparison, value: attempt, upperValue: attempt)607 let crate = SmartCrate(name: "probe", rules: [rule])608 if let predicate = try? crate.predicate() {609 // The SQL text is built from fixed fragments only.610 #expect(!predicate.sql.contains("DROP"))611 #expect(!predicate.sql.contains("DELETE"))612 #expect(!predicate.sql.contains("1=1"))613 // ("\\" alone is excluded: the fixed fragment ESCAPE '\\' contains a backslash.)614 if attempt != "\\" { #expect(!predicate.sql.contains(attempt)) }615 _ = try store.tracks(matching: [rule])616 } else {617 // Numeric and boolean fields refuse text instead of passing it on.618 #expect(expectLibraryError({ if case .invalidArgument = $0 { return true } else { return false } }) {619 _ = try store.tracks(matching: [rule])620 })621 }622 }623 }624 }625 #expect(try store.count() == 5) // the table is still there, with all its rows626627 // Stored and matched literally.628 #expect(try match(store, [SmartRule(field: .title, comparison: .equals, value: i)]) == [i])629 #expect(try match(store, [SmartRule(field: .artist, comparison: .contains, value: "DROP TABLE")]) == [i])630 #expect(try match(store, [SmartRule(field: .genre, comparison: .equals, value: i)]) == [i])631 #expect(try match(store, [SmartRule(field: .tag, comparison: .equals, value: i)]) == [i])632 #expect(try match(store, [SmartRule(field: .title, comparison: .equals, value: "x' OR '1'='1")]).isEmpty)633634 let crate = try store.createSmartCrate(name: i, rules: [SmartRule(field: .title, comparison: .equals, value: i)])635 #expect(try store.smartCrate(id: crate.id)?.name == i)636 #expect(try store.tracks(query: TrackQuery(search: i, scope: .smartCrate(crate.id))).map(\.title) == [i])637 let stored = try #require(try store.tracks(query: TrackQuery(scope: .smartCrate(crate.id))).first)638 #expect(stored.artist == i)639 #expect(stored.tags == [i])640 }641642 @Test func invalidRulesAreRejected() throws {643 let store = try LibraryStore(inMemory: true)644 let bad = [645 SmartRule(field: .bpm, comparison: .equals, value: "fast"),646 SmartRule(field: .bpm, comparison: .contains, value: "12"),647 SmartRule(field: .bpm, comparison: .between, value: "120"),648 SmartRule(field: .favorite, comparison: .greaterThan, value: "true"),649 SmartRule(field: .favorite, comparison: .equals, value: "maybe"),650 SmartRule(field: .tag, comparison: .between, value: "a", upperValue: "b"),651 SmartRule(field: .title, comparison: .between, value: "a"),652 SmartRule(field: .dateAdded, comparison: .contains, value: "3"),653 ]654 for rule in bad {655 #expect(expectLibraryError({ if case .invalidArgument = $0 { return true } else { return false } }) {656 _ = try store.createSmartCrate(name: "bad", rules: [rule])657 })658 }659 #expect(try store.smartCrates().isEmpty)660 }661}662663@Suite(.serialized) struct PerformanceAndConcurrencyTests {664 @Test func twentyThousandTracksInsertAndSearchTimings() throws {665 let temp = TempDirectory()666 let store = try LibraryStore(url: temp.file("big.sqlite"))667 let artists = ["Beyoncé", "Daft Punk", "deadmau5", "Björk", "Röyksopp", "Moderat", "Bicep", "Jamie xx", "Caribou", "Four Tet"]668 let genres = ["House", "Techno", "Drum & Bass", "Disco", "Ambient", "Trance", "Hip-Hop", "Pop"]669 let tracks = (0..<20_000).map { index -> Track in670 var track = sampleTrack("Track \(index) \(index % 97 == 0 ? "Sunrise" : "Night")",671 artist: artists[index % artists.count], album: "Album \(index / 12)",672 genre: genres[index % genres.count], bpm: 90 + Double(index % 80),673 path: "/music/library/\(index / 100)/track-\(index).flac")674 track.tags = index % 5 == 0 ? ["peak", "vocal"] : []675 track.rating = index % 6676 track.camelot = "\(index % 12 + 1)\(index % 2 == 0 ? "A" : "B")"677 return track678 }679 let clock = ContinuousClock()680 func milliseconds(_ duration: Duration) -> Double {681 Double(duration.components.seconds) * 1_000 + Double(duration.components.attoseconds) / 1e15682 }683 var listed = 0, found = 0, counted = 0, smart = 0, paged = 0, single = 0684 let insert = clock.measure { _ = try? store.upsertTracks(tracks) }685 #expect(try store.count() == 20_000)686 let listAll = clock.measure { listed = (try? store.tracks().count) ?? -1 }687 let search = clock.measure { found = (try? store.tracks(query: TrackQuery(search: "beyonce sunrise")).count) ?? -1 }688 let count = clock.measure { counted = (try? store.count(query: TrackQuery(search: "bjork"))) ?? -1 }689 let page = clock.measure {690 paged = (try? store.tracks(query: TrackQuery(search: "night", sort: .bpm, ascending: false, limit: 100)).count) ?? -1691 }692 let smartQuery = clock.measure {693 smart = (try? store.tracks(matching: [SmartRule(.bpm, .between, 120, 130), SmartRule(field: .genre, comparison: .contains, value: "house"),694 SmartRule(field: .tag, comparison: .equals, value: "peak")]).count) ?? -1695 }696 let oneByOne = clock.measure {697 for index in 0..<200 where (try? store.setRating(trackID: Int64(index + 1), rating: 3)) != nil { single += 1 }698 }699 #expect(listed == 20_000)700 #expect(found == 21) // multiples of 970 below 20 000701 #expect(counted == 2_000)702 #expect(paged == 100)703 #expect(smart > 0)704 #expect(single == 200)705 print(String(format: """706 [ORIXLibrary timing, debug build] 20 000 tracks: batch insert %.0f ms | list all %.0f ms | \707 2-word search %.1f ms | count with search %.1f ms | search + sort + limit 100 %.1f ms | \708 3-rule smart query %.1f ms | 200 single-row writes %.0f ms709 """, milliseconds(insert), milliseconds(listAll), milliseconds(search), milliseconds(count),710 milliseconds(page), milliseconds(smartQuery), milliseconds(oneByOne)))711 // Loose bounds only: these catch an accidental quadratic path, not a slow machine.712 #expect(insert < .seconds(30))713 #expect(search < .seconds(3))714 #expect(listAll < .seconds(10))715 }716717 @Test func concurrentAccessFromManyThreads() throws {718 let temp = TempDirectory()719 let store = try LibraryStore(url: temp.file("concurrent.sqlite"))720 let seed = try store.upsertTracks((0..<200).map { sampleTrack("Seed \($0)") }).map(\.id)721 let playlist = try store.createPlaylist(name: "Shared")722 let script = try store.createScript(name: "Shared", source: "v0")723 let failures = FailureCounter()724 let threads = 12725 let iterations = 150726727 DispatchQueue.concurrentPerform(iterations: threads) { thread in728 for iteration in 0..<iterations {729 do {730 switch (thread + iteration) % 8 {731 case 0: try store.upsertTrack(sampleTrack("T\(thread)-\(iteration)"))732 case 1: _ = try store.tracks(query: TrackQuery(search: "seed 1", sort: .title, limit: 50))733 case 2: try store.setRating(trackID: seed[(thread * 31 + iteration) % seed.count], rating: iteration % 6)734 case 3: try store.recordPlay(trackID: seed[iteration % seed.count], deck: "A")735 case 4: try store.addTracks([seed[iteration % seed.count]], toPlaylist: playlist.id, at: 0)736 case 5: _ = try store.count(query: TrackQuery(scope: .playlist(playlist.id)))737 case 6: try store.setSetting("k\(thread)", value: "\(iteration)")738 default:739 // Optimistic saves race on purpose: a conflict is the correct outcome for the loser.740 if let current = try store.script(id: script.id) {741 do {742 try store.saveScript(id: script.id, source: "v-\(thread)-\(iteration)", expectedUpdatedAt: current.updatedAt)743 } catch LibraryError.conflict {}744 }745 }746 } catch {747 failures.record(error)748 }749 }750 }751 #expect(failures.count == 0, "unexpected errors: \(failures.samples)")752 // Every write landed exactly once.753 let expectedInserts = (0..<threads).reduce(0) { total, thread in total + (0..<iterations).filter { (thread + $0) % 8 == 0 }.count }754 let expectedPlays = (0..<threads).reduce(0) { total, thread in total + (0..<iterations).filter { (thread + $0) % 8 == 3 }.count }755 let expectedAdds = (0..<threads).reduce(0) { total, thread in total + (0..<iterations).filter { (thread + $0) % 8 == 4 }.count }756 #expect(try store.count() == 200 + expectedInserts)757 #expect(try store.history().count == expectedPlays)758 #expect(try store.tracks().reduce(0) { $0 + $1.playCount } == expectedPlays)759 let entries = try store.playlistEntries(playlistID: playlist.id)760 #expect(entries.count == expectedAdds)761 #expect(try store.read { try $0.scalarInt("SELECT COUNT(DISTINCT position) FROM playlist_tracks") } == Int64(expectedAdds))762 // Version history has no gaps and the latest version is the stored source.763 let versions = try store.scriptVersions(scriptID: script.id)764 #expect(versions.map(\.version) == Array((1...versions.count).reversed()))765 #expect(versions.first?.source == (try store.script(id: script.id)?.source))766 #expect(try store.read { try $0.query("PRAGMA integrity_check") { $0.string(0) } } == ["ok"])767 }768769 @Test func twoStoresOnTheSameFile() throws {770 let temp = TempDirectory()771 let url = temp.file("shared.sqlite")772 let first = try LibraryStore(url: url)773 let second = try LibraryStore(url: url)774 let failures = FailureCounter()775 DispatchQueue.concurrentPerform(iterations: 4) { thread in776 let store = thread % 2 == 0 ? first : second777 for iteration in 0..<100 {778 do {779 try store.upsertTrack(sampleTrack("S\(thread)-\(iteration)"))780 _ = try store.count()781 } catch {782 failures.record(error)783 }784 }785 }786 #expect(failures.count == 0, "unexpected errors: \(failures.samples)")787 #expect(try first.count() == 400)788 #expect(try second.count() == 400)789 }790}791792final class FailureCounter: @unchecked Sendable {793 private let lock = NSLock()794 private var errors: [String] = []795796 func record(_ error: Error) {797 lock.lock()798 errors.append(String(describing: error))799 lock.unlock()800 }801802 var count: Int {803 lock.lock()804 defer { lock.unlock() }805 return errors.count806 }807808 var samples: [String] {809 lock.lock()810 defer { lock.unlock() }811 return Array(errors.prefix(5))812 }813}814