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