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。

9.3 KB
Raw Blame
use dbx_sql_schema::table_structure_sql::{
    build_create_table_sql, build_table_structure_change_sql, TableStructureSqlOptions,
};
use serde_json::{json, Value};

fn definition(name: &str) -> Value {
    json!({
        "name": name, "columns": ["B", "A,quoted"],
        "refSchema": "Parent Owner", "refTable": "Parent,Table", "refColumns": ["V", "ID\"quoted"],
        "onDelete": "CASCADE",
        "state": { "enabled": true, "validated": false, "deferrable": false, "initiallyDeferred": false, "rely": false }
    })
}

fn options(engine: &str, original: Value, target: Value) -> TableStructureSqlOptions {
    serde_json::from_value(json!({
        "databaseType": engine, "schema": "App Owner", "tableName": "Child,Table",
        "columns": [], "indexes": [],
        "oracleForeignKeyChanges": [{ "original": original, "target": target }]
    }))
    .unwrap()
}

#[test]
fn ordered_quoted_composite_add_reaches_real_builder() {
    for engine in ["oracle", "oceanbase-oracle"] {
        let result = build_table_structure_change_sql(options(engine, Value::Null, definition("FK\"name")));
        assert!(result.warnings.is_empty(), "{:?}", result.warnings);
        let deferred = if engine == "oracle" { " NOT DEFERRABLE INITIALLY IMMEDIATE" } else { "" };
        assert_eq!(
            result.statements,
            vec![format!(
                "ALTER TABLE \"App Owner\".\"Child,Table\" ADD CONSTRAINT \"FK\"\"name\" FOREIGN KEY (\"B\", \"A,quoted\") REFERENCES \"Parent Owner\".\"Parent,Table\" (\"V\", \"ID\"\"quoted\") ON DELETE CASCADE{deferred} NORELY ENABLE NOVALIDATE;"
            )],
            "{engine}"
        );
    }
}

#[test]
fn invalid_replacement_never_returns_original_drop() {
    let mut invalid = definition("FK_NEW");
    invalid["refColumns"] = json!(["V"]);
    let result = build_table_structure_change_sql(options("oracle", definition("FK_OLD"), invalid));
    assert!(result.statements.is_empty());
    assert!(!result.warnings.is_empty());
}

#[test]
fn unsupported_engine_cannot_consume_oracle_changes() {
    let result = build_table_structure_change_sql(options("postgres", Value::Null, definition("FK_NEW")));
    assert!(result.statements.is_empty());
    assert!(!result.warnings.is_empty());
}

#[test]
fn oracle_name_and_state_edit_preserves_existing_key() {
    let original = definition("FK_OLD");
    let mut target = definition("FK_NEW");
    target["state"]["enabled"] = json!(false);
    let result = build_table_structure_change_sql(options("oracle", original, target));
    assert!(result.warnings.is_empty());
    assert_eq!(
        result.statements,
        vec![
            "ALTER TABLE \"App Owner\".\"Child,Table\" DISABLE NOVALIDATE CONSTRAINT \"FK_OLD\";",
            "ALTER TABLE \"App Owner\".\"Child,Table\" RENAME CONSTRAINT \"FK_OLD\" TO \"FK_NEW\";"
        ]
    );
}

#[test]
fn oceanbase_name_edit_rebuilds_but_state_edit_is_in_place() {
    let original = definition("FK_OLD");
    let mut target = original.clone();
    target["state"]["enabled"] = json!(false);
    let result = build_table_structure_change_sql(options("oceanbase-oracle", original.clone(), target));
    assert_eq!(
        result.statements,
        vec!["ALTER TABLE \"App Owner\".\"Child,Table\" DISABLE NOVALIDATE CONSTRAINT \"FK_OLD\";"]
    );
    let renamed = build_table_structure_change_sql(options("oceanbase-oracle", original, definition("FK_NEW")));
    assert!(renamed.warnings.is_empty());
    assert_eq!(renamed.statements.len(), 2);
    assert_eq!(renamed.statements[0], "ALTER TABLE \"App Owner\".\"Child,Table\" DROP CONSTRAINT \"FK_OLD\";");
    assert!(renamed.statements[1].contains("ADD CONSTRAINT \"FK_NEW\""));
}

#[test]
fn duplicate_columns_and_illegal_actions_are_rejected_before_drop() {
    for (field, value) in [("columns", json!(["B", "B"])), ("onDelete", json!("RESTRICT"))] {
        let mut target = definition("FK_NEW");
        target[field] = value;
        let result = build_table_structure_change_sql(options("oracle", definition("FK_OLD"), target));
        assert!(result.statements.is_empty());
        assert!(!result.warnings.is_empty());
    }
}

fn new_table_options(engine: &str) -> TableStructureSqlOptions {
    serde_json::from_value(json!({
        "databaseType": engine, "schema": "APP", "tableName": "child",
        "columns": [{"id":"id", "name":"key_id", "dataType":"NUMBER", "isNullable":false, "isPrimaryKey":true},
                    {"id":"pid", "name":"parent_id", "dataType":"NUMBER", "isNullable":true}],
        "oracleForeignKeyChanges": [{"original":null,"target":{
            "name":"fk_self", "columns":["parent_id"], "refSchema":"APP", "refTable":"child", "refColumns":["key_id"],
            "onDelete":"NO ACTION", "state":{"enabled":true,"validated":true,"deferrable":false,"initiallyDeferred":false,"rely":false}
        }}]
    })).unwrap()
}

#[test]
fn new_table_foreign_key_uses_existing_create_identity_rules() {
    for engine in ["oracle", "oceanbase-oracle"] {
        let result = build_create_table_sql(new_table_options(engine));
        assert!(result.warnings.is_empty(), "{:?}", result.warnings);
        assert_eq!(result.statements.len(), 2, "{engine}: {:?}", result.statements);
        if engine == "oracle" {
            assert!(result.statements[0].contains("CREATE TABLE \"APP\".child"));
            assert!(result.statements[1].contains("ALTER TABLE \"APP\".child ADD CONSTRAINT fk_self FOREIGN KEY (parent_id) REFERENCES \"APP\".child (key_id)"), "{:?}", result.statements);
        } else {
            assert!(result.statements[1].contains("ALTER TABLE \"APP\".\"child\" ADD CONSTRAINT \"fk_self\" FOREIGN KEY (\"parent_id\") REFERENCES \"APP\".\"child\" (\"key_id\")"));
        }
    }
}

#[test]
fn self_reference_preserves_reverse_composite_parent_key_order() {
    let options = serde_json::from_value::<TableStructureSqlOptions>(json!({
        "databaseType": "oracle", "schema": "APP", "tableName": "child",
        "columns": [
            {"id":"a", "name":"A", "dataType":"NUMBER", "isNullable":false, "isPrimaryKey":true},
            {"id":"b", "name":"B", "dataType":"NUMBER", "isNullable":false, "isPrimaryKey":true},
            {"id":"x", "name":"X", "dataType":"NUMBER", "isNullable":true},
            {"id":"y", "name":"Y", "dataType":"NUMBER", "isNullable":true}
        ],
        "oracleForeignKeyChanges": [{"original":null,"target":{
            "name":"FK_SELF", "columns":["X","Y"], "refSchema":"APP", "refTable":"child", "refColumns":["B","A"],
            "onDelete":"NO ACTION", "state":{"enabled":true,"validated":true,"deferrable":false,"initiallyDeferred":false,"rely":false}
        }}]
    })).unwrap();
    let result = build_create_table_sql(options.clone());
    assert!(result.warnings.is_empty(), "{:?}", result.warnings);
    assert!(result.statements[1].contains("FOREIGN KEY (X, Y) REFERENCES \"APP\".child (B, A)"));
}

#[test]
fn invalid_new_foreign_key_returns_zero_create_statements() {
    let mut options = new_table_options("oracle");
    options.oracle_foreign_key_changes.as_mut().unwrap()[0].target.as_mut().unwrap().columns = vec!["missing".into()];
    let result = build_create_table_sql(options);
    assert!(result.statements.is_empty());
    assert!(!result.warnings.is_empty());
}

#[test]
fn self_reference_must_target_the_new_primary_key_before_create() {
    let mut options = new_table_options("oracle");
    options.oracle_foreign_key_changes.as_mut().unwrap()[0].target.as_mut().unwrap().ref_columns =
        vec!["parent_id".into()];
    let result = build_create_table_sql(options);
    assert!(result.statements.is_empty());
    assert!(!result.warnings.is_empty());
}

#[test]
fn oracle_self_reference_uses_unquoted_identifier_folding() {
    let mut options = new_table_options("oracle");
    options.oracle_foreign_key_changes.as_mut().unwrap()[0].target.as_mut().unwrap().ref_table = "CHILD".into();
    let result = build_create_table_sql(options);
    assert!(result.warnings.is_empty(), "{:?}", result.warnings);
    assert!(result.statements[1].contains("REFERENCES \"APP\".child (key_id)"));
}

#[test]
fn new_foreign_keys_preserve_special_source_and_existing_parent_names() {
    let mut options = new_table_options("oracle");
    options.table_name = "Child,Quoted".into();
    options.columns[1].name = " parent\"column ".into();
    let target = options.oracle_foreign_key_changes.as_mut().unwrap()[0].target.as_mut().unwrap();
    target.name = "FK,Quoted".into();
    target.columns = vec![" parent\"column ".into()];
    target.ref_schema = "Parent Owner".into();
    target.ref_table = "MixedCaseParent".into();
    target.ref_columns = vec![" Key,One ".into()];
    let result = build_create_table_sql(options);
    assert!(result.warnings.is_empty(), "{:?}", result.warnings);
    assert!(result.statements[1].contains("ALTER TABLE \"APP\".\"Child,Quoted\" ADD CONSTRAINT \"FK,Quoted\" FOREIGN KEY (\" parent\"\"column \") REFERENCES \"Parent Owner\".\"MixedCaseParent\" (\" Key,One \")"), "{:?}", result.statements);
}

#[test]
fn ordinary_oracle_new_constraint_names_cannot_collide_after_folding() {
    let mut options = new_table_options("oracle");
    let mut second = options.oracle_foreign_key_changes.as_ref().unwrap()[0].clone();
    second.target.as_mut().unwrap().name = "FK_SELF".into();
    options.oracle_foreign_key_changes.as_mut().unwrap().push(second);
    let result = build_create_table_sql(options);
    assert!(result.statements.is_empty());
    assert!(!result.warnings.is_empty());
}