Порівняти коміти

...
Автор SHA1 Повідомлення Дата
l0ng-ai 9f19223558 feat(database): compare the structure of two schemas
Compare Structure… (Database tree context menu of a data source or
schema, and Find Action) picks a source and a target schema, reads their
tables with columns, indexes and foreign keys, and shows the differences
as a tree: tables, columns, indexes and foreign keys only in the source,
only in the target, or different (type, nullability, default, primary
key, uniqueness, indexed columns), with identical items hidden unless
asked for. Checked differences become the migration script that makes
the target match the source, in the target's dialect: drops of indexes
first, then tables and columns (Modify Table's statements), then new
indexes and foreign keys. Changes a dialect cannot express are left as
comments. The script is copied or opened in a console of the target;
nothing is run.

Closes #2694
2026-11-16 10:52:04 +08:00
10 змінених файлів з 840 додано та 3 видалено

No files matched your search

+2
Переглянути файл
@@ -13,6 +13,7 @@ const ObjectDialogs = lazyComponent(() => import("./ObjectDialogs").then((m) =>
const ExportDialog = lazyComponent(() => import("./ExportDialog").then((m) => m.ExportDialog));
const HistoryDialog = lazyComponent(() => import("./DataDialogs").then((m) => m.HistoryDialog));
const ImportDialog = lazyComponent(() => import("./DataDialogs").then((m) => m.ImportDialog));
const SchemaCompareDialog = lazyComponent(() => import("./SchemaCompareDialog").then((m) => m.SchemaCompareDialog));
function PasswordDialog({ source, resolve }: { source: DataSource; resolve: (ok: boolean) => void }) {
const [password, setPassword] = useState("");
@@ -185,6 +186,7 @@ export function DatabaseDialogs() {
if (dialog.kind === "params") return <ParamsDialog names={dialog.names} resolve={dialog.resolve} />;
if (dialog.kind === "export")
return <ExportDialog data={dialog.data} source={dialog.source} initialFormat={dialog.format} />;
if (dialog.kind === "compareSchemas") return <SchemaCompareDialog initial={dialog.source} />;
if (dialog.kind === "history") return <HistoryDialog key={dialog.id} id={dialog.id} path={dialog.path} />;
if (dialog.kind === "import")
return (
+91
Переглянути файл
@@ -0,0 +1,91 @@
.body {
display: flex;
flex-direction: column;
gap: var(--space-2);
height: 560px;
padding: 14px;
}
.pickers {
display: flex;
align-items: center;
gap: var(--space-2);
flex-wrap: wrap;
}
.side {
display: flex;
align-items: center;
gap: var(--space-1);
}
.sideLabel {
color: var(--fg-secondary);
}
.arrow {
color: var(--fg-muted);
}
.toolbar {
display: flex;
align-items: center;
gap: var(--space-2);
}
.spacer {
flex: 1;
}
.count,
.direction {
color: var(--fg-muted);
}
.panes {
display: flex;
gap: var(--space-2);
flex: 1;
min-height: 0;
}
.tree {
display: flex;
flex-direction: column;
flex: 1.2;
min-width: 0;
border: 1px solid var(--border);
border-radius: var(--radius-control);
overflow: hidden;
}
.tree > * {
flex: 1;
min-height: 0;
}
.preview {
flex: 1;
min-width: 0;
}
.row {
display: inline-flex;
align-items: center;
gap: var(--space-1-5);
min-width: 0;
}
.name {
white-space: nowrap;
}
.detail {
overflow: hidden;
color: var(--fg-muted);
text-overflow: ellipsis;
white-space: nowrap;
}
.state {
width: 12px;
font-weight: 600;
text-align: center;
}
.added {
color: var(--vcs-added);
}
.removed {
color: var(--vcs-deleted);
}
.changed {
color: var(--vcs-modified);
}
.same {
color: var(--fg-muted);
}
+334
Переглянути файл
@@ -0,0 +1,334 @@
/**
* Compare Structure (DataGrip's schema comparison): pick a source and a target schema,
* see their differences — tables, columns, indexes, foreign keys — choose the ones to
* migrate and preview the SQL that makes the target match the source. Nothing runs: the
* script is copied or opened in a query console of the target.
*/
import { useEffect, useMemo, useRef, useState } from "react";
import clsx from "clsx";
import { tx } from "@/platform/i18n";
import { Button, Checkbox, Dialog, EmptyState, Icon, Select, Spinner, T, Tree, type SelectOption } from "@/ui";
import { notifications } from "@/workbench/contributions";
import { CodePreview } from "./CodePreview";
import {
diffSchemas,
differenceKeys,
migrationSql,
type DiffState,
type ItemDiff,
type TableDiff,
} from "./schemaCompare";
import {
closeDialog,
loadSchemas,
loadSources,
openConsoleWith,
schemaSnapshot,
sourceById,
targetLabel,
useDatabase,
} from "./store";
import styles from "./SchemaCompare.module.css";
interface Side {
id: string;
schema: string;
}
/** A row of the differences tree: a table, a group of its items, or an item. */
type Row =
| { kind: "table"; key: string; diff: TableDiff }
| { kind: "group"; key: string; label: string; items: ItemDiff<unknown>[]; table: TableDiff }
| { kind: "item"; key: string; diff: ItemDiff<unknown>; table: TableDiff; what: "column" | "index" | "foreignKey" };
const STATE_LABEL: Record<DiffState, string> = {
added: "Only in source",
removed: "Only in target",
changed: "Different",
same: "Identical",
};
function SidePicker({ label, side, onChange }: { label: string; side: Side; onChange: (side: Side) => void }) {
const sources = useDatabase((s) => s.sources);
const schemas = useDatabase((s) => (side.id ? s.schemas[side.id] : undefined));
useEffect(() => {
if (side.id && !useDatabase.getState().schemas[side.id]) void loadSchemas(side.id);
}, [side.id]);
const sourceOptions: SelectOption[] = sources.map((s) => ({
value: s.id ?? "",
label: s.name ?? "",
icon: "database",
}));
if (side.id && !sourceOptions.some((o) => o.value === side.id))
sourceOptions.push({ value: side.id, label: targetLabel(side.id), icon: "database" });
const names = schemas?.status === "ok" ? schemas.value : [];
const schemaOptions: SelectOption[] = names.map((n) => ({ value: n, label: n, icon: "db-schema" }));
if (side.schema && !names.includes(side.schema)) schemaOptions.unshift({ value: side.schema, label: side.schema });
return (
<div className={styles.side}>
<span className={styles.sideLabel}>
<T>{label}</T>
</span>
<Select
aria-label={tx`${tx(label)} data source`}
value={side.id || null}
placeholder="<data source>"
options={sourceOptions}
onChange={(id) => onChange({ id, schema: sourceById(id)?.driver === "sqlite" ? "main" : "" })}
/>
<Select
aria-label={tx`${tx(label)} schema`}
value={side.schema || null}
placeholder="<schema>"
options={schemaOptions}
searchable={schemaOptions.length > 10}
disabled={!side.id}
onChange={(schema) => onChange({ ...side, schema })}
/>
</div>
);
}
function StateIcon({ state }: { state: DiffState }) {
return (
<span
className={clsx(styles.state, styles[state])}
title={tx(STATE_LABEL[state])}
aria-label={tx(STATE_LABEL[state])}
>
{state === "added" ? "+" : state === "removed" ? "−" : state === "changed" ? "≠" : "="}
</span>
);
}
function itemDetail(row: Extract<Row, { kind: "item" }>): string {
const d = row.diff;
if (row.what === "column") {
const c = (x: unknown) => x as { dataType: string; nullable: boolean; default: string | null } | undefined;
const s = c(d.source);
const t = c(d.target);
const text = (x: ReturnType<typeof c>) =>
x ? `${x.dataType}${x.nullable ? "" : " not null"}${x.default ? ` default ${x.default}` : ""}` : "";
if (d.state === "changed") return `${text(t)} → ${text(s)}`;
return text(s ?? t);
}
if (row.what === "index") {
const i = (d.source ?? d.target) as { unique: boolean; columns: string[] };
return `${i.unique ? "unique " : ""}(${i.columns.join(", ")})`;
}
return "";
}
/** The direction of the migration, said in the footer. */
function migrates(source: Side, target: Side): string {
const from = `${targetLabel(source.id)}.${source.schema}`;
const to = `${targetLabel(target.id)}.${target.schema}`;
return tx`Migrates ${to} to match ${from}`;
}
function includeLabel(r: Row): string {
const name = r.kind === "group" ? tx(r.label) : r.diff.name;
return tx`Include ${name}`;
}
export function SchemaCompareDialog({ initial }: { initial: Side | null }) {
const [source, setSource] = useState<Side>(initial ?? { id: "", schema: "" });
const [target, setTarget] = useState<Side>({ id: "", schema: "" });
const [diffs, setDiffs] = useState<TableDiff[] | null>(null);
const [busy, setBusy] = useState(false);
const [error, setError] = useState<string | null>(null);
const [onlyDifferences, setOnlyDifferences] = useState(true);
const [picked, setPicked] = useState<Set<string>>(new Set());
const [expanded, setExpanded] = useState<Set<string>>(new Set());
const abort = useRef<AbortController | null>(null);
useEffect(() => {
if (!useDatabase.getState().loaded) void loadSources();
return () => abort.current?.abort();
}, []);
const ready = !!(source.id && source.schema && target.id && target.schema);
const same = source.id === target.id && source.schema === target.schema;
const targetDriver = sourceById(target.id)?.driver ?? "sqlite";
const compare = async () => {
abort.current?.abort();
const controller = (abort.current = new AbortController());
setBusy(true);
setError(null);
try {
const [s, t] = await Promise.all([
schemaSnapshot(source.id, source.schema, controller.signal),
schemaSnapshot(target.id, target.schema, controller.signal),
]);
if (controller.signal.aborted) return;
const result = diffSchemas(s, t);
setDiffs(result);
setPicked(differenceKeys(result));
// Changed tables open down to their differing items.
const open = result.filter((d) => d.state === "changed");
setExpanded(
new Set(open.flatMap((d) => [d.key, ...["Columns", "Indexes", "Foreign Keys"].map((g) => `${d.key}/${g}`)])),
);
} catch (e) {
if (!controller.signal.aborted) setError(e instanceof Error ? e.message : String(e));
} finally {
if (!controller.signal.aborted) setBusy(false);
}
};
const shown = useMemo(
() => (diffs ?? []).filter((d) => !onlyDifferences || d.state !== "same"),
[diffs, onlyDifferences],
);
const sql = useMemo(
() => (diffs ? migrationSql(targetDriver, target.schema, diffs, picked) : ""),
[diffs, picked, targetDriver, target.schema],
);
const differences = (diffs ?? []).filter((d) => d.state !== "same").length;
const children = (row: Row): Row[] | undefined => {
if (row.kind === "table") {
const d = row.diff;
const groups: Row[] = [];
const add = (label: string, items: ItemDiff<unknown>[]) => {
const visible = items.filter((i) => !onlyDifferences || i.state !== "same");
if (visible.length) groups.push({ kind: "group", key: `${d.key}/${label}`, label, items: visible, table: d });
};
add("Columns", d.columns);
add("Indexes", d.indexes);
add("Foreign Keys", d.foreignKeys);
return groups;
}
if (row.kind === "group") {
const what = row.label === "Columns" ? "column" : row.label === "Indexes" ? "index" : "foreignKey";
return row.items.map((diff) => ({ kind: "item", key: diff.key, diff, table: row.table, what }));
}
return undefined;
};
/** The keys a row's checkbox covers: a table covers its items too. */
const keysOf = (row: Row): string[] => {
if (row.kind === "item") return row.diff.state === "same" ? [] : [row.diff.key];
const d = row.kind === "table" ? row.diff : row.table;
const items = row.kind === "group" ? row.items : [...d.columns, ...d.indexes, ...d.foreignKeys];
const own = row.kind === "table" && d.state !== "same" ? [d.key] : [];
return [...own, ...items.filter((i) => i.state !== "same").map((i) => i.key)];
};
const toggle = (row: Row, on: boolean) =>
setPicked((p) => {
const next = new Set(p);
for (const k of keysOf(row)) {
if (on) next.add(k);
else next.delete(k);
}
return next;
});
const roots: Row[] = shown.map((diff) => ({ kind: "table", key: diff.key, diff }));
const copy = async () => {
await navigator.clipboard.writeText(sql);
notifications().notify({ level: "info", title: "Migration script copied", key: "db-compare" });
};
return (
<Dialog
open
onClose={closeDialog}
title="Compare Structure"
width={1040}
footerStart={<span className={styles.direction}>{ready && migrates(source, target)}</span>}
buttons={[
{ label: "Close", role: "cancel" },
{ label: "Copy", disabled: !sql, onClick: () => void copy() },
{
label: "Open in Console",
role: "default",
disabled: !sql,
onClick: () => {
closeDialog();
void openConsoleWith(target.id, sql);
},
},
]}
>
<div className={styles.body}>
<div className={styles.pickers}>
<SidePicker label="Source" side={source} onChange={setSource} />
<Icon name="arrow-right" size={14} className={styles.arrow} />
<SidePicker label="Target" side={target} onChange={setTarget} />
<Button size="small" variant="primary" disabled={!ready || same || busy} onClick={() => void compare()}>
<T>Compare</T>
</Button>
{busy && <Spinner size={14} />}
</div>
<div className={styles.toolbar}>
<Checkbox checked={onlyDifferences} onChange={setOnlyDifferences} label="Show only differences" />
<span className={styles.spacer} />
{diffs && <span className={styles.count}>{tx`${differences} of ${diffs.length} tables differ`}</span>}
</div>
<div className={styles.panes}>
<div className={styles.tree}>
{error ? (
<EmptyState title="Cannot compare">{error}</EmptyState>
) : !diffs ? (
<EmptyState
title={busy ? "Reading the schemas…" : "Pick a source and a target schema, then Compare"}
icon="diff-side-by-side"
/>
) : (
<Tree<Row>
roots={roots}
getKey={(r) => r.key}
getLabel={(r) => (r.kind === "table" ? r.diff.name : r.kind === "group" ? r.label : r.diff.name)}
getChildren={children}
isExpandable={(r) => r.kind !== "item"}
expanded={expanded}
onExpandedChange={(next) => setExpanded(next)}
aria-label={tx("Differences")}
empty={<EmptyState title="The schemas have the same structure" />}
renderNode={(r) => {
const keys = keysOf(r);
const on = keys.filter((k) => picked.has(k)).length;
const state = r.kind === "group" ? null : r.diff.state;
return (
<span className={styles.row}>
{keys.length > 0 && (
<Checkbox
checked={on === keys.length}
indeterminate={on > 0 && on < keys.length}
onChange={(v) => toggle(r, v)}
aria-label={includeLabel(r)}
/>
)}
{state && <StateIcon state={state} />}
<Icon
name={
r.kind === "table"
? "db-table"
: r.kind === "group"
? "db-folder"
: r.what === "column"
? "db-column"
: r.what === "index"
? "db-index"
: "db-foreign-key"
}
size={14}
/>
<span className={styles.name}>
{r.kind === "table" ? r.diff.name : r.kind === "group" ? tx(r.label) : r.diff.name}
</span>
{r.kind === "item" && <span className={styles.detail}>{itemDetail(r)}</span>}
</span>
);
}}
/>
)}
</div>
<CodePreview sql className={styles.preview} aria-label="Migration script" text={sql} />
</div>
</div>
</Dialog>
);
}
+90
Переглянути файл
@@ -0,0 +1,90 @@
import { describe, expect, it } from "vitest";
import type { DbColumnInfo } from "@/platform/ipc";
import { diffSchemas, differenceKeys, migrationSql, type TableSnapshot } from "./schemaCompare";
const col = (name: string, dataType: string, extra: Partial<DbColumnInfo> = {}): DbColumnInfo => ({
name,
dataType,
nullable: true,
default: null,
primaryKey: false,
...extra,
});
const users = (columns: DbColumnInfo[], indexes: TableSnapshot["indexes"] = []): TableSnapshot => ({
name: "users",
kind: "table",
columns,
indexes,
foreignKeys: [],
});
const SOURCE: TableSnapshot[] = [
users(
[
col("id", "int", { nullable: false, primaryKey: true }),
col("email", "varchar(255)", { nullable: false }),
col("age", "int", { default: "0" }),
],
[
{ name: "PRIMARY", unique: true, columns: ["id"] },
{ name: "users_email", unique: true, columns: ["email"] },
],
),
{
name: "orders",
kind: "table",
columns: [col("id", "int", { nullable: false, primaryKey: true }), col("user_id", "int")],
indexes: [
{ name: "PRIMARY", unique: true, columns: ["id"] },
{ name: "orders_user", unique: false, columns: ["user_id"] },
],
foreignKeys: [
{ schema: "app", table: "orders", columns: ["user_id"], refSchema: "app", refTable: "users", refColumns: ["id"] },
],
},
];
const TARGET: TableSnapshot[] = [
users(
[col("ID", "INT", { nullable: false, primaryKey: true }), col("email", "varchar(100)"), col("nickname", "text")],
[{ name: "PRIMARY", unique: true, columns: ["id"] }],
),
{ name: "legacy", kind: "table", columns: [col("x", "int")], indexes: [], foreignKeys: [] },
];
describe("Compare Structure", () => {
const diffs = diffSchemas(SOURCE, TARGET);
it("lists added, removed and changed tables with their column and index differences", () => {
expect(diffs.map((d) => `${d.name}:${d.state}`)).toEqual(["legacy:removed", "orders:added", "users:changed"]);
const u = diffs.find((d) => d.name === "users")!;
expect(u.columns.map((c) => `${c.name}:${c.state}:${c.changes.join("+")}`)).toEqual([
"id:same:",
"email:changed:type+nullable",
"age:added:",
"nickname:removed:",
]);
expect(u.indexes.map((i) => `${i.name}:${i.state}`)).toEqual(["PRIMARY:same", "users_email:added"]);
});
it("writes the migration of the selected differences for the target's dialect", () => {
const all = migrationSql("mysql", "app", diffs, differenceKeys(diffs));
expect(all).toBe(
[
"drop table app.legacy;",
"create table app.orders\n(\n id int not null,\n user_id int,\n primary key (id)\n);",
"alter table app.users change column email email varchar(255) not null;",
"alter table app.users add column age int default 0;",
"alter table app.users drop column nickname;",
"create index orders_user on app.orders (user_id);",
"alter table app.orders add foreign key (user_id) references app.users (id);",
"create unique index users_email on app.users (email);",
"",
].join("\n"),
);
// Only what is picked; identical items never produce SQL.
const some = migrationSql("postgres", "public", diffs, new Set(["c:users.age", "c:users.id", "t:legacy"]));
expect(some).toBe("drop table public.legacy;\nalter table public.users add column age int default 0;\n");
});
});
+234
Переглянути файл
@@ -0,0 +1,234 @@
/**
* Compare Structure (DataGrip's schema comparison): the differences between a source and a
* target schema — tables, columns, indexes and foreign keys — and the migration SQL that
* makes the chosen parts of the target match the source. Pure functions; nothing is run.
*/
import type { DbColumnInfo, DbDriver, DbForeignKey, DbIndexInfo, DbTableKind } from "@/platform/ipc";
import { alterTableSql, createTableSql, draftOf, ident, qualifiedName, type ColumnDraft } from "./objects";
export interface TableSnapshot {
name: string;
kind: DbTableKind;
columns: DbColumnInfo[];
indexes: DbIndexInfo[];
foreignKeys: DbForeignKey[];
}
/** `added`: only in the source (the migration creates it); `removed`: only in the target (it drops it). */
export type DiffState = "added" | "removed" | "changed" | "same";
export interface ItemDiff<T> {
/** Selection key, unique in the comparison. */
key: string;
name: string;
state: DiffState;
source?: T;
target?: T;
/** What differs, for a changed item: `type`, `nullable`, `default`, `primary key`, `unique`, `columns`, `references`. */
changes: string[];
}
export interface TableDiff extends ItemDiff<TableSnapshot> {
columns: ItemDiff<DbColumnInfo>[];
indexes: ItemDiff<DbIndexInfo>[];
foreignKeys: ItemDiff<DbForeignKey>[];
}
const norm = (s: string | null | undefined) => (s ?? "").trim().replace(/\s+/g, " ").toLowerCase();
/** Pairs items by name: exact first, then ignoring case. */
function pair<T>(
source: readonly T[],
target: readonly T[],
name: (t: T) => string,
): Array<[T | undefined, T | undefined]> {
const left = new Set(target);
const out: Array<[T | undefined, T | undefined]> = [];
for (const s of source) {
const n = name(s);
let t = [...left].find((x) => name(x) === n);
t ??= [...left].find((x) => name(x).toLowerCase() === n.toLowerCase());
if (t) left.delete(t);
out.push([s, t]);
}
for (const t of target) if (left.has(t)) out.push([undefined, t]);
return out;
}
function stateOf(source: unknown, target: unknown, changes: string[]): DiffState {
if (!target) return "added";
if (!source) return "removed";
return changes.length ? "changed" : "same";
}
function columnChanges(s: DbColumnInfo, t: DbColumnInfo): string[] {
const out: string[] = [];
if (norm(s.dataType) !== norm(t.dataType)) out.push("type");
if (s.nullable !== t.nullable) out.push("nullable");
if (norm(s.default) !== norm(t.default)) out.push("default");
if (s.primaryKey !== t.primaryKey) out.push("primary key");
return out;
}
const fkName = (k: DbForeignKey) => `(${k.columns.join(", ")}) → ${k.refTable}(${k.refColumns.join(", ")})`;
export function diffSchemas(source: readonly TableSnapshot[], target: readonly TableSnapshot[]): TableDiff[] {
return pair(source, target, (t) => t.name)
.map(([s, t]): TableDiff => {
const name = (s ?? t)!.name;
const columns = pair(s?.columns ?? [], t?.columns ?? [], (c) => c.name).map(([a, b]) => {
const changes = a && b ? columnChanges(a, b) : [];
return {
key: `c:${name}.${(a ?? b)!.name}`,
name: (a ?? b)!.name,
state: stateOf(a, b, changes),
source: a,
target: b,
changes,
};
});
const indexes = pair(s?.indexes ?? [], t?.indexes ?? [], (i) => i.name).map(([a, b]) => {
const changes: string[] = [];
if (a && b) {
if (a.unique !== b.unique) changes.push("unique");
if (norm(a.columns.join(",")) !== norm(b.columns.join(","))) changes.push("columns");
}
return {
key: `i:${name}.${(a ?? b)!.name}`,
name: (a ?? b)!.name,
state: stateOf(a, b, changes),
source: a,
target: b,
changes,
};
});
// Foreign keys have no comparable name across databases: they pair by what they link.
const foreignKeys = pair(s?.foreignKeys ?? [], t?.foreignKeys ?? [], (k) => norm(fkName(k))).map(([a, b]) => ({
key: `f:${name}.${fkName((a ?? b)!)}`,
name: fkName((a ?? b)!),
state: stateOf(a, b, []),
source: a,
target: b,
changes: [],
}));
const changes: string[] = [];
if (s && t && [...columns, ...indexes, ...foreignKeys].some((d) => d.state !== "same")) changes.push("content");
return {
key: `t:${name}`,
name,
state: stateOf(s, t, changes),
source: s,
target: t,
changes,
columns,
indexes,
foreignKeys,
};
})
.sort((a, b) => a.name.localeCompare(b.name));
}
/** Every key of a difference (not of identical items): the default selection. */
export function differenceKeys(diffs: readonly TableDiff[]): Set<string> {
const keys = new Set<string>();
for (const t of diffs) {
if (t.state === "same") continue;
keys.add(t.key);
for (const d of [...t.columns, ...t.indexes, ...t.foreignKeys]) if (d.state !== "same") keys.add(d.key);
}
return keys;
}
function createIndexSql(driver: DbDriver, schema: string, table: string, i: DbIndexInfo): string {
const cols = i.columns.map((c) => ident(driver, c)).join(", ");
return `create ${i.unique ? "unique " : ""}index ${ident(driver, i.name)} on ${qualifiedName(driver, schema, table)} (${cols});`;
}
function dropIndexSql(driver: DbDriver, schema: string, table: string, i: DbIndexInfo): string {
if (driver === "mysql" || driver === "sqlServer")
return `drop index ${ident(driver, i.name)} on ${qualifiedName(driver, schema, table)};`;
return `drop index ${qualifiedName(driver, schema, i.name)};`;
}
function addForeignKeySql(driver: DbDriver, schema: string, table: string, k: DbForeignKey): string {
const cols = (names: string[]) => names.map((c) => ident(driver, c)).join(", ");
return `alter table ${qualifiedName(driver, schema, table)} add foreign key (${cols(k.columns)}) references ${qualifiedName(driver, schema, k.refTable)} (${cols(k.refColumns)});`;
}
/**
* The statements making the selected differences of the target (`schema` on `driver`)
* match the source: drops of foreign keys and indexes first, then tables and columns,
* then new indexes and foreign keys. A dropped foreign key has no portable name: it is
* left as a comment to write by hand.
*/
export function migrationSql(
driver: DbDriver,
schema: string,
diffs: readonly TableDiff[],
selected: ReadonlySet<string>,
): string {
const first: string[] = [];
const tables: string[] = [];
const last: string[] = [];
for (const t of diffs) {
const pick = (d: ItemDiff<unknown>) => selected.has(d.key) && d.state !== "same";
if (t.state === "added") {
if (!selected.has(t.key) || !t.source) continue;
tables.push(
createTableSql(
driver,
schema,
t.name,
t.source.columns.map((c, i) => ({ ...draftOf(c, i), original: null })),
).trimEnd(),
);
// The primary key's own index comes with the table.
const key = t.source.columns.filter((c) => c.primaryKey).map((c) => norm(c.name));
const isKey = (i: DbIndexInfo) =>
i.unique && i.columns.length === key.length && i.columns.every((c) => key.includes(norm(c)));
for (const i of t.source.indexes) if (!isKey(i)) last.push(createIndexSql(driver, schema, t.name, i));
for (const k of t.source.foreignKeys) last.push(addForeignKeySql(driver, schema, t.name, k));
continue;
}
if (t.state === "removed") {
if (selected.has(t.key)) tables.push(`drop table ${qualifiedName(driver, schema, t.name)};`);
continue;
}
for (const k of t.foreignKeys)
if (pick(k) && k.state === "removed")
first.push(
`-- drop the foreign key ${k.name} of ${qualifiedName(driver, schema, t.name)} (constraint name differs per database)`,
);
for (const i of t.indexes)
if (pick(i) && (i.state === "removed" || i.state === "changed"))
first.push(dropIndexSql(driver, schema, t.name, i.target!));
const drafts: ColumnDraft[] = [];
t.columns.forEach((c, n) => {
if (!pick(c)) return;
if (c.state === "added") drafts.push({ ...draftOf(c.source!, n), original: null });
else if (c.state === "removed") drafts.push({ ...draftOf(c.target!, n), dropped: true });
else {
const s = c.source!;
drafts.push({ ...draftOf(c.target!, n), dataType: s.dataType, notNull: !s.nullable, default: s.default ?? "" });
}
});
if (drafts.length) tables.push(...alterTableSql(driver, schema, t.name, t.name, drafts));
// SQLite only adds and drops columns: a changed one is rebuilt by hand.
if (driver === "sqlite")
for (const c of t.columns)
if (pick(c) && c.state === "changed")
tables.push(
`-- SQLite cannot alter ${qualifiedName(driver, schema, t.name)}.${ident(driver, c.name)} (${c.changes.join(", ")}): rebuild the table`,
);
for (const c of t.columns)
if (pick(c) && c.changes.includes("primary key"))
last.push(`-- the primary key of ${qualifiedName(driver, schema, t.name)} changes at ${ident(driver, c.name)}`);
for (const i of t.indexes)
if (pick(i) && (i.state === "added" || i.state === "changed"))
last.push(createIndexSql(driver, schema, t.name, i.source!));
for (const k of t.foreignKeys)
if (pick(k) && k.state === "added") last.push(addForeignKeySql(driver, schema, t.name, k.source!));
}
const all = [...first, ...tables, ...last];
return all.length ? `${all.join("\n")}\n` : "";
}
+23 -1
Переглянути файл
@@ -56,6 +56,7 @@ import { formatted } from "./sql/format";
import { bindParams, findParams } from "./sql/params";
import { isReadOnlyQuery } from "./sql/readOnly";
import { pageFetch } from "./resultView";
import type { TableSnapshot } from "./schemaCompare";
import { resolutionScopeOf, resolutionScopesOf, type SqlResolutionScope } from "./resolutionScopes";
import { databaseRequest, invalidateDatabaseRequests, projectRequest } from "./requests";
import { tx } from "@/platform/i18n";
@@ -143,7 +144,9 @@ export type DialogState =
/** Browse Query History of target `id`; Paste goes to console `path`. */
| { kind: "history"; id: string; path: string | null }
/** Import Data from File into a table. */
| { kind: "import"; id: string; schema: string; table: string };
| { kind: "import"; id: string; schema: string; table: string }
/** Compare Structure: `source` starts as the schema the action was invoked on. */
| { kind: "compareSchemas"; source: { id: string; schema: string } | null };
/** The schemas and tables of a data source's databases, as far as introspection has read them. */
export interface SourceCatalog {
@@ -709,6 +712,25 @@ export async function loadTables(id: string, schema: string): Promise<void> {
}
}
/** The tables of `schema` on target `id` with their columns, indexes and foreign keys (Compare Structure). */
export async function schemaSnapshot(id: string, schema: string, signal?: AbortSignal): Promise<TableSnapshot[]> {
const tables = (await withConnection(id, () => call(commands.dbTables(id, schema)))).filter(
(t) => t.kind === "table",
);
const out: TableSnapshot[] = [];
let next = 0;
const worker = async () => {
while (next < tables.length) {
if (signal?.aborted) return;
const t = tables[next++]!;
const details = await call(commands.dbTableDetails(id, schema, t.name));
out.push({ name: t.name, kind: t.kind, ...details });
}
};
await Promise.all(Array.from({ length: Math.min(4, tables.length) }, worker));
return out;
}
export async function loadDetails(id: string, schema: string, table: string): Promise<void> {
const key = detailsKey(id, schema, table);
const current = metadataRequest(id, "details", key);
+16
Переглянути файл
@@ -389,6 +389,22 @@ export function registerDatabaseTreeActions(): () => void {
if (schema !== null) openDialog({ kind: "modifyTable", id, schema, table: null });
},
}),
tree({
id: "db.compareSchemas",
title: "Compare Structure…",
description: "Compare the tables of two schemas and write the migration SQL from one to the other",
icon: "diff-side-by-side",
keywords: ["schema compare", "diff", "migration", "compare schemas", "structure"],
run: ({ arg }) => {
const n = nodeFrom(arg);
const id = n && nodeTarget(n);
const schema = n && "schema" in n && typeof n.schema === "string" ? n.schema : null;
openDialog({
kind: "compareSchemas",
source: id ? { id, schema: schema ?? (driverOf(id) === "sqlite" ? "main" : "") } : null,
});
},
}),
tree({
id: "db.newColumn",
title: "Column",
+2
Переглянути файл
@@ -117,6 +117,7 @@ export function nodeMenu(n: DbNode | null): MenuItem[] {
navigation(),
sep,
sqlScripts(n, false),
act("db.compareSchemas", n),
sep,
act("db.drop", n, { label: "Remove Data Source…" }),
];
@@ -142,6 +143,7 @@ export function nodeMenu(n: DbNode | null): MenuItem[] {
navigation(),
sep,
sqlScripts(n, false),
...(n.kind === "schema" ? [act("db.compareSchemas", n)] : []),
...(n.kind === "schema" ? [sep, drop] : []),
];
case "table":
+26 -1
Переглянути файл
@@ -1977,5 +1977,30 @@
"Project data source": "Project data source",
"Project schema": "Project schema",
"Add Folder or File": "Add Folder or File",
"Paths are relative to the project root. The deepest folder or file that contains a file applies to it.": "Paths are relative to the project root. The deepest folder or file that contains a file applies to it."
"Paths are relative to the project root. The deepest folder or file that contains a file applies to it.": "Paths are relative to the project root. The deepest folder or file that contains a file applies to it.",
"{label} data source": "{label} data source",
"{label} schema": "{label} schema",
"Migrates {to} to match {from}": "Migrates {to} to match {from}",
"Migration script copied": "Migration script copied",
"Compare Structure": "Compare Structure",
"Compare Structure…": "Compare Structure…",
"Compare the tables of two schemas and write the migration SQL from one to the other": "Compare the tables of two schemas and write the migration SQL from one to the other",
"Open in Console": "Open in Console",
"Show only differences": "Show only differences",
"{differences} of {length} tables differ": "{differences} of {length} tables differ",
"Cannot compare": "Cannot compare",
"Reading the schemas…": "Reading the schemas…",
"Pick a source and a target schema, then Compare": "Pick a source and a target schema, then Compare",
"Differences": "Differences",
"The schemas have the same structure": "The schemas have the same structure",
"Migration script": "Migration script",
"Only in source": "Only in source",
"Only in target": "Only in target",
"Different": "Different",
"Identical": "Identical",
"Source": "Source",
"Target": "Target",
"Foreign Keys": "Foreign Keys",
"Indexes": "Indexes",
"Columns": "Columns"
}
+22 -1
Переглянути файл
@@ -8369,5 +8369,26 @@
"Project data source": "项目数据源",
"Project schema": "项目架构",
"Add Folder or File": "添加文件夹或文件",
"Paths are relative to the project root. The deepest folder or file that contains a file applies to it.": "路径相对于项目根目录。对某个文件生效的是包含它的最深一级文件夹或该文件本身。"
"Paths are relative to the project root. The deepest folder or file that contains a file applies to it.": "路径相对于项目根目录。对某个文件生效的是包含它的最深一级文件夹或该文件本身。",
"{label} data source": "{label}数据源",
"{label} schema": "{label}架构",
"Migrates {to} to match {from}": "迁移 {to},使其与 {from} 一致",
"Migration script copied": "已复制迁移脚本",
"Compare Structure": "比较结构",
"Compare Structure…": "比较结构…",
"Compare the tables of two schemas and write the migration SQL from one to the other": "比较两个架构的表,并生成从一个迁移到另一个的 SQL",
"Open in Console": "在控制台中打开",
"Show only differences": "仅显示差异",
"{differences} of {length} tables differ": "{length} 个表中有 {differences} 个不同",
"Cannot compare": "无法比较",
"Reading the schemas…": "正在读取架构…",
"Pick a source and a target schema, then Compare": "选择源架构和目标架构,然后点击“比较”",
"Differences": "差异",
"The schemas have the same structure": "两个架构的结构相同",
"Migration script": "迁移脚本",
"Only in source": "仅在源中",
"Only in target": "仅在目标中",
"Different": "不同",
"Foreign Keys": "外键",
"Indexes": "索引"
}