import Foundation import SQLite3 private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self) public enum SQLiteValue: Sendable, Hashable { case text(String) case int(Int) case double(Double) case null public static func bool(_ value: Bool) -> SQLiteValue { .int(value ? 1 : 0) } public static func dateKey(_ date: Date) -> SQLiteValue { .text(DayMath.key(date)) } public static func optionalText(_ value: String?) -> SQLiteValue { value.map { .text($0) } ?? .null } } public struct SQLiteRow: Sendable { public let values: [String: SQLiteValue] public func string(_ key: String) -> String? { guard case .text(let value)? = values[key] else { return nil } return value } public func string(_ key: String, default defaultValue: String) -> String { string(key) ?? defaultValue } public func int(_ key: String) -> Int? { switch values[key] { case .int(let value): return value case .text(let value): return Int(value) case .double(let value): return Int(value) default: return nil } } public func int(_ key: String, default defaultValue: Int) -> Int { int(key) ?? defaultValue } public func double(_ key: String) -> Double? { switch values[key] { case .double(let value): return value case .int(let value): return Double(value) case .text(let value): return Double(value) default: return nil } } public func bool(_ key: String, default defaultValue: Bool = false) -> Bool { guard let value = int(key) else { return defaultValue } return value != 0 } public func date(_ key: String) -> Date? { guard let raw = string(key) else { return nil } return DayMath.date(fromKey: raw) } } public struct SQLiteError: LocalizedError { public var message: String public var code: Int32 public var errorDescription: String? { "SQLite \(code): \(message)" } } public final class SQLiteDatabase { private var handle: OpaquePointer? private let lock = NSRecursiveLock() public let path: String public init(path: String) throws { self.path = path var db: OpaquePointer? let flags = SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX let result = sqlite3_open_v2(path, &db, flags, nil) guard result == SQLITE_OK, let db else { let message = db.map { String(cString: sqlite3_errmsg($0)) } ?? "Unbekannter Fehler" if let db { sqlite3_close(db) } throw SQLiteError(message: message, code: result) } self.handle = db sqlite3_busy_timeout(db, 5_000) if path != ":memory:" { try execute("PRAGMA journal_mode=WAL;") } try execute("PRAGMA foreign_keys=ON;") try execute("PRAGMA synchronous=NORMAL;") } deinit { if let handle { sqlite3_close(handle) } } public func execute(_ sql: String) throws { lock.lock() defer { lock.unlock() } guard let handle else { throw SQLiteError(message: "Datenbank geschlossen", code: SQLITE_MISUSE) } var errorMessage: UnsafeMutablePointer? let result = sqlite3_exec(handle, sql, nil, nil, &errorMessage) if result != SQLITE_OK { let message = errorMessage.map { String(cString: $0) } ?? "Unbekannter Fehler" sqlite3_free(errorMessage) throw SQLiteError(message: message, code: result) } } public func run(_ sql: String, _ params: [SQLiteValue] = []) throws { lock.lock() defer { lock.unlock() } let statement = try prepare(sql) defer { sqlite3_finalize(statement) } try bind(params, to: statement) let result = sqlite3_step(statement) guard result == SQLITE_DONE || result == SQLITE_ROW else { throw currentError(code: result) } } public func query(_ sql: String, _ params: [SQLiteValue] = []) throws -> [SQLiteRow] { lock.lock() defer { lock.unlock() } let statement = try prepare(sql) defer { sqlite3_finalize(statement) } try bind(params, to: statement) var rows: [SQLiteRow] = [] while true { let result = sqlite3_step(statement) if result == SQLITE_DONE { break } guard result == SQLITE_ROW else { throw currentError(code: result) } var values: [String: SQLiteValue] = [:] for index in 0.. Int { guard let row = try query(sql, params).first else { return 0 } if let value = row.int("value") { return value } guard case .int(let first)? = row.values.values.first else { return 0 } return first } public var lastInsertRowID: Int64 { lock.lock() defer { lock.unlock() } guard let handle else { return 0 } return sqlite3_last_insert_rowid(handle) } @discardableResult public func transaction(_ body: () throws -> T) throws -> T { lock.lock() defer { lock.unlock() } try execute("BEGIN IMMEDIATE;") do { let value = try body() try execute("COMMIT;") return value } catch { try? execute("ROLLBACK;") throw error } } private func prepare(_ sql: String) throws -> OpaquePointer { guard let handle else { throw SQLiteError(message: "Datenbank geschlossen", code: SQLITE_MISUSE) } var statement: OpaquePointer? let result = sqlite3_prepare_v2(handle, sql, -1, &statement, nil) guard result == SQLITE_OK, let statement else { throw currentError(code: result) } return statement } private func bind(_ params: [SQLiteValue], to statement: OpaquePointer) throws { for (offset, value) in params.enumerated() { let index = Int32(offset + 1) let result: Int32 switch value { case .text(let string): result = sqlite3_bind_text(statement, index, string, -1, SQLITE_TRANSIENT) case .int(let int): result = sqlite3_bind_int64(statement, index, Int64(int)) case .double(let double): result = sqlite3_bind_double(statement, index, double) case .null: result = sqlite3_bind_null(statement, index) } guard result == SQLITE_OK else { throw currentError(code: result) } } } private func currentError(code: Int32) -> SQLiteError { let message = handle.map { String(cString: sqlite3_errmsg($0)) } ?? "Unbekannter Fehler" return SQLiteError(message: message, code: code) } }