Repository Public
1// SP Notes Core2// File: Sources/SPNotesStore/SQLite.swift3// Description: Minimal safe wrapper over the system SQLite3 library: connection, cached prepared statements,4// typed binding/reading, nested transactions with rollback. Internal to the store module.5//6// Author: Simon-Pierre Boucher7// Contact: contact@spboucher.ai8import Foundation9import SQLite31011private let sqliteTransient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)1213/// A value that can be bound to a statement parameter.14enum SQLValue: Sendable, Equatable {15 case null16 case int(Int64)17 case double(Double)18 case text(String)19 case blob(Data)20}2122protocol SQLBindable {23 var sqlValue: SQLValue { get }24}2526extension SQLValue: SQLBindable { var sqlValue: SQLValue { self } }27extension Int: SQLBindable { var sqlValue: SQLValue { .int(Int64(self)) } }28extension Int64: SQLBindable { var sqlValue: SQLValue { .int(self) } }29extension Double: SQLBindable { var sqlValue: SQLValue { .double(self) } }30extension String: SQLBindable { var sqlValue: SQLValue { .text(self) } }31extension Data: SQLBindable { var sqlValue: SQLValue { .blob(self) } }32extension Bool: SQLBindable { var sqlValue: SQLValue { .int(self ? 1 : 0) } }33extension Date: SQLBindable { var sqlValue: SQLValue { .int(millis) } }34extension Optional: SQLBindable where Wrapped: SQLBindable {35 var sqlValue: SQLValue {36 switch self {37 case .none: return .null38 case .some(let value): return value.sqlValue39 }40 }41}4243extension Date {44 /// Integer milliseconds since the Unix epoch (the storage format of every timestamp).45 var millis: Int64 { Int64((timeIntervalSince1970 * 1000).rounded()) }46 init(millis: Int64) { self.init(timeIntervalSince1970: Double(millis) / 1000) }47}4849/// A cursor positioned on one result row. Only valid inside the mapping closure of `Database.query`.50struct Row {51 fileprivate let stmt: OpaquePointer5253 func isNull(_ index: Int) -> Bool { sqlite3_column_type(stmt, Int32(index)) == SQLITE_NULL }54 func int(_ index: Int) -> Int64 { sqlite3_column_int64(stmt, Int32(index)) }55 func optInt(_ index: Int) -> Int64? { isNull(index) ? nil : int(index) }56 func bool(_ index: Int) -> Bool { int(index) != 0 }57 func double(_ index: Int) -> Double { sqlite3_column_double(stmt, Int32(index)) }58 func string(_ index: Int) -> String {59 guard let text = sqlite3_column_text(stmt, Int32(index)) else { return "" }60 return String(cString: text)61 }62 func optString(_ index: Int) -> String? { isNull(index) ? nil : string(index) }63 func data(_ index: Int) -> Data {64 let count = Int(sqlite3_column_bytes(stmt, Int32(index)))65 guard count > 0, let bytes = sqlite3_column_blob(stmt, Int32(index)) else { return Data() }66 return Data(bytes: bytes, count: count)67 }68 func optData(_ index: Int) -> Data? { isNull(index) ? nil : data(index) }69 func date(_ index: Int) -> Date { Date(millis: int(index)) }70 func optDate(_ index: Int) -> Date? { isNull(index) ? nil : date(index) }71}7273/// One SQLite connection. Not thread-safe by itself beyond SQLite's own FULLMUTEX: it is owned by the74/// `NotesStore` actor, which serializes every call.75final class Database {76 private var handle: OpaquePointer?77 let readOnly: Bool78 private var cache: [String: OpaquePointer] = [:]79 private var depth = 080 private static let cacheLimit = 2568182 init(path: String, readOnly: Bool, busyTimeoutMs: Int32 = 8000) throws {83 self.readOnly = readOnly84 let flags = (readOnly ? SQLITE_OPEN_READONLY : (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE)) | SQLITE_OPEN_FULLMUTEX85 var db: OpaquePointer?86 let rc = sqlite3_open_v2(path, &db, flags, nil)87 guard rc == SQLITE_OK, let db else {88 let message = db.map { String(cString: sqlite3_errmsg($0)) } ?? "cannot open database"89 if let db { sqlite3_close_v2(db) }90 throw StoreError.sqlite(code: rc, message: message, sql: nil)91 }92 handle = db93 sqlite3_extended_result_codes(db, 1)94 sqlite3_busy_timeout(db, busyTimeoutMs)95 do {96 if readOnly {97 try exec("PRAGMA query_only=ON")98 } else {99 // WAL + FULL: a committed transaction survives a power loss; readers never block the writer.100 _ = try query("PRAGMA journal_mode=WAL") { $0.string(0) }101 try exec("PRAGMA synchronous=FULL")102 }103 try exec("PRAGMA foreign_keys=ON")104 } catch {105 close()106 throw error107 }108 }109110 deinit { close() }111112 var isOpen: Bool { handle != nil }113114 func close() {115 guard let db = handle else { return }116 for stmt in cache.values { sqlite3_finalize(stmt) }117 cache.removeAll()118 sqlite3_close_v2(db)119 handle = nil120 }121122 private func connection() throws -> OpaquePointer {123 guard let handle else { throw StoreError.closed }124 return handle125 }126127 private func error(_ db: OpaquePointer, _ sql: String?) -> StoreError {128 .sqlite(code: sqlite3_extended_errcode(db), message: String(cString: sqlite3_errmsg(db)), sql: sql)129 }130131 /// Runs one or several statements without parameters (DDL, PRAGMA).132 func exec(_ sql: String) throws {133 let db = try connection()134 guard sqlite3_exec(db, sql, nil, nil, nil) == SQLITE_OK else { throw error(db, sql) }135 }136137 /// Takes a prepared statement out of the cache (so that a re-entrant use of the same SQL gets its own).138 private func checkout(_ sql: String) throws -> OpaquePointer {139 let db = try connection()140 if let stmt = cache.removeValue(forKey: sql) { return stmt }141 var stmt: OpaquePointer?142 guard sqlite3_prepare_v3(db, sql, -1, UInt32(SQLITE_PREPARE_PERSISTENT), &stmt, nil) == SQLITE_OK, let stmt else {143 throw error(db, sql)144 }145 return stmt146 }147148 private func checkin(_ sql: String, _ stmt: OpaquePointer) {149 sqlite3_reset(stmt)150 sqlite3_clear_bindings(stmt)151 if handle == nil || cache[sql] != nil {152 sqlite3_finalize(stmt)153 return154 }155 if cache.count >= Self.cacheLimit {156 for other in cache.values { sqlite3_finalize(other) }157 cache.removeAll(keepingCapacity: true)158 }159 cache[sql] = stmt160 }161162 private func bind(_ stmt: OpaquePointer, _ params: [any SQLBindable], _ sql: String) throws {163 let db = try connection()164 guard Int(sqlite3_bind_parameter_count(stmt)) == params.count else {165 throw StoreError.sqlite(code: SQLITE_RANGE, message: "expected \(sqlite3_bind_parameter_count(stmt)) parameters, got \(params.count)", sql: sql)166 }167 for (offset, param) in params.enumerated() {168 let index = Int32(offset + 1)169 let rc: Int32170 switch param.sqlValue {171 case .null: rc = sqlite3_bind_null(stmt, index)172 case .int(let value): rc = sqlite3_bind_int64(stmt, index, value)173 case .double(let value): rc = sqlite3_bind_double(stmt, index, value)174 case .text(let value): rc = sqlite3_bind_text(stmt, index, value, -1, sqliteTransient)175 case .blob(let value):176 rc = value.withUnsafeBytes { buffer in177 // A zero-length blob must stay a blob (not NULL): bind a non-null pointer with length 0.178 if let base = buffer.baseAddress, !buffer.isEmpty {179 return sqlite3_bind_blob64(stmt, index, base, sqlite3_uint64(buffer.count), sqliteTransient)180 }181 return sqlite3_bind_zeroblob(stmt, index, 0)182 }183 }184 guard rc == SQLITE_OK else { throw error(db, sql) }185 }186 }187188 /// Executes a statement that returns no row. Returns the number of rows changed.189 @discardableResult190 func run(_ sql: String, _ params: [any SQLBindable] = []) throws -> Int {191 let db = try connection()192 let stmt = try checkout(sql)193 defer { checkin(sql, stmt) }194 try bind(stmt, params, sql)195 var rc = sqlite3_step(stmt)196 while rc == SQLITE_ROW { rc = sqlite3_step(stmt) }197 guard rc == SQLITE_DONE else { throw error(db, sql) }198 return Int(sqlite3_changes(db))199 }200201 /// Executes a query and maps every row. Rows are fully materialized before returning.202 func query<T>(_ sql: String, _ params: [any SQLBindable] = [], _ map: (Row) throws -> T) throws -> [T] {203 let db = try connection()204 let stmt = try checkout(sql)205 defer { checkin(sql, stmt) }206 try bind(stmt, params, sql)207 var out: [T] = []208 let row = Row(stmt: stmt)209 while true {210 let rc = sqlite3_step(stmt)211 if rc == SQLITE_ROW {212 out.append(try map(row))213 } else if rc == SQLITE_DONE {214 break215 } else {216 throw error(db, sql)217 }218 }219 return out220 }221222 func first<T>(_ sql: String, _ params: [any SQLBindable] = [], _ map: (Row) throws -> T) throws -> T? {223 try query(sql, params, map).first224 }225226 func scalar(_ sql: String, _ params: [any SQLBindable] = []) throws -> Int64 {227 try first(sql, params) { $0.int(0) } ?? 0228 }229230 var lastInsertRowID: Int64 { handle.map { sqlite3_last_insert_rowid($0) } ?? 0 }231232 var inTransaction: Bool { depth > 0 }233234 /// Runs `body` atomically. The outermost level is a `BEGIN IMMEDIATE` transaction (a plain `BEGIN` on a235 /// read-only connection, which gives a consistent snapshot); nested levels are savepoints. Any error thrown236 /// by `body` rolls its level back and is rethrown.237 func transaction<T>(immediate: Bool = true, _ body: () throws -> T) throws -> T {238 let level = depth239 let name = "sp\(level)"240 try exec(level == 0 ? (readOnly || !immediate ? "BEGIN" : "BEGIN IMMEDIATE") : "SAVEPOINT \(name)")241 depth += 1242 do {243 let result = try body()244 try exec(level == 0 ? "COMMIT" : "RELEASE \(name)")245 depth -= 1246 return result247 } catch {248 depth -= 1249 // A failed rollback must not hide the original error.250 try? exec(level == 0 ? "ROLLBACK" : "ROLLBACK TO \(name); RELEASE \(name)")251 throw error252 }253 }254}255