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.5 KB
Raw Blame
import type { ColumnInfo, DatabaseType } from "@/types/database";
import type { SqlFormatterCase } from "@/lib/sql/sqlFormatterConfig";
import { metricRangeQuery, qualifiedTableName, quoteTableDataIdentifier, quoteTableIdentifierIfNeeded } from "@/lib/table/tableSelectSql";

export interface TableSqlTemplateOptions {
  databaseType?: DatabaseType;
  driverProfile?: string;
  identifierQuote?: string;
  schema?: string;
  catalog?: string;
  database?: string;
  includeDatabaseName?: boolean;
  /** Connection default schema; the PostgreSQL family qualifies tables outside it (#13235). */
  defaultSchema?: string;
  /** Omit optional identifier quotes while retaining quotes required by the dialect. */
  quoteIdentifiers?: boolean;
  keywordCase?: SqlFormatterCase;
  tableName: string;
  columns?: ColumnInfo[];
  tableType?: string;
  /** Number of INSERT statements to generate; values are numbered per row when greater than 1. */
  rowCount?: number;
}

/** Upper bound for INSERT template row generation, shared with the row-count dialog. */
export const INSERT_TEMPLATE_MAX_ROW_COUNT = 1000;

function normalizeInsertTemplateRowCount(rowCount: number | undefined): number {
  if (!rowCount || !Number.isFinite(rowCount) || rowCount <= 1) return 1;
  return Math.min(Math.floor(rowCount), INSERT_TEMPLATE_MAX_ROW_COUNT);
}

function insertTemplateStatements(rowCount: number, buildStatement: (rowIndex?: number) => string): string {
  if (rowCount <= 1) return buildStatement();
  const statements: string[] = [];
  for (let rowIndex = 0; rowIndex < rowCount; rowIndex += 1) statements.push(buildStatement(rowIndex));
  return statements.join("\n\n");
}

export function buildTableSelectTemplate(options: TableSqlTemplateOptions): string {
  if (options.databaseType === "victoriametrics") return metricRangeQuery(options.tableName);
  const tableName = templateTableName(options);
  const columns = options.columns ?? [];
  const lower = options.keywordCase === "lower";
  const selectKw = lower ? "select" : "SELECT";
  const fromKw = lower ? "from" : "FROM";
  if (!columns.length) {
    return `${selectKw} *\n${fromKw} ${tableName};`;
  }
  const selectColumns = columns.map((column) => templateIdentifier(options, column.name));
  return `${selectKw} ${selectColumns.join(", ")}\n${fromKw} ${tableName};`;
}

export function buildTableInsertTemplate(options: TableSqlTemplateOptions): string {
  const strategy = options.databaseType ? insertTemplateStrategies[options.databaseType] : undefined;
  return (strategy ?? buildDefaultInsertTemplate)(options);
}

type InsertTemplateStrategy = (options: TableSqlTemplateOptions) => string;

function buildDefaultInsertTemplate(options: TableSqlTemplateOptions): string {
  const tableName = templateTableName(options);
  const columns = (options.columns ?? []).filter((column) => !isAutoGeneratedColumn(column));
  const rowCount = normalizeInsertTemplateRowCount(options.rowCount);

  // TDengine does not support INSERT INTO t(col1, col2) VALUES(...) syntax
  if (options.databaseType === "tdengine") {
    if (!columns.length) {
      return `INSERT INTO ${tableName}\nVALUES (/* TODO: add values */);`;
    }
    return insertTemplateStatements(rowCount, (rowIndex) => `INSERT INTO ${tableName}\nVALUES (${columns.map((column) => columnPlaceholderValue(column, options.databaseType, rowIndex)).join(", ")});`);
  }

  if (!columns.length) {
    return `INSERT INTO ${tableName}\n/* TODO: add column values */\nVALUES ();`;
  }

  const columnNames = columns.map((column) => templateIdentifier(options, column.name));
  return insertTemplateStatements(rowCount, (rowIndex) => `INSERT INTO ${tableName} (${columnNames.join(", ")})\nVALUES (${columns.map((column) => columnPlaceholderValue(column, options.databaseType, rowIndex)).join(", ")});`);
}

function buildTdengineInsertTemplate(options: TableSqlTemplateOptions): string {
  const rowCount = normalizeInsertTemplateRowCount(options.rowCount);
  const columns = options.columns ?? [];
  const isTagColumn = (column: ColumnInfo) => (column.comment ?? "").toUpperCase() === "TAG" || (column.extra ?? "").toUpperCase().includes("TAG");
  const tagColumns = columns.filter((column) => isTagColumn(column));
  const normalizedTableType = (options.tableType ?? "").toUpperCase();
  const shouldUseStableTemplate = normalizedTableType === "STABLE" || (!normalizedTableType && tagColumns.length > 0);

  if (!shouldUseStableTemplate) {
    return buildDefaultInsertTemplate(options);
  }

  const valueColumns = columns.filter((column) => !isTagColumn(column)).filter((column) => !isAutoGeneratedColumn(column));
  const tagPlaceholders = tagColumns.map((column) => columnPlaceholderValue(column, options.databaseType));
  const tagsSql = tagPlaceholders.length > 0 ? `TAGS (${tagPlaceholders.join(", ")})` : "TAGS (/* TODO: add tag values */)";
  const stableNameForUsing = templateTableName(options).replace(/^`|`$/g, "");

  if (!valueColumns.length) {
    return `INSERT INTO /* child_table_name */ USING ${stableNameForUsing}\n${tagsSql}\nVALUES (/* TODO: add values */);`;
  }

  return insertTemplateStatements(rowCount, (rowIndex) => `INSERT INTO /* child_table_name */ USING ${stableNameForUsing}\n${tagsSql}\nVALUES (${valueColumns.map((column) => columnPlaceholderValue(column, options.databaseType, rowIndex)).join(", ")});`);
}

const insertTemplateStrategies: Partial<Record<DatabaseType, InsertTemplateStrategy>> = {
  tdengine: buildTdengineInsertTemplate,
};

export function buildTableUpdateTemplate(options: TableSqlTemplateOptions): string {
  const tableName = templateTableName(options);
  const columns = options.columns ?? [];
  const primaryKeys = columns.filter((column) => column.is_primary_key);
  const primaryKeyNames = new Set(primaryKeys.map((column) => column.name));
  const updateColumns = columns.filter((column) => !primaryKeyNames.has(column.name));
  const setClause = updateColumns.length ? updateColumns.map((column) => `${templateIdentifier(options, column.name)} = ${columnPlaceholderValue(column, options.databaseType)}`).join(",\n    ") : "/* TODO: set column = value */";

  return `UPDATE ${tableName}\nSET ${setClause}\n${buildWhereClause(options, primaryKeys)};`;
}

export function buildTableDeleteTemplate(options: TableSqlTemplateOptions): string {
  const tableName = templateTableName(options);
  const primaryKeys = (options.columns ?? []).filter((column) => column.is_primary_key);
  return `DELETE FROM ${tableName}\n${buildWhereClause(options, primaryKeys)};`;
}

export interface InsertTemplatePkConflict {
  column: string;
  reason: "numeric-cap" | "static-value";
  cap?: number;
}

/**
 * Primary-key columns that cannot hold `rowCount` distinct placeholder values:
 * narrow numeric keys capped below the row count, and date/time keys whose
 * placeholder stays static across rows. Auto-generated keys are skipped
 * because they are omitted from the generated INSERT column list.
 */
export function insertTemplatePrimaryKeyConflicts(columns: ColumnInfo[], rowCount: number): InsertTemplatePkConflict[] {
  if (rowCount <= 1) return [];
  const conflicts: InsertTemplatePkConflict[] = [];
  for (const column of columns) {
    if (!column.is_primary_key || isAutoGeneratedColumn(column)) continue;
    const dataType = (column.data_type ?? "").trim();
    if (typeLooksNumeric(dataType)) {
      const cap = numericRowValueCap(column);
      if (cap !== null && cap < rowCount) conflicts.push({ column: column.name, reason: "numeric-cap", cap });
    } else if (typeLooksDateOnly(dataType) || typeLooksTimeOnly(dataType) || typeLooksTimestamp(dataType)) {
      conflicts.push({ column: column.name, reason: "static-value" });
    }
  }
  return conflicts;
}

function templateTableName(options: TableSqlTemplateOptions): string {
  return qualifiedTableName({
    databaseType: options.databaseType,
    driverProfile: options.driverProfile,
    identifierQuote: options.identifierQuote,
    catalog: options.catalog,
    database: options.database,
    includeDatabaseName: options.includeDatabaseName,
    defaultSchema: options.defaultSchema,
    quoteIdentifiers: options.quoteIdentifiers,
    schema: options.schema,
    tableName: options.tableName,
  });
}

function templateIdentifier(options: TableSqlTemplateOptions, name: string): string {
  return options.quoteIdentifiers === false ? quoteTableIdentifierIfNeeded(options.databaseType, name, options.identifierQuote) : quoteTableDataIdentifier(options.databaseType, name, options.identifierQuote);
}

function buildWhereClause(options: TableSqlTemplateOptions, primaryKeys: ColumnInfo[]): string {
  if (!primaryKeys.length) return "WHERE /* TODO: add WHERE clause */";
  const conditions = primaryKeys.map((column) => `${templateIdentifier(options, column.name)} = ${columnPlaceholderValue(column, options.databaseType)}`);
  return `WHERE ${conditions.join(" AND ")}`;
}

function isAutoGeneratedColumn(column: ColumnInfo): boolean {
  const extra = (column.extra ?? "").toLowerCase().trim();
  const colDefault = (column.column_default ?? "").toLowerCase().trim();
  const dataType = (column.data_type ?? "").trim();

  if (typeLooksPostgresTextSearchVector(dataType)) return true;
  if (extra.includes("auto_increment")) return true;
  if (extra.includes("autoincrement")) return true;
  if (extra.includes("identity")) return true;
  if (extra.includes("generated") && (extra.includes("as identity") || extra.includes("always as"))) return true;
  if (colDefault.includes("nextval(")) return true;
  if (colDefault.includes(".nextval")) return true;
  if ((extra.includes("virtual") || extra.includes("computed")) && extra.includes("generated")) return true;

  return false;
}

function columnPlaceholderValue(column: ColumnInfo, databaseType?: DatabaseType, rowIndex?: number): string {
  const dataType = (column.data_type ?? "").trim();
  const colName = column.name;
  const rowNumber = rowIndex === undefined ? undefined : rowIndex + 1;

  if (typeLooksNumeric(dataType)) {
    if (rowNumber === undefined) return "0";
    const cap = numericRowValueCap(column);
    return String(cap === null ? rowNumber : Math.min(rowNumber, cap));
  }
  if (typeLooksBoolean(dataType)) return databaseType === "mysql" || databaseType === "sqlite" || databaseType === "rqlite" || databaseType === "cloudflare-d1" ? "1" : "TRUE";
  if (typeLooksDateOnly(dataType)) return "'2024-01-01'";
  if (typeLooksTimeOnly(dataType)) return "'12:00:00'";
  if (typeLooksTimestamp(dataType)) return databaseType === "tdengine" ? "NOW" : "CURRENT_TIMESTAMP";
  if (typeLooksJson(dataType)) return databaseType === "postgres" || databaseType === "redshift" ? "'{}'::jsonb" : "'{}'";
  if (typeLooksBinary(dataType)) return "''";
  if (typeLooksUuid(dataType)) {
    const uuid = rowNumber === undefined ? "00000000-0000-0000-0000-000000000000" : randomUuidValue();
    return databaseType === "postgres" || databaseType === "redshift" ? `'${uuid}'::uuid` : `'${uuid}'`;
  }
  if (typeLooksXml(dataType)) return "'<root/>'";
  if (typeLooksSpatial(dataType)) return "NULL";
  if (typeLooksArray(dataType)) return "'{}'";

  return stringPlaceholderValue(colName, rowNumber, column.character_maximum_length);
}

// Byte-aware budget so byte-length columns (Oracle VARCHAR2 N BYTE) stay safe
// even when the column name carries multi-byte characters.
function utf8Length(value: string): number {
  let length = 0;
  for (const char of value) {
    const code = char.codePointAt(0)!;
    length += code < 0x80 ? 1 : code < 0x800 ? 2 : code < 0x10000 ? 3 : 4;
  }
  return length;
}

/**
 * `col_value[_row]` placeholder clipped to the column's declared length: the
 * name part shrinks so the row-suffix (the per-row distinct part) survives.
 * When even the suffix cannot fit, degrade to the bare row number.
 */
function stringPlaceholderValue(colName: string, rowNumber: number | undefined, maxLength: number | null | undefined): string {
  const suffix = `_value${rowNumber === undefined ? "" : `_${rowNumber}`}`;
  const limit = typeof maxLength === "number" && Number.isFinite(maxLength) && maxLength > 0 ? maxLength : null;
  if (limit === null) return `'${colName}${suffix}'`;
  const suffixLength = utf8Length(suffix);
  if (suffixLength > limit) {
    if (rowNumber === undefined) return `'${"_value".slice(0, limit)}'`;
    const fallback = String(rowNumber);
    return `'${utf8Length(fallback) <= limit ? fallback : [...fallback].slice(0, Math.max(1, limit)).join("")}'`;
  }
  const nameBudget = limit - suffixLength;
  let name = "";
  let used = 0;
  for (const char of colName) {
    const charLength = utf8Length(char);
    if (used + charLength > nameBudget) break;
    name += char;
    used += charLength;
  }
  return `'${name}${suffix}'`;
}

function randomUuidValue(): string {
  const uuid = globalThis.crypto?.randomUUID?.();
  if (uuid) return uuid;
  return "xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx".replace(/[xy]/g, (char) => {
    const random = (Math.random() * 16) | 0;
    const value = char === "x" ? random : (random & 0x3) | 0x8;
    return value.toString(16);
  });
}

// Unsigned metadata is not exposed per column, so narrow integer types use the
// conservative signed upper bound when capping per-row placeholder numbers.
function numericRowValueCap(column: ColumnInfo): number | null {
  const dataType = (column.data_type ?? "").trim().toLowerCase();
  if (dataType === "tinyint" || dataType === "int8") return 127;
  const precision = column.numeric_precision;
  if (typeof precision !== "number" || !Number.isFinite(precision) || precision <= 0) return null;
  const scale = typeof column.numeric_scale === "number" && Number.isFinite(column.numeric_scale) && column.numeric_scale > 0 ? column.numeric_scale : 0;
  const integerDigits = precision - scale;
  // A pure-fraction column (scale >= precision) cannot hold any nonzero integer; keep 0 so statements stay valid.
  if (integerDigits <= 0) return 0;
  return 10 ** integerDigits - 1;
}

function typeLooksNumeric(dataType: string): boolean {
  return /^(tinyint|smallint|mediumint|int\d*|integer\d*|bigint|smallserial|serial\d*|bigserial|number|numeric|decimal|dec|float\d*|double|real|double\s+precision|money|smallmoney|fixed)/i.test(dataType.trim());
}

function typeLooksBoolean(dataType: string): boolean {
  return /^(bool|boolean|bit\s*\(\s*1\s*\))$/i.test(dataType.trim());
}

function typeLooksDateOnly(dataType: string): boolean {
  return /^date$/i.test(dataType.trim());
}

function typeLooksTimeOnly(dataType: string): boolean {
  return /^time\b(?!stamp|stamptz|with|without)/i.test(dataType.trim());
}

function typeLooksTimestamp(dataType: string): boolean {
  return /^(datetime|timestamp|timestamptz|timestamp\s+with\s+time\s+zone|timestamp\s+without\s+time\s+zone|smalldatetime|datetime2|datetimeoffset)/i.test(dataType.trim());
}

function typeLooksJson(dataType: string): boolean {
  return /^(json|jsonb)$/i.test(dataType.trim());
}

function typeLooksBinary(dataType: string): boolean {
  return /^(bytea|blob|binary|varbinary|image|raw|longblob|mediumblob|tinyblob|long\s+raw|bfile)$/i.test(dataType.trim());
}

function typeLooksUuid(dataType: string): boolean {
  return /^uuid$/i.test(dataType.trim());
}

function typeLooksXml(dataType: string): boolean {
  return /^xml$/i.test(dataType.trim());
}

function typeLooksSpatial(dataType: string): boolean {
  return /^(geometry|geography|point|polygon|linestring|box|circle|path|line|lseg)/i.test(dataType.trim());
}

function typeLooksArray(dataType: string): boolean {
  return /^(array|vector|_float\d*|_int\d*|_text|_varchar|_bool|_uuid)/i.test(dataType.trim());
}

function typeLooksPostgresTextSearchVector(dataType: string): boolean {
  const normalized = dataType
    .trim()
    .replace(/^"+|"+$/g, "")
    .toLowerCase();
  return normalized === "tsvector" || normalized.endsWith(".tsvector");
}