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
//! Read-only planning for the structure-only transfer SQL preview.
//!
//! A structure-only transfer used to start with no idea what it would run. This module
//! renders the statements the create pass is about to execute so the confirmation dialog can
//! show them first. It is strictly planning: target names, source metadata and the SQL are
//! read from the same helpers the execution pass uses (`ddl_plan::prepare_table_ddl`,
//! `plan_postgres_owned_sequences_for_transfer`, `generate_comment_ddl_with_column_quoting`,
//! `generate_postgres_index_ddl`, `generate_postgres_foreign_key_ddl`,
//! `generate_mysql_foreign_key_alter_statements`), and no statement is ever executed here.
//!
//! The result is a plan, not a frozen script: `start_transfer` re-reads source and target
//! metadata before it executes, and the existing execution-time fail-closed checks stay in
//! force. Callers must therefore never present the preview as "these exact statements will
//! run".
//!
//! Where the create pass normalizes before executing (splitting a reused PostgreSQL table
//! script, stripping inline foreign keys, dropping the post-table index/foreign-key statements
//! it re-applies from metadata), the preview runs the same normalization — a statement the
//! executor filters out must not be shown. Re-applied statements are shown once per step,
//! including the idempotent `COMMENT` the reused script may already contain.
use super::*;
/// Emitted when the request asks for no structure DDL at all (`createTable = false`).
const CREATE_TABLE_DISABLED_NOTE: &str =
"-- This transfer has structure DDL disabled (createTable = false): no structure statements will run.";
/// Emitted when the transfer also moves non-table schema objects whose DDL this preview
/// does not expand (except for the MySQL overwrite path, which is rendered explicitly).
const UNEXPANDED_OBJECTS_NOTE: &str = "\
-- Additional schema objects selected by this request (or the legacy PostgreSQL default) are transferred.
-- Their generated DDL is not expanded in this preview.";
/// Emitted for PostgreSQL-compatible table transfers whose dependency closure is applied by
/// the execution pass but is intentionally not expanded in the table DDL preview.
const POSTGRES_TABLE_DEPENDENCIES_NOTE: &str = "\
-- PostgreSQL table dependencies (types, extensions, policies and sequence bindings) are transferred
-- for the selected tables during execution; their generated SQL is not expanded in this preview.";
/// Plan the structure DDL a structure-only transfer is about to run.
///
/// Everything inside is read-only: metadata reads plus SQL rendering. The only statements
/// handed to `execute_on_pool` are catalog queries (`SELECT`), never DDL.
pub(super) async fn build_structure_preview(
state: &Arc<AppState>,
request: &TransferRequest,
source_db_type: &DatabaseType,
target_db_type: &DatabaseType,
source_pool_key: &str,
target_pool_key: &str,
) -> Result<TransferStructurePreview, String> {
let xugu_plan = if *source_db_type == DatabaseType::Xugu
&& *target_db_type == DatabaseType::Xugu
&& should_transfer_schema_objects(source_db_type, target_db_type, request)
{
Some(xugu_objects::prepare(state, request, source_pool_key).await?)
} else {
None
};
let schema_object_preview = if let Some(plan) = &xugu_plan {
Some(plan.preview_after_tables())
} else {
mysql_schema_object_preview_sql(
state,
request,
source_db_type,
target_db_type,
source_pool_key,
target_pool_key,
)
.await?
};
if !request.create_table {
let mut sections = vec![if request.has_package_selection()
&& matches!(source_db_type, DatabaseType::Oracle | DatabaseType::OceanbaseOracle)
{
"-- Table creation is disabled; selected package definitions are reviewed separately.".into()
} else {
CREATE_TABLE_DISABLED_NOTE.to_string()
}];
if let Some(plan) = &xugu_plan {
sections.push(plan.preview_before_tables());
}
if let Some(sql) = &schema_object_preview {
sections.push(sql.clone());
}
return Ok(TransferStructurePreview {
sql: sections.join("\n\n"),
tables: Vec::new(),
operations: Vec::new(),
schema_objects_expanded: schema_object_preview.is_some(),
});
}
let pg_compat_transfer = is_postgres_compat_transfer(source_db_type, target_db_type);
let index_if_not_exists = pg_compat_transfer && target_supports_if_not_exists_ddl(state, target_pool_key).await;
// Two execution paths create the target schema before any table does: the inline ensure in
// `create_transfer_target_table` (PostgreSQL-dialect target from another family) and
// `transfer_postgres_schema_dependencies` (PostgreSQL→PostgreSQL). Plan the same statement
// for either, deciding it with a read-only existence check instead of executing anything.
let create_schema_sql = if is_postgres_family_target(target_db_type)
&& !request.target_schema.trim().is_empty()
&& !postgres_schema_exists(state, target_pool_key, &request.target_schema).await?
{
Some(format!("CREATE SCHEMA {}", quote_identifier(&request.target_schema, target_db_type)))
} else {
None
};
// Same order and same foreign key metadata the create pass uses.
let (tables, known_foreign_keys) = sorted_source_tables_with_foreign_keys(state, request).await;
let mut sections: Vec<String> = Vec::new();
if let Some(plan) = &xugu_plan {
sections.push(plan.preview_before_tables());
}
let mut operations = Vec::new();
let mut deferred_fk_operations = Vec::new();
if let Some(create_schema_sql) = &create_schema_sql {
operations.push(TransferStructureOperation::schema(&request.target_schema));
sections.push(format!(
"-- Ensure the target schema exists\n{}",
statement_block(std::slice::from_ref(create_schema_sql))
));
}
let mut planned_tables: Vec<TransferStructurePreviewTable> = Vec::with_capacity(tables.len());
let mut table_sections: Vec<String> = Vec::new();
let mut deferred_fk_alters: Vec<String> = Vec::new();
for table in &tables {
let ResolvedTransferTargetTable { name: target_table, preexisting } = resolve_transfer_target_table_name(
state,
request,
table,
target_pool_key,
target_db_type,
request.source_catalog.as_deref(),
request.target_catalog.as_deref(),
)
.await;
// A rebuild renames every preexisting target aside before the create pass runs, so
// those tables are created fresh — the same reset the create pass performs.
if preexisting && !request.drop_target_before_create {
operations.push(TransferStructureOperation::table(
TransferStructureOperationKind::SkipExistingTable,
table,
&target_table,
));
let note = format!(
"-- {table} -> {target_table}: the target table already exists, so this transfer plans no structure \
DDL for it"
);
planned_tables.push(TransferStructurePreviewTable {
oracle_plan: None,
source_table: table.clone(),
target_table,
preexisting: true,
sql: note.clone(),
});
table_sections.push(note);
continue;
}
let planned = plan_table_structure(
state,
request,
table,
&target_table,
preexisting && request.drop_target_before_create,
source_db_type,
target_db_type,
source_pool_key,
target_pool_key,
&known_foreign_keys,
pg_compat_transfer,
index_if_not_exists,
)
.await?;
deferred_fk_alters.extend(planned.deferred_fk_alters);
operations.extend(planned.operations);
deferred_fk_operations.extend(planned.deferred_fk_operations);
let section = format!("-- {} -> {}\n{}", table, target_table, statement_block(&planned.statements));
planned_tables.push(TransferStructurePreviewTable {
oracle_plan: planned.oracle_plan,
source_table: table.clone(),
target_table,
preexisting: false,
sql: section.clone(),
});
table_sections.push(section);
}
sections.extend(table_sections);
if pg_compat_transfer && !request.tables.is_empty() {
sections.push(POSTGRES_TABLE_DEPENDENCIES_NOTE.to_string());
}
if !deferred_fk_alters.is_empty() {
// MySQL-family targets defer foreign keys so creation order never has to satisfy
// them; the create pass flushes these after every table exists.
sections.push(format!(
"-- Foreign keys added after every table exists, so creation order never has to satisfy them\n{}",
statement_block(&deferred_fk_alters)
));
}
operations.extend(deferred_fk_operations);
if let Some(sql) = schema_object_preview {
sections.push(sql);
} else {
sections.extend(unexpanded_schema_object_notes(source_db_type, target_db_type, request));
}
Ok(TransferStructurePreview {
sql: sections.join("\n\n"),
tables: planned_tables,
operations,
schema_objects_expanded: xugu_plan.is_some()
|| request.mode == TransferMode::Overwrite
&& transfer_object_family(source_db_type) == Some(TransferObjectFamily::Mysql)
&& transfer_object_family(target_db_type) == Some(TransferObjectFamily::Mysql)
&& request.object_selection_mode().selections().iter().any(|selection| {
selection.object_type != TransferObjectKind::Table
&& transfer_object_kinds_for_family(&TransferObjectFamily::Mysql)
.contains(&selection.object_type)
&& !selection.names.is_empty()
}),
})
}
/// Expands the MySQL overwrite path in the read-only structure preview. The
/// executor re-reads the same source DDL and target existence state, but the
/// preview intentionally never executes any generated DDL.
async fn mysql_schema_object_preview_sql(
state: &Arc<AppState>,
request: &TransferRequest,
source_db_type: &DatabaseType,
target_db_type: &DatabaseType,
source_pool_key: &str,
target_pool_key: &str,
) -> Result<Option<String>, String> {
if request.mode != TransferMode::Overwrite
|| transfer_object_family(source_db_type) != Some(TransferObjectFamily::Mysql)
|| transfer_object_family(target_db_type) != Some(TransferObjectFamily::Mysql)
{
return Ok(None);
}
let selections = request.object_selection_mode().selections();
let supported_kinds = transfer_object_kinds_for_family(&TransferObjectFamily::Mysql);
if !selections.iter().any(|selection| {
selection.object_type != TransferObjectKind::Table
&& supported_kinds.contains(&selection.object_type)
&& !selection.names.is_empty()
}) {
return Ok(None);
}
let source_database = &request.source_database;
let target_database = if request.target_database.trim().is_empty() {
source_database.as_str()
} else {
request.target_database.as_str()
};
let mut sections = Vec::new();
let order = ordered_transfer_object_kinds(
selections
.iter()
.filter(|selection| {
selection.object_type != TransferObjectKind::Table && supported_kinds.contains(&selection.object_type)
})
.map(|selection| selection.object_type)
.collect(),
);
for kind in order {
for name in selected_object_names(selections, &kind) {
let exists_sql = target_object_exists_sql(&DatabaseType::Mysql, target_database, &name, &kind)?;
let exists = !execute_on_pool(state, target_pool_key, &exists_sql).await?.rows.is_empty();
let query = mysql_object_source_query(&kind, source_database, &name)?;
let result = execute_on_pool(state, source_pool_key, &query).await?;
let raw_ddl = mysql_object_ddl_from_result(&kind, source_database, &result.rows)?;
let ddl = rewrite_mysql_schema_qualifier(&strip_mysql_definer(&raw_ddl), source_database, target_database);
let validation_name =
if exists && matches!(kind, TransferObjectKind::Procedure | TransferObjectKind::Function) {
Some(mysql_transfer_validation_routine_name(&request.transfer_id, target_database, &kind, &name))
} else {
None
};
let statements = if exists {
mysql_object_replacement_statements(&kind, target_database, &name, &ddl, validation_name.as_deref())?
} else {
vec![ddl]
};
let operation = if exists { "Replace" } else { "Create" };
sections.push(format!("-- {operation} MySQL {kind:?} {name}\n{}", statement_block(&statements)));
}
}
Ok((!sections.is_empty()).then(|| sections.join("\n\n")))
}
struct PlannedTableStructure {
oracle_plan: Option<ddl_plan::PreparedTableDdl>,
statements: Vec<String>,
operations: Vec<TransferStructureOperation>,
deferred_fk_alters: Vec<String>,
deferred_fk_operations: Vec<TransferStructureOperation>,
}
fn sequence_operation(
kind: TransferStructureOperationKind,
sequence: &PostgresOwnedSequence,
target_table: &str,
) -> TransferStructureOperation {
TransferStructureOperation::object(kind, &sequence.name, &sequence.owner_table, target_table)
}
fn postgres_index_operations(
indexes: &[db::IndexInfo],
source_table: &str,
target_table: &str,
) -> Vec<TransferStructureOperation> {
indexes
.iter()
.filter(|index| !index.is_primary && !index.name.trim().is_empty() && !index.columns.is_empty())
.map(|index| {
TransferStructureOperation::object(
TransferStructureOperationKind::CreateIndex,
&index.name,
source_table,
target_table,
)
})
.collect()
}
fn foreign_key_operations(names: &[String], source_table: &str, target_table: &str) -> Vec<TransferStructureOperation> {
names
.iter()
.map(|name| {
TransferStructureOperation::object(
TransferStructureOperationKind::AddForeignKey,
name,
source_table,
target_table,
)
})
.collect()
}
fn comment_operations(
columns: &[db::ColumnInfo],
source_table: &str,
target_table: &str,
target_db_type: &DatabaseType,
table_comment: Option<&str>,
) -> Vec<TransferStructureOperation> {
let table_comments_supported = supports_comment_on_transfer_ddl(target_db_type);
let column_comments_supported = table_comments_supported || matches!(target_db_type, DatabaseType::ClickHouse);
let mut operations = Vec::new();
if table_comments_supported && table_comment.is_some_and(|comment| !comment.trim().is_empty()) {
operations.push(TransferStructureOperation::table(
TransferStructureOperationKind::AddComment,
source_table,
target_table,
));
}
if column_comments_supported {
operations.extend(columns.iter().filter_map(|column| {
column.comment.as_deref().filter(|comment| !comment.trim().is_empty()).map(|_| {
TransferStructureOperation::object(
TransferStructureOperationKind::AddComment,
&column.name,
source_table,
target_table,
)
})
}));
}
operations
}
/// Render the statements the create pass runs for one table, in execution order.
#[allow(clippy::too_many_arguments)]
async fn plan_table_structure(
state: &Arc<AppState>,
request: &TransferRequest,
table: &str,
target_table: &str,
rebuild_existing_target: bool,
source_db_type: &DatabaseType,
target_db_type: &DatabaseType,
source_pool_key: &str,
target_pool_key: &str,
known_foreign_keys: &HashMap<String, Vec<db::ForeignKeyInfo>>,
pg_compat_transfer: bool,
index_if_not_exists: bool,
) -> Result<PlannedTableStructure, String> {
let preserves_target_table_name = target_table == table && request.preserves_column_names();
let columns = source_transfer_columns(state, request, table, source_pool_key).await?;
let table_comment = source_table_comment(state, request, table, source_db_type).await;
// Planned here, created by the execution pass.
let owned_sequences = plan_postgres_owned_sequences_for_transfer(
state,
request,
table,
source_pool_key,
target_pool_key,
pg_compat_transfer,
preserves_target_table_name,
false,
)
.await?;
// Shared with `create_transfer_target_table`: the same helper renders the DDL that runs.
let prepared = ddl_plan::prepare_table_ddl(
state,
request,
table,
target_table,
source_db_type,
target_db_type,
source_pool_key,
&columns,
table_comment.as_deref(),
known_foreign_keys,
)
.await?;
let oracle_plan = request.has_package_selection().then(|| prepared.clone());
let ddl = if pg_compat_transfer && !owned_sequences.owned_sequences.is_empty() {
rewrite_postgres_serial_columns_for_transfer(
&prepared.ddl,
&owned_sequences.owned_sequences,
&request.target_schema,
)
} else {
prepared.ddl
};
let mut statements = Vec::new();
let mut operations = Vec::new();
for (sequence, statement) in owned_sequences.create_sequences.iter().zip(&owned_sequences.create_statements) {
statements.push(statement.clone());
operations.push(sequence_operation(TransferStructureOperationKind::CreateSequence, sequence, target_table));
}
let table_kind = if rebuild_existing_target {
TransferStructureOperationKind::RebuildTable
} else {
TransferStructureOperationKind::CreateTable
};
operations.push(TransferStructureOperation::table(table_kind, table, target_table));
// Normalize the DDL exactly like the executor does before running it: the reused
// PostgreSQL script spans several statements, has its inline foreign keys stripped, and
// drops the post-table index/foreign-key statements that are re-applied from structured
// metadata below. A statement the create pass filters out must never appear here.
statements.extend(transfer_ddl_statements(&ddl, target_db_type));
let target_columns = request.target_columns(&columns);
let comment_statements = generate_comment_ddl_with_column_quoting(
&target_columns,
target_table,
&request.target_schema,
target_db_type,
table_comment.as_deref(),
request.quote_target_column_names,
);
operations.extend(comment_operations(
&target_columns,
table,
target_table,
target_db_type,
table_comment.as_deref(),
));
statements.extend(comment_statements);
for sequence in &owned_sequences.owned_sequences {
statements.push(postgres_owned_sequence_bind_sql(request, sequence));
operations.push(sequence_operation(TransferStructureOperationKind::BindSequence, sequence, target_table));
}
let mut deferred_fk_operations = Vec::new();
if supports_deferred_mysql_foreign_keys(target_db_type) {
deferred_fk_operations = foreign_key_operations(&prepared.deferred_fk_names, table, target_table);
}
if pg_compat_transfer && preserves_target_table_name {
// PostgreSQL-compatible targets restore indexes and foreign keys from structured
// source metadata once the table exists. The operation records use the same metadata
// consumed by the DDL generators; SQL is never parsed to infer them.
let indexes = get_postgres_indexes_for_transfer(
state,
source_pool_key,
&request.source_database,
&request.source_schema,
table,
)
.await?;
statements.extend(generate_postgres_index_ddl(
&indexes,
target_table,
&request.target_schema,
index_if_not_exists,
));
operations.extend(postgres_index_operations(&indexes, table, target_table));
let foreign_keys = get_postgres_foreign_keys_for_transfer(
state,
source_pool_key,
&request.source_database,
&request.source_schema,
table,
)
.await?;
statements.extend(generate_postgres_foreign_key_ddl(
&foreign_keys,
target_table,
&request.source_schema,
&request.target_schema,
));
let foreign_key_names = group_foreign_keys_by_constraint_name(&foreign_keys)
.into_iter()
.map(|(name, _)| name.to_string())
.collect::<Vec<_>>();
operations.extend(foreign_key_operations(&foreign_key_names, table, target_table));
}
Ok(PlannedTableStructure {
oracle_plan,
statements,
operations,
deferred_fk_alters: prepared.deferred_fk_alters,
deferred_fk_operations,
})
}
/// Read and de-duplicate the source columns the create pass uses, failing the same way the
/// pass does when the source returns nothing.
async fn source_transfer_columns(
state: &AppState,
request: &TransferRequest,
table: &str,
source_pool_key: &str,
) -> Result<Vec<db::ColumnInfo>, String> {
let raw = get_columns_for_transfer(
state,
source_pool_key,
&request.source_connection_id,
&request.source_database,
&request.source_schema,
table,
request.source_catalog.as_deref(),
)
.await?;
let mut seen = HashSet::new();
let columns = raw.into_iter().filter(|column| seen.insert(column.name.clone())).collect::<Vec<_>>();
if columns.is_empty() {
return Err(format!("No columns found for table {table}"));
}
Ok(columns)
}
/// Source table comment, via the same fallback chain the create pass uses. A metadata read
/// failure only drops the comment, exactly like execution.
async fn source_table_comment(
state: &Arc<AppState>,
request: &TransferRequest,
table: &str,
source_db_type: &DatabaseType,
) -> Option<String> {
if *source_db_type == DatabaseType::Postgres {
get_transfer_table_comment_isolated(
state.clone(),
request.source_connection_id.clone(),
request.source_database.clone(),
request.source_schema.clone(),
table.to_string(),
)
.await
.unwrap_or_default()
} else {
list_transfer_tables_isolated(
state.clone(),
request.source_connection_id.clone(),
request.source_database.clone(),
request.source_schema.clone(),
request.source_catalog.clone(),
*source_db_type,
table.to_string(),
1,
)
.await
.unwrap_or_default()
.into_iter()
.next()
.and_then(|info| info.comment)
}
}
/// Table creation order plus the foreign key metadata behind the deferred MySQL alters, using
/// the same helper (and the same external-catalog skip) the transfer pass uses.
async fn sorted_source_tables_with_foreign_keys(
state: &Arc<AppState>,
request: &TransferRequest,
) -> (Vec<String>, HashMap<String, Vec<db::ForeignKeyInfo>>) {
if request.tables.len() <= 1 {
return (request.tables.clone(), HashMap::new());
}
let skip_fk_sort = {
let configs = state.configs.read().await;
configs
.get(&request.source_connection_id)
.and_then(|config| resolve_external_transfer_catalog_for_config(request.source_catalog.as_deref(), config))
.is_some()
};
if skip_fk_sort {
return (request.tables.clone(), HashMap::new());
}
sort_tables_by_fk_dependency_with_foreign_keys(
state,
&request.source_connection_id,
&request.source_database,
&request.source_schema,
&request.tables,
true,
)
.await
.unwrap_or_else(|error| {
log::warn!("[transfer] structure preview could not sort tables by FK dependency: {error}");
(request.tables.clone(), HashMap::new())
})
}
async fn postgres_schema_exists(state: &AppState, target_pool_key: &str, target_schema: &str) -> Result<bool, String> {
let result = execute_on_pool(state, target_pool_key, &postgres_schema_exists_sql(target_schema)).await?;
Ok(query_result_has_rows(&result))
}
/// The statement block the preview never expands: selected non-table schema objects.
fn unexpanded_schema_object_notes(
source_db_type: &DatabaseType,
target_db_type: &DatabaseType,
request: &TransferRequest,
) -> Vec<String> {
if !should_transfer_schema_objects(source_db_type, target_db_type, request) {
return Vec::new();
}
let selections = match request.object_selection_mode() {
TransferObjectSelectionMode::LegacyUnspecified => return vec![UNEXPANDED_OBJECTS_NOTE.to_string()],
TransferObjectSelectionMode::Explicit(selections) => selections,
};
let mut notes = Vec::new();
for selection in selections {
if matches!(
selection.object_type,
TransferObjectKind::Table | TransferObjectKind::Package | TransferObjectKind::PackageBody
) || selection.names.is_empty()
{
continue;
}
if notes.is_empty() {
notes.push(UNEXPANDED_OBJECTS_NOTE.to_string());
}
notes.push(format!("-- {}: {}", preview_object_kind_label(&selection.object_type), selection.names.join(", ")));
}
notes
}
fn preview_object_kind_label(kind: &TransferObjectKind) -> &'static str {
match kind {
TransferObjectKind::Table => "Tables",
TransferObjectKind::View => "Views",
TransferObjectKind::MaterializedView => "Materialized views",
TransferObjectKind::Procedure => "Procedures",
TransferObjectKind::Function => "Functions",
TransferObjectKind::Package => "Packages",
TransferObjectKind::PackageBody => "Package bodies",
TransferObjectKind::Trigger => "Triggers",
TransferObjectKind::Sequence => "Sequences",
TransferObjectKind::Event => "Events",
TransferObjectKind::Synonym => "Synonyms",
}
}
fn statement_block(statements: &[String]) -> String {
statements
.iter()
.map(|statement| ensure_sql_statement_terminated(statement))
.filter(|statement| !statement.is_empty() && statement != ";")
.collect::<Vec<_>>()
.join("\n")
}
#[cfg(test)]
mod tests {
use super::*;
use serde_json::json;
#[test]
fn internal_table_provenance_is_not_part_of_the_legacy_structure_payload() {
let table = TransferStructurePreviewTable {
source_table: "source".into(),
target_table: "target".into(),
preexisting: false,
sql: "CREATE TABLE target (N NUMBER)".into(),
oracle_plan: Some(ddl_plan::PreparedTableDdl {
ddl: "CREATE TABLE target (N NUMBER)".into(),
reused_source_ddl: true,
deferred_fk_alters: Vec::new(),
deferred_fk_names: Vec::new(),
source_evidence: Some(json!({"sourceDdl":"full original input"})),
}),
};
assert_eq!(
table.oracle_plan.as_ref().unwrap().source_evidence.as_ref().unwrap()["sourceDdl"],
"full original input"
);
assert_eq!(
serde_json::to_value(&table).unwrap(),
json!({
"sourceTable":"source", "targetTable":"target", "preexisting":false,
"sql":"CREATE TABLE target (N NUMBER)",
})
);
}
#[test]
fn structured_ddl_metadata_produces_operation_dtos() {
let index = db::IndexInfo {
owner: None,
key_directions: Vec::new(),
visibility: None,
key_metadata_complete: false,
name: "idx_orders_created_at".into(),
columns: vec!["lower(\"email\")".into()],
is_unique: false,
is_primary: false,
filter: None,
index_type: Some("btree".into()),
included_columns: None,
comment: None,
key_is_expression: vec![true],
column_opclasses: vec![Some("text_pattern_ops".into())],
key_options: vec![1],
constraint_backed: false,
};
let index_sql = generate_postgres_index_ddl(std::slice::from_ref(&index), "orders", "public", true);
let index_operations = postgres_index_operations(std::slice::from_ref(&index), "orders", "orders");
assert_eq!(
index_sql,
vec![
"CREATE INDEX IF NOT EXISTS \"idx_orders_created_at\" ON \"public\".\"orders\" USING btree (lower(\"email\") text_pattern_ops DESC NULLS LAST)"
]
);
assert_eq!(index_operations[0].kind, TransferStructureOperationKind::CreateIndex);
assert_eq!(index_operations[0].object_name.as_deref(), Some("idx_orders_created_at"));
let foreign_key = db::ForeignKeyInfo {
name: "fk_orders_user".into(),
column: "user_id".into(),
ref_schema: Some("public".into()),
ref_table: "users".into(),
ref_column: "id".into(),
on_update: None,
on_delete: None,
};
let foreign_key_sql =
generate_postgres_foreign_key_ddl(std::slice::from_ref(&foreign_key), "orders", "public", "reporting");
let foreign_key_names = group_foreign_keys_by_constraint_name(std::slice::from_ref(&foreign_key))
.into_iter()
.map(|(name, _)| name.to_string())
.collect::<Vec<_>>();
let pg_foreign_key_operations = foreign_key_operations(&foreign_key_names, "orders", "orders");
assert!(foreign_key_sql[0].contains("fk_orders_user"));
assert_eq!(pg_foreign_key_operations[0].kind, TransferStructureOperationKind::AddForeignKey);
assert_eq!(pg_foreign_key_operations[0].object_name.as_deref(), Some("fk_orders_user"));
let request = structure_request(json!({
"sourceDatabase": "app",
"sourceSchema": "public",
"targetDatabase": "warehouse",
"targetSchema": "reporting"
}));
let mysql_foreign_key_sql = generate_mysql_foreign_key_alter_statements(
std::slice::from_ref(&foreign_key),
&request,
"orders",
&DatabaseType::Mysql,
);
let mysql_foreign_key_operations = foreign_key_operations(&foreign_key_names, "orders", "orders");
assert!(mysql_foreign_key_sql[0].contains("ADD CONSTRAINT `fk_orders_user`"));
assert_eq!(mysql_foreign_key_operations[0].kind, TransferStructureOperationKind::AddForeignKey);
let sequence = PostgresOwnedSequence {
name: "orders_id_seq".into(),
owner_table: "orders".into(),
owner_column: "id".into(),
};
let sequence_definition = PostgresTransferSequence {
name: sequence.name.clone(),
data_type: "bigint".into(),
start_value: "1".into(),
min_value: "1".into(),
max_value: "9223372036854775807".into(),
increment: "1".into(),
cycle: false,
cache_value: "1".into(),
last_value: None,
is_called: None,
};
let create_sequence_sql =
generate_postgres_transfer_sequence_create_ddl(&sequence_definition, "reporting", false);
let bind_sequence_sql = postgres_owned_sequence_bind_sql(&request, &sequence);
let create_sequence_operation =
sequence_operation(TransferStructureOperationKind::CreateSequence, &sequence, "orders");
let bind_sequence_operation =
sequence_operation(TransferStructureOperationKind::BindSequence, &sequence, "orders");
assert!(create_sequence_sql.contains("CREATE SEQUENCE"));
assert_eq!(create_sequence_operation.kind, TransferStructureOperationKind::CreateSequence);
assert!(bind_sequence_sql.contains("OWNED BY"));
assert_eq!(bind_sequence_operation.kind, TransferStructureOperationKind::BindSequence);
assert_eq!(bind_sequence_operation.target_table.as_deref(), Some("orders"));
let columns = vec![db::ColumnInfo {
name: "id".into(),
comment: Some("primary identifier".into()),
..Default::default()
}];
let comment_sql = generate_comment_ddl_with_column_quoting(
&columns,
"orders",
"reporting",
&DatabaseType::Postgres,
Some("order records"),
true,
);
let comment_operations =
comment_operations(&columns, "orders", "orders", &DatabaseType::Postgres, Some("order records"));
assert_eq!(comment_sql.len(), 2);
assert_eq!(comment_operations.len(), 2);
assert!(comment_operations
.iter()
.all(|operation| operation.kind == TransferStructureOperationKind::AddComment));
assert_eq!(comment_operations[0].object_name, None);
assert_eq!(comment_operations[1].object_name.as_deref(), Some("id"));
let schema = serde_json::to_value(TransferStructureOperation::schema("reporting")).unwrap();
assert_eq!(schema["kind"], "createSchema");
assert_eq!(schema["objectName"], "reporting");
assert!(schema.get("sourceTable").is_none());
}
#[test]
fn unexpanded_object_notes_distinguish_legacy_and_explicit_empty_selections() {
let legacy = structure_request(json!({}));
let explicit_empty = structure_request(json!({ "objects": [] }));
let explicit_view = structure_request(json!({
"objects": [{ "objectType": "VIEW", "names": ["v_orders"] }]
}));
assert_eq!(
unexpanded_schema_object_notes(&DatabaseType::Postgres, &DatabaseType::Postgres, &legacy),
vec![UNEXPANDED_OBJECTS_NOTE.to_string()],
);
assert!(unexpanded_schema_object_notes(&DatabaseType::Postgres, &DatabaseType::Postgres, &explicit_empty)
.is_empty());
assert_eq!(
unexpanded_schema_object_notes(&DatabaseType::Postgres, &DatabaseType::Postgres, &explicit_view),
vec![UNEXPANDED_OBJECTS_NOTE.to_string(), "-- Views: v_orders".to_string()],
);
}
async fn sqlite_fixture() -> (tempfile::TempDir, Arc<AppState>, String, String) {
let directory = tempfile::tempdir().unwrap();
let storage = crate::persistence::test_storage::open(&directory.path().join("storage.db")).await.unwrap();
let state = Arc::new(AppState::new_with_plugin_dir(storage, directory.path().join("plugins")));
for id in ["source", "target"] {
let path = directory.path().join(format!("{id}.db"));
crate::db::sqlite::connect_path_create_if_missing(path.to_str().unwrap()).await.unwrap();
let config: ConnectionConfig = serde_json::from_value(json!({
"id": id, "name": id, "db_type": "sqlite", "host": path.to_str().unwrap(),
"port": 0, "username": "", "password": "", "database": null,
"one_time": false, "save_password": false, "read_only": false
}))
.unwrap();
state.configs.write().await.insert(id.to_string(), config);
}
let source_pool = ensure_transfer_pool(&state, "source", "main", None).await.unwrap();
let target_pool = ensure_transfer_pool(&state, "target", "main", None).await.unwrap();
for table in ["orders", "users"] {
execute_on_pool(&state, &source_pool, &format!("CREATE TABLE {table}(id INTEGER PRIMARY KEY, name TEXT)"))
.await
.unwrap();
}
(directory, state, source_pool, target_pool)
}
fn structure_request(overrides: serde_json::Value) -> TransferRequest {
let mut value = json!({
"transferId": uuid::Uuid::new_v4().to_string(),
"sourceConnectionId": "source", "sourceDatabase": "main", "sourceSchema": "main",
"targetConnectionId": "target", "targetDatabase": "main", "targetSchema": "main",
"tables": ["orders", "users"], "createTable": true, "content": "structureOnly",
"mode": "append", "batchSize": 10
});
let map = value.as_object_mut().unwrap();
for (key, override_value) in overrides.as_object().unwrap() {
map.insert(key.clone(), override_value.clone());
}
serde_json::from_value(value).unwrap()
}
async fn preview(
state: &Arc<AppState>,
request: &TransferRequest,
source_pool: &str,
target_pool: &str,
) -> Result<TransferOwnershipPreview, String> {
preview_transfer_ownership(
state,
request,
&DatabaseType::Sqlite,
&DatabaseType::Sqlite,
source_pool,
target_pool,
)
.await
}
async fn target_table_names(state: &AppState, target_pool: &str) -> Vec<String> {
let result = execute_read_on_pool(
state,
target_pool,
"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name",
)
.await
.unwrap();
result.rows.iter().filter_map(|row| json_string_cell(row, 0)).collect()
}
#[tokio::test]
async fn structure_only_preview_plans_create_table_without_executing_ddl() {
let (_directory, state, source_pool, target_pool) = sqlite_fixture().await;
let request = structure_request(json!({}));
let preview = preview(&state, &request, &source_pool, &target_pool).await.unwrap();
let structure = preview.structure.expect("structure-only must expose its structure SQL plan");
assert_eq!(structure.tables.len(), 2, "every selected table must be planned: {:?}", structure.tables);
assert_eq!(
structure
.operations
.iter()
.filter(|operation| operation.kind == TransferStructureOperationKind::CreateTable)
.count(),
2,
"new targets must have one logical create operation each: {:?}",
structure.operations
);
for table in &structure.tables {
assert!(!table.preexisting, "a fresh target has no preexisting tables: {table:?}");
assert!(table.sql.contains("CREATE TABLE"), "{}", table.sql);
}
assert!(structure.sql.contains("orders"), "{}", structure.sql);
assert!(structure.sql.contains("users"), "{}", structure.sql);
// Planning must not run any of it: the target database stays empty.
assert_eq!(target_table_names(&state, &target_pool).await, Vec::<String>::new());
}
#[tokio::test]
async fn structure_only_preview_handles_preexisting_target_consistently() {
let (_directory, state, source_pool, target_pool) = sqlite_fixture().await;
execute_on_pool(&state, &target_pool, "CREATE TABLE orders(id INTEGER PRIMARY KEY, legacy TEXT)")
.await
.unwrap();
execute_on_pool(&state, &target_pool, "INSERT INTO orders VALUES(7, 'original')").await.unwrap();
let request = structure_request(json!({ "tables": ["orders"] }));
let preview = preview(&state, &request, &source_pool, &target_pool).await.unwrap();
let structure = preview.structure.expect("structure-only must expose its structure SQL plan");
assert_eq!(structure.tables.len(), 1);
assert!(structure.tables[0].preexisting, "an existing target must be reported as preexisting: {structure:?}");
assert!(
!structure.sql.contains("CREATE TABLE"),
"execution skips create DDL for a preexisting target, so the preview must too: {}",
structure.sql
);
assert!(structure.sql.contains("already exists"), "{}", structure.sql);
assert_eq!(structure.operations.len(), 1);
assert_eq!(structure.operations[0].kind, TransferStructureOperationKind::SkipExistingTable);
assert_eq!(structure.operations[0].target_table.as_deref(), Some("orders"));
// The preview must not have touched the preexisting table.
let rows = execute_read_on_pool(&state, &target_pool, "SELECT legacy FROM orders WHERE id = 7").await.unwrap();
assert_eq!(rows.rows[0][0], json!("original"));
}
#[tokio::test]
async fn structure_only_preview_without_create_table_reports_no_structure_ddl() {
let (_directory, state, source_pool, target_pool) = sqlite_fixture().await;
let request = structure_request(json!({ "createTable": false }));
let preview = preview(&state, &request, &source_pool, &target_pool).await.unwrap();
let structure = preview
.structure
.expect("structure-only always answers with a structure preview so clients can fail closed");
assert!(!structure.sql.contains("CREATE TABLE"), "{}", structure.sql);
assert!(structure.sql.contains("createTable = false"), "{}", structure.sql);
}
#[tokio::test]
async fn data_only_has_no_structure_preview() {
let (_directory, state, source_pool, target_pool) = sqlite_fixture().await;
let request = structure_request(json!({ "content": "dataOnly", "createTable": false }));
let preview = preview(&state, &request, &source_pool, &target_pool).await.unwrap();
assert!(preview.structure.is_none(), "data-only runs no structure DDL: {preview:?}");
assert!(preview.rebuild.is_none());
}
#[tokio::test]
async fn structure_and_data_keeps_existing_preview_behavior() {
let (_directory, state, source_pool, target_pool) = sqlite_fixture().await;
execute_on_pool(&state, &target_pool, "CREATE TABLE orders(id INTEGER PRIMARY KEY, legacy TEXT)")
.await
.unwrap();
let request = structure_request(json!({ "content": "structureAndData", "tables": ["orders"] }));
let preview = preview(&state, &request, &source_pool, &target_pool).await.unwrap();
assert!(
preview.structure.is_none(),
"structure-and-data keeps its current behavior in this phase: {preview:?}"
);
assert!(preview.rebuild.is_none());
assert!(preview.missing_owners.is_empty());
}
#[tokio::test]
async fn rebuild_preview_still_contains_existing_rename_and_cleanup_sql() {
let (_directory, state, source_pool, target_pool) = sqlite_fixture().await;
for table in ["orders", "users"] {
execute_on_pool(
&state,
&target_pool,
&format!("CREATE TABLE {table}(id INTEGER PRIMARY KEY, legacy TEXT)"),
)
.await
.unwrap();
}
let request = structure_request(json!({ "dropTargetBeforeCreate": true, "dropTargetConfirmed": true }));
let preview = preview(&state, &request, &source_pool, &target_pool).await.unwrap();
let rebuild = preview.rebuild.expect("a rebuild transfer must expose its SQL plan");
assert!(rebuild.sql.contains("-- 1. Backup existing target tables"), "{}", rebuild.sql);
assert!(rebuild.sql.contains("-- 2. Recreate the"), "{}", rebuild.sql);
assert!(rebuild.sql.contains("-- 3. Drop backups after success"), "{}", rebuild.sql);
assert!(rebuild.backup_sql.as_deref().unwrap_or_default().contains("RENAME"), "{rebuild:?}");
assert!(rebuild.cleanup_sql.as_deref().unwrap_or_default().contains("DROP TABLE"), "{rebuild:?}");
// The structure plan is rendered without repeating the rename/cleanup operations.
let structure = preview.structure.expect("structure-only must expose its structure SQL plan");
assert!(structure.sql.contains("CREATE TABLE"), "{}", structure.sql);
assert_eq!(
structure
.operations
.iter()
.filter(|operation| operation.kind == TransferStructureOperationKind::RebuildTable)
.count(),
2,
"each rebuilt target is represented once: {:?}",
structure.operations
);
assert!(
structure.operations.iter().all(|operation| operation.kind != TransferStructureOperationKind::CreateTable),
"a rebuild must not also be listed as a normal create: {:?}",
structure.operations
);
assert!(!structure.sql.contains("RENAME"), "{}", structure.sql);
assert!(!structure.sql.contains("DROP TABLE"), "{}", structure.sql);
for table in &structure.tables {
assert!(!table.preexisting, "a rebuild renames the old target aside, so it is created fresh: {table:?}");
}
// Still pure planning: nothing was renamed, nothing was dropped.
assert_eq!(target_table_names(&state, &target_pool).await, vec!["orders".to_string(), "users".to_string()]);
}
}