SPB Git

Repository Public

MigrationV2Tests.swift 20.0 KB 377 lines swift

1// SP Notes Core2// File: Tests/SPNotesStoreTests/MigrationV2Tests.swift3// Description: The first migration ever run on shipped data, tested on a real schema v1 file (Fixtures/v1.sqlite,4//              written by the 1.0 code): every v1 row survives, the step is transactional and idempotent, a failure5//              mid-way leaves v1 intact, the pre-migration backup exists and is removed later, and opening6//              failures come back as typed errors instead of an empty store.7//8// Author: Simon-Pierre Boucher9// Contact: contact@spboucher.ai10import Foundation11import Testing12@testable import SPNotesStore1314/// A private copy of the v1 fixture in a temporary directory.15func v1Copy() throws -> TempDatabase {16    let temp = TempDatabase()17    let fixture = try #require(Bundle.module.url(forResource: "v1", withExtension: "sqlite", subdirectory: "Fixtures"))18    try FileManager.default.copyItem(at: fixture, to: temp.url)19    return temp20}2122let v1Tables = ["pages", "page_bases", "tags", "page_tags", "attachments", "ops", "conflicts", "local_versions", "reminders", "meta"]2324/// Every row of every v1 table, as text, in a stable order.25func dump(_ db: Database, tables: [String] = v1Tables) throws -> [String: [String]] {26    var out: [String: [String]] = [:]27    for table in tables {28        let columns = try db.query("SELECT name FROM pragma_table_info('\(table)') ORDER BY cid") { $0.string(0) }29        let list = columns.map { "quote(\($0))" }.joined(separator: " || '|' || ")30        out[table] = try db.query("SELECT \(list) FROM \(table) ORDER BY 1") { $0.string(0) }31    }32    out["pages_fts"] = try db.query("SELECT rowid || '|' || title || '|' || body || '|' || tags FROM pages_fts ORDER BY rowid") { $0.string(0) }33    return out34}3536func dump(path: String, tables: [String] = v1Tables) throws -> [String: [String]] {37    // Read-write on purpose: a read-only connection cannot open a cleanly closed WAL database (no -shm file).38    let db = try Database(path: path, readOnly: false)39    defer { db.close() }40    return try dump(db, tables: tables)41}4243/// The schema objects (tables, indexes) as SQL text, sorted.44func schemaSQL(_ db: Database) throws -> [String] {45    try db.query("SELECT type || ' ' || name || ' ' || coalesce(sql, '') FROM sqlite_master WHERE name NOT LIKE 'sqlite_%' ORDER BY type, name") { $0.string(0) }46}4748func userVersion(_ path: String) -> Int? { StoreBackup.peekVersion(path: path) }4950@Suite struct MigrationV2Tests {51    @Test func fixtureIsARealV1DatabaseWithData() throws {52        let temp = try v1Copy()53        #expect(userVersion(temp.url.path) == 1)54        let rows = try dump(path: temp.url.path)55        #expect(rows["pages"]?.count == 6)56        #expect(rows["ops"]?.count == 3)57        #expect(rows["page_bases"]?.count == 3)58        #expect(rows["conflicts"]?.count == 1)59        #expect(rows["local_versions"]?.count == 1)60        #expect(rows["reminders"]?.count == 1)61        #expect(rows["attachments"]?.count == 2)62        #expect((rows["meta"]?.count ?? 0) >= 2)63        for table in v1Tables { #expect(!(rows[table] ?? []).isEmpty, "v1 table \(table) has rows in the fixture") }64        let db = try Database(path: temp.url.path, readOnly: false)65        #expect(try db.scalar("SELECT count(*) FROM sqlite_master WHERE name IN ('entities','entity_ops','canvases','events','kv_cache','sync_cursors','ops_parked')") == 0)66    }6768    @Test func migrationKeepsEveryV1RowAndAddsTheNewTables() async throws {69        let temp = try v1Copy()70        let before = try dump(path: temp.url.path)71        let store = try NotesStore(url: temp.url)72        #expect(NotesStore.schemaVersion == 2)73        #expect(try await store.debugValue("PRAGMA user_version") == "2")74        #expect(store.openReport.foundVersion == 1)75        #expect(store.openReport.migratedFrom == 1)76        #expect(try await store.integrityCheck().isEmpty)77        // Typed reads through the 2.0 code agree with the 1.0 data.78        let alpha = try await store.must("00000000-0000-4000-8000-000000000001")79        #expect(alpha.title == "Projet Alpha")80        #expect(alpha.dirtyContent)81        #expect(alpha.tags == ["alpha", "projet"])82        #expect(try await store.pageBase(id: alpha.id)?.version == 3)83        #expect(try await store.conflicts().map(\.pageId) == ["00000000-0000-4000-8000-000000000005"])84        #expect(try await store.syncCursor() == 16)85        #expect(try await store.meta("workspace.name") == "Atelier")86        #expect(try await store.queuedOps().map(\.kind) == ["trash", "deleteAttachment", "futureKind"])87        #expect(try await store.search(query: "zèbre").map(\.id) == ["00000000-0000-4000-8000-000000000010"])88        #expect(try await store.search(query: "reunion").count == 1) // diacritics folding still works89        #expect(try await store.localVersions(pageId: alpha.id).count == 1)90        #expect(try await store.dirtyReminders().count == 1)91        #expect(try await store.pendingUploads().map(\.filename) == ["photo.png"])92        // The seven new tables exist and are empty.93        for table in Schema.v2Tables { #expect(try await store.debugValue("SELECT count(*) FROM \(table)") == "0") }94        #expect(try await store.entityCounts().isEmpty)95        try await store.checkpoint()96        await store.close()97        let after = try dump(path: temp.url.path)98        #expect(after == before)99    }100101    @Test func migratedStoreIsFullyUsable() async throws {102        let temp = try v1Copy()103        let store = try NotesStore(url: temp.url)104        _ = try await store.edit("00000000-0000-4000-8000-000000000001", text: "après migration okapi")105        #expect(try await store.search(query: "okapi").count == 1)106        let created = try await store.createPage(draft("Nouvelle", "page créée en v2"))107        _ = try await store.saveLocalEntity(kind: .task, id: "t1", json: Data(#"{"title":"x","pageId":"\#(created.id)"}"#.utf8), op: EntityOpVerb.create)108        #expect(try await store.tasks(pageId: created.id).count == 1)109        _ = try await store.purge(id: "00000000-0000-4000-8000-000000000004")110        #expect(try await store.integrityCheck().isEmpty)111    }112113    @Test func migrationIsIdempotent() async throws {114        let temp = try v1Copy()115        var schemas: [[String]] = []116        var dumps: [[String: [String]]] = []117        for _ in 0..<3 {118            let store = try NotesStore(url: temp.url)119            try await store.checkpoint()120            await store.close()121            let db = try Database(path: temp.url.path, readOnly: false)122            schemas.append(try schemaSQL(db))123            dumps.append(try dump(db, tables: v1Tables + Schema.v2Tables))124            #expect(try db.scalar("PRAGMA user_version") == 2)125            db.close()126        }127        #expect(schemas[0] == schemas[1] && schemas[1] == schemas[2])128        #expect(dumps[0] == dumps[1] && dumps[1] == dumps[2])129        // Running the migrator again on an up-to-date connection does nothing either.130        let raw = try Database(path: temp.url.path, readOnly: false)131        try Schema.migrate(raw)132        try Schema.migrate(raw)133        #expect(try schemaSQL(raw) == schemas[0])134    }135136    @Test func freshDatabaseAndMigratedDatabaseHaveTheSameSchema() async throws {137        let fresh = TempDatabase()138        let a = try NotesStore(url: fresh.url)139        await a.close()140        let migrated = try v1Copy()141        let b = try NotesStore(url: migrated.url)142        await b.close()143        let x = try Database(path: fresh.url.path, readOnly: false), y = try Database(path: migrated.url.path, readOnly: false)144        #expect(try schemaSQL(x) == schemaSQL(y))145    }146147    @Test func failureInTheMiddleOfV2RollsBackToAnIntactV1() throws {148        let temp = try v1Copy()149        let before = try dump(path: temp.url.path)150        let raw = try Database(path: temp.url.path, readOnly: false)151        let schemaBefore = try schemaSQL(raw)152        struct Boom: Error {}153        // The real v2 DDL runs entirely, rows are written into its tables, then the step fails before its commit.154        let failing: [Schema.Migration] = [Schema.migrations[0], { db in155            try Schema.v2(db)156            try db.run("INSERT INTO kv_cache(key, json, fetched_at) VALUES('x', x'00', 1)")157            try db.run("UPDATE pages SET title='CORROMPU'")158            try db.run("DELETE FROM ops")159            throw Boom()160        }]161        #expect(throws: Boom.self) { try Schema.migrate(raw, steps: failing) }162        #expect(try raw.scalar("PRAGMA user_version") == 1)163        #expect(try schemaSQL(raw) == schemaBefore)164        #expect(try dump(raw) == before)165        #expect(try raw.query("PRAGMA integrity_check") { $0.string(0) } == ["ok"])166        // A failure half-way through the DDL itself (a statement in the middle of the script is refused).167        let halfway: [Schema.Migration] = [Schema.migrations[0], { db in168            try db.exec("CREATE TABLE entities(x); CREATE TABLE canvases(y); CREATE TABLE pages(z);")169        }]170        #expect(throws: StoreError.self) { try Schema.migrate(raw, steps: halfway) }171        #expect(try raw.scalar("PRAGMA user_version") == 1)172        #expect(try schemaSQL(raw) == schemaBefore)173        #expect(try dump(raw) == before)174        // The real migration then succeeds on the same file.175        try Schema.migrate(raw)176        #expect(try raw.scalar("PRAGMA user_version") == 2)177        #expect(try dump(raw) == before)178    }179180    @Test func aRealFailureOfV2IsReportedTypedAndLeavesV1PlusABackup() async throws {181        let temp = try v1Copy()182        let before = try dump(path: temp.url.path)183        do {184            // A table named like one of v2's already exists: CREATE TABLE fails in the middle of the script.185            let raw = try Database(path: temp.url.path, readOnly: false)186            try raw.exec("CREATE TABLE canvases(unexpected)")187            raw.close()188        }189        let result = NotesStore.open(url: temp.url)190        #expect(result.store == nil)191        guard case .failed(.migrationFailed(let from, let to, let reason, let backup)) = result else {192            Issue.record("expected migrationFailed, got \(String(describing: result.error))")193            return194        }195        #expect(from == 1 && to == 2)196        #expect(reason.contains("canvases"))197        #expect(result.error?.userMessage.contains("intactes") == true)198        #expect(result.error?.backupURL == backup)199        let backupURL = try #require(backup)200        #expect(FileManager.default.fileExists(atPath: backupURL.path))201        #expect(userVersion(temp.url.path) == 1)202        #expect(try dump(path: temp.url.path) == before)203        #expect(try dump(path: backupURL.path) == before)204        // No half-created v2 table is left (entities is created before canvases in the script).205        let raw = try Database(path: temp.url.path, readOnly: false)206        #expect(try raw.scalar("SELECT count(*) FROM sqlite_master WHERE name IN ('entities','entity_ops','ops_parked')") == 0)207        // Once the obstacle is gone the migration goes through; the pristine backup of the first attempt was kept.208        try raw.exec("DROP TABLE canvases")209        raw.close()210        let second = NotesStore.open(url: temp.url)211        let store = try second.get()212        #expect(store.openReport.backupURL == backupURL)213        #expect(try await store.pageCount() == 6)214    }215216    @Test func backupIsMadeBeforeMigratingAndRemovedAtALaterHealthyOpen() async throws {217        let temp = try v1Copy()218        let before = try dump(path: temp.url.path)219        let backup = StoreBackup.backupURL(path: temp.url.path, version: 1)220        #expect(backup.lastPathComponent == "notes.sqlite.backup-v1")221        let first = try NotesStore(url: temp.url)222        #expect(first.openReport.backupURL == backup)223        #expect(first.openReport.removedBackups.isEmpty)224        #expect(FileManager.default.fileExists(atPath: backup.path))225        #expect(!FileManager.default.fileExists(atPath: backup.path + ".partial"))226        // The backup is a complete, healthy schema v1 database holding the pre-migration rows.227        #expect(userVersion(backup.path) == 1)228        #expect(try dump(path: backup.path) == before)229        let check = try Database(path: backup.path, readOnly: false)230        #expect(try check.query("PRAGMA integrity_check") { $0.string(0) } == ["ok"])231        check.close()232        _ = try await first.edit("00000000-0000-4000-8000-000000000001", text: "modifié après la migration")233        await first.close()234        // Another open moments later (widget, extension, relaunch) keeps it: it is younger than the retention.235        let second = try NotesStore(url: temp.url)236        #expect(second.openReport.migratedFrom == nil)237        #expect(second.openReport.backupURL == backup)238        #expect(second.openReport.removedBackups.isEmpty)239        await second.close()240        #expect(FileManager.default.fileExists(atPath: backup.path))241        // A read-only connection never removes it.242        let reader = try NotesStore(url: temp.url, readOnly: true)243        await reader.close()244        #expect(FileManager.default.fileExists(atPath: backup.path))245        // The next successful launch after the retention removes it.246        let third = NotesStore.open(url: temp.url, backupRetention: 0)247        #expect(third.store?.openReport.removedBackups == [backup])248        #expect(third.store?.openReport.backupURL == nil)249        #expect(!FileManager.default.fileExists(atPath: backup.path))250        #expect(StoreBackup.existingBackups(of: temp.url).isEmpty)251    }252253    @Test func backupIncludesWhatIsStillInTheWriteAheadLog() async throws {254        // A v1 file whose last writes are only in the WAL (the app was killed before a checkpoint).255        let temp = try v1Copy()256        let raw = try Database(path: temp.url.path, readOnly: false)257        try raw.exec("PRAGMA wal_autocheckpoint=0")258        try raw.run("UPDATE pages SET title='Seulement dans le WAL' WHERE id='00000000-0000-4000-8000-000000000001'")259        #expect(FileManager.default.fileExists(atPath: temp.url.path + "-wal"))260        let other = try NotesStore(url: temp.url)   // opened while the first connection still holds the WAL261        let backup = try #require(other.openReport.backupURL)262        let db = try Database(path: backup.path, readOnly: false)263        #expect(try db.first("SELECT title FROM pages WHERE id='00000000-0000-4000-8000-000000000001'") { $0.string(0) } == "Seulement dans le WAL")264        #expect(try await other.page(id: "00000000-0000-4000-8000-000000000001")?.title == "Seulement dans le WAL")265        raw.close()266    }267268    @Test func noBackupForANewFileNorInMemory() async throws {269        let temp = TempDatabase()270        let store = try NotesStore(url: temp.url)271        #expect(store.openReport.foundVersion == 0)272        #expect(store.openReport.migratedFrom == 0)273        #expect(store.openReport.backupURL == nil)274        #expect(StoreBackup.existingBackups(of: temp.url).isEmpty)275        let memory = try NotesStore.inMemory()276        #expect(memory.openReport.backupURL == nil)277        #expect(try await memory.debugValue("PRAGMA user_version") == "2")278    }279280    @Test func backupFailureBlocksTheMigration() throws {281        let temp = try v1Copy()282        let raw = try Database(path: temp.url.path, readOnly: false)283        let nowhere = temp.directory.appendingPathComponent("missing-directory/backup.sqlite")284        #expect(throws: StoreError.self) { try StoreBackup.write(raw, to: nowhere) }285        do { try StoreBackup.write(raw, to: nowhere) } catch let error as StoreError {286            guard case .backup = error else { Issue.record("expected .backup, got \(error)"); return }287            #expect(NotesStore.openError(for: error, url: temp.url) == .backupFailed(String(error.description.dropFirst("pre-migration backup failed: ".count))))288        }289        #expect(try raw.scalar("PRAGMA user_version") == 1)290        #expect(StoreOpenError.backupFailed("x").isTransient)291        #expect(StoreOpenError.backupFailed("x").userMessage.contains("n’a donc pas été tentée"))292    }293294    @Test func newerFileIsRefusedTypedAndUntouched() async throws {295        let temp = try v1Copy()296        do {297            let raw = try Database(path: temp.url.path, readOnly: false)298            try raw.exec("PRAGMA user_version=99")299            raw.close()300        }301        let before = try dump(path: temp.url.path)302        let result = NotesStore.open(url: temp.url)303        #expect(result.error == .newerSchema(found: 99, supported: 2))304        #expect(result.error?.isTransient == false)305        #expect(result.error?.errorDescription?.contains("version plus récente") == true)306        #expect(throws: StoreOpenError.self) { try result.get() }307        #expect(userVersion(temp.url.path) == 99)308        #expect(try dump(path: temp.url.path) == before)309        #expect(StoreBackup.existingBackups(of: temp.url).isEmpty)310        // The plain initializer still throws the 1.x error; openError(for:) maps it.311        do { _ = try NotesStore(url: temp.url); Issue.record("should throw") } catch {312            #expect(NotesStore.openError(for: error, url: temp.url) == .newerSchema(found: 99, supported: 2))313        }314    }315316    @Test func damagedFileIsReportedAsCorruptAndLeftAsItIs() throws {317        let temp = TempDatabase()318        let garbage = Data((0..<8192).map { UInt8(truncatingIfNeeded: $0 &* 31 &+ 7) })319        try garbage.write(to: temp.url)320        let result = NotesStore.open(url: temp.url)321        guard case .failed(.corrupt) = result else { Issue.record("expected corrupt, got \(String(describing: result.error))"); return }322        #expect(result.error?.userMessage.contains("endommagée") == true)323        #expect(try Data(contentsOf: temp.url) == garbage)324    }325326    @Test func unreachableLocationIsAStorageError() {327        let url = URL(fileURLWithPath: "/nonexistent-\(UUID().uuidString)/notes.sqlite")328        let result = NotesStore.open(url: url)329        guard case .failed(.storage) = result else { Issue.record("expected storage, got \(String(describing: result.error))"); return }330        #expect(result.error?.isTransient == true)331    }332333    @Test func readOnlyConnectionNeitherMigratesNorBacksUp() throws {334        let temp = try v1Copy()335        let before = try dump(path: temp.url.path)336        let result = NotesStore.open(url: temp.url, readOnly: true)337        #expect(result.error == .outdatedReadOnly(found: 1, required: 2))338        #expect(userVersion(temp.url.path) == 1)339        #expect(try dump(path: temp.url.path) == before)340        #expect(StoreBackup.existingBackups(of: temp.url).isEmpty)341    }342343    @Test func downMigrationRestoresTheExactV1Schema() async throws {344        let temp = try v1Copy()345        let original = try Database(path: temp.url.path, readOnly: false)346        let v1Schema = try schemaSQL(original)347        let before = try dump(original)348        original.close()349        let store = try NotesStore(url: temp.url)350        _ = try await store.saveLocalEntity(kind: .task, id: "t", json: Data("{}".utf8), op: EntityOpVerb.create)351        await store.close()352        let raw = try Database(path: temp.url.path, readOnly: false)353        try Schema.v2Down(raw)354        #expect(try raw.scalar("PRAGMA user_version") == 1)355        #expect(try schemaSQL(raw) == v1Schema)356        #expect(try dump(raw) == before)357        #expect(throws: StoreError.self) { try Schema.v2Down(raw) }   // only from version 2358        raw.close()359        let again = try NotesStore(url: temp.url)360        #expect(try await again.debugValue("PRAGMA user_version") == "2")361        #expect(try await again.entityCounts().isEmpty)362    }363364    @Test func wipeAllAlsoEmptiesTheV2Tables() async throws {365        let store = try NotesStore.inMemory()366        _ = try await store.saveLocalEntity(kind: .task, id: "t", json: Data("{}".utf8), op: EntityOpVerb.create)367        _ = try await store.saveCanvas(pageId: "p", json: Data("{}".utf8))368        try await store.setCached(CacheKey.brief, json: Data("{}".utf8))369        try await store.cacheEvents([EventRecord(id: "e", at: epoch, kind: "page.created")])370        try await store.setEntityCursor(kind: .task, 9)371        let op = try await store.enqueueOp(kind: "x")372        try await store.parkOp(id: op.id, reason: "r")373        try await store.wipeAll()374        for table in Schema.v2Tables { #expect(try await store.debugValue("SELECT count(*) FROM \(table)") == "0", "\(table)") }375    }376}377
Files main
Menu

Theme