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。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
use crate::models::connection::DatabaseType;
#[derive(Debug, Clone, PartialEq, serde::Serialize, serde::Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct DatabaseStructureSearchSqlOptions {
#[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>,
pub database: String,
#[serde(default, skip_serializing_if = "Option::is_none")]
pub schema: Option<String>,
pub term: String,
#[serde(default, skip_serializing_if = "Option::is_none")]
pub limit: Option<usize>,
}
#[derive(Debug, Clone, PartialEq, serde::Serialize, serde::Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct DatabaseStructureSearchSql {
pub sql: String,
}
pub fn supports_database_structure_search(database_type: Option<DatabaseType>, driver_profile: Option<&str>) -> bool {
build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type,
driver_profile: driver_profile.map(str::to_string),
database: "db".to_string(),
schema: None,
term: "x".to_string(),
limit: Some(1),
})
.is_some()
}
pub fn build_database_structure_search_sql(
options: DatabaseStructureSearchSqlOptions,
) -> Option<DatabaseStructureSearchSql> {
let term = clean_term(&options.term);
if term.is_empty() {
return None;
}
let limit = clamp_limit(options.limit);
let pattern = like_pattern(&term);
let pattern_literal = sql_string_literal(&pattern);
let database_type = options.database_type?;
let sql = match database_type {
DatabaseType::Mysql
| DatabaseType::Doris
| DatabaseType::StarRocks
| DatabaseType::ManticoreSearch
| DatabaseType::Goldendb => {
mysql_family_structure_search_sql(&options.database, options.schema.as_deref(), &pattern_literal, limit)
}
DatabaseType::Postgres
| DatabaseType::Kingbase
| DatabaseType::Highgo
| DatabaseType::Uxdb
| DatabaseType::Gbase8c
| DatabaseType::Vastbase
| DatabaseType::OpenGauss
| DatabaseType::Gaussdb
| DatabaseType::Kwdb
| DatabaseType::Yashandb
| DatabaseType::Oscar => {
postgres_family_structure_search_sql(options.schema.as_deref(), &pattern_literal, limit)
}
DatabaseType::Redshift => {
postgres_family_structure_search_sql(options.schema.as_deref(), &pattern_literal, limit)
}
DatabaseType::Sqlite | DatabaseType::Turso | DatabaseType::CloudflareD1 | DatabaseType::Rqlite => {
sqlite_family_structure_search_sql(&pattern_literal, limit)
}
DatabaseType::ClickHouse => clickhouse_structure_search_sql(&options.database, &pattern_literal, limit),
DatabaseType::Oracle | DatabaseType::OceanbaseOracle | DatabaseType::Dameng => {
oracle_family_structure_search_sql(options.schema.as_deref(), &options.database, &pattern_literal, limit)
}
DatabaseType::SqlServer => sqlserver_structure_search_sql(options.schema.as_deref(), &pattern_literal, limit),
// TDengine 没有 ESCAPE 语法,无法复用上面的转义模式,单独构造 "%term%"。
DatabaseType::Tdengine => {
tdengine_structure_search_sql(&options.database, &tdengine_like_pattern_literal(&term), limit)
}
DatabaseType::Snowflake => snowflake_structure_search_sql(options.schema.as_deref(), &pattern_literal, limit),
// GBase 8s is its own `Gbase8s` type (Informix-compatible, no information_schema)
// and falls through to the data search; only MySQL-dialect Gbase 8a takes this path.
DatabaseType::Gbase => {
gbase_structure_search_sql(&options.database, options.schema.as_deref(), &pattern_literal, limit)
}
DatabaseType::Jdbc if driver_profile_is_mysql(options.driver_profile.as_deref()) => {
mysql_family_structure_search_sql(&options.database, options.schema.as_deref(), &pattern_literal, limit)
}
DatabaseType::Jdbc if driver_profile_is_postgres(options.driver_profile.as_deref()) => {
postgres_family_structure_search_sql(options.schema.as_deref(), &pattern_literal, limit)
}
DatabaseType::Jdbc if driver_profile_is_oracle(options.driver_profile.as_deref()) => {
oracle_family_structure_search_sql(options.schema.as_deref(), &options.database, &pattern_literal, limit)
}
_ => return None,
};
Some(DatabaseStructureSearchSql { sql })
}
fn driver_profile_is_mysql(profile: Option<&str>) -> bool {
let profile = profile.map(str::trim).unwrap_or("").to_ascii_lowercase();
profile.contains("mysql")
|| profile.contains("mariadb")
|| profile.contains("doris")
|| profile.contains("selectdb")
|| profile.contains("starrocks")
|| profile.contains("gbase8a")
}
fn driver_profile_is_postgres(profile: Option<&str>) -> bool {
let profile = profile.map(str::trim).unwrap_or("").to_ascii_lowercase();
profile.contains("postgres") || profile.contains("kingbase") || profile.contains("opengauss")
}
fn driver_profile_is_oracle(profile: Option<&str>) -> bool {
let profile = profile.map(str::trim).unwrap_or("").to_ascii_lowercase();
profile.contains("oracle") || profile.contains("dameng") || profile.contains("dm")
}
fn mysql_family_structure_search_sql(
database: &str,
schema: Option<&str>,
pattern_literal: &str,
limit: usize,
) -> String {
let schema_filter = if let Some(schema) = schema.filter(|value| !value.trim().is_empty()) {
format!(" AND TABLE_SCHEMA = {}", sql_string_literal(schema))
} else {
format!(" AND TABLE_SCHEMA = {}", sql_string_literal(database))
};
let comment_match = format!(" OR LOWER(COLUMN_COMMENT) LIKE LOWER({}) ESCAPE '~'", pattern_literal);
let system_schemas = "TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')";
// 表注释分支单独限额,避免字段命中占满共享 LIMIT 后把表注释命中挤掉。
let table_limit = table_comment_search_limit(limit);
// 结构搜索同时覆盖「字段名/字段注释」与「表自身注释」两类命中:
// 字段命中来自 information_schema.COLUMNS,表注释命中单独走 information_schema.TABLES,
// 两个分支各自用子查询限额后再用 UNION ALL 合并统一排序,表级命中行的 column_name 为 NULL。
format!(
"SELECT * FROM ( \
SELECT TABLE_SCHEMA AS object_schema, TABLE_NAME AS object_name, COLUMN_NAME AS column_name, \
COLUMN_COMMENT AS column_comment, NULL AS table_comment \
FROM information_schema.COLUMNS \
WHERE (LOWER(COLUMN_NAME) LIKE LOWER({}) ESCAPE '~'{}) \
{schema_filter} \
AND {system_schemas} \
ORDER BY object_schema, object_name, column_name \
LIMIT {limit} ) AS field_hits \
UNION ALL \
SELECT * FROM ( \
SELECT TABLE_SCHEMA AS object_schema, TABLE_NAME AS object_name, NULL AS column_name, \
NULL AS column_comment, TABLE_COMMENT AS table_comment \
FROM information_schema.TABLES \
WHERE TABLE_TYPE = 'BASE TABLE' \
AND LOWER(COALESCE(TABLE_COMMENT, '')) LIKE LOWER({}) ESCAPE '~' \
{schema_filter} \
AND {system_schemas} \
ORDER BY object_schema, object_name \
LIMIT {table_limit} ) AS table_hits \
ORDER BY object_schema, object_name, column_name \
LIMIT {total_limit}",
pattern_literal,
comment_match,
pattern_literal,
limit = limit,
table_limit = table_limit,
total_limit = limit + table_limit,
)
}
fn postgres_family_structure_search_sql(schema: Option<&str>, pattern_literal: &str, limit: usize) -> String {
let schema_filter = if let Some(schema) = schema.filter(|value| !value.trim().is_empty()) {
format!(" AND n.nspname = {}", sql_string_literal(schema))
} else {
" AND n.nspname NOT IN ('pg_catalog', 'information_schema')".to_string()
};
// 表注释分支单独限额,避免字段命中占满共享 LIMIT 后把表注释命中挤掉。
let table_limit = table_comment_search_limit(limit);
format!(
"SELECT * FROM ( \
SELECT n.nspname AS object_schema, c.relname AS object_name, a.attname AS column_name, \
d.description AS column_comment, NULL AS table_comment \
FROM pg_catalog.pg_attribute a \
JOIN pg_catalog.pg_class c ON c.oid = a.attrelid \
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
LEFT JOIN pg_catalog.pg_description d ON d.objoid = c.oid AND d.objsubid = a.attnum \
WHERE a.attnum > 0 AND NOT a.attisdropped \
AND c.relkind IN ('r', 'v', 'm', 'f', 'p') \
{schema_filter} \
AND (LOWER(a.attname::text) LIKE LOWER({pattern}) ESCAPE '~' \
OR LOWER(COALESCE(d.description, '')) LIKE LOWER({pattern}) ESCAPE '~') \
ORDER BY object_schema, object_name, column_name \
LIMIT {limit} ) AS field_hits \
UNION ALL \
SELECT * FROM ( \
SELECT n.nspname AS object_schema, c.relname AS object_name, NULL AS column_name, \
NULL AS column_comment, d.description AS table_comment \
FROM pg_catalog.pg_class c \
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace \
JOIN pg_catalog.pg_description d ON d.objoid = c.oid AND d.objsubid = 0 \
WHERE c.relkind IN ('r', 'v', 'm', 'f', 'p') \
{schema_filter} \
AND LOWER(COALESCE(d.description, '')) LIKE LOWER({pattern}) ESCAPE '~' \
ORDER BY object_schema, object_name \
LIMIT {table_limit} ) AS table_hits \
ORDER BY object_schema, object_name, column_name \
LIMIT {total_limit}",
schema_filter = schema_filter,
pattern = pattern_literal,
limit = limit,
table_limit = table_limit,
total_limit = limit + table_limit,
)
}
fn sqlite_family_structure_search_sql(pattern_literal: &str, limit: usize) -> String {
format!(
"SELECT '' AS object_schema, m.name AS object_name, p.name AS column_name, NULL AS column_comment \
FROM sqlite_schema AS m, pragma_table_info(m.name) AS p \
WHERE m.type IN ('table', 'view') \
AND m.name NOT LIKE 'sqlite_%' \
AND LOWER(p.name) LIKE LOWER({pattern}) ESCAPE '~' \
ORDER BY m.name, p.cid \
LIMIT {limit}",
pattern = pattern_literal,
limit = limit,
)
}
fn clickhouse_structure_search_sql(database: &str, pattern_literal: &str, limit: usize) -> String {
// ClickHouse 的 ORDER BY/LIMIT 只作用于最近的 SELECT,因此各分支先各自排序限额外,
// 再把 UNION 结果包一层子查询做统一排序与总限量。
// 这里用空串代替 NULL,避免 Nullable(Nothing) 参与 UNION 时的类型兼容问题。
let table_limit = table_comment_search_limit(limit);
format!(
"SELECT * FROM ( \
SELECT * FROM ( \
SELECT database AS object_schema, table AS object_name, name AS column_name, \
comment AS column_comment, '' AS table_comment \
FROM system.columns \
WHERE database = {db} \
AND (lower(name) LIKE lower({pattern}) OR lower(comment) LIKE lower({pattern})) \
ORDER BY object_schema, object_name, column_name \
LIMIT {limit} ) \
UNION ALL \
SELECT * FROM ( \
SELECT database AS object_schema, name AS object_name, '' AS column_name, \
'' AS column_comment, comment AS table_comment \
FROM system.tables \
WHERE database = {db} \
AND lower(COALESCE(comment, '')) LIKE lower({pattern}) \
ORDER BY object_schema, object_name \
LIMIT {table_limit} ) \
) ORDER BY object_schema, object_name, column_name \
LIMIT {total_limit}",
db = sql_string_literal(database),
pattern = pattern_literal,
limit = limit,
table_limit = table_limit,
total_limit = limit + table_limit,
)
}
fn oracle_family_structure_search_sql(
schema: Option<&str>,
database: &str,
pattern_literal: &str,
limit: usize,
) -> String {
let owner = schema.filter(|value| !value.trim().is_empty()).unwrap_or(database);
let owner_literal = sql_string_literal(owner);
let name_expr = "LOWER(c.COLUMN_NAME)";
// 表注释分支单独限额;Oracle 的 ROWNUM 需在排序后的子查询外层生效,因此各分支先排序再取头 N 条。
let table_limit = table_comment_search_limit(limit);
format!(
"SELECT * FROM ( \
SELECT * FROM ( \
SELECT c.OWNER AS object_schema, c.TABLE_NAME AS object_name, c.COLUMN_NAME AS column_name, m.COMMENTS AS column_comment \
, CAST(NULL AS VARCHAR2(4000)) AS table_comment \
FROM ALL_TAB_COLUMNS c \
LEFT JOIN ALL_COL_COMMENTS m ON c.OWNER = m.OWNER AND c.TABLE_NAME = m.TABLE_NAME AND c.COLUMN_NAME = m.COLUMN_NAME \
WHERE c.OWNER = {owner} \
AND ({name_expr} LIKE LOWER({pattern}) ESCAPE '~' OR LOWER(COALESCE(m.COMMENTS, '')) LIKE LOWER({pattern}) ESCAPE '~') \
ORDER BY 1, 2, 3) WHERE ROWNUM <= {limit} \
UNION ALL \
SELECT * FROM ( \
SELECT t.OWNER AS object_schema, t.TABLE_NAME AS object_name, CAST(NULL AS VARCHAR2(128)) AS column_name \
, CAST(NULL AS VARCHAR2(4000)) AS column_comment, t.COMMENTS AS table_comment \
FROM ALL_TAB_COMMENTS t \
WHERE t.OWNER = {owner} \
AND LOWER(COALESCE(t.COMMENTS, '')) LIKE LOWER({pattern}) ESCAPE '~' \
ORDER BY 1, 2) WHERE ROWNUM <= {table_limit} \
ORDER BY 1, 2, 3) WHERE ROWNUM <= {total_limit}",
owner = owner_literal,
name_expr = name_expr,
pattern = pattern_literal,
limit = limit,
table_limit = table_limit,
total_limit = limit + table_limit,
)
}
fn sqlserver_structure_search_sql(schema: Option<&str>, pattern_literal: &str, limit: usize) -> String {
let schema_filter = if let Some(schema) = schema.filter(|value| !value.trim().is_empty()) {
format!(" AND c.TABLE_SCHEMA = {}", sql_string_literal(schema))
} else {
" AND c.TABLE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA')".to_string()
};
// SQL Server 的 TOP 不能直接作用在 UNION 的单个分支上,因此每个分支先在子查询内取 TOP,
// 再由外层对并集统一排序并取总 TOP。
let sys_schema_filter = if let Some(schema) = schema.filter(|value| !value.trim().is_empty()) {
format!(" AND s.name = {}", sql_string_literal(schema))
} else {
" AND s.name NOT IN ('sys', 'INFORMATION_SCHEMA')".to_string()
};
let table_limit = table_comment_search_limit(limit);
format!(
"SELECT TOP {total_limit} * FROM ( \
SELECT * FROM ( \
SELECT TOP {limit} * FROM ( \
SELECT c.TABLE_SCHEMA AS object_schema, c.TABLE_NAME AS object_name, c.COLUMN_NAME AS column_name, \
CAST(ep.value AS NVARCHAR(4000)) AS column_comment, CAST(NULL AS NVARCHAR(4000)) AS table_comment \
FROM INFORMATION_SCHEMA.COLUMNS c \
LEFT JOIN sys.tables t ON t.name = c.TABLE_NAME AND SCHEMA_NAME(t.schema_id) = c.TABLE_SCHEMA \
LEFT JOIN sys.columns sc ON sc.object_id = t.object_id AND sc.name = c.COLUMN_NAME \
LEFT JOIN sys.extended_properties ep ON ep.major_id = sc.object_id AND ep.minor_id = sc.column_id AND ep.name = 'MS_Description' \
WHERE (LOWER(c.COLUMN_NAME) LIKE LOWER({pattern}) ESCAPE '~' \
OR LOWER(COALESCE(CAST(ep.value AS NVARCHAR(4000)), '')) LIKE LOWER({pattern}) ESCAPE '~') \
{schema_filter} \
) AS field_rows ORDER BY object_schema, object_name, column_name ) AS field_hits \
UNION ALL \
SELECT * FROM ( \
SELECT TOP {table_limit} * FROM ( \
SELECT s.name AS object_schema, o.name AS object_name, CAST(NULL AS NVARCHAR(128)) AS column_name, \
CAST(NULL AS NVARCHAR(4000)) AS column_comment, CAST(tep.value AS NVARCHAR(4000)) AS table_comment \
FROM sys.objects o \
JOIN sys.schemas s ON s.schema_id = o.schema_id \
JOIN sys.extended_properties tep ON tep.major_id = o.object_id AND tep.minor_id = 0 AND tep.name = 'MS_Description' \
WHERE o.type IN ('U', 'V') \
{sys_schema_filter} \
AND LOWER(COALESCE(CAST(tep.value AS NVARCHAR(4000)), '')) LIKE LOWER({pattern}) ESCAPE '~' \
) AS table_rows ORDER BY object_schema, object_name ) AS table_hits \
) AS r ORDER BY r.object_schema, r.object_name, r.column_name",
total_limit = limit + table_limit,
limit = limit,
table_limit = table_limit,
pattern = pattern_literal,
schema_filter = schema_filter,
sys_schema_filter = sys_schema_filter,
)
}
fn tdengine_structure_search_sql(database: &str, pattern_literal: &str, limit: usize) -> String {
// TDengine 的表注释来自 information_schema.ins_tables(普通表/子表)与 ins_stables(超级表)。
// 这里要遵守 TDengine 的三条语法约束:
// 1. LIMIT 只能出现在完整 query_expression(顶层语句或带括号的子查询)上,裸的 UNION 分支
// 不能各自带 LIMIT,因此每个分支都写成 `( SELECT ... LIMIT n )` 子查询;
// 2. 语法里没有 ESCAPE 子句,无法转义 %/_,模式直接使用 "%term%"(%/_ 按通配符处理),
// 精确匹配由前端在结果集上二次过滤,避免过度裁剪真实命中;
// 3. ins_columns 没有 note 列,TDengine 也没有列注释,因此列命中只匹配 col_name。
let table_limit = table_comment_search_limit(limit);
let db = sql_string_literal(database);
format!(
"( SELECT db_name AS object_schema, table_name AS object_name, col_name AS column_name, \
'' AS column_comment, '' AS table_comment \
FROM information_schema.ins_columns \
WHERE db_name = {db} AND LOWER(col_name) LIKE {pattern} \
LIMIT {limit} ) \
UNION ALL \
( SELECT db_name AS object_schema, table_name AS object_name, '' AS column_name, \
'' AS column_comment, table_comment AS table_comment \
FROM information_schema.ins_tables \
WHERE db_name = {db} AND table_comment IS NOT NULL AND LOWER(table_comment) LIKE {pattern} \
LIMIT {table_limit} ) \
UNION ALL \
( SELECT db_name AS object_schema, stable_name AS object_name, '' AS column_name, \
'' AS column_comment, table_comment AS table_comment \
FROM information_schema.ins_stables \
WHERE db_name = {db} AND table_comment IS NOT NULL AND LOWER(table_comment) LIKE {pattern} \
LIMIT {table_limit} ) \
ORDER BY object_schema, object_name, column_name \
LIMIT {total_limit}",
db = db,
pattern = pattern_literal,
limit = limit,
table_limit = table_limit,
total_limit = limit + table_limit,
)
}
/// TDengine 的 LIKE 语法没有 ESCAPE 子句,无法转义 `%`/`_`,因此直接使用 `%term%` 模式,
/// 让 `%`/`_` 按通配符处理;精确匹配由前端在结果集上二次过滤,避免把带通配符的词条误判为精确子串。
fn tdengine_like_pattern_literal(term: &str) -> String {
sql_string_literal(&format!("%{}%", clean_term(term).to_ascii_lowercase()))
}
fn snowflake_structure_search_sql(schema: Option<&str>, pattern_literal: &str, limit: usize) -> String {
let schema_filter = if let Some(schema) = schema.filter(|value| !value.trim().is_empty()) {
format!(" AND table_schema = {}", sql_string_literal(schema))
} else {
String::new()
};
// 表注释分支单独限额,避免字段命中占满共享 LIMIT 后把表注释命中挤掉。
let table_limit = table_comment_search_limit(limit);
format!(
"SELECT * FROM ( \
SELECT table_schema AS object_schema, table_name AS object_name, column_name AS column_name, \
comment AS column_comment, NULL AS table_comment \
FROM information_schema.columns \
WHERE (LOWER(column_name) LIKE LOWER({pattern}) ESCAPE '~' OR LOWER(COALESCE(comment, '')) LIKE LOWER({pattern}) ESCAPE '~') \
{schema_filter} \
ORDER BY object_schema, object_name, column_name \
LIMIT {limit} ) AS field_hits \
UNION ALL \
SELECT * FROM ( \
SELECT table_schema AS object_schema, table_name AS object_name, NULL AS column_name, \
NULL AS column_comment, comment AS table_comment \
FROM information_schema.tables \
WHERE LOWER(COALESCE(comment, '')) LIKE LOWER({pattern}) ESCAPE '~' \
{schema_filter} \
ORDER BY object_schema, object_name \
LIMIT {table_limit} ) AS table_hits \
ORDER BY object_schema, object_name, column_name \
LIMIT {total_limit}",
pattern = pattern_literal,
schema_filter = schema_filter,
limit = limit,
table_limit = table_limit,
total_limit = limit + table_limit,
)
}
fn gbase_structure_search_sql(database: &str, schema: Option<&str>, pattern_literal: &str, limit: usize) -> String {
let database = if database.trim().is_empty() { "gbase" } else { database };
mysql_family_structure_search_sql(database, schema, pattern_literal, limit)
}
fn clean_term(term: &str) -> String {
term.trim().to_string()
}
fn clamp_limit(limit: Option<usize>) -> usize {
limit.unwrap_or(200).clamp(1, 5000)
}
/// 表级注释命中的独立结果预算。
///
/// 结构搜索把「字段命中」与「表注释命中」放在同一个 UNION 里,如果两者共用一个 LIMIT,
/// 字段命中较多时表注释命中会被排序挤掉(对齐 DBeaver 按对象类型分配预算的做法)。
/// 这里给表注释分支单独预留总限额的 1/4(至少 1 条),字段命中仍可使用完整的 limit,
/// 外层上限为两者之和,保证表注释命中不会被字段命中挤占。
fn table_comment_search_limit(limit: usize) -> usize {
(limit / 4).max(1)
}
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
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn mysql_structure_search_uses_information_schema() {
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Mysql),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "email".to_string(),
limit: Some(50),
})
.unwrap();
assert!(query.sql.contains("information_schema.COLUMNS"));
assert!(query.sql.contains("COLUMN_NAME"));
assert!(query.sql.contains("COLUMN_COMMENT"));
assert!(query.sql.contains("LIMIT 50"));
}
#[test]
fn dameng_structure_search_uses_oracle_catalog() {
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Dameng),
driver_profile: None,
database: "SYSDBA".to_string(),
schema: Some("APP".to_string()),
term: "user".to_string(),
limit: Some(20),
})
.unwrap();
assert!(query.sql.contains("ALL_TAB_COLUMNS"));
assert!(query.sql.contains("ALL_COL_COMMENTS"));
assert!(query.sql.contains("ROWNUM <= 20"));
}
#[test]
fn unsupported_engine_returns_none() {
assert_eq!(
build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Redis),
driver_profile: None,
database: "0".to_string(),
schema: None,
term: "key".to_string(),
limit: None,
}),
None
);
}
#[test]
fn supports_flag_matches_builder() {
assert!(supports_database_structure_search(Some(DatabaseType::Mysql), None));
assert!(!supports_database_structure_search(Some(DatabaseType::Redis), None));
}
#[test]
fn dropped_engines_return_none() {
for database_type in [
DatabaseType::Cassandra,
DatabaseType::Informix,
DatabaseType::Iris,
DatabaseType::Hive,
DatabaseType::Spark,
DatabaseType::Impala,
DatabaseType::Kyuubi,
DatabaseType::Exasol,
DatabaseType::Vertica,
DatabaseType::Db2,
DatabaseType::Firebird,
DatabaseType::SapHana,
DatabaseType::Teradata,
DatabaseType::Databend,
DatabaseType::Questdb,
DatabaseType::PrestoSql,
DatabaseType::Trino,
] {
assert_eq!(
build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(database_type),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "col".to_string(),
limit: Some(10),
}),
None,
"expected no SQL for {:?}",
database_type
);
assert!(
!supports_database_structure_search(Some(database_type), None),
"supports should be false for {:?}",
database_type
);
}
}
#[test]
fn gbase_search_uses_the_connected_database_and_drops_gbase8s() {
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Gbase),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "email".to_string(),
limit: Some(10),
})
.unwrap();
assert!(
query.sql.contains("TABLE_SCHEMA = 'app'"),
"gbase must filter by the connected database, not a fixed literal"
);
assert!(!query.sql.contains("'gbase'"));
assert_eq!(
build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Gbase8s),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "email".to_string(),
limit: Some(10),
}),
None,
"GBase 8s is Informix-compatible and must fall back to data search"
);
}
#[test]
fn structure_search_includes_table_comment_branch() {
// 表注释命中要与字段命中一起返回:MySQL 家族额外走 information_schema.TABLES 分支。
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Mysql),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "order".to_string(),
limit: Some(80),
})
.unwrap();
assert!(query.sql.contains("UNION ALL"));
assert!(query.sql.contains("information_schema.TABLES"));
assert!(query.sql.contains("TABLE_COMMENT AS table_comment"));
assert!(query.sql.contains("LIMIT 80"));
// MySQL 家族的视图在 information_schema.TABLES 里的 TABLE_COMMENT 是字面量 'VIEW',
// 必须按 BASE TABLE 过滤,否则搜索包含 "view" 子串的词会把所有视图误报为表级命中。
assert!(query.sql.contains("TABLE_TYPE = 'BASE TABLE'"));
}
#[test]
fn sqlserver_structure_search_hoists_top_outside_union() {
// SQL Server 的 TOP 不能直接作用在 UNION 的单个分支上:各分支先在子查询内取 TOP,
// 外层再对并集取总 TOP(30 + 30/4 = 37)。
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::SqlServer),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "name".to_string(),
limit: Some(30),
})
.unwrap();
assert!(query.sql.starts_with("SELECT TOP 37 * FROM ("));
assert!(query.sql.contains("sys.extended_properties"));
assert!(query.sql.contains("minor_id = 0"));
}
#[test]
fn structure_search_reserves_a_separate_budget_for_table_comments() {
// 字段命中用完整 limit,表注释命中额外预留,外层上限为两者之和,
// 这样字段命中再多也不会把表注释命中从 UNION 里挤掉。
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Mysql),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "order".to_string(),
limit: Some(80),
})
.unwrap();
assert!(query.sql.contains("AS field_hits"));
assert!(query.sql.contains("AS table_hits"));
// 字段分支保留完整的 80 条
assert!(query.sql.contains("LIMIT 80 ) AS field_hits"));
// 表注释分支预留 80 / 4 = 20 条
assert!(query.sql.contains("LIMIT 20 ) AS table_hits"));
// 外层上限 = 80 + 20,两个分支都取满也不会被裁掉
assert!(query.sql.contains("LIMIT 100"));
}
#[test]
fn tdengine_structure_search_matches_table_comments() {
// TDengine 表注释来自 ins_tables(普通表/子表)与 ins_stables(超级表)。
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(DatabaseType::Tdengine),
driver_profile: None,
database: "pm".to_string(),
schema: None,
term: "meter".to_string(),
limit: Some(40),
})
.unwrap();
assert!(query.sql.contains("information_schema.ins_columns"));
assert!(query.sql.contains("information_schema.ins_tables"));
assert!(query.sql.contains("information_schema.ins_stables"));
assert!(query.sql.contains("table_comment IS NOT NULL"));
assert!(query.sql.contains("db_name = 'pm'"));
// LIMIT 只能挂在括号子查询上:每个 UNION 分支都要自带括号(40 与 40/4=10)。
assert_eq!(query.sql.matches("( SELECT").count(), 3);
assert!(query.sql.contains("LIMIT 40 )"));
assert!(query.sql.contains("LIMIT 10 )"));
assert!(query.sql.contains("LIMIT 50"));
// TDengine 语法没有 ESCAPE,也没有列注释(ins_columns 没有 note 列),只匹配 col_name。
assert!(!query.sql.contains("ESCAPE"));
assert!(!query.sql.contains("note"));
assert!(query.sql.contains("LOWER(col_name) LIKE '%meter%'"));
}
#[test]
fn structure_search_table_comment_sources_per_dialect() {
// 关系型方言需各自提供表注释来源,缺失时不会打断字段搜索。
let cases = [
(DatabaseType::Postgres, "pg_description"),
(DatabaseType::Oracle, "ALL_TAB_COMMENTS"),
(DatabaseType::Snowflake, "information_schema.tables"),
(DatabaseType::ClickHouse, "system.tables"),
(DatabaseType::Tdengine, "information_schema.ins_tables"),
];
for (database_type, expected) in cases {
let query = build_database_structure_search_sql(DatabaseStructureSearchSqlOptions {
database_type: Some(database_type),
driver_profile: None,
database: "app".to_string(),
schema: None,
term: "user".to_string(),
limit: Some(10),
})
.unwrap_or_else(|| panic!("expected structure search SQL for {:?}", database_type));
assert!(
query.sql.contains(expected),
"expected {:?} structure search SQL to reference {}",
database_type,
expected
);
}
}
}