SPB Git

Repository Public

ProfileDatabase.swift 3.7 KB 101 lines swift

1import Foundation23/// One SQLite file per profile holding history, bookmarks and notes.4/// Schema changes are append-only migrations tracked with `PRAGMA user_version`.5enum ProfileSchema {6    static let migrations: [[String]] = [7        // v1 — initial schema8        [9            """10            CREATE TABLE history (11                id INTEGER PRIMARY KEY AUTOINCREMENT,12                url TEXT NOT NULL,13                title TEXT NOT NULL DEFAULT '',14                visited_at REAL NOT NULL15            )16            """,17            "CREATE INDEX history_url ON history(url)",18            "CREATE INDEX history_time ON history(visited_at DESC)",19            """20            CREATE TABLE bookmarks (21                id TEXT PRIMARY KEY,22                parent_id TEXT REFERENCES bookmarks(id) ON DELETE CASCADE,23                is_folder INTEGER NOT NULL DEFAULT 0,24                title TEXT NOT NULL DEFAULT '',25                url TEXT,26                position INTEGER NOT NULL DEFAULT 0,27                created_at REAL NOT NULL28            )29            """,30            "CREATE INDEX bookmarks_parent ON bookmarks(parent_id, position)",31        ],32        // v2 — tags on bookmarks, notes attached to a URL33        [34            "ALTER TABLE bookmarks ADD COLUMN tags TEXT NOT NULL DEFAULT ''",35            """36            CREATE TABLE notes (37                url TEXT PRIMARY KEY,38                body TEXT NOT NULL,39                updated_at REAL NOT NULL40            )41            """,42        ],43    ]4445    /// Opens the profile database at `url` (or in memory when `nil`) and brings it to the current schema.46    static func open(at url: URL?) throws -> SQLiteDatabase {47        if let url {48            try FileManager.default.createDirectory(at: url.deletingLastPathComponent(), withIntermediateDirectories: true)49        }50        let db = try SQLiteDatabase(path: url?.path)51        try db.migrate(migrations)52        return db53    }54}5556struct PageNote: Equatable, Sendable {57    var url: String58    var body: String59    var updatedAt: Date60}6162/// Local notes attached to a page address. Never leaves the device.63final class NotesStore: Sendable {64    private let db: SQLiteDatabase65    init(db: SQLiteDatabase) { self.db = db }6667    /// Notes are keyed by the URL without its fragment so that in-page anchors share one note.68    static func key(for url: URL) -> String {69        var components = URLComponents(url: url, resolvingAgainstBaseURL: false)70        components?.fragment = nil71        return components?.string ?? url.absoluteString72    }7374    func note(for url: URL) -> PageNote? {75        let rows = (try? db.query("SELECT url, body, updated_at FROM notes WHERE url = ?", [.text(Self.key(for: url))])) ?? []76        return rows.first.map(Self.note)77    }7879    func save(_ body: String, for url: URL) {80        let key = Self.key(for: url)81        if body.trimmingCharacters(in: .whitespacesAndNewlines).isEmpty {82            _ = try? db.execute("DELETE FROM notes WHERE url = ?", [.text(key)])83        } else {84            _ = try? db.execute(85                "INSERT INTO notes(url, body, updated_at) VALUES(?,?,?) ON CONFLICT(url) DO UPDATE SET body = excluded.body, updated_at = excluded.updated_at",86                [.text(key), .text(body), .double(Date().timeIntervalSince1970)])87        }88    }8990    func all() -> [PageNote] {91        ((try? db.query("SELECT url, body, updated_at FROM notes ORDER BY updated_at DESC")) ?? []).map(Self.note)92    }9394    func deleteAll() { _ = try? db.execute("DELETE FROM notes") }9596    private static func note(_ row: [String: SQLValue]) -> PageNote {97        PageNote(url: row["url"]?.string ?? "", body: row["body"]?.string ?? "",98                 updatedAt: Date(timeIntervalSince1970: row["updated_at"]?.double ?? 0))99    }100}101
Files main
Menu

Theme