Repository Public
1// SP Notes Core2// File: Sources/SPNotesStore/Schema.swift3// Description: Database schema and migrations, versioned by PRAGMA user_version (one transaction per step).4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai7import Foundation89enum Schema {10 typealias Migration = @Sendable (Database) throws -> Void1112 /// Migration `n` (0-based) brings the file from user_version `n` to `n + 1`. Never edit a shipped step: append.13 static let migrations: [Migration] = [v1, v2]1415 static var currentVersion: Int { migrations.count }1617 /// Applies the missing steps. Each step and its version bump commit together or not at all.18 static func migrate(_ db: Database, steps: [Migration] = migrations) throws {19 let version = Int(try db.scalar("PRAGMA user_version"))20 if version > steps.count {21 throw StoreError.schema("database version \(version) is newer than this build supports (\(steps.count))")22 }23 if db.readOnly {24 if version < steps.count {25 throw StoreError.schema("database version \(version) is older than \(steps.count) and this connection is read-only")26 }27 return28 }29 guard version < steps.count else { return }30 for index in version..<steps.count {31 try db.transaction {32 // Another process may have migrated while this one waited for the write lock.33 guard Int(try db.scalar("PRAGMA user_version")) == index else { return }34 try steps[index](db)35 try db.exec("PRAGMA user_version=\(index + 1)")36 }37 }38 }3940 /// The condition under which a page has something to push. Must stay textually identical to the partial41 /// index `pages_dirty` so that the planner uses it.42 static let dirtyPredicate = "(needs_create=1 OR dirty_content=1 OR dirty_meta<>'' OR tags_dirty=1)"4344 @Sendable private static func v1(_ db: Database) throws {45 try db.exec("""46 CREATE TABLE pages(47 rid INTEGER PRIMARY KEY AUTOINCREMENT,48 id TEXT NOT NULL UNIQUE,49 parent_id TEXT,50 database_id TEXT,51 type TEXT NOT NULL DEFAULT 'page',52 section TEXT NOT NULL DEFAULT 'notes',53 title TEXT NOT NULL DEFAULT '',54 icon TEXT,55 cover TEXT,56 content BLOB NOT NULL,57 properties BLOB NOT NULL,58 position REAL NOT NULL DEFAULT 0,59 pinned INTEGER NOT NULL DEFAULT 0,60 archived INTEGER NOT NULL DEFAULT 0,61 server_version INTEGER NOT NULL DEFAULT 0,62 word_count INTEGER NOT NULL DEFAULT 0,63 created_at INTEGER NOT NULL,64 updated_at INTEGER NOT NULL,65 last_opened_at INTEGER,66 deleted_at INTEGER,67 inbox_at INTEGER,68 capture_kind TEXT,69 rev INTEGER NOT NULL DEFAULT 0,70 needs_create INTEGER NOT NULL DEFAULT 0,71 dirty_content INTEGER NOT NULL DEFAULT 0,72 dirty_meta TEXT NOT NULL DEFAULT '',73 tags_dirty INTEGER NOT NULL DEFAULT 0,74 edit_seq INTEGER NOT NULL DEFAULT 0,75 sync_state TEXT NOT NULL DEFAULT 'synced',76 sync_error TEXT,77 plain_text TEXT NOT NULL DEFAULT '',78 preview TEXT NOT NULL DEFAULT '',79 favorite INTEGER NOT NULL DEFAULT 0,80 is_folder INTEGER NOT NULL DEFAULT 0,81 local_updated_at INTEGER NOT NULL82 );83 CREATE INDEX pages_parent ON pages(parent_id, position);84 CREATE INDEX pages_live_updated ON pages(updated_at DESC, rid DESC) WHERE deleted_at IS NULL AND archived=0;85 CREATE INDEX pages_live_recent ON pages(max(updated_at, coalesce(last_opened_at, 0)) DESC, rid DESC) WHERE deleted_at IS NULL AND archived=0;86 CREATE INDEX pages_pinned ON pages(updated_at DESC, rid DESC) WHERE pinned=1;87 CREATE INDEX pages_favorite ON pages(updated_at DESC, rid DESC) WHERE favorite=1;88 CREATE INDEX pages_inbox ON pages(inbox_at DESC, rid DESC) WHERE inbox_at IS NOT NULL;89 CREATE INDEX pages_archived ON pages(updated_at DESC, rid DESC) WHERE archived=1;90 CREATE INDEX pages_trash ON pages(deleted_at DESC, rid DESC) WHERE deleted_at IS NOT NULL;91 CREATE INDEX pages_folders ON pages(section, position) WHERE is_folder=1;92 CREATE INDEX pages_section_root ON pages(section, position) WHERE parent_id IS NULL;93 CREATE INDEX pages_dirty ON pages(local_updated_at) WHERE \(dirtyPredicate);94 CREATE INDEX pages_unsynced_state ON pages(sync_state) WHERE sync_state<>'synced';9596 CREATE TABLE page_bases(97 page_id TEXT PRIMARY KEY,98 title TEXT NOT NULL,99 content BLOB NOT NULL,100 properties BLOB NOT NULL,101 version INTEGER NOT NULL102 ) WITHOUT ROWID;103104 CREATE TABLE tags(105 name TEXT PRIMARY KEY,106 color TEXT107 ) WITHOUT ROWID;108109 CREATE TABLE page_tags(110 page_rid INTEGER NOT NULL REFERENCES pages(rid) ON DELETE CASCADE,111 name TEXT NOT NULL,112 PRIMARY KEY(page_rid, name)113 ) WITHOUT ROWID;114 CREATE INDEX page_tags_name ON page_tags(name, page_rid);115116 CREATE VIRTUAL TABLE pages_fts USING fts5(title, body, tags, tokenize='unicode61 remove_diacritics 2');117118 CREATE TABLE attachments(119 id TEXT PRIMARY KEY,120 page_id TEXT,121 filename TEXT NOT NULL,122 mime TEXT NOT NULL,123 size INTEGER NOT NULL DEFAULT 0,124 sha256 TEXT,125 page_count INTEGER,126 created_at INTEGER NOT NULL,127 deleted_at INTEGER,128 rev INTEGER NOT NULL DEFAULT 0,129 state TEXT NOT NULL DEFAULT 'remote',130 local_path TEXT,131 keep_offline INTEGER NOT NULL DEFAULT 0,132 uploaded INTEGER NOT NULL DEFAULT 1,133 upload_attempts INTEGER NOT NULL DEFAULT 0,134 last_error TEXT,135 last_accessed_at INTEGER136 );137 CREATE INDEX attachments_page ON attachments(page_id, created_at);138 CREATE INDEX attachments_not_uploaded ON attachments(created_at) WHERE uploaded=0;139 CREATE INDEX attachments_local ON attachments(last_accessed_at) WHERE local_path IS NOT NULL;140141 CREATE TABLE ops(142 seq INTEGER PRIMARY KEY AUTOINCREMENT,143 kind TEXT NOT NULL,144 target_id TEXT,145 payload BLOB,146 idem_key TEXT NOT NULL UNIQUE,147 created_at INTEGER NOT NULL,148 attempts INTEGER NOT NULL DEFAULT 0,149 next_attempt_at INTEGER NOT NULL DEFAULT 0,150 leased_until INTEGER NOT NULL DEFAULT 0,151 handed_out INTEGER NOT NULL DEFAULT 0,152 coalescable INTEGER NOT NULL DEFAULT 0,153 last_error TEXT154 );155 CREATE INDEX ops_target ON ops(target_id, seq);156157 CREATE TABLE conflicts(158 page_id TEXT PRIMARY KEY,159 version INTEGER NOT NULL,160 title TEXT NOT NULL,161 content BLOB NOT NULL,162 properties BLOB NOT NULL,163 updated_at INTEGER NOT NULL,164 detected_at INTEGER NOT NULL165 ) WITHOUT ROWID;166167 CREATE TABLE local_versions(168 seq INTEGER PRIMARY KEY AUTOINCREMENT,169 id TEXT NOT NULL UNIQUE,170 page_id TEXT NOT NULL,171 created_at INTEGER NOT NULL,172 title TEXT NOT NULL,173 content BLOB NOT NULL,174 word_count INTEGER NOT NULL DEFAULT 0,175 reason TEXT NOT NULL DEFAULT '',176 plain_text TEXT NOT NULL DEFAULT '',177 preview TEXT NOT NULL DEFAULT ''178 );179 CREATE INDEX local_versions_page ON local_versions(page_id, seq DESC);180181 CREATE TABLE reminders(182 id TEXT PRIMARY KEY,183 page_id TEXT,184 title TEXT NOT NULL DEFAULT '',185 due_at INTEGER,186 done INTEGER NOT NULL DEFAULT 0,187 server_task_id TEXT,188 dirty INTEGER NOT NULL DEFAULT 0,189 deleted INTEGER NOT NULL DEFAULT 0,190 edit_seq INTEGER NOT NULL DEFAULT 0,191 created_at INTEGER NOT NULL,192 updated_at INTEGER NOT NULL193 );194 CREATE INDEX reminders_page ON reminders(page_id);195 CREATE INDEX reminders_due ON reminders(due_at) WHERE deleted=0 AND done=0 AND due_at IS NOT NULL;196 CREATE INDEX reminders_dirty ON reminders(updated_at) WHERE dirty=1;197198 CREATE TABLE meta(199 key TEXT PRIMARY KEY,200 value TEXT NOT NULL201 ) WITHOUT ROWID;202 """)203 }204205 /// Schema v2 (SPNotesCore 2.0) — purely additive: seven new tables, no shipped table or index is altered.206 /// Hand-written inverse (documented in docs/MIGRATION-V2.md): `v2Down`.207 @Sendable static func v2(_ db: Database) throws {208 try db.exec("""209 CREATE TABLE entities(210 kind TEXT NOT NULL,211 id TEXT NOT NULL,212 rev INTEGER NOT NULL DEFAULT 0,213 json BLOB NOT NULL,214 deleted INTEGER NOT NULL DEFAULT 0,215 updated_at INTEGER NOT NULL,216 scope TEXT,217 ref TEXT,218 sort_key,219 dirty INTEGER NOT NULL DEFAULT 0,220 remote_json BLOB,221 PRIMARY KEY(kind, id)222 );223 CREATE INDEX entities_live ON entities(kind, updated_at DESC) WHERE deleted=0;224 CREATE INDEX entities_scope ON entities(kind, scope, sort_key) WHERE deleted=0;225 CREATE INDEX entities_ref ON entities(kind, ref) WHERE deleted=0 AND ref IS NOT NULL;226 CREATE INDEX entities_rev ON entities(kind, rev);227 CREATE INDEX entities_dirty ON entities(kind, id) WHERE dirty=1;228229 CREATE TABLE entity_ops(230 id INTEGER PRIMARY KEY AUTOINCREMENT,231 kind TEXT NOT NULL,232 entity_id TEXT NOT NULL,233 op TEXT NOT NULL,234 payload BLOB,235 base_version INTEGER,236 idem_key TEXT NOT NULL UNIQUE,237 created_at INTEGER NOT NULL,238 attempts INTEGER NOT NULL DEFAULT 0,239 next_attempt_at INTEGER NOT NULL DEFAULT 0,240 last_error TEXT,241 state TEXT NOT NULL DEFAULT 'pending'242 );243 CREATE INDEX entity_ops_entity ON entity_ops(kind, entity_id, id);244 CREATE INDEX entity_ops_blocked ON entity_ops(state, id) WHERE state<>'pending';245246 CREATE TABLE ops_parked(247 seq INTEGER PRIMARY KEY,248 reason TEXT NOT NULL,249 parked_at INTEGER NOT NULL250 );251252 CREATE TABLE canvases(253 page_id TEXT PRIMARY KEY,254 json BLOB NOT NULL,255 version INTEGER NOT NULL DEFAULT 0,256 base_json BLOB,257 dirty INTEGER NOT NULL DEFAULT 0,258 rev INTEGER NOT NULL DEFAULT 0,259 edit_seq INTEGER NOT NULL DEFAULT 0,260 conflict_json BLOB,261 conflict_version INTEGER,262 sync_error TEXT,263 updated_at INTEGER NOT NULL264 );265 CREATE INDEX canvases_dirty ON canvases(updated_at) WHERE dirty=1;266267 CREATE TABLE events(268 id TEXT PRIMARY KEY,269 at INTEGER NOT NULL,270 kind TEXT NOT NULL,271 page_id TEXT,272 entity_kind TEXT,273 entity_id TEXT,274 actor TEXT NOT NULL DEFAULT 'user',275 device TEXT,276 summary TEXT NOT NULL DEFAULT '',277 data BLOB,278 fetched_at INTEGER NOT NULL279 );280 CREATE INDEX events_at ON events(at DESC, id);281 CREATE INDEX events_page ON events(page_id, at DESC) WHERE page_id IS NOT NULL;282 CREATE INDEX events_kind ON events(kind, at DESC);283284 CREATE TABLE kv_cache(285 key TEXT PRIMARY KEY,286 json BLOB NOT NULL,287 fetched_at INTEGER NOT NULL,288 etag TEXT289 ) WITHOUT ROWID;290291 CREATE TABLE sync_cursors(292 kind TEXT PRIMARY KEY,293 cursor INTEGER NOT NULL DEFAULT 0,294 updated_at INTEGER NOT NULL295 ) WITHOUT ROWID;296 """)297 }298299 /// The tables v2 created, in an order that is safe to drop.300 static let v2Tables = ["sync_cursors", "kv_cache", "events", "canvases", "ops_parked", "entity_ops", "entities"]301302 /// Hand-written inverse of `v2` (never run by the apps; kept for support and tested): drops the seven v2 tables303 /// with their indexes and sets `user_version` back to 1. The v1 tables are untouched in both directions.304 static func v2Down(_ db: Database) throws {305 try db.transaction {306 guard Int(try db.scalar("PRAGMA user_version")) == 2 else { throw StoreError.schema("v2Down needs a version 2 database") }307 for table in v2Tables { try db.exec("DROP TABLE IF EXISTS \(table)") }308 try db.exec("PRAGMA user_version=1")309 }310 }311}312