Local database: migration from schema v1 to v2
Schema v2 ships with SPNotesCore 2.0. It is the first migration ever run on users' files (1.x created v1 and never changed it). This page says what it does, how it is protected, how it was tested, and what the apps must do.
1. What changes
| v1 → v2 | |
|---|---|
v1 tables (pages, page_bases, tags, page_tags, pages_fts, attachments, ops, conflicts, local_versions, reminders, meta) |
untouched: no column added, no index changed, no row rewritten |
| New tables | entities, entity_ops, ops_parked, canvases, events, kv_cache, sync_cursors (with their indexes), all empty |
PRAGMA user_version |
1 → 2 |
The step is Schema.v2 (Sources/SPNotesStore/Schema.swift). It is additive only, so it cannot lose data by construction;
the tests below check it anyway. Roles of the tables: see STORE_API.md §12.
2. How it runs
NotesStore.init(url:)/NotesStore.open(url:)opens the file and readsuser_version.- Backup. When the file holds data of an older version (0 < version < 2) a consistent copy is written next to it as
<file>.backup-v<old version>(for the apps:notes.sqlite.backup-v1) withVACUUM INTO— one read transaction, so the copy includes what is still in the write-ahead log and is itself a complete, healthy v1 database. It is written to…backup-v1.partialand renamed, so a half-written copy never has the final name. If a backup of that version already exists (an earlier attempt failed after it), it is kept: it is the pristine one. If the copy cannot be written (StoreError.backup→StoreOpenError.backupFailed), the migration is not attempted and the file is unchanged. - Migration. The DDL and the version bump run in one
BEGIN IMMEDIATEtransaction. Any error rolls everything back: the file is exactly the v1 file it was. Two processes opening at the same time are serialized by the write lock; the second one sees version 2 and does nothing. - Removal of the backup. A later open that finds the file already at the current version, passes
PRAGMA quick_check, and finds the backup older thanbackupRetention(600 s by default) deletes it. The retention exists because a widget or a share extension may open the file seconds after the app migrated it: that is not "the next launch". A read-only connection never deletes it.openReportsays what happened (migratedFrom,backupURL,removedBackups).
A read-only connection (widget) neither migrates nor backs up: on a v1 file it fails with StoreError.schema /
StoreOpenError.outdatedReadOnly until the app has been opened once — as in 1.x for any future version.
3. Failure modes and what the user sees
| Situation | Result | File |
|---|---|---|
| Disk full while writing the backup | backupFailed (transient) |
unchanged, v1 |
| Error inside the migration (disk full, I/O, unexpected object) | migrationFailed(from: 1, to: 2, reason, backupURL) |
rolled back to v1; backup kept |
| File written by a newer app | newerSchema(found:supported:) |
unchanged; no backup made |
| Damaged file | corrupt |
unchanged |
| Locked by another process for more than 8 s | locked (transient) |
unchanged |
StoreOpenError.userMessage is the French text to show. Apps must show it in a blocking screen and must not fall back to
NotesStore.inMemory() (the macOS 1.0.2 behaviour: the user sees an empty library and loses what is typed). Adopt
NotesStore.open(url:), or keep init(url:) and map the thrown error with NotesStore.openError(for:url:).
4. Going back
-
A 1.x app cannot open a v2 file: it refuses any file newer than its own schema (by design, since 1.0). Do not ship a build with Core 2.0 to users who may go back to a 1.x build without knowing this; TestFlight users who downgrade must restore.
-
Hand-written inverse.
Schema.v2Down(tested) is:sqlBEGIN IMMEDIATE; DROP TABLE IF EXISTS sync_cursors; DROP TABLE IF EXISTS kv_cache; DROP TABLE IF EXISTS events; DROP TABLE IF EXISTS canvases; DROP TABLE IF EXISTS ops_parked; DROP TABLE IF EXISTS entity_ops; DROP TABLE IF EXISTS entities; PRAGMA user_version = 1; COMMIT;It restores exactly the v1 schema and leaves every v1 row as it is. It discards what only v2 holds: unsent entity operations, offline canvas edits, cached events and read models, parked-op markers (the parked ops themselves stay in
ops, where a 1.x build would then drop them — the defect 2.0 fixes). It is a support tool; no app calls it. -
Restoring the backup: quit the app, replace
notes.sqlitebynotes.sqlite.backup-v1(deletenotes.sqlite-walandnotes.sqlite-shm), reopen with the 1.x build. Everything written after the migration is lost, except what was synchronized.
5. How it was tested
Tests/SPNotesStoreTests/Fixtures/v1.sqlite is a real v1 database written by the shipped 1.0 code path (commit 267a673, before
any v2 code existed; generator kept in V1FixtureGenerator.swift): 6 pages (synced, dirty, never pushed, trashed, in conflict),
3 base copies, tags, 2 attachments, 3 queued ops including one of a kind no build knows, a conflict copy, a local version, a
reminder, meta and the sync cursor. MigrationV2Tests (17 tests) checks on copies of it:
- every row of every v1 table and of the full-text index is identical before and after; typed reads through the 2.0 API agree;
- the seven new tables exist and are empty; a fresh database and a migrated one have the same schema;
- opening three times, and running the migrator again, changes neither schema nor rows (idempotent);
- a failure after the whole v2 DDL plus writes into old and new tables, and a failure in the middle of a DDL script, both leave version 1, the v1 schema and every row intact; the real migration then succeeds on the same file;
- a real failure (a conflicting table) is reported as
migrationFailedwith the backup, and leaves a v1 file; - the backup is a healthy v1 database with the pre-migration rows, includes writes that were only in the WAL, is kept by an open within the retention and by read-only opens, and is removed by a later healthy open;
- newer file, damaged file, unreachable location, read-only connection: typed error, file untouched;
v2Downrestores the exact v1 schema and rows; the file migrates again afterwards.
Not tested: a real power loss or a full disk during the migration (the transaction guarantees are SQLite's), and the migration of a multi-gigabyte database (the step itself only creates empty tables; the backup copy is proportional to the file size).