Repository Public
1// SP Drive iOS2// File: Core/Persistence/SQLite.swift3// Description: Minimal SQLite wrapper (system library, WAL, prepared statements, nested-safe transactions). One connection per store, serialised by a lock.4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai7import Foundation8import SQLite3910public enum SQLValue: Sendable, Equatable {11 case null, int(Int64), real(Double), text(String), blob(Data)1213 public var int: Int64? { if case .int(let v) = self { return v }; if case .real(let v) = self { return Int64(v) }; return nil }14 public var double: Double? { if case .real(let v) = self { return v }; if case .int(let v) = self { return Double(v) }; return nil }15 public var string: String? { if case .text(let v) = self { return v }; return nil }16 public var bool: Bool { (int ?? 0) != 0 }17 public var date: Date? { double.map { Date(timeIntervalSince1970: $0) } }18}1920public protocol SQLBindable { var sqlValue: SQLValue { get } }21extension Int64: SQLBindable { public var sqlValue: SQLValue { .int(self) } }22extension Int: SQLBindable { public var sqlValue: SQLValue { .int(Int64(self)) } }23extension Bool: SQLBindable { public var sqlValue: SQLValue { .int(self ? 1 : 0) } }24extension Double: SQLBindable { public var sqlValue: SQLValue { .real(self) } }25extension String: SQLBindable { public var sqlValue: SQLValue { .text(self) } }26extension Data: SQLBindable { public var sqlValue: SQLValue { .blob(self) } }27extension Date: SQLBindable { public var sqlValue: SQLValue { .real(timeIntervalSince1970) } }28extension SQLValue: SQLBindable { public var sqlValue: SQLValue { self } }29extension Optional: SQLBindable where Wrapped: SQLBindable { public var sqlValue: SQLValue { self?.sqlValue ?? .null } }3031public struct SQLRow: Sendable {32 let columns: [String: Int]33 let values: [SQLValue]34 public subscript(_ name: String) -> SQLValue { columns[name].map { values[$0] } ?? .null }35}3637public struct SQLiteError: Error, CustomStringConvertible, Sendable {38 public let code: Int3239 public let message: String40 public var description: String { "SQLite \(code): \(message)" }41}4243public final class SQLiteDatabase: @unchecked Sendable {44 private var handle: OpaquePointer?45 private let lock = NSRecursiveLock()46 private var depth = 047 private static let transient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)4849 /// `path == nil` opens a private in-memory database (tests).50 public init(path: String?) throws {51 let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX52 let rc = sqlite3_open_v2(path ?? ":memory:", &handle, flags, nil)53 guard rc == SQLITE_OK else { let m = handle.map { String(cString: sqlite3_errmsg($0)) } ?? "open failed"; sqlite3_close(handle); throw SQLiteError(code: rc, message: m) }54 sqlite3_busy_timeout(handle, 5000)55 try exec("PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL; PRAGMA foreign_keys=ON;")56 }5758 deinit { sqlite3_close(handle) }5960 private func fail(_ rc: Int32) -> SQLiteError { SQLiteError(code: rc, message: String(cString: sqlite3_errmsg(handle))) }6162 public func exec(_ sql: String) throws {63 lock.lock(); defer { lock.unlock() }64 let rc = sqlite3_exec(handle, sql, nil, nil, nil)65 guard rc == SQLITE_OK else { throw fail(rc) }66 }6768 private func prepare(_ sql: String, _ binds: [SQLBindable]) throws -> OpaquePointer {69 var stmt: OpaquePointer?70 let rc = sqlite3_prepare_v2(handle, sql, -1, &stmt, nil)71 guard rc == SQLITE_OK, let stmt else { throw fail(rc) }72 for (i, b) in binds.enumerated() {73 let idx = Int32(i + 1)74 let r: Int3275 switch b.sqlValue {76 case .null: r = sqlite3_bind_null(stmt, idx)77 case .int(let v): r = sqlite3_bind_int64(stmt, idx, v)78 case .real(let v): r = sqlite3_bind_double(stmt, idx, v)79 case .text(let v): r = sqlite3_bind_text(stmt, idx, v, -1, Self.transient)80 case .blob(let v): r = v.withUnsafeBytes { sqlite3_bind_blob(stmt, idx, $0.baseAddress, Int32(v.count), Self.transient) }81 }82 guard r == SQLITE_OK else { sqlite3_finalize(stmt); throw fail(r) }83 }84 return stmt85 }8687 /// Runs a statement that returns no rows; returns the number of rows changed.88 @discardableResult89 public func run(_ sql: String, _ binds: SQLBindable...) throws -> Int { try run(sql, binds: binds) }9091 @discardableResult92 public func run(_ sql: String, binds: [SQLBindable]) throws -> Int {93 lock.lock(); defer { lock.unlock() }94 let stmt = try prepare(sql, binds)95 defer { sqlite3_finalize(stmt) }96 let rc = sqlite3_step(stmt)97 guard rc == SQLITE_DONE || rc == SQLITE_ROW else { throw fail(rc) }98 return Int(sqlite3_changes(handle))99 }100101 public var lastInsertID: Int64 { lock.lock(); defer { lock.unlock() }; return sqlite3_last_insert_rowid(handle) }102103 public func query(_ sql: String, _ binds: SQLBindable...) throws -> [SQLRow] { try query(sql, binds: binds) }104105 public func query(_ sql: String, binds: [SQLBindable]) throws -> [SQLRow] {106 lock.lock(); defer { lock.unlock() }107 let stmt = try prepare(sql, binds)108 defer { sqlite3_finalize(stmt) }109 let n = Int(sqlite3_column_count(stmt))110 var columns: [String: Int] = [:]111 for i in 0..<n { columns[String(cString: sqlite3_column_name(stmt, Int32(i)))] = i }112 var rows: [SQLRow] = []113 while true {114 let rc = sqlite3_step(stmt)115 if rc == SQLITE_DONE { break }116 guard rc == SQLITE_ROW else { throw fail(rc) }117 var values: [SQLValue] = []118 values.reserveCapacity(n)119 for i in 0..<Int32(n) {120 switch sqlite3_column_type(stmt, i) {121 case SQLITE_INTEGER: values.append(.int(sqlite3_column_int64(stmt, i)))122 case SQLITE_FLOAT: values.append(.real(sqlite3_column_double(stmt, i)))123 case SQLITE_TEXT: values.append(.text(String(cString: sqlite3_column_text(stmt, i))))124 case SQLITE_BLOB:125 let c = Int(sqlite3_column_bytes(stmt, i))126 values.append(.blob(sqlite3_column_blob(stmt, i).map { Data(bytes: $0, count: c) } ?? Data()))127 default: values.append(.null)128 }129 }130 rows.append(SQLRow(columns: columns, values: values))131 }132 return rows133 }134135 /// All-or-nothing. Nested calls join the outer transaction.136 public func transaction<T>(_ body: () throws -> T) throws -> T {137 lock.lock(); defer { lock.unlock() }138 if depth > 0 { return try body() }139 try exec("BEGIN IMMEDIATE")140 depth += 1141 do {142 let r = try body()143 depth -= 1144 try exec("COMMIT")145 return r146 } catch {147 depth -= 1148 try? exec("ROLLBACK")149 throw error150 }151 }152}153