Skip to content

fix(db): 清空表后自动刷新表数据页签（TRUNCATE 与全表 DELETE 的数据级失效通知） #413

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
feigeCode merged 1 commit into from
Oct 16, 2026
Merged

fix(db): 清空表后自动刷新表数据页签（TRUNCATE 与全表 DELETE 的数据级失效通知） #413

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
11 changes: 11 additions & 0 deletions crates/core/src/connection_notifier.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -45,6 +45,17 @@ pub enum ConnectionDataEvent {
database: String,
schema: Option<String>,
},
/// 表数据被整表清空（TRUNCATE 或无 WHERE 的 `DELETE FROM` 执行成功后触发）
///
/// 只代表数据变化，表结构未变：打开的表数据网格应重新拉取数据，
/// 树结构、元数据缓存均无需刷新。订阅方按 `connection_id + database +
/// schema + table` 精确匹配，避免无关页签无谓刷新。
TableDataCleared {
connection_id: String,
database: String,
schema: Option<String>,
table: String,
},
/// 请求执行一次云同步（由表单等非首页入口触发）
CloudSyncRequested,
/// 团队缓存已刷新，打开的表单应重新读取团队选项
Expand Down
91 changes: 80 additions & 11 deletions crates/db/src/cache_manager.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -26,10 +26,37 @@ use tracing::{debug, info, warn};
pub struct SchemaInvalidationScope {
pub database: String,
pub schema: Option<String>,
/// 触发本次失效的具体表。仅数据级事件（`data_only == true`）携带，
/// 供调用方发 `TableDataCleared` 通知让数据页签刷新。
pub table: Option<String>,
/// `true` 表示仅数据变化（TRUNCATE / 无 WHERE 的整表 DELETE），
/// 结构未变：订阅方只需刷新数据，树结构与元数据保持原样。
pub data_only: bool,
}

impl SchemaInvalidationScope {
fn from_event(event: &DdlEvent) -> Self {
// 整表清空（TRUNCATE / DELETE FROM 无 WHERE）只改数据不改结构，
// 归为数据级范围；其余事件按结构级处理。
if let DdlEvent::TruncateTable {
database,
schema,
table,
}
| DdlEvent::DeleteAllRows {
database,
schema,
table,
} = event
{
return Self {
database: database.clone(),
schema: schema.clone(),
table: Some(table.clone()),
data_only: true,
};
}

let (database, schema) = match event {
DdlEvent::CreateTable {
database, schema, ..
Expand All @@ -40,9 +67,6 @@ impl SchemaInvalidationScope {
| DdlEvent::DropTable {
database, schema, ..
}
| DdlEvent::TruncateTable {
database, schema, ..
}
| DdlEvent::RenameTable {
database, schema, ..
}
Expand Down Expand Up @@ -79,9 +103,17 @@ impl SchemaInvalidationScope {
| DdlEvent::DropFunction { database, .. }
| DdlEvent::CreateProcedure { database, .. }
| DdlEvent::DropProcedure { database, .. } => (database.clone(), None),
DdlEvent::TruncateTable { .. } | DdlEvent::DeleteAllRows { .. } => unreachable!(
"data-only events are handled above and must not be classified as structural"
),
};

Self { database, schema }
Self {
database,
schema,
table: None,
data_only: false,
}
}
}

Expand All @@ -103,7 +135,12 @@ impl SchemaInvalidationPlan {
Self {
events: Vec::new(),
connection_wide: true,
fallback_scope: Some(SchemaInvalidationScope { database, schema }),
fallback_scope: Some(SchemaInvalidationScope {
database,
schema,
table: None,
data_only: false,
}),
}
}

Expand Down Expand Up @@ -601,8 +638,9 @@ impl GlobalNodeCache {

/// 解析 SQL 并触发 DDL 失效（MetadataCache + NodeCache）
///
/// 返回解析到的 DDL 事件信息（connection_id, database, schema），供调用方发射 SchemaChanged 事件。
/// 支持多语句：返回第一个 DDL 事件的范围信息。
/// 返回本次失效涉及的全部 [`SchemaInvalidationScope`]，供调用方发通知：
/// `data_only` 的范围发 `TableDataCleared`，其余发 `SchemaChanged`。
/// 支持多语句。
pub async fn process_sql_for_invalidation(
&self,
connection_id: &str,
Expand All @@ -611,7 +649,7 @@ impl GlobalNodeCache {
current_schema: Option<&str>,
database_type: &DatabaseType,
cache_ctx: Option<&CacheContext>,
) -> Option<(String, String, Option<String>)> {
) -> Vec<SchemaInvalidationScope> {
let plan = self.plan_sql_invalidation(
sql,
SqlInvalidationContext {
Expand All @@ -622,9 +660,6 @@ impl GlobalNodeCache {
);
self.apply_invalidation_plan(connection_id, &plan, cache_ctx)
.await
.into_iter()
.next()
.map(|scope| (connection_id.to_string(), scope.database, scope.schema))
}

/// 使表相关缓存失效
Expand Down Expand Up @@ -1181,10 +1216,14 @@ mod tests {
SchemaInvalidationScope {
database: "app".to_string(),
schema: Some("public".to_string()),
table: None,
data_only: false,
},
SchemaInvalidationScope {
database: "app".to_string(),
schema: Some("audit".to_string()),
table: None,
data_only: false,
},
]
);
Expand All @@ -1204,6 +1243,36 @@ mod tests {
vec![SchemaInvalidationScope {
database: "app".to_string(),
schema: Some("public".to_string()),
table: None,
data_only: false,
}]
);
}

#[test]
fn data_clearing_events_map_to_data_only_scopes() {
// TRUNCATE 与无 WHERE 的 DELETE 是数据级范围：携带表名、data_only，
// 订阅方据此发 TableDataCleared 而不是 SchemaChanged。
let plan = SchemaInvalidationPlan::from_events(vec![
DdlEvent::TruncateTable {
database: "app".to_string(),
schema: Some("public".to_string()),
table: "users".to_string(),
},
DdlEvent::DeleteAllRows {
database: "app".to_string(),
schema: Some("public".to_string()),
table: "users".to_string(),
},
]);

assert_eq!(
plan.scopes(),
vec![SchemaInvalidationScope {
database: "app".to_string(),
schema: Some("public".to_string()),
table: Some("users".to_string()),
data_only: true,
}]
);
}
Expand Down
161 changes: 157 additions & 4 deletions crates/db/src/ddl_invalidator.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -48,6 +48,16 @@ pub enum DdlEvent {
schema: Option<String>,
table: String,
},
/// 整表数据清空（无 WHERE 子句的 `DELETE FROM <table>`）
///
/// 与 [`DdlEvent::TruncateTable`] 等价的数据效果，但属于 DML：
/// SQLite/DuckDB 等不支持 TRUNCATE 的数据库生成的清空语句走这里。
/// 表结构未变，只触发数据级通知，不做元数据失效。
DeleteAllRows {
database: String,
schema: Option<String>,
table: String,
},
/// 重命名表
RenameTable {
database: String,
Expand Down Expand Up @@ -158,9 +168,9 @@ impl DdlInvalidator {

/// 使用数据库方言解析 SQL 中的所有 DDL 事件（支持多语句）。
///
/// 先用流式语句切分器线性扫描脚本，只把以 DDL 关键字开头的语句交给
/// sqlparser 构建 AST。这样包含超大 INSERT/UPDATE 的混合脚本不会为了缓存
/// 失效而再次构建完整 DML AST。
/// 先用流式语句切分器线性扫描脚本，只把以 DDL 关键字开头（或疑似整表
/// `DELETE`）的语句交给 sqlparser 构建 AST。这样包含超大 INSERT/UPDATE
/// 的混合脚本不会为了缓存失效而再次构建完整 AST。
pub fn parse_ddl_events_for_database(
sql: &str,
current_database: &str,
Expand Down Expand Up @@ -191,7 +201,8 @@ impl DdlInvalidator {

for statement in parser {
let statement = statement?;
if Self::starts_with_ddl_keyword(&statement) {
if Self::starts_with_ddl_keyword(&statement) || Self::is_full_table_delete(&statement)
{
candidates.push(statement);
}
}
Expand Down Expand Up @@ -243,6 +254,18 @@ impl DdlInvalidator {
.any(|keyword| first_word.eq_ignore_ascii_case(keyword))
}

/// 廉价预筛：是否疑似「整表清空」的 `DELETE FROM <table>`。
///
/// 只有语句整体不含 `WHERE` 字样时才算候选（子串匹配，不做词法分析）：
/// - 带 WHERE 的条件删除一定被排除，不会为大体积条件删除构建 AST；
/// - 整表 `DELETE` 的字符串字面量里恰好出现 "WHERE" 时会被漏判，
/// 代价只是数据页签少一次自动刷新，用户手动刷新即可，可接受。
/// 精确判定交给 sqlparser AST（`selection == None`）。
fn is_full_table_delete(sql: &str) -> bool {
let upper = sql.to_uppercase();
upper.trim_start().starts_with("DELETE FROM") && !upper.contains("WHERE")
}

fn parse_ddl_candidate(
sql: &str,
current_database: &str,
Expand Down Expand Up @@ -492,6 +515,33 @@ impl DdlInvalidator {
})
}

// 无 WHERE 的 `DELETE FROM <table>`：整表数据清空（SQLite/DuckDB
// 等不支持 TRUNCATE 的数据库生成的清空语句）。带 WHERE 的条件删除
// 不产生任何事件。
Statement::Delete(delete) => {
let is_unconditional = delete.selection.is_none();
let first_table = match &delete.from {
sqlparser::ast::FromTable::WithFromKeyword(tables) => tables.first(),
sqlparser::ast::FromTable::WithoutKeyword(tables) => tables.first(),
};
let table = first_table.and_then(|table_with_joins| {
match &table_with_joins.relation {
sqlparser::ast::TableFactor::Table { name, .. } => {
Some(Self::extract_object_name_from_ast(name))
}
_ => None,
}
});
match (is_unconditional, table) {
(true, Some(table)) => Some(DdlEvent::DeleteAllRows {
database: current_database.to_string(),
schema: current_schema.map(|s| s.to_string()),
table,
}),
_ => None,
}
}

Statement::RenameTable(operations) => {
let first = operations.first()?;
let old_table = Self::extract_object_name_from_ast(&first.old_name);
Expand Down Expand Up @@ -682,6 +732,17 @@ impl DdlInvalidator {
});
}

// 无 WHERE 的 DELETE FROM：整表数据清空（与 is_full_table_delete 的
// 候选条件保持一致，见该函数注释）。
if upper.starts_with("DELETE FROM") && !upper.contains("WHERE") {
let table = Self::extract_object_name(sql_trimmed, "DELETE FROM")?;
return Some(DdlEvent::DeleteAllRows {
database: current_database.to_string(),
schema: current_schema.map(|s| s.to_string()),
table,
});
}

// RENAME TABLE
if upper.starts_with("RENAME TABLE") {
let rest = sql_trimmed[12..].trim();
Expand Down Expand Up @@ -1196,6 +1257,11 @@ impl DdlInvalidator {
let key = CacheKey::schemas(connection_id, database);
cache.invalidate(&key).await;
}

// 整表数据清空不改变表结构与元数据，无需失效任何缓存；
// 它只会通过 SchemaInvalidationScope 的 data_only 标记触发
// TableDataCleared 数据刷新通知。
DdlEvent::DeleteAllRows { .. } => {}
}
}
}
Expand Down Expand Up @@ -1342,6 +1408,93 @@ mod tests {
assert!(matches!(event, Some(DdlEvent::TruncateTable { table, .. }) if table == "users"));
}

#[test]
fn test_parse_full_table_delete() {
// 无 WHERE 的 DELETE：与 TRUNCATE 等价的整表清空（SQLite/DuckDB 生成）
let event = DdlInvalidator::parse_ddl_event("DELETE FROM users", "mydb", None);
assert!(
matches!(event, Some(DdlEvent::DeleteAllRows { table, .. }) if table == "users")
);

// schema 限定：表名取最后一段
let event = DdlInvalidator::parse_ddl_event("DELETE FROM public.users", "mydb", None);
assert!(
matches!(event, Some(DdlEvent::DeleteAllRows { table, .. }) if table == "users")
);

// 带引号标识符
let event = DdlInvalidator::parse_ddl_event("DELETE FROM `users`", "mydb", None);
assert!(
matches!(event, Some(DdlEvent::DeleteAllRows { table, .. }) if table == "users")
);

// 大小写不敏感
let event = DdlInvalidator::parse_ddl_event("delete from users", "mydb", None);
assert!(matches!(event, Some(DdlEvent::DeleteAllRows { .. })));

// 当前 schema 透传
let event = DdlInvalidator::parse_ddl_event("DELETE FROM users", "mydb", Some("public"));
assert!(
matches!(event, Some(DdlEvent::DeleteAllRows { schema, .. }) if schema == Some("public".to_string()))
);
}

#[test]
fn test_conditional_delete_is_not_detected() {
// 带 WHERE 的条件删除不属于整表清空，不应产生任何事件
let event =
DdlInvalidator::parse_ddl_event("DELETE FROM users WHERE id = 1", "mydb", None);
assert!(event.is_none());

// 候选预筛必须排除带 WHERE 的删除，避免为大体积条件删除构建 AST
assert!(!DdlInvalidator::is_full_table_delete(
"DELETE FROM users WHERE id = 1"
));
assert!(DdlInvalidator::is_full_table_delete("DELETE FROM users"));
assert!(DdlInvalidator::is_full_table_delete(" delete from users"));
// ORDER BY 不带 LIMIT 仍是整表删除；带 LIMIT 的部分删除会被预筛放行，
// 属于可接受的多刷新一次（见 is_full_table_delete 注释）。
assert!(DdlInvalidator::is_full_table_delete(
"DELETE FROM users ORDER BY id"
));
}

#[test]
fn test_full_table_delete_in_mixed_script() {
let sql = "SELECT 1; DELETE FROM users; INSERT INTO logs VALUES (1);";
let events = DdlInvalidator::parse_ddl_events(sql, "mydb", None);
assert_eq!(events.len(), 1, "events: {events:?}");
assert!(matches!(&events[0], DdlEvent::DeleteAllRows { table, .. } if table == "users"));
}

#[tokio::test]
async fn delete_all_rows_does_not_invalidate_metadata() {
// 数据级事件只触发 TableDataCleared 通知，表结构/表列表缓存必须保留
let cache = memory_only_cache();
let invalidator = DdlInvalidator::new(cache.clone());
let tables_key = CacheKey::tables("connection", "database", Some("public"));

cache
.set(
&tables_key,
MetadataEntry::Tables(Vec::new()),
CacheLevel::Database,
)
.await;
invalidator
.invalidate(
"connection",
&DdlEvent::DeleteAllRows {
database: "database".to_string(),
schema: Some("public".to_string()),
table: "users".to_string(),
},
)
.await;

assert!(cache.get(&tables_key).await.is_some());
}

#[test]
fn test_parse_create_database() {
let event = DdlInvalidator::parse_ddl_event("CREATE DATABASE testdb", "mydb", None);
Expand Down
Loading
Loading