Skip to content

A native macOS / windows / Linux database IDE built with Swift and AppKit. Connect to PostgreSQL, MySQL, SQLite, and Redis from a single app with a fast, keyboard-driven interface.

C++
18.1 KB
Raw Blame
// SQLiteAdapter.swift
// Gridex
//
// SQLite database adapter using the sqlite3 C API.
// Thread-safe via serial DispatchQueue.

import Foundation
import SQLite3

final class SQLiteAdapter: DatabaseAdapter, SchemaInspectable, @unchecked Sendable {
    let databaseType: DatabaseType = .sqlite
    private(set) var isConnected: Bool = false
    private var db: OpaquePointer?
    private var filePath: String?
    private let queue = DispatchQueue(label: "com.gridex.sqlite", qos: .userInitiated)

    deinit {
        if let db { sqlite3_close_v2(db) }
    }

    // MARK: - Connection

    func connect(config: ConnectionConfig, password: String?) async throws {
        guard let path = config.filePath else {
            throw GridexError.connectionFailed(underlying: NSError(domain: "SQLite", code: -1, userInfo: [NSLocalizedDescriptionKey: "No file path"]))
        }
        try await withCheckedThrowingContinuation { (cont: CheckedContinuation<Void, Error>) in
            queue.async {
                let flags = SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX
                var dbPointer: OpaquePointer?
                let rc = sqlite3_open_v2(path, &dbPointer, flags, nil)
                if rc != SQLITE_OK {
                    let msg = dbPointer.flatMap { String(cString: sqlite3_errmsg($0)) } ?? "Unknown"
                    sqlite3_close_v2(dbPointer)
                    cont.resume(throwing: GridexError.connectionFailed(underlying: NSError(domain: "SQLite", code: Int(rc), userInfo: [NSLocalizedDescriptionKey: msg])))
                    return
                }
                self.db = dbPointer
                self.filePath = path
                self.isConnected = true
                sqlite3_exec(dbPointer, "PRAGMA journal_mode=WAL", nil, nil, nil)
                sqlite3_exec(dbPointer, "PRAGMA foreign_keys=ON", nil, nil, nil)
                cont.resume()
            }
        }
    }

    func disconnect() async throws {
        try await withCheckedThrowingContinuation { (cont: CheckedContinuation<Void, Error>) in
            queue.async {
                if let db = self.db {
                    sqlite3_close_v2(db)
                    self.db = nil
                }
                self.isConnected = false
                cont.resume()
            }
        }
    }

    func testConnection(config: ConnectionConfig, password: String?) async throws -> Bool {
        guard let path = config.filePath else {
            throw GridexError.connectionFailed(underlying: NSError(domain: "Gridex", code: -1, userInfo: [NSLocalizedDescriptionKey: "No file path specified"]))
        }
        var testDb: OpaquePointer?
        let rc = sqlite3_open_v2(path, &testDb, SQLITE_OPEN_READONLY, nil)
        defer { sqlite3_close_v2(testDb) }
        if rc != SQLITE_OK {
            let msg = testDb.flatMap { String(cString: sqlite3_errmsg($0)) } ?? "Unknown SQLite error"
            throw GridexError.connectionFailed(underlying: NSError(domain: "SQLite", code: Int(rc), userInfo: [NSLocalizedDescriptionKey: msg]))
        }
        return true
    }

    // MARK: - Query Execution

    func execute(query: String, parameters: [QueryParameter]?) async throws -> QueryResult {
        try ensureConnected()
        let startTime = CFAbsoluteTimeGetCurrent()

        return try await withCheckedThrowingContinuation { cont in
            queue.async {
                do {
                    let result = try self.executeSync(sql: query, parameters: parameters, startTime: startTime)
                    cont.resume(returning: result)
                } catch {
                    cont.resume(throwing: error)
                }
            }
        }
    }

    func executeRaw(sql: String) async throws -> QueryResult {
        try await execute(query: sql, parameters: nil)
    }

    private func executeSync(sql: String, parameters: [QueryParameter]?, startTime: CFAbsoluteTime) throws -> QueryResult {
        guard let db else { throw GridexError.queryExecutionFailed("No database connection") }

        var stmt: OpaquePointer?
        guard sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK, let stmt else {
            let msg = String(cString: sqlite3_errmsg(db))
            throw GridexError.queryExecutionFailed(msg)
        }
        defer { sqlite3_finalize(stmt) }

        // Bind parameters
        if let parameters {
            for (i, param) in parameters.enumerated() {
                bindValue(param.value, to: stmt, at: Int32(i + 1))
            }
        }

        // Detect query type
        let upper = sql.trimmingCharacters(in: .whitespacesAndNewlines).uppercased()
        let queryType: QueryType =
            upper.hasPrefix("SELECT") || upper.hasPrefix("PRAGMA") || upper.hasPrefix("EXPLAIN") ? .select :
            upper.hasPrefix("INSERT") ? .insert :
            upper.hasPrefix("UPDATE") ? .update :
            upper.hasPrefix("DELETE") ? .delete :
            upper.hasPrefix("CREATE") || upper.hasPrefix("ALTER") || upper.hasPrefix("DROP") ? .ddl : .other

        // SELECT-like: read all rows
        if queryType == .select {
            let colCount = sqlite3_column_count(stmt)
            var columns: [ColumnHeader] = []
            for i in 0..<colCount {
                let name = String(cString: sqlite3_column_name(stmt, i))
                let declType = sqlite3_column_decltype(stmt, i).map { String(cString: $0) } ?? "TEXT"
                columns.append(ColumnHeader(name: name, dataType: declType))
            }

            var rows: [[RowValue]] = []
            while sqlite3_step(stmt) == SQLITE_ROW {
                var row: [RowValue] = []
                for i in 0..<colCount {
                    row.append(readColumn(stmt: stmt, index: i))
                }
                rows.append(row)
            }

            let duration = CFAbsoluteTimeGetCurrent() - startTime
            return QueryResult(columns: columns, rows: rows, rowsAffected: rows.count, executionTime: duration, queryType: queryType)
        }

        // Non-SELECT: execute and return affected count
        let stepResult = sqlite3_step(stmt)
        guard stepResult == SQLITE_DONE else {
            throw GridexError.queryExecutionFailed(String(cString: sqlite3_errmsg(db)))
        }

        let duration = CFAbsoluteTimeGetCurrent() - startTime
        return QueryResult(columns: [], rows: [], rowsAffected: Int(sqlite3_changes(db)), executionTime: duration, queryType: queryType)
    }

    private func readColumn(stmt: OpaquePointer, index: Int32) -> RowValue {
        switch sqlite3_column_type(stmt, index) {
        case SQLITE_NULL:    return .null
        case SQLITE_INTEGER: return .integer(sqlite3_column_int64(stmt, index))
        case SQLITE_FLOAT:   return .double(sqlite3_column_double(stmt, index))
        case SQLITE_TEXT:    return .string(String(cString: sqlite3_column_text(stmt, index)))
        case SQLITE_BLOB:
            let n = sqlite3_column_bytes(stmt, index)
            if let ptr = sqlite3_column_blob(stmt, index) {
                return .data(Data(bytes: ptr, count: Int(n)))
            }
            return .null
        default: return .null
        }
    }

    private func bindValue(_ value: RowValue, to stmt: OpaquePointer, at idx: Int32) {
        let TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
        switch value {
        case .null:          sqlite3_bind_null(stmt, idx)
        case .string(let v): sqlite3_bind_text(stmt, idx, v, -1, TRANSIENT)
        case .integer(let v):sqlite3_bind_int64(stmt, idx, v)
        case .double(let v): sqlite3_bind_double(stmt, idx, v)
        case .boolean(let v):sqlite3_bind_int(stmt, idx, v ? 1 : 0)
        case .date(let v):   sqlite3_bind_text(stmt, idx, ISO8601DateFormatter().string(from: v), -1, TRANSIENT)
        case .data(let v):   _ = v.withUnsafeBytes { sqlite3_bind_blob(stmt, idx, $0.baseAddress, Int32(v.count), TRANSIENT) }
        case .json(let v):   sqlite3_bind_text(stmt, idx, v, -1, TRANSIENT)
        case .uuid(let v):   sqlite3_bind_text(stmt, idx, v.uuidString, -1, TRANSIENT)
        case .array:         sqlite3_bind_text(stmt, idx, value.description, -1, TRANSIENT)
        }
    }

    // MARK: - Schema Inspection

    func listDatabases() async throws -> [String] {
        [filePath.map { URL(fileURLWithPath: $0).lastPathComponent } ?? "main"]
    }

    func listSchemas(database: String?) async throws -> [String] { ["main"] }

    func listTables(schema: String?) async throws -> [TableInfo] {
        let result = try await executeRaw(sql: """
            SELECT name FROM sqlite_master
            WHERE type='table' AND name NOT LIKE 'sqlite_%'
            ORDER BY name
            """)
        var tables: [TableInfo] = []
        for row in result.rows {
            guard case .string(let name) = row.first else { continue }
            let count = try? await tableRowCount(table: name, schema: nil)
            tables.append(TableInfo(name: name, schema: nil, type: .table, estimatedRowCount: count))
        }
        return tables
    }

    func listViews(schema: String?) async throws -> [ViewInfo] {
        let result = try await executeRaw(sql: """
            SELECT name, sql FROM sqlite_master WHERE type='view' ORDER BY name
            """)
        return result.rows.compactMap { row in
            guard case .string(let name) = row.first else { return nil }
            return ViewInfo(name: name, schema: nil, definition: row.count > 1 ? row[1].stringValue : nil, isMaterialized: false)
        }
    }

    func describeTable(name: String, schema: String?) async throws -> TableDescription {
        let columns = try await describeColumns(table: name)
        let indexes = try await listIndexes(table: name, schema: nil)
        let fks = try await listForeignKeys(table: name, schema: nil)
        let count = try? await tableRowCount(table: name, schema: nil)
        return TableDescription(name: name, schema: nil, columns: columns, indexes: indexes, foreignKeys: fks, constraints: [], comment: nil, estimatedRowCount: count)
    }

    private func describeColumns(table: String) async throws -> [ColumnInfo] {
        let result = try await executeRaw(sql: "PRAGMA table_info(\(q(table)))")
        return result.rows.enumerated().map { idx, row in
            let cid = row[0].stringValue ?? "\(idx)"
            let name = row[1].stringValue ?? ""
            let type = row[2].stringValue ?? "TEXT"
            let notNull = row[3].stringValue == "1"
            let defVal = row[4].isNull ? nil : row[4].stringValue
            let pk = row[5].stringValue != "0"
            return ColumnInfo(
                name: name, dataType: type, isNullable: !notNull, defaultValue: defVal,
                isPrimaryKey: pk, isAutoIncrement: pk && type.uppercased() == "INTEGER",
                comment: nil, ordinalPosition: Int(cid) ?? idx, characterMaxLength: nil
            )
        }
    }

    func listIndexes(table: String, schema: String?) async throws -> [IndexInfo] {
        let escapedTable = table.replacingOccurrences(of: "'", with: "''")
        let sql = """
            SELECT il.name, il.\"unique\", GROUP_CONCAT(ii.name) AS columns
            FROM pragma_index_list('\(escapedTable)') il
            JOIN pragma_index_info(il.name) ii
            GROUP BY il.name, il.\"unique\"
            ORDER BY il.name, ii.seqno
            """
        let result = try await executeRaw(sql: sql)
        return result.rows.map { row in
            let name = row[0].stringValue ?? ""
            let unique = row[1].stringValue == "1"
            let cols = (row[2].stringValue ?? "").split(separator: ",").map(String.init)
            return IndexInfo(name: name, columns: cols, isUnique: unique, type: "btree", tableName: table)
        }
    }

    func listForeignKeys(table: String, schema: String?) async throws -> [ForeignKeyInfo] {
        let result = try await executeRaw(sql: "PRAGMA foreign_key_list(\(q(table)))")
        return result.rows.map { row in
            ForeignKeyInfo(
                name: nil,
                columns: [row[3].stringValue ?? ""],
                referencedTable: row[2].stringValue ?? "",
                referencedColumns: [row[4].stringValue ?? ""],
                onDelete: ForeignKeyAction(rawValue: row[6].stringValue ?? "NO ACTION") ?? .noAction,
                onUpdate: ForeignKeyAction(rawValue: row[5].stringValue ?? "NO ACTION") ?? .noAction
            )
        }
    }

    func listFunctions(schema: String?) async throws -> [String] {
        return []
    }

    func getFunctionSource(name: String, schema: String?) async throws -> String {
        throw GridexError.queryExecutionFailed("SQLite does not support stored functions")
    }

    // MARK: - Data Manipulation

    func insertRow(table: String, schema: String?, values: [String: RowValue]) async throws -> QueryResult {
        let cols = values.keys.map { q($0) }.joined(separator: ", ")
        let placeholders = values.keys.map { _ in "?" }.joined(separator: ", ")
        return try await execute(query: "INSERT INTO \(q(table)) (\(cols)) VALUES (\(placeholders))",
                                 parameters: values.values.map { QueryParameter($0) })
    }

    func updateRow(table: String, schema: String?, set values: [String: RowValue], where conditions: [String: RowValue]) async throws -> QueryResult {
        let setClauses = values.keys.map { "\(q($0)) = ?" }.joined(separator: ", ")
        let whereClauses = conditions.keys.map { "\(q($0)) = ?" }.joined(separator: " AND ")
        let params = (Array(values.values) + Array(conditions.values)).map { QueryParameter($0) }
        return try await execute(query: "UPDATE \(q(table)) SET \(setClauses) WHERE \(whereClauses)", parameters: params)
    }

    func deleteRow(table: String, schema: String?, where conditions: [String: RowValue]) async throws -> QueryResult {
        let whereClauses = conditions.keys.map { "\(q($0)) = ?" }.joined(separator: " AND ")
        return try await execute(query: "DELETE FROM \(q(table)) WHERE \(whereClauses)",
                                 parameters: conditions.values.map { QueryParameter($0) })
    }

    func beginTransaction() async throws { _ = try await executeRaw(sql: "BEGIN TRANSACTION") }
    func commitTransaction() async throws { _ = try await executeRaw(sql: "COMMIT") }
    func rollbackTransaction() async throws { _ = try await executeRaw(sql: "ROLLBACK") }

    // MARK: - Pagination

    func fetchRows(table: String, schema: String?, columns: [String]?, where filter: FilterExpression?, orderBy: [QuerySortDescriptor]?, limit: Int, offset: Int) async throws -> QueryResult {
        let d = SQLDialect.sqlite
        let colList = columns?.map { d.quoteIdentifier($0) }.joined(separator: ", ") ?? "*"
        var sql = "SELECT \(colList) FROM \(d.quoteIdentifier(table))"
        if let filter, !filter.conditions.isEmpty {
            sql += " WHERE \(filter.toSQL(dialect: d))"
        }
        if let orderBy, !orderBy.isEmpty {
            sql += " ORDER BY " + orderBy.map { $0.toSQL(dialect: d) }.joined(separator: ", ")
        }
        sql += " LIMIT \(limit) OFFSET \(offset)"
        return try await executeRaw(sql: sql)
    }

    func serverVersion() async throws -> String {
        let r = try await executeRaw(sql: "SELECT sqlite_version()")
        return "SQLite " + (r.rows.first?.first?.stringValue ?? "")
    }

    func currentDatabase() async throws -> String? { filePath }

    // MARK: - SchemaInspectable

    func fullSchemaSnapshot(database: String?) async throws -> SchemaSnapshot {
        let tables = try await listTables(schema: nil)
        var descs: [TableDescription] = []
        for t in tables { descs.append(try await describeTable(name: t.name, schema: nil)) }
        let views = try await listViews(schema: nil)
        return SchemaSnapshot(
            databaseName: filePath.map { URL(fileURLWithPath: $0).lastPathComponent } ?? "main",
            databaseType: .sqlite,
            schemas: [SchemaInfo(name: "main", tables: descs, views: views, functions: [], enums: [])],
            capturedAt: Date()
        )
    }

    func columnStatistics(table: String, schema: String?, sampleSize: Int) async throws -> [ColumnStatistics] {
        let cols = try await describeColumns(table: table)
        var stats: [ColumnStatistics] = []
        for col in cols {
            let r = try await executeRaw(sql: """
                SELECT COUNT(DISTINCT \(q(col.name))),
                       CAST(SUM(CASE WHEN \(q(col.name)) IS NULL THEN 1 ELSE 0 END) AS REAL) / MAX(COUNT(*), 1),
                       MIN(\(q(col.name))), MAX(\(q(col.name)))
                FROM \(q(table))
                """)
            if let row = r.rows.first {
                stats.append(ColumnStatistics(
                    columnName: col.name,
                    distinctCount: row[0].stringValue.flatMap(Int.init),
                    nullRatio: row[1].stringValue.flatMap(Double.init),
                    topValues: nil,
                    minValue: row[2].stringValue,
                    maxValue: row[3].stringValue
                ))
            }
        }
        return stats
    }

    func tableRowCount(table: String, schema: String?) async throws -> Int {
        let r = try await executeRaw(sql: "SELECT COUNT(*) FROM \(q(table))")
        guard let row = r.rows.first, case .integer(let n) = row.first else { return 0 }
        return Int(n)
    }

    func tableSizeBytes(table: String, schema: String?) async throws -> Int64? { nil }
    func queryStatistics() async throws -> [QueryStatisticsEntry] { [] }

    func primaryKeyColumns(table: String, schema: String?) async throws -> [String] {
        let r = try await executeRaw(sql: "PRAGMA table_info(\(q(table)))")
        // PRAGMA table_info columns: cid, name, type, notnull, dflt_value, pk
        return r.rows.compactMap { row in
            guard row.count >= 6, let pk = row[5].intValue, pk > 0 else { return nil }
            return row[1].stringValue
        }
    }

    // MARK: - Helpers

    private func ensureConnected() throws {
        guard isConnected, db != nil else {
            throw GridexError.queryExecutionFailed("Not connected to database")
        }
    }

    /// Shorthand for quoting identifiers
    private func q(_ name: String) -> String {
        "\"\(name.replacingOccurrences(of: "\"", with: "\"\""))\""
    }
}