Repository Public
1// Author: Simon-Pierre Boucher — contact@spboucher.ai2// File: Core/Persistence/SQLite.swift3// Description: A small, thread-safe SQLite wrapper (WAL, busy timeout, prepared statements, transactions) used by the metadata mirror and the operation journal.4import Foundation5import SQLite367public enum SQLValue: Sendable, Equatable {8 case null, int(Int64), double(Double), text(String), blob(Data)9 public var int: Int64? { if case .int(let v) = self { return v }; return nil }10 public var text: String? { if case .text(let v) = self { return v }; return nil }11 public var double: Double? { switch self { case .double(let v): return v; case .int(let v): return Double(v); default: return nil } }12 public var bool: Bool { int ?? 0 != 0 }13}14public typealias SQLRow = [String: SQLValue]15public struct SQLiteError: Error, Sendable, CustomStringConvertible { public let code: Int32; public let message: String; public var description: String { "SQLite \(code): \(message)" } }1617/// One connection, serialised by a recursive lock. Several processes may open the same file (WAL + busy timeout).18public final class SQLiteDB: @unchecked Sendable {19 private var handle: OpaquePointer?20 private let lock = NSRecursiveLock()21 private var cache: [String: OpaquePointer] = [:]22 private static let transient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)23 public let path: String2425 public init(path: String) throws {26 self.path = path27 let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX28 guard sqlite3_open_v2(path, &handle, flags, nil) == SQLITE_OK else { let e = error(); sqlite3_close(handle); throw e }29 sqlite3_busy_timeout(handle, 8000)30 try exec("PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA foreign_keys=ON; PRAGMA temp_store=MEMORY;")31 }32 deinit { for s in cache.values { sqlite3_finalize(s) }; sqlite3_close(handle) }33 private func error() -> SQLiteError { SQLiteError(code: sqlite3_errcode(handle), message: String(cString: sqlite3_errmsg(handle))) }3435 public func exec(_ sql: String) throws {36 try lock.withLock { guard sqlite3_exec(handle, sql, nil, nil, nil) == SQLITE_OK else { throw error() } }37 }38 private func prepare(_ sql: String) throws -> OpaquePointer {39 if let s = cache[sql] { sqlite3_reset(s); sqlite3_clear_bindings(s); return s }40 var s: OpaquePointer?41 guard sqlite3_prepare_v2(handle, sql, -1, &s, nil) == SQLITE_OK, let stmt = s else { throw error() }42 cache[sql] = stmt43 return stmt44 }45 private func bind(_ s: OpaquePointer, _ values: [SQLValue]) throws {46 for (i, v) in values.enumerated() {47 let n = Int32(i + 1); let rc: Int3248 switch v {49 case .null: rc = sqlite3_bind_null(s, n)50 case .int(let x): rc = sqlite3_bind_int64(s, n, x)51 case .double(let x): rc = sqlite3_bind_double(s, n, x)52 case .text(let x): rc = sqlite3_bind_text(s, n, x, -1, SQLiteDB.transient)53 case .blob(let x): rc = x.withUnsafeBytes { sqlite3_bind_blob(s, n, $0.baseAddress, Int32(x.count), SQLiteDB.transient) }54 }55 guard rc == SQLITE_OK else { throw error() }56 }57 }58 /// Runs a statement that returns no row; returns the number of rows changed.59 @discardableResult public func run(_ sql: String, _ values: [SQLValue] = []) throws -> Int {60 try lock.withLock {61 let s = try prepare(sql); try bind(s, values)62 defer { sqlite3_reset(s) }63 guard sqlite3_step(s) == SQLITE_DONE else { throw error() }64 return Int(sqlite3_changes(handle))65 }66 }67 public func query(_ sql: String, _ values: [SQLValue] = []) throws -> [SQLRow] {68 try lock.withLock {69 let s = try prepare(sql); try bind(s, values)70 defer { sqlite3_reset(s) }71 var rows: [SQLRow] = []72 let n = sqlite3_column_count(s)73 while true {74 let rc = sqlite3_step(s)75 if rc == SQLITE_DONE { break }76 guard rc == SQLITE_ROW else { throw error() }77 var row = SQLRow(minimumCapacity: Int(n))78 for i in 0..<n {79 let name = String(cString: sqlite3_column_name(s, i))80 switch sqlite3_column_type(s, i) {81 case SQLITE_INTEGER: row[name] = .int(sqlite3_column_int64(s, i))82 case SQLITE_FLOAT: row[name] = .double(sqlite3_column_double(s, i))83 case SQLITE_TEXT: row[name] = .text(String(cString: sqlite3_column_text(s, i)))84 case SQLITE_BLOB: let c = Int(sqlite3_column_bytes(s, i)); row[name] = .blob(c > 0 ? Data(bytes: sqlite3_column_blob(s, i), count: c) : Data())85 default: row[name] = .null86 }87 }88 rows.append(row)89 }90 return rows91 }92 }93 public func scalar(_ sql: String, _ values: [SQLValue] = []) throws -> SQLValue { try query(sql, values).first?.values.first ?? .null }94 /// Runs `body` in an IMMEDIATE transaction (the write lock is taken at once, so two processes queue instead of failing half-way).95 public func transaction<T>(_ body: () throws -> T) throws -> T {96 try lock.withLock {97 if sqlite3_get_autocommit(handle) == 0 { return try body() } // already inside one98 try exec("BEGIN IMMEDIATE")99 do { let r = try body(); try exec("COMMIT"); return r } catch { try? exec("ROLLBACK"); throw error }100 }101 }102}103