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。

39.8 KB
Raw Blame
import type { DatabaseType, QueryResult } from "@/types/database";
import * as api from "@/lib/backend/api";
import { supportsDatabaseFeature } from "@/lib/database/databaseDriverManifest";
import { isQueryExecutionErrorResult } from "@/lib/query/queryResultError";
import { parseDb2ExplainText } from "./db2ExplainPlan";
export { parseDb2ExplainText } from "./db2ExplainPlan";
import { parseXuguExplainResult } from "./xuguExplainPlan";

export interface ExplainPlanNode {
  id: string;
  title: string;
  nodeType: string;
  /** Optional dialect hint for engine-specific operator presentation. */
  dialect?: ExplainPlanDatabaseType;
  relation?: string;
  index?: string;
  cost?: string;
  /** Suppress derived cost shares when costs cannot be safely attributed to nodes. */
  costModel?: "unknown";
  rows?: string;
  width?: string;
  estimatedTimeUs?: string;
  details: string[];
  children: ExplainPlanNode[];
}

export type ExplainPlanDatabaseType = "mysql" | "postgres" | "opengauss" | "dameng" | "questdb" | "doris" | "oracle" | "oceanbase-oracle" | "sqlserver" | "xugu" | "db2";

export interface ParsedExplainPlan {
  databaseType: ExplainPlanDatabaseType;
  raw: unknown;
  nodes: ExplainPlanNode[];
}

export type BuildExplainSqlResult = { ok: true; sql: string } | { ok: false; reason: "unsupported" | "empty" | "unsafe" };

export function formatExplainPlanDetails(node: ExplainPlanNode | undefined, estimatedTimeLabel: string): string[] {
  if (!node) return [];
  return node.estimatedTimeUs === undefined ? node.details : [`${estimatedTimeLabel}: ${node.estimatedTimeUs} µs`, ...node.details];
}

const SUPPORTED_EXPLAIN_TYPES = new Set<DatabaseType>(["mysql", "postgres", "opengauss", "dameng", "questdb", "doris", "oracle", "oceanbase-oracle", "sqlserver", "xugu", "db2"]);
export function supportsExplainPlan(databaseType?: DatabaseType): databaseType is ExplainPlanDatabaseType {
  return !!databaseType && supportsDatabaseFeature(databaseType, "sqlExplain") && SUPPORTED_EXPLAIN_TYPES.has(databaseType);
}

/** `analyze` is honored by PostgreSQL only; every other engine ignores it server-side. */
export function buildExplainSql(databaseType: DatabaseType | undefined, sql: string, format: "json" | "standard" = "json", analyze?: boolean): Promise<BuildExplainSqlResult> {
  return api.buildExplainSql({ databaseType, sql, format, analyze }) as Promise<BuildExplainSqlResult>;
}

export function parseExplainResult(databaseType: ExplainPlanDatabaseType, result: QueryResult): ParsedExplainPlan {
  if (databaseType === "db2") {
    if (isQueryExecutionErrorResult(result)) throw new Error(String(result.rows[0]?.[0] ?? "DB2 EXPLAIN failed"));
    return parseDb2ExplainText(String(result.rows[0]?.[0] ?? ""));
  } else if (databaseType === "xugu") {
    return parseXuguExplainResult(result);
  } else if (databaseType === "dameng") {
    return parseDamengExplain(result);
  } else if (databaseType === "opengauss") {
    return parseOpenGaussExplain(result);
  } else if (databaseType === "questdb") {
    return parseQuestdbExplain(result);
  } else if (databaseType === "doris") {
    return parseDorisExplain(result);
  } else if (databaseType === "sqlserver") {
    return parseSqlServerExplain(result);
  } else if (databaseType === "oceanbase-oracle") {
    return parseOceanbaseOracleExplain(result);
  }
  const raw = parseExplainCell(result.rows[0]?.[0]);
  const nodes = databaseType === "postgres" ? parsePostgresExplain(raw) : parseMysqlExplain(raw);
  return { databaseType, raw, nodes };
}

/**
 * Parse DM's getExplainInfo() text output.
 * Format (flat list with indentation):
 *   1   #NSET2: [cost, rows, width]
 *   2     #PIPE2: [cost, rows, width]
 *   3       #PRJT2: [cost, rows, width]; props...
 *   ...
 *   Statistics
 *       logical reads
 *       exec time(ms)
 *
 * For autotrace, rows include ->actual: [cost, estRows->actualRows, width]
 */
export function parseDamengExplainText(planText: string): ParsedExplainPlan {
  const lines = planText.split("\n");
  const operatorLines: { indent: number; line: string; raw: string }[] = [];
  const stats: Record<string, string> = {};
  let inStats = false;

  for (const rawLine of lines) {
    const trimmed = rawLine.trim();
    if (!trimmed) continue;

    // Check for Statistics section
    if (trimmed.toLowerCase() === "statistics") {
      inStats = true;
      continue;
    }
    if (inStats) {
      const m = trimmed.match(/^(\d+)\s+(.+)$/);
      if (m) {
        stats[m[2].trim()] = m[1];
      }
      continue;
    }

    // Try to parse operator line: <line_number><spaces>#OPERATOR...
    const opMatch = trimmed.match(/^\d+(\s+)#/);
    if (opMatch) {
      // indent = number of spaces between line number and #
      const indent = opMatch[1].length;
      operatorLines.push({ indent, line: trimmed.substring(trimmed.indexOf("#")), raw: rawLine });
    }
  }

  // Build tree from flat operator lines using indent spacing
  // First line's indent = baseline (depth 0), each +2 spaces = +1 depth
  const baseIndent = operatorLines.length > 0 ? operatorLines[0].indent : 0;
  const rootNodes: ExplainPlanNode[] = [];
  const parentStack: ExplainPlanNode[] = [];

  for (const { indent, line } of operatorLines) {
    const nodeInfo = parseDamengPlanLine(line);
    if (!nodeInfo) continue;

    const depth = Math.max(0, Math.round((indent - baseIndent) / 2));
    while (parentStack.length > depth) parentStack.pop();

    const childIndex = parentStack.length === 0 ? rootNodes.length + 1 : parentStack[parentStack.length - 1].children.length + 1;
    const id = parentStack.length === 0 ? String(childIndex) : `${parentStack[parentStack.length - 1].id}.${childIndex}`;

    const details: string[] = [];
    if (nodeInfo.props) details.push(nodeInfo.props);
    if (nodeInfo.actualRows) details.push(`Actual Rows: ${nodeInfo.actualRows}`);
    if (nodeInfo.memUsed) details.push(`Memory: ${nodeInfo.memUsed}`);
    if (nodeInfo.diskUsed) details.push(`Disk: ${nodeInfo.diskUsed}`);

    const node: ExplainPlanNode = {
      id,
      title: nodeInfo.relation ? `${nodeInfo.nodeType} on ${nodeInfo.relation}` : nodeInfo.nodeType,
      nodeType: nodeInfo.operation,
      cost: nodeInfo.cost || undefined,
      rows: nodeInfo.rows || undefined,
      relation: nodeInfo.relation || undefined,
      details,
      children: [],
    };

    if (parentStack.length === 0) {
      rootNodes.push(node);
    } else {
      parentStack[parentStack.length - 1].children.push(node);
    }
    parentStack.push(node);
  }

  // Add stats summary to root node
  if (Object.keys(stats).length > 0) {
    const statsDetail = Object.entries(stats)
      .map(([k, v]) => `${k}: ${v}`)
      .join(", ");
    if (rootNodes.length > 0 && !rootNodes[0].details.some((d) => d.startsWith("Statistics:"))) {
      rootNodes[0].details.push(`Statistics: ${statsDetail}`);
    }
  }

  return { databaseType: "dameng", raw: planText, nodes: rootNodes };
}

export function parseOracleExplainText(planText: string): ParsedExplainPlan {
  const lines = planText.split("\n");
  const headerIndex = lines.findIndex((line) => /^\|\s*Id\s*\|/i.test(line) && line.includes("Operation"));
  if (headerIndex < 0) return { databaseType: "oracle", raw: planText, nodes: [] };

  const headers = splitOraclePlanColumns(lines[headerIndex]).map((header) => header.trim().toLowerCase().replace(/\s+/g, " "));
  const columnIndex = (name: string) => headers.findIndex((header) => header === name || header.startsWith(`${name} `));
  const idIndex = columnIndex("id");
  const operationIndex = columnIndex("operation");
  const nameIndex = columnIndex("name");
  const rowsIndex = columnIndex("rows");
  const bytesIndex = columnIndex("bytes");
  const costIndex = columnIndex("cost");
  const timeIndex = columnIndex("time");
  const predicates = oraclePredicateDetails(lines);

  const parsedRows: Array<{
    id: string;
    depth: number;
    operation: string;
    name?: string;
    rows?: string;
    cost?: string;
    bytes?: string;
    time?: string;
  }> = [];
  let baseIndent: number | undefined;

  for (const line of lines.slice(headerIndex + 1)) {
    if (!line.startsWith("|")) continue;
    const cells = splitOraclePlanColumns(line);
    const idMatch = cells[idIndex]?.match(/\d+/);
    const operationCell = cells[operationIndex];
    if (!idMatch || operationCell == null) continue;

    const indent = operationCell.search(/\S/);
    if (indent < 0) continue;
    baseIndent ??= indent;
    parsedRows.push({
      id: idMatch[0],
      depth: Math.max(0, indent - baseIndent),
      operation: operationCell.trim(),
      name: cellValue(cells, nameIndex),
      rows: cellValue(cells, rowsIndex),
      cost: cellValue(cells, costIndex),
      bytes: cellValue(cells, bytesIndex),
      time: cellValue(cells, timeIndex),
    });
  }

  const roots: ExplainPlanNode[] = [];
  const parents: ExplainPlanNode[] = [];
  for (const row of parsedRows) {
    const isIndex = /\bINDEX\b/i.test(row.operation) && !/\bTABLE ACCESS\b/i.test(row.operation);
    const relation = row.name && !isIndex ? row.name : undefined;
    const index = row.name && isIndex ? row.name : undefined;
    const details = [row.bytes ? `Bytes: ${row.bytes}` : "", row.time ? `Time: ${row.time}` : "", ...(predicates.get(row.id) ?? []).map((predicate) => `Predicate: ${predicate}`)].filter(Boolean);
    const node: ExplainPlanNode = {
      id: row.id,
      title: row.name ? `${row.operation} on ${row.name}` : row.operation,
      nodeType: row.operation,
      relation,
      index,
      cost: row.cost,
      rows: row.rows,
      details,
      children: [],
    };

    while (parents.length > row.depth) parents.pop();
    const parent = row.depth > 0 ? parents[row.depth - 1] : undefined;
    if (parent) parent.children.push(node);
    else roots.push(node);
    parents[row.depth] = node;
    parents.length = row.depth + 1;
  }

  return { databaseType: "oracle", raw: planText, nodes: roots };
}

function splitOraclePlanColumns(line: string): string[] {
  const columns = line.split("|");
  return columns.length >= 3 ? columns.slice(1, -1) : [];
}

function cellValue(cells: string[], index: number): string | undefined {
  if (index < 0) return undefined;
  const value = cells[index]?.trim();
  return value || undefined;
}

function oraclePredicateDetails(lines: string[]): Map<string, string[]> {
  const predicates = new Map<string, string[]>();
  const start = lines.findIndex((line) => line.trim().toLowerCase().startsWith("predicate information"));
  if (start < 0) return predicates;

  let currentId = "";
  for (const rawLine of lines.slice(start + 1)) {
    const line = rawLine.trim();
    if (!line || /^-+$/.test(line)) continue;
    if (/^note\b/i.test(line)) break;
    const entry = line.match(/^(\d+)\s*-\s*(.+)$/);
    if (entry) {
      currentId = entry[1];
      predicates.set(currentId, [entry[2]]);
    } else if (currentId) {
      const details = predicates.get(currentId);
      if (!details?.length) continue;
      if (/^(?:access|filter|storage)\s*\(/i.test(line)) {
        details.push(line);
      } else {
        details[details.length - 1] = `${details[details.length - 1]} ${line}`;
      }
    }
  }
  return predicates;
}

interface DamengOpInfo {
  operation: string;
  nodeType: string;
  cost?: string;
  rows?: string;
  actualRows?: string;
  width?: string;
  relation?: string;
  props?: string;
  memUsed?: string;
  diskUsed?: string;
}

/**
 * Parse a single DM operator line:
 *   #NSET2: [cost, rows, width]; props
 *   #HASH2 INNER JOIN: [cost, rows->actual, width]; KEY_NUM(1), MEM_USED(20352KB)
 *   #CSCN2: [cost, rows, width]; INDEX_NAME; btr_scan(1)
 */
function parseDamengPlanLine(line: string): DamengOpInfo | null {
  // Remove leading #
  const content = line.replace(/^#+/, "").trim();

  // Split at first colon
  const colonIdx = content.indexOf(":");
  if (colonIdx < 0) return null;

  const operator = content.substring(0, colonIdx).trim();
  const rest = content.substring(colonIdx + 1).trim();

  // Extract [cost, rows, width] or [cost, estRows->actualRows, width]
  let costPart = "";
  let afterBracket = rest;
  const bracketMatch = rest.match(/^\[([^\]]+)\]/);
  if (bracketMatch) {
    costPart = bracketMatch[1];
    afterBracket = rest.substring(bracketMatch[0].length).trim();
  }

  // Parse cost part
  let cost: string | undefined;
  let rows: string | undefined;
  let actualRows: string | undefined;
  let width: string | undefined;

  if (costPart) {
    const parts = costPart.split(",").map((s) => s.trim());
    if (parts.length >= 1) cost = parts[0];
    if (parts.length >= 2) {
      const rowPart = parts[1];
      const arrowIdx = rowPart.indexOf("->");
      if (arrowIdx >= 0) {
        rows = rowPart.substring(0, arrowIdx).trim();
        actualRows = rowPart.substring(arrowIdx + 2).trim();
      } else {
        rows = rowPart;
      }
    }
    if (parts.length >= 3) width = parts[2];
  }

  // Extract props after semicolons
  const semiParts = afterBracket
    .split(";")
    .map((s) => s.trim())
    .filter(Boolean);
  let relation: string | undefined;
  let memUsed: string | undefined;
  let diskUsed: string | undefined;
  const otherProps: string[] = [];

  for (const part of semiParts) {
    if (/^[A-Za-z_]\w*\(/.test(part) || part.includes("(")) {
      otherProps.push(part);
    } else if (part.toUpperCase().includes("MEM_USED")) {
      const m = part.match(/MEM_USED\((\d+)(KB|MB)\)/i);
      if (m) memUsed = `${m[1]}${m[2]}`;
      otherProps.push(part);
    } else if (part.toUpperCase().includes("DISK_USED")) {
      const m = part.match(/DISK_USED\((\d+)(KB|MB)\)/i);
      if (m) diskUsed = `${m[1]}${m[2]}`;
      otherProps.push(part);
    } else if (!relation && part.length > 0 && part.length < 100 && !part.includes(" ")) {
      relation = part; // likely an index or table name
    } else if (part.length > 0) {
      otherProps.push(part);
    }
  }

  return {
    operation: operator,
    nodeType: operator,
    cost,
    rows,
    actualRows,
    width,
    relation,
    props: otherProps.length > 0 ? otherProps.join("; ") : undefined,
    memUsed,
    diskUsed,
  };
}

export function flattenExplainPlanNodes(nodes: ExplainPlanNode[]): ExplainPlanNode[] {
  const rows: ExplainPlanNode[] = [];
  function visit(node: ExplainPlanNode) {
    rows.push(node);
    node.children.forEach((child) => visit(child));
  }
  nodes.forEach((node) => visit(node));
  return rows;
}

export function sqlServerExplainResult(results: QueryResult[]): { result?: QueryResult; error?: string } {
  const errorResult = results.find((result) => isQueryExecutionErrorResult(result) && result.rows.length > 0);
  if (errorResult) return { error: String(errorResult.rows[0]?.[0] ?? "") };

  const result = results.find((candidate) => firstSqlServerShowplanXml(candidate) !== undefined);
  return result ? { result } : { error: "SQL Server did not return ShowPlan XML" };
}

function parseSqlServerExplain(result: QueryResult): ParsedExplainPlan {
  const raw = firstSqlServerShowplanXml(result);
  if (!raw) throw new Error("SQL Server did not return ShowPlan XML");
  if (typeof DOMParser === "undefined") throw new Error("XML parser is unavailable");

  if (!hasBalancedXmlTags(raw)) throw new Error("Invalid SQL Server ShowPlan XML");
  const parserIssues: string[] = [];
  type XmlParserConstructor = new (options?: { onError?: (level: string, message: string) => void }) => DOMParser;
  const Parser = DOMParser as unknown as XmlParserConstructor;
  const recordParserIssue = (_level: string, message: string) => parserIssues.push(message);
  let document: Document;
  try {
    document = new Parser({
      onError: recordParserIssue,
    }).parseFromString(raw, "application/xml");
  } catch {
    // xmldom 0.9 throws ParseError on fatal issues even with onError set; the
    // recorded issues are non-fatal warnings at this point.
    throw new Error("Invalid SQL Server ShowPlan XML");
  }
  if (parserIssues.length > 0 || xmlElements(document, "parsererror").length > 0 || !document.documentElement) {
    throw new Error("Invalid SQL Server ShowPlan XML");
  }

  const rootName = document.documentElement.localName || document.documentElement.nodeName.split(":").pop();
  if (rootName !== "ShowPlanXML") throw new Error("Invalid SQL Server ShowPlan XML root element");

  const relOps = xmlElements(document, "RelOp");
  if (relOps.length === 0) throw new Error("SQL Server ShowPlan XML contains no RelOp nodes");
  const roots = relOps.filter((element) => !hasRelOpAncestor(element));
  if (roots.length === 0) throw new Error("SQL Server ShowPlan XML contains no root RelOp node");
  return {
    databaseType: "sqlserver",
    raw,
    nodes: roots.map(parseSqlServerRelOp),
  };
}

function hasBalancedXmlTags(xml: string): boolean {
  const stack: string[] = [];
  const tokens = xml.match(/<!--[\s\S]*?-->|<!\[CDATA\[[\s\S]*?\]\]>|<\?[^>]*\?>|<!DOCTYPE[^>]*>|<\/?[A-Za-z_][\w:.-]*(?:\s[^<>]*?)?\/?>/gi);
  if (!tokens || xml.replace(/<!--[\s\S]*?-->|<!\[CDATA\[[\s\S]*?\]\]>|<\?[^>]*\?>|<!DOCTYPE[^>]*>|<\/?[A-Za-z_][\w:.-]*(?:\s[^<>]*?)?\/?>/gi, "").includes("<")) return false;
  for (const token of tokens) {
    if (token.startsWith("<!--") || token.startsWith("<![CDATA[") || token.startsWith("<?") || /^<!DOCTYPE/i.test(token)) continue;
    const closing = /^<\//.test(token);
    const selfClosing = /\/\s*>$/.test(token);
    const name = token.match(/^<\/?([A-Za-z_][\w:.-]*)/)?.[1];
    if (!name) return false;
    if (closing) {
      if (stack.pop() !== name) return false;
    } else if (!selfClosing) stack.push(name);
  }
  return stack.length === 0;
}

function firstSqlServerShowplanXml(result: QueryResult): string | undefined {
  for (const row of result.rows) {
    for (const cell of row) {
      if (typeof cell === "string" && cell.includes("<ShowPlanXML")) return cell;
    }
  }
  return undefined;
}

function parseSqlServerRelOp(element: Element): ExplainPlanNode {
  const nodeType = element.getAttribute("PhysicalOp") || element.getAttribute("LogicalOp") || "Plan";
  const logicalOp = element.getAttribute("LogicalOp") || undefined;
  const object = planRegionElements(element, "Object")[0];
  const schema = sqlServerIdentifier(object?.getAttribute("Schema"));
  const table = sqlServerIdentifier(object?.getAttribute("Table"));
  const relation = table ? [schema, table].filter(Boolean).join(".") : undefined;
  const index = sqlServerIdentifier(object?.getAttribute("Index"));
  const expressions = [
    ...new Set(
      planRegionElements(element, "ScalarOperator")
        .map((operator) => operator.getAttribute("ScalarString")?.trim())
        .filter((value): value is string => !!value),
    ),
  ];
  const details = [
    logicalOp && logicalOp !== nodeType ? `Logical: ${logicalOp}` : "",
    element.getAttribute("EstimatedRowsRead") ? `Estimated Rows Read: ${element.getAttribute("EstimatedRowsRead")}` : "",
    element.getAttribute("EstimateIO") ? `Estimated I/O: ${element.getAttribute("EstimateIO")}` : "",
    element.getAttribute("EstimateCPU") ? `Estimated CPU: ${element.getAttribute("EstimateCPU")}` : "",
    ...expressions.slice(0, 3).map((expression) => `Expression: ${expression}`),
    ...sqlServerRuntimeDetails(sqlServerRuntimeCounters(element)),
  ].filter(Boolean);

  return {
    id: element.getAttribute("NodeId") || "0",
    title: relation ? `${nodeType} on ${relation}` : nodeType,
    nodeType,
    relation,
    index,
    cost: element.getAttribute("EstimatedTotalSubtreeCost") || undefined,
    rows: element.getAttribute("EstimateRows") || undefined,
    width: element.getAttribute("AvgRowSize") || undefined,
    details,
    children: planRegionElements(element, "RelOp").map(parseSqlServerRelOp),
  };
}

interface SqlServerRuntimeCounters {
  rows: number;
  executions: number;
  threads: number;
  /** Set only when the operator was genuinely invoked more than once per thread. */
  rowsPerExecution?: number;
  elapsedMs?: number;
  cpuMs?: number;
}

/**
 * Aggregates the `SET STATISTICS XML` runtime counters of one operator across its
 * threads: rows and executions are summed, elapsed time is wall clock per thread so
 * the slowest thread is the operator duration, and CPU time is additive work so it
 * is summed. Estimated plans carry no RunTimeInformation and yield undefined.
 */
function sqlServerRuntimeCounters(element: Element): SqlServerRuntimeCounters | undefined {
  const perThread = planRegionElements(element, "RunTimeCountersPerThread");
  if (perThread.length === 0) return undefined;

  const counters: SqlServerRuntimeCounters = { rows: 0, executions: 0, threads: perThread.length };
  let activeThreads = 0;
  for (const thread of perThread) {
    const executions = xmlNumberAttribute(thread, "ActualExecutions") ?? 0;
    counters.rows += xmlNumberAttribute(thread, "ActualRows") ?? 0;
    counters.executions += executions;
    if (executions > 0) activeThreads += 1;
    const elapsed = xmlNumberAttribute(thread, "ActualElapsedms");
    if (elapsed !== undefined) counters.elapsedMs = Math.max(counters.elapsedMs ?? 0, elapsed);
    const cpu = xmlNumberAttribute(thread, "ActualCPUms");
    if (cpu !== undefined) counters.cpuMs = (counters.cpuMs ?? 0) + cpu;
  }

  // ActualRows is cumulative over executions while EstimateRows stays per execution,
  // so repeated invocations (a nested loop inner side) need normalizing. A parallel
  // operator reports one execution per worker thread, which is still one logical
  // execution: dividing there would understate every parallel node instead.
  const executionsPerThread = counters.executions / (activeThreads || counters.threads);
  if (executionsPerThread > 1) counters.rowsPerExecution = counters.rows / counters.executions;
  return counters;
}

function sqlServerRuntimeDetails(counters?: SqlServerRuntimeCounters): string[] {
  if (!counters) return [];

  const details = [`Actual Rows: ${counters.rows}`];
  if (counters.threads > 1) details.push(`Actual Threads: ${counters.threads}`);
  if (counters.executions > 1) details.push(`Actual Executions: ${counters.executions}`);
  if (counters.rowsPerExecution !== undefined) details.push(`Actual Rows Per Execution: ${formatPlanMetric(counters.rowsPerExecution)}`);
  if (counters.elapsedMs !== undefined) details.push(`Actual Time: ${formatPlanMetric(counters.elapsedMs)} ms`);
  if (counters.cpuMs !== undefined) details.push(`Actual CPU: ${formatPlanMetric(counters.cpuMs)} ms`);
  return details;
}

function formatPlanMetric(value: number): string {
  return String(Number(value.toFixed(2)));
}

function xmlNumberAttribute(element: Element, attribute: string): number | undefined {
  const raw = element.getAttribute(attribute);
  if (raw === null || raw.trim() === "") return undefined;
  const value = Number(raw);
  return Number.isFinite(value) ? value : undefined;
}

function planRegionElements(root: Element, localName: string): Element[] {
  const matches: Element[] = [];
  const visit = (element: Element) => {
    for (const child of elementChildren(element)) {
      if (child.localName === "RelOp") {
        if (localName === "RelOp") matches.push(child);
        continue;
      }
      if (child.localName === localName) matches.push(child);
      visit(child);
    }
  };
  visit(root);
  return matches;
}

function xmlElements(root: Document | Element, localName: string): Element[] {
  return Array.from(root.getElementsByTagNameNS("*", localName));
}

function hasRelOpAncestor(element: Element): boolean {
  let parent = element.parentNode;
  while (parent) {
    if (parent.nodeType === 1 && (parent as Element).localName === "RelOp") return true;
    parent = parent.parentNode;
  }
  return false;
}

function elementChildren(element: Element): Element[] {
  return Array.from(element.childNodes).filter((node): node is Element => node.nodeType === 1);
}

function sqlServerIdentifier(value: string | null | undefined): string | undefined {
  if (!value) return undefined;
  return value.startsWith("[") && value.endsWith("]") ? value.slice(1, -1).replaceAll("]]", "]") : value;
}

function parseExplainCell(value: unknown): unknown {
  if (typeof value !== "string") return value;
  try {
    return JSON.parse(value);
  } catch {
    return value;
  }
}

function parseOceanbaseOracleExplain(result: QueryResult): ParsedExplainPlan {
  // OceanBase JDBC returns one line of the JSON document per Query Plan row.
  const text = result.rows.map((row) => String(row[0] ?? "")).join("\n");
  const raw = parseExplainCell(text);
  const root = objectValue(raw);
  return { databaseType: "oceanbase-oracle", raw, nodes: root ? [parseOceanbaseOracleNode(root, "0")] : [] };
}

const OCEANBASE_PLAN_KEYS = { id: "ID", operator: "OPERATOR", name: "NAME", rows: "EST.ROWS", time: "EST.TIME(us)", cost: "COST" } as const;
const OCEANBASE_PLAN_FIELDS = new Set<string>(Object.values(OCEANBASE_PLAN_KEYS));
const OCEANBASE_CHILD_KEY = /^CHILD_\d+$/;

function parseOceanbaseOracleNode(plan: Record<string, unknown>, fallbackId: string): ExplainPlanNode {
  const nodeType = stringValue(plan[OCEANBASE_PLAN_KEYS.operator])?.trim() || "Plan";
  const relation = stringValue(plan[OCEANBASE_PLAN_KEYS.name])?.trim() || undefined;
  const children = Object.entries(plan)
    .filter(([key, value]) => OCEANBASE_CHILD_KEY.test(key) && objectValue(value))
    .sort(([left], [right]) => Number(left.slice(6)) - Number(right.slice(6)))
    .map(([key, value]) => parseOceanbaseOracleNode(objectValue(value)!, `${fallbackId}.${key.slice(6)}`));
  const estimatedTimeUs = numberLike(plan[OCEANBASE_PLAN_KEYS.time]);
  const details = Object.entries(plan)
    .filter(([key, value]) => !OCEANBASE_PLAN_FIELDS.has(key) && !OCEANBASE_CHILD_KEY.test(key) && value !== null)
    .map(([key, value]) => `${key}: ${typeof value === "string" ? value : JSON.stringify(value)}`);
  return {
    id: numberLike(plan[OCEANBASE_PLAN_KEYS.id]) || fallbackId,
    title: relation && relation !== nodeType ? `${nodeType} on ${relation}` : nodeType,
    nodeType,
    relation,
    cost: numberLike(plan[OCEANBASE_PLAN_KEYS.cost]),
    rows: numberLike(plan[OCEANBASE_PLAN_KEYS.rows]),
    estimatedTimeUs,
    details,
    children,
  };
}

// ── DM (达梦) tabular explain parser ──────────────────────────────────

interface DamengExplainRow {
  id: string;
  operation: string;
  options: string;
  objectName: string;
  objectType: string;
  cost: string;
  cardinality: string;
  [key: string]: unknown;
}

/**
 * DM's EXPLAIN returns a tabular result set.
 * Columns: EXPLAIN_ID, ID, OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE,
 *          COST, CARDINALITY, CPU_COST, IO_COST, etc.
 * ID is hierarchical dot-notation (1, 2, 2.1, 2.2, 3).
 */
function parseDamengExplain(result: QueryResult): ParsedExplainPlan {
  const colIndex = buildColumnIndex(result.columns);
  const rows: DamengExplainRow[] = result.rows.map((row: unknown[]) => ({
    id: String(row[colIndex.id] ?? ""),
    operation: String(row[colIndex.operation] ?? ""),
    options: String(row[colIndex.options] ?? ""),
    objectName: String(row[colIndex.objectName] ?? ""),
    objectType: String(row[colIndex.objectType] ?? ""),
    cost: String(row[colIndex.cost] ?? ""),
    cardinality: String(row[colIndex.cardinality] ?? ""),
  }));

  const nodes = buildDamengTree(rows);
  return { databaseType: "dameng", raw: rows, nodes };
}

function buildColumnIndex(columns: string[]): Record<string, number> {
  const lower = columns.map((c) => c.toLowerCase());
  const idx = (name: string) => {
    const i = lower.indexOf(name);
    // fallback search for similar names
    if (i >= 0) return i;
    if (name === "id") return lower.findIndex((c) => c === "id" || c === "step" || c === "step_id");
    if (name === "operation") return lower.findIndex((c) => c.includes("operation"));
    if (name === "options") return lower.findIndex((c) => c === "options" || c === "option");
    if (name === "objectName") return lower.findIndex((c) => c.includes("object_name"));
    if (name === "objectType") return lower.findIndex((c) => c.includes("object_type"));
    if (name === "cost") return lower.findIndex((c) => c === "cost" || c === "total_cost");
    if (name === "cardinality") return lower.findIndex((c) => c === "cardinality" || c.includes("cardinality") || c === "rows");
    return -1;
  };
  return {
    id: idx("id"),
    operation: idx("operation"),
    options: idx("options"),
    objectName: idx("objectName"),
    objectType: idx("objectType"),
    cost: idx("cost"),
    cardinality: idx("cardinality"),
  };
}

/**
 * Build a tree from flat DM explain rows using dot-notation ID hierarchy.
 * Root nodes have IDs like "1", "2", "3".
 * Children have IDs like "1.1", "1.2", "2.1.1".
 */
function buildDamengTree(rows: DamengExplainRow[]): ExplainPlanNode[] {
  const nodes: ExplainPlanNode[] = [];

  // First pass: create all nodes
  const nodeMap = new Map<string, ExplainPlanNode>();
  for (const row of rows) {
    const nodeType = [row.operation, row.options].filter(Boolean).join(" ");
    const relation = row.objectName || undefined;
    const index = row.objectType === "INDEX" ? row.objectName : undefined;

    const details: string[] = [];
    if (row.objectType && row.objectType !== "TABLE" && row.objectType !== "INDEX") {
      details.push(`Object Type: ${row.objectType}`);
    }
    if (row.cardinality && row.cardinality !== "0") {
      details.push(`Cardinality: ${row.cardinality}`);
    }

    const node: ExplainPlanNode = {
      id: row.id,
      title: relation ? `${nodeType} on ${relation}` : nodeType || "Plan",
      nodeType: row.operation || "Plan",
      relation,
      index,
      cost: row.cost || undefined,
      rows: row.cardinality || undefined,
      details,
      children: [],
    };
    nodeMap.set(row.id, node);
  }

  // Second pass: build parent-child relationships based on dot notation
  for (const row of rows) {
    const node = nodeMap.get(row.id);
    if (!node) continue;

    const parts = row.id.split(".");
    if (parts.length === 1) {
      // Root-level node
      nodes.push(node);
    } else {
      // Child node: find parent
      const parentId = parts.slice(0, -1).join(".");
      const parent = nodeMap.get(parentId);
      if (parent) {
        parent.children.push(node);
      } else {
        // Orphan — treat as root
        nodes.push(node);
      }
    }
  }

  // Sort children by their last ID segment (numerically)
  function sortChildren(nodes: ExplainPlanNode[]) {
    for (const n of nodes) {
      sortChildren(n.children);
    }
    nodes.sort((a, b) => {
      const aLast = parseInt(a.id.split(".").pop() || "0", 10);
      const bLast = parseInt(b.id.split(".").pop() || "0", 10);
      return aLast - bLast;
    });
  }
  sortChildren(nodes);

  return nodes;
}

// ── PostgreSQL JSON explain parser ────────────────────────────────────

function parsePostgresExplain(raw: unknown): ExplainPlanNode[] {
  const plans = Array.isArray(raw) ? raw : [raw];
  return plans
    .map((item, index) => {
      const root = objectValue(item);
      if (!root) return null;
      const plan = objectValue(root.Plan) || root;
      const node = parsePostgresNode(plan, String(index));
      if (!node) return null;

      // EXPLAIN ANALYZE reports these next to "Plan", not inside it.
      const planningTime = numberLike(root["Planning Time"]);
      const executionTime = numberLike(root["Execution Time"]);
      if (planningTime !== undefined) node.details.push(`Planning Time: ${planningTime} ms`);
      if (executionTime !== undefined) node.details.push(`Execution Time: ${executionTime} ms`);
      return node;
    })
    .filter((node): node is ExplainPlanNode => !!node);
}

function parsePostgresNode(plan: Record<string, unknown> | null, id: string): ExplainPlanNode | null {
  if (!plan) return null;
  const nodeType = stringValue(plan["Node Type"]) || "Plan";
  const relation = stringValue(plan["Relation Name"]);
  const index = stringValue(plan["Index Name"]);
  const startupCost = numberLike(plan["Startup Cost"]);
  const totalCost = numberLike(plan["Total Cost"]);
  const rows = numberLike(plan["Plan Rows"]);
  const width = numberLike(plan["Plan Width"]);
  const filter = stringValue(plan.Filter);
  const joinType = stringValue(plan["Join Type"]);
  const sortKey = arrayValue(plan["Sort Key"])?.map(String).join(", ");
  // EXPLAIN ANALYZE only. Actual Rows is per loop, matching Plan Rows.
  const actualRows = numberLike(plan["Actual Rows"]);
  const actualLoops = numberLike(plan["Actual Loops"]);
  const actualStartupTime = numberLike(plan["Actual Startup Time"]);
  const actualTotalTime = numberLike(plan["Actual Total Time"]);

  const children =
    arrayValue(plan.Plans)
      ?.map((child, childIndex) => parsePostgresNode(objectValue(child), `${id}.${childIndex}`))
      .filter((node): node is ExplainPlanNode => !!node) ?? [];

  return {
    id,
    title: relation ? `${nodeType} on ${relation}` : nodeType,
    nodeType,
    relation,
    index,
    cost: [startupCost, totalCost].every(Boolean) ? `${startupCost}..${totalCost}` : totalCost,
    rows,
    width,
    details: [
      joinType ? `Join: ${joinType}` : "",
      filter ? `Filter: ${filter}` : "",
      sortKey ? `Sort: ${sortKey}` : "",
      actualRows !== undefined ? `Actual Rows: ${actualRows}` : "",
      actualLoops !== undefined && Number(actualLoops) > 1 ? `Actual Loops: ${actualLoops}` : "",
      actualStartupTime !== undefined && actualTotalTime !== undefined ? `Actual Time: ${actualStartupTime}..${actualTotalTime} ms` : "",
    ].filter(Boolean),
    children,
  };
}

// ── MySQL JSON explain parser ─────────────────────────────────────────

function parseMysqlExplain(raw: unknown): ExplainPlanNode[] {
  const root = objectValue(raw);
  if (!root) return [];
  const block = objectValue(root.query_block) || root;
  return [parseMysqlBlock(block, "0", "query_block")];
}

function parseMysqlBlock(block: Record<string, unknown>, id: string, nodeType: string): ExplainPlanNode {
  const costInfo = objectValue(block.cost_info);
  const children: ExplainPlanNode[] = [];

  const table = objectValue(block.table);
  if (table) children.push(parseMysqlTable(table, `${id}.0`));

  const nestedLoop = arrayValue(block.nested_loop);
  if (nestedLoop) {
    nestedLoop.forEach((item) => {
      const itemObject = objectValue(item);
      if (!itemObject) return;
      const nestedTable = objectValue(itemObject.table);
      if (nestedTable) {
        children.push(parseMysqlTable(nestedTable, `${id}.${children.length}`));
        return;
      }
      children.push(parseMysqlBlock(itemObject, `${id}.${children.length}`, "operation"));
    });
  }

  ["ordering_operation", "grouping_operation", "duplicates_removal", "union_result", "materialized_from_subquery"].forEach((key) => {
    const child = objectValue(block[key]);
    if (child) children.push(parseMysqlBlock(child, `${id}.${children.length}`, key));
  });

  return {
    id,
    title: nodeType,
    nodeType,
    cost: stringValue(costInfo?.query_cost),
    rows: numberLike(block.select_id),
    details: [stringValue(block.message)].filter(nonEmptyString),
    children,
  };
}

function parseMysqlTable(table: Record<string, unknown>, id: string): ExplainPlanNode {
  const relation = stringValue(table.table_name);
  const accessType = stringValue(table.access_type) || "table";
  const costInfo = objectValue(table.cost_info);
  const rows = numberLike(table.rows_examined_per_scan) || numberLike(table.rows_produced_per_join);
  const cost = stringValue(costInfo?.query_cost) || stringValue(costInfo?.read_cost) || stringValue(costInfo?.eval_cost);
  const details = [stringValue(table.attached_condition) ? `Condition: ${stringValue(table.attached_condition)}` : "", arrayValue(table.used_columns)?.length ? `Columns: ${arrayValue(table.used_columns)!.map(String).join(", ")}` : "", table.using_index === true ? "Using index" : ""].filter(Boolean);

  return {
    id,
    title: relation ? `${accessType} on ${relation}` : accessType,
    nodeType: accessType,
    relation,
    index: stringValue(table.key),
    cost,
    rows,
    details,
    children: [],
  };
}

// ── QuestDB explain parser ─────────────────────────────────────────
// ── openGauss explain parser ────────────────────────────────────────
// openGauss (PostgreSQL 9.2 lineage) returns the plan as indented text rows
// with no FORMAT JSON output, so the tree comes from the leading-space depth.
function parseOpenGaussExplain(result: QueryResult): ParsedExplainPlan {
  const rows = (result.rows ?? []).map((row: unknown[]) => String(row[0] ?? ""));
  const nodes: ExplainPlanNode[] = [];
  // Stack of (depth, node); each row nests under the shallowest deeper predecessor.
  const stack: { depth: number; node: ExplainPlanNode }[] = [];
  rows.forEach((text, index) => {
    if (!text.trim()) return;
    const leading = text.length - text.trimStart().length;
    const depth = Math.floor(leading / 2);
    const node: ExplainPlanNode = {
      id: `plan_${index}`,
      title: text.trim(),
      nodeType: "Plan",
      relation: "",
      index: String(index),
      cost: undefined,
      rows: undefined,
      details: [text.trim()],
      children: [],
    };
    // Compare stored depths, not stack length: a row may jump more than one
    // level past its parent (detail lines under a `->` node), which desyncs
    // length from depth and chains same-indent siblings as parent-child.
    while (stack.length > 0 && stack[stack.length - 1]!.depth >= depth) stack.pop();
    if (stack.length === 0) {
      nodes.push(node);
    } else {
      stack[stack.length - 1]!.node.children.push(node);
    }
    stack.push({ depth, node });
  });
  return { databaseType: "opengauss", raw: result, nodes };
}

function parseQuestdbExplain(result: QueryResult): ParsedExplainPlan {
  let nodes: ExplainPlanNode[] = result.rows.map((row: unknown[], index: number) => {
    const text = String(row[0] ?? "");
    const node: ExplainPlanNode = {
      id: `plan_${index}`,
      title: text,
      nodeType: "Plan",
      relation: "",
      index: String(index),
      cost: undefined,
      rows: undefined,
      details: [text],
      children: [],
    };
    return node;
  });
  return { databaseType: "questdb", raw: result, nodes };
}

// ── Doris explain parser ────────────────────────────────────────────
function parseDorisExplain(result: QueryResult): ParsedExplainPlan {
  const lines = result.rows.flatMap((row) => String(row[0] ?? "").split(/\r?\n/));
  const raw = lines.join("\n");
  const nodes = lines
    .filter((line) => line.trim().length > 0)
    .map((line, index): ExplainPlanNode => {
      const text = line.trim();
      return {
        id: `plan_${index}`,
        title: text,
        nodeType: "Plan",
        relation: "",
        index: String(index),
        cost: undefined,
        rows: undefined,
        details: [text],
        children: [],
      };
    });
  return { databaseType: "doris", raw, nodes };
}

// ── Helpers ───────────────────────────────────────────────────────────

function objectValue(value: unknown): Record<string, unknown> | null {
  return value !== null && typeof value === "object" && !Array.isArray(value) ? (value as Record<string, unknown>) : null;
}

function arrayValue(value: unknown): unknown[] | null {
  return Array.isArray(value) ? value : null;
}

function stringValue(value: unknown): string | undefined {
  if (typeof value === "string") return value;
  if (typeof value === "number" || typeof value === "boolean") return String(value);
  return undefined;
}

function nonEmptyString(value: string | undefined): value is string {
  return !!value;
}

function numberLike(value: unknown): string | undefined {
  if (typeof value === "number") return Number.isInteger(value) ? String(value) : String(value);
  if (typeof value === "string" && value.trim()) return value;
  return undefined;
}