SPB Git

Repository Public

SQLite.swift 10.3 KB 255 lines swift

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
Files main
Menu

Theme