SPB Git

Repository Public

SQLite.swift 6.3 KB 132 lines swift

1// Author: Simon-Pierre Boucher — contact@spboucher.ai2// File: Core/Persistence/SQLite.swift3// Description: Thin wrapper over one SQLite connection (statements cache, typed rows, transactions).4import Foundation5import SQLite367public struct SQLiteError: Error, CustomStringConvertible, Sendable {8    public let code: Int329    public let message: String10    public var description: String { "SQLite \(code): \(message)" }11}1213public enum SQLValue: Sendable, Equatable {14    case null, int(Int64), double(Double), text(String), blob(Data)15    public static func of(_ v: String?) -> SQLValue { v.map { .text($0) } ?? .null }16    public static func of(_ v: Int?) -> SQLValue { v.map { .int(Int64($0)) } ?? .null }17    public static func of(_ v: Int64?) -> SQLValue { v.map { .int($0) } ?? .null }18    public static func of(_ v: Double?) -> SQLValue { v.map { .double($0) } ?? .null }19    public static func of(_ v: Bool) -> SQLValue { .int(v ? 1 : 0) }20    public static func of(_ v: Date?) -> SQLValue { v.map { .double($0.timeIntervalSince1970) } ?? .null }21}2223/// One row of a result set, read by column position.24public struct SQLRow {25    fileprivate let stmt: OpaquePointer26    public func string(_ i: Int32) -> String? { sqlite3_column_type(stmt, i) == SQLITE_NULL ? nil : sqlite3_column_text(stmt, i).map { String(cString: $0) } }27    public func text(_ i: Int32) -> String { string(i) ?? "" }28    public func int(_ i: Int32) -> Int { Int(sqlite3_column_int64(stmt, i)) }29    public func int64(_ i: Int32) -> Int64 { sqlite3_column_int64(stmt, i) }30    public func optInt(_ i: Int32) -> Int? { sqlite3_column_type(stmt, i) == SQLITE_NULL ? nil : int(i) }31    public func double(_ i: Int32) -> Double { sqlite3_column_double(stmt, i) }32    public func optDouble(_ i: Int32) -> Double? { sqlite3_column_type(stmt, i) == SQLITE_NULL ? nil : double(i) }33    public func bool(_ i: Int32) -> Bool { sqlite3_column_int64(stmt, i) != 0 }34    public func date(_ i: Int32) -> Date { Date(timeIntervalSince1970: double(i)) }35}3637/// Thin wrapper over one SQLite connection. Not thread-safe by itself: it is owned by an actor (LibraryStore).38public final class SQLiteConnection {39    private var db: OpaquePointer?40    private var cache: [String: OpaquePointer] = [:]41    private static let transient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)4243    public init(path: String) throws {44        var handle: OpaquePointer?45        let rc = sqlite3_open_v2(path, &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nil)46        guard rc == SQLITE_OK, let handle else {47            let msg = handle.map { String(cString: sqlite3_errmsg($0)) } ?? "cannot open"48            if let handle { sqlite3_close(handle) }49            throw SQLiteError(code: rc, message: msg)50        }51        db = handle52        sqlite3_busy_timeout(handle, 5000)53        try exec("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = OFF; PRAGMA temp_store = MEMORY;")54    }5556    deinit { close() }5758    public func close() {59        for (_, s) in cache { sqlite3_finalize(s) }60        cache.removeAll()61        if let db { sqlite3_close_v2(db) }62        db = nil63    }6465    private func fail(_ rc: Int32) -> SQLiteError { SQLiteError(code: rc, message: db.map { String(cString: sqlite3_errmsg($0)) } ?? "closed") }6667    public func exec(_ sql: String) throws {68        guard let db else { throw SQLiteError(code: SQLITE_MISUSE, message: "closed") }69        var err: UnsafeMutablePointer<CChar>?70        let rc = sqlite3_exec(db, sql, nil, nil, &err)71        if rc != SQLITE_OK { let m = err.map { String(cString: $0) } ?? "error"; sqlite3_free(err); throw SQLiteError(code: rc, message: m) }72    }7374    private func prepare(_ sql: String) throws -> OpaquePointer {75        if let s = cache[sql] { sqlite3_reset(s); sqlite3_clear_bindings(s); return s }76        guard let db else { throw SQLiteError(code: SQLITE_MISUSE, message: "closed") }77        var s: OpaquePointer?78        let rc = sqlite3_prepare_v2(db, sql, -1, &s, nil)79        guard rc == SQLITE_OK, let s else { throw fail(rc) }80        if cache.count > 200 { for (_, old) in cache { sqlite3_finalize(old) }; cache.removeAll() }81        cache[sql] = s82        return s83    }8485    private func bind(_ s: OpaquePointer, _ params: [SQLValue]) throws {86        for (i, p) in params.enumerated() {87            let idx = Int32(i + 1)88            let rc: Int3289            switch p {90            case .null: rc = sqlite3_bind_null(s, idx)91            case .int(let v): rc = sqlite3_bind_int64(s, idx, v)92            case .double(let v): rc = sqlite3_bind_double(s, idx, v)93            case .text(let v): rc = sqlite3_bind_text(s, idx, v, -1, Self.transient)94            case .blob(let v): rc = v.withUnsafeBytes { sqlite3_bind_blob(s, idx, $0.baseAddress, Int32(v.count), Self.transient) }95            }96            if rc != SQLITE_OK { throw fail(rc) }97        }98    }99100    /// Runs a statement that returns no rows. Returns the number of rows changed.101    @discardableResult102    public func run(_ sql: String, _ params: [SQLValue] = []) throws -> Int {103        let s = try prepare(sql)104        try bind(s, params)105        let rc = sqlite3_step(s)106        sqlite3_reset(s)107        guard rc == SQLITE_DONE || rc == SQLITE_ROW else { throw fail(rc) }108        return Int(sqlite3_changes(db))109    }110111    public func query<T>(_ sql: String, _ params: [SQLValue] = [], _ map: (SQLRow) throws -> T) throws -> [T] {112        let s = try prepare(sql)113        try bind(s, params)114        defer { sqlite3_reset(s) }115        var out: [T] = []116        while true {117            let rc = sqlite3_step(s)118            if rc == SQLITE_ROW { out.append(try map(SQLRow(stmt: s))) } else if rc == SQLITE_DONE { break } else { throw fail(rc) }119        }120        return out121    }122123    public func scalarInt(_ sql: String, _ params: [SQLValue] = []) throws -> Int { try query(sql, params) { $0.int(0) }.first ?? 0 }124    public func scalarText(_ sql: String, _ params: [SQLValue] = []) throws -> String? { try query(sql, params) { $0.string(0) }.first ?? nil }125    public var lastInsertRowID: Int64 { db.map { sqlite3_last_insert_rowid($0) } ?? 0 }126127    public func transaction<T>(_ body: () throws -> T) throws -> T {128        try exec("BEGIN IMMEDIATE")129        do { let r = try body(); try exec("COMMIT"); return r } catch { try? exec("ROLLBACK"); throw error }130    }131}132
Files main
Menu

Theme