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。

23.2 KB
Raw Blame
use super::column_format::{
    column_data_type, column_extra_clause, has_dameng_identity, is_dameng_identity_compatible_type,
    is_mysql_character_data_type, is_mysql_timestamp_type, mysql_generated_clause,
    mysql_on_update_current_timestamp_clause, strip_inherited_mysql_column_charsets,
};
use super::comments::{build_sqlserver_column_comment_sql_for_profile, build_sqlserver_table_comment_sql_for_profile};
use super::create_dialect::create_table_dialect;
use super::create_options::CreateTableDialectOptions;
use super::dialect::{capabilities_for, database_label, StructureDialect};
use super::foreign_keys::build_foreign_key_sql_for_new_table;
use super::indexes::{build_create_index_statements, mysql_inline_index_definition, normalized_index_type};
use super::mysql_engine::{append_mysql_table_option, validate_mysql_engine};
use super::transwarp;
use super::triggers::{build_trigger_sql_for_new_table, build_trigger_sql_with_operations};
use super::types::{TableStructureSqlOptions, TableStructureSqlPlan, TableStructureSqlResult};
use super::util::{
    clean, format_default_for_sql, normalize_default, qualified_new_table, quote_ident, quote_new_ident, quote_string,
};
use super::validation::{
    validate_columns, validate_concurrent_index_scope, validate_dameng_identity, validate_mysql_literal_defaults,
};
use crate::models::connection::DatabaseType;

fn is_sqlite_integer_family_type(data_type: &str) -> bool {
    let normalized = data_type.trim().to_ascii_lowercase();
    ["int", "integer", "tinyint", "smallint", "mediumint", "bigint"]
        .iter()
        .any(|candidate| normalized == *candidate || normalized.starts_with(&format!("{candidate}(")))
}

pub fn build_create_table_sql(options: TableStructureSqlOptions) -> TableStructureSqlResult {
    build_create_table_sql_with_partition_clause(options, None)
}

/// Optional server context for version-aware dialects; legacy callers retain conservative defaults.
pub fn build_create_table_sql_for_version(
    options: TableStructureSqlOptions,
    server_version: Option<&str>,
) -> TableStructureSqlResult {
    build_create_table_sql_with_dialect_options(options, server_version, CreateTableDialectOptions::default())
}

pub fn build_create_table_sql_with_dialect_options(
    options: TableStructureSqlOptions,
    server_version: Option<&str>,
    dialect_options: CreateTableDialectOptions,
) -> TableStructureSqlResult {
    build_create_table_sql_plan_with_dialect_options(options, server_version, dialect_options).sql
}

pub fn build_create_table_sql_plan_with_dialect_options(
    options: TableStructureSqlOptions,
    server_version: Option<&str>,
    dialect_options: CreateTableDialectOptions,
) -> TableStructureSqlPlan {
    build_create_table_sql_impl(options, None, server_version, dialect_options)
}

/// Shared implementation for plain and partitioned `CREATE TABLE`.
///
/// `partition_clause` is a ready-made `PARTITION BY ...` body (no leading
/// keyword), appended to the `CREATE TABLE` statement. Keeping it as a separate
/// entry point means `build_create_table_sql`'s signature — used from ~160 call
/// sites and integration tests — stays unchanged.
pub(super) fn build_create_table_sql_with_partition_clause(
    options: TableStructureSqlOptions,
    partition_clause: Option<String>,
) -> TableStructureSqlResult {
    build_create_table_sql_impl(options, partition_clause, None, CreateTableDialectOptions::default()).sql
}

fn build_create_table_sql_impl(
    mut options: TableStructureSqlOptions,
    partition_clause: Option<String>,
    server_version: Option<&str>,
    dialect_options: CreateTableDialectOptions,
) -> TableStructureSqlPlan {
    let foreign_clause = match dialect_options.postgres_foreign_table.as_ref() {
        Some(foreign) => match super::postgres_foreign_table::create_clause(&options, foreign) {
            Ok(clause) => Some(clause),
            Err(warnings) => return TableStructureSqlResult { statements: Vec::new(), warnings }.into(),
        },
        None => None,
    };
    let create_dialect = create_table_dialect(&options, server_version, dialect_options);
    let capabilities;
    let dialect;
    if options.is_gaussdb_m_mode {
        capabilities = super::dialect::gaussdb_m_capabilities();
        dialect = StructureDialect::GaussdbM;
    } else {
        capabilities = capabilities_for(options.database_type, options.driver_profile.as_deref());
        dialect = capabilities.dialect;
    }
    // OceanBase callers supply decoded identifiers, including quoted clone targets.
    if options.database_type != Some(DatabaseType::OceanbaseOracle) {
        options.table_name = clean(&options.table_name);
    }
    strip_inherited_mysql_column_charsets(&mut options);
    let ordered_foreign_keys = match super::oracle_foreign_keys::build_new_table_keys(&options) {
        Ok(statements) => statements,
        Err(message) => {
            return TableStructureSqlResult { statements: Vec::new(), warnings: vec![message.into()] }.into()
        }
    };
    let mut warnings = Vec::new();
    warnings.extend(validate_mysql_engine(&options));
    if let Some(value) = &options.mysql_auto_increment_value {
        let counter = crate::db_admin_sql::MysqlAutoIncrementSqlOptions {
            database_type: options.database_type,
            driver_profile: options.driver_profile.clone(),
            schema: options.schema.clone(),
            table_name: options.table_name.clone(),
            value: value.clone(),
        };
        if let Err(error) = crate::db_admin_sql::validate_mysql_auto_increment_options(&counter) {
            warnings.push(error);
        }
        if options.is_gaussdb_m_mode
            || !options.columns.iter().any(|column| {
                !column.marked_for_drop && column.extra.as_ref().is_some_and(|extra| extra.auto_increment == Some(true))
            })
        {
            warnings.push("AUTO_INCREMENT requires an auto-increment column.".to_string());
        }
    }
    // Fail closed: a concurrent-index request on a partitioned parent (or on an
    // existing index in a hand-built draft) is refused up front instead of
    // degrading into blocking index DDL.
    warnings.extend(validate_concurrent_index_scope(&options));
    if options.table_name.is_empty() {
        warnings.push("Table name is required.".to_string());
    }
    let mut active_columns: Vec<_> = options.columns.iter().filter(|column| !column.marked_for_drop).collect();
    if create_dialect.primary_key_columns_first() {
        // Stable partition of references only: never mutate the editor draft or primary-key membership.
        active_columns.sort_by_key(|column| !column.is_primary_key);
    }
    if active_columns.is_empty() {
        warnings.push("At least one column is required.".to_string());
    }
    // MySQL requires the supporting key to exist in the CREATE itself. A later
    // CREATE INDEX cannot rescue a table rejected for ERROR 1075.
    let native_mysql = options.database_type == Some(DatabaseType::Mysql)
        && !options.is_gaussdb_m_mode
        && options
            .driver_profile
            .as_deref()
            .is_none_or(|profile| profile.trim().is_empty() || profile.trim().eq_ignore_ascii_case("mysql"));
    let mut inline_auto_index = None;
    if native_mysql {
        let auto_columns: Vec<_> = active_columns
            .iter()
            .filter(|column| column.extra.as_ref().is_some_and(|extra| extra.auto_increment == Some(true)))
            .collect();
        if auto_columns.len() > 1 {
            warnings.push("MySQL allows only one AUTO_INCREMENT column per table.".to_string());
        } else if let Some(column) = auto_columns.first() {
            // MyISAM also supports a grouped sequence when the auto column is
            // a later key part; InnoDB requires the leading key part.
            let grouped =
                options.mysql_engine.as_deref().is_some_and(|engine| engine.trim().eq_ignore_ascii_case("MyISAM"));
            let primary: Vec<_> = active_columns.iter().filter(|column| column.is_primary_key).collect();
            let primary_supports =
                primary.iter().enumerate().any(|(position, key)| (grouped || position == 0) && key.name == column.name);
            if !primary_supports {
                inline_auto_index = options.indexes.iter().enumerate().find_map(|(position, index)| {
                    if index.marked_for_drop
                        || index.is_primary
                        || !matches!(normalized_index_type(index).as_str(), "" | "BTREE" | "HASH")
                    {
                        return None;
                    }
                    let supports =
                        index.columns.iter().filter(|key| !clean(key).is_empty()).enumerate().any(|(part, key)| {
                            (grouped || part == 0) && clean(key).eq_ignore_ascii_case(&clean(&column.name))
                        });
                    if supports {
                        mysql_inline_index_definition(index).map(|definition| (position, definition))
                    } else {
                        None
                    }
                });
                if inline_auto_index.is_none() {
                    warnings.push(format!(
                        "AUTO_INCREMENT column \"{}\" requires a supporting index (first key column for InnoDB).",
                        column.name
                    ));
                }
            }
        }
    }
    validate_columns(&active_columns, &mut warnings);
    validate_dameng_identity(&options, &active_columns, &mut warnings);
    validate_mysql_literal_defaults(&options, &active_columns, &mut warnings);
    transwarp::validate_create_options(&options, &active_columns, &mut warnings);
    create_dialect.validate(&options, &active_columns, &mut warnings);
    if !warnings.is_empty() {
        return TableStructureSqlResult { statements: Vec::new(), warnings }.into();
    }
    let table = qualified_new_table(options.database_type, dialect, options.schema.as_deref(), &options.table_name);
    if dialect == StructureDialect::Dameng {
        for column in &active_columns {
            if has_dameng_identity(column) && !is_dameng_identity_compatible_type(&column.data_type) {
                warnings.push(format!(
                    "Dameng identity column \"{}\" must use tinyint, smallint, int, integer, bigint, number, numeric, or decimal/dec with scale 0.",
                    column.name
                ));
            }
        }
        if !warnings.is_empty() {
            return TableStructureSqlResult { statements: Vec::new(), warnings }.into();
        }
    }
    if dialect == StructureDialect::Sqlite {
        let primary_key_count = active_columns.iter().filter(|column| column.is_primary_key).count();
        for column in &active_columns {
            if !column.is_primary_key || !column.extra.as_ref().is_some_and(|e| e.auto_increment.unwrap_or(false)) {
                continue;
            }
            if primary_key_count != 1 {
                warnings.push(
                    "SQLite AUTOINCREMENT requires a single INTEGER PRIMARY KEY column; disable auto-increment on composite primary keys.".to_string(),
                );
            } else if !is_sqlite_integer_family_type(&column.data_type) {
                warnings.push(format!(
                    "SQLite auto-increment column \"{}\" must use an integer type (normalized to INTEGER).",
                    column.name
                ));
            }
        }
        if !warnings.is_empty() {
            return TableStructureSqlResult { statements: Vec::new(), warnings }.into();
        }
    }
    let mut statements = Vec::new();
    let mut column_definitions = Vec::new();

    for column in &active_columns {
        if transwarp::is_partition_column(&options, column) {
            continue;
        }
        let mut data_type = column_data_type(dialect, column, options.database_type);
        // SQLite accepts AUTOINCREMENT only on an exact INTEGER PRIMARY KEY,
        // so integer-family aliases are normalized when auto-increment is on.
        if dialect == StructureDialect::Sqlite
            && column.is_primary_key
            && column.extra.as_ref().is_some_and(|e| e.auto_increment.unwrap_or(false))
        {
            data_type = "INTEGER".to_string();
        }
        let mut parts = vec![quote_new_ident(options.database_type, dialect, &column.name), data_type];
        if options.database_type == Some(DatabaseType::Mysql)
            && create_dialect.supports_column_charset()
            && is_mysql_character_data_type(&column.data_type)
        {
            if !column.character_set.trim().is_empty() {
                parts.push(format!("CHARACTER SET {}", quote_ident(dialect, &column.character_set)));
            }
            if !column.collation.trim().is_empty() {
                parts.push(format!("COLLATE {}", quote_ident(dialect, &column.collation)));
            }
        }
        let generated_clause = (dialect == StructureDialect::Mysql).then(|| mysql_generated_clause(column)).flatten();
        if let Some(clause) = generated_clause.as_ref() {
            parts.push(clause.clone());
        }
        // Oracle's column grammar is `col type [DEFAULT expr] [inline constraint ...]`, so the
        // DEFAULT clause has to precede NOT NULL. Emitting `NOT NULL DEFAULT ...` leaves the
        // parser looking for the closing parenthesis of the column list and fails with
        // ORA-00907 (t8y2/dbx#9477). Scoped to Oracle: every other engine keeps the historical
        // NOT NULL-then-DEFAULT order.
        let default_value = normalize_default(Some(&column.default_value));
        // Generated columns cannot carry a DEFAULT in MySQL.
        let default_clause =
            (!default_value.is_empty() && dialect != StructureDialect::ManticoreSearch && generated_clause.is_none())
                .then(|| format!("DEFAULT {}", format_default_for_sql(dialect, &column.data_type, &default_value)));
        if dialect == StructureDialect::Oracle {
            if let Some(clause) = default_clause.clone() {
                parts.push(clause);
            }
        }
        if dialect == StructureDialect::Sqlite
            && column.is_primary_key
            && column.extra.as_ref().is_some_and(|e| e.auto_increment.unwrap_or(false))
        {
            parts.push("PRIMARY KEY".to_string());
        } else if (column.is_primary_key && create_dialect.explicit_primary_key_not_null())
            || (!column.is_nullable
                && !column.is_primary_key
                && !matches!(dialect, StructureDialect::ClickHouse | StructureDialect::ManticoreSearch))
        {
            parts.push("NOT NULL".to_string());
        } else if column.is_nullable
            && !column.is_primary_key
            && dialect == StructureDialect::Mysql
            && is_mysql_timestamp_type(&column.data_type)
        {
            parts.push("NULL".to_string());
        }
        if generated_clause.is_none() {
            if let Some(extra_clause) = column_extra_clause(dialect, column) {
                parts.push(extra_clause);
            }
        }
        if dialect != StructureDialect::Oracle {
            if let Some(clause) = default_clause {
                parts.push(clause);
            }
        }
        if generated_clause.is_none() {
            if let Some(on_update) = column.extra.as_ref().and_then(|e| e.on_update_current_timestamp).filter(|v| *v) {
                if on_update && dialect == StructureDialect::Mysql {
                    parts.push(mysql_on_update_current_timestamp_clause(&column.data_type));
                }
            }
        }
        if dialect == StructureDialect::Mysql && capabilities.comment && !clean(&column.comment).is_empty() {
            parts.push(format!("COMMENT {}", quote_string(&clean(&column.comment))));
        }
        column_definitions.push(parts.join(" "));
    }

    let pk_columns: Vec<_> = active_columns
        .iter()
        .filter(|column| {
            column.is_primary_key
                && dialect != StructureDialect::ManticoreSearch
                && !(dialect == StructureDialect::Sqlite
                    && column.extra.as_ref().is_some_and(|e| e.auto_increment.unwrap_or(false)))
        })
        .collect();
    let pk_list = pk_columns
        .iter()
        .map(|column| quote_new_ident(options.database_type, dialect, &column.name))
        .collect::<Vec<_>>()
        .join(", ");
    if !pk_list.is_empty() && create_dialect.primary_key_in_column_list() {
        // Optional user-supplied constraint name; empty keeps the anonymous form.
        let constraint = super::columns::new_primary_key_constraint_clause(&options, dialect);
        column_definitions.push(format!("{constraint}PRIMARY KEY ({pk_list})"));
    }

    if let Some((_, definition)) = &inline_auto_index {
        column_definitions.push(definition.clone());
    }

    let is_foreign_table = foreign_clause.is_some();
    let kind = if is_foreign_table { "FOREIGN TABLE" } else { "TABLE" };
    let mut create_table = format!("CREATE {kind} {table} (\n  {}\n)", column_definitions.join(",\n  "));
    create_table.push_str(&create_dialect.after_columns(&options, &pk_list));
    create_table.push_str(&transwarp::create_table_suffix(&options, &active_columns, dialect));
    let mut create_table = match partition_clause {
        Some(clause) => format!("{create_table} {clause}"),
        None => create_table,
    };
    create_table.push_str(&create_dialect.partition_clause(&active_columns));
    create_table.push_str(&create_dialect.after_partition(&active_columns, &pk_list));
    create_table.push_str(&create_dialect.sort_clause(&active_columns));
    if let Some(clause) = foreign_clause {
        create_table.push_str(&clause);
    }
    statements.push(format!("{create_table};"));

    if let Some(engine) = options.mysql_engine.as_deref().map(str::trim).filter(|engine| !engine.is_empty()) {
        if let Some(statement) = statements.last_mut() {
            append_mysql_table_option(statement, &format!("ENGINE = {engine}"));
        }
    }

    if let Some(value) = &options.mysql_auto_increment_value {
        if let Some(statement) = statements.last_mut() {
            append_mysql_table_option(statement, &format!("AUTO_INCREMENT = {value}"));
        }
    }

    if capabilities.comment {
        let table_comment = clean(options.table_comment.as_deref().unwrap_or(""));
        if !table_comment.is_empty()
            && options.database_type != Some(DatabaseType::Transwarp)
            && !create_dialect.handles_table_comment()
        {
            if matches!(dialect, StructureDialect::Mysql | StructureDialect::GaussdbM) {
                if let Some(last) = statements.last_mut() {
                    append_mysql_table_option(last, &format!("COMMENT = {}", quote_string(&table_comment)));
                }
            } else if matches!(
                dialect,
                StructureDialect::Postgres
                    | StructureDialect::Oracle
                    | StructureDialect::Dameng
                    | StructureDialect::Oscar
                    | StructureDialect::H2
                    | StructureDialect::SapHana
            ) {
                let comment_kind =
                    if dialect == StructureDialect::Postgres && is_foreign_table { "FOREIGN TABLE" } else { "TABLE" };
                statements.push(format!("COMMENT ON {comment_kind} {table} IS {};", quote_string(&table_comment)));
            } else if dialect == StructureDialect::ClickHouse {
                statements.push(format!("ALTER TABLE {table} MODIFY COMMENT {};", quote_string(&table_comment)));
            } else if dialect == StructureDialect::SqlServer {
                statements.extend(build_sqlserver_table_comment_sql_for_profile(
                    &table,
                    options.schema.as_deref(),
                    &options.table_name,
                    &table_comment,
                    options.driver_profile.as_deref(),
                ));
            }
        }
    }

    if capabilities.comment
        && matches!(
            dialect,
            StructureDialect::Postgres
                | StructureDialect::Oracle
                | StructureDialect::Dameng
                | StructureDialect::Oscar
                | StructureDialect::H2
                | StructureDialect::SapHana
        )
    {
        for column in &active_columns {
            if !clean(&column.comment).is_empty() {
                statements.push(format!(
                    "COMMENT ON COLUMN {table}.{} IS {};",
                    quote_new_ident(options.database_type, dialect, &column.name),
                    quote_string(&clean(&column.comment))
                ));
            }
        }
    }
    if capabilities.comment && dialect == StructureDialect::ClickHouse {
        for column in &active_columns {
            if !clean(&column.comment).is_empty() {
                statements.push(format!(
                    "ALTER TABLE {table} COMMENT COLUMN {} {};",
                    quote_new_ident(options.database_type, dialect, &column.name),
                    quote_string(&clean(&column.comment))
                ));
            }
        }
    }
    if capabilities.comment && dialect == StructureDialect::SqlServer {
        for column in &active_columns {
            if !clean(&column.comment).is_empty() {
                statements.extend(build_sqlserver_column_comment_sql_for_profile(
                    &table,
                    options.schema.as_deref(),
                    &options.table_name,
                    &column.name,
                    &column.comment,
                    options.driver_profile.as_deref(),
                ));
            }
        }
    }

    for (position, index) in
        options.indexes.iter().enumerate().filter(|(_, index)| !index.marked_for_drop && !index.is_primary)
    {
        if inline_auto_index.as_ref().is_some_and(|(inline_position, _)| *inline_position == position) {
            continue;
        }
        if !capabilities.create_index {
            warnings.push(format!(
                "Creating indexes is not supported for {} from this editor.",
                database_label(options.database_type)
            ));
            continue;
        }
        statements.extend(build_create_index_statements(
            options.database_type,
            dialect,
            &table,
            index,
            &mut warnings,
            options.schema.as_deref(),
            &options.table_name,
            false,
            capabilities.index_concurrent,
            true,
            options.driver_profile.as_deref(),
        ));
    }

    if options.oracle_foreign_key_changes.is_some() {
        statements.extend(ordered_foreign_keys);
    } else {
        statements.extend(build_foreign_key_sql_for_new_table(&options, &mut warnings));
    }
    let mut trigger_operations = Vec::new();
    let oracle_trigger_contract = options.triggers.iter().any(|trigger| trigger.oracle_exact.is_some()).then_some(1);
    let trigger_sql = if oracle_trigger_contract.is_some() {
        build_trigger_sql_with_operations(
            &options,
            &mut warnings,
            true,
            statements.len(),
            Some(&mut trigger_operations),
        )
    } else {
        build_trigger_sql_for_new_table(&options, &mut warnings)
    };
    statements.extend(trigger_sql);

    TableStructureSqlPlan {
        sql: TableStructureSqlResult { statements, warnings },
        trigger_operations,
        oracle_trigger_contract,
    }
}