SPB Git

Repository Public

SQLiteDatabase.swift 8.9 KB 205 lines swift

1// ORIX DJ2// File: Packages/ORIXDJCore/Sources/ORIXLibrary/SQLiteDatabase.swift3// Description: Minimal wrapper over the SQLite3 C API: connection, cached prepared statements, typed binding, transactions.4//5// Author: Simon-Pierre Boucher6// Contact: contact@spboucher.ai78import Foundation9import SQLite31011/// A value bound to a SQL parameter. User text always travels through this type, never through the SQL string.12public enum SQLiteValue: Sendable, Equatable {13    case null14    case integer(Int64)15    case real(Double)16    case text(String)17    case blob(Data)1819    static func optional(_ value: Double?) -> SQLiteValue { value.map { .real($0) } ?? .null }20    static func optional(_ value: Int?) -> SQLiteValue { value.map { .integer(Int64($0)) } ?? .null }21    static func optional(_ value: String?) -> SQLiteValue { value.map { .text($0) } ?? .null }22    static func optional(_ value: Data?) -> SQLiteValue { value.map { .blob($0) } ?? .null }23    static func optional(_ value: Date?) -> SQLiteValue { value.map { .real($0.timeIntervalSinceReferenceDate) } ?? .null }24    static func date(_ value: Date) -> SQLiteValue { .real(value.timeIntervalSinceReferenceDate) }25    static func bool(_ value: Bool) -> SQLiteValue { .integer(value ? 1 : 0) }26    static func int(_ value: Int) -> SQLiteValue { .integer(Int64(value)) }27}2829private let sqliteTransient = unsafeBitCast(-1, to: sqlite3_destructor_type.self)3031/// Read access to the current row of a statement.32struct SQLiteRow {33    fileprivate let statement: OpaquePointer3435    func isNull(_ index: Int32) -> Bool { sqlite3_column_type(statement, index) == SQLITE_NULL }36    func int64(_ index: Int32) -> Int64 { sqlite3_column_int64(statement, index) }37    func int(_ index: Int32) -> Int { Int(sqlite3_column_int64(statement, index)) }38    func bool(_ index: Int32) -> Bool { sqlite3_column_int64(statement, index) != 0 }39    func double(_ index: Int32) -> Double { sqlite3_column_double(statement, index) }40    func date(_ index: Int32) -> Date { Date(timeIntervalSinceReferenceDate: sqlite3_column_double(statement, index)) }41    func string(_ index: Int32) -> String {42        guard let pointer = sqlite3_column_text(statement, index) else { return "" }43        return String(cString: pointer)44    }45    func data(_ index: Int32) -> Data {46        let count = Int(sqlite3_column_bytes(statement, index))47        guard count > 0, let pointer = sqlite3_column_blob(statement, index) else { return Data() }48        return Data(bytes: pointer, count: count)49    }50    func optionalInt(_ index: Int32) -> Int? { isNull(index) ? nil : int(index) }51    func optionalInt64(_ index: Int32) -> Int64? { isNull(index) ? nil : int64(index) }52    func optionalDouble(_ index: Int32) -> Double? { isNull(index) ? nil : double(index) }53    func optionalString(_ index: Int32) -> String? { isNull(index) ? nil : string(index) }54    func optionalData(_ index: Int32) -> Data? { isNull(index) ? nil : data(index) }55    func optionalDate(_ index: Int32) -> Date? { isNull(index) ? nil : date(index) }56}5758/// One SQLite connection. Not thread-safe by itself: `LibraryStore` serialises every access.59final class SQLiteDatabase {60    private var handle: OpaquePointer?61    private var statements: [String: OpaquePointer] = [:]62    private var transactionDepth = 06364    /// `path` may be a file path, ":memory:" or "" (private temporary database).65    init(path: String) throws {66        var connection: OpaquePointer?67        let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX68        let code = sqlite3_open_v2(path, &connection, flags, nil)69        guard code == SQLITE_OK, let connection else {70            let message = connection.map { String(cString: sqlite3_errmsg($0)) } ?? "cannot open database"71            if let connection { sqlite3_close_v2(connection) }72            throw LibraryError.sqlite(code: code, message: message)73        }74        handle = connection75        sqlite3_extended_result_codes(connection, 1)76        sqlite3_busy_timeout(connection, 5_000)77    }7879    deinit { close() }8081    var isOpen: Bool { handle != nil }8283    func close() {84        guard let handle else { return }85        for statement in statements.values { sqlite3_finalize(statement) }86        statements.removeAll()87        sqlite3_close_v2(handle)88        self.handle = nil89    }9091    private func connection() throws -> OpaquePointer {92        guard let handle else { throw LibraryError.closed }93        return handle94    }9596    private func error(_ code: Int32, _ db: OpaquePointer) -> LibraryError {97        .sqlite(code: code, message: String(cString: sqlite3_errmsg(db)))98    }99100    /// Runs one or several statements without parameters (schema, pragmas).101    func execute(_ sql: String) throws {102        let db = try connection()103        let code = sqlite3_exec(db, sql, nil, nil, nil)104        guard code == SQLITE_OK else { throw error(code, db) }105    }106107    private func prepared(_ sql: String, _ db: OpaquePointer) throws -> OpaquePointer {108        if let cached = statements[sql] { return cached }109        var statement: OpaquePointer?110        let code = sqlite3_prepare_v2(db, sql, -1, &statement, nil)111        guard code == SQLITE_OK, let statement else { throw error(code, db) }112        if statements.count > 256 {113            for old in statements.values { sqlite3_finalize(old) }114            statements.removeAll()115        }116        statements[sql] = statement117        return statement118    }119120    private func bind(_ values: [SQLiteValue], to statement: OpaquePointer, _ db: OpaquePointer) throws {121        let expected = Int(sqlite3_bind_parameter_count(statement))122        guard expected == values.count else {123            throw LibraryError.invalidArgument("SQL expects \(expected) parameters, \(values.count) given")124        }125        for (offset, value) in values.enumerated() {126            let index = Int32(offset + 1)127            let code: Int32128            switch value {129            case .null: code = sqlite3_bind_null(statement, index)130            case let .integer(number): code = sqlite3_bind_int64(statement, index, number)131            case let .real(number): code = sqlite3_bind_double(statement, index, number)132            case let .text(text): code = sqlite3_bind_text(statement, index, text, -1, sqliteTransient)133            case let .blob(data):134                if data.isEmpty {135                    code = sqlite3_bind_zeroblob(statement, index, 0)136                } else {137                    code = data.withUnsafeBytes { buffer in138                        sqlite3_bind_blob64(statement, index, buffer.baseAddress, sqlite3_uint64(buffer.count), sqliteTransient)139                    }140                }141            }142            guard code == SQLITE_OK else { throw error(code, db) }143        }144    }145146    /// Runs a statement that returns no rows. Returns the number of changed rows.147    @discardableResult148    func run(_ sql: String, _ values: [SQLiteValue] = []) throws -> Int {149        let db = try connection()150        let statement = try prepared(sql, db)151        defer { sqlite3_reset(statement); sqlite3_clear_bindings(statement) }152        try bind(values, to: statement, db)153        var code = sqlite3_step(statement)154        while code == SQLITE_ROW { code = sqlite3_step(statement) }155        guard code == SQLITE_DONE else { throw error(code, db) }156        return Int(sqlite3_changes(db))157    }158159    /// Runs a query and maps every row. `map` must not use the database.160    func query<T>(_ sql: String, _ values: [SQLiteValue] = [], map: (SQLiteRow) throws -> T) throws -> [T] {161        let db = try connection()162        let statement = try prepared(sql, db)163        defer { sqlite3_reset(statement); sqlite3_clear_bindings(statement) }164        try bind(values, to: statement, db)165        var result: [T] = []166        let row = SQLiteRow(statement: statement)167        while true {168            let code = sqlite3_step(statement)169            if code == SQLITE_ROW {170                result.append(try map(row))171            } else if code == SQLITE_DONE {172                return result173            } else {174                throw error(code, db)175            }176        }177    }178179    func first<T>(_ sql: String, _ values: [SQLiteValue] = [], map: (SQLiteRow) throws -> T) throws -> T? {180        try query(sql, values, map: map).first181    }182183    func scalarInt(_ sql: String, _ values: [SQLiteValue] = []) throws -> Int64 {184        try first(sql, values) { $0.int64(0) } ?? 0185    }186187    var lastInsertRowID: Int64 { handle.map { sqlite3_last_insert_rowid($0) } ?? 0 }188189    /// Runs `body` atomically. Nested calls join the outer transaction.190    func transaction<T>(_ body: () throws -> T) throws -> T {191        if transactionDepth > 0 { return try body() }192        try execute("BEGIN IMMEDIATE")193        transactionDepth = 1194        defer { transactionDepth = 0 }195        do {196            let value = try body()197            try execute("COMMIT")198            return value199        } catch {200            try? execute("ROLLBACK")201            throw error202        }203    }204}205
Files main
Menu

Theme