NKUsCAT

校园流浪猫领养与信息管理系统
数据库工程课程作业

NKUsCAT 是一个基于 Flask 与 MySQL 的 B/S 架构信息管理系统。项目围绕校园流浪猫救助、档案管理和领养审核展开，将课程要求中的视图、触发器、存储过程和事务操作应用到完整的业务流程中。

系统同时对领养人的手机号和身份证号进行 AES-GCM 对称加密存储。页面展示隐私信息时，后端会先解密，再返回脱敏结果。

1. 功能概览

模块	功能
首页	展示系统定位和主要功能入口
待领养查询	查询当前开放领养的猫咪，支持按性别和健康状态筛选
猫咪档案管理	查看猫咪档案、统计信息，支持名称搜索、状态筛选和新猫登记
领养业务管理	提交领养申请、审核通过、驳回申请、删除申请
隐私信息处理	手机号和身份证号 AES-GCM 加密存储，查询时脱敏展示

页面预览

首页	待领养查询

猫咪档案管理	领养业务管理

2. 技术环境

本项目已在以下环境中完成运行和演示：

组件	版本或取值
操作系统	Windows 11
数据库	MySQL Community Server 8.0.19
Python	3.13.2
Flask	3.1.3
PyMySQL	1.1.2
数据库名	test_db
数据库账号	nkucat
数据库端口	3306
Web 地址	http://127.0.0.1:5000/

数据库连接参数位于 backend/config.py，连接代码位于 backend/db.py。

当前配置用于本地课程演示。若部署到其他环境，应通过环境变量或独立配置文件管理数据库密码和 AES 密钥。

3. 项目结构

NKUsCAT/
├─ backend/
│  ├─ templates/
│  │  └─ index.html                 # 前端页面
│  ├─ app.py                        # Flask 路由和业务逻辑
│  ├─ config.py                     # 本地数据库连接参数
│  ├─ crypto_utils.py               # AES-GCM 加解密与脱敏函数
│  ├─ db.py                         # PyMySQL 连接函数
│  ├─ migrate_privacy_data.py       # 旧版明文隐私数据迁移脚本
│  └─ requirements.txt              # Python 依赖
├─ sql/
│  ├─ init_all.sql                  # 完整初始化入口
│  ├─ ddl.sql                       # 数据表定义
│  ├─ v_available_cats.sql          # 待领养查询视图
│  ├─ trigger.sql                   # 领养申请插入触发器
│  ├─ procedure.sql                 # 领养审核存储过程
│  ├─ nku_cat_seed_realistic.sql    # 初始化演示数据
│  ├─ admin_repair_current_db.sql   # 已有数据库升级脚本
│  ├─ verify_course_requirements.sql# 数据库对象验证脚本
│  ├─ report_demo.sql               # 报告现场演示 SQL
│  └─ query_samples.sql             # 五类查询样例
├─ fig/                             # 报告截图和界面预览
└─ README.md

4. 快速开始

4.1 准备 MySQL 数据库

使用 MySQL 管理员账号登录。若 mysql 已加入 PATH：

mysql --default-character-set=utf8mb4 -h 127.0.0.1 -P 3306 -u root -p

若 PowerShell 提示无法识别 mysql，使用本机 MySQL 可执行文件的完整路径：

& "D:\MySQL\MySQL Server 8.0\bin\mysql.exe" `
  --default-character-set=utf8mb4 `
  -h 127.0.0.1 -P 3306 -u root -p

进入 MySQL 后创建本地演示数据库和业务账号：

CREATE DATABASE IF NOT EXISTS test_db
    DEFAULT CHARACTER SET utf8mb4
    DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'nkucat'@'localhost'
    IDENTIFIED BY 'Nku123456';

ALTER USER 'nkucat'@'localhost'
    IDENTIFIED BY 'Nku123456';

GRANT ALL PRIVILEGES ON test_db.* TO 'nkucat'@'localhost';
FLUSH PRIVILEGES;

4.2 初始化数据库

在 PowerShell 中进入项目根目录，再启动 MySQL 管理员会话：

cd C:\Users\19902\Desktop\NKUsCAT

& "D:\MySQL\MySQL Server 8.0\bin\mysql.exe" `
  --default-character-set=utf8mb4 `
  -h 127.0.0.1 -P 3306 -u root -p test_db

在 MySQL 中执行：

SOURCE C:/Users/19902/Desktop/NKUsCAT/sql/init_all.sql;

init_all.sql 会依次执行：

sql/ddl.sql
sql/v_available_cats.sql
sql/trigger.sql
sql/procedure.sql
sql/nku_cat_seed_realistic.sql

init_all.sql 会按依赖顺序删除并重建业务表，适合首次安装或恢复演示环境。已有业务数据需要保留时，请使用后文的升级方式。

4.3 安装 Python 依赖

项目目录中可以保留本地虚拟环境。若尚未创建虚拟环境：

cd C:\Users\19902\Desktop\NKUsCAT\backend
python -m venv .venv

激活虚拟环境并安装依赖：

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

4.4 配置 AES 演示密钥

在启动程序前设置：

$env:APP_AES_KEY="NfL7cRaLEB/19ad7j3GGUeJCqrWtmmR2rUnONLxt0jY="

初始化演示数据中的手机号和身份证号已经使用该课程演示密钥加密。若更换密钥，已有密文将无法正确解密。

4.5 启动 Web 服务

cd C:\Users\19902\Desktop\NKUsCAT\backend
.\.venv\Scripts\Activate.ps1
$env:APP_AES_KEY="NfL7cRaLEB/19ad7j3GGUeJCqrWtmmR2rUnONLxt0jY="
python app.py

浏览器访问：

http://127.0.0.1:5000/

健康检查接口：

http://127.0.0.1:5000/health

5. 数据库设计

系统包含 8 张关系表。Person 是人员父类，Volunteer 和 Adopter 使用“主键同时作为外键”的方式扩展父类信息。

创建顺序	数据表	主键	外键或约束
1	Cat	cat_id	-
2	Person	person_id	-
3	Volunteer	volunteer_id	volunteer_id -> Person.person_id
4	Adopter	adopter_id	adopter_id -> Person.person_id
5	RescueRecord	rescue_id	cat_id -> Cat.cat_id；volunteer_id -> Volunteer.volunteer_id
6	FosterRecord	foster_id	cat_id -> Cat.cat_id
7	SterilizationRecord	ster_id	cat_id -> Cat.cat_id；cat_id 唯一
8	AdoptionApplication	app_id	cat_id -> Cat.cat_id；adopter_id -> Adopter.adopter_id

数据库关系图：

6. 课程要求对应实现

课程评分项	业务场景	实现位置
含有视图的查询操作	查询当前可领养猫咪，并按性别和健康状态筛选	sql/v_available_cats.sql、backend/app.py
触发器控制下的添加操作	提交待审核领养申请时校验猫咪状态，并将猫咪更新为“领养审核中”	sql/trigger.sql、backend/app.py
存储过程控制下的更新操作	审核通过申请，将申请更新为“已通过”，并将猫咪更新为“已被领养”	sql/procedure.sql、backend/app.py
含有事务应用的删除操作	删除待审核申请；若没有其他待审核申请，将猫咪恢复为“待领养”	backend/app.py

6.1 视图

视图 v_available_cats 将 Cat 与 SterilizationRecord 左连接，并固定筛选当前状态为“待领养”的猫咪：

SELECT * FROM v_available_cats ORDER BY cat_id;

6.2 触发器

触发器 trg_before_insert_adoption_application 在插入申请前执行：

1. 检查猫咪是否存在。
2. 新增“待审核”申请时，要求猫咪当前状态为“待领养”。
3. 插入成功前，将猫咪状态更新为“领养审核中”。
4. 规则不满足时，通过 SIGNAL SQLSTATE '45000' 阻止写入。

6.3 存储过程

存储过程 sp_approve_adoption_application 用于审核通过申请：

1. 通过 FOR UPDATE 锁定申请记录。
2. 仅允许审核“待审核”申请。
3. 将申请状态更新为“已通过”，写入审核日期。
4. 将对应猫咪状态更新为“已被领养”。
5. 任意异常发生时回滚事务。

6.4 事务删除

后端删除申请时使用 autocommit=False、SELECT ... FOR UPDATE、commit() 和 rollback() 保证操作原子性。删除待审核申请后，系统会检查是否仍存在其他待审核申请，再决定是否恢复猫咪状态。

6.5 查询样例

sql/query_samples.sql 提供以下五类查询：

1. 单表查询。
2. 多表连接查询。
3. 多表嵌套查询。
4. EXISTS 查询。
5. 聚合查询。

7. 隐私信息处理

领养人隐私字段包括：

表	字段	处理方式
Person	phone	AES-GCM 加密存储，页面展示时脱敏
Adopter	id_number	AES-GCM 加密存储，页面展示时脱敏

加解密和脱敏函数位于 backend/crypto_utils.py。新增领养人时，后端先调用 encrypt_text()，再将密文写入数据库。

数据库中的密文效果：

若已有数据库仍保存旧版明文演示数据，请先设置 APP_AES_KEY，再执行一次迁移：

cd C:\Users\19902\Desktop\NKUsCAT\backend
.\.venv\Scripts\Activate.ps1
$env:APP_AES_KEY="NfL7cRaLEB/19ad7j3GGUeJCqrWtmmR2rUnONLxt0jY="
python migrate_privacy_data.py

该脚本可重复执行。已经加密的数据会自动跳过。

8. 数据库维护与验证

8.1 保留现有数据并升级数据库对象

若数据库中已经有业务数据，不要执行 init_all.sql。使用 MySQL 管理员账号执行：

SOURCE C:/Users/19902/Desktop/NKUsCAT/sql/admin_repair_current_db.sql;
SOURCE C:/Users/19902/Desktop/NKUsCAT/sql/procedure.sql;

admin_repair_current_db.sql 不会删除业务表。它会重建查询视图和插入触发器，并修复待审核申请对应猫咪的状态。

8.2 验证数据库对象

初始化或升级后执行：

SOURCE C:/Users/19902/Desktop/NKUsCAT/sql/verify_course_requirements.sql;

验证脚本会检查：

1. 查询视图是否存在并可执行。
2. 触发器是否安装。
3. 存储过程是否存在。
4. 待审核申请与猫咪状态是否一致。

最后一项一致性检查应返回 0 行。

8.3 报告现场演示

sql/report_demo.sql 按报告评分项整理了演示 SQL：

区块	演示内容
A	触发器允许正常插入
B	触发器拒绝异常插入
C	存储过程正常审核
D	存储过程拒绝重复审核并回滚
E	事务删除申请并恢复猫咪状态

请按区块分别执行，不要一次性执行整个文件。

9. 后端接口

方法	路径	功能
GET	/	系统首页
GET	/health	服务健康检查
GET	/cats/available	查询当前待领养猫咪
GET	/cats/stats	获取猫咪统计信息
GET	/cats	查询全部猫咪档案
GET	/cats/next-id	生成下一个猫咪编号
GET	/cats/<cat_id>	查询单只猫咪详情
POST	/cats	登记新猫咪
GET	/adopters	查询领养人信息，默认脱敏
POST	/adopters	新增领养人
GET	/applications	查询领养申请
GET	/applications/next-id	生成申请编号
POST	/applications	提交领养申请
POST	/applications/<app_id>/approve	审核通过
POST	/applications/<app_id>/reject	驳回申请
DELETE	/applications/<app_id>	删除申请

申请编号采用 A + 日期 + 当日两位顺序号 的格式，例如：A2026053101。

10. 常见问题

PowerShell 提示无法识别 mysql

MySQL 可执行文件没有加入系统 PATH。请使用完整路径：

& "D:\MySQL\MySQL Server 8.0\bin\mysql.exe" -h 127.0.0.1 -P 3306 -u root -p

提交申请时提示“数据库触发器未安装”

使用 MySQL 管理员账号执行：

SOURCE C:/Users/19902/Desktop/NKUsCAT/sql/admin_repair_current_db.sql;

创建触发器或存储过程时报权限错误

使用 MySQL 管理员账号执行：

SET GLOBAL log_bin_trust_function_creators = 1;

隐私信息无法解密

确认当前 PowerShell 会话已经设置与初始化数据一致的 APP_AES_KEY，然后重新启动 Flask 服务。

11. AI 工具辅助使用说明

本项目在工程作业整理阶段使用 AI 工具辅助完成了部分文档润色、ER 图表达、SQL 查询样例整理和代码检查建议。数据库模式设计、业务流程设计、系统实现、数据初始化、运行调试与最终提交材料均结合课程要求进行确认和修改。AI 生成内容已人工核对，并以项目实际 SQL DDL 与后端业务逻辑为准。