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。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
import * as api from "@/lib/backend/api";
import type { ConstraintInfo, OracleConstraintMetadata } from "@/types/database";
import type { BuildTableStructureChangeSqlOptions, OracleCheckConstraint, OracleConstraintChange, OracleConstraintDefinition, OracleUniqueConstraint, OracleForeignKeyState } from "@/lib/table/tableStructureEditorSql";
import { generateUniqueIndexName } from "@/lib/table/tableStructureEditorState";
export interface OracleConstraintTarget {
connectionId: string;
database: string;
schema: string;
tableName: string;
catalog?: string;
databaseType: "oracle" | "oceanbase-oracle";
exactIdentity?: boolean;
}
export interface OracleConstraintSnapshot {
user: string;
currentSchema: string;
database: string;
databaseName: string;
objectId: string;
columns: Array<{ name: string; dataType: string; length: string | null; precision: string | null; scale: string | null; nullable: boolean; charUsed?: string | null; charLength?: string | null; userGenerated?: string | null; hiddenColumn?: string | null }>;
indexColumnReadMode?: "enhanced" | "strict-base";
constraints: OracleConstraintDefinition[];
names: Array<{ name: string; table: string }>;
indexNames: string[];
indexes: Array<{
owner: string;
name: string;
tableOwner: string;
tableName: string;
indexType: string;
uniqueness: string;
status: string;
partitioned: boolean;
visibility: string;
secondary: string | null;
columns: string[];
directions: string[];
keyIsExpression: boolean[];
includedColumns: string[];
constraints: Array<{ owner: string; tableName: string; name: string }>;
}>;
references: Array<{ owner: string; table: string; name: string; status: string; validated: string }>;
}
export interface OracleConstraintPlan {
target: OracleConstraintTarget;
options: BuildTableStructureChangeSqlOptions;
change: OracleConstraintChange;
snapshot: OracleConstraintSnapshot;
snapshotFingerprint: string;
statements: string[];
notices: string[];
}
export interface OracleConstraintExecutionResult {
steps: Array<{ sql: string; state: "committed" | "rejected" | "unknown" | "notExecuted"; error?: string }>;
readbackError?: string;
recoverySql?: string;
}
export interface OracleIndexSnapshot {
user: string;
currentSchema: string;
database: string;
databaseName: string;
objectId: string;
columns: OracleConstraintSnapshot["columns"];
userColumns: OracleConstraintSnapshot["columns"];
columnReadMode: "enhanced" | "strict-base";
indexes: OracleConstraintSnapshot["indexes"];
indexNames: string[];
}
export class OracleConstraintMetadataError extends Error {
constructor(readonly reason: "incomplete" | "changed" | "invalidData" | "unreadableData") {
super(reason === "invalidData" ? "Existing rows violate the target constraint." : reason === "unreadableData" ? "Existing rows could not be verified before rebuilding the constraint." : "Constraint metadata is incomplete, unsupported, or changed. Refresh the table definition.");
}
}
function invalid(reason: OracleConstraintMetadataError["reason"] = "incomplete"): never {
throw new OracleConstraintMetadataError(reason);
}
const literal = (value: string) => `'${value.replace(/'/g, "''")}'`;
const ident = (value: string) => `"${value.replace(/"/g, '""')}"`;
async function query(target: OracleConstraintTarget, sql: string, stillCurrent: () => boolean = () => true): Promise<unknown[][]> {
if (!stillCurrent()) invalid("changed");
const result = await api.executeQuery(target.connectionId, target.database, sql, target.schema, undefined, { catalog: target.catalog, maxRows: 10001 });
if (!stillCurrent()) invalid("changed");
if (result.execution_error || result.truncated || result.has_more || result.rows.length >= 10001) invalid();
return result.rows;
}
function stateFromMetadata(metadata: OracleConstraintMetadata): OracleForeignKeyState {
const { status, validated, deferrable, deferred, rely } = metadata;
if (
!status ||
!["ENABLED", "DISABLED"].includes(status) ||
!validated ||
!["VALIDATED", "NOT VALIDATED"].includes(validated) ||
!deferrable ||
!["DEFERRABLE", "NOT DEFERRABLE"].includes(deferrable) ||
!deferred ||
!["DEFERRED", "IMMEDIATE"].includes(deferred) ||
(rely !== null && !["RELY", "NORELY"].includes(rely))
)
invalid();
const result = { enabled: status === "ENABLED", validated: validated === "VALIDATED", deferrable: deferrable === "DEFERRABLE", initiallyDeferred: deferred === "DEFERRED", rely: rely === "RELY" };
if (result.initiallyDeferred && !result.deferrable) invalid();
return result;
}
export function oracleConstraintDefinitionFromInfo(info: ConstraintInfo, target?: OracleConstraintTarget): OracleConstraintDefinition {
const metadata = info.oracle_metadata;
if (!metadata?.metadataComplete || metadata.owner === "" || !metadata.constraintType) invalid();
if (target && metadata.owner !== target.schema) invalid("changed");
const state = stateFromMetadata(metadata);
if (metadata.constraintType === "C" && info.constraint_type === "CHECK" && info.definition.trim()) {
const definition: OracleCheckConstraint = { name: info.name, expression: info.definition, state };
return { kind: "check", definition };
}
if (metadata.constraintType === "U" && info.constraint_type === "UNIQUE" && info.columns.length) {
const index = metadata.index;
if (!metadata.indexName && !metadata.indexOwner && !metadata.indexSharing && !index) {
if (state.enabled || (target && info.oracle_metadata?.metadataComplete !== true)) invalid();
return {
kind: "unique",
definition: {
name: info.name,
columns: [...info.columns],
state,
relyRaw: metadata.rely ?? "NULL",
index: {
owner: target?.schema ?? metadata.owner,
name: "",
tableOwner: target?.schema ?? metadata.owner,
tableName: target?.tableName ?? "",
sharing: "exclusive",
metadataComplete: true,
indexType: "NORMAL",
uniqueness: "NONUNIQUE",
status: "VALID",
partitioned: false,
visibility: "VISIBLE",
secondary: null,
columns: [...info.columns],
directions: info.columns.map(() => "ASC"),
keyIsExpression: info.columns.map(() => false),
includedColumns: [],
newNameAvailable: false,
},
},
};
}
if (!metadata.indexName || !metadata.indexOwner || !metadata.indexSharing || !index) invalid();
if (!index.metadataComplete || index.owner !== metadata.indexOwner || index.tableOwner !== metadata.owner || (target && (index.tableOwner !== target.schema || index.tableName !== target.tableName))) invalid();
const definition: OracleUniqueConstraint = {
name: info.name,
columns: [...info.columns],
state,
relyRaw: metadata.rely ?? "NULL",
index: {
owner: index.owner,
name: metadata.indexName,
tableOwner: index.tableOwner,
tableName: index.tableName,
sharing: metadata.indexSharing,
metadataComplete: index.metadataComplete,
indexType: index.indexType,
uniqueness: index.uniqueness,
status: index.status,
partitioned: index.partitioned,
visibility: index.visibility,
secondary: index.secondary,
columns: [...index.columns],
directions: [...index.directions],
keyIsExpression: [...index.keyIsExpression],
includedColumns: [...index.includedColumns],
newNameAvailable: false,
},
};
return { kind: "unique", definition };
}
invalid();
}
function sortedJson(value: unknown): string {
return JSON.stringify(value);
}
function expressionTokens(expression: string): string[] {
const tokens: string[] = [];
for (let i = 0; i < expression.length;) {
const ch = expression[i]!;
if (/\s/.test(ch)) {
i++;
continue;
}
if (expression.startsWith("--", i)) {
const newline = expression.indexOf("\n", i + 2);
i = newline < 0 ? expression.length : newline + 1;
continue;
}
if (expression.startsWith("/*", i)) {
const end = expression.indexOf("*/", i + 2);
if (end < 0) return [];
i = end + 2;
continue;
}
if (ch === "'" || ch === '"') {
const quote = ch;
let value = ch;
i++;
let closed = false;
while (i < expression.length) {
value += expression[i]!;
if (expression[i] === quote) {
if (expression[i + 1] === quote) {
value += expression[i + 1]!;
i += 2;
continue;
}
i++;
closed = true;
break;
}
i++;
}
if (!closed) return [];
tokens.push(value);
continue;
}
if (/[A-Za-z0-9_$#]/.test(ch)) {
let word = ch;
i++;
while (i < expression.length && /[A-Za-z0-9_$#]/.test(expression[i]!)) word += expression[i++]!;
tokens.push(word.toUpperCase());
continue;
}
const pair = expression.slice(i, i + 2);
if (["<=", ">=", "<>", "!=", "||", ":=", "**"].includes(pair)) {
tokens.push(pair);
i += 2;
continue;
}
tokens.push(ch);
i++;
}
return tokens;
}
/** Reads the table/session/constraint/index/reference state required before a CHECK/UNIQUE write. */
export async function readOracleIndexSnapshot(target: OracleConstraintTarget, stillCurrent: () => boolean = () => true, ownerNames: string[] = []): Promise<OracleIndexSnapshot> {
const snapshot = await readOracleConstraintSnapshot(target, undefined, true, stillCurrent, ownerNames);
const columns = [...snapshot.columns].sort((left, right) => left.name.localeCompare(right.name));
const userColumns = snapshot.indexColumnReadMode === "enhanced" ? columns.filter((column) => !(column.userGenerated === "NO" && column.hiddenColumn === "YES")) : columns;
return {
user: snapshot.user,
currentSchema: snapshot.currentSchema,
database: snapshot.database,
databaseName: snapshot.databaseName,
objectId: snapshot.objectId,
columns,
userColumns,
columnReadMode: snapshot.indexColumnReadMode ?? "strict-base",
indexes: snapshot.indexes.filter((index) => index.tableOwner === target.schema && index.tableName === target.tableName),
indexNames: snapshot.indexNames,
};
}
export async function readOracleConstraintSnapshot(target: OracleConstraintTarget, change?: OracleConstraintChange, indexesOnly = false, stillCurrent: () => boolean = () => true, requestedIndexNames: string[] = []): Promise<OracleConstraintSnapshot> {
if (!stillCurrent()) invalid("changed");
target = { ...target };
const oceanbase = target.databaseType === "oceanbase-oracle";
if (change) change = JSON.parse(JSON.stringify(change)) as OracleConstraintChange;
const baseColumnsSql = `SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE FROM SYS.ALL_TAB_COLS WHERE OWNER = ${literal(target.schema)} AND TABLE_NAME = ${literal(target.tableName)} ORDER BY COLUMN_NAME`;
const enhancedColumnsSql = `SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, CHAR_USED, CHAR_LENGTH, USER_GENERATED, HIDDEN_COLUMN FROM SYS.ALL_TAB_COLS WHERE OWNER = ${literal(target.schema)} AND TABLE_NAME = ${literal(target.tableName)}`;
let indexColumnReadMode: OracleConstraintSnapshot["indexColumnReadMode"];
const columnsQuery = indexesOnly
? (async () => {
try {
const rows = await query(target, enhancedColumnsSql, stillCurrent);
indexColumnReadMode = "enhanced";
return rows;
} catch {
const rows = await query(target, baseColumnsSql, stillCurrent);
indexColumnReadMode = "strict-base";
return rows;
}
})()
: query(target, `SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE FROM SYS.ALL_TAB_COLS WHERE OWNER = ${literal(target.schema)} AND TABLE_NAME = ${literal(target.tableName)} ORDER BY COLUMN_ID`, stillCurrent);
const wantedConstraintNames = [...new Set([change?.original?.definition.name, change?.target?.definition.name].filter((name): name is string => !!name))];
const wantedIndexNames = [
...new Set([...requestedIndexNames, ...(change?.original?.kind === "unique" ? [change.original.definition.index.name] : []), ...(change?.target?.kind === "unique" ? [change.target.definition.index.name, change.target.definition.index.newName || ""] : [])].filter(Boolean)),
];
const inList = (column: string, names: string[]) => (names.length ? ` AND ${column} IN (${names.map(literal).join(", ")})` : " AND 1 = 0");
const sharedUsageNamesSql = `SELECT c.INDEX_OWNER, c.INDEX_NAME, c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME FROM SYS.ALL_CONSTRAINTS c WHERE c.CONSTRAINT_TYPE IN ('P', 'U') AND c.INDEX_NAME IS NOT NULL AND EXISTS (SELECT 1 FROM SYS.ALL_INDEXES i WHERE i.OWNER = c.INDEX_OWNER AND i.INDEX_NAME = c.INDEX_NAME AND i.TABLE_OWNER = ${literal(target.schema)} AND i.TABLE_NAME = ${literal(target.tableName)}) ORDER BY c.INDEX_OWNER, c.INDEX_NAME, c.OWNER, c.TABLE_NAME, c.CONSTRAINT_NAME`;
const [identity, objects, tableShapes, externalTables, columns, infos, names, indexNames, indexDetails, indexUsage] = await Promise.all([
query(target, "SELECT USER, SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), SYS_CONTEXT('USERENV','DB_NAME') FROM DUAL", stillCurrent),
query(target, `SELECT OWNER, OBJECT_NAME, OBJECT_ID FROM SYS.ALL_OBJECTS WHERE OWNER = ${literal(target.schema)} AND OBJECT_NAME = ${literal(target.tableName)} AND OBJECT_TYPE = 'TABLE'`, stillCurrent),
query(target, `SELECT IOT_TYPE, TEMPORARY${oceanbase ? ", EXTERNAL" : ""} FROM SYS.ALL_TABLES WHERE OWNER = ${literal(target.schema)} AND TABLE_NAME = ${literal(target.tableName)}`, stillCurrent),
oceanbase ? Promise.resolve([]) : query(target, `SELECT OWNER, TABLE_NAME FROM SYS.ALL_EXTERNAL_TABLES WHERE OWNER = ${literal(target.schema)} AND TABLE_NAME = ${literal(target.tableName)}`, stillCurrent),
columnsQuery,
indexesOnly ? Promise.resolve([]) : api.listConstraints(target.connectionId, target.database, target.schema, target.tableName, target.catalog),
indexesOnly ? Promise.resolve([]) : query(target, `SELECT CONSTRAINT_NAME, TABLE_NAME FROM SYS.ALL_CONSTRAINTS WHERE OWNER = ${literal(target.schema)}${inList("CONSTRAINT_NAME", wantedConstraintNames)} ORDER BY CONSTRAINT_NAME`, stillCurrent),
query(target, `SELECT INDEX_NAME FROM SYS.ALL_INDEXES WHERE OWNER = ${literal(target.schema)}${inList("INDEX_NAME", wantedIndexNames)} ORDER BY INDEX_NAME`, stillCurrent),
query(
target,
`SELECT i.OWNER, i.INDEX_NAME, i.TABLE_OWNER, i.TABLE_NAME, i.INDEX_TYPE, i.UNIQUENESS, i.STATUS, i.PARTITIONED, i.VISIBILITY, i.SECONDARY, ic.COLUMN_NAME, ic.DESCEND, ie.COLUMN_EXPRESSION FROM SYS.ALL_INDEXES i JOIN SYS.ALL_IND_COLUMNS ic ON ic.INDEX_OWNER = i.OWNER AND ic.INDEX_NAME = i.INDEX_NAME LEFT JOIN SYS.ALL_IND_EXPRESSIONS ie ON ie.INDEX_OWNER = ic.INDEX_OWNER AND ie.INDEX_NAME = ic.INDEX_NAME AND ie.TABLE_OWNER = ic.TABLE_OWNER AND ie.TABLE_NAME = ic.TABLE_NAME AND ie.COLUMN_POSITION = ic.COLUMN_POSITION WHERE i.TABLE_OWNER = ${literal(target.schema)} AND i.TABLE_NAME = ${literal(target.tableName)} ORDER BY i.OWNER, i.INDEX_NAME, ic.COLUMN_POSITION`,
stillCurrent,
),
query(target, sharedUsageNamesSql, stillCurrent),
]);
if (!stillCurrent()) invalid("changed");
if (identity.length !== 1 || identity[0]?.length !== 3 || identity[0].some((value) => typeof value !== "string" || !value)) invalid();
if (objects.length !== 1 || objects[0]?.length !== 3 || objects[0][0] !== target.schema || objects[0][1] !== target.tableName || !/^[1-9]\d*$/.test(String(objects[0][2]))) invalid();
if (tableShapes.length !== 1 || tableShapes[0]?.length !== (oceanbase ? 3 : 2) || (tableShapes[0][0] !== null && tableShapes[0][0] !== "") || tableShapes[0][1] !== "N" || (oceanbase && tableShapes[0][2] !== "NO")) invalid();
if (externalTables.length !== 0) invalid();
if (typeof objects[0][2] === "number" && !Number.isSafeInteger(objects[0][2])) invalid();
const numeric = (value: unknown): string | null => {
if (value === null) return null;
if ((typeof value !== "string" && typeof value !== "number") || !/^-?\d+$/.test(String(value))) invalid();
return String(value);
};
if (!columns.length) invalid();
const seenColumns = new Set<string>();
const columnDefinitions = columns.map((row) => {
if (row.length < 6 || (!indexesOnly && row.length !== 6) || (indexColumnReadMode === "enhanced" && row.length > 10) || typeof row[0] !== "string" || !row[0] || seenColumns.has(row[0]) || typeof row[1] !== "string" || !row[1] || !["Y", "N"].includes(String(row[5]))) invalid();
seenColumns.add(row[0]);
const base = { name: row[0], dataType: row[1], length: numeric(row[2]), precision: numeric(row[3]), scale: numeric(row[4]), nullable: row[5] === "Y" };
if (indexColumnReadMode !== "enhanced") return base;
const optionalText = (index: number): string | null => {
const value = row[index];
if (value === undefined || value === null) return null;
if (typeof value !== "string") invalid();
return value;
};
return { ...base, charUsed: optionalText(6), charLength: numeric(row[7] ?? null), userGenerated: optionalText(8), hiddenColumn: optionalText(9) };
});
const editableInfos = indexesOnly ? [] : infos.filter((item) => item.constraint_type === "CHECK" || item.constraint_type === "UNIQUE");
const definitions = editableInfos.map((info) => oracleConstraintDefinitionFromInfo(info, target)).sort((a, b) => a.definition.name.localeCompare(b.definition.name));
if (!names.every((row) => row.length === 2 && typeof row[0] === "string" && !!row[0] && typeof row[1] === "string" && !!row[1])) invalid();
const nameRows = names.map((row) => ({ name: String(row[0]), table: String(row[1]) }));
if (!indexNames.every((row) => row.length === 1 && typeof row[0] === "string" && !!row[0])) invalid();
if (
indexDetails.some(
(row) => row.length !== 13 || row.slice(0, 9).some((value) => typeof value !== "string" || !value) || (row[9] !== null && typeof row[9] !== "string") || typeof row[10] !== "string" || !row[10] || !["ASC", "DESC"].includes(String(row[11])) || (row[12] !== null && typeof row[12] !== "string"),
)
)
invalid();
if (indexUsage.some((row) => row.length !== 5 || row.some((value) => typeof value !== "string" || !value))) invalid();
const references: OracleConstraintSnapshot["references"] = [];
if (!indexesOnly && change?.original?.kind === "unique") {
const original = change.original.definition;
const rows = await query(
target,
`SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, STATUS, VALIDATED FROM SYS.ALL_CONSTRAINTS WHERE R_OWNER = ${literal(target.schema)} AND R_CONSTRAINT_NAME = ${literal(original.name)} AND CONSTRAINT_TYPE = 'R' ORDER BY OWNER, TABLE_NAME, CONSTRAINT_NAME`,
stillCurrent,
);
for (const row of rows) {
if (row.length !== 5 || row.some((value) => typeof value !== "string" || !value)) invalid();
references.push({ owner: String(row[0]), table: String(row[1]), name: String(row[2]), status: String(row[3]), validated: String(row[4]) });
}
}
return {
user: String(identity[0]![0]),
currentSchema: String(identity[0]![1]),
database: target.database,
databaseName: String(identity[0]![2]),
objectId: String(objects[0]![2]),
columns: columnDefinitions,
...(indexesOnly ? { indexColumnReadMode } : {}),
constraints: definitions,
names: nameRows,
indexNames: indexNames.map((row) => String(row[0])),
indexes: [...new Set(indexDetails.map((row) => `${String(row[0])}\u0000${String(row[1])}`))].sort().map((identity) => {
const [owner, name] = identity.split("\u0000");
const rows = indexDetails.filter((row) => row[0] === owner && row[1] === name);
const first = rows[0]!;
return {
owner: String(first[0]),
name: String(name),
tableOwner: String(first[2]),
tableName: String(first[3]),
indexType: String(first[4]),
uniqueness: String(first[5]),
status: String(first[6]),
partitioned: String(first[7]) === "YES",
visibility: String(first[8]),
secondary: first[9] === null ? null : String(first[9]),
columns: rows.map((row) => (typeof row[12] === "string" && row[12].trim() ? String(row[12]) : String(row[10]))),
directions: rows.map((row) => String(row[11])),
keyIsExpression: rows.map((row) => typeof row[12] === "string" && row[12].trim() !== ""),
includedColumns: [],
constraints: indexUsage.filter((use) => use[0] === first[0] && use[1] === name).map((use) => ({ owner: String(use[2]), tableName: String(use[3]), name: String(use[4]) })),
};
}),
references,
};
}
function validateChange(snapshot: OracleConstraintSnapshot, target: OracleConstraintTarget, change: OracleConstraintChange) {
if (!change.original && !change.target) invalid();
if (change.original) {
const actual = snapshot.constraints.find((item) => item.kind === change.original!.kind && item.definition.name === change.original!.definition.name);
if (!actual || sortedJson(actual) !== sortedJson(change.original)) invalid("changed");
}
if (change.target) {
const name = change.target.definition.name;
if (!name.trim() || (change.target.kind === "check" && !change.target.definition.expression.trim())) invalid();
if (change.target.kind === "unique") {
const columns = change.target.definition.columns;
if (!columns.length || new Set(columns).size !== columns.length || columns.some((column) => !snapshot.columns.some((item) => item.name === column))) invalid();
if (target.databaseType === "oceanbase-oracle" && (change.target.definition.state.deferrable || change.target.definition.state.initiallyDeferred || change.target.definition.state.rely)) invalid();
}
if (snapshot.names.some((item) => item.name === name && change.original?.definition.name !== name)) invalid("changed");
}
}
function sameIdentifierSet(left: string[], right: string[]) {
return left.length === right.length && left.every((name) => right.includes(name));
}
async function constraintRecoveryCandidate(plan: OracleConstraintPlan, snapshot: OracleConstraintSnapshot, stillCurrent: () => boolean): Promise<string | undefined> {
const original = plan.change.original;
if (
!original ||
!stillCurrent() ||
snapshot.objectId !== plan.snapshot.objectId ||
snapshot.user !== plan.snapshot.user ||
snapshot.currentSchema !== plan.snapshot.currentSchema ||
snapshot.databaseName !== plan.snapshot.databaseName ||
JSON.stringify(snapshot.columns) !== JSON.stringify(plan.snapshot.columns)
)
return undefined;
if (snapshot.constraints.some((item) => item.definition.name === original.definition.name)) return undefined;
let recovery = JSON.parse(JSON.stringify(original)) as OracleConstraintDefinition;
let indexPolicy: OracleConstraintChange["indexPolicy"] = "none";
if (recovery.kind === "unique" && plan.target.databaseType === "oracle") {
const oldIndex = recovery.definition.index;
const actualIndex = snapshot.indexes.find((index) => index.owner === oldIndex.owner && index.name === oldIndex.name);
const completeIndexStillMatches =
!!oldIndex.name &&
!!actualIndex &&
actualIndex.tableOwner === plan.target.schema &&
actualIndex.tableName === plan.target.tableName &&
actualIndex.indexType === oldIndex.indexType &&
actualIndex.uniqueness === oldIndex.uniqueness &&
actualIndex.status === oldIndex.status &&
actualIndex.partitioned === oldIndex.partitioned &&
actualIndex.visibility === oldIndex.visibility &&
actualIndex.secondary === oldIndex.secondary &&
(recovery.definition.state.deferrable ? actualIndex.uniqueness === "NONUNIQUE" : ["UNIQUE", "NONUNIQUE"].includes(actualIndex.uniqueness)) &&
sameIdentifierSet(actualIndex.columns, oldIndex.columns) &&
actualIndex.directions.join("\0") === oldIndex.directions.join("\0") &&
actualIndex.keyIsExpression.join("\0") === oldIndex.keyIsExpression.join("\0") &&
actualIndex.includedColumns.length === oldIndex.includedColumns.length &&
actualIndex.constraints.length === 0;
if (completeIndexStillMatches) {
recovery = { ...recovery, definition: { ...recovery.definition, index: { ...oldIndex } } };
indexPolicy = "keep";
recovery = { ...recovery, definition: { ...recovery.definition, index: { ...recovery.definition.index, sharing: "exclusive" } } };
} else {
const candidate = await availableUniqueIndexName(plan.target, plan.target.tableName, recovery.definition.columns, [...snapshot.indexNames, ...snapshot.names.map((item) => item.name)], stillCurrent);
if (!candidate) return undefined;
recovery = { ...recovery, definition: { ...recovery.definition, index: { ...oldIndex, name: "", newName: candidate, newNameAvailable: true } } };
}
if (!recovery.definition.state.enabled) recovery = { ...recovery, definition: { ...recovery.definition, index: { ...recovery.definition.index, name: "", newName: undefined, newNameAvailable: false } } };
} else if (recovery.kind === "unique") {
recovery = { ...recovery, definition: { ...recovery.definition, index: { ...recovery.definition.index, name: "", metadataComplete: false } } };
}
const recoveryOptions = { ...plan.options, oracleConstraintChanges: [{ original: null, target: recovery, indexPolicy }] };
const built = await api.buildTableStructureChangeSql(recoveryOptions);
if (!stillCurrent() || built.warnings.length || built.statements.length !== 1) return undefined;
return built.statements[0];
}
async function availableUniqueIndexName(target: OracleConstraintTarget, tableName: string, columns: string[], occupied: string[], stillCurrent: () => boolean): Promise<string | undefined> {
const taken = [...occupied];
for (let attempt = 0; attempt < 64; attempt++) {
const candidate = generateUniqueIndexName(tableName, columns, taken, 30) || generateUniqueIndexName("DBX_UQ", [], taken, 30);
if (!candidate) return undefined;
const [indexes, constraints] = await Promise.all([
query(target, `SELECT INDEX_NAME FROM SYS.ALL_INDEXES WHERE OWNER = ${literal(target.schema)} AND INDEX_NAME = ${literal(candidate)}`, stillCurrent),
query(target, `SELECT CONSTRAINT_NAME FROM SYS.ALL_CONSTRAINTS WHERE OWNER = ${literal(target.schema)} AND CONSTRAINT_NAME = ${literal(candidate)}`, stillCurrent),
]);
if (!indexes.length && !constraints.length) return candidate;
taken.push(candidate);
}
return undefined;
}
async function prepareUniqueTargetIndex(snapshot: OracleConstraintSnapshot, target: OracleConstraintTarget, change: OracleConstraintChange, stillCurrent: () => boolean): Promise<OracleConstraintChange> {
if (change.target?.kind !== "unique") return change;
const next = JSON.parse(JSON.stringify(change)) as OracleConstraintChange;
const targetDefinition = next.target!.definition as OracleUniqueConstraint;
const original = next.original?.kind === "unique" ? next.original.definition : undefined;
const oldIndex = original?.index;
if (target.databaseType === "oceanbase-oracle") {
targetDefinition.index =
oldIndex && original
? {
...oldIndex,
owner: target.schema,
name: targetDefinition.name,
tableOwner: target.schema,
tableName: target.tableName,
columns: [...targetDefinition.columns],
directions: targetDefinition.columns.map(() => "ASC"),
keyIsExpression: targetDefinition.columns.map(() => false),
includedColumns: [],
}
: {
owner: target.schema,
name: "",
tableOwner: target.schema,
tableName: target.tableName,
sharing: "exclusive",
metadataComplete: false,
indexType: "NORMAL",
uniqueness: "UNIQUE",
status: "VALID",
partitioned: false,
visibility: "VISIBLE",
secondary: null,
columns: [...targetDefinition.columns],
directions: targetDefinition.columns.map(() => "ASC"),
keyIsExpression: targetDefinition.columns.map(() => false),
includedColumns: [],
newNameAvailable: false,
};
return next;
}
if (!targetDefinition.state.enabled) {
targetDefinition.index = {
owner: target.schema,
name: "",
tableOwner: target.schema,
tableName: target.tableName,
sharing: "exclusive",
metadataComplete: true,
indexType: "NORMAL",
uniqueness: "NONUNIQUE",
status: "VALID",
partitioned: false,
visibility: "VISIBLE",
secondary: "N",
columns: [...targetDefinition.columns],
directions: targetDefinition.columns.map(() => "ASC"),
keyIsExpression: targetDefinition.columns.map(() => false),
includedColumns: [],
newNameAvailable: false,
};
return next;
}
if (
oldIndex &&
!!oldIndex.name &&
oldIndex.metadataComplete &&
oldIndex.sharing === "exclusive" &&
sameIdentifierSet(oldIndex.columns, targetDefinition.columns) &&
change.indexPolicy !== "drop" &&
(targetDefinition.state.deferrable ? oldIndex.uniqueness === "NONUNIQUE" : ["UNIQUE", "NONUNIQUE"].includes(oldIndex.uniqueness))
) {
targetDefinition.index = { ...oldIndex, columns: [...oldIndex.columns], directions: [...oldIndex.directions], keyIsExpression: [...oldIndex.keyIsExpression], includedColumns: [...oldIndex.includedColumns], newNameAvailable: false };
return next;
}
let candidate: string | undefined;
if (targetDefinition.index.newName && targetDefinition.index.newNameAvailable) {
const frozen = targetDefinition.index.newName;
const [indexes, constraints] = await Promise.all([
query(target, `SELECT INDEX_NAME FROM SYS.ALL_INDEXES WHERE OWNER = ${literal(target.schema)} AND INDEX_NAME = ${literal(frozen)}`, stillCurrent),
query(target, `SELECT CONSTRAINT_NAME FROM SYS.ALL_CONSTRAINTS WHERE OWNER = ${literal(target.schema)} AND CONSTRAINT_NAME = ${literal(frozen)}`, stillCurrent),
]);
if (indexes.length || constraints.length) invalid("changed");
candidate = frozen;
} else {
candidate = await availableUniqueIndexName(target, target.tableName, targetDefinition.columns, [...snapshot.indexNames, ...snapshot.names.map((item) => item.name)], stillCurrent);
}
if (!candidate) invalid("changed");
targetDefinition.index = {
owner: target.schema,
name: "",
tableOwner: target.schema,
tableName: target.tableName,
sharing: "exclusive",
metadataComplete: false,
indexType: "NORMAL",
uniqueness: targetDefinition.state.deferrable || !targetDefinition.state.validated ? "NONUNIQUE" : "UNIQUE",
status: "VALID",
partitioned: false,
visibility: "VISIBLE",
secondary: "N",
columns: [...targetDefinition.columns],
directions: targetDefinition.columns.map(() => "ASC"),
keyIsExpression: targetDefinition.columns.map(() => false),
includedColumns: [],
newNameAvailable: true,
newName: candidate,
};
return next;
}
function oceanbaseCheckTokens(expression: string): string[] {
let tokens = expressionTokens(expression);
let depth = 0;
for (const token of tokens) {
if (token === "(") depth++;
if (token === ")" && --depth < 0) return [];
}
if (depth !== 0) return [];
// OceanBase wraps CHECK dictionary expressions; preserve all internal grouping.
while (tokens[0] === "(" && tokens[tokens.length - 1] === ")") {
depth = 0;
const end = tokens.findIndex((token) => {
if (token === "(") depth++;
if (token === ")") depth--;
return depth === 0;
});
if (end !== tokens.length - 1) break;
tokens = tokens.slice(1, -1);
}
return tokens;
}
function readbackMatches(target: OracleConstraintTarget, actual: OracleConstraintDefinition | undefined, expected: OracleConstraintDefinition): boolean {
if (!actual || actual.kind !== expected.kind || actual.definition.name !== expected.definition.name || sortedJson(actual.definition.state) !== sortedJson(expected.definition.state)) return false;
if (expected.kind === "check" && actual.kind === "check") {
if (target.databaseType === "oceanbase-oracle") {
const actualTokens = oceanbaseCheckTokens(actual.definition.expression);
const expectedTokens = oceanbaseCheckTokens(expected.definition.expression);
return actualTokens.length > 0 && expectedTokens.length > 0 && sortedJson(actualTokens) === sortedJson(expectedTokens);
}
return sortedJson(expressionTokens(actual.definition.expression)) === sortedJson(expressionTokens(expected.definition.expression));
}
if (expected.kind !== "unique" || actual.kind !== "unique") return false;
if (sortedJson(actual.definition.columns) !== sortedJson(expected.definition.columns)) return false;
const index = actual.definition.index;
const expectedUniqueness = expected.definition.index.uniqueness;
if (!expected.definition.state.enabled && !expected.definition.index.name) return true;
if (
!index.metadataComplete ||
index.owner !== target.schema ||
index.tableOwner !== target.schema ||
index.tableName !== target.tableName ||
index.indexType !== "NORMAL" ||
index.uniqueness !== expectedUniqueness ||
index.status !== "VALID" ||
index.partitioned ||
index.visibility !== "VISIBLE" ||
index.columns.length !== expected.definition.columns.length ||
!sameIdentifierSet(index.columns, expected.definition.columns) ||
index.directions.length !== index.columns.length ||
index.directions.some((direction) => direction !== "ASC") ||
index.keyIsExpression.some(Boolean) ||
index.includedColumns.length
)
return false;
if (target.databaseType === "oceanbase-oracle") return !!index.name && index.secondary === null;
const expectedIndexName = expected.definition.index.newName || expected.definition.index.name;
return !!expectedIndexName && index.name === expectedIndexName;
}
async function checkCheckRows(target: OracleConstraintTarget, expression: string, stillCurrent: () => boolean) {
let rows: unknown[][];
try {
rows = await query(target, `SELECT 1 FROM ${ident(target.schema)}.${ident(target.tableName)} WHERE NOT (\n${expression}\n) AND ROWNUM <= 1`, stillCurrent);
} catch {
invalid("unreadableData");
}
if (rows!.some((row) => row.length !== 1 || (row[0] !== 1 && row[0] !== "1"))) invalid("unreadableData");
if (rows!.length) invalid("invalidData");
}
async function checkUniqueRows(target: OracleConstraintTarget, columns: string[], stillCurrent: () => boolean) {
const names = columns.map(ident);
const nonNull = names.map((name) => `${name} IS NOT NULL`).join(" OR ");
let rows: unknown[][];
try {
rows = await query(target, `SELECT 1 FROM (SELECT 1 FROM ${ident(target.schema)}.${ident(target.tableName)} WHERE ${nonNull} GROUP BY ${names.join(", ")} HAVING COUNT(*) > 1) WHERE ROWNUM <= 1`, stillCurrent);
} catch {
invalid("unreadableData");
}
if (rows!.some((row) => row.length !== 1 || (row[0] !== 1 && row[0] !== "1"))) invalid("unreadableData");
if (rows!.length) invalid("invalidData");
}
/** Builds one checked constraint change and its non-destructive data preflight. */
export async function prepareOracleConstraintPlan(target: OracleConstraintTarget, options: BuildTableStructureChangeSqlOptions, change: OracleConstraintChange, stillCurrent: () => boolean = () => true): Promise<OracleConstraintPlan> {
target = { ...target };
change = JSON.parse(JSON.stringify(change)) as OracleConstraintChange;
options = { ...JSON.parse(JSON.stringify(options)), databaseType: target.databaseType, schema: target.schema, tableName: target.tableName, foreignKeys: [] };
const snapshot = await readOracleConstraintSnapshot(target, change, false, stillCurrent);
if (!stillCurrent()) invalid("changed");
validateChange(snapshot, target, change);
change = await prepareUniqueTargetIndex(snapshot, target, change, stillCurrent);
options.oracleConstraintChanges = [change];
const generated = await api.buildTableStructureChangeSql(options);
if (generated.warnings.length || !generated.statements.length) throw new Error(generated.warnings.join("\n") || "The constraint plan is empty.");
const hasDrop = generated.statements.some((statement) => /\bDROP\s+CONSTRAINT\b/i.test(statement));
const checkTarget = change.target?.kind === "check" ? change.target.definition : undefined;
const checkSource = change.original?.kind === "check" ? change.original.definition : undefined;
if (checkTarget && checkTarget.state.validated && checkSource && (checkSource.expression !== checkTarget.expression || hasDrop)) await checkCheckRows(target, checkTarget.expression, stillCurrent);
const uniqueTarget = change.target?.kind === "unique" ? change.target.definition : undefined;
const uniqueSource = change.original?.kind === "unique" ? change.original.definition : undefined;
if (uniqueSource && hasDrop && snapshot.references.length) invalid("changed");
if (uniqueTarget && uniqueSource && uniqueTarget.state.validated && hasDrop) {
await checkUniqueRows(target, uniqueTarget.columns, stillCurrent);
}
if (!stillCurrent()) invalid("changed");
const dropsConstraint = generated.statements.some((statement) => /\bDROP\s+CONSTRAINT\b/i.test(statement));
const notices = target.databaseType === "oceanbase-oracle" && change.original?.kind === "unique" && dropsConstraint ? ["oracleConstraint.pairedIndexImpact"] : [];
return { target, options, change, snapshot, snapshotFingerprint: JSON.stringify(snapshot), statements: [...generated.statements], notices };
}
/** Rechecks the fixed table identity before each DDL, stops on first failure, and never retries. */
export async function executeOracleConstraintPlan(plan: OracleConstraintPlan, timeoutSecs: number, guards: (() => boolean) | { canStart?: () => boolean; connectionCurrent?: () => boolean }): Promise<OracleConstraintExecutionResult | undefined> {
const canStart = typeof guards === "function" ? guards : (guards.canStart ?? (() => true));
const connectionCurrent = typeof guards === "function" ? guards : (guards.connectionCurrent ?? (() => true));
if (!canStart() || !connectionCurrent()) return undefined;
const fresh = await prepareOracleConstraintPlan(plan.target, plan.options, plan.change, connectionCurrent);
if (!connectionCurrent()) return undefined;
if (!canStart()) return undefined;
if (fresh.snapshotFingerprint !== plan.snapshotFingerprint || JSON.stringify(fresh.statements) !== JSON.stringify(plan.statements)) invalid("changed");
const steps: OracleConstraintExecutionResult["steps"] = plan.statements.map((sql) => ({ sql, state: "notExecuted" }));
for (const step of steps) {
if (!canStart() || !connectionCurrent()) break;
try {
const result = await api.executeQuery(plan.target.connectionId, plan.target.database, step.sql.replace(/;\s*$/, ""), plan.target.schema, undefined, { catalog: plan.target.catalog, timeoutSecs });
if (!connectionCurrent()) {
step.state = "unknown";
step.error = "Connection or target changed during execution; outcome is unknown.";
return { steps, readbackError: "Connection or target changed during constraint DDL; readback is unknown." };
}
if (result.execution_error) {
step.state = "rejected";
step.error = result.error?.detail || "Constraint statement rejected";
try {
const actual = await readOracleConstraintSnapshot(plan.target, plan.change, false, connectionCurrent);
if (!connectionCurrent()) return { steps, readbackError: "Connection or target changed during failure readback; actual state is unknown." };
if (actual.objectId !== plan.snapshot.objectId) return { steps, readbackError: "Target table was replaced after a rejected constraint statement." };
if (plan.change.target && actual.constraints.some((item) => readbackMatches(plan.target, item, plan.change.target!))) {
step.error += " Actual target definition is present after readback.";
} else if (plan.change.original && actual.constraints.some((item) => sortedJson(item) === sortedJson(plan.change.original))) {
step.error += " Original definition remains after readback.";
} else {
step.error += " Readback shows a partial or unexpected constraint state; inspect before recovery.";
}
const recoverySql = await constraintRecoveryCandidate(plan, actual, () => canStart() && connectionCurrent());
if (recoverySql) step.error += ` Manual recovery candidate: ${recoverySql}`;
} catch (readbackError) {
return { steps, readbackError: readbackError instanceof Error ? readbackError.message : String(readbackError) };
}
break;
}
step.state = "committed";
if (!canStart()) break;
} catch (error) {
step.state = "unknown";
step.error = error instanceof Error ? error.message : String(error);
if (!connectionCurrent()) return { steps, readbackError: "Connection or target changed during unknown constraint outcome; readback is unknown." };
try {
const actual = await readOracleConstraintSnapshot(plan.target, plan.change, false, connectionCurrent);
if (!connectionCurrent()) return { steps, readbackError: "Connection or target changed during unknown-outcome readback; actual state is unknown." };
if (actual.objectId !== plan.snapshot.objectId) return { steps, readbackError: "Target table was replaced after an unknown constraint outcome." };
if (plan.change.target && actual.constraints.some((item) => readbackMatches(plan.target, item, plan.change.target!))) {
step.error += " Actual target definition is present after readback.";
} else if (plan.change.original && actual.constraints.some((item) => sortedJson(item) === sortedJson(plan.change.original))) {
step.error += " Original definition remains after readback.";
} else {
step.error += " Readback shows a partial or unexpected constraint state; inspect before recovery.";
}
const recoverySql = await constraintRecoveryCandidate(plan, actual, () => canStart() && connectionCurrent());
if (recoverySql) step.error += ` Manual recovery candidate: ${recoverySql}`;
} catch (readbackError) {
return { steps, readbackError: readbackError instanceof Error ? readbackError.message : String(readbackError) };
}
break;
}
if (!canStart()) break;
if (!connectionCurrent()) return { steps, readbackError: "Connection or target changed during execution; readback is unknown." };
try {
const current = await readOracleConstraintSnapshot(plan.target, plan.change, false, connectionCurrent);
if (!connectionCurrent()) return { steps, readbackError: "Connection or target changed during readback; actual state is unknown." };
if (
current.user !== plan.snapshot.user ||
current.currentSchema !== plan.snapshot.currentSchema ||
current.database !== plan.snapshot.database ||
current.databaseName !== plan.snapshot.databaseName ||
current.objectId !== plan.snapshot.objectId ||
JSON.stringify(current.columns) !== JSON.stringify(plan.snapshot.columns)
) {
return { steps, readbackError: "Target identity changed during constraint DDL; later statements stopped." };
}
} catch (error) {
return { steps, readbackError: error instanceof Error ? error.message : String(error) };
}
}
const outcome: OracleConstraintExecutionResult = { steps };
const oldUnique = plan.change.original?.kind === "unique" ? plan.change.original.definition : undefined;
const oldIndexName = oldUnique?.index.name;
const plannedIndexName = plan.change.target?.kind === "unique" ? plan.change.target.definition.index.newName || plan.change.target.definition.index.name : undefined;
const destructiveIndexChange =
!!oldIndexName && (plan.change.target === null || plan.change.target?.kind !== "unique" || plan.change.target.definition.columns.join("\0") !== oldUnique?.columns.join("\0") || plan.change.target.definition.state.deferrable !== oldUnique?.state.deferrable || plan.change.indexPolicy === "drop");
if (!connectionCurrent()) {
outcome.readbackError = "Connection or target changed during execution; readback is unknown.";
return outcome;
}
if (steps.some((step) => step.state !== "notExecuted")) {
try {
const readback = await readOracleConstraintSnapshot(plan.target, plan.change, false, connectionCurrent);
if (!connectionCurrent()) outcome.readbackError = "Connection or target changed during readback; result is unknown.";
else if (readback.objectId !== plan.snapshot.objectId) outcome.readbackError = "Target table was replaced during constraint DDL.";
else if (steps.every((step) => step.state === "committed") && plan.change.target && !readback.constraints.some((item) => readbackMatches(plan.target, item, plan.change.target!))) outcome.readbackError = "Constraint readback differs from the submitted definition.";
else if (!plan.change.target && readback.constraints.some((item) => item.definition.name === plan.change.original?.definition.name)) outcome.readbackError = "Deleted constraint is still present after readback.";
else if (oldIndexName && plan.change.indexPolicy === "keep" && !readback.indexNames.includes(oldIndexName)) outcome.readbackError = "The selected KEEP INDEX supporting index is missing after readback.";
else if (oldIndexName && destructiveIndexChange && plan.target.databaseType === "oracle" && plan.change.indexPolicy !== "keep" && readback.indexNames.includes(oldIndexName) && plannedIndexName !== oldIndexName)
outcome.readbackError = "The replaced or dropped supporting index remains after readback.";
else if (plannedIndexName && plan.change.target?.kind === "unique" && plan.change.target.definition.state.enabled && plan.change.target.definition.index.newName && !readback.indexNames.includes(plannedIndexName))
outcome.readbackError = "The planned supporting index is missing after readback.";
if (oldUnique && !readback.constraints.some((item) => item.kind === "unique" && item.definition.name === oldUnique.name) && plan.change.target?.kind === "unique" && destructiveIndexChange && canStart())
outcome.recoverySql = await constraintRecoveryCandidate(plan, readback, () => canStart() && connectionCurrent());
} catch (error) {
outcome.readbackError = error instanceof Error ? error.message : String(error);
}
}
return outcome;
}