| 1 | import Foundation |
| 2 | import SQLite3 |
| 3 | |
| 4 | // 🤖 The local message store: every message decrypted ONCE, in the background, |
| 5 | // at ingest — then the UI reads plaintext rows with indexed queries and the |
| 6 | // main thread never touches crypto again. Also the foundation search stands on |
| 7 | // (FTS5 rides along from day one). |
| 8 | // |
| 9 | // E2E posture (deliberate, documented like the unfurl exception): the SERVER |
| 10 | // still only ever sees ciphertext — this database lives on the device, which |
| 11 | // already holds the keys in its Keychain. The file is OS-protected |
| 12 | // (complete-until-first-auth, so the store works after unlock exactly like the |
| 13 | // keys) and EXCLUDED FROM BACKUPS, so plaintext never leaves the device inside |
| 14 | // an iCloud/iTunes backup. |
| 15 | // |
| 16 | // An actor over a serial connection: all statements run off the main actor by |
| 17 | // construction. sqlite3 is compiled in on both platforms — the same engine the |
| 18 | // server itself runs on, fittingly. |
| 19 | public actor MessageStore { |
| 20 | public static let shared = MessageStore() |
| 21 | |
| 22 | public struct StoredMessage: Sendable { |
| 23 | public let id: Int64 |
| 24 | public let at: Int64 |
| 25 | public let edited: Bool |
| 26 | public let reactions: [Reaction] |
| 27 | public let payload: MessagePayload |
| 28 | |
| 29 | public init(id: Int64, at: Int64, edited: Bool, |
| 30 | reactions: [Reaction], payload: MessagePayload) { |
| 31 | self.id = id |
| 32 | self.at = at |
| 33 | self.edited = edited |
| 34 | self.reactions = reactions |
| 35 | self.payload = payload |
| 36 | } |
| 37 | } |
| 38 | |
| 39 | private var db: OpaquePointer? |
| 40 | // The suspension gate (see suspend()): while down, every op no-ops |
| 41 | // instead of lazily reopening — a reopen while backgrounded is the |
| 42 | // 0xdead10cc kill the close exists to prevent. |
| 43 | private var suspended = false |
| 44 | |
| 45 | // The App Group container, so the notification extension can ingest a |
| 46 | // pushed message into the SAME store the app reads (still on-device, |
| 47 | // OS-protected, never backed up). Falls back to Application Support where |
| 48 | // no group container exists (ad-hoc dev signing without the entitlement). |
| 49 | private static let appGroupID = "group.com.elevenmessenger.messenger" |
| 50 | |
| 51 | private func storeDir() -> URL { |
| 52 | let fm = FileManager.default |
| 53 | let fallback = (fm.urls(for: .applicationSupportDirectory, in: .userDomainMask).first |
| 54 | ?? URL(fileURLWithPath: NSTemporaryDirectory())) |
| 55 | .appendingPathComponent("lchat", isDirectory: true) |
| 56 | // Prefer the shared group container. On a sandboxed app that lacks the |
| 57 | // App Group entitlement, `containerURL` can still hand back a path the |
| 58 | // sandbox then refuses to create — so we don't trust the URL alone, |
| 59 | // we confirm the directory actually exists after trying to create it. |
| 60 | // If it doesn't, fall back to Application Support rather than let every |
| 61 | // store op fail on a dead handle (which reads as "search finds nothing" |
| 62 | // while the network-backed feed still renders — #1121). |
| 63 | if let group = fm.containerURL(forSecurityApplicationGroupIdentifier: Self.appGroupID) { |
| 64 | let dir = group.appendingPathComponent("lchat", isDirectory: true) |
| 65 | try? fm.createDirectory(at: dir, withIntermediateDirectories: true) |
| 66 | // Existence isn't proof either: an unentitled process (a dev test |
| 67 | // runner, say) can SEE another group's container on macOS 15+ but |
| 68 | // every write inside is denied — sqlite reports SQLITE_AUTH and |
| 69 | // the dead handle reads as "search finds nothing" again. Probe |
| 70 | // with a real write before trusting the path. |
| 71 | if fm.fileExists(atPath: dir.path), canWrite(in: dir) { |
| 72 | // One-time migration from the pre-group location: move the db |
| 73 | // (and its WAL sidecars) so existing history survives the path |
| 74 | // change. |
| 75 | let old = fallback.appendingPathComponent("messages.db") |
| 76 | let new = dir.appendingPathComponent("messages.db") |
| 77 | if fm.fileExists(atPath: old.path), !fm.fileExists(atPath: new.path) { |
| 78 | for suffix in ["", "-wal", "-shm"] { |
| 79 | try? fm.moveItem(at: URL(fileURLWithPath: old.path + suffix), |
| 80 | to: URL(fileURLWithPath: new.path + suffix)) |
| 81 | } |
| 82 | } |
| 83 | return dir |
| 84 | } |
| 85 | } |
| 86 | // No usable group container — make sure the fallback exists before we |
| 87 | // hand it back, so open() can create the db there. |
| 88 | try? fm.createDirectory(at: fallback, withIntermediateDirectories: true) |
| 89 | return fallback |
| 90 | } |
| 91 | |
| 92 | private func canWrite(in dir: URL) -> Bool { |
| 93 | let probe = dir.appendingPathComponent(".writable") |
| 94 | guard FileManager.default.createFile(atPath: probe.path, contents: nil) else { |
| 95 | return false |
| 96 | } |
| 97 | try? FileManager.default.removeItem(at: probe) |
| 98 | return true |
| 99 | } |
| 100 | |
| 101 | private func open() -> OpaquePointer? { |
| 102 | if suspended { return nil } |
| 103 | if let db { return db } |
| 104 | let dir = storeDir() |
| 105 | try? FileManager.default.createDirectory(at: dir, withIntermediateDirectories: true) |
| 106 | var url = dir.appendingPathComponent("messages.db") |
| 107 | var handle: OpaquePointer? |
| 108 | guard sqlite3_open_v2(url.path, &handle, |
| 109 | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, |
| 110 | nil) == SQLITE_OK, let handle else { return nil } |
| 111 | exec(handle, "PRAGMA journal_mode=WAL") |
| 112 | exec(handle, "PRAGMA synchronous=NORMAL") |
| 113 | // Two processes share this file now (app + NSE); wait out the other's |
| 114 | // write instead of failing with SQLITE_BUSY. |
| 115 | exec(handle, "PRAGMA busy_timeout=3000") |
| 116 | exec(handle, """ |
| 117 | CREATE TABLE IF NOT EXISTS messages( |
| 118 | instance TEXT NOT NULL, |
| 119 | thread TEXT NOT NULL, |
| 120 | id INTEGER NOT NULL, |
| 121 | at INTEGER NOT NULL, |
| 122 | edited INTEGER NOT NULL DEFAULT 0, |
| 123 | reactions TEXT NOT NULL DEFAULT '[]', |
| 124 | payload TEXT NOT NULL, |
| 125 | PRIMARY KEY(instance, thread, id) |
| 126 | ) |
| 127 | """) |
| 128 | exec(handle, "CREATE INDEX IF NOT EXISTS idx_messages_at ON messages(instance, thread, at, id)") |
| 129 | migrateSearchIndex(handle) |
| 130 | // Where each thread's full-history backfill has reached (#584): the |
| 131 | // oldest fetched id, and whether the walk hit the beginning. Additive, |
| 132 | // like every migration here. |
| 133 | exec(handle, """ |
| 134 | CREATE TABLE IF NOT EXISTS backfill( |
| 135 | instance TEXT NOT NULL, |
| 136 | thread TEXT NOT NULL, |
| 137 | oldest INTEGER NOT NULL DEFAULT 0, |
| 138 | complete INTEGER NOT NULL DEFAULT 0, |
| 139 | PRIMARY KEY(instance, thread) |
| 140 | ) |
| 141 | """) |
| 142 | // What's IN each shared photo (ImageSearchIndex): Vision's scene labels |
| 143 | // + recognised text, per blob, so the media view can search photos by |
| 144 | // content. Derived plaintext — it lives here, in the protected store |
| 145 | // beside the decrypted payloads it came from, and never on a server. |
| 146 | // `ver` is the indexer's recipe version, so a smarter pass can re-do |
| 147 | // only the rows an older one produced. |
| 148 | exec(handle, """ |
| 149 | CREATE TABLE IF NOT EXISTS image_index( |
| 150 | instance TEXT NOT NULL, |
| 151 | blob TEXT NOT NULL, |
| 152 | labels TEXT NOT NULL DEFAULT '', |
| 153 | text TEXT NOT NULL DEFAULT '', |
| 154 | ver INTEGER NOT NULL DEFAULT 1, |
| 155 | PRIMARY KEY(instance, blob) |
| 156 | ) |
| 157 | """) |
| 158 | // Never in a backup; protected at rest like the keys. |
| 159 | try? FileManager.default.setAttributes( |
| 160 | [.protectionKey: FileProtectionType.completeUntilFirstUserAuthentication], |
| 161 | ofItemAtPath: url.path) |
| 162 | var values = URLResourceValues() |
| 163 | values.isExcludedFromBackup = true |
| 164 | try? url.setResourceValues(values) |
| 165 | db = handle |
| 166 | return handle |
| 167 | } |
| 168 | |
| 169 | private func exec(_ handle: OpaquePointer, _ sql: String) { |
| 170 | sqlite3_exec(handle, sql, nil, nil, nil) |
| 171 | } |
| 172 | |
| 173 | /// The FTS table, keyed by the messages row's rowid. The original shape |
| 174 | /// carried instance/thread/msgid as UNINDEXED fts5 columns and deleted by |
| 175 | /// matching them — which fts5 can only answer with a scan of the whole |
| 176 | /// index, per message, per fold. That cost grew with the store until a |
| 177 | /// page write could straddle backgrounding (the Aug 2026 0xdead10cc |
| 178 | /// recurrence — see suspend()). user_version 1 re-keys the table by |
| 179 | /// rowid so index upkeep is an O(log n) lookup, rebuilding it once from |
| 180 | /// the messages we already hold. |
| 181 | private func migrateSearchIndex(_ handle: OpaquePointer) { |
| 182 | var version: Int32 = 0 |
| 183 | var stmt: OpaquePointer? |
| 184 | if sqlite3_prepare_v2(handle, "PRAGMA user_version", -1, &stmt, nil) == SQLITE_OK, |
| 185 | sqlite3_step(stmt) == SQLITE_ROW { |
| 186 | version = sqlite3_column_int(stmt, 0) |
| 187 | } |
| 188 | sqlite3_finalize(stmt) |
| 189 | guard version < 1 else { return } |
| 190 | exec(handle, "DROP TABLE IF EXISTS messages_fts") |
| 191 | exec(handle, "CREATE VIRTUAL TABLE messages_fts USING fts5(text)") |
| 192 | exec(handle, """ |
| 193 | INSERT INTO messages_fts(rowid, text) |
| 194 | SELECT rowid, json_extract(payload, '$.text') FROM messages |
| 195 | WHERE json_extract(payload, '$.text') IS NOT NULL |
| 196 | AND json_extract(payload, '$.text') <> '' |
| 197 | """) |
| 198 | exec(handle, "PRAGMA user_version=1") |
| 199 | } |
| 200 | |
| 201 | /// Close the connection AND gate reopens until resume(). Holding a file |
| 202 | /// lock in a shared (app-group) container while suspended is the |
| 203 | /// 0xdead10cc RunningBoard kill. Round one (26–28 Jul 2026): the app |
| 204 | /// suspended mid-write (or mid-3s busy_timeout wait on the NSE's write |
| 205 | /// lock); closing on background fixed those. Round two (2–5 Aug 2026): |
| 206 | /// a detached page-fold upsert landing AFTER the close simply reopened |
| 207 | /// via open() and died holding the lock mid-sqlite3_step — hence the |
| 208 | /// gate. Every legitimate window brackets itself with resume(): the iOS |
| 209 | /// app on scenePhase .active, Background App Refresh at the start of |
| 210 | /// its pass, the NSE at the start of each ingest. Anything outside a |
| 211 | /// bracket no-ops, which is safe — the store is a cache, and the next |
| 212 | /// fold rewrites whatever a dropped straggler would have written. |
| 213 | public func suspend() { |
| 214 | suspended = true |
| 215 | guard let db else { return } |
| 216 | sqlite3_close(db) |
| 217 | self.db = nil |
| 218 | } |
| 219 | |
| 220 | /// Lift the suspension gate; the connection itself reopens lazily on |
| 221 | /// the next op. |
| 222 | public func resume() { suspended = false } |
| 223 | |
| 224 | // ---- ingest ------------------------------------------------------------- |
| 225 | |
| 226 | /// Upsert a decrypted batch (history page or a single live frame). Existing |
| 227 | /// rows keep their reactions/edited unless the new row carries fresher ones |
| 228 | /// (live echoes arrive bare — the same merge rule the in-memory fold used). |
| 229 | public func upsert(instance: String, thread: String, _ batch: [StoredMessage]) { |
| 230 | guard let handle = open(), !batch.isEmpty else { return } |
| 231 | exec(handle, "BEGIN") |
| 232 | defer { exec(handle, "COMMIT") } |
| 233 | let sql = """ |
| 234 | INSERT INTO messages(instance, thread, id, at, edited, reactions, payload) |
| 235 | VALUES(?,?,?,?,?,?,?) |
| 236 | ON CONFLICT(instance, thread, id) DO UPDATE SET |
| 237 | at=excluded.at, |
| 238 | edited=CASE WHEN excluded.edited=1 THEN 1 ELSE messages.edited END, |
| 239 | reactions=CASE WHEN excluded.reactions='[]' THEN messages.reactions ELSE excluded.reactions END, |
| 240 | payload=excluded.payload |
| 241 | """ |
| 242 | var stmt: OpaquePointer? |
| 243 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return } |
| 244 | defer { sqlite3_finalize(stmt) } |
| 245 | let enc = JSONEncoder() |
| 246 | for m in batch { |
| 247 | sqlite3_reset(stmt) |
| 248 | bind(stmt, 1, instance) |
| 249 | bind(stmt, 2, thread) |
| 250 | sqlite3_bind_int64(stmt, 3, m.id) |
| 251 | sqlite3_bind_int64(stmt, 4, m.at) |
| 252 | sqlite3_bind_int(stmt, 5, m.edited ? 1 : 0) |
| 253 | bind(stmt, 6, (try? enc.encode(m.reactions)).flatMap { String(data: $0, encoding: .utf8) } ?? "[]") |
| 254 | bind(stmt, 7, (try? enc.encode(m.payload)).flatMap { String(data: $0, encoding: .utf8) } ?? "{}") |
| 255 | sqlite3_step(stmt) |
| 256 | indexText(handle, instance: instance, thread: thread, id: m.id, text: m.payload.text ?? "") |
| 257 | } |
| 258 | } |
| 259 | |
| 260 | public func updateReactions(instance: String, thread: String, id: Int64, _ reactions: [Reaction]) { |
| 261 | guard let handle = open() else { return } |
| 262 | let json = (try? JSONEncoder().encode(reactions)) |
| 263 | .flatMap { String(data: $0, encoding: .utf8) } ?? "[]" |
| 264 | run(handle, "UPDATE messages SET reactions=? WHERE instance=? AND thread=? AND id=?") { |
| 265 | bind($0, 1, json); bind($0, 2, instance); bind($0, 3, thread) |
| 266 | sqlite3_bind_int64($0, 4, id) |
| 267 | } |
| 268 | } |
| 269 | |
| 270 | // ---- full-history backfill (#584) --------------------------------------- |
| 271 | |
| 272 | /// Where a thread's backwards walk has reached. (0, false) = never started. |
| 273 | public func backfillMark(instance: String, thread: String) -> (oldest: Int64, complete: Bool) { |
| 274 | guard let handle = open() else { return (0, false) } |
| 275 | var stmt: OpaquePointer? |
| 276 | guard sqlite3_prepare_v2(handle, |
| 277 | "SELECT oldest, complete FROM backfill WHERE instance=? AND thread=?", |
| 278 | -1, &stmt, nil) == SQLITE_OK else { return (0, false) } |
| 279 | defer { sqlite3_finalize(stmt) } |
| 280 | bind(stmt, 1, instance) |
| 281 | bind(stmt, 2, thread) |
| 282 | guard sqlite3_step(stmt) == SQLITE_ROW else { return (0, false) } |
| 283 | return (sqlite3_column_int64(stmt, 0), sqlite3_column_int64(stmt, 1) == 1) |
| 284 | } |
| 285 | |
| 286 | public func setBackfillMark(instance: String, thread: String, oldest: Int64, complete: Bool) { |
| 287 | guard let handle = open() else { return } |
| 288 | run(handle, """ |
| 289 | INSERT INTO backfill(instance, thread, oldest, complete) VALUES(?,?,?,?) |
| 290 | ON CONFLICT(instance, thread) DO UPDATE SET oldest=excluded.oldest, complete=excluded.complete |
| 291 | """) { |
| 292 | bind($0, 1, instance); bind($0, 2, thread) |
| 293 | sqlite3_bind_int64($0, 3, oldest); sqlite3_bind_int64($0, 4, complete ? 1 : 0) |
| 294 | } |
| 295 | } |
| 296 | |
| 297 | /// The lowest stored id for a thread — where a first walk starts from. |
| 298 | /// 0 when nothing is stored yet. |
| 299 | public func oldestStoredId(instance: String, thread: String) -> Int64 { |
| 300 | guard let handle = open() else { return 0 } |
| 301 | var stmt: OpaquePointer? |
| 302 | guard sqlite3_prepare_v2(handle, |
| 303 | "SELECT id FROM messages WHERE instance=? AND thread=? ORDER BY id ASC LIMIT 1", |
| 304 | -1, &stmt, nil) == SQLITE_OK else { return 0 } |
| 305 | defer { sqlite3_finalize(stmt) } |
| 306 | bind(stmt, 1, instance) |
| 307 | bind(stmt, 2, thread) |
| 308 | guard sqlite3_step(stmt) == SQLITE_ROW else { return 0 } |
| 309 | return sqlite3_column_int64(stmt, 0) |
| 310 | } |
| 311 | |
| 312 | public func delete(instance: String, thread: String, id: Int64) { |
| 313 | guard let handle = open() else { return } |
| 314 | // FTS row first — its key is the messages row's rowid. |
| 315 | run(handle, """ |
| 316 | DELETE FROM messages_fts WHERE rowid= |
| 317 | (SELECT rowid FROM messages WHERE instance=? AND thread=? AND id=?) |
| 318 | """) { |
| 319 | bind($0, 1, instance); bind($0, 2, thread); sqlite3_bind_int64($0, 3, id) |
| 320 | } |
| 321 | run(handle, "DELETE FROM messages WHERE instance=? AND thread=? AND id=?") { |
| 322 | bind($0, 1, instance); bind($0, 2, thread); sqlite3_bind_int64($0, 3, id) |
| 323 | } |
| 324 | } |
| 325 | |
| 326 | /// Upkeep is keyed by the messages row's rowid (see migrateSearchIndex) |
| 327 | /// — always called with the messages row already upserted. |
| 328 | private func indexText(_ handle: OpaquePointer, instance: String, thread: String, |
| 329 | id: Int64, text: String) { |
| 330 | run(handle, """ |
| 331 | DELETE FROM messages_fts WHERE rowid= |
| 332 | (SELECT rowid FROM messages WHERE instance=? AND thread=? AND id=?) |
| 333 | """) { |
| 334 | bind($0, 1, instance); bind($0, 2, thread); sqlite3_bind_int64($0, 3, id) |
| 335 | } |
| 336 | guard !text.isEmpty else { return } |
| 337 | run(handle, """ |
| 338 | INSERT INTO messages_fts(rowid, text) VALUES( |
| 339 | (SELECT rowid FROM messages WHERE instance=? AND thread=? AND id=?), ?) |
| 340 | """) { |
| 341 | bind($0, 1, instance); bind($0, 2, thread); sqlite3_bind_int64($0, 3, id) |
| 342 | bind($0, 4, text) |
| 343 | } |
| 344 | } |
| 345 | |
| 346 | // ---- reads -------------------------------------------------------------- |
| 347 | |
| 348 | /// The newest `limit` messages of a thread (ascending) — the instant open. |
| 349 | public func page(instance: String, thread: String, before: Int64? = nil, |
| 350 | limit: Int = 200) -> [StoredMessage] { |
| 351 | guard let handle = open() else { return [] } |
| 352 | var sql = "SELECT id, at, edited, reactions, payload FROM messages WHERE instance=? AND thread=?" |
| 353 | if before != nil { sql += " AND id < ?" } |
| 354 | sql += " ORDER BY id DESC LIMIT \(limit)" |
| 355 | var out: [StoredMessage] = [] |
| 356 | var stmt: OpaquePointer? |
| 357 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return [] } |
| 358 | defer { sqlite3_finalize(stmt) } |
| 359 | bind(stmt, 1, instance) |
| 360 | bind(stmt, 2, thread) |
| 361 | if let before { sqlite3_bind_int64(stmt, 3, before) } |
| 362 | while sqlite3_step(stmt) == SQLITE_ROW { |
| 363 | if let m = readRow(stmt) { out.append(m) } |
| 364 | } |
| 365 | return out.reversed() |
| 366 | } |
| 367 | |
| 368 | /// Everything stored for a thread, newest first — the Media/Links/Docs |
| 369 | /// extraction sweep. The cap mirrors the web store's allMessages 50k |
| 370 | /// watermark (spec § Edge cases: huge threads inherit the documented gap). |
| 371 | public func allMessages(instance: String, thread: String, |
| 372 | limit: Int = 50_000) -> [StoredMessage] { |
| 373 | guard let handle = open() else { return [] } |
| 374 | var out: [StoredMessage] = [] |
| 375 | var stmt: OpaquePointer? |
| 376 | guard sqlite3_prepare_v2(handle, |
| 377 | "SELECT id, at, edited, reactions, payload FROM messages WHERE instance=? AND thread=? ORDER BY id DESC LIMIT \(limit)", |
| 378 | -1, &stmt, nil) == SQLITE_OK else { return [] } |
| 379 | defer { sqlite3_finalize(stmt) } |
| 380 | bind(stmt, 1, instance) |
| 381 | bind(stmt, 2, thread) |
| 382 | while sqlite3_step(stmt) == SQLITE_ROW { |
| 383 | if let m = readRow(stmt) { out.append(m) } |
| 384 | } |
| 385 | return out |
| 386 | } |
| 387 | |
| 388 | /// The newest stored message's (id, at) for a thread — the incremental-sync |
| 389 | /// watermark background refresh and the NSE-ingest catch-up key off. (0, 0) |
| 390 | /// when nothing is stored yet. |
| 391 | public func latest(instance: String, thread: String) -> (id: Int64, at: Int64) { |
| 392 | guard let handle = open() else { return (0, 0) } |
| 393 | var stmt: OpaquePointer? |
| 394 | guard sqlite3_prepare_v2(handle, |
| 395 | "SELECT id, at FROM messages WHERE instance=? AND thread=? ORDER BY id DESC LIMIT 1", |
| 396 | -1, &stmt, nil) == SQLITE_OK else { return (0, 0) } |
| 397 | defer { sqlite3_finalize(stmt) } |
| 398 | bind(stmt, 1, instance) |
| 399 | bind(stmt, 2, thread) |
| 400 | guard sqlite3_step(stmt) == SQLITE_ROW else { return (0, 0) } |
| 401 | return (sqlite3_column_int64(stmt, 0), sqlite3_column_int64(stmt, 1)) |
| 402 | } |
| 403 | |
| 404 | /// Full-text search across an instance (newest first). |
| 405 | public func search(instance: String, query: String, limit: Int = 50) |
| 406 | -> [(thread: String, message: StoredMessage)] { |
| 407 | guard let handle = open(), !query.isEmpty else { return [] } |
| 408 | let sql = """ |
| 409 | SELECT m.thread, m.id, m.at, m.edited, m.reactions, m.payload |
| 410 | FROM messages_fts f JOIN messages m ON m.rowid=f.rowid |
| 411 | WHERE messages_fts MATCH ? AND m.instance=? |
| 412 | ORDER BY m.id DESC LIMIT \(limit) |
| 413 | """ |
| 414 | var out: [(String, StoredMessage)] = [] |
| 415 | var stmt: OpaquePointer? |
| 416 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return [] } |
| 417 | defer { sqlite3_finalize(stmt) } |
| 418 | bind(stmt, 1, query) |
| 419 | bind(stmt, 2, instance) |
| 420 | while sqlite3_step(stmt) == SQLITE_ROW { |
| 421 | guard let thread = sqlite3_column_text(stmt, 0).map({ String(cString: $0) }) else { continue } |
| 422 | if let m = readRow(stmt, offset: 1) { out.append((thread, m)) } |
| 423 | } |
| 424 | return out |
| 425 | } |
| 426 | |
| 427 | /// Wipe an instance's rows (sign-out). |
| 428 | public func clear(instance: String) { |
| 429 | guard let handle = open() else { return } |
| 430 | // FTS rows first — keyed by the messages rowids about to go. |
| 431 | run(handle, """ |
| 432 | DELETE FROM messages_fts WHERE rowid IN |
| 433 | (SELECT rowid FROM messages WHERE instance=?) |
| 434 | """) { bind($0, 1, instance) } |
| 435 | run(handle, "DELETE FROM messages WHERE instance=?") { bind($0, 1, instance) } |
| 436 | run(handle, "DELETE FROM image_index WHERE instance=?") { bind($0, 1, instance) } |
| 437 | } |
| 438 | |
| 439 | // ---- image index (ImageSearchIndex) -------------------------------------- |
| 440 | |
| 441 | public struct ImageIndexRow: Sendable { |
| 442 | public let labels: [String] |
| 443 | public let text: String |
| 444 | /// How far the indexer got with this photo (ImageSearchIndex's pass |
| 445 | /// versions): labels-only rows are usable and get their text later. |
| 446 | public let ver: Int |
| 447 | public init(labels: [String], text: String, ver: Int = 1) { |
| 448 | self.labels = labels |
| 449 | self.text = text |
| 450 | self.ver = ver |
| 451 | } |
| 452 | } |
| 453 | |
| 454 | /// Every indexed photo for an instance, by blob id — one read at open, |
| 455 | /// held in memory by the index (a thread's photos are a subset). |
| 456 | public func imageIndex(instance: String, minVersion: Int = 1) -> [String: ImageIndexRow] { |
| 457 | guard let handle = open() else { return [:] } |
| 458 | var out: [String: ImageIndexRow] = [:] |
| 459 | var stmt: OpaquePointer? |
| 460 | guard sqlite3_prepare_v2(handle, |
| 461 | "SELECT blob, labels, text, ver FROM image_index WHERE instance=? AND ver>=?", |
| 462 | -1, &stmt, nil) == SQLITE_OK else { return [:] } |
| 463 | defer { sqlite3_finalize(stmt) } |
| 464 | bind(stmt, 1, instance) |
| 465 | sqlite3_bind_int64(stmt, 2, Int64(minVersion)) |
| 466 | while sqlite3_step(stmt) == SQLITE_ROW { |
| 467 | guard let blob = sqlite3_column_text(stmt, 0).map({ String(cString: $0) }) else { continue } |
| 468 | let labels = sqlite3_column_text(stmt, 1).map { String(cString: $0) } ?? "" |
| 469 | let text = sqlite3_column_text(stmt, 2).map { String(cString: $0) } ?? "" |
| 470 | out[blob] = ImageIndexRow(labels: labels.split(separator: "\n").map(String.init), text: text, |
| 471 | ver: Int(sqlite3_column_int64(stmt, 3))) |
| 472 | } |
| 473 | return out |
| 474 | } |
| 475 | |
| 476 | public func setImageIndex(instance: String, blob: String, row: ImageIndexRow) { |
| 477 | guard let handle = open() else { return } |
| 478 | run(handle, """ |
| 479 | INSERT INTO image_index(instance, blob, labels, text, ver) VALUES(?,?,?,?,?) |
| 480 | ON CONFLICT(instance, blob) DO UPDATE SET labels=excluded.labels, text=excluded.text, ver=excluded.ver |
| 481 | """) { |
| 482 | bind($0, 1, instance); bind($0, 2, blob) |
| 483 | bind($0, 3, row.labels.joined(separator: "\n")); bind($0, 4, row.text) |
| 484 | sqlite3_bind_int64($0, 5, Int64(row.ver)) |
| 485 | } |
| 486 | } |
| 487 | |
| 488 | // ---- plumbing ----------------------------------------------------------- |
| 489 | |
| 490 | private func readRow(_ stmt: OpaquePointer?, offset: Int32 = 0) -> StoredMessage? { |
| 491 | let dec = JSONDecoder() |
| 492 | guard let payloadText = sqlite3_column_text(stmt, offset + 4) |
| 493 | .map({ String(cString: $0) }), |
| 494 | let payload = try? dec.decode(MessagePayload.self, from: Data(payloadText.utf8)) |
| 495 | else { return nil } |
| 496 | let reactions = sqlite3_column_text(stmt, offset + 3) |
| 497 | .map { String(cString: $0) } |
| 498 | .flatMap { try? dec.decode([Reaction].self, from: Data($0.utf8)) } ?? [] |
| 499 | return StoredMessage(id: sqlite3_column_int64(stmt, offset + 0), |
| 500 | at: sqlite3_column_int64(stmt, offset + 1), |
| 501 | edited: sqlite3_column_int(stmt, offset + 2) == 1, |
| 502 | reactions: reactions, |
| 503 | payload: payload) |
| 504 | } |
| 505 | |
| 506 | private func run(_ handle: OpaquePointer, _ sql: String, |
| 507 | _ bindings: (OpaquePointer?) -> Void) { |
| 508 | var stmt: OpaquePointer? |
| 509 | guard sqlite3_prepare_v2(handle, sql, -1, &stmt, nil) == SQLITE_OK else { return } |
| 510 | defer { sqlite3_finalize(stmt) } |
| 511 | bindings(stmt) |
| 512 | sqlite3_step(stmt) |
| 513 | } |
| 514 | |
| 515 | private func bind(_ stmt: OpaquePointer?, _ index: Int32, _ value: String) { |
| 516 | sqlite3_bind_text(stmt, index, value, -1, |
| 517 | unsafeBitCast(-1, to: sqlite3_destructor_type.self)) // SQLITE_TRANSIENT |
| 518 | } |
| 519 | } |
| 520 | |
amadan