数据库同步功能实现：从问题排查到自动化解决方案
一、问题背景
在项目开发和部署过程中，我们遇到了一系列数据库相关的问题，严重影响了应用的稳定性和运维效率：
1.1 启动失败：SQLite Error 14
Microsoft.Data.Sqlite.SqliteException: SQLite Error 14: 'unable to open database file'.
场景：应用首次启动时，目标数据库目录不存在，导致 SQLite 无法创建数据库文件。
1.2 文件占用：进程占用错误
The process cannot access the file '...\appdata.db' because it is being used by another process.
场景：数据库初始化时尝试删除旧数据库文件，但连接池中的连接未释放，导致文件被锁定。
1.3 表结构不同步
场景：开发过程中实体模型发生变更（新增字段、新增表），但生产环境数据库未能及时同步更新，导致运行时错误。
1.4 手动脚本维护困境
原有方案依赖手动编写 SQL 脚本文件进行数据库更新，存在以下问题：
-
脚本版本管理困难
-
部署时容易遗漏或执行顺序错误
-
无法自动检测结构差异
-
缺乏回滚机制
二、问题分析过程
2.1 SQLite Error 14 分析
根因定位：
// 原代码直接创建数据库，未检查目录是否存在
string logDatabaseFilePath = "...\\appdata.db";
// 如果上级目录不存在，SQLite 会抛出 Error 14
解决方案方向：在创建数据库前确保目录存在。
2.2 文件占用问题分析
根因定位：EF Core 连接池机制导致连接未及时释放：
-
应用启动时创建了数据库连接
-
连接被放入连接池复用
-
尝试删除数据库文件时，连接池中的连接仍持有文件句柄
解决方案方向：删除前显式关闭连接、清空连接池、触发垃圾回收。
2.3 表结构同步问题分析
现有方案缺陷：
-
依赖外部 SQL 脚本文件
-
无法自动检测差异
-
无法增量更新
解决方案方向：基于 EF Core 模型元数据，自动检测并同步表结构差异。
三、解决方案实施
3.1 问题1：目录不存在 - 防御性创建
修改文件：WarehouseDispalayBLL/DatabaseHelper.cs、WarehouseDispalayBLL/DatabaseLogHelper.cs
public void InitializeLogDatabase()
{
using (var logDbContext = _dbContextFactory.CreateLogDbContext())
{
string logDatabaseFilePath = logDbContext.Database.GetDbConnection().DataSource;
// 确保数据库目录存在
string dbDirectory = Path.GetDirectoryName(logDatabaseFilePath);
if (!string.IsNullOrEmpty(dbDirectory) && !Directory.Exists(dbDirectory))
{
Directory.CreateDirectory(dbDirectory);
LogHelper.Info($"创建数据库目录: {dbDirectory}");
}
// ... 后续初始化逻辑
}
}
3.2 问题2：文件占用 - 连接清理
修改文件：同上
// 关键修复：关闭连接并清空连接池
logDbContext.Database.CloseConnection();
SqliteConnection.ClearAllPools();
GC.Collect();
GC.WaitForPendingFinalizers();
// 现在可以安全地删除数据库文件
if (File.Exists(logDatabaseFilePath))
{
logDbContext.Database.EnsureDeleted();
}
3.3 问题3：表结构自动同步 - 核心功能实现
3.3.1 差异模型设计
// SchemaChangeType.cs - 差异类型枚举
public enum SchemaChangeType
{
TableNotFound, // 表不存在
ColumnNotFound, // 字段不存在
ColumnTypeMismatch, // 字段类型不匹配
ColumnNullabilityMismatch,
ColumnLengthMismatch
}
// ColumnDifference.cs - 字段差异信息
public class ColumnDifference
{
public string TableName { get; set; }
public string ColumnName { get; set; }
public SchemaChangeType ChangeType { get; set; }
public string ExpectedType { get; set; }
public string ActualType { get; set; }
}
// TableDifference.cs - 表差异信息
public class TableDifference
{
public string TableName { get; set; }
public SchemaChangeType ChangeType { get; set; }
public List<ColumnDifference> ColumnDifferences { get; set; } = new List<ColumnDifference>();
}
3.3.2 SchemaComparer - 差异比较服务
核心逻辑：
-
从 EF Core Model 获取实体元数据
-
从 SQLite 系统表获取实际表结构
-
对比两者差异
public List<TableDifference> CompareSchemas()
{
var differences = new List<TableDifference>();
var modelTables = _context.Model.GetEntityTypes().ToList();
var dbTables = GetDatabaseTables();
foreach (var modelTable in modelTables)
{
var tableName = modelTable.GetTableName();
if (!dbTables.Contains(tableName))
{
differences.Add(new TableDifference
{
TableName = tableName,
ChangeType = SchemaChangeType.TableNotFound
});
}
else
{
var columnDiffs = CompareTableColumns(modelTable);
if (columnDiffs.Any())
{
differences.Add(new TableDifference
{
TableName = tableName,
ColumnDifferences = columnDiffs
});
}
}
}
return differences;
}
3.3.3 SchemaMigrator - 迁移执行服务
public MigrationResult Migrate(List<TableDifference> differences)
{
var result = new MigrationResult();
foreach (var tableDiff in differences)
{
try
{
if (tableDiff.ChangeType == SchemaChangeType.TableNotFound)
{
// 创建新表
_context.Database.EnsureCreated();
result.AddSuccess($"创建表: {tableDiff.TableName}");
}
else if (tableDiff.ColumnDifferences.Any())
{
foreach (var columnDiff in tableDiff.ColumnDifferences)
{
if (columnDiff.ChangeType == SchemaChangeType.ColumnNotFound)
{
// 添加新字段
string sql = $"ALTER TABLE {tableDiff.TableName} " +
$"ADD COLUMN {columnDiff.ColumnName} {columnDiff.ExpectedType}";
_context.Database.ExecuteSqlRaw(sql);
result.AddSuccess($"添加字段: {tableDiff.TableName}.{columnDiff.ColumnName}");
}
}
}
}
catch (Exception ex)
{
result.AddError($"迁移失败 {tableDiff.TableName}: {ex.Message}");
}
}
return result;
}
3.3.4 DatabaseSchemaSyncService - 主服务
public MigrationResult SyncSchema(bool backupBeforeMigration = true)
{
// 1. 检测差异
var differences = DetectDifferences();
if (!differences.Any())
{
LogHelper.Info("数据库表结构与模型定义一致，无需同步");
return new MigrationResult();
}
// 2. 备份数据库（可选）
if (backupBeforeMigration)
{
BackupDatabase();
}
// 3. 执行迁移
var migrator = new SchemaMigrator(_context);
var result = migrator.Migrate(differences);
// 4. 记录日志
LogMigrationResult(result);
return result;
}
3.4 问题4：集成到启动流程
修改文件：WarehouseDisplayOnScreen/App.xaml.cs
移除的旧代码：
// 旧的脚本更新逻辑（已删除）
InitializeDatabaseScripts(databaseHelper);
新增的同步逻辑：
using (var dbContext = _container.Resolve<AgiletyDbContext>())
{
var schemaSyncService = new DatabaseSchemaSyncService(dbContext);
var syncResult = schemaSyncService.SyncSchema();
if (syncResult.SuccessMessages.Any())
{
LogHelper.Info("数据库表结构同步完成");
}
if (syncResult.HasErrors)
{
foreach (var error in syncResult.ErrorMessages)
{
LogHelper.Error(error, null);
}
}
}
四、修改策略总结
4.1 防御性编程原则
策略
应用场景
实施方式
前置检查
数据库目录创建
创建前检查目录是否存在
资源释放
连接池管理
删除前清空连接池
异常捕获
迁移操作
捕获 IOException 并提供友好提示
4.2 自动化优先原则
-
检测自动化：自动检测表结构差异
-
更新自动化：根据检测结果自动执行迁移
-
备份自动化：迁移前自动备份数据库
4.3 数据安全原则
-
增量更新：只添加缺失的表和字段，不修改现有数据
-
自动备份：迁移前自动备份到
Backups目录 -
事务保护：迁移操作在事务中执行
4.4 可观测性原则
-
日志记录：详细记录检测到的差异和执行的操作
-
结果反馈：提供成功、警告、错误三种级别的反馈
五、测试验证
5.1 测试项目结构
WarehouseDispalayBLL.Tests/
├── SchemaSync/
│ ├── SchemaComparerTests.cs # 差异检测测试
│ ├── SchemaMigratorTests.cs # 迁移执行测试
│ └── DatabaseSchemaSyncServiceTests.cs # 同步服务测试
└── WarehouseDispalayBLL.Tests.csproj
5.2 测试用例覆盖
测试类别
测试用例
验证目标
差异检测
空数据库检测
返回表不存在差异
差异检测
已存在表检测
不返回表不存在差异
差异检测
无差异检测
返回空列表
迁移执行
创建新表
成功创建表结构
迁移执行
添加新字段
成功添加字段
迁移执行
数据保留
迁移后数据完整保留
同步服务
空数据库同步
成功初始化数据库
同步服务
已有数据库同步
成功检测无差异
同步服务
差异检测
正确识别结构差异
5.3 测试结果
测试摘要: 总计: 10, 失败: 0, 成功: 10, 已跳过: 0, 持续时间: 1.6 秒
六、最终成果
6.1 问题解决清单
问题
状态
解决方案
SQLite Error 14
已解决
自动创建数据库目录
文件占用错误
已解决
连接池清理 + GC
表结构不同步
已解决
自动同步服务
手动脚本维护
已消除
自动化迁移替代
6.2 新增功能特性
功能
描述
智能差异检测
自动识别缺失的表和字段
增量更新
只添加缺失项，不影响现有数据
自动备份
迁移前自动备份数据库
详细日志
记录完整的迁移过程
错误处理
提供明确的错误信息
6.3 架构改进
应用启动流程
│
▼
数据库初始化
│
▼
SchemaSyncService.SyncSchema()
│
├─► DetectDifferences()
│ │
│ ▼
│ SchemaComparer.CompareSchemas()
│
├─► BackupDatabase()
│
└─► SchemaMigrator.Migrate()
│
├─► CREATE TABLE (新表)
└─► ALTER TABLE ADD COLUMN (新字段)
七、技术亮点
7.1 基于 EF Core Model API
-
利用 EF Core 的元数据 API 获取实体定义
-
无需维护额外的配置文件
-
确保与代码定义完全一致
7.2 SQLite 系统表查询
-- 获取所有表名
SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'
-- 获取表结构
PRAGMA table_info(table_name)
7.3 增量迁移策略
差异类型
处理方式
说明
表不存在
CREATE TABLE
创建新表
字段不存在
ALTER TABLE ADD COLUMN
添加新字段
字段类型不匹配
记录警告
不自动修改（避免数据丢失）
字段不存在于模型
记录警告
不删除（保护历史数据）
八、未来改进方向
8.1 功能增强
-
支持字段属性变更检测（长度、约束等）
-
支持索引同步
-
提供迁移回滚功能
-
支持数据迁移脚本
8.2 性能优化
-
差异检测缓存机制
-
批量迁移操作
-
并发迁移支持
8.3 运维支持
-
迁移报告生成
-
迁移历史记录
-
版本控制集成
九、总结
通过本次优化，我们成功解决了数据库相关的一系列问题，并构建了一套完整的自动化表结构同步机制。核心价值包括：
-
稳定性提升：解决启动失败和文件占用问题
-
效率提升：自动化迁移替代手动脚本
-
数据安全：增量更新 + 自动备份
-
可维护性：基于代码定义，无需额外配置
这套方案不仅解决了当前问题，更为未来的持续迭代提供了坚实的基础设施支持。
适用场景：WPF + EF Core + SQLite 项目的数据库同步需求