SPB Git

Repository Public

LibraryStoreTests.swift 46.5 KB 814 lines swift

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

Theme