SPB Git

Repository Public

CacheStore.swift 14.0 KB 235 lines swift

1// Author: Simon-Pierre Boucher — contact@spboucher.ai2// File: SPChat/Core/Persistence/CacheStore.swift3// Description: Local SQLite cache (conversations, message trees, catalogues, full-text index) that makes history readable offline and startup instant.4import Foundation5import SQLite367public struct CacheError: Error, CustomStringConvertible, Sendable { public let description: String }89private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)1011/// Thin, serialised SQLite connection. All access goes through the `CacheStore` actor.12final class SQLiteDatabase {13    private var db: OpaquePointer?1415    init(path: String) throws {16        let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX17        guard sqlite3_open_v2(path, &db, flags, nil) == SQLITE_OK else {18            let msg = db.map { String(cString: sqlite3_errmsg($0)) } ?? "open failed"; sqlite3_close(db)19            throw CacheError(description: "SQLite: \(msg)")20        }21        sqlite3_busy_timeout(db, 3000)22    }23    deinit { sqlite3_close(db) }2425    enum Value { case text(String), int(Int64), double(Double), blob(Data), null }2627    func exec(_ sql: String) throws {28        var err: UnsafeMutablePointer<CChar>?29        guard sqlite3_exec(db, sql, nil, nil, &err) == SQLITE_OK else {30            let msg = err.map { String(cString: $0) } ?? "exec failed"; sqlite3_free(err)31            throw CacheError(description: "SQLite: \(msg)")32        }33    }3435    @discardableResult36    func run(_ sql: String, _ params: [Value] = []) throws -> [[Value]] {37        var stmt: OpaquePointer?38        guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK else { throw CacheError(description: "SQLite: \(String(cString: sqlite3_errmsg(db)))") }39        defer { sqlite3_finalize(stmt) }40        for (i, p) in params.enumerated() {41            let idx = Int32(i + 1)42            switch p {43            case .text(let s): sqlite3_bind_text(stmt, idx, s, -1, SQLITE_TRANSIENT)44            case .int(let n): sqlite3_bind_int64(stmt, idx, n)45            case .double(let d): sqlite3_bind_double(stmt, idx, d)46            case .blob(let d): _ = d.withUnsafeBytes { sqlite3_bind_blob(stmt, idx, $0.baseAddress, Int32(d.count), SQLITE_TRANSIENT) }47            case .null: sqlite3_bind_null(stmt, idx)48            }49        }50        var rows: [[Value]] = []51        while true {52            let rc = sqlite3_step(stmt)53            if rc == SQLITE_DONE { break }54            guard rc == SQLITE_ROW else { throw CacheError(description: "SQLite: \(String(cString: sqlite3_errmsg(db)))") }55            var row: [Value] = []56            for c in 0..<sqlite3_column_count(stmt) {57                switch sqlite3_column_type(stmt, c) {58                case SQLITE_INTEGER: row.append(.int(sqlite3_column_int64(stmt, c)))59                case SQLITE_FLOAT: row.append(.double(sqlite3_column_double(stmt, c)))60                case SQLITE_TEXT: row.append(.text(String(cString: sqlite3_column_text(stmt, c))))61                case SQLITE_BLOB:62                    let n = Int(sqlite3_column_bytes(stmt, c))63                    row.append(.blob(n > 0 ? Data(bytes: sqlite3_column_blob(stmt, c), count: n) : Data()))64                default: row.append(.null)65                }66            }67            rows.append(row)68        }69        return rows70    }71}7273private extension SQLiteDatabase.Value {74    var string: String? { if case .text(let s) = self { return s }; return nil }75    var data: Data? { if case .blob(let d) = self { return d }; if case .text(let s) = self { return Data(s.utf8) }; return nil }76    var int: Int? { if case .int(let n) = self { return Int(n) }; return nil }77}7879/// Offline cache. The server stays the source of truth: every successful fetch overwrites the corresponding rows, and80/// signing out wipes the file's content. Stored inside the app's sandbox container.81public actor CacheStore {82    private let db: SQLiteDatabase83    private static let schemaVersion = 184    private let encoder = JSONEncoder.api()85    private let decoder = JSONDecoder.api()8687    /// `url == nil` → in-memory database (tests, previews).88    public init(url: URL?) throws {89        if let url { try FileManager.default.createDirectory(at: url.deletingLastPathComponent(), withIntermediateDirectories: true) }90        db = try SQLiteDatabase(path: url?.path ?? ":memory:")91        try db.exec("PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA foreign_keys=ON; PRAGMA secure_delete=ON;")92        let version = try db.run("PRAGMA user_version").first?.first?.int ?? 093        if version != Self.schemaVersion {94            // a cache can always be rebuilt from the server: on a schema change it is simply recreated95            try db.exec("DROP TABLE IF EXISTS conversations; DROP TABLE IF EXISTS details; DROP TABLE IF EXISTS messages; DROP TABLE IF EXISTS kv; DROP TABLE IF EXISTS message_fts;")96            try db.exec("""97            CREATE TABLE conversations (id TEXT PRIMARY KEY, json BLOB NOT NULL, title TEXT NOT NULL, pinned INTEGER NOT NULL, favorite INTEGER NOT NULL, archived INTEGER NOT NULL, project_id TEXT, sort_ts REAL NOT NULL);98            CREATE INDEX conversations_sort ON conversations (archived, pinned DESC, sort_ts DESC);99            CREATE TABLE details (conversation_id TEXT PRIMARY KEY, json BLOB NOT NULL, fetched_at REAL NOT NULL);100            CREATE TABLE messages (id TEXT PRIMARY KEY, conversation_id TEXT NOT NULL, role TEXT NOT NULL, active INTEGER NOT NULL, created_at REAL NOT NULL, text TEXT NOT NULL);101            CREATE INDEX messages_conv ON messages (conversation_id, created_at);102            CREATE VIRTUAL TABLE message_fts USING fts5(text, message_id UNINDEXED, conversation_id UNINDEXED, tokenize = 'unicode61 remove_diacritics 2');103            CREATE TABLE kv (key TEXT PRIMARY KEY, value BLOB NOT NULL, updated_at REAL NOT NULL);104            PRAGMA user_version = \(Self.schemaVersion);105            """)106        }107    }108109    // MARK: Conversations110111    /// Replaces the cached list for one filter with what the server returned (rows the server no longer lists are removed).112    public func replaceConversations(_ list: [Conversation], archived: Bool) throws {113        try db.exec("BEGIN")114        do {115            let ids = Set(list.map(\.id))116            for row in try db.run("SELECT id FROM conversations WHERE archived = ?", [.int(archived ? 1 : 0)]) {117                if let id = row[0].string, !ids.contains(id) { try removeConversation(id) }118            }119            for c in list { try upsert(c) }120            try db.exec("COMMIT")121        } catch { try? db.exec("ROLLBACK"); throw error }122    }123124    public func upsertConversation(_ c: Conversation) throws { try upsert(c) }125126    private func upsert(_ c: Conversation) throws {127        try db.run("INSERT INTO conversations (id, json, title, pinned, favorite, archived, project_id, sort_ts) VALUES (?,?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET json=excluded.json, title=excluded.title, pinned=excluded.pinned, favorite=excluded.favorite, archived=excluded.archived, project_id=excluded.project_id, sort_ts=excluded.sort_ts",128                   [.text(c.id), .blob(try encoder.encode(c)), .text(c.title), .int(c.pinned ? 1 : 0), .int(c.favorite ? 1 : 0), .int(c.isArchived ? 1 : 0), c.projectId.map { .text($0) } ?? .null, .double(c.sortDate.timeIntervalSince1970)])129    }130131    public func conversations(filter: ConversationFilter = .all, query: String? = nil, limit: Int = 500) throws -> [Conversation] {132        var sql = "SELECT json FROM conversations WHERE archived = ?"; var params: [SQLiteDatabase.Value] = [.int(filter == .archived ? 1 : 0)]133        if filter == .favorites { sql += " AND favorite = 1" }134        if filter == .pinned { sql += " AND pinned = 1" }135        if let q = query?.trimmingCharacters(in: .whitespaces), !q.isEmpty { sql += " AND title LIKE ? ESCAPE '\\'"; params.append(.text("%" + Self.likeEscape(q) + "%")) }136        sql += " ORDER BY pinned DESC, sort_ts DESC LIMIT ?"; params.append(.int(Int64(limit)))137        return try db.run(sql, params).compactMap { $0[0].data.flatMap { try? decoder.decode(Conversation.self, from: $0) } }138    }139140    public func conversation(_ id: String) throws -> Conversation? {141        try db.run("SELECT json FROM conversations WHERE id = ?", [.text(id)]).first?[0].data.flatMap { try? decoder.decode(Conversation.self, from: $0) }142    }143144    public func deleteConversation(_ id: String) throws { try removeConversation(id) }145146    private func removeConversation(_ id: String) throws {147        try db.run("DELETE FROM conversations WHERE id = ?", [.text(id)])148        try db.run("DELETE FROM details WHERE conversation_id = ?", [.text(id)])149        try db.run("DELETE FROM messages WHERE conversation_id = ?", [.text(id)])150        try db.run("DELETE FROM message_fts WHERE conversation_id = ?", [.text(id)])151    }152153    // MARK: Conversation details (message tree)154155    /// Stores the full tree and refreshes the offline full-text index for the active thread.156    public func saveDetail(_ detail: ConversationDetail) throws {157        var d = detail; d.liveRun = nil158        let id = d.conversation.id159        try db.exec("BEGIN")160        do {161            try upsert(d.conversation)162            try db.run("INSERT INTO details (conversation_id, json, fetched_at) VALUES (?,?,?) ON CONFLICT(conversation_id) DO UPDATE SET json=excluded.json, fetched_at=excluded.fetched_at",163                       [.text(id), .blob(try encoder.encode(d)), .double(Date().timeIntervalSince1970)])164            try db.run("DELETE FROM messages WHERE conversation_id = ?", [.text(id)])165            try db.run("DELETE FROM message_fts WHERE conversation_id = ?", [.text(id)])166            for m in d.messages {167                let text = m.visibleText168                try db.run("INSERT OR REPLACE INTO messages (id, conversation_id, role, active, created_at, text) VALUES (?,?,?,?,?,?)",169                           [.text(m.id), .text(id), .text(m.role.rawValue), .int(m.active ? 1 : 0), .double(m.createdAt.timeIntervalSince1970), .text(text)])170                if m.active, !text.isEmpty { try db.run("INSERT INTO message_fts (text, message_id, conversation_id) VALUES (?,?,?)", [.text(text), .text(m.id), .text(id)]) }171            }172            try db.exec("COMMIT")173        } catch { try? db.exec("ROLLBACK"); throw error }174    }175176    public func detail(_ conversationId: String) throws -> (detail: ConversationDetail, fetchedAt: Date)? {177        guard let row = try db.run("SELECT json, fetched_at FROM details WHERE conversation_id = ?", [.text(conversationId)]).first,178              let data = row[0].data, let d = try? decoder.decode(ConversationDetail.self, from: data) else { return nil }179        var at = Date.distantPast; if case .double(let t) = row[1] { at = Date(timeIntervalSince1970: t) }180        return (d, at)181    }182183    public func cachedDetailIds() throws -> Set<String> { Set(try db.run("SELECT conversation_id FROM details").compactMap { $0[0].string }) }184185    // MARK: Offline search186187    /// Full-text search over cached conversations (titles and active messages). Used when the server cannot be reached.188    public func search(_ query: String, limit: Int = 40) throws -> [SearchHit] {189        let terms = query.split(whereSeparator: { !$0.isLetter && !$0.isNumber }).map(String.init).filter { !$0.isEmpty }190        guard !terms.isEmpty else { return [] }191        let match = terms.map { "\"\($0)\"*" }.joined(separator: " ")192        let rows = try db.run("""193            SELECT f.message_id, f.conversation_id, m.role, m.created_at, c.title, snippet(message_fts, 0, '<b>', '</b>', '…', 18)194            FROM message_fts f JOIN messages m ON m.id = f.message_id JOIN conversations c ON c.id = f.conversation_id195            WHERE message_fts MATCH ? ORDER BY rank LIMIT ?196            """, [.text(match), .int(Int64(limit))])197        var hits = rows.compactMap { r -> SearchHit? in198            guard let id = r[0].string, let cid = r[1].string else { return nil }199            var date: Date?; if case .double(let t) = r[3] { date = Date(timeIntervalSince1970: t) }200            return SearchHit(id: id, conversationId: cid, role: r[2].string ?? "assistant", title: r[4].string ?? "", snippet: r[5].string ?? "", createdAt: date)201        }202        let seen = Set(hits.map(\.conversationId))203        for c in try conversations(filter: .all, query: query, limit: 20) where !seen.contains(c.id) {204            hits.append(SearchHit(id: "title:" + c.id, conversationId: c.id, role: "title", title: c.title, snippet: c.title, createdAt: c.sortDate))205        }206        return hits207    }208209    // MARK: Key / value (catalogues: models, presets, settings…)210211    public func set<T: Encodable>(_ value: T, for key: String) throws {212        try db.run("INSERT INTO kv (key, value, updated_at) VALUES (?,?,?) ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at",213                   [.text(key), .blob(try encoder.encode(value)), .double(Date().timeIntervalSince1970)])214    }215    public func get<T: Decodable>(_ type: T.Type, for key: String) throws -> T? {216        try db.run("SELECT value FROM kv WHERE key = ?", [.text(key)]).first?[0].data.flatMap { try? decoder.decode(T.self, from: $0) }217    }218    public func remove(key: String) throws { try db.run("DELETE FROM kv WHERE key = ?", [.text(key)]) }219220    // MARK: Maintenance221222    /// Removes every cached row (sign-out, or complete data deletion). The file is compacted so nothing readable remains.223    public func wipe() throws {224        try db.exec("DELETE FROM conversations; DELETE FROM details; DELETE FROM messages; DELETE FROM message_fts; DELETE FROM kv;")225        try? db.exec("VACUUM"); _ = try? db.run("PRAGMA wal_checkpoint(TRUNCATE)")226    }227228    public func stats() throws -> (conversations: Int, details: Int, messages: Int) {229        func count(_ t: String) throws -> Int { try db.run("SELECT count(*) FROM \(t)").first?[0].int ?? 0 }230        return (try count("conversations"), try count("details"), try count("messages"))231    }232233    private static func likeEscape(_ s: String) -> String { s.replacingOccurrences(of: "\\", with: "\\\\").replacingOccurrences(of: "%", with: "\\%").replacingOccurrences(of: "_", with: "\\_") }234}235
Files main
Menu

Theme