Repository Public
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