SPB Git

Repository Public

Schema.swift 12.7 KB 312 lines swift

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

Theme