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。

5.9 KB
Raw Blame
import { strict as assert } from "node:assert";
import { test } from "vitest";
import { buildProcedureExecutionSql, buildProcedureExecutionSqlFromValues } from "../../apps/desktop/src/lib/table/routineExecutionSql.ts";
import { routineParametersFromResult, routineParametersQuery } from "../../apps/desktop/src/lib/table/routineParameters.ts";

test("builds procedure execution templates for common dialect families", () => {
  assert.equal(buildProcedureExecutionSql({ databaseType: "postgres", schema: "public", routineName: "refresh_stats" }), 'CALL "public"."refresh_stats"();');
  assert.equal(buildProcedureExecutionSql({ databaseType: "mysql", schema: "app", routineName: "refresh_stats" }), "CALL `refresh_stats`();");
  assert.equal(buildProcedureExecutionSql({ databaseType: "sqlserver", schema: "dbo", routineName: "refresh_stats" }), "EXEC [dbo].[refresh_stats];");
  assert.equal(buildProcedureExecutionSql({ databaseType: "dameng", schema: "SYSDBA", routineName: "refresh_stats" }), 'BEGIN\n  "SYSDBA"."refresh_stats"();\nEND;');
  assert.equal(buildProcedureExecutionSql({ databaseType: "databend", schema: "default", routineName: "refresh_stats" }), "CALL PROCEDURE refresh_stats();");
});

test("builds procedure execution templates from parameter values", () => {
  const parameters = [
    { name: "p_message", dataType: "text", mode: "IN" as const, ordinal: 1, value: "it's ready" },
    { name: "p_count", dataType: "integer", mode: "IN" as const, ordinal: 2, value: "3" },
    { name: "p_debug", dataType: "boolean", mode: "IN" as const, ordinal: 3, value: "true" },
    { name: "p_result", dataType: "text", mode: "OUT" as const, ordinal: 4, value: "" },
  ];

  assert.equal(
    buildProcedureExecutionSqlFromValues({
      databaseType: "postgres",
      schema: "public",
      routineName: "refresh_stats",
      parameters,
    }),
    'CALL "public"."refresh_stats"(' + "'it''s ready', 3, TRUE);",
  );

  assert.equal(
    buildProcedureExecutionSqlFromValues({
      databaseType: "sqlserver",
      schema: "dbo",
      routineName: "refresh_stats",
      parameters,
    }),
    [
      "DECLARE @dbx_output_4 text;",
      "EXEC [dbo].[refresh_stats] @p_message = 'it''s ready', @p_count = 3, @p_debug = 1, @p_result = @dbx_output_4 OUTPUT;",
      "SELECT @dbx_output_4 AS [p_result];",
    ].join("\n"),
  );
});

test("omits parameters that use database defaults", () => {
  assert.equal(
    buildProcedureExecutionSqlFromValues({
      databaseType: "postgres",
      schema: "public",
      routineName: "refresh_stats",
      parameters: [
        {
          name: "p_message",
          dataType: "text",
          mode: "IN",
          ordinal: 1,
          value: "",
          hasDefault: true,
          useDefault: true,
        },
      ],
    }),
    'CALL "public"."refresh_stats"();',
  );
});

test("uses named arguments when a defaulted positional argument is skipped", () => {
  assert.equal(
    buildProcedureExecutionSqlFromValues({
      databaseType: "postgres",
      schema: "public",
      routineName: "refresh_stats",
      parameters: [
        {
          name: "p_first",
          dataType: "text",
          mode: "IN",
          ordinal: 1,
          value: "",
          hasDefault: true,
          useDefault: true,
        },
        { name: "p_second", dataType: "integer", mode: "IN", ordinal: 2, value: "8" },
      ],
    }),
    'CALL "public"."refresh_stats"("p_second" => 8);',
  );

  assert.equal(
    buildProcedureExecutionSqlFromValues({
      databaseType: "oracle",
      schema: "APP",
      routineName: "refresh_stats",
      parameters: [
        {
          name: "P_FIRST",
          dataType: "VARCHAR2",
          mode: "IN",
          ordinal: 1,
          value: "",
          hasDefault: true,
          useDefault: true,
        },
        { name: "P_SECOND", dataType: "NUMBER", mode: "IN", ordinal: 2, value: "8" },
      ],
    }),
    'BEGIN\n  "APP"."refresh_stats"("P_SECOND" => 8);\nEND;',
  );
});

test("maps routine parameter query results into form metadata", () => {
  const parameters = routineParametersFromResult({
    columns: ["name", "data_type", "mode", "ordinal", "has_default"],
    rows: [
      ["p_message", "text", "IN", 1, true],
      ["p_total", "integer", "OUT", 2, false],
    ],
    affected_rows: 0,
    execution_time_ms: 1,
  });

  assert.deepEqual(parameters, [
    { name: "p_message", dataType: "text", mode: "IN", ordinal: 1, hasDefault: true },
    { name: "p_total", dataType: "integer", mode: "OUT", ordinal: 2, hasDefault: false },
  ]);
});

test("maps Databend procedure signatures into positional input parameters", () => {
  const parameters = routineParametersFromResult(
    {
      columns: ["arguments"],
      rows: [["convert_weight(Decimal(10, 2), String) RETURN (Decimal(10, 2))"]],
      affected_rows: 0,
      execution_time_ms: 1,
    },
    "databend",
  );

  assert.deepEqual(parameters, [
    { name: "arg1", dataType: "Decimal(10, 2)", mode: "IN", ordinal: 1, hasDefault: false },
    { name: "arg2", dataType: "String", mode: "IN", ordinal: 2, hasDefault: false },
  ]);
});

test("builds parameter metadata queries for supported dialects", () => {
  assert.match(routineParametersQuery({ database: "app", databaseType: "postgres", schema: "public", routineName: "demo" }) ?? "", /pg_proc/);
  assert.match(routineParametersQuery({ database: "app", databaseType: "sqlserver", schema: "dbo", routineName: "demo" }) ?? "", /sys\.parameters/);
  assert.match(routineParametersQuery({ database: "app", databaseType: "mysql", routineName: "demo" }) ?? "", /SPECIFIC_SCHEMA = 'app'/);
  assert.match(routineParametersQuery({ database: "default", databaseType: "databend", schema: "default", routineName: "demo" }) ?? "", /system\.procedures/);
  assert.match(routineParametersQuery({ database: "app", databaseType: "postgres", routineName: "demo" }) ?? "", /n\.nspname = 'public'/);
  assert.equal(routineParametersQuery({ database: "app", databaseType: "sqlite", schema: "main", routineName: "demo" }), null);
});