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。

15.8 KB
Raw Blame
import { describe, expect, it } from "vitest";
import { buildSqlSemanticDiagnostics, sqlTextSpanToOffsetRange } from "@/lib/sql/semantic/diagnostics";
import type { SqlReferenceAnalysis, SqlTextSpan } from "@/types/database";

const span = (startColumn: number, endColumn: number): SqlTextSpan => ({
  start_line: 1,
  start_column: startColumn,
  end_line: 1,
  end_column: endColumn,
});

describe("sqlTextSpanToOffsetRange inclusive column semantics", () => {
  it("converts 1-based inclusive single-character and multi-character spans to exact 0-based slice ranges", () => {
    const sql = "SELECT id, name AS user_name FROM users;";

    // Single character 'i' in "id" at line 1, column 8
    const singleChar = sqlTextSpanToOffsetRange(sql, { start_line: 1, start_column: 8, end_line: 1, end_column: 8 });
    expect(singleChar).toEqual({ from: 7, to: 8 });
    expect(sql.slice(singleChar!.from, singleChar!.to)).toBe("i");

    // Two-character "id" from column 8 to 9 (inclusive)
    const idRange = sqlTextSpanToOffsetRange(sql, { start_line: 1, start_column: 8, end_line: 1, end_column: 9 });
    expect(idRange).toEqual({ from: 7, to: 9 });
    expect(sql.slice(idRange!.from, idRange!.to)).toBe("id");

    // Nine-character "user_name"
    const aliasStart = sql.indexOf("user_name");
    const aliasSpan: SqlTextSpan = {
      start_line: 1,
      start_column: aliasStart + 1,
      end_line: 1,
      end_column: aliasStart + "user_name".length,
    };
    const aliasRange = sqlTextSpanToOffsetRange(sql, aliasSpan);
    expect(aliasRange).toEqual({ from: aliasStart, to: aliasStart + "user_name".length });
    expect(sql.slice(aliasRange!.from, aliasRange!.to)).toBe("user_name");
  });

  it("does not overshoot into trailing delimiters such as commas or semicolons", () => {
    const sql = "SELECT USER_ID, col FROM users GROUP BY USER_ID;";
    const userIdCol = sql.indexOf("USER_ID");
    // "USER_ID" is 7 characters; 1-based columns are 8..=14
    const spanBeforeComma: SqlTextSpan = {
      start_line: 1,
      start_column: userIdCol + 1,
      end_line: 1,
      end_column: userIdCol + "USER_ID".length,
    };
    const rangeBeforeComma = sqlTextSpanToOffsetRange(sql, spanBeforeComma);
    expect(rangeBeforeComma).toEqual({ from: userIdCol, to: userIdCol + 7 });
    expect(sql.slice(rangeBeforeComma!.from, rangeBeforeComma!.to)).toBe("USER_ID");
    expect(sql[rangeBeforeComma!.to]).toBe(",");

    const lastCol = sql.lastIndexOf("USER_ID");
    const spanBeforeSemi: SqlTextSpan = {
      start_line: 1,
      start_column: lastCol + 1,
      end_line: 1,
      end_column: lastCol + "USER_ID".length,
    };
    const rangeBeforeSemi = sqlTextSpanToOffsetRange(sql, spanBeforeSemi);
    expect(rangeBeforeSemi).toEqual({ from: lastCol, to: lastCol + 7 });
    expect(sql.slice(rangeBeforeSemi!.from, rangeBeforeSemi!.to)).toBe("USER_ID");
    expect(sql[rangeBeforeSemi!.to]).toBe(";");
  });

  it("handles multi-line spans without overshooting the end line column", () => {
    const sql = "SELECT\n  user_name\nFROM users;";
    const multiLineSpan: SqlTextSpan = {
      start_line: 2,
      start_column: 3,
      end_line: 2,
      end_column: 11,
    };
    const range = sqlTextSpanToOffsetRange(sql, multiLineSpan);
    expect(range).not.toBeNull();
    expect(sql.slice(range!.from, range!.to)).toBe("user_name");
  });

  it("returns null for invalid or empty spans", () => {
    const sql = "SELECT 1;";
    expect(sqlTextSpanToOffsetRange(sql, { start_line: 0, start_column: 0, end_line: 0, end_column: 0 })).toBeNull();
    expect(sqlTextSpanToOffsetRange(sql, { start_line: 1, start_column: 10, end_line: 1, end_column: 5 })).toBeNull();
    expect(sqlTextSpanToOffsetRange(sql, { start_line: 5, start_column: 1, end_line: 5, end_column: 2 })).toBeNull();
  });
});

describe("buildSqlSemanticDiagnostics GROUP BY violations", () => {
  it("maps analyzer GROUP BY violations to error diagnostics", () => {
    const sql = "SELECT USER_ID, SUM(MONEY) FROM users GROUP BY USER_NAME";
    const analysis: SqlReferenceAnalysis = {
      tables: [],
      columns: [],
      group_by_violations: [{ span: span(8, 14), column: "USER_ID", qualifier: null }],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, { tables: [], columnsByTable: new Map(), sql });

    expect(diagnostics).toEqual([
      {
        span: span(8, 14),
        message: "Column USER_ID must appear in the GROUP BY clause or be used in an aggregate function",
        severity: "error",
      },
    ]);
  });

  it("downgrades the severity to warning on PostgreSQL", () => {
    const analysis: SqlReferenceAnalysis = {
      tables: [],
      columns: [],
      group_by_violations: [{ span: span(8, 11), column: "name", qualifier: "u" }],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, { tables: [], columnsByTable: new Map(), databaseType: "postgres" });

    expect(diagnostics).toHaveLength(1);
    expect(diagnostics[0]?.severity).toBe("warning");
  });

  it("includes the qualifier in the message and tolerates missing sql", () => {
    const analysis: SqlReferenceAnalysis = {
      tables: [],
      columns: [],
      group_by_violations: [{ span: span(1, 4), column: "name", qualifier: "u" }],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, { tables: [], columnsByTable: new Map() });

    expect(diagnostics).toHaveLength(1);
    expect(diagnostics[0]?.message).toBe("Column u.name must appear in the GROUP BY clause or be used in an aggregate function");
    expect(diagnostics[0]?.severity).toBe("error");
  });
});

describe("buildSqlSemanticDiagnostics Oracle pseudo-columns", () => {
  const buildAnalysis = (sql: string, token: string, options: { quoted?: boolean; qualifier?: string } = {}) => {
    const tokenStart = sql.indexOf(token);
    const start = options.quoted ? tokenStart - 1 : tokenStart;
    const end = options.quoted ? tokenStart + token.length + 1 : tokenStart + token.length;
    return {
      tables: [{ name: "users", span: span(sql.indexOf("users") + 1, sql.indexOf("users") + "users".length) }],
      columns: [{ name: token, qualifier: options.qualifier ?? null, span: span(start + 1, end) }],
    };
  };

  it("accepts unqualified Oracle pseudo-columns missing from table metadata", () => {
    const sql = "SELECT * FROM users WHERE ROWNUM < 10;";
    const analysis = buildAnalysis(sql, "ROWNUM");

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([["users", [{ name: "id" }]]]),
      loadedColumnTables: new Set(["users"]),
      sql,
      databaseType: "oracle",
    });

    expect(diagnostics).toEqual([]);
  });

  it("keeps validating quoted pseudo-column names as real columns", () => {
    const sql = 'SELECT * FROM users WHERE "ROWNUM" < 10;';
    const analysis = buildAnalysis(sql, "ROWNUM", { quoted: true });

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([["users", [{ name: "id" }]]]),
      loadedColumnTables: new Set(["users"]),
      sql,
      databaseType: "oracle",
    });

    expect(diagnostics.map((diagnostic) => diagnostic.message)).toEqual(["Unknown column ROWNUM"]);
  });
});

describe("buildSqlSemanticDiagnostics projection aliases in GROUP BY / ORDER BY (#11627)", () => {
  it("accepts SELECT projection aliases in GROUP BY even when terminated with a semicolon", () => {
    const sql = "SELECT CASE WHEN submitter_id IS NOT NULL THEN 'A' ELSE 'B' END AS repair_type, COUNT(*) AS row_cnt FROM ipu_quotation GROUP BY repair_type;";
    const aliasStart = sql.indexOf("GROUP BY repair_type") + "GROUP BY ".length;
    const aliasEnd = aliasStart + "repair_type".length;

    const analysis: SqlReferenceAnalysis = {
      tables: [{ name: "ipu_quotation", span: span(sql.indexOf("ipu_quotation") + 1, sql.indexOf("ipu_quotation") + "ipu_quotation".length) }],
      columns: [
        { name: "submitter_id", qualifier: null, span: span(sql.indexOf("submitter_id") + 1, sql.indexOf("submitter_id") + "submitter_id".length) },
        { name: "repair_type", qualifier: null, span: span(aliasStart + 1, aliasEnd) },
      ],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "ipu_quotation" }],
      columnsByTable: new Map([["ipu_quotation", [{ name: "submitter_id" }]]]),
      loadedColumnTables: new Set(["ipu_quotation"]),
      sql,
      databaseType: "mysql",
    });

    expect(diagnostics).toEqual([]);
  });

  it("accepts projection aliases in ORDER BY terminated with a semicolon", () => {
    const sql = "SELECT id, name AS user_name FROM users ORDER BY user_name;";
    const aliasStart = sql.indexOf("ORDER BY user_name") + "ORDER BY ".length;
    const aliasEnd = aliasStart + "user_name".length;

    const analysis: SqlReferenceAnalysis = {
      tables: [{ name: "users", span: span(sql.indexOf("users") + 1, sql.indexOf("users") + "users".length) }],
      columns: [
        { name: "id", qualifier: null, span: span(sql.indexOf("id") + 1, sql.indexOf("id") + 2) },
        { name: "name", qualifier: null, span: span(sql.indexOf("name") + 1, sql.indexOf("name") + 4) },
        { name: "user_name", qualifier: null, span: span(aliasStart + 1, aliasEnd) },
      ],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([["users", [{ name: "id" }, { name: "name" }]]]),
      loadedColumnTables: new Set(["users"]),
      sql,
      databaseType: "mysql",
    });

    expect(diagnostics).toEqual([]);
  });

  it("still reports genuinely unknown columns in GROUP BY", () => {
    const sql = "SELECT id, name AS user_name FROM users GROUP BY missing_col;";
    const colStart = sql.indexOf("missing_col");
    const colEnd = colStart + "missing_col".length;

    const analysis: SqlReferenceAnalysis = {
      tables: [{ name: "users", span: span(sql.indexOf("users") + 1, sql.indexOf("users") + "users".length) }],
      columns: [
        { name: "id", qualifier: null, span: span(sql.indexOf("id") + 1, sql.indexOf("id") + 2) },
        { name: "name", qualifier: null, span: span(sql.indexOf("name") + 1, sql.indexOf("name") + 4) },
        { name: "missing_col", qualifier: null, span: span(colStart + 1, colEnd) },
      ],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([["users", [{ name: "id" }, { name: "name" }]]]),
      loadedColumnTables: new Set(["users"]),
      sql,
      databaseType: "mysql",
    });

    expect(diagnostics.map((d) => d.message)).toEqual(["Unknown column missing_col"]);
  });

  it("accepts projection aliases in GROUP BY when another expression contains a string literal with a semicolon", () => {
    const sql = "SELECT CASE WHEN status = ';' THEN 'semi;' ELSE 'other' END AS status_alias, COUNT(*) AS total FROM orders GROUP BY CONCAT(category, ';'), status_alias;";
    const aliasStart = sql.indexOf(", status_alias;") + ", ".length;
    const aliasEnd = aliasStart + "status_alias".length;

    const analysis: SqlReferenceAnalysis = {
      tables: [{ name: "orders", span: span(sql.indexOf("orders") + 1, sql.indexOf("orders") + "orders".length) }],
      columns: [
        { name: "status", qualifier: null, span: span(sql.indexOf("status") + 1, sql.indexOf("status") + "status".length) },
        { name: "category", qualifier: null, span: span(sql.indexOf("category") + 1, sql.indexOf("category") + "category".length) },
        { name: "status_alias", qualifier: null, span: span(aliasStart + 1, aliasEnd) },
      ],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "orders" }],
      columnsByTable: new Map([["orders", [{ name: "status" }, { name: "category" }]]]),
      loadedColumnTables: new Set(["orders"]),
      sql,
      databaseType: "mysql",
    });

    expect(diagnostics).toEqual([]);
  });

  it("accepts projection aliases in GROUP BY in the second statement of a multi-statement script", () => {
    const sql = "SELECT 1;\nSELECT id, name AS user_name FROM users GROUP BY user_name;";
    const secondLine = "SELECT id, name AS user_name FROM users GROUP BY user_name;";
    const aliasStartOnLine = secondLine.indexOf("GROUP BY user_name") + "GROUP BY ".length;
    const aliasEndOnLine = aliasStartOnLine + "user_name".length;

    const analysis: SqlReferenceAnalysis = {
      tables: [{ name: "users", span: { start_line: 2, start_column: secondLine.indexOf("users") + 1, end_line: 2, end_column: secondLine.indexOf("users") + "users".length } }],
      columns: [
        { name: "id", qualifier: null, span: { start_line: 2, start_column: secondLine.indexOf("id") + 1, end_line: 2, end_column: secondLine.indexOf("id") + 2 } },
        { name: "name", qualifier: null, span: { start_line: 2, start_column: secondLine.indexOf("name") + 1, end_line: 2, end_column: secondLine.indexOf("name") + 4 } },
        { name: "user_name", qualifier: null, span: { start_line: 2, start_column: aliasStartOnLine + 1, end_line: 2, end_column: aliasEndOnLine } },
      ],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([["users", [{ name: "id" }, { name: "name" }]]]),
      loadedColumnTables: new Set(["users"]),
      sql,
      databaseType: "mysql",
    });

    expect(diagnostics).toEqual([]);
  });

  it("accepts projection aliases in GROUP BY in the first statement followed by another statement", () => {
    const sql = "SELECT id, name AS user_name FROM users GROUP BY user_name;\nSELECT 2;";
    const aliasStart = sql.indexOf("GROUP BY user_name") + "GROUP BY ".length;
    const aliasEnd = aliasStart + "user_name".length;

    const analysis: SqlReferenceAnalysis = {
      tables: [{ name: "users", span: span(sql.indexOf("users") + 1, sql.indexOf("users") + "users".length) }],
      columns: [
        { name: "id", qualifier: null, span: span(sql.indexOf("id") + 1, sql.indexOf("id") + 2) },
        { name: "name", qualifier: null, span: span(sql.indexOf("name") + 1, sql.indexOf("name") + 4) },
        { name: "user_name", qualifier: null, span: span(aliasStart + 1, aliasEnd) },
      ],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([["users", [{ name: "id" }, { name: "name" }]]]),
      loadedColumnTables: new Set(["users"]),
      sql,
      databaseType: "mysql",
    });

    expect(diagnostics).toEqual([]);
  });

  it("still reports genuinely unknown columns in multi-statement scripts", () => {
    const sql = "SELECT 1;\nSELECT id, name AS user_name FROM users GROUP BY missing_col;";
    const secondLine = "SELECT id, name AS user_name FROM users GROUP BY missing_col;";
    const colStartOnLine = secondLine.indexOf("missing_col");
    const colEndOnLine = colStartOnLine + "missing_col".length;

    const analysis: SqlReferenceAnalysis = {
      tables: [{ name: "users", span: { start_line: 2, start_column: secondLine.indexOf("users") + 1, end_line: 2, end_column: secondLine.indexOf("users") + "users".length } }],
      columns: [
        { name: "id", qualifier: null, span: { start_line: 2, start_column: secondLine.indexOf("id") + 1, end_line: 2, end_column: secondLine.indexOf("id") + 2 } },
        { name: "name", qualifier: null, span: { start_line: 2, start_column: secondLine.indexOf("name") + 1, end_line: 2, end_column: secondLine.indexOf("name") + 4 } },
        { name: "missing_col", qualifier: null, span: { start_line: 2, start_column: colStartOnLine + 1, end_line: 2, end_column: colEndOnLine } },
      ],
    };

    const diagnostics = buildSqlSemanticDiagnostics(analysis, {
      tables: [{ name: "users" }],
      columnsByTable: new Map([["users", [{ name: "id" }, { name: "name" }]]]),
      loadedColumnTables: new Set(["users"]),
      sql,
      databaseType: "mysql",
    });

    expect(diagnostics.map((d) => d.message)).toEqual(["Unknown column missing_col"]);
  });
});