Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

8.5 KB
Raw Blame
//! Overwrite pre-pass for target tables linked by foreign keys inside the transfer.
//!
//! `transfer_table` clears each overwrite target right before copying into it, and the
//! transfer loop visits tables parents first. That order cannot clear a referenced table:
//! SQL Server (4712), MySQL (1701) and PostgreSQL refuse to `TRUNCATE` a table another
//! table's foreign key points at, even when the referencing table is already empty, and a
//! parent cannot lose its rows while a child still references them. This pass empties the
//! linked tables children first before the loop starts, and the loop then skips the
//! per-table clear for them.

use super::*;

/// Empty the overwrite targets that are linked to each other by foreign keys, children first.
///
/// Tables referenced by another selected table are cleared with `DELETE`, the rest with the
/// usual per-database clear statement. Returns the source names of the tables that were
/// cleared; the caller passes `target_cleared = true` to `transfer_table_with_result` for
/// those so they are not cleared a second time.
///
/// Returns an empty set, leaving every table to the per-table clear, when the request is not
/// an overwrite, when no selected target references another, when the foreign key metadata
/// cannot be read, or when a table outside the selection references one of the linked
/// tables: a `DELETE` there could cascade into rows the transfer never selected.
pub async fn clear_foreign_key_linked_overwrite_targets(
    state: &Arc<AppState>,
    request: &TransferRequest,
    tables: &[String],
    target_db_type: DatabaseType,
    target_pool_key: &str,
) -> Result<HashSet<String>, String> {
    super::check_data_charset_transfer(state, request).await?;
    if !overwrite_clear_prepass_enabled(request, tables.len(), &target_db_type) {
        return Ok(HashSet::new());
    }

    let mut targets: Vec<(String, String)> = Vec::new();
    for table in tables {
        let ResolvedTransferTargetTable { name, 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;
        if preexisting {
            targets.push((table.clone(), name));
        }
    }

    let mut edges: Vec<(String, String)> = Vec::new();
    for (_, target_table) in &targets {
        let foreign_keys = match crate::schema::list_foreign_keys_core(
            state,
            &request.target_connection_id,
            &request.target_database,
            &request.target_schema,
            target_table,
        )
        .await
        {
            Ok(foreign_keys) => foreign_keys,
            Err(error) => {
                log::warn!(
                    "[transfer] overwrite pre-pass skipped: cannot read foreign keys of {target_table}: {error}"
                );
                return Ok(HashSet::new());
            }
        };
        for foreign_key in foreign_keys {
            let referenced_in_target_schema = foreign_key.ref_schema.as_deref().is_none_or(|schema| {
                schema.is_empty()
                    || schema.eq_ignore_ascii_case(&request.target_schema)
                    || schema.eq_ignore_ascii_case(&request.target_database)
            });
            if referenced_in_target_schema
                && foreign_key.ref_table != *target_table
                && targets.iter().any(|(_, name)| *name == foreign_key.ref_table)
            {
                edges.push((target_table.clone(), foreign_key.ref_table));
            }
        }
    }
    edges.sort();
    edges.dedup();
    if edges.is_empty() {
        return Ok(HashSet::new());
    }

    let linked: Vec<String> = targets
        .iter()
        .map(|(_, name)| name.clone())
        .filter(|name| edges.iter().any(|(child, parent)| child == name || parent == name))
        .collect();
    match crate::transfer_rebuild::detect_external_incoming_foreign_keys(
        state,
        target_pool_key,
        &request.target_database,
        &request.target_schema,
        &linked,
        target_db_type,
    )
    .await
    {
        Ok(external) if external.is_empty() => {}
        Ok(external) => {
            log::warn!(
                "[transfer] overwrite pre-pass skipped: {} foreign key(s) from outside the transfer reference the target tables",
                external.len()
            );
            return Ok(HashSet::new());
        }
        Err(error) => {
            log::warn!("[transfer] overwrite pre-pass skipped: cannot check incoming foreign keys: {error}");
            return Ok(HashSet::new());
        }
    }

    let mut cleared = HashSet::new();
    for target_table in sort_table_names_by_dependencies(&linked, &edges, false) {
        let referenced = edges.iter().any(|(_, parent)| *parent == target_table);
        let sql = overwrite_pre_pass_clear_sql(
            &target_table,
            &request.target_schema,
            &target_db_type,
            request.target_catalog.as_deref(),
            referenced,
        );
        execute_on_pool(state, target_pool_key, &sql)
            .await
            .map_err(|error| format!("Failed to clear target table '{target_table}' before overwrite: {error}"))?;
        if let Some((source_table, _)) = targets.iter().find(|(_, name)| *name == target_table) {
            cleared.insert(source_table.clone());
        }
    }
    Ok(cleared)
}

fn overwrite_clear_prepass_enabled(
    request: &TransferRequest,
    table_count: usize,
    target_db_type: &DatabaseType,
) -> bool {
    request.mode == TransferMode::Overwrite
        // Structure-only transfers use overwrite for selected schema objects
        // (for example MySQL routines), but must never mutate table rows.
        && should_copy_data(&request.content)
        && !request.drop_target_before_create
        && table_count >= 2
        && !is_mongodb_transfer_type(target_db_type)
}

pub(super) fn overwrite_pre_pass_clear_sql(
    table: &str,
    schema: &str,
    db_type: &DatabaseType,
    catalog: Option<&str>,
    referenced: bool,
) -> String {
    if referenced {
        format!("DELETE FROM {}", qualified_table(table, schema, db_type, catalog))
    } else {
        transfer_clear_table_sql(table, schema, db_type, catalog)
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    fn request(content: TransferContent) -> TransferRequest {
        TransferRequest {
            transfer_id: "transfer-test".into(),
            source_connection_id: "source".into(),
            source_database: "source".into(),
            source_schema: "source".into(),
            source_catalog: None,
            target_connection_id: "target".into(),
            target_database: "target".into(),
            target_schema: "target".into(),
            target_catalog: None,
            tables: vec!["parent".into(), "child".into()],
            create_table: true,
            content,
            objects: None,
            mode: TransferMode::Overwrite,
            target_table_name_case: TransferTableNameCase::Preserve,
            target_column_name_case: TransferColumnNameCase::Preserve,
            quote_target_column_names: true,
            ownership_policy: TransferOwnershipPolicy::Preserve,
            batch_size: 1000,
            table_filters: HashMap::new(),
            drop_target_before_create: false,
            drop_target_confirmed: false,
            oracle_packages: None,
        }
    }

    #[test]
    fn referenced_targets_are_deleted_instead_of_truncated() {
        assert_eq!(
            overwrite_pre_pass_clear_sql("QRTZ_JOB_DETAILS", "dbo", &DatabaseType::SqlServer, None, true),
            "DELETE FROM [dbo].[QRTZ_JOB_DETAILS]"
        );
        assert_eq!(
            overwrite_pre_pass_clear_sql("QRTZ_TRIGGERS", "dbo", &DatabaseType::SqlServer, None, false),
            "TRUNCATE TABLE [dbo].[QRTZ_TRIGGERS]"
        );
        assert_eq!(
            overwrite_pre_pass_clear_sql("EVENTS", "APP", &DatabaseType::Db2, None, false),
            "TRUNCATE TABLE \"APP\".\"EVENTS\" IMMEDIATE"
        );
    }

    #[test]
    fn structure_only_overwrite_does_not_clear_table_rows() {
        let structure_request = request(TransferContent::StructureOnly);
        assert!(!overwrite_clear_prepass_enabled(&structure_request, 2, &DatabaseType::Mysql));
        let data_request = request(TransferContent::StructureAndData);
        assert!(overwrite_clear_prepass_enabled(&data_request, 2, &DatabaseType::Mysql));
    }
}