src/lib/sql-ast-parser.ts - main - dupleymi-amp/sql-trainer

src/lib/sql-ast-parser.ts
166 строк4 KB
Maxim Dupley
fix: validateSQL bug (never detected invalid SQL), fix test assertions (Russian→English)
11 июн 2026, 10:42
11 июн 2026, 10:42982a819
/**
 * AST-based SQL parser for reliable syntax transformation.
 * Uses node-sql-parser for parsing and generating SQL across different dialects.
 *
 * Advantages over regex-based approach:
 * - Handles nested queries correctly
 * - Preserves function names and arguments
 * - Properly handles string literals and comments
 * - Validates SQL syntax before transformation
 */
import { Parser, AST, Option } from 'node-sql-parser';

export type SQLDialect = 'postgresql' | 'mysql' | 'sqlite' | 'clickhouse' | 'bigquery';

export interface ParseResult {
  ast: AST | AST[] | null;
  warnings: string[];
  tableList: string[];
  columnList: string[];
}

export interface TransformResult {
  sql: string;
  warnings: string[];
  errors: string[];
}

/**
 * Parse SQL from a specific dialect into AST.
 */
export function parseSQL(sql: string, dialect: SQLDialect): ParseResult {
  const warnings: string[] = [];
  const tableList: string[] = [];
  const columnList: string[] = [];

  try {
    const parser = new Parser();

    // Map dialect to node-sql-parser database option
    const dbMap: Record<SQLDialect, string> = {
      postgresql: 'postgresql',
      mysql: 'mysql',
      sqlite: 'sqlite',
      clickhouse: 'clickhouse',
      bigquery: 'bigquery',
    };

    const option: Option = {
      database: dbMap[dialect],
      parseOptions: { includeLocations: false },
    };

    const result = parser.parse(sql, option);

    return {
      ast: result.ast,
      warnings,
      tableList: result.tableList || [],
      columnList: result.columnList || [],
    };
  } catch (err: unknown) {
    const error = err instanceof Error ? err.message : String(err);
    warnings.push(`Parse error: ${error}`);

    return { ast: null, warnings, tableList, columnList };
  }
}

/**
 * Generate SQL from AST for a target dialect.
 */
export function generateSQL(ast: AST | AST[], dialect: SQLDialect): TransformResult {
  const warnings: string[] = [];
  const errors: string[] = [];

  try {
    const parser = new Parser();

    // Map dialect to node-sql-parser database option
    const dbMap: Record<SQLDialect, string> = {
      postgresql: 'postgresql',
      mysql: 'mysql',
      sqlite: 'sqlite',
      clickhouse: 'clickhouse',
      bigquery: 'bigquery',
    };

    const option: Option = {
      database: dbMap[dialect],
    };

    const sql = parser.sqlify(ast, option);

    return { sql, warnings, errors };
  } catch (err: unknown) {
    const error = err instanceof Error ? err.message : String(err);
    errors.push(`Generation error: ${error}`);

    return { sql: '', warnings, errors };
  }
}

/**
 * Transform SQL from source dialect to target dialect.
 */
export function transformSQL(sql: string, fromDialect: SQLDialect, toDialect: SQLDialect): TransformResult {
  const allWarnings: string[] = [];
  const allErrors: string[] = [];

  // Parse from source dialect
  const parseResult = parseSQL(sql, fromDialect);
  allWarnings.push(...parseResult.warnings);

  if (!parseResult.ast) {
    return { sql: '', warnings: allWarnings, errors: allErrors };
  }

  // Generate for target dialect
  const genResult = generateSQL(parseResult.ast, toDialect);
  allWarnings.push(...genResult.warnings);
  allErrors.push(...genResult.errors);

  return {
    sql: genResult.sql,
    warnings: allWarnings,
    errors: allErrors,
  };
}

/**
 * Validate SQL syntax for a specific dialect.
 */
export function validateSQL(sql: string, dialect: SQLDialect): { valid: boolean; errors: string[] } {
  const errors: string[] = [];

  const result = parseSQL(sql, dialect);

  if (result.ast === null) {
    errors.push(...result.warnings.map((w) => w.replace(/^Parse error: /, '')));
    if (errors.length === 0) errors.push('Invalid SQL syntax');
    return { valid: false, errors };
  }

  return { valid: true, errors };
}

/**
 * Extract table names from SQL query.
 */
export function extractTables(sql: string, dialect: SQLDialect): string[] {
  const { tableList } = parseSQL(sql, dialect);
  return tableList;
}

/**
 * Check if SQL contains unsupported features for target dialect.
 */
export function checkUnsupportedFeatures(sql: string, fromDialect: SQLDialect, _toDialect?: SQLDialect): string[] {
  const { ast } = parseSQL(sql, fromDialect);

  if (!ast) return [];

  void _toDialect;

  return [];
}