Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

29.0 KB
Raw Blame
import type { SqlCompletionColumn, SqlCompletionTable } from "@/lib/sql/sqlCompletion";
import { getSqlCompletionContext, isOraclePseudoColumnName, isOracleSystemValueName, matchesOceanBaseCompletionCacheKey } from "@/lib/sql/sqlCompletion";
import { tokenizeSqlSemantic, tokenIsIdentifier } from "@/lib/sql/semantic/tokens";
import { executableStatementRanges, keepsOracleStyleBlockTogether, type SqlTextRange } from "@/lib/sql/sqlStatementRanges";
import { DBX_TDENGINE_TBNAME_COLUMN, isTdengineStableTableType } from "@/lib/table/tableEditing";
import type { DatabaseType, SqlColumnReference, SqlGroupByViolation, SqlReferenceAnalysis, SqlReferenceScope, SqlTableReference, SqlTextSpan } from "@/types/database";

export interface SqlSemanticDiagnostic {
  span: SqlTextSpan;
  message: string;
  severity: "error" | "warning";
}

export interface SqlSemanticDiagnosticSchema {
  tables: SqlCompletionTable[];
  columnsByTable: Map<string, SqlCompletionColumn[]>;
  missingTables?: Set<string>;
  loadedColumnTables?: Set<string>;
  sql?: string;
  databaseType?: DatabaseType;
  currentSchema?: string;
}

export interface SqlSemanticDiagnosticVisibleRange {
  from: number;
  to: number;
}

export function sqlSemanticDiagnosticRangesForViewport(sql: string, visibleRanges: readonly SqlSemanticDiagnosticVisibleRange[], databaseType?: DatabaseType, cachedStatements?: readonly SqlTextRange[], parameterOptions?: { compatibilityMode?: string }): SqlTextRange[] {
  const statements = databaseType === "sqlserver" ? sqlServerSemanticDiagnosticRanges(sql) : (cachedStatements ?? executableStatementRanges(sql, databaseType, parameterOptions));
  if (statements.length === 0 || visibleRanges.length === 0) return [];

  const selected: SqlTextRange[] = [];
  const seen = new Set<string>();
  for (const statement of statements) {
    if (keepsOracleStyleBlockTogether(statement.sql, databaseType, parameterOptions)) continue;
    if (!visibleRanges.some((visibleRange) => rangesIntersect(statement, visibleRange))) continue;
    const key = `${statement.from}:${statement.to}`;
    if (seen.has(key)) continue;
    seen.add(key);
    selected.push({ from: statement.from, to: statement.to, sql: sql.slice(statement.from, statement.to) });
  }
  return selected;
}

/**
 * SQL Server routine definition batches (`CREATE/ALTER PROCEDURE | FUNCTION`) are
 * deliberately excluded from reference diagnostics: the MsSql grammar in the
 * analyzer cannot parse the parameter list, and metadata checks inside a routine
 * body (temp tables, table variables) would report noise (see
 * `isSqlServerRoutineDefinitionBatch`). That suppression also dropped the
 * *syntax* errors of the body, so a routine that does not even compile looked
 * clean (issue #9315). Callers use these ranges to run the routine-only syntax
 * rules that survive the suppression, while reference diagnostics stay off.
 */
export function sqlServerRoutineDefinitionRangesForViewport(sql: string, visibleRanges: readonly SqlSemanticDiagnosticVisibleRange[]): SqlTextRange[] {
  if (visibleRanges.length === 0) return [];

  const selected: SqlTextRange[] = [];
  for (const batch of sqlServerBatchRanges(sql)) {
    if (!isSqlServerRoutineDefinitionBatch(batch.sql)) continue;
    if (!visibleRanges.some((visibleRange) => rangesIntersect(batch, visibleRange))) continue;
    selected.push(batch);
  }
  return selected;
}

function sqlServerSemanticDiagnosticRanges(sql: string): SqlTextRange[] {
  const ranges: SqlTextRange[] = [];
  for (const batch of sqlServerBatchRanges(sql)) {
    if (isSqlServerRoutineDefinitionBatch(batch.sql)) continue;
    const statements = executableStatementRanges(batch.sql, "sqlserver").map((statement) => ({
      from: batch.from + statement.from,
      to: batch.from + statement.to,
      sql: statement.sql,
    }));
    pushSqlServerDiagnosticBatchRanges(ranges, sql, statements);
  }
  return ranges;
}

function isSqlServerRoutineDefinitionBatch(sql: string): boolean {
  const keywords = leadingUnquotedSqlKeywords(sql, 4);
  const routineKeyword = (value: string | undefined) => value === "PROC" || value === "PROCEDURE" || value === "FUNCTION";
  return (keywords[0] === "ALTER" && routineKeyword(keywords[1])) || (keywords[0] === "CREATE" && routineKeyword(keywords[1])) || (keywords[0] === "CREATE" && keywords[1] === "OR" && keywords[2] === "ALTER" && routineKeyword(keywords[3]));
}

function leadingUnquotedSqlKeywords(sql: string, limit: number): string[] {
  const keywords: string[] = [];
  let index = 0;
  while (index < sql.length && keywords.length < limit) {
    if (/\s/.test(sql[index])) {
      index++;
      continue;
    }
    if (sql.startsWith("--", index)) {
      const carriageReturn = sql.indexOf("\r", index + 2);
      const lineFeed = sql.indexOf("\n", index + 2);
      const newline = carriageReturn < 0 ? lineFeed : lineFeed < 0 ? carriageReturn : Math.min(carriageReturn, lineFeed);
      index = newline < 0 ? sql.length : newline + 1;
      if (sql[newline] === "\r" && sql[newline + 1] === "\n") index += 1;
      continue;
    }
    if (sql.startsWith("/*", index)) {
      let depth = 1;
      index += 2;
      while (index < sql.length && depth > 0) {
        if (sql.startsWith("/*", index)) {
          depth += 1;
          index += 2;
        } else if (sql.startsWith("*/", index)) {
          depth -= 1;
          index += 2;
        } else {
          index += 1;
        }
      }
      if (depth > 0) break;
      continue;
    }
    const match = /^[A-Za-z]+/.exec(sql.slice(index));
    if (!match) break;
    keywords.push(match[0].toUpperCase());
    index += match[0].length;
  }
  return keywords;
}

function sqlServerBatchRanges(sql: string): SqlTextRange[] {
  const separators = sqlServerGoSeparatorRanges(sql);
  const ranges: SqlTextRange[] = [];
  let from = 0;
  for (const separator of separators) {
    if (separator.from > from) ranges.push({ from, to: separator.from, sql: sql.slice(from, separator.from) });
    from = separator.to;
  }
  if (from < sql.length) ranges.push({ from, to: sql.length, sql: sql.slice(from) });
  return ranges;
}

type SqlServerBatchQuote = "none" | "single" | "double" | "bracket";

interface SqlServerBatchScanState {
  blockCommentDepth: number;
  quote: SqlServerBatchQuote;
}

function sqlServerGoSeparatorRanges(sql: string): Array<{ from: number; to: number }> {
  const ranges: Array<{ from: number; to: number }> = [];
  const state: SqlServerBatchScanState = { blockCommentDepth: 0, quote: "none" };
  let lineStart = 0;
  while (lineStart <= sql.length) {
    const newline = sql.indexOf("\n", lineStart);
    const lineEnd = newline < 0 ? sql.length : newline;
    const line = sql.slice(lineStart, lineEnd);
    if (state.blockCommentDepth === 0 && state.quote === "none" && isSqlServerGoSeparatorLine(line)) {
      ranges.push({ from: lineStart, to: newline < 0 ? lineEnd : lineEnd + 1 });
    } else {
      updateSqlServerBatchScanState(line, state);
    }
    if (newline < 0) break;
    lineStart = newline + 1;
  }
  return ranges;
}

function isSqlServerGoSeparatorLine(line: string): boolean {
  let index = 0;
  while (index < line.length && /\s/.test(line[index] ?? "")) index += 1;
  if (line.slice(index, index + 2).toUpperCase() !== "GO") return false;
  index += 2;
  if (index < line.length && !/\s/.test(line[index] ?? "") && !line.startsWith("--", index) && !line.startsWith("/*", index)) return false;

  let sawWhitespace = false;
  while (index < line.length && /\s/.test(line[index] ?? "")) {
    sawWhitespace = true;
    index += 1;
  }
  if (sawWhitespace && /\d/.test(line[index] ?? "")) {
    while (index < line.length && /\d/.test(line[index] ?? "")) index += 1;
  }

  while (index < line.length) {
    while (index < line.length && /\s/.test(line[index] ?? "")) index += 1;
    if (index >= line.length || line.startsWith("--", index)) return true;
    if (!line.startsWith("/*", index)) return false;

    let depth = 1;
    index += 2;
    while (index < line.length && depth > 0) {
      if (line.startsWith("/*", index)) {
        depth += 1;
        index += 2;
      } else if (line.startsWith("*/", index)) {
        depth -= 1;
        index += 2;
      } else {
        index += 1;
      }
    }
    if (depth > 0) return false;
  }
  return true;
}

function updateSqlServerBatchScanState(line: string, state: SqlServerBatchScanState) {
  let index = 0;
  while (index < line.length) {
    if (state.blockCommentDepth > 0) {
      if (line.startsWith("/*", index)) {
        state.blockCommentDepth += 1;
        index += 2;
      } else if (line.startsWith("*/", index)) {
        state.blockCommentDepth -= 1;
        index += 2;
      } else {
        index += 1;
      }
      continue;
    }

    if (state.quote !== "none") {
      const quote = state.quote === "single" ? "'" : state.quote === "double" ? '"' : "]";
      if (line[index] !== quote) {
        index += 1;
      } else if (line[index + 1] === quote) {
        index += 2;
      } else {
        state.quote = "none";
        index += 1;
      }
      continue;
    }

    if (line.startsWith("--", index)) break;
    if (line.startsWith("/*", index)) {
      state.blockCommentDepth = 1;
      index += 2;
    } else if (line[index] === "'") {
      state.quote = "single";
      index += 1;
    } else if (line[index] === '"') {
      state.quote = "double";
      index += 1;
    } else if (line[index] === "[") {
      state.quote = "bracket";
      index += 1;
    } else {
      index += 1;
    }
  }
}

function pushSqlServerDiagnosticBatchRanges(ranges: SqlTextRange[], sql: string, batch: readonly SqlTextRange[]) {
  if (batch.length > 1 && batch.some((statement) => sqlServerStatementNeedsBatchContext(statement.sql))) {
    const from = batch[0].from;
    const to = batch[batch.length - 1].to;
    ranges.push({ from, to, sql: sql.slice(from, to) });
  } else {
    ranges.push(...batch);
  }
}

function sqlServerStatementNeedsBatchContext(sql: string): boolean {
  return /^\s*(?:WHILE|BEGIN|END|IF|ELSE|TRY|CATCH)\b/i.test(sql) || /^\s*DECLARE\s+(?:\[[^\]]+\]|"[^"]+"|[A-Z_@#][\w@$#]*)\s+CURSOR\b/i.test(sql);
}

export function buildSqlSemanticDiagnostics(analysis: SqlReferenceAnalysis, schema: SqlSemanticDiagnosticSchema): SqlSemanticDiagnostic[] {
  const diagnostics: SqlSemanticDiagnostic[] = [];
  const tables = analysis.tables.filter((table) => table.name.trim()).map((table) => (schema.databaseType === "oceanbase-oracle" && schema.sql ? oceanBaseDiagnosticTableReference(table, schema.sql) : table));
  const knownTables = new Map<string, SqlTableReference>();
  const scopesById = scopesByIdMap(analysis.scopes);
  let tdengineStableTables: Set<string> | undefined;

  for (const table of tables) {
    knownTables.set(normalizeName(table.name), table);
    if (table.alias) knownTables.set(normalizeName(table.alias), table);
    if (table.schema) knownTables.set(normalizeName(`${table.schema}.${table.name}`), table);
  }

  for (const table of tables) {
    if (isSqlVirtualTableReference(table, schema.databaseType)) continue;
    if (!schema.missingTables?.has(tableReferenceKey(table))) continue;
    diagnostics.push({
      span: trimSqlTextSpanWhitespace(schema.sql, table.span),
      message: `Unknown table ${displayTableName(table)}`,
      severity: "error",
    });
  }

  for (const column of analysis.columns) {
    if (isUnquotedOracleBuiltinReference(column, schema)) continue;
    const table = resolveColumnTable(column, tables, knownTables, schema.sql, scopesById);
    if (!table) continue;
    if (schema.missingTables?.has(tableReferenceKey(table))) continue;

    const columns = columnsForTable(table, schema.columnsByTable, schema.loadedColumnTables, schema.currentSchema);
    if (!columns) continue;

    const columnNames = new Set(columns.map((item) => normalizeName(item.name)));
    if (columnNames.has(normalizeName(column.name))) continue;
    if (schema.databaseType === "tdengine" && normalizeName(column.name) === DBX_TDENGINE_TBNAME_COLUMN) {
      tdengineStableTables ??= tdengineStableTableKeys(schema.tables);
      if (tdengineStableTables.has(tableReferenceKey(table))) continue;
    }

    // SQL engines allow SELECT projection aliases in ORDER BY/GROUP BY (and,
    // for example, DuckDB also allows them in HAVING). The reference parser
    // reports those aliases as ordinary columns, so metadata-only validation
    // would otherwise show a false "Unknown column" diagnostic. Reuse the
    // completion context, which already extracts aliases and knows when they
    // are visible, to keep diagnostics aligned with executable SQL semantics.
    if (schema.sql && isVisibleProjectionAlias(schema.sql, column.span, column.name, schema.databaseType)) continue;

    const displayName = column.qualifier ? `${column.qualifier}.${column.name}` : column.name;
    diagnostics.push({
      span: trimSqlTextSpanWhitespace(schema.sql, column.span),
      message: `Unknown column ${displayName}`,
      severity: "error",
    });
  }

  for (const violation of analysis.group_by_violations ?? []) {
    diagnostics.push(buildSqlGroupByViolationDiagnostic(violation, schema.sql, schema.databaseType));
  }

  return diagnostics;
}

function buildSqlGroupByViolationDiagnostic(violation: SqlGroupByViolation, sql?: string, databaseType?: DatabaseType): SqlSemanticDiagnostic {
  const displayName = violation.qualifier ? `${violation.qualifier}.${violation.column}` : violation.column;
  return {
    span: trimSqlTextSpanWhitespace(sql, violation.span),
    message: `Column ${displayName} must appear in the GROUP BY clause or be used in an aggregate function`,
    // PostgreSQL additionally accepts columns functionally dependent on the
    // GROUP BY key (e.g. projecting other columns of the primary-key table),
    // which the analyzer cannot see from column metadata alone; keep the hint
    // there, but as a warning instead of an error.
    severity: databaseType === "postgres" ? "warning" : "error",
  };
}

function tdengineStableTableKeys(tables: readonly SqlCompletionTable[]): Set<string> {
  const keys = new Set<string>();
  for (const table of tables) {
    if (!isTdengineStableTableType(table.tableType)) continue;
    keys.add(completionTableReferenceKey(table));
  }
  return keys;
}

function completionTableReferenceKey(table: Pick<SqlCompletionTable, "name" | "database" | "schema">): string {
  if (table.schema) return normalizeName(`${table.database ? `${table.database}.` : ""}${table.schema}.${table.name}`);
  return normalizeName(table.name);
}

function isUnquotedOracleBuiltinReference(column: SqlColumnReference, schema: SqlSemanticDiagnosticSchema): boolean {
  if (column.qualifier || !(isOracleSystemValueName(column.name, schema.databaseType) || isOraclePseudoColumnName(column.name, schema.databaseType))) return false;
  if (!schema.sql) return false;

  const range = sqlTextSpanToOffsetRange(schema.sql, column.span);
  if (!range) return false;
  const firstCharacter = schema.sql.slice(range.from, range.to).trimStart()[0];
  return firstCharacter !== '"' && firstCharacter !== "'" && firstCharacter !== "`" && firstCharacter !== "[";
}

export function isSqlVirtualTableReference(table: { name: string; schema?: string | null }, databaseType?: DatabaseType): boolean {
  return databaseType === "mysql" && !table.schema && normalizeName(table.name) === "dual";
}

function isVisibleProjectionAlias(sql: string, span: SqlTextSpan, name: string, databaseType?: DatabaseType): boolean {
  const range = sqlTextSpanToOffsetRange(sql, span);
  if (!range) return false;
  // If the identifier is at the end of a statement terminated by ';' or punctuation,
  // evaluating completion strictly at range.to can place the cursor on or past the
  // semicolon, causing getSqlCompletionContext to treat it as an empty subsequent
  // statement. Testing at range.from (and range.to as fallback) ensures we evaluate
  // inside the clause (GROUP BY / ORDER BY / HAVING) containing this alias reference.
  for (const cursor of [range.from, range.to]) {
    const context = getSqlCompletionContext(sql, cursor, { databaseType });
    if (context.prioritizeSelectAliases && context.selectAliases.some((alias) => normalizeName(alias) === normalizeName(name))) {
      return true;
    }
  }
  return false;
}

function trimSqlTextSpanWhitespace(sql: string | undefined, span: SqlTextSpan): SqlTextSpan {
  if (!sql) return span;
  const range = sqlTextSpanToOffsetRange(sql, span);
  if (!range) return span;

  let from = range.from;
  let to = range.to;
  while (from < to && /\s/.test(sql[from] ?? "")) from += 1;
  while (to > from && /\s/.test(sql[to - 1] ?? "")) to -= 1;
  if (from === range.from && to === range.to) return span;

  const start = offsetToSqlTextStartPosition(sql, from);
  const end = offsetToSqlTextEndPosition(sql, to);
  if (!start || !end) return span;
  return {
    start_line: start.line,
    start_column: start.column,
    end_line: end.line,
    end_column: Math.max(end.column, start.column),
  };
}

/**
 * Converts a 1-based inclusive `SqlTextSpan` into a 0-based half-open offset range `[from, to)`.
 *
 * Both `start_column` and `end_column` are 1-based and inclusive.
 * For a single-line span, `from` is `span.start_column - 1` and `to` is `span.end_column`.
 * For example, a token from col 8 to 14 (`USER_ID`) maps to `[7, 14)` without overshooting
 * into a following delimiter such as `,` or `;`.
 */
export function sqlTextSpanToOffsetRange(sql: string, span: SqlTextSpan): { from: number; to: number } | null {
  if (!span.start_line || !span.start_column) return null;
  const from = sqlTextPositionToOffset(sql, span.start_line, span.start_column - 1);
  const endLine = Math.max(span.end_line, span.start_line);
  const endColumn = endLine === span.start_line ? Math.max(span.end_column, span.start_column) : span.end_column;
  const to = sqlTextPositionToOffset(sql, endLine, endColumn);
  if (from == null || to == null || to <= from) return null;
  return { from, to };
}

function sqlTextPositionToOffset(sql: string, line: number, column: number): number | null {
  const lines = sql.split(/\r?\n/);
  if (line < 1 || line > lines.length) return null;
  let offset = 0;
  for (let index = 0; index < line - 1; index += 1) {
    offset += lines[index].length + 1;
  }
  return Math.min(offset + Math.max(column, 0), offset + lines[line - 1].length);
}

function offsetToSqlTextStartPosition(sql: string, offset: number): { line: number; column: number } | null {
  const position = offsetToLineColumn(sql, offset);
  return position ? { line: position.line, column: position.column + 1 } : null;
}

function offsetToSqlTextEndPosition(sql: string, offset: number): { line: number; column: number } | null {
  return offsetToLineColumn(sql, offset);
}

function offsetToLineColumn(sql: string, offset: number): { line: number; column: number } | null {
  if (offset < 0 || offset > sql.length) return null;
  const lines = sql.split(/\r?\n/);
  let remaining = offset;
  for (let index = 0; index < lines.length; index += 1) {
    const lineLength = lines[index].length;
    if (remaining <= lineLength) {
      return { line: index + 1, column: remaining };
    }
    remaining -= lineLength + 1;
  }
  const lastLine = lines[lines.length - 1] ?? "";
  return { line: lines.length, column: lastLine.length };
}

export function buildSqlParserErrorDiagnostic(error: unknown, sql: string): SqlSemanticDiagnostic | null {
  const message = errorMessage(error);
  const location = /\bat Line:\s*(\d+),\s*Column:\s*(\d+)\b/i.exec(message);
  if (!location) return null;

  const startLine = Number.parseInt(location[1], 10);
  const startColumn = Number.parseInt(location[2], 10);
  if (!Number.isFinite(startLine) || !Number.isFinite(startColumn) || startLine < 1 || startColumn < 1) return null;

  const lineText = sql.split(/\r?\n/)[startLine - 1] ?? "";
  const startIndex = Math.max(startColumn - 1, 0);
  const token = /^[\w$]+/.exec(lineText.slice(startIndex))?.[0];
  const tokenLength = Math.max(token?.length ?? 1, 1);

  return {
    span: {
      start_line: startLine,
      start_column: startColumn,
      end_line: startLine,
      end_column: startColumn + tokenLength - 1,
    },
    message,
    severity: "error",
  };
}

export function areSqlSemanticDiagnosticsEqual(left: readonly SqlSemanticDiagnostic[], right: readonly SqlSemanticDiagnostic[]): boolean {
  if (left.length !== right.length) return false;
  return left.every((item, index) => {
    const other = right[index];
    return !!other && item.message === other.message && item.severity === other.severity && item.span.start_line === other.span.start_line && item.span.start_column === other.span.start_column && item.span.end_line === other.span.end_line && item.span.end_column === other.span.end_column;
  });
}

export function shouldRunSqlSemanticDiagnostics(sql: string, cursor: number, options: { databaseType?: DatabaseType } = {}): boolean {
  if (
    options.databaseType === "mongodb" ||
    options.databaseType === "elasticsearch" ||
    options.databaseType === "easysearch" ||
    options.databaseType === "meilisearch" ||
    options.databaseType === "solr" ||
    options.databaseType === "qdrant" ||
    options.databaseType === "milvus" ||
    options.databaseType === "weaviate" ||
    options.databaseType === "chromadb" ||
    options.databaseType === "redis"
  )
    return false;
  const context = getSqlCompletionContext(sql, cursor, options);
  if (context.exclusiveColumnSuggestions) return false;
  if (context.qualifier) return false;
  if ((context.suggestTables || context.exclusiveTableSuggestions) && isCursorAfterTableTrigger(sql, cursor)) return false;
  return true;
}

export function isSqlSemanticDiagnosticInputContext(sql: string, cursor: number, options: { databaseType?: DatabaseType } = {}): boolean {
  if (
    options.databaseType === "mongodb" ||
    options.databaseType === "elasticsearch" ||
    options.databaseType === "easysearch" ||
    options.databaseType === "meilisearch" ||
    options.databaseType === "solr" ||
    options.databaseType === "qdrant" ||
    options.databaseType === "milvus" ||
    options.databaseType === "weaviate" ||
    options.databaseType === "chromadb" ||
    options.databaseType === "redis"
  )
    return false;
  const context = getSqlCompletionContext(sql, cursor, options);
  return context.exclusiveColumnSuggestions || !!context.qualifier || ((context.suggestTables || context.exclusiveTableSuggestions) && isCursorAfterTableTrigger(sql, cursor));
}

function resolveColumnTable(column: SqlColumnReference, tables: SqlTableReference[], knownTables: Map<string, SqlTableReference>, sql?: string, scopesById?: Map<number, SqlReferenceScope>): SqlTableReference | null {
  const candidateTables = candidateTablesForColumn(tables, column, sql, scopesById);
  if (column.qualifier) {
    return tableLookupFor(candidateTables).get(normalizeName(column.qualifier)) ?? (sql ? null : (knownTables.get(normalizeName(column.qualifier)) ?? null));
  }
  if (candidateTables.length !== 1) return null;
  return candidateTables[0];
}

function candidateTablesForColumn(tables: SqlTableReference[], column: SqlColumnReference, sql?: string, scopesById?: Map<number, SqlReferenceScope>): SqlTableReference[] {
  const scoped = tablesInVisibleScopes(tables, column, scopesById);
  if (scoped) return scoped;
  return sql ? tablesInSameStatement(tables, column, sql) : tables;
}

function tablesInVisibleScopes(tables: SqlTableReference[], column: SqlColumnReference, scopesById?: Map<number, SqlReferenceScope>): SqlTableReference[] | null {
  if (column.scope_id == null || !scopesById || scopesById.size === 0) return null;
  if (!column.qualifier) {
    const currentScopeTables = tables.filter((table) => table.scope_id === column.scope_id);
    if (currentScopeTables.length > 0) return currentScopeTables;
  }
  const visibleScopeIds = scopeAndParents(column.scope_id, scopesById);
  if (visibleScopeIds.size === 0) return null;
  return tables.filter((table) => table.scope_id != null && visibleScopeIds.has(table.scope_id));
}

function scopeAndParents(scopeId: number, scopesById: Map<number, SqlReferenceScope>): Set<number> {
  const ids = new Set<number>();
  let current: number | undefined = scopeId;
  while (current != null && !ids.has(current)) {
    ids.add(current);
    const scope = scopesById.get(current);
    current = scope?.parent_id ?? undefined;
  }
  return ids;
}

function tableLookupFor(tables: SqlTableReference[]): Map<string, SqlTableReference> {
  const lookup = new Map<string, SqlTableReference>();
  for (const table of tables) {
    lookup.set(normalizeName(table.name), table);
    if (table.alias) lookup.set(normalizeName(table.alias), table);
    if (table.schema) lookup.set(normalizeName(`${table.schema}.${table.name}`), table);
    if (table.database && table.schema) lookup.set(normalizeName(`${table.database}.${table.schema}.${table.name}`), table);
  }
  return lookup;
}

function tablesInSameStatement(tables: SqlTableReference[], column: SqlColumnReference, sql: string): SqlTableReference[] {
  const columnOffset = spanStartOffset(sql, column.span);
  if (columnOffset == null) return tables;
  return tables.filter((table) => {
    const tableOffset = spanStartOffset(sql, table.span);
    return tableOffset != null && statementIndexAt(sql, tableOffset) === statementIndexAt(sql, columnOffset);
  });
}

function scopesByIdMap(scopes: readonly SqlReferenceScope[] | undefined): Map<number, SqlReferenceScope> {
  const map = new Map<number, SqlReferenceScope>();
  for (const scope of scopes ?? []) map.set(scope.id, scope);
  return map;
}

function spanStartOffset(sql: string, span: SqlTextSpan): number | null {
  if (!span.start_line || !span.start_column) return null;
  const lines = sql.split(/\r?\n/);
  const lineIndex = span.start_line - 1;
  if (lineIndex < 0 || lineIndex >= lines.length) return null;
  let offset = 0;
  for (let index = 0; index < lineIndex; index++) offset += lines[index].length + 1;
  return Math.min(offset + span.start_column - 1, offset + lines[lineIndex].length);
}

function statementIndexAt(sql: string, offset: number): number {
  let statementIndex = 0;
  for (let index = 0; index < Math.min(offset, sql.length); index++) {
    if (sql[index] === ";") statementIndex++;
  }
  return statementIndex;
}

export function oceanBaseDiagnosticTableReference(table: SqlTableReference, sql: string) {
  const range = sqlTextSpanToOffsetRange(sql, table.span);
  const identifiers = range ? tokenizeSqlSemantic(sql.slice(range.from, range.to), "oracle").filter(tokenIsIdentifier) : [];
  return { ...table, nameQuoted: !!identifiers[table.schema ? 1 : 0]?.quote, schemaQuoted: !!table.schema && !!identifiers[0]?.quote };
}

function columnsForTable(table: SqlTableReference, columnsByTable: Map<string, SqlCompletionColumn[]>, loadedColumnTables?: Set<string>, currentSchema?: string): SqlCompletionColumn[] | null {
  const inlineColumns = (table as SqlTableReference & { columns?: string[] }).columns;
  if (inlineColumns && inlineColumns.length > 0) {
    return inlineColumns.map((name) => ({
      name,
      table: table.name,
      schema: table.schema ?? undefined,
    }));
  }
  const scopedColumns = [...columnsByTable].filter(([key]) => matchesOceanBaseCompletionCacheKey(key, table, currentSchema));
  if (scopedColumns.length > 0) return scopedColumns.length === 1 ? scopedColumns[0]![1] : null;
  const keys = table.schema ? [table.database ? `${table.database}.${table.schema}.${table.name}` : undefined, `${table.schema}.${table.name}`, table.name].filter((key): key is string => !!key) : [table.name, ...keysWithTableName(columnsByTable, table.name)];
  for (const key of keys) {
    const normalizedKey = normalizeName(key);
    const columns = columnsByTable.get(key) ?? columnsByTable.get(normalizedKey);
    if (!columns) continue;
    if (columns.length > 0 || loadedColumnTables?.has(normalizedKey)) return columns;
  }
  if (loadedColumnTables?.has(tableReferenceKey(table))) return [];
  return null;
}

function keysWithTableName(columnsByTable: Map<string, SqlCompletionColumn[]>, tableName: string): string[] {
  const suffix = `.${normalizeName(tableName)}`;
  return [...columnsByTable.keys()].filter((key) => normalizeName(key).endsWith(suffix));
}

function rangesIntersect(left: SqlSemanticDiagnosticVisibleRange, right: SqlSemanticDiagnosticVisibleRange): boolean {
  return left.from < right.to && right.from < left.to;
}

export function tableReferenceKey(table: Pick<SqlTableReference, "name" | "database" | "schema">): string {
  return normalizeName(table.schema ? `${table.database ? `${table.database}.` : ""}${table.schema}.${table.name}` : table.name);
}

function displayTableName(table: SqlTableReference): string {
  return table.schema ? `${table.database ? `${table.database}.` : ""}${table.schema}.${table.name}` : table.name;
}

function isCursorAfterTableTrigger(sql: string, cursor: number): boolean {
  const beforeCursor = sql.slice(0, cursor).trimEnd();
  return /\b(from|join|update|into|table)(?:\s+[\w$`"'[\].]*)?$/i.test(beforeCursor);
}

function normalizeName(value: string): string {
  let normalized = value;
  while (normalized && `"'\`[]`.includes(normalized[0])) normalized = normalized.slice(1);
  while (normalized && `"'\`[]`.includes(normalized[normalized.length - 1])) normalized = normalized.slice(0, -1);
  return normalized.toLowerCase();
}

function errorMessage(error: unknown): string {
  if (error instanceof Error) return error.message;
  return String(error);
}