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。

34.7 KB
Raw Blame
import { describe, expect, it } from "vitest";
import { sqlSemanticCompletionScope, sqlSemanticLocalColumnsByTable, sqlSemanticProjectionAliasColumns, sqlSemanticSelectStarIsOnlyProjection, sqlSemanticSelectStarQualifierSql, sqlSemanticSelectStarTableSource, sqlSemanticSelectStarTableSources } from "@/lib/sql/semantic/completion";
import { SQL_SEMANTIC_BASELINE_FIXTURES, sqlFixtureCursor } from "@/lib/sql/semantic/fixtures";
import { buildSqlSemanticModel, sqlSemanticTableNameSpans } from "@/lib/sql/semantic/model";

describe("sqlSemanticModel baseline fixtures", () => {
  for (const fixture of SQL_SEMANTIC_BASELINE_FIXTURES) {
    it(fixture.name, () => {
      const { sql, cursor } = sqlFixtureCursor(fixture.sql);
      const model = buildSqlSemanticModel(sql, cursor, { databaseType: fixture.databaseType });
      const scope = sqlSemanticCompletionScope(model);

      expect(model.statement.kind).toBe(fixture.expected.statementKind);
      expect(model.cursorIntent.kind).toBe(fixture.expected.cursorKind);
      expect(scope.kind).toBe(fixture.expected.completionScope);
      expect(model.cursorIntent.prefix).toBe(fixture.expected.prefix);
      expect(model.cursorIntent.qualifierParts).toEqual(fixture.expected.qualifierParts ?? []);
      expect(model.cursorIntent.confidence).toBe(fixture.expected.confidence);

      for (const expectedSource of fixture.expected.rowSources ?? []) {
        const expectedObject = Object.fromEntries(Object.entries(expectedSource).filter(([, value]) => value !== undefined));
        expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining(expectedObject)]));
      }

      if (fixture.expected.completionLabels) {
        const labels = [...sqlSemanticLocalColumnsByTable(model).values()].flat().map((column) => column.name);
        expect(labels).toEqual(expect.arrayContaining(fixture.expected.completionLabels));
      }
    });
  }

  it("does not treat SELECT as the qualifier of an unqualified star", () => {
    const { sql, cursor } = sqlFixtureCursor("select *| from apis as ap");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "star", qualifierParts: [] }));
  });

  it("retains the qualifier of a qualified star", () => {
    const { sql, cursor } = sqlFixtureCursor("select ap.*| from apis as ap");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "star", qualifierParts: ["ap"], targetSourceId: model.rowSources[0]?.id }));
  });

  it("does not resolve an unqualified star to the only physical table among multiple row sources", () => {
    const { sql, cursor } = sqlFixtureCursor("select *| from users u join (select 1 as x) s on 1 = 1");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.rowSources.map((source) => source.kind)).toEqual(["table", "subquery"]);
    expect(sqlSemanticSelectStarTableSource(model)).toBeUndefined();
  });

  it("returns every table source for an unqualified multi-table star", () => {
    const { sql, cursor } = sqlFixtureCursor("select *| from tVillage tV inner join tland tl on tV.villageId = tl.villageId");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(sqlSemanticSelectStarTableSources(model).map((source) => source.alias)).toEqual(["tV", "tl"]);
  });

  it.each([
    ["select *| from apis", true],
    ["select distinct *| from apis", true],
    ["select ap.*| from apis ap", true],
    ["select *, 1 as extra| from apis", false],
    ["select ap.*, 1 as extra| from apis ap", false],
  ] as const)("detects whether the star is the only projection in %s", (markedSql, expected) => {
    const { sql, cursor } = sqlFixtureCursor(markedSql);

    expect(sqlSemanticSelectStarIsOnlyProjection(buildSqlSemanticModel(sql, cursor))).toBe(expected);
  });

  it("retains the original quoting of a star qualifier", () => {
    const { sql, cursor } = sqlFixtureCursor('select "Order Alias".*| from orders as "Order Alias"');

    expect(sqlSemanticSelectStarQualifierSql(buildSqlSemanticModel(sql, cursor, { databaseType: "postgres", dialect: "postgres" }))).toBe('"Order Alias"');
  });

  it("does not mix row sources from inactive statements", () => {
    const { sql, cursor } = sqlFixtureCursor("select * from users u; select * from orders o where o.|");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.rowSources.some((source) => source.name === "orders")).toBe(true);
    expect(model.rowSources.some((source) => source.name === "users")).toBe(false);
  });

  it("does not expose CTE body tables as outer query row sources", () => {
    const { sql, cursor } = sqlFixtureCursor("WITH recent_orders AS (SELECT id FROM orders) SELECT * FROM recent_orders ro WHERE ro.|");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.rowSources.some((source) => source.name === "recent_orders")).toBe(true);
    expect(model.rowSources.some((source) => source.name === "orders")).toBe(false);
  });

  it("does not expose subquery body tables as outer query row sources", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM (SELECT id FROM users) sq WHERE sq.|");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "sq", kind: "subquery" })]));
    expect(model.rowSources.some((source) => source.name === "users")).toBe(false);
  });

  it("enriches derived tables inside a CTE body but never outside one", () => {
    const { sql, cursor } = sqlFixtureCursor("WITH w AS (SELECT x.id FROM (SELECT t.id FROM orders t) x) SELECT w.| FROM w");
    const model = buildSqlSemanticModel(sql, cursor);
    const definition = model.rowSources.find((source) => source.id.startsWith("cte:"))!;
    const inner = definition.bodySources?.find((source) => source.kind === "subquery");

    // The inline view mirrors a CTE definition's body metadata so lineage can descend into it.
    expect(inner?.cteOutputs?.map((output) => output.name)).toEqual(["id"]);
    expect(inner?.cteStars).toEqual([]);
    expect(inner?.bodySources?.map((source) => source.name)).toEqual(["orders"]);
    // Its tables still stay nested on the source and never leak to the outer model.
    expect(definition.bodySources?.some((source) => source.name === "orders")).toBe(false);

    // A derived table outside a CTE body keeps its previous metadata-free shape.
    const plain = buildSqlSemanticModel("SELECT * FROM (SELECT id FROM users) sq WHERE sq.id = 1", 20);
    const outer = plain.rowSources.find((source) => source.kind === "subquery")!;
    expect(outer.cteOutputs).toBeUndefined();
    expect(outer.cteStars).toBeUndefined();
    expect(outer.bodySources).toBeUndefined();
  });

  it("enriches only the outermost derived table of a CTE body", () => {
    const sql = "WITH w AS (SELECT x.id FROM (SELECT y.id FROM (SELECT t.id FROM orders t) y) x) SELECT id FROM w";
    const model = buildSqlSemanticModel(sql, sql.length);
    const definition = model.rowSources.find((source) => source.id.startsWith("cte:"))!;
    const outerView = definition.bodySources?.find((source) => source.kind === "subquery");
    const nestedView = outerView?.bodySources?.find((source) => source.kind === "subquery");

    expect(outerView?.name).toBe("x");
    expect(outerView?.bodySources?.map((source) => source.name)).toEqual(["y"]);
    expect(nestedView?.name).toBe("y");
    expect(nestedView?.bodySources).toBeUndefined();
  });

  it("suppresses completion inside string literals without metadata scope", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT 'u.|' FROM users");
    const model = buildSqlSemanticModel(sql, cursor);
    const scope = sqlSemanticCompletionScope(model);

    expect(model.cursorIntent.kind).toBe("suppressed");
    expect(scope.useRemoteMetadata).toBe(false);
  });

  it("classifies table references after comma-separated table lists", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users u, ord|");
    const model = buildSqlSemanticModel(sql, cursor);
    const scope = sqlSemanticCompletionScope(model);

    expect(model.cursorIntent.kind).toBe("table");
    expect(model.cursorIntent.prefix).toBe("ord");
    expect(scope.kind).toBe("table");
  });

  it("extracts aliases from completed comma-separated table lists", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM table_a a, table_b b WHERE a.id = b.|");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "table_a", alias: "a" }), expect.objectContaining({ name: "table_b", alias: "b" })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["b"] }));
  });

  it.each([
    ["table name", "SELECT orders.| FROM orders", ["orders"]],
    ["schema-qualified table name", "SELECT public.orders.| FROM public.orders", ["public", "orders"]],
  ] as const)("resolves columns through a PostgreSQL %s qualifier before FROM", (_label, markedSql, qualifierParts) => {
    const { sql, cursor } = sqlFixtureCursor(markedSql);
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres", dialect: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "orders", alias: undefined })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts, targetSourceId: model.rowSources[0]?.id }));
  });

  it("keeps nested EXISTS sources and outer correlated sources visible", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM aa.tb t WHERE EXISTS (SELECT 1 FROM aa.tb1 t1, aa.tb2 t2 WHERE t1.|)");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "mysql", dialect: "mysql" });

    expect(model.rowSources).toEqual(
      expect.arrayContaining([
        expect.objectContaining({ name: "tb1", alias: "t1", metadataTarget: { schema: "aa", table: "tb1" } }),
        expect.objectContaining({ name: "tb2", alias: "t2", metadataTarget: { schema: "aa", table: "tb2" } }),
        expect.objectContaining({ name: "tb", alias: "t", metadataTarget: { schema: "aa", table: "tb" } }),
      ]),
    );
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["t1"] }));
    expect(model.cursorIntent.targetSourceId).toBe(model.rowSources.find((source) => source.alias === "t1")?.id);
  });

  it("classifies an incomplete nested database qualifier as a table context", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM aa.tb t WHERE EXISTS (SELECT 1 FROM aa.tb1 t1, aa.|)");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "mysql", dialect: "mysql" });

    expect(model.rowSources.some((source) => source.name === "aa")).toBe(false);
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "table", qualifierParts: ["aa"], confidence: "high" }));
  });

  it("does not treat an Oracle FOR UPDATE clause as a table alias", () => {
    const sql = "SELECT * FROM APP.USERS FOR UPDATE SKIP LOCKED";
    const model = buildSqlSemanticModel(sql, sql.length, { databaseType: "oracle" });

    expect(model.rowSources).toEqual([expect.objectContaining({ name: "USERS", qualifierParts: ["APP"], alias: undefined })]);
  });

  it("consumes correlation column lists before parsing later comma-separated sources", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM table_a a(id), table_b b, table_c c WHERE c.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "table_a", alias: "a", columns: undefined, columnAliases: ["id"] }), expect.objectContaining({ name: "table_b", alias: "b" }), expect.objectContaining({ name: "table_c", alias: "c" })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["c"] }));
  });

  it("parses generic PostgreSQL table functions and their correlation columns", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM generate_series(1, 3) g(value) WHERE g.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "table_function", name: "g", alias: "g", columns: ["value"] })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["g"] }));
  });

  it("consumes LATERAL functions before parsing later comma-separated sources", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users u, LATERAL generate_series(1, 3) g(value), orders o WHERE o.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "users", alias: "u" }), expect.objectContaining({ kind: "table_function", name: "g", alias: "g", columns: ["value"] }), expect.objectContaining({ name: "orders", alias: "o" })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["o"] }));
  });

  it("consumes WITH ORDINALITY before function aliases and later sources", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM generate_series(1, 3) WITH ORDINALITY AS g(value, ord), orders o WHERE o.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "table_function", name: "g", alias: "g", columns: ["value", "ord"] }), expect.objectContaining({ name: "orders", alias: "o" })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["o"] }));
  });

  it("parses comma-separated sources after a complete joined table", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users u JOIN orders o ON o.user_id = u.id, audit_log a WHERE a.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "users", alias: "u" }), expect.objectContaining({ name: "orders", alias: "o" }), expect.objectContaining({ name: "audit_log", alias: "a" })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["a"] }));
  });

  it("does not parse commas in later SELECT clauses as row sources", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users u WINDOW w1 AS (PARTITION BY u.id), w2 AS (PARTITION BY u.id)|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "users", alias: "u" })]));
    expect(model.rowSources.some((source) => source.name === "w2")).toBe(false);
  });

  it("keeps LATERAL subqueries available as row sources", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users u, LATERAL (SELECT u.id AS user_id) s WHERE s.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "subquery", name: "s", alias: "s", columns: ["user_id"] })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["s"] }));
  });

  it("does not classify SQL Server table hints as generic table functions", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users (NOLOCK) WHERE users.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "sqlserver" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "table", name: "users" })]));
    expect(model.rowSources.some((source) => source.kind === "table_function")).toBe(false);
  });

  it("keeps aliased SQL Server table hints separate from correlation columns", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users u (NOLOCK), orders o WHERE u.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "sqlserver" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "table", name: "users", alias: "u", columns: undefined, columnAliases: undefined }), expect.objectContaining({ kind: "table", name: "orders", alias: "o" })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["u"] }));
  });

  it("consumes SQL Server WITH table hints without treating WITH as an alias", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users WITH (NOLOCK), orders o WHERE users.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "sqlserver" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "table", name: "users", alias: undefined, columns: undefined }), expect.objectContaining({ kind: "table", name: "orders", alias: "o" })]));
  });

  it("keeps partial PostgreSQL correlation names separate from the source schema", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM users u(user_id) WHERE u.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "table", name: "users", alias: "u", columns: undefined, columnAliases: ["user_id"], metadataTarget: { table: "users" } })]));
  });

  it("treats LATERAL as a regular SQL Server table name", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM lateral l WHERE l.|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "sqlserver" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ kind: "table", name: "lateral", alias: "l" })]));
    expect(model.cursorIntent).toEqual(expect.objectContaining({ kind: "alias_column", qualifierParts: ["l"] }));
  });

  it("classifies alias-qualified star with replacement range", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT u.*| FROM users u");
    const model = buildSqlSemanticModel(sql, cursor);

    expect(model.cursorIntent.kind).toBe("star");
    expect(model.cursorIntent.prefix).toBe("*");
    expect(model.cursorIntent.qualifierParts).toEqual(["u"]);
    expect(sql.slice(model.cursorIntent.replacementRange.start, model.cursorIntent.replacementRange.end)).toBe("*");
  });

  it("returns low-confidence keyword fallback for unknown SQL", () => {
    const { sql, cursor } = sqlFixtureCursor("explain analyze |");
    const model = buildSqlSemanticModel(sql, cursor);
    const scope = sqlSemanticCompletionScope(model);

    expect(model.cursorIntent.kind).toBe("keyword");
    expect(model.cursorIntent.confidence).toBe("low");
    expect(scope.useRemoteMetadata).toBe(false);
  });

  it("exposes PostgreSQL projection aliases in ORDER BY but not WHERE", () => {
    const orderBy = sqlFixtureCursor("select total_amount as total from orders order by to|");
    const where = sqlFixtureCursor("select total_amount as total from orders where to|");

    expect(sqlSemanticProjectionAliasColumns(buildSqlSemanticModel(orderBy.sql, orderBy.cursor, { databaseType: "postgres" })).map((column) => column.name)).toContain("total");
    expect(sqlSemanticProjectionAliasColumns(buildSqlSemanticModel(where.sql, where.cursor, { databaseType: "postgres" })).map((column) => column.name)).not.toContain("total");
  });

  it("exposes MySQL projection aliases in GROUP BY and HAVING", () => {
    const groupBy = sqlFixtureCursor("select total_amount as total from orders group by to|");
    const having = sqlFixtureCursor("select total_amount as total from orders having to|");

    expect(sqlSemanticProjectionAliasColumns(buildSqlSemanticModel(groupBy.sql, groupBy.cursor, { databaseType: "mysql" })).map((column) => column.name)).toContain("total");
    expect(sqlSemanticProjectionAliasColumns(buildSqlSemanticModel(having.sql, having.cursor, { databaseType: "mysql" })).map((column) => column.name)).toContain("total");
  });

  it("keeps dialect-specific identifier normalization and qualifier scopes", () => {
    const sqlServer = sqlFixtureCursor("SELECT * FROM [dbo].[Users] u WHERE u.|");
    const postgres = sqlFixtureCursor('SELECT total AS "Order Total" FROM "Sales"."Orders" o ORDER BY "Order|');
    const mysql = sqlFixtureCursor("SELECT * FROM `analytics`.`events` e WHERE e.|");
    const sqlite = sqlFixtureCursor("SELECT * FROM main.users u WHERE u.|");

    expect(buildSqlSemanticModel(sqlServer.sql, sqlServer.cursor, { databaseType: "sqlserver" }).rowSources[0]).toEqual(expect.objectContaining({ name: "Users", qualifierParts: ["dbo"], alias: "u" }));
    expect(sqlSemanticProjectionAliasColumns(buildSqlSemanticModel(postgres.sql, postgres.cursor, { databaseType: "postgres" })).map((column) => column.name)).toContain("Order Total");
    expect(buildSqlSemanticModel(mysql.sql, mysql.cursor, { databaseType: "mysql" }).rowSources[0]).toEqual(expect.objectContaining({ name: "events", qualifierParts: ["analytics"], alias: "e" }));
    expect(buildSqlSemanticModel(sqlite.sql, sqlite.cursor, { databaseType: "sqlite" }).rowSources[0]).toEqual(expect.objectContaining({ name: "users", qualifierParts: ["main"], alias: "u" }));
  });

  it("covers SQL Server case-insensitive bracket and multi-part qualifier contexts", () => {
    const { sql, cursor } = sqlFixtureCursor("SELECT * FROM [ServerOne].[AppDb].[dbo].[Users] U WHERE u.na|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "sqlserver" });

    expect(model.rowSources[0]).toEqual(expect.objectContaining({ name: "Users", qualifierParts: ["ServerOne", "AppDb", "dbo"], alias: "U", metadataTarget: { database: "AppDb", schema: "dbo", table: "Users" } }));
    expect(model.cursorIntent.kind).toBe("alias_column");
    expect(model.cursorIntent.qualifierParts).toEqual(["u"]);
  });

  it("covers PostgreSQL lower-case folding with CTEs and ORDER BY projection aliases", () => {
    const { sql, cursor } = sqlFixtureCursor("WITH RecentOrders AS (SELECT id, total FROM orders) SELECT total AS total_alias FROM RecentOrders ro ORDER BY total_|");
    const model = buildSqlSemanticModel(sql, cursor, { databaseType: "postgres" });

    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "recentorders", alias: "ro", columns: ["id", "total"] })]));
    expect(sqlSemanticProjectionAliasColumns(model).map((column) => column.name)).toContain("total_alias");
  });

  it("covers MySQL database-qualified backticks and projection alias visibility", () => {
    const groupBy = sqlFixtureCursor("SELECT amount AS total FROM `analytics`.`events` e GROUP BY to|");
    const where = sqlFixtureCursor("SELECT amount AS total FROM `analytics`.`events` e WHERE to|");

    expect(buildSqlSemanticModel(groupBy.sql, groupBy.cursor, { databaseType: "mysql" }).rowSources[0]).toEqual(expect.objectContaining({ name: "events", qualifierParts: ["analytics"], alias: "e" }));
    expect(sqlSemanticProjectionAliasColumns(buildSqlSemanticModel(groupBy.sql, groupBy.cursor, { databaseType: "mysql" })).map((column) => column.name)).toContain("total");
    expect(sqlSemanticProjectionAliasColumns(buildSqlSemanticModel(where.sql, where.cursor, { databaseType: "mysql" })).map((column) => column.name)).not.toContain("total");
  });

  it("covers SQLite and DuckDB schema-light local row-source behavior", () => {
    const sqlite = sqlFixtureCursor("SELECT * FROM main.users u WHERE u.|");
    const duckdb = sqlFixtureCursor("SELECT * FROM read_csv('users.csv') csv WHERE csv.|");

    expect(sqlSemanticCompletionScope(buildSqlSemanticModel(sqlite.sql, sqlite.cursor, { databaseType: "sqlite" })).useRemoteMetadata).toBe(true);
    expect(buildSqlSemanticModel(duckdb.sql, duckdb.cursor, { databaseType: "duckdb" }).rowSources[0]).toEqual(expect.objectContaining({ kind: "table_function", name: "csv", alias: "csv" }));
  });

  it("returns only concrete table-name spans for semantic highlighting", () => {
    const sql = "SELECT customer_id FROM dbo.wfAdmin AS wa WHERE wa.customer_id > 0";
    const spans = sqlSemanticTableNameSpans(sql, { dialect: "sqlserver" });

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["wfAdmin"]);
  });

  it("finds table names across statements, subqueries, and comma table lists", () => {
    const sql = "SELECT * FROM users u, orders o; SELECT * FROM (SELECT * FROM audit_log) a JOIN dbo.events e ON e.id = a.id";
    const spans = sqlSemanticTableNameSpans(sql, { dialect: "sqlserver" });

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["users", "orders", "audit_log", "events"]);
  });

  it("keeps table highlighting linear for a 24,000-line Oracle select list", () => {
    const sql = `SELECT\n${Array.from({ length: 23_997 }, (_, index) => `  t.col_${index} AS alias_${index},`).join("\n")}\n  t.final_col\nFROM app.huge_table t`;
    const startedAt = performance.now();
    const spans = sqlSemanticTableNameSpans(sql, { databaseType: "oracle" });
    const elapsedMs = performance.now() - startedAt;

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["huge_table"]);
    expect(elapsedMs).toBeLessThan(2_000);
  });

  it("highlights mutation targets but ignores strings, comments, and table functions", () => {
    const sql = "UPDATE users SET name = 'FROM fake'; INSERT INTO audit_log(id) VALUES (1); -- FROM ignored\nSELECT * FROM read_csv('events.csv') e";
    const spans = sqlSemanticTableNameSpans(sql);

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["users", "audit_log"]);
  });

  it("skips ASE maintenance keywords before update table targets", () => {
    const statements = [
      { sql: "UPDATE STATISTICS wfAdmin", table: "wfAdmin" },
      { sql: "UPDATE INDEX STATISTICS wfAdmin ix_name", table: "wfAdmin" },
      { sql: "UPDATE TABLE STATISTICS dbo.wfAdmin", table: "wfAdmin" },
      { sql: "UPDATE ALL STATISTICS [dbo].[wfAdmin]", table: "wfAdmin" },
    ];

    for (const { sql, table } of statements) {
      const spans = sqlSemanticTableNameSpans(sql, { dialect: "sqlserver" });
      const model = buildSqlSemanticModel(sql, sql.length, { dialect: "sqlserver" });
      expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual([sql.includes("[wfAdmin]") ? "[wfAdmin]" : table]);
      expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: table, kind: "mutation_target" })]));
      expect(model.rowSources.some((source) => ["ALL", "INDEX", "TABLE", "STATISTICS"].includes(source.name.toUpperCase()))).toBe(false);
    }
  });

  it("does not treat MERGE branch UPDATE as a table introducer", () => {
    const sql = "MERGE INTO target_table t USING source_table s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.name = s.name;";
    const spans = sqlSemanticTableNameSpans(sql, { dialect: "sqlserver" });
    const model = buildSqlSemanticModel(sql, sql.length - 1, { dialect: "sqlserver" });

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["target_table", "source_table"]);
    expect(model.rowSources.some((source) => source.name.toLowerCase() === "set")).toBe(false);
  });

  it("does not treat MySQL upsert UPDATE as a table introducer", () => {
    const sql = "INSERT INTO users (id, name) VALUES (1, 'A') ON DUPLICATE KEY UPDATE name = VALUES(name);";
    const spans = sqlSemanticTableNameSpans(sql, { dialect: "mysql" });
    const model = buildSqlSemanticModel(sql, sql.length - 1, { dialect: "mysql" });

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["users"]);
    expect(model.rowSources.some((source) => source.name === "name")).toBe(false);
  });

  it("keeps CTE UPDATE mutation targets", () => {
    const sql = "WITH candidates AS (SELECT id FROM staging) UPDATE users SET active = 1 WHERE id IN (SELECT id FROM candidates);";
    const spans = sqlSemanticTableNameSpans(sql, { dialect: "sqlserver" });
    const model = buildSqlSemanticModel(sql, sql.length - 1, { dialect: "sqlserver" });

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["staging", "users", "candidates"]);
    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "users", kind: "mutation_target" })]));
  });

  it("recognizes SQL Server local, global, and tempdb-qualified temporary tables", () => {
    const sql = "SELECT * FROM #temp; SELECT * FROM ##global_temp; SELECT * FROM tempdb..#temp";
    const spans = sqlSemanticTableNameSpans(sql, { dialect: "sqlserver" });
    const model = buildSqlSemanticModel(sql, sql.length, { dialect: "sqlserver" });

    expect(spans.map((span) => sql.slice(span.start, span.end))).toEqual(["#temp", "##global_temp", "#temp"]);
    expect(model.rowSources).toEqual(expect.arrayContaining([expect.objectContaining({ name: "#temp", kind: "table" })]));
  });
});

describe("CTE 富化边界与熔断", () => {
  function cteDefinitionOf(sql: string, cursor = sql.length) {
    const model = buildSqlSemanticModel(sql, cursor);
    const definition = model.rowSources.find((source) => source.id.startsWith("cte:"));
    expect(definition, "未解析出 CTE 定义源").toBeDefined();
    return definition!;
  }

  it("多段星号链 s.t.* 记录完整限定符链", () => {
    const sql = "WITH w AS (SELECT s.t.* FROM s.t) SELECT id FROM w";
    const definition = cteDefinitionOf(sql);
    const star = definition.cteStars?.[0];

    expect(definition.cteStars?.map((item) => item.qualifierParts)).toEqual([["s", "t"]]);
    expect(sql.slice(star!.starSpan.start, star!.starSpan.end)).toBe("*");
    expect(definition.cteOutputs).toEqual([]);
    expect(definition.bodySources?.map((source) => ({ name: source.name, qualifierParts: source.qualifierParts }))).toEqual([{ name: "t", qualifierParts: ["s"] }]);
  });

  it("无别名的表达式投影只提供跳转范围,不挂 origin", () => {
    const sql = "WITH w AS (SELECT n + 1 FROM orders) SELECT id FROM w";
    const definition = cteDefinitionOf(sql);
    const output = definition.cteOutputs?.[0];

    expect(output?.name).toBe("n");
    expect(output?.origin).toBeUndefined();
    expect(sql.slice(output!.jumpSpan.start, output!.jumpSpan.end)).toBe("n + 1");
  });

  it("body 没有顶层 SELECT(如 VALUES)时不产生星号,显式列名保留但无 origin", () => {
    const sql = "WITH w(a) AS (VALUES (1)) SELECT a FROM w";
    const definition = cteDefinitionOf(sql);

    expect(definition.cteStars).toEqual([]);
    expect(definition.cteOutputs?.map((column) => column.name)).toEqual(["a"]);
    expect(definition.cteOutputs?.[0]?.origin).toBeUndefined();
  });

  it("多星号 body 保留全部星号与来源表(供上层按序回退)", () => {
    const sql = "WITH w AS (SELECT a.*, b.* FROM t1 a, t2 b) SELECT c FROM w";
    const definition = cteDefinitionOf(sql);

    expect(definition.cteStars?.map((star) => star.qualifierParts)).toEqual([["a"], ["b"]]);
    expect(definition.bodySources?.map((source) => source.name)).toEqual(["t1", "t2"]);
  });

  it("派生表别名列 X(a) 按位改名到外层名字,origin 仍指向内层真实列", () => {
    const sql = "WITH w AS (SELECT X.a FROM (SELECT id FROM orders) X(a)) SELECT id FROM w";
    const definition = cteDefinitionOf(sql);
    const derived = definition.bodySources?.find((source) => source.name === "X");

    expect(derived?.columns).toEqual(["a"]);
    expect(derived?.cteOutputs?.map((column) => column.name)).toEqual(["a"]);
    expect(derived?.cteOutputs?.[0]?.origin).toEqual({ qualifierParts: [], column: "id" });
    expect(sql.slice(derived!.cteOutputs![0]!.jumpSpan.start, derived!.cteOutputs![0]!.jumpSpan.end)).toBe("id");
    expect(derived?.bodySources?.map((source) => source.name)).toEqual(["orders"]);
  });

  it("派生表别名列与星号共存时不改名(无法按位对应)", () => {
    const sql = "WITH w AS (SELECT X.a FROM (SELECT * FROM orders) X(a)) SELECT id FROM w";
    const definition = cteDefinitionOf(sql);
    const derived = definition.bodySources?.find((source) => source.name === "X");

    // columns 走宽松规则拿到别名,cteOutputs 走严格规则不接受改名 —— 两者故意不一致。
    expect(derived?.columns).toEqual(["a"]);
    expect(derived?.cteOutputs).toEqual([]);
    expect(derived?.cteStars).toHaveLength(1);
  });

  it("派生表别名列长度与投影长度不符时不改名,count 少的一方不被补齐", () => {
    const sql = "WITH w AS (SELECT X.a FROM (SELECT id FROM orders) X(a, b)) SELECT id FROM w";
    const definition = cteDefinitionOf(sql);
    const derived = definition.bodySources?.find((source) => source.name === "X");

    // 别名多于投影:columns 按位截断为 ["a"],cteOutputs 保持内层名字 ["id"]。
    expect(derived?.columns).toEqual(["a"]);
    expect(derived?.cteOutputs?.map((column) => column.name)).toEqual(["id"]);
  });

  it("括号未闭合时 bodySpan 收敛到已解析的最后一个 token,不越界", () => {
    const sql = "WITH w AS (SELECT id FROM orders";
    const definition = cteDefinitionOf(sql, 0);

    expect(sql.slice(definition.bodySpan!.start, definition.bodySpan!.end)).toBe("(SELECT id FROM orders");
    expect(definition.bodySpan!.end).toBeLessThanOrEqual(sql.length);
  });

  it("语句 token 超限时整体跳过富化,扁平列名仍可用", () => {
    const filler = Array.from({ length: 7000 }, () => "a = 1 or").join(" ");
    const sql = `WITH w AS (SELECT id FROM orders) SELECT id FROM w where ${filler} 1 = 1`;
    const definition = cteDefinitionOf(sql, 0);

    expect(definition.cteOutputs).toBeUndefined();
    expect(definition.cteStars).toBeUndefined();
    expect(definition.bodySources).toBeUndefined();
    expect(definition.columns).toEqual(["id"]);
  });

  it("仅 body token 超限时只跳过该 body,同语句其它 CTE 仍完整富化", () => {
    const projections = Array.from({ length: 2501 }, (_, index) => `c${index}`).join(",");
    const sql = `WITH w AS (SELECT ${projections} FROM orders), small AS (SELECT id FROM orders) SELECT id FROM small`;
    const model = buildSqlSemanticModel(sql, 0);
    const definitions = model.rowSources.filter((source) => source.id.startsWith("cte:"));
    const oversized = definitions.find((source) => source.name === "w");
    const neighbor = definitions.find((source) => source.name === "small");

    expect(oversized?.cteOutputs).toBeUndefined();
    expect(oversized?.cteStars).toBeUndefined();
    expect(oversized?.bodySources).toBeUndefined();
    expect(oversized?.columns).toHaveLength(2501);
    // 同语句里的其它 CTE 不受影响,说明熔断是按 body 粒度生效的。
    expect(neighbor?.cteOutputs?.map((column) => column.name)).toEqual(["id"]);
    expect(neighbor?.bodySources?.map((source) => source.name)).toEqual(["orders"]);
  });
});