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。

16.9 KB
Raw Blame
use super::dialect::{capabilities_for, database_label, dialect_label, StructureDialect};
use super::types::TableStructureSqlOptions;
use super::util::{clean, qualified_table, quote_string};
use crate::models::connection::DatabaseType;

pub(super) fn build_table_comment_sql(options: &TableStructureSqlOptions, warnings: &mut Vec<String>) -> Vec<String> {
    let capabilities = capabilities_for(options.database_type, options.driver_profile.as_deref());
    let new_comment = options.table_comment.as_deref().unwrap_or("");
    let original_comment = options.original_table_comment.as_deref().unwrap_or("");
    if clean(new_comment) == clean(original_comment) {
        return Vec::new();
    }
    if !capabilities.comment {
        warnings.push(format!(
            "Table comments are not supported for {} from this editor; the comment change was ignored.",
            database_label(options.database_type)
        ));
        return Vec::new();
    }
    let dialect = capabilities.dialect;
    let table = qualified_table(dialect, options.schema.as_deref(), &options.table_name);
    let quoted = quote_string(&clean(new_comment));
    if options.database_type == Some(DatabaseType::Transwarp) {
        return vec![format!("ALTER TABLE {table} SET TBLPROPERTIES ('comment' = {quoted});")];
    }
    match dialect {
        StructureDialect::Mysql | StructureDialect::GaussdbM => {
            vec![format!("ALTER TABLE {table} COMMENT = {quoted};")]
        }
        StructureDialect::Postgres if options.foreign_table => {
            // PostgreSQL foreign tables (relkind = 'f') reject
            // `COMMENT ON TABLE` ("<name>" is not a table) and require the
            // FOREIGN form instead.
            vec![format!("COMMENT ON FOREIGN TABLE {table} IS {quoted};")]
        }
        StructureDialect::Postgres
        | StructureDialect::Oracle
        | StructureDialect::Dameng
        | StructureDialect::Oscar
        | StructureDialect::H2
        | StructureDialect::SapHana => {
            vec![format!("COMMENT ON TABLE {table} IS {quoted};")]
        }
        StructureDialect::ClickHouse => {
            vec![format!("ALTER TABLE {table} MODIFY COMMENT {quoted};")]
        }
        StructureDialect::SqlServer => build_sqlserver_table_comment_sql_for_profile(
            &table,
            options.schema.as_deref(),
            &options.table_name,
            new_comment,
            options.driver_profile.as_deref(),
        ),
        _ => {
            if !clean(new_comment).is_empty() {
                warnings
                    .push(format!("Table comments are not supported for {} from this editor.", dialect_label(dialect)));
            }
            Vec::new()
        }
    }
}

pub(super) fn sqlserver_schema_name(schema: Option<&str>) -> String {
    schema.filter(|s| !s.trim().is_empty()).map(|s| s.trim().to_string()).unwrap_or_else(|| "dbo".to_string())
}

fn build_sqlserver_extended_property_comment_sql(
    exists: &str,
    levels: &str,
    new_comment: &str,
    procedure_prefix: &str,
) -> Vec<String> {
    let new_comment = clean(new_comment);
    if new_comment.is_empty() {
        return vec![format!(
            "IF {exists} EXEC {procedure_prefix}sp_dropextendedproperty @name=N'MS_Description', {levels};"
        )];
    }

    let escaped_comment = new_comment.replace('\'', "''");
    vec![format!(
        "IF {exists} EXEC {procedure_prefix}sp_updateextendedproperty @name=N'MS_Description', @value=N'{escaped_comment}', {levels} ELSE EXEC {procedure_prefix}sp_addextendedproperty @name=N'MS_Description', @value=N'{escaped_comment}', {levels};"
    )]
}

pub fn build_sqlserver_table_comment_sql(
    qualified_table: &str,
    schema: Option<&str>,
    table_name: &str,
    new_comment: &str,
) -> Vec<String> {
    build_sqlserver_table_comment_sql_for_profile(qualified_table, schema, table_name, new_comment, None)
}

pub fn build_sqlserver_table_comment_sql_for_profile(
    qualified_table: &str,
    schema: Option<&str>,
    table_name: &str,
    new_comment: &str,
    driver_profile: Option<&str>,
) -> Vec<String> {
    let schema_name = sqlserver_schema_name(schema);
    let escaped_qualified = qualified_table.replace('\'', "''");
    let escaped_schema = schema_name.replace('\'', "''");
    let escaped_table = table_name.replace('\'', "''");
    let modern = build_sqlserver_extended_property_comment_sql(
        &format!(
            "EXISTS (SELECT 1 FROM sys.extended_properties AS ep WHERE ep.class = 1 AND ep.major_id = OBJECT_ID(N'{escaped_qualified}') AND ep.minor_id = 0 AND ep.name = N'MS_Description')"
        ),
        &format!(
            "@level0type=N'SCHEMA', @level0name=N'{escaped_schema}', @level1type=N'TABLE', @level1name=N'{escaped_table}'"
        ),
        new_comment,
        "sys.",
    );
    if !is_sqlserver_legacy_profile(driver_profile) {
        return modern;
    }
    let sql2000 = build_sqlserver_extended_property_comment_sql(
        &format!(
            "EXISTS (SELECT 1 FROM ::fn_listextendedproperty(N'MS_Description', N'USER', N'{escaped_schema}', N'TABLE', N'{escaped_table}', NULL, NULL))"
        ),
        &format!(
            "@level0type=N'USER', @level0name=N'{escaped_schema}', @level1type=N'TABLE', @level1name=N'{escaped_table}'"
        ),
        new_comment,
        "",
    );
    sqlserver_version_switched_comment_sql(modern, sql2000)
}

pub(super) fn build_sqlserver_index_comment_sql_for_profile(
    qualified_table: &str,
    schema: Option<&str>,
    table_name: &str,
    index_name: &str,
    new_comment: &str,
    driver_profile: Option<&str>,
) -> Vec<String> {
    let schema_name = sqlserver_schema_name(schema);
    let escaped_qualified = qualified_table.replace('\'', "''");
    let escaped_schema = schema_name.replace('\'', "''");
    let escaped_table = table_name.replace('\'', "''");
    let escaped_idx = index_name.replace('\'', "''");
    let modern = build_sqlserver_extended_property_comment_sql(
        &format!(
            "EXISTS (SELECT 1 FROM sys.extended_properties AS ep INNER JOIN sys.indexes AS i ON i.object_id = ep.major_id AND i.index_id = ep.minor_id WHERE ep.class = 7 AND ep.major_id = OBJECT_ID(N'{escaped_qualified}') AND i.name = N'{escaped_idx}' AND ep.name = N'MS_Description')"
        ),
        &format!(
            "@level0type=N'SCHEMA', @level0name=N'{escaped_schema}', @level1type=N'TABLE', @level1name=N'{escaped_table}', @level2type=N'INDEX', @level2name=N'{escaped_idx}'"
        ),
        new_comment,
        "sys.",
    );
    if !is_sqlserver_legacy_profile(driver_profile) {
        return modern;
    }
    let sql2000 = build_sqlserver_extended_property_comment_sql(
        &format!(
            "EXISTS (SELECT 1 FROM ::fn_listextendedproperty(N'MS_Description', N'USER', N'{escaped_schema}', N'TABLE', N'{escaped_table}', N'INDEX', N'{escaped_idx}'))"
        ),
        &format!(
            "@level0type=N'USER', @level0name=N'{escaped_schema}', @level1type=N'TABLE', @level1name=N'{escaped_table}', @level2type=N'INDEX', @level2name=N'{escaped_idx}'"
        ),
        new_comment,
        "",
    );
    sqlserver_version_switched_comment_sql(modern, sql2000)
}

pub fn build_sqlserver_column_comment_sql(
    qualified_table: &str,
    schema: Option<&str>,
    table_name: &str,
    column_name: &str,
    new_comment: &str,
) -> Vec<String> {
    build_sqlserver_column_comment_sql_for_profile(qualified_table, schema, table_name, column_name, new_comment, None)
}

pub fn build_sqlserver_column_comment_sql_for_profile(
    qualified_table: &str,
    schema: Option<&str>,
    table_name: &str,
    column_name: &str,
    new_comment: &str,
    driver_profile: Option<&str>,
) -> Vec<String> {
    let schema_name = sqlserver_schema_name(schema);
    let escaped_qualified = qualified_table.replace('\'', "''");
    let escaped_schema = schema_name.replace('\'', "''");
    let escaped_table = table_name.replace('\'', "''");
    let escaped_col = column_name.replace('\'', "''");
    let modern = build_sqlserver_extended_property_comment_sql(
        &format!(
            "EXISTS (SELECT 1 FROM sys.extended_properties AS ep WHERE ep.class = 1 AND ep.major_id = OBJECT_ID(N'{escaped_qualified}') AND ep.minor_id = COLUMNPROPERTY(OBJECT_ID(N'{escaped_qualified}'), N'{escaped_col}', 'ColumnId') AND ep.name = N'MS_Description')"
        ),
        &format!(
            "@level0type=N'SCHEMA', @level0name=N'{escaped_schema}', @level1type=N'TABLE', @level1name=N'{escaped_table}', @level2type=N'COLUMN', @level2name=N'{escaped_col}'"
        ),
        new_comment,
        "sys.",
    );
    if !is_sqlserver_legacy_profile(driver_profile) {
        return modern;
    }
    let sql2000 = build_sqlserver_extended_property_comment_sql(
        &format!(
            "EXISTS (SELECT 1 FROM ::fn_listextendedproperty(N'MS_Description', N'USER', N'{escaped_schema}', N'TABLE', N'{escaped_table}', N'COLUMN', N'{escaped_col}'))"
        ),
        &format!(
            "@level0type=N'USER', @level0name=N'{escaped_schema}', @level1type=N'TABLE', @level1name=N'{escaped_table}', @level2type=N'COLUMN', @level2name=N'{escaped_col}'"
        ),
        new_comment,
        "",
    );
    sqlserver_version_switched_comment_sql(modern, sql2000)
}

/// The legacy driver serves SQL Server 2000 through current releases. Since
/// SQL Server 2005, the `USER` level only resolves when a database user shares
/// the schema name, so non-`dbo` schemas fail with Msg 15135. Choose the level
/// on the server instead, and keep each branch in dynamic SQL so SQL Server
/// 2000 never compiles the `sys.` catalog references.
fn sqlserver_version_switched_comment_sql(modern: Vec<String>, sql2000: Vec<String>) -> Vec<String> {
    let dynamic = |statements: Vec<String>| {
        let sql = statements.concat();
        format!("EXEC (N'{}')", sql.trim_end_matches(';').replace('\'', "''"))
    };
    vec![format!("IF @@MICROSOFTVERSION / 16777216 >= 9 {} ELSE {};", dynamic(modern), dynamic(sql2000))]
}

fn is_sqlserver_legacy_profile(driver_profile: Option<&str>) -> bool {
    driver_profile.is_some_and(|profile| profile.trim().eq_ignore_ascii_case("sqlserver-legacy"))
}

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

    fn pg_comment_options(foreign_table: bool) -> TableStructureSqlOptions {
        TableStructureSqlOptions {
            database_type: Some(DatabaseType::Postgres),
            driver_profile: None,
            schema: Some("de_sk".to_string()),
            table_name: "vmes_base_destination".to_string(),
            columns: Vec::new(),
            indexes: Vec::new(),
            foreign_keys: Vec::new(),
            triggers: Vec::new(),
            table_comment: Some("港口资料".to_string()),
            original_table_comment: None,
            mysql_engine: None,
            mysql_auto_increment_value: None,
            transwarp_create: None,
            partitioned: false,
            is_gaussdb_m_mode: false,
            table_collation: None,
            primary_key_name: None,
            primary_key_change: None,
            oracle_foreign_key_changes: None,
            oracle_constraint_changes: None,
            foreign_table,
        }
    }

    #[test]
    fn postgres_foreign_table_comment_uses_foreign_table_form() {
        let mut warnings = Vec::new();
        let statements = build_table_comment_sql(&pg_comment_options(true), &mut warnings);

        assert_eq!(statements, vec!["COMMENT ON FOREIGN TABLE \"de_sk\".\"vmes_base_destination\" IS '港口资料';"]);
        assert!(warnings.is_empty(), "no warnings expected: {warnings:?}");
    }

    #[test]
    fn postgres_regular_table_comment_keeps_table_form() {
        let mut warnings = Vec::new();
        let statements = build_table_comment_sql(&pg_comment_options(false), &mut warnings);

        assert_eq!(statements, vec!["COMMENT ON TABLE \"de_sk\".\"vmes_base_destination\" IS '港口资料';"]);
    }

    #[test]
    fn foreign_table_flag_is_ignored_by_non_postgres_dialects() {
        let mut options = pg_comment_options(true);
        options.database_type = Some(DatabaseType::Oracle);
        let mut warnings = Vec::new();
        let statements = build_table_comment_sql(&options, &mut warnings);

        assert_eq!(statements, vec!["COMMENT ON TABLE \"de_sk\".\"vmes_base_destination\" IS '港口资料';"]);
    }

    #[test]
    fn sqlserver_table_comment_updates_or_adds_without_dropping() {
        let statements = build_sqlserver_table_comment_sql("[app's].[user's]", Some("app's"), "user's", "owner's 新值");

        assert_eq!(statements.len(), 1);
        let sql = &statements[0];
        assert!(sql.contains("ep.class = 1"), "table extended-property class: {sql}");
        assert!(sql.contains("sys.sp_updateextendedproperty"), "update existing comment: {sql}");
        assert!(sql.contains("ELSE EXEC sys.sp_addextendedproperty"), "add missing comment: {sql}");
        assert!(!sql.contains("sys.sp_dropextendedproperty"), "non-empty comments are not dropped first: {sql}");
        assert!(sql.contains("OBJECT_ID(N'[app''s].[user''s]')"), "object name escaping: {sql}");
        assert!(sql.contains("@value=N'owner''s 新值'"), "Unicode comment escaping: {sql}");
        assert_eq!(
            crate::sql::split_sql_statements_for_database(sql, crate::models::connection::DatabaseType::SqlServer)
                .len(),
            1,
            "IF/ELSE comment upsert must remain one executable statement: {sql}"
        );
    }

    #[test]
    fn sqlserver_empty_column_comment_drops_only_when_present() {
        let statements = build_sqlserver_column_comment_sql("[dbo].[orders]", None, "orders", "owner'id", "  ");

        assert_eq!(statements.len(), 1);
        let sql = &statements[0];
        assert!(sql.contains("COLUMNPROPERTY(OBJECT_ID(N'[dbo].[orders]'), N'owner''id', 'ColumnId')"));
        assert!(sql.contains("sys.sp_dropextendedproperty"), "drop existing comment: {sql}");
        assert!(!sql.contains("sys.sp_updateextendedproperty"), "empty comment must not update: {sql}");
        assert!(!sql.contains("sys.sp_addextendedproperty"), "empty comment must not add: {sql}");
        assert!(sql.contains("@level0name=N'dbo'"), "default schema: {sql}");
        assert_eq!(
            crate::sql::split_sql_statements_for_database(sql, crate::models::connection::DatabaseType::SqlServer)
                .len(),
            1,
            "conditional comment drop must remain one executable statement: {sql}"
        );
    }

    #[test]
    fn sqlserver_index_comment_uses_index_extended_property_identity() {
        let statements = build_sqlserver_index_comment_sql_for_profile(
            "[dbo].[orders]",
            None,
            "orders",
            "ix_owner's",
            "index comment",
            None,
        );

        assert_eq!(statements.len(), 1);
        let sql = &statements[0];
        assert!(sql.contains("INNER JOIN sys.indexes AS i"), "index lookup: {sql}");
        assert!(sql.contains("ep.class = 7"), "index extended-property class: {sql}");
        assert!(sql.contains("i.name = N'ix_owner''s'"), "index name escaping: {sql}");
        assert!(sql.contains("sys.sp_updateextendedproperty"), "update existing comment: {sql}");
        assert!(sql.contains("ELSE EXEC sys.sp_addextendedproperty"), "add missing comment: {sql}");
    }

    #[test]
    fn sqlserver_legacy_column_comment_picks_extended_property_level_by_server_version() {
        let statements = build_sqlserver_column_comment_sql_for_profile(
            "[app].[Categories]",
            Some("app"),
            "Categories",
            "CategoryID",
            "owner's test",
            Some("sqlserver-legacy"),
        );

        assert_eq!(statements.len(), 1);
        let sql = &statements[0];
        let (modern, sql2000) = sql
            .strip_prefix("IF @@MICROSOFTVERSION / 16777216 >= 9 EXEC (N'")
            .and_then(|rest| rest.split_once("') ELSE EXEC (N'"))
            .unwrap_or_else(|| panic!("version switch: {sql}"));
        assert!(modern.contains("sys.extended_properties"), "2005+ property lookup: {sql}");
        assert!(modern.contains("@level0type=N''SCHEMA'', @level0name=N''app''"), "2005+ hierarchy: {sql}");
        assert!(modern.contains("EXEC sys.sp_updateextendedproperty"), "2005+ update procedure: {sql}");
        assert!(!modern.contains("N''USER''"), "USER level fails for non-dbo schemas on 2005+: {sql}");
        assert!(sql2000.contains("::fn_listextendedproperty"), "legacy property lookup: {sql}");
        assert!(sql2000.contains("@level0type=N''USER''"), "legacy hierarchy: {sql}");
        assert!(sql2000.contains("ELSE EXEC sp_addextendedproperty"), "legacy add procedure: {sql}");
        assert!(!sql2000.contains("sys."), "SQL Server 2005+ catalog leaked into the 2000 branch: {sql}");
        assert!(sql.contains("@value=N''owner''''s test''"), "comment escaped for dynamic SQL: {sql}");
        assert!(sql.ends_with("');"), "{sql}");
        assert_eq!(
            crate::sql::split_sql_statements_for_database(sql, crate::models::connection::DatabaseType::SqlServer)
                .len(),
            1,
            "version-switched comment upsert must remain one executable statement: {sql}"
        );
    }
}