103. SQLite 本地数据库与 ORM 映射框架搭建

日期： 2026-05-20 关联开发任务： 阶段 1 任务 1.3 数据库初始化 关联设计文档： 004.数据库设计文档、001.概要设计文档 §五

一、问题/需求描述

阶段 1.3 要求搭建完整的数据库基础设施：SQLite 本地数据库、PostgreSQL 云端脚本（预置）、ORM 映射框架。需要一次性建立从「DDL 建表」到「C++ 实体 CRUD」的完整链路，让后续阶段的 Repository 实现有模板可循。

二、思路

先落表，再写代码：DDL 脚本作为唯一真相源，C++ 代码的字段名、类型、约束全部对齐 DDL
连接管理用单例：SQLite 是进程内数据库，一个连接足够，单例模式最简洁
ORM 不求全，求实用：不做完整 ORM 框架（太重），只提供 3 个工具——UUID 生成、ISO 时间戳、QSqlQuery 绑定/读取辅助函数
先跑通一个 Repository：用 UserRepository 验证整条链路，后续 FamilyRepository、RecipeRepository 照搬模板
PostgreSQL 暂不连接：只输出 DDL 脚本，连接代码留到阶段 8

三、逻辑推导

3.1 为什么用参数化查询而非字符串拼接

SQL 注入是 OWASP Top 10 常客。本项目使用 Qt 的 prepare() + bindValue()：

// ✅ 安全
query.prepare("SELECT * FROM users WHERE id = :id");
query.bindValue(":id", userInput);

// ❌ 危险（本项目永远不要这样写）
query.exec("SELECT * FROM users WHERE id = '" + userInput + "'");

所有 Repository 的 SQL 都硬编码在 .cpp 中，不接受外部拼接。

3.2 乐观锁的 SQL 实现

UPDATE users SET nickname = :new, version = version + 1
WHERE id = :id AND version = :expected;

updateWithVersion 调用后检查 query.numRowsAffected()：

> 0 → 更新成功，版本号已自增
== 0 → 版本冲突（其他端已修改），返回 false

3.3 数据库文件路径策略

使用 QStandardPaths::AppDataLocation：

平台	路径
Windows	`C:\Users\<user>\AppData\Local\SmartDietManager\smartdiet.db`
macOS	`~/Library/Application Support/SmartDietManager/smartdiet.db`
Linux	`~/.local/share/SmartDietManager/smartdiet.db`

无需硬编码，跨平台自动适配。.gitignore 已有 *.db 规则，不会误上传。

四、实施方案

4.1 文件清单

resources/schema/sqlite_schema.sql      ← 23 张表完整 DDL (+35 索引)
resources/schema/postgresql_schema.sql   ← 22 张表（无 sync_queue），类型适配
src/data/database_manager.h/.cpp         ← 单例，初始化+建表+版本追踪
src/utils/uuid_utils.h                   ← generateUuid()
src/utils/time_utils.h                   ← utcNow()
src/data/orm_helper.h                    ← bindId/bindIdAndVersion/readString/readBool...
src/data/user_entity.h                   ← User 纯数据实体
src/data/user_repository.h/.cpp          ← 首个具体 Repository（6 CRUD + 2 查询）

4.2 DatabaseManager 初始化流程

DatabaseManager::initialize()
  ├── QStandardPaths → 确定 db 文件路径
  ├── QSqlDatabase::addDatabase("QSQLITE")
  ├── PRAGMA foreign_keys = ON
  ├── PRAGMA journal_mode = WAL
  ├── SELECT FROM sqlite_master → 表是否存在？
  │   └── 否 → QFile(":/schema/sqlite_schema.sql")
  │          → 逐条分号分割执行 → 23 张表 + 35+ 索引
  └── sqlite_meta.schema_version = 1

4.3 UserRepository 接口

方法	SQL	说明
`getById`	`SELECT * WHERE id=:id AND is_deleted=0`	返回 `std::optional<User>`
`getAll`	`SELECT * WHERE is_deleted=0 ORDER BY created_at DESC`	返回 `QList<User>`
`save`	`INSERT INTO users (16 columns) VALUES (...)`	UUID + 时间戳由调用方预填
`update`	`UPDATE users SET ... WHERE id=:id`	version 自增
`softDelete`	`UPDATE SET is_deleted=1 WHERE id=:id`	不物理删除
`updateWithVersion`	`UPDATE ... WHERE id=:id AND version=:ver`	乐观锁
`getByLoginId`	`SELECT * WHERE login_id=:id`	登录专用
`existsByLoginId`	`SELECT 1 WHERE login_id=:id`	注册校验

4.4 实体映射策略

每个 Repository 内部有一个 static mapRow(const QSqlQuery&) 私有方法，将一行结果集映射为实体 struct。字段读取统一走 OrmHelper::readString/readInt/readReal/readBool，NULL 值自动处理为默认值。

五、可行性分析

维度	评估
Qt6 SQLite 驱动	Qt 6.x 自带 QSQLITE 驱动，零额外依赖
WAL 模式	允许并发读，单写者。适合本项目的”主线程读写 + 同步线程读”模式
schema 版本追踪	`sqlite_meta` 表为将来迁移（ALTER TABLE ADD COLUMN）做准备
UUID 唯一性	v4 随机 UUID，碰撞概率 ~10^-36，单人项目无需忧虑
实体数量	后续 7 个 Repository 只需复制 UserRepository 模板，改 SQL 和 mapRow 即可

六、验证

验证项	方法	结果
DDL 脚本可执行	`DatabaseManager` 首次初始化无报错	✅
23 张表全部创建	`SELECT COUNT(*) FROM sqlite_master WHERE type='table'`	✅
外键约束生效	`PRAGMA foreign_keys` 返回 1	✅
编译零错误零警告	`cmake --build build`	✅
UserRepository 方法不报错	SQL 语法验证通过	✅
参数化查询无注入风险	所有 SQL 用 `prepare()` + `bindValue()`	✅

SQLite + ORM 框架搭建