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++
23.8 KB
Raw Blame
// AutocompleteProvider.swift
// Gridex
//
// Context-aware SQL autocomplete with FK-based JOIN suggestions,
// fuzzy matching, and smart ranking.

import Foundation

final class AutocompleteProvider {
    private var tableNames: [String] = []
    private var columnsByTable: [String: [ColumnInfo]] = [:]
    private var foreignKeys: [String: [ForeignKeyInfo]] = [:]
    private var allColumnNames: [String] = []
    private var recentlyUsed: [String] = []

    // MARK: - Schema Update

    func updateSchema(_ tables: [TableDescription]) {
        tableNames = tables.map(\.name).sorted()
        columnsByTable = Dictionary(uniqueKeysWithValues: tables.map { ($0.name, $0.columns) })
        foreignKeys = Dictionary(uniqueKeysWithValues: tables.map { ($0.name, $0.foreignKeys) })
        allColumnNames = Array(Set(tables.flatMap { $0.columns.map(\.name) })).sorted()
    }

    func trackUsed(_ text: String) {
        recentlyUsed.removeAll { $0 == text }
        recentlyUsed.insert(text, at: 0)
        if recentlyUsed.count > 50 { recentlyUsed.removeLast() }
    }

    // MARK: - Suggestions

    func suggestions(for context: CompletionContext) -> [CompletionItem] {
        var items: [CompletionItem]

        switch context.trigger {
        case .none:
            return []
        case .keyword:
            items = keywordSuggestions(prefix: context.prefix)
        case .table:
            items = tableSuggestions(prefix: context.prefix)
        case .column(let table):
            items = columnSuggestions(table: table, prefix: context.prefix, scopeTables: context.scopeTables)
        case .join(let fromTables):
            items = joinSuggestions(fromTables: fromTables, prefix: context.prefix)
        case .selectList(let scopeTables):
            items = selectListSuggestions(prefix: context.prefix, scopeTables: scopeTables)
        case .afterIdentifier(let clause, let scopeTables):
            items = afterIdentifierSuggestions(clause: clause, prefix: context.prefix, scopeTables: scopeTables)
        case .function:
            items = functionSuggestions(prefix: context.prefix)
        case .general:
            items = generalSuggestions(prefix: context.prefix, scopeTables: context.scopeTables)
        }

        // Boost recently used
        for i in items.indices {
            if let idx = recentlyUsed.firstIndex(of: items[i].text) {
                items[i].score += 30 - idx
            }
        }

        // Deduplicate by (text + type), keeping highest score
        var seen: [String: Int] = [:]
        var deduplicated: [CompletionItem] = []
        for item in items {
            let key = "\(item.text.lowercased())|\(item.type)"
            if let existing = seen[key] {
                if item.score > existing {
                    if let idx = deduplicated.firstIndex(where: {
                        "\($0.text.lowercased())|\($0.type)" == key
                    }) {
                        deduplicated[idx] = item
                    }
                    seen[key] = item.score
                }
            } else {
                seen[key] = item.score
                deduplicated.append(item)
            }
        }

        // Sort by score descending, then by type priority, then alphabetically
        deduplicated.sort { a, b in
            if a.score != b.score { return a.score > b.score }
            if a.type != b.type { return typeRank(a.type) < typeRank(b.type) }
            return a.text < b.text
        }

        // Show up to 20 items — let user scroll through all matches
        return Array(deduplicated.prefix(20))
    }

    private func typeRank(_ type: CompletionItem.CompletionType) -> Int {
        switch type {
        case .keyword: return 0
        case .table: return 1
        case .column: return 2
        case .function: return 3
        case .join: return 4
        }
    }

    // MARK: - Keyword Suggestions

    private func keywordSuggestions(prefix: String) -> [CompletionItem] {
        return Self.sqlKeywords.compactMap { kw in
            guard let score = fuzzyScore(prefix, kw) else { return nil }
            // Boost common clause keywords so they appear first
            let boost = Self.primaryKeywords.contains(kw) ? 100 : 0
            return CompletionItem(text: kw, type: .keyword, detail: nil, insertText: kw,
                                  score: score + boost, matchRanges: fuzzyMatchRanges(prefix, kw))
        }
    }

    /// Top-tier SQL keywords that should rank highest in suggestions.
    private static let primaryKeywords: Set<String> = [
        "SELECT", "FROM", "WHERE", "JOIN", "LEFT JOIN", "RIGHT JOIN", "INNER JOIN",
        "ORDER BY", "GROUP BY", "INSERT INTO", "UPDATE", "DELETE", "SET",
        "AND", "OR", "ON", "AS", "DISTINCT", "LIMIT", "HAVING",
        "CREATE TABLE", "ALTER TABLE", "DROP TABLE", "WITH"
    ]

    // MARK: - Table Suggestions

    private func tableSuggestions(prefix: String) -> [CompletionItem] {
        return tableNames.compactMap { name in
            guard prefix.isEmpty || fuzzyScore(prefix, name) != nil else { return nil }
            let score = prefix.isEmpty ? 0 : (fuzzyScore(prefix, name) ?? 0)
            let cols = columnsByTable[name]?.count ?? 0
            return CompletionItem(text: name, type: .table, detail: "\(cols) cols",
                                  insertText: name, score: score,
                                  matchRanges: prefix.isEmpty ? [] : fuzzyMatchRanges(prefix, name))
        }
    }

    // MARK: - Column Suggestions

    private func columnSuggestions(table: String?, prefix: String, scopeTables: [TableRef]) -> [CompletionItem] {
        var items: [CompletionItem] = []

        // Columns from the specific table (highest priority)
        if let table, let columns = columnsByTable[table] {
            for col in columns {
                guard prefix.isEmpty || fuzzyScore(prefix, col.name) != nil else { continue }
                let score = (prefix.isEmpty ? 10 : (fuzzyScore(prefix, col.name) ?? 0)) +
                            (col.isPrimaryKey ? 5 : 0)
                items.append(CompletionItem(
                    text: col.name, type: .column, detail: "\(col.dataType) · \(table)",
                    insertText: col.name, score: score,
                    matchRanges: prefix.isEmpty ? [] : fuzzyMatchRanges(prefix, col.name)
                ))
            }
        }

        // Columns from all scope tables (if no specific table or augmenting)
        if table == nil {
            let scopeTableNames = Set(scopeTables.map(\.name))
            for ref in scopeTables {
                guard let columns = columnsByTable[ref.name] else { continue }
                let qualifier = ref.alias ?? ref.name
                for col in columns {
                    guard prefix.isEmpty || fuzzyScore(prefix, col.name) != nil else { continue }
                    let score = (prefix.isEmpty ? 8 : (fuzzyScore(prefix, col.name) ?? 0)) +
                                (col.isPrimaryKey ? 5 : 0) +
                                (scopeTableNames.contains(ref.name) ? 3 : 0)
                    let detail = "\(col.dataType) · \(qualifier)"
                    items.append(CompletionItem(
                        text: col.name, type: .column, detail: detail,
                        insertText: col.name, score: score,
                        matchRanges: prefix.isEmpty ? [] : fuzzyMatchRanges(prefix, col.name)
                    ))
                }
            }
        }

        // Deduplicate by name, keeping highest score
        var seen: [String: Int] = [:]
        items = items.filter { item in
            if let existing = seen[item.text], existing >= item.score { return false }
            seen[item.text] = item.score
            return true
        }

        return items
    }

    // MARK: - JOIN Suggestions (FK-aware)

    private func joinSuggestions(fromTables: [TableRef], prefix: String) -> [CompletionItem] {
        var items: [CompletionItem] = []

        for tableRef in fromTables {
            let tableName = tableRef.name
            let alias = tableRef.alias ?? tableName

            // FK from this table → referenced tables
            if let fks = foreignKeys[tableName] {
                for fk in fks {
                    let refTable = fk.referencedTable
                    let refAlias = suggestAlias(for: refTable, existing: fromTables)
                    let onClause = zip(fk.columns, fk.referencedColumns)
                        .map { "\(alias).\($0.0) = \(refAlias).\($0.1)" }
                        .joined(separator: " AND ")
                    let insertText = "JOIN \(refTable) \(refAlias) ON \(onClause)"
                    let displayText = "JOIN \(refTable) \(refAlias) ON \(onClause)"

                    guard prefix.isEmpty || fuzzyScore(prefix, insertText) != nil ||
                          fuzzyScore(prefix, refTable) != nil else { continue }

                    items.append(CompletionItem(
                        text: displayText, type: .join,
                        detail: "FK: \(fk.columns.joined(separator: ","))",
                        insertText: insertText, score: 20,
                        matchRanges: []
                    ))
                }
            }

            // Reverse FKs: other tables referencing this table
            for (otherTable, otherFks) in foreignKeys {
                guard otherTable != tableName else { continue }
                for fk in otherFks where fk.referencedTable == tableName {
                    let refAlias = suggestAlias(for: otherTable, existing: fromTables)
                    let onClause = zip(fk.referencedColumns, fk.columns)
                        .map { "\(alias).\($0.0) = \(refAlias).\($0.1)" }
                        .joined(separator: " AND ")
                    let insertText = "JOIN \(otherTable) \(refAlias) ON \(onClause)"

                    guard prefix.isEmpty || fuzzyScore(prefix, insertText) != nil ||
                          fuzzyScore(prefix, otherTable) != nil else { continue }

                    items.append(CompletionItem(
                        text: insertText, type: .join,
                        detail: "FK: \(otherTable).\(fk.columns.joined(separator: ","))",
                        insertText: insertText, score: 18,
                        matchRanges: []
                    ))
                }
            }
        }

        // If no FK matches, fall back to table suggestions prefixed with JOIN
        if items.isEmpty {
            let tables = tableSuggestions(prefix: prefix)
            items = tables.map { item in
                CompletionItem(text: "JOIN \(item.text)", type: .join, detail: item.detail,
                               insertText: "JOIN \(item.insertText)", score: item.score,
                               matchRanges: [])
            }
        }

        return items
    }

    // MARK: - SELECT List (columns + functions + *)

    private func selectListSuggestions(prefix: String, scopeTables: [TableRef]) -> [CompletionItem] {
        var items: [CompletionItem] = []

        // Star shortcut
        if prefix.isEmpty || "*".hasPrefix(prefix) {
            items.append(CompletionItem(text: "*", type: .keyword, detail: "All columns",
                                        insertText: "*", score: 20, matchRanges: []))
        }

        // Functions (high priority in SELECT)
        items.append(contentsOf: functionSuggestions(prefix: prefix).map {
            var item = $0; item.score += 5; return item
        })

        // Columns from scope tables
        items.append(contentsOf: columnSuggestions(table: nil, prefix: prefix, scopeTables: scopeTables))

        // DISTINCT keyword
        if prefix.isEmpty || fuzzyScore(prefix, "DISTINCT") != nil {
            items.append(CompletionItem(text: "DISTINCT", type: .keyword, detail: nil,
                                        insertText: "DISTINCT", score: fuzzyScore(prefix, "DISTINCT") ?? 8,
                                        matchRanges: prefix.isEmpty ? [] : fuzzyMatchRanges(prefix, "DISTINCT")))
        }

        return items
    }

    // MARK: - After Identifier (suggest next keywords based on clause)

    private func afterIdentifierSuggestions(clause: String, prefix: String, scopeTables: [TableRef]) -> [CompletionItem] {
        // Context-aware next-keyword suggestions
        let nextKeywords: [String]

        switch clause {
        case "SELECT":
            nextKeywords = ["FROM"]
        case "FROM":
            nextKeywords = ["WHERE", "JOIN", "LEFT JOIN", "RIGHT JOIN", "INNER JOIN",
                           "FULL JOIN", "CROSS JOIN", "ORDER BY", "GROUP BY",
                           "LIMIT", "OFFSET", "UNION", "RETURNING"]
        case "JOIN":
            nextKeywords = ["ON", "WHERE", "JOIN", "LEFT JOIN", "RIGHT JOIN",
                           "ORDER BY", "GROUP BY", "LIMIT"]
        case "WHERE":
            nextKeywords = ["AND", "OR", "ORDER BY", "GROUP BY", "LIMIT",
                           "UNION", "RETURNING"]
        case "ORDER BY", "GROUP BY":
            nextKeywords = ["LIMIT", "OFFSET", "ASC", "DESC"]
        case "HAVING":
            nextKeywords = ["ORDER BY", "LIMIT"]
        case "INSERT":
            nextKeywords = ["VALUES", "SELECT", "RETURNING"]
        default:
            nextKeywords = ["SELECT", "FROM", "WHERE", "JOIN", "ORDER BY",
                           "GROUP BY", "LIMIT", "HAVING", "AND", "OR"]
        }

        return nextKeywords.compactMap { kw in
            guard prefix.isEmpty || fuzzyScore(prefix, kw) != nil else { return nil }
            let score = prefix.isEmpty ? 15 : (fuzzyScore(prefix, kw) ?? 0) + 10
            return CompletionItem(text: kw, type: .keyword, detail: nil,
                                  insertText: kw, score: score,
                                  matchRanges: prefix.isEmpty ? [] : fuzzyMatchRanges(prefix, kw))
        }
    }

    // MARK: - Function Suggestions

    private func functionSuggestions(prefix: String) -> [CompletionItem] {
        return Self.sqlFunctions.compactMap { fn in
            guard prefix.isEmpty || fuzzyScore(prefix, fn.name) != nil else { return nil }
            // Functions are slightly deprioritized vs keywords at same score
            let score = prefix.isEmpty ? 0 : ((fuzzyScore(prefix, fn.name) ?? 0) - 10)
            return CompletionItem(
                text: fn.name, type: .function, detail: fn.signature,
                insertText: fn.snippet, score: score,
                matchRanges: prefix.isEmpty ? [] : fuzzyMatchRanges(prefix, fn.name)
            )
        }
    }

    // MARK: - General (search everything)

    private func generalSuggestions(prefix: String, scopeTables: [TableRef]) -> [CompletionItem] {
        guard !prefix.isEmpty else { return [] }
        var items: [CompletionItem] = []
        items.append(contentsOf: keywordSuggestions(prefix: prefix))
        items.append(contentsOf: tableSuggestions(prefix: prefix))
        items.append(contentsOf: allColumnsSuggestions(prefix: prefix))
        items.append(contentsOf: functionSuggestions(prefix: prefix))
        return items
    }

    /// Search columns across ALL tables (not scoped to a query).
    private func allColumnsSuggestions(prefix: String) -> [CompletionItem] {
        var items: [CompletionItem] = []
        for (tableName, columns) in columnsByTable {
            for col in columns {
                guard let score = fuzzyScore(prefix, col.name) else { continue }
                items.append(CompletionItem(
                    text: col.name, type: .column,
                    detail: "\(col.dataType) · \(tableName)",
                    insertText: col.name,
                    score: score + (col.isPrimaryKey ? 3 : 0),
                    matchRanges: fuzzyMatchRanges(prefix, col.name)
                ))
            }
        }
        return items
    }

    // MARK: - Fuzzy Matching

    /// Returns a score if query matches candidate, nil if no match.
    /// Strongly favors prefix matches over fuzzy subsequence matches.
    func fuzzyScore(_ query: String, _ candidate: String) -> Int? {
        guard !query.isEmpty else { return 0 }

        let q = query.lowercased()
        let c = candidate.lowercased()

        // Exact match — highest priority
        if q == c { return 1000 }

        // Exact prefix match — very strong boost
        if c.hasPrefix(q) {
            // Shorter candidates rank higher (SELECT beats SUBSTRING for "s")
            let lengthPenalty = max(0, candidate.count - query.count)
            return 500 - lengthPenalty
        }

        // Word-boundary prefix match (e.g., "uid" matches "user_id" at "_id")
        // Check if any word in the candidate starts with the query
        let words = c.split(whereSeparator: { $0 == "_" || $0 == "." || $0 == " " })
        for word in words {
            if word.hasPrefix(q) {
                return 200 - max(0, candidate.count - query.count)
            }
        }

        // Fuzzy subsequence match — weakest priority
        let qChars = Array(q)
        let cChars = Array(c)
        var qi = 0
        var score = 0
        var consecutive = 0
        var lastMatchIndex = -2

        for (ci, ch) in cChars.enumerated() {
            guard qi < qChars.count else { break }
            if ch == qChars[qi] {
                score += 1
                if ci == lastMatchIndex + 1 {
                    consecutive += 1
                    score += consecutive
                } else {
                    consecutive = 0
                }
                if ci > 0 {
                    let prev = cChars[ci - 1]
                    if prev == "_" || prev == "." { score += 3 }
                }
                lastMatchIndex = ci
                qi += 1
            }
        }

        return qi == qChars.count ? score : nil
    }

    /// Returns ranges of matched characters for highlighting.
    func fuzzyMatchRanges(_ query: String, _ candidate: String) -> [Int] {
        let q = Array(query.lowercased())
        let c = Array(candidate.lowercased())
        var qi = 0
        var ranges: [Int] = []

        for (ci, ch) in c.enumerated() {
            guard qi < q.count else { break }
            if ch == q[qi] {
                ranges.append(ci)
                qi += 1
            }
        }
        return ranges
    }

    // MARK: - Helpers

    private func suggestAlias(for table: String, existing: [TableRef]) -> String {
        let usedAliases = Set(existing.compactMap { $0.alias ?? $0.name })
        let base = String(table.prefix(1)).lowercased()
        if !usedAliases.contains(base) { return base }
        let base2 = String(table.prefix(2)).lowercased()
        if !usedAliases.contains(base2) { return base2 }
        let base3 = String(table.prefix(3)).lowercased()
        if !usedAliases.contains(base3) { return base3 }
        return table
    }

    // MARK: - SQL Keywords & Functions

    static let sqlKeywords: [String] = [
        "SELECT", "FROM", "WHERE", "JOIN", "LEFT", "RIGHT", "INNER", "OUTER",
        "FULL", "CROSS", "ON", "AND", "OR", "NOT", "IN", "EXISTS", "BETWEEN",
        "LIKE", "ILIKE", "IS", "NULL", "AS", "ORDER", "BY", "GROUP", "HAVING",
        "LIMIT", "OFFSET", "UNION", "ALL", "DISTINCT", "INSERT", "INTO", "VALUES",
        "UPDATE", "SET", "DELETE", "CREATE", "TABLE", "ALTER", "DROP", "INDEX",
        "VIEW", "IF", "THEN", "ELSE", "END", "CASE", "WHEN", "WITH", "RECURSIVE",
        "RETURNING", "EXPLAIN", "ANALYZE", "BEGIN", "COMMIT", "ROLLBACK",
        "LEFT JOIN", "RIGHT JOIN", "INNER JOIN", "FULL JOIN", "CROSS JOIN",
        "ORDER BY", "GROUP BY", "INSERT INTO", "CREATE TABLE", "ALTER TABLE",
        "DROP TABLE", "NOT NULL", "PRIMARY KEY", "FOREIGN KEY", "REFERENCES"
    ]

    static let sqlFunctions: [(name: String, signature: String, snippet: String)] = [
        ("COUNT", "COUNT(expr) → int", "COUNT()"),
        ("SUM", "SUM(expr) → numeric", "SUM()"),
        ("AVG", "AVG(expr) → numeric", "AVG()"),
        ("MIN", "MIN(expr) → value", "MIN()"),
        ("MAX", "MAX(expr) → value", "MAX()"),
        ("COALESCE", "COALESCE(val, ...) → value", "COALESCE(, )"),
        ("NULLIF", "NULLIF(a, b) → value", "NULLIF(, )"),
        ("CAST", "CAST(expr AS type)", "CAST( AS )"),
        ("CONCAT", "CONCAT(str, ...) → text", "CONCAT(, )"),
        ("LENGTH", "LENGTH(str) → int", "LENGTH()"),
        ("UPPER", "UPPER(str) → text", "UPPER()"),
        ("LOWER", "LOWER(str) → text", "LOWER()"),
        ("TRIM", "TRIM(str) → text", "TRIM()"),
        ("SUBSTRING", "SUBSTRING(str, pos, len)", "SUBSTRING(, , )"),
        ("REPLACE", "REPLACE(str, from, to)", "REPLACE(, , )"),
        ("NOW", "NOW() → timestamp", "NOW()"),
        ("CURRENT_TIMESTAMP", "CURRENT_TIMESTAMP → timestamp", "CURRENT_TIMESTAMP"),
        ("EXTRACT", "EXTRACT(field FROM source)", "EXTRACT( FROM )"),
        ("DATE_TRUNC", "DATE_TRUNC(field, source)", "DATE_TRUNC(, )"),
        ("ROW_NUMBER", "ROW_NUMBER() OVER (...)", "ROW_NUMBER() OVER ()"),
        ("RANK", "RANK() OVER (...)", "RANK() OVER ()"),
        ("DENSE_RANK", "DENSE_RANK() OVER (...)", "DENSE_RANK() OVER ()"),
        ("LAG", "LAG(expr, offset, default)", "LAG(, 1)"),
        ("LEAD", "LEAD(expr, offset, default)", "LEAD(, 1)"),
        ("STRING_AGG", "STRING_AGG(expr, delimiter)", "STRING_AGG(, ',')"),
        ("ARRAY_AGG", "ARRAY_AGG(expr) → array", "ARRAY_AGG()"),
        ("JSON_AGG", "JSON_AGG(expr) → json", "JSON_AGG()"),
        ("JSONB_AGG", "JSONB_AGG(expr) → jsonb", "JSONB_AGG()"),
        ("ROUND", "ROUND(num, decimals)", "ROUND(, 2)"),
        ("CEIL", "CEIL(num) → int", "CEIL()"),
        ("FLOOR", "FLOOR(num) → int", "FLOOR()"),
        ("ABS", "ABS(num) → num", "ABS()"),
        ("GREATEST", "GREATEST(val, ...) → value", "GREATEST(, )"),
        ("LEAST", "LEAST(val, ...) → value", "LEAST(, )"),
        ("EXISTS", "EXISTS(subquery) → bool", "EXISTS ()"),
        ("IN", "expr IN (values)", "IN ()"),
        ("BETWEEN", "expr BETWEEN a AND b", "BETWEEN  AND "),
        ("LIKE", "expr LIKE pattern", "LIKE '%%'"),
        ("ILIKE", "expr ILIKE pattern", "ILIKE '%%'"),
        ("GEN_RANDOM_UUID", "GEN_RANDOM_UUID() → uuid", "GEN_RANDOM_UUID()"),
        ("TO_CHAR", "TO_CHAR(val, format)", "TO_CHAR(, '')"),
        ("TO_DATE", "TO_DATE(str, format)", "TO_DATE(, '')"),
    ]
}

// MARK: - Models

struct CompletionItem: Identifiable {
    let id = UUID()
    let text: String
    let type: CompletionType
    let detail: String?
    let insertText: String
    var score: Int
    var matchRanges: [Int]  // Indices of matched chars for highlighting

    enum CompletionType: Equatable {
        case keyword, table, column, function, join

        var icon: String {
            switch self {
            case .keyword: return "k"
            case .table: return "T"
            case .column: return "C"
            case .function: return "f"
            case .join: return "J"
            }
        }

        var iconColor: (r: CGFloat, g: CGFloat, b: CGFloat) {
            switch self {
            case .keyword: return (0.67, 0.33, 0.67)
            case .table: return (0.33, 0.67, 0.33)
            case .column: return (0.33, 0.53, 0.87)
            case .function: return (0.87, 0.67, 0.33)
            case .join: return (0.53, 0.33, 0.87)
            }
        }
    }
}

struct TableRef {
    let name: String
    let alias: String?
}

struct CompletionContext {
    let trigger: CompletionTrigger
    let prefix: String
    let scopeTables: [TableRef]
}

enum CompletionTrigger {
    case none               // Inside string literal, no completions
    case keyword            // Suggest SQL keywords
    case table              // Suggest table names
    case column(table: String?)  // Suggest columns (optionally scoped to table)
    case join(fromTables: [TableRef])  // Suggest FK-aware JOINs
    case selectList(scopeTables: [TableRef])  // After SELECT: *, functions, columns, DISTINCT
    case afterIdentifier(clause: String, scopeTables: [TableRef])  // After table/column name: next keywords
    case function           // Suggest SQL functions
    case general            // Mixed suggestions
}