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。

18.7 KB
Raw Blame
use serde::{Deserialize, Serialize};
use serde_json::Value;

use crate::models::connection::DatabaseType;
use crate::sql_dialect::{
    firebird_rows_clause, pagination_strategy, qualified_table_name, quote_table_identifier, PaginationContext,
    TablePaginationStrategy,
};

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
pub struct DatabaseSearchColumn {
    pub name: String,
    pub data_type: String,
    #[serde(default)]
    pub is_primary_key: bool,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct DatabaseSearchSqlOptions {
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub database_type: Option<DatabaseType>,
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub driver_profile: Option<String>,
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub schema: Option<String>,
    pub table_name: String,
    #[serde(default)]
    pub columns: Vec<DatabaseSearchColumn>,
    pub term: String,
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub limit: Option<usize>,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct DatabaseSearchSql {
    pub sql: String,
    pub searchable_columns: Vec<String>,
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct SearchResultWhereOptions {
    #[serde(default, skip_serializing_if = "Option::is_none")]
    pub database_type: Option<DatabaseType>,
    #[serde(default)]
    pub columns: Vec<DatabaseSearchColumn>,
    #[serde(default)]
    pub result_columns: Vec<String>,
    #[serde(default)]
    pub row: Vec<Value>,
    #[serde(default)]
    pub matched_columns: Vec<String>,
}

const TEXT_TYPES: &[&str] =
    &["char", "text", "clob", "varchar", "nvarchar", "nchar", "uuid", "uniqueidentifier", "enum"];
const NUMBER_TYPES: &[&str] = &["int", "serial", "number", "numeric", "decimal", "float", "double", "real", "money"];
const SKIPPED_TYPES: &[&str] = &["blob", "binary", "bytea", "image", "geometry", "geography"];

pub fn is_text_search_column(column: &DatabaseSearchColumn) -> bool {
    let data_type = column.data_type.to_ascii_lowercase();
    if SKIPPED_TYPES.iter().any(|skipped| data_type.contains(skipped)) {
        return false;
    }
    TEXT_TYPES.iter().any(|text_type| data_type.contains(text_type))
}

pub fn is_numeric_search_column(column: &DatabaseSearchColumn) -> bool {
    let data_type = column.data_type.to_ascii_lowercase();
    if SKIPPED_TYPES.iter().any(|skipped| data_type.contains(skipped)) {
        return false;
    }
    NUMBER_TYPES.iter().any(|number_type| data_type.contains(number_type))
}

#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct TableSearchConditions {
    pub where_clause: String,
    pub searchable_columns: Vec<String>,
}

/// 单表搜索条件拼装:数据搜索单表查询与多表探测分支共用,
/// 保证两条路径对同一表同一关键词产出完全一致的 WHERE 条件。
pub fn build_table_search_conditions(
    database_type: Option<DatabaseType>,
    driver_profile: Option<&str>,
    columns: &[DatabaseSearchColumn],
    term: &str,
) -> Option<TableSearchConditions> {
    let term = clean_term(term);
    if term.is_empty() {
        return None;
    }

    let text_columns: Vec<_> = columns.iter().filter(|column| is_text_search_column(column)).collect();
    let numeric_term = parse_numeric_term(&term);
    let numeric_columns: Vec<_> = if numeric_term.is_some() {
        columns.iter().filter(|column| is_numeric_search_column(column)).collect()
    } else {
        Vec::new()
    };

    let mut conditions = Vec::new();
    for column in &text_columns {
        let identifier = quote_table_identifier(database_type, &column.name);
        conditions.push(text_search_condition(database_type, driver_profile, column, &identifier, &term));
    }
    for column in &numeric_columns {
        let identifier = quote_table_identifier(database_type, &column.name);
        conditions.push(format!("{identifier} = {}", numeric_term.as_deref().unwrap_or_default()));
    }
    if conditions.is_empty() {
        return None;
    }

    Some(TableSearchConditions {
        where_clause: conditions.join(" OR "),
        searchable_columns: text_columns
            .iter()
            .chain(numeric_columns.iter())
            .map(|column| column.name.clone())
            .collect(),
    })
}

pub fn build_database_search_sql(options: DatabaseSearchSqlOptions) -> Option<DatabaseSearchSql> {
    let conditions = build_table_search_conditions(
        options.database_type,
        options.driver_profile.as_deref(),
        &options.columns,
        &options.term,
    )?;
    let table = qualified_table_name(options.database_type, options.schema.as_deref(), &options.table_name);
    let where_clause = conditions.where_clause;
    let limit = clamp_limit(options.limit);
    let searchable_columns = conditions.searchable_columns;

    let sql = match pagination_strategy(options.database_type, PaginationContext::BoundedRead) {
        TablePaginationStrategy::SqlServerTop => format!("SELECT TOP {limit} * FROM {table} WHERE ({where_clause})"),
        TablePaginationStrategy::IrisTop => format!("SELECT TOP {limit} * FROM {table} WHERE ({where_clause})"),
        TablePaginationStrategy::InformixFirst => format!("SELECT FIRST {limit} * FROM {table} WHERE ({where_clause})"),
        TablePaginationStrategy::FirebirdRows => {
            let rows = firebird_rows_clause(limit, 0);
            format!("SELECT * FROM {table} WHERE ({where_clause}) {rows}")
        }
        TablePaginationStrategy::Db2FetchFirst | TablePaginationStrategy::FetchFirst => {
            format!("SELECT * FROM {table} WHERE ({where_clause}) FETCH FIRST {limit} ROWS ONLY")
        }
        TablePaginationStrategy::Rownum => {
            format!("SELECT * FROM (SELECT * FROM {table} WHERE ({where_clause})) WHERE ROWNUM <= {limit}")
        }
        TablePaginationStrategy::AgentMaxRows => format!("SELECT * FROM {table} WHERE ({where_clause});"),
        TablePaginationStrategy::QuestDbLimit | TablePaginationStrategy::LimitOffset => {
            format!("SELECT * FROM {table} WHERE ({where_clause}) LIMIT {limit};")
        }
        TablePaginationStrategy::Unbounded => format!("SELECT * FROM {table} WHERE ({where_clause})"),
    };

    Some(DatabaseSearchSql { sql, searchable_columns })
}

pub fn build_search_result_where(options: SearchResultWhereOptions) -> String {
    let mut value_by_column = std::collections::HashMap::new();
    for (index, column) in options.result_columns.iter().enumerate() {
        if let Some(value) = options.row.get(index) {
            value_by_column.insert(column.to_ascii_lowercase(), value);
        }
    }

    let primary_columns: Vec<_> = options
        .columns
        .iter()
        .filter(|column| column.is_primary_key && value_by_column.contains_key(&column.name.to_ascii_lowercase()))
        .collect();
    let fallback_columns: Vec<_> = options
        .columns
        .iter()
        .filter(|column| {
            options.matched_columns.iter().any(|name| name.eq_ignore_ascii_case(&column.name))
                && value_by_column.contains_key(&column.name.to_ascii_lowercase())
        })
        .collect();
    let selected_columns = if primary_columns.is_empty() { fallback_columns } else { primary_columns };

    selected_columns
        .iter()
        .take(3)
        .map(|column| {
            let identifier = quote_table_identifier(options.database_type, &column.name);
            let value = value_by_column.get(&column.name.to_ascii_lowercase()).copied().unwrap_or(&Value::Null);
            if value.is_null() {
                format!("{identifier} IS NULL")
            } else {
                format!("{identifier} = {}", sql_value_literal(options.database_type, column, value))
            }
        })
        .collect::<Vec<_>>()
        .join(" AND ")
}

fn clean_term(term: &str) -> String {
    term.trim().to_string()
}

pub(crate) fn clamp_limit(limit: Option<usize>) -> usize {
    limit.unwrap_or(20).clamp(1, 100)
}

fn parse_numeric_term(term: &str) -> Option<String> {
    let trimmed = clean_term(term);
    if trimmed.is_empty() {
        return None;
    }
    let bytes = trimmed.as_bytes();
    let start = usize::from(matches!(bytes.first(), Some(b'+') | Some(b'-')));
    let number = &trimmed[start..];
    if number.is_empty() {
        return None;
    }
    let mut dot_count = 0;
    let mut digit_count = 0;
    for ch in number.chars() {
        if ch == '.' {
            dot_count += 1;
            if dot_count > 1 {
                return None;
            }
        } else if ch.is_ascii_digit() {
            digit_count += 1;
        } else {
            return None;
        }
    }
    if digit_count == 0 {
        None
    } else {
        Some(trimmed)
    }
}

fn sql_string_literal(value: &str) -> String {
    format!("'{}'", value.replace('\'', "''"))
}

fn like_pattern(term: &str) -> String {
    let mut pattern = String::from("%");
    for ch in clean_term(term).to_ascii_lowercase().chars() {
        if matches!(ch, '~' | '%' | '_') {
            pattern.push('~');
        }
        pattern.push(ch);
    }
    pattern.push('%');
    pattern
}

fn text_cast_expression(database_type: Option<DatabaseType>, driver_profile: Option<&str>, identifier: &str) -> String {
    match database_type {
        Some(DatabaseType::Mysql) => format!("LOWER(CAST({identifier} AS CHAR))"),
        Some(DatabaseType::SqlServer)
            if driver_profile.is_some_and(|profile| profile.trim().eq_ignore_ascii_case("sqlserver-legacy")) =>
        {
            format!("LOWER(CAST({identifier} AS NVARCHAR(4000)))")
        }
        Some(DatabaseType::SqlServer) => format!("LOWER(CAST({identifier} AS NVARCHAR(MAX)))"),
        Some(DatabaseType::Oracle | DatabaseType::OceanbaseOracle) => {
            format!("LOWER(CAST({identifier} AS VARCHAR2(4000)))")
        }
        Some(DatabaseType::ClickHouse) => format!("lower(toString({identifier}))"),
        _ => format!("LOWER(CAST({identifier} AS TEXT))"),
    }
}

/// Oracle 家族的 CLOB 列不能走 CAST AS VARCHAR2(4000):内容超 4000 字符时直接抛
/// ORA-22835(整表不可搜)。改用 DBMS_LOB.INSTR 全量搜索——不截断、不报错;
/// LOWER(CLOB) 返回 CLOB,INSTR 对 CLOB 直接可用。DM8/OB Oracle 模式均提供 DBMS_LOB。
/// INSTR 按字面子串匹配,与 LIKE '%term%' ESCAPE(通配符已被转义为字面量)语义一致。
fn is_oracle_family_clob(database_type: Option<DatabaseType>, data_type: &str) -> bool {
    matches!(database_type, Some(DatabaseType::Oracle | DatabaseType::OceanbaseOracle | DatabaseType::Dameng))
        && data_type.to_ascii_lowercase().contains("clob")
}

fn text_search_condition(
    database_type: Option<DatabaseType>,
    driver_profile: Option<&str>,
    column: &DatabaseSearchColumn,
    identifier: &str,
    term: &str,
) -> String {
    if is_oracle_family_clob(database_type, &column.data_type) {
        return format!(
            "DBMS_LOB.INSTR(LOWER({identifier}), {}) > 0",
            sql_string_literal(&clean_term(term).to_ascii_lowercase())
        );
    }
    format!(
        "{} LIKE {} ESCAPE '~'",
        text_cast_expression(database_type, driver_profile, identifier),
        sql_string_literal(&like_pattern(term))
    )
}

fn sql_value_literal(database_type: Option<DatabaseType>, column: &DatabaseSearchColumn, value: &Value) -> String {
    match value {
        Value::Null => "NULL".to_string(),
        Value::Number(number) => number.to_string(),
        Value::Bool(value) => {
            if *value {
                "TRUE".to_string()
            } else {
                "FALSE".to_string()
            }
        }
        Value::String(value) => {
            if is_numeric_search_column(column) && parse_numeric_term(value).is_some() {
                return value.trim().to_string();
            }
            if database_type == Some(DatabaseType::SqlServer) {
                format!("N{}", sql_string_literal(value))
            } else {
                sql_string_literal(value)
            }
        }
        other => sql_string_literal(&other.to_string()),
    }
}

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

    fn col(name: &str, data_type: &str, is_primary_key: bool) -> DatabaseSearchColumn {
        DatabaseSearchColumn { name: name.to_string(), data_type: data_type.to_string(), is_primary_key }
    }

    #[test]
    fn oracle_family_clob_columns_use_dbms_lob_instr() {
        let query = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::Oracle),
            driver_profile: None,
            schema: Some("ZOEDM".to_string()),
            table_name: "CLOSED_LOOP_VIEW_LOG".to_string(),
            columns: vec![col("ID", "number", true), col("TITLE", "varchar2", false), col("CONTENT", "clob", false)],
            term: "closed_loop".to_string(),
            limit: Some(20),
        })
        .unwrap();

        // CLOB 列走 DBMS_LOB.INSTR(全量、不抛 ORA-22835),varchar2 列保持 CAST+LIKE
        assert!(query.sql.contains("DBMS_LOB.INSTR(LOWER(\"CONTENT\"), 'closed_loop') > 0"));
        // LIKE 侧通配符照常转义(_ → ~_)
        assert!(query.sql.contains("LOWER(CAST(\"TITLE\" AS VARCHAR2(4000))) LIKE '%closed~_loop%' ESCAPE '~'"));

        // Dameng / OceanBase Oracle 同为 Oracle 家族
        let dameng = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::Dameng),
            driver_profile: None,
            schema: None,
            table_name: "t".to_string(),
            columns: vec![col("BODY", "clob", false)],
            term: "x".to_string(),
            limit: None,
        })
        .unwrap();
        assert!(dameng.sql.contains("DBMS_LOB.INSTR(LOWER(\"BODY\"), 'x') > 0"));

        // 其他方言的 clob/text 列不受影响,仍走 CAST+LIKE
        let mysql = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::Mysql),
            driver_profile: None,
            schema: None,
            table_name: "t".to_string(),
            columns: vec![col("body", "longtext", false)],
            term: "x".to_string(),
            limit: None,
        })
        .unwrap();
        assert!(mysql.sql.contains("LIKE '%x%'"));
    }

    #[test]
    fn builds_table_search_query_over_text_columns() {
        let query = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::Mysql),
            driver_profile: None,
            schema: None,
            table_name: "users".to_string(),
            columns: vec![col("id", "bigint", true), col("email", "varchar", false), col("avatar", "blob", false)],
            term: "[email protected]".to_string(),
            limit: Some(20),
        })
        .unwrap();

        assert_eq!(query.searchable_columns, vec!["email"]);
        assert_eq!(
            query.sql,
            "SELECT * FROM `users` WHERE (LOWER(CAST(`email` AS CHAR)) LIKE '%[email protected]%' ESCAPE '~') LIMIT 20;"
        );
    }

    #[test]
    fn uses_legacy_sqlserver_cast_without_max_type() {
        let query = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::SqlServer),
            driver_profile: Some(" SQLSERVER-LEGACY ".to_string()),
            schema: Some("dbo".to_string()),
            table_name: "users".to_string(),
            columns: vec![col("name", "nvarchar", false)],
            term: "alice".to_string(),
            limit: Some(20),
        })
        .unwrap();

        assert_eq!(
            query.sql,
            "SELECT TOP 20 * FROM [dbo].[users] WHERE (LOWER(CAST([name] AS NVARCHAR(4000))) LIKE '%alice%' ESCAPE '~')"
        );
    }

    #[test]
    fn adds_numeric_predicates_only_for_numeric_terms() {
        let query = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::Postgres),
            driver_profile: None,
            schema: Some("public".to_string()),
            table_name: "orders".to_string(),
            columns: vec![col("id", "integer", true), col("note", "text", false)],
            term: "42".to_string(),
            limit: Some(10),
        })
        .unwrap();

        assert_eq!(query.searchable_columns, vec!["note", "id"]);
        assert!(query.sql.contains("\"id\" = 42"));
        assert!(query.sql.contains("FROM \"public\".\"orders\""));
    }

    #[test]
    fn cassandra_bounded_search_keeps_its_limit() {
        let query = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::Cassandra),
            driver_profile: None,
            schema: Some("app".to_string()),
            table_name: "events".to_string(),
            columns: vec![col("id", "int", true)],
            term: "42".to_string(),
            limit: Some(17),
        })
        .unwrap();

        assert!(query.sql.ends_with(" LIMIT 17;"), "sql was: {}", query.sql);
    }

    #[test]
    fn builds_oceanbase_oracle_search_query_with_rownum_limit() {
        let query = build_database_search_sql(DatabaseSearchSqlOptions {
            database_type: Some(DatabaseType::OceanbaseOracle),
            driver_profile: None,
            schema: Some("APP".to_string()),
            table_name: "USERS".to_string(),
            columns: vec![col("NAME", "varchar2", false)],
            term: "alice".to_string(),
            limit: Some(20),
        })
        .unwrap();

        assert_eq!(query.searchable_columns, vec!["NAME"]);
        assert_eq!(
            query.sql,
            "SELECT * FROM (SELECT * FROM \"APP\".\"USERS\" WHERE (LOWER(CAST(\"NAME\" AS VARCHAR2(4000))) LIKE '%alice%' ESCAPE '~')) WHERE ROWNUM <= 20"
        );
    }

    #[test]
    fn returns_none_for_tables_without_searchable_columns() {
        assert_eq!(
            build_database_search_sql(DatabaseSearchSqlOptions {
                database_type: Some(DatabaseType::Sqlite),
                driver_profile: None,
                schema: None,
                table_name: "files".to_string(),
                columns: vec![col("payload", "blob", false)],
                term: "needle".to_string(),
                limit: None,
            }),
            None
        );
    }

    #[test]
    fn builds_stable_where_predicate_for_opening_result_rows() {
        let where_clause = build_search_result_where(SearchResultWhereOptions {
            database_type: Some(DatabaseType::SqlServer),
            columns: vec![col("id", "integer", true), col("email", "varchar", false)],
            result_columns: vec!["id".to_string(), "email".to_string()],
            row: vec![Value::from(42), Value::from("[email protected]")],
            matched_columns: Vec::new(),
        });

        assert_eq!(where_clause, "[id] = 42");
    }
}