准备好构建数据层了 💪
🎯 今日目标
- 使用draw.io设计完整的数据库ER图
- 编写针对macOS优化的docker-compose.yml
- 创建并启动PostgreSQL容器，验证连接
- 设计符合奥赛场景的详细数据模型
- 编写数据库初始化脚本和数据迁移策略
数据库概念设计
1. 分析奥赛平台的数据需求
首先创建需求分析文档：
# 创建需求文档
$ cd ~/Projects/math-olympiad
$ mkdir -p docs/database
$ cat > docs/database/requirements.md << 'EOF'
# 奥赛AI平台数据需求分析
## 核心实体
1. **用户系统**
- 学生：练习、答题、查看报告
- 管理员：管理题目、查看全局数据
- 家长：查看孩子学习进度（后续扩展）
2. **题目系统**
- 题目：题干、选项、答案、解析
- 知识点：多级分类（如：几何->平面几何->面积计算）
- 难度等级：1-5星
- 来源：AMC8、迎春杯、华杯赛等
3. **学习记录**
- 练习记录：一次练习包含多道题
- 答题记录：每道题的答题详情
- 错题本：自动收集错题
4. **分析系统**
- 能力模型：基于知识点掌握度
- 学习报告：每日/每周/每月报告
- 推荐题目：个性化推荐
## 核心关系
- 学生 1:n 练习记录
- 练习记录 1:n 答题记录
- 题目 1:n 知识点（多对多）
- 知识点 n:n 学生（通过掌握度关联）
EOF
2. 使用draw.io设计ER图
安装draw.io（本地桌面版）：
# 通过Homebrew安装
$ brew install --cask drawio
# 或者使用在线版：https://app.diagrams.net
创建ER图设计（保存为docs/database/er_design.drawio）：
- 使用分层架构：基础表 → 核心表 → 分析表
- 颜色编码：
- 蓝色：用户相关表
- 绿色：题目相关表
- 橙色：学习记录表
- 紫色：分析统计表
实体设计要点：
- users表：区分学生/管理员，预留家长角色
- problems表：支持富文本题干，JSON格式存储选项
- knowledge_points表：树形结构存储知识点
- answer_records表：详细记录答题过程（用时、是否检查等）
上午 11:00-12:30 | 编写macOS优化的Docker配置
3. 创建针对Apple Silicon的docker-compose.yml
$ cat > docker-compose.yml << 'EOF'
version: '3.8'
# 网络配置 - macOS特化
networks:
olympiad-net:
driver: bridge
ipam:
config:
- subnet: 172.20.0.0/16
# 服务定义
services:
# 主数据库 - PostgreSQL 15（推荐稳定版本）
postgres:
image: postgres:15-alpine
container_name: olympiad-postgres
platform: linux/amd64 # Apple Silicon兼容性
restart: unless-stopped
environment:
POSTGRES_USER: admin
POSTGRES_PASSWORD: ${DB_PASSWORD:-olympiad123}
POSTGRES_DB: olympiad
POSTGRES_INITDB_ARGS: "--encoding=UTF8 --locale=C"
TZ: Asia/Shanghai
ports:
- "5432:5432"
volumes:
# macOS特化：使用命名卷提高性能
- postgres_data:/var/lib/postgresql/data
# 初始化脚本
- ./database/init:/docker-entrypoint-initdb.d:ro
# 配置优化
- ./database/postgresql.conf:/etc/postgresql/postgresql.conf:ro
networks:
- olympiad-net
command: >
postgres
-c config_file=/etc/postgresql/postgresql.conf
-c shared_preload_libraries='pg_stat_statements'
healthcheck:
test: ["CMD-SHELL", "pg_isready -U admin -d olympiad"]
interval: 10s
timeout: 5s
retries: 5
start_period: 30s
# 数据库管理界面 - pgAdmin 4
pgadmin:
image: dpage/pgadmin4:latest
container_name: olympiad-pgadmin
platform: linux/amd64
restart: unless-stopped
environment:
PGADMIN_DEFAULT_EMAIL: admin@olympiad.local
PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD:-admin123}
PGADMIN_CONFIG_SERVER_MODE: 'False'
PGADMIN_CONFIG_MASTER_PASSWORD_REQUIRED: 'False'
ports:
- "5050:80"
volumes:
- pgadmin_data:/var/lib/pgadmin
- ./database/pgadmin/servers.json:/pgadmin4/servers.json:ro
networks:
- olympiad-net
depends_on:
postgres:
condition: service_healthy
logging:
driver: "json-file"
options:
max-size: "10m"
max-file: "3"
# Redis缓存（为后续会话和缓存准备）
redis:
image: redis:7-alpine
container_name: olympiad-redis
platform: linux/amd64
restart: unless-stopped
command: redis-server --appendonly yes --requirepass ${REDIS_PASSWORD:-redis123}
ports:
- "6379:6379"
volumes:
- redis_data:/data
networks:
- olympiad-net
healthcheck:
test: ["CMD", "redis-cli", "--raw", "incr", "ping"]
interval: 10s
timeout: 5s
retries: 5
# 卷定义（macOS推荐使用命名卷）
volumes:
postgres_data:
driver: local
driver_opts:
type: none
o: bind
device: ./database/data # 本地目录映射，便于备份
pgadmin_data:
redis_data:
EOF
4. 创建PostgreSQL优化配置文件
# 创建配置目录
$ mkdir -p database
# PostgreSQL配置优化（针对开发环境）
$ cat > database/postgresql.conf << 'EOF'
# macOS开发环境优化配置
# 连接设置
listen_addresses = '*' # 监听所有地址
port = 5432 # 默认端口
max_connections = 100 # 开发环境足够
# 内存设置（针对Apple Silicon 16GB内存优化）
shared_buffers = 256MB # 共享缓冲区
work_mem = 8MB # 每个操作的排序内存
maintenance_work_mem = 64MB # 维护操作内存
# 检查点优化
checkpoint_timeout = 10min # 检查点间隔
checkpoint_completion_target = 0.7 # 完成目标
# 日志设置
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB
log_min_duration_statement = 1000 # 记录执行超过1秒的语句
# 性能监控
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
# WAL设置（写前日志）
wal_level = replica
max_wal_size = 1GB
min_wal_size = 80MB
# 并行查询（针对多核M2 Pro）
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2
# 其他优化
effective_cache_size = 2GB
random_page_cost = 1.1 # SSD优化
effective_io_concurrency = 200
EOF
下午 13:30-15:00 | 数据库初始化脚本
5. 创建数据库初始化脚本
$ mkdir -p database/init
# 主初始化脚本
$ cat > database/init/01-init.sql << 'EOF'
-- Math Olympiad AI Platform 数据库初始化脚本
-- 生成时间: $(date)
-- macOS环境: Apple Silicon
-- 设置数据库参数
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
-- 创建扩展（如果有需要）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm"; -- 用于模糊搜索
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"; -- SQL性能监控
-- 设置搜索路径
SET search_path = public, pg_catalog;
-- 注释
COMMENT ON DATABASE olympiad IS '奥赛AI平台主数据库';
COMMENT ON SCHEMA public IS '标准公共模式';
EOF
# 创建表结构脚本
$ cat > database/init/02-tables.sql << 'EOF'
-- ============================================
-- 表结构定义
-- 遵循命名规范：复数表名，小写加下划线
-- ============================================
-- 用户表
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE,
hashed_password VARCHAR(255) NOT NULL,
full_name VARCHAR(100),
role VARCHAR(20) NOT NULL DEFAULT 'student'
CHECK (role IN ('student', 'teacher', 'admin', 'parent')),
-- 学生特定字段
grade VARCHAR(20), -- 年级：如 '初一'、'初二'
school VARCHAR(100), -- 学校
-- 账户状态
is_active BOOLEAN DEFAULT TRUE,
is_verified BOOLEAN DEFAULT FALSE,
-- 时间戳
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMP WITH TIME ZONE,
-- 元数据
metadata JSONB DEFAULT '{}'::jsonb
);
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.role IS '用户角色：student(学生), teacher(老师), admin(管理员), parent(家长)';
COMMENT ON COLUMN users.metadata IS '扩展元数据，如头像URL、偏好设置等';
-- 知识点表（树形结构）
CREATE TABLE knowledge_points (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL, -- 知识点名称
code VARCHAR(50) UNIQUE NOT NULL, -- 知识点编码，如 'geometry.plane.area'
parent_id INTEGER REFERENCES knowledge_points(id) ON DELETE CASCADE,
level INTEGER NOT NULL DEFAULT 1, -- 层级：1-一级，2-二级...
description TEXT,
-- 排序和权重
sort_order INTEGER DEFAULT 0,
weight FLOAT DEFAULT 1.0, -- 在考试中的权重
-- 统计信息（预计算）
problem_count INTEGER DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE knowledge_points IS '知识点表（支持多级分类）';
COMMENT ON COLUMN knowledge_points.code IS '知识点编码，用于快速查询和关联，如 algebra.equation.quadratic';
-- 题目表
CREATE TABLE problems (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL, -- 题目标题
content TEXT NOT NULL, -- 题目内容（支持Markdown）
content_type VARCHAR(20) DEFAULT 'text'
CHECK (content_type IN ('text', 'markdown', 'latex')),
-- 选项（JSON格式）
options JSONB NOT NULL DEFAULT '{}'::jsonb,
-- 答案和解析
correct_answer VARCHAR(10) NOT NULL, -- 如 'A', 'B', 'C', 'D'
solution TEXT, -- 解题步骤
solution_type VARCHAR(20) DEFAULT 'text',
-- 难度和分类
difficulty INTEGER NOT NULL DEFAULT 3
CHECK (difficulty >= 1 AND difficulty <= 5),
source_type VARCHAR(50), -- 来源类型：'AMC8', '迎春杯', '华杯赛'
source_year INTEGER, -- 年份
source_detail VARCHAR(100), -- 详细来源
-- 预估属性
estimated_time INTEGER, -- 预估解题时间（秒）
success_rate FLOAT, -- 历史正确率
-- 状态控制
is_published BOOLEAN DEFAULT FALSE,
is_deleted BOOLEAN DEFAULT FALSE,
-- 审核信息
reviewed_by INTEGER REFERENCES users(id),
reviewed_at TIMESTAMP WITH TIME ZONE,
review_status VARCHAR(20) DEFAULT 'pending'
CHECK (review_status IN ('pending', 'approved', 'rejected')),
-- 统计信息
total_attempts INTEGER DEFAULT 0,
correct_attempts INTEGER DEFAULT 0,
-- 时间戳
created_by INTEGER REFERENCES users(id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- 全文搜索支持
search_vector tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(content, '')), 'B') ||
setweight(to_tsvector('english', coalesce(solution, '')), 'C')
) STORED
);
COMMENT ON TABLE problems IS '题目表';
COMMENT ON COLUMN problems.options IS '题目选项，JSON格式：{"A": "选项A内容", "B": "选项B内容", ...}';
COMMENT ON COLUMN problems.search_vector IS '全文搜索向量，用于快速搜索题目';
-- 题目-知识点关联表（多对多）
CREATE TABLE problem_knowledge_points (
problem_id INTEGER NOT NULL REFERENCES problems(id) ON DELETE CASCADE,
knowledge_point_id INTEGER NOT NULL REFERENCES knowledge_points(id) ON DELETE CASCADE,
is_primary BOOLEAN DEFAULT FALSE, -- 是否主要知识点
weight FLOAT DEFAULT 1.0, -- 在该题中的权重
PRIMARY KEY (problem_id, knowledge_point_id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE problem_knowledge_points IS '题目-知识点关联表';
-- 练习会话表
CREATE TABLE practice_sessions (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- 练习配置
session_type VARCHAR(50) NOT NULL DEFAULT 'random'
CHECK (session_type IN ('random', 'knowledge_point', 'difficulty', 'exam')),
config JSONB DEFAULT '{}'::jsonb, -- 练习配置：如知识点、题数、难度
-- 状态
status VARCHAR(20) NOT NULL DEFAULT 'in_progress'
CHECK (status IN ('in_progress', 'completed', 'abandoned')),
-- 统计信息
total_questions INTEGER DEFAULT 0,
completed_questions INTEGER DEFAULT 0,
correct_questions INTEGER DEFAULT 0,
-- 时间跟踪
started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
completed_at TIMESTAMP WITH TIME ZONE,
total_duration INTEGER, -- 总用时（秒）
-- 性能指标
average_time_per_question FLOAT,
accuracy_rate FLOAT,
-- 元数据
device_info JSONB DEFAULT '{}'::jsonb,
ip_address INET
);
COMMENT ON TABLE practice_sessions IS '练习会话表';
COMMENT ON COLUMN practice_sessions.config IS '练习配置JSON，如 {"knowledge_points": [1,2,3], "difficulty": [3,4], "count": 20}';
-- 答题记录表（核心学习数据）
CREATE TABLE answer_records (
id SERIAL PRIMARY KEY,
session_id INTEGER NOT NULL REFERENCES practice_sessions(id) ON DELETE CASCADE,
problem_id INTEGER NOT NULL REFERENCES problems(id),
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- 答题详情
user_answer VARCHAR(10), -- 用户答案
is_correct BOOLEAN,
confidence_level INTEGER CHECK (confidence_level >= 1 AND confidence_level <= 5), -- 自信程度
-- 时间分析
time_spent INTEGER NOT NULL, -- 用时（秒）
first_response_time INTEGER, -- 第一次响应时间
review_count INTEGER DEFAULT 0, -- 检查次数
-- 步骤数据（为后续分析预留）
steps JSONB DEFAULT '[]'::jsonb, -- 解题步骤记录
hesitation_points JSONB DEFAULT '[]'::jsonb, -- 犹豫点
-- 反馈
user_feedback VARCHAR(20) CHECK (user_feedback IN ('too_easy', 'appropriate', 'too_hard', 'unclear')),
note TEXT, -- 用户备注
-- 时间戳
answered_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
-- 索引字段（优化查询）
knowledge_point_ids INTEGER[] -- 该题关联的知识点ID数组
);
COMMENT ON TABLE answer_records IS '答题记录表（核心学习数据）';
COMMENT ON COLUMN answer_records.steps IS '解题步骤记录，格式：[{"time": 10, "action": "read_question"}, ...]';
COMMENT ON COLUMN answer_records.knowledge_point_ids IS '知识点ID数组，用于快速查询和分析';
-- 错题本表
CREATE TABLE mistake_collections (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
problem_id INTEGER NOT NULL REFERENCES problems(id),
-- 错题信息
first_wrong_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_wrong_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
wrong_count INTEGER DEFAULT 1,
-- 掌握情况
is_mastered BOOLEAN DEFAULT FALSE,
mastered_at TIMESTAMP WITH TIME ZONE,
-- 分析信息
error_types VARCHAR(50)[], -- 错误类型：如 'careless', 'concept_unclear', 'calculation'
weak_knowledge_points INTEGER[], -- 薄弱知识点
-- 练习计划
next_review_at TIMESTAMP WITH TIME ZONE, -- 下次复习时间
review_count INTEGER DEFAULT 0,
UNIQUE(user_id, problem_id),
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE mistake_collections IS '错题本表';
-- 学生能力画像表
CREATE TABLE student_profiles (
id SERIAL PRIMARY KEY,
user_id INTEGER UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- 总体统计
total_practice_time INTEGER DEFAULT 0, -- 总练习时间（秒）
total_problems_attempted INTEGER DEFAULT 0,
overall_accuracy FLOAT DEFAULT 0,
-- 知识点掌握度（JSON格式）
knowledge_mastery JSONB DEFAULT '{}'::jsonb,
-- 格式: {"knowledge_point_id": {"score": 0.85, "attempts": 10, "last_practiced": "2024-05-21"}}
-- 难度表现
difficulty_performance JSONB DEFAULT '{}'::jsonb,
-- 学习习惯
preferred_practice_time VARCHAR(20), -- 偏好练习时间
average_session_duration INTEGER, -- 平均单次练习时长
-- 趋势数据
weekly_progress JSONB DEFAULT '{}'::jsonb,
monthly_trend JSONB DEFAULT '{}'::jsonb,
-- 推荐系统相关
recommended_problems INTEGER[], -- 推荐题目ID列表
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_calculated_at TIMESTAMP WITH TIME ZONE -- 上次计算时间
);
COMMENT ON TABLE student_profiles IS '学生能力画像表';
COMMENT ON COLUMN student_profiles.knowledge_mastery IS '知识点掌握度，JSON格式存储';
-- 系统配置表
CREATE TABLE system_configs (
id SERIAL PRIMARY KEY,
config_key VARCHAR(100) UNIQUE NOT NULL,
config_value JSONB NOT NULL,
description TEXT,
is_public BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
COMMENT ON TABLE system_configs IS '系统配置表';
EOF
下午 15:00-16:30 | 索引、触发器与初始化数据
6. 创建索引和触发器脚本
$ cat > database/init/03-indexes.sql << 'EOF'
-- ============================================
-- 索引定义
-- 针对查询模式优化
-- ============================================
-- users表索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_created_at ON users(created_at DESC);
-- problems表索引
CREATE INDEX idx_problems_difficulty ON problems(difficulty);
CREATE INDEX idx_problems_source_type ON problems(source_type);
CREATE INDEX idx_problems_source_year ON problems(source_year);
CREATE INDEX idx_problems_is_published ON problems(is_published) WHERE is_published = TRUE;
CREATE INDEX idx_problems_created_at ON problems(created_at DESC);
CREATE INDEX idx_problems_review_status ON problems(review_status);
-- 全文搜索索引
CREATE INDEX idx_problems_search ON problems USING GIN(search_vector);
-- knowledge_points表索引
CREATE INDEX idx_knowledge_points_parent_id ON knowledge_points(parent_id);
CREATE INDEX idx_knowledge_points_code ON knowledge_points(code);
CREATE INDEX idx_knowledge_points_level ON knowledge_points(level);
-- problem_knowledge_points表索引
CREATE INDEX idx_pkp_knowledge_point_id ON problem_knowledge_points(knowledge_point_id);
CREATE INDEX idx_pkp_problem_id ON problem_knowledge_points(problem_id);
-- practice_sessions表索引
CREATE INDEX idx_sessions_user_id ON practice_sessions(user_id);
CREATE INDEX idx_sessions_status ON practice_sessions(status);
CREATE INDEX idx_sessions_started_at ON practice_sessions(started_at DESC);
CREATE INDEX idx_sessions_user_status ON practice_sessions(user_id, status);
-- answer_records表索引（查询最频繁的表）
CREATE INDEX idx_answers_user_id ON answer_records(user_id);
CREATE INDEX idx_answers_problem_id ON answer_records(problem_id);
CREATE INDEX idx_answers_session_id ON answer_records(session_id);
CREATE INDEX idx_answers_is_correct ON answer_records(is_correct);
CREATE INDEX idx_answers_answered_at ON answer_records(answered_at DESC);
CREATE INDEX idx_answers_user_problem ON answer_records(user_id, problem_id);
CREATE INDEX idx_answers_knowledge_points ON answer_records USING GIN(knowledge_point_ids);
-- mistake_collections表索引
CREATE INDEX idx_mistakes_user_id ON mistake_collections(user_id);
CREATE INDEX idx_mistakes_is_mastered ON mistake_collections(is_mastered);
CREATE INDEX idx_mistakes_next_review ON mistake_collections(next_review_at) WHERE NOT is_mastered;
-- student_profiles表索引
CREATE INDEX idx_profiles_user_id ON student_profiles(user_id);
CREATE INDEX idx_profiles_updated_at ON student_profiles(updated_at DESC);
-- ============================================
-- 触发器函数
-- ============================================
-- 自动更新updated_at时间戳
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ language 'plpgsql';
-- 为所有需要updated_at的表创建触发器
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_knowledge_points_updated_at BEFORE UPDATE ON knowledge_points
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_problems_updated_at BEFORE UPDATE ON problems
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_mistake_collections_updated_at BEFORE UPDATE ON mistake_collections
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_student_profiles_updated_at BEFORE UPDATE ON student_profiles
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_system_configs_updated_at BEFORE UPDATE ON system_configs
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- 自动更新知识点题目数量
CREATE OR REPLACE FUNCTION update_knowledge_point_problem_count()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE knowledge_points
SET problem_count = problem_count + 1
WHERE id = NEW.knowledge_point_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE knowledge_points
SET problem_count = problem_count - 1
WHERE id = OLD.knowledge_point_id;
END IF;
RETURN NULL;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_kp_count AFTER INSERT OR DELETE ON problem_knowledge_points
FOR EACH ROW EXECUTE FUNCTION update_knowledge_point_problem_count();
-- 自动填充answer_records的知识点数组
CREATE OR REPLACE FUNCTION fill_answer_knowledge_points()
RETURNS TRIGGER AS $$
BEGIN
SELECT array_agg(knowledge_point_id)
INTO NEW.knowledge_point_ids
FROM problem_knowledge_points
WHERE problem_id = NEW.problem_id;
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER fill_knowledge_points BEFORE INSERT ON answer_records
FOR EACH ROW EXECUTE FUNCTION fill_answer_knowledge_points();
EOF
7. 创建初始化数据脚本
$ cat > database/init/04-initial-data.sql << 'EOF'
-- ============================================
-- 初始化数据
-- ============================================
-- 插入系统管理员
INSERT INTO users (username, email, hashed_password, full_name, role, is_active, is_verified)
VALUES
('admin', 'admin@olympiad.local', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', '系统管理员', 'admin', TRUE, TRUE),
('demo_student', 'student@demo.local', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', '演示学生', 'student', TRUE, TRUE),
('demo_teacher', 'teacher@demo.local', '$2b$12$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', '演示老师', 'teacher', TRUE, TRUE);
-- 插入初始知识点（奥赛数学分类）
INSERT INTO knowledge_points (name, code, parent_id, level, description, sort_order) VALUES
-- 一级知识点
('算术', 'arithmetic', NULL, 1, '基础算术运算', 1),
('代数', 'algebra', NULL, 1, '代数表达式和方程', 2),
('几何', 'geometry', NULL, 1, '平面和立体几何', 3),
('组合数学', 'combinatorics', NULL, 1, '计数和概率', 4),
('数论', 'number_theory', NULL, 1, '整数性质', 5),
-- 二级知识点 - 算术
('整数运算', 'arithmetic.integer', 1, 2, '整数四则运算', 1),
('分数小数', 'arithmetic.fraction', 1, 2, '分数小数运算', 2),
('百分数', 'arithmetic.percentage', 1, 2, '百分数应用', 3),
('比例', 'arithmetic.ratio', 1, 2, '比例和比例分配', 4),
-- 二级知识点 - 代数
('代数式', 'algebra.expression', 2, 2, '代数式化简', 1),
('方程', 'algebra.equation', 2, 2, '一元一次方程', 2),
('方程组', 'algebra.equation_system', 2, 2, '二元一次方程组', 3),
('不等式', 'algebra.inequality', 2, 2, '一元一次不等式', 4),
-- 二级知识点 - 几何
('平面几何', 'geometry.plane', 3, 2, '平面图形性质', 1),
('立体几何', 'geometry.solid', 3, 2, '立体图形性质', 2),
('几何变换', 'geometry.transformation', 3, 2, '平移旋转对称', 3),
('坐标几何', 'geometry.coordinate', 3, 2, '坐标系和图形', 4),
-- 二级知识点 - 组合数学
('计数原理', 'combinatorics.counting', 4, 2, '排列组合', 1),
('概率', 'combinatorics.probability', 4, 2, '简单概率计算', 2),
('逻辑推理', 'combinatorics.logic', 4, 2, '逻辑推理问题', 3),
-- 二级知识点 - 数论
('整除性质', 'number_theory.divisibility', 5, 2, '整除和余数', 1),
('质数与合数', 'number_theory.prime', 5, 2, '质数合数性质', 2),
('最大公约数', 'number_theory.gcd', 5, 2, '最大公约数应用', 3);
-- 插入系统配置
INSERT INTO system_configs (config_key, config_value, description, is_public) VALUES
('system.name', '"Math Olympiad AI Platform"', '系统名称', TRUE),
('system.version', '"1.0.0"', '系统版本', TRUE),
('practice.default_question_count', '20', '默认练习题目数量', TRUE),
('practice.time_limit', '3600', '默认练习时间限制（秒）', TRUE),
('difficulty.levels', '["入门", "基础", "中等", "困难", "挑战"]', '难度级别名称', TRUE),
('knowledge_point.weight_default', '1.0', '知识点默认权重', FALSE),
('recommendation.enabled', 'false', '是否启用智能推荐', FALSE);
-- 插入示例题目（AMC8风格）
INSERT INTO problems (title, content, options, correct_answer, difficulty, source_type, source_year, is_published) VALUES
('简单的算术运算',
'小明有15个苹果，他给了小红3个，又给了小刚5个，最后自己吃了1个。请问小明现在还有几个苹果？',
'{"A": "6个", "B": "7个", "C": "8个", "D": "9个", "E": "10个"}',
'A',
1,
'AMC8',
2020,
TRUE),
('分数加法',
'计算：$\frac{1}{2} + \frac{1}{3} = $',
'{"A": "$\frac{2}{5}$", "B": "$\frac{5}{6}$", "C": "$\frac{3}{5}$", "D": "$\frac{1}{6}$", "E": "$\frac{5}{3}$"}',
'B',
2,
'AMC8',
2019,
TRUE),
('几何面积',
'一个正方形的边长是6厘米，那么它的面积是多少平方厘米？',
'{"A": "12", "B": "24", "C": "30", "D": "36", "E": "42"}',
'D',
1,
'迎春杯',
2021,
TRUE),
('代数方程',
'如果 $2x + 5 = 15$，那么 $x$ 的值是多少？',
'{"A": "3", "B": "4", "C": "5", "D": "6", "E": "7"}',
'C',
2,
'华杯赛',
2022,
TRUE),
('逻辑推理',
'三个数的平均数是12，其中两个数分别是8和15，第三个数是多少？',
'{"A": "10", "B": "11", "C": "12", "D": "13", "E": "14"}',
'D',
3,
'AMC8',
2023,
TRUE);
-- 关联题目和知识点
INSERT INTO problem_knowledge_points (problem_id, knowledge_point_id, is_primary) VALUES
(1, 6, TRUE), -- 算术 -> 整数运算
(1, 7, FALSE), -- 算术 -> 分数小数（次要）
(2, 7, TRUE), -- 算术 -> 分数小数
(3, 13, TRUE), -- 几何 -> 平面几何
(4, 10, TRUE), -- 代数 -> 方程
(5, 6, TRUE); -- 算术 -> 整数运算
-- 创建演示学生能力画像
INSERT INTO student_profiles (user_id)
SELECT id FROM users WHERE username = 'demo_student';
-- 更新知识点题目计数
UPDATE knowledge_points kp
SET problem_count = (
SELECT COUNT(DISTINCT problem_id)
FROM problem_knowledge_points pkp
WHERE pkp.knowledge_point_id = kp.id
);
-- 创建初始练习会话
INSERT INTO practice_sessions (user_id, session_type, config, status, total_questions, completed_questions, started_at)
SELECT
id,
'random',
'{"difficulty": [1,2,3], "count": 5}'::jsonb,
'completed',
5,
5,
CURRENT_TIMESTAMP - INTERVAL '1 day'
FROM users
WHERE username = 'demo_student';
-- 创建初始答题记录
INSERT INTO answer_records (session_id, problem_id, user_id, user_answer, is_correct, time_spent, answered_at)
SELECT
ps.id,
p.id,
u.id,
CASE p.id
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'D'
WHEN 4 THEN 'C'
WHEN 5 THEN 'A' -- 故意答错一题
END,
CASE p.id
WHEN 5 THEN FALSE
ELSE TRUE
END,
CASE p.id
WHEN 1 THEN 45
WHEN 2 THEN 60
WHEN 3 THEN 30
WHEN 4 THEN 75
WHEN 5 THEN 90
END,
CURRENT_TIMESTAMP - INTERVAL '1 day' + (p.id * INTERVAL '5 minutes')
FROM practice_sessions ps
CROSS JOIN problems p
CROSS JOIN users u
WHERE u.username = 'demo_student'
AND ps.user_id = u.id
AND p.id BETWEEN 1 AND 5;
-- 自动生成错题记录
INSERT INTO mistake_collections (user_id, problem_id, first_wrong_at, last_wrong_at, wrong_count, error_types)
SELECT
ar.user_id,
ar.problem_id,
MIN(ar.answered_at),
MAX(ar.answered_at),
COUNT(*),
ARRAY['concept_unclear']
FROM answer_records ar
WHERE ar.is_correct = FALSE
GROUP BY ar.user_id, ar.problem_id
ON CONFLICT (user_id, problem_id) DO UPDATE
SET
last_wrong_at = EXCLUDED.last_wrong_at,
wrong_count = mistake_collections.wrong_count + EXCLUDED.wrong_count;
EOF
下午 16:30-17:30 | pgAdmin配置与容器启动
8. 配置pgAdmin连接信息
$ mkdir -p database/pgadmin
$ cat > database/pgadmin/servers.json << 'EOF'
{
"Servers": {
"1": {
"Name": "Olympiad DB",
"Group": "Servers",
"Host": "postgres",
"Port": 5432,
"MaintenanceDB": "postgres",
"Username": "admin",
"Password": "olympiad123",
"SSLMode": "prefer",
"PassFile": "",
"SSLCert": "",
"SSLKey": "",
"SSLRootCert": "",
"SSLCompression": 0,
"ConnectTimeout": 10,
"UseSSHTunnel": 0,
"TunnelPort": "22",
"TunnelAuthentication": 0,
"KerberosAuthentication": false,
"ConnectionParameters": {
"sslmode": "prefer",
"connect_timeout": 10
}
}
}
}
EOF
9. 创建环境变量文件
$ cat > .env << 'EOF'
# macOS开发环境配置
# 数据库配置
DB_HOST=localhost
DB_PORT=5432
DB_NAME=olympiad
DB_USER=admin
DB_PASSWORD=olympiad123
# Redis配置
REDIS_HOST=localhost
REDIS_PORT=6379
REDIS_PASSWORD=redis123
# 应用配置
APP_ENV=development
APP_SECRET_KEY=$(openssl rand -hex 32)
JWT_SECRET_KEY=$(openssl rand -hex 32)
# 前端配置
VITE_API_BASE_URL=http://localhost:8000
VITE_APP_TITLE=奥赛AI平台
# Docker配置
COMPOSE_PROJECT_NAME=olympiad
# macOS特化配置
DOCKER_DEFAULT_PLATFORM=linux/amd64
EOF
# 生成安全的密钥
$ echo "APP_SECRET_KEY=$(openssl rand -hex 32)" >> .env
$ echo "JWT_SECRET_KEY=$(openssl rand -hex 32)" >> .env
10. 启动Docker容器并验证
# 给脚本执行权限
$ chmod +x scripts/dev/setup_mac.sh
# 启动Docker Desktop（如果还没启动）
$ open /Applications/Docker.app
# 等待Docker启动后，启动服务
$ docker-compose up -d
# 这里会出现2类错误 第1类是启动时候email没有验证，记得要让docker的email变成验证状态
# 第2类是发现database下面的data文件夹找不到 需要把工程加入到docker的设置-Resources-file Sharing中并且 apply时候会显示消失
# 查看容器状态
$ docker-compose ps
NAME COMMAND SERVICE STATUS PORTS
olympiad-postgres "docker-entrypoint.s…" postgres running 0.0.0.0:5432->5432/tcp
olympiad-pgadmin "/entrypoint.sh" pgadmin running 0.0.0.0:5050->80/tcp
olympiad-redis "docker-entrypoint.s…" redis running 0.0.0.0:6379->6379/tcp
# 查看日志（确保初始化完成）
$ docker-compose logs postgres | tail -20
11. 验证数据库连接
# 方法1: 使用psql直接连接
$ PGPASSWORD=olympiad123 psql -h localhost -U admin -d olympiad -c "\dt"
List of relations
Schema | Name | Type | Owner
--------+---------------------------+-------+-------
public | answer_records | table | admin
public | knowledge_points | table | admin
public | mistake_collections | table | admin
public | practice_sessions | table | admin
public | problem_knowledge_points | table | admin
public | problems | table | admin
public | student_profiles | table | admin
public | system_configs | table | admin
public | users | table | admin
(9 rows)
# 方法2: 使用Python脚本验证
$ cat > scripts/test_db_connection.py << 'EOF'
import psycopg2
import os
from dotenv import load_dotenv
load_dotenv()
try:
conn = psycopg2.connect(
host=os.getenv('DB_HOST'),
port=os.getenv('DB_PORT'),
database=os.getenv('DB_NAME'),
user=os.getenv('DB_USER'),
password=os.getenv('DB_PASSWORD')
)
cursor = conn.cursor()
cursor.execute("SELECT version();")
db_version = cursor.fetchone()
cursor.execute("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';")
table_count = cursor.fetchone()
print("✅ 数据库连接成功！")
print(f"📊 PostgreSQL版本: {db_version[0]}")
print(f"📈 数据表数量: {table_count[0]}")
# 检查各个表的数据量
tables = ['users', 'problems', 'knowledge_points', 'answer_records']
for table in tables:
cursor.execute(f"SELECT COUNT(*) FROM {table};")
count = cursor.fetchone()[0]
print(f" {table}: {count} 条记录")
cursor.close()
conn.close()
except Exception as e:
print(f"❌ 数据库连接失败: {e}")
EOF
$ python scripts/test_db_connection.py
✅ 数据库连接成功！
📊 PostgreSQL版本: PostgreSQL 15.4 on aarch64-apple-darwin20.6.0, compiled by Apple clang version 14.0.3 (clang-1403.0.22.14.1), 64-bit
📈 数据表数量: 9
users: 3 条记录
problems: 5 条记录
knowledge_points: 24 条记录
answer_records: 5 条记录
12. 访问pgAdmin管理界面
- 打开浏览器访问：http://localhost:5050
- 登录账号：admin@olympiad.local / admin123
- 添加服务器：
- Name: Olympiad DB
- Host: postgres (容器名)
- Port: 5432
- Username: admin
- Password: olympiad123
#如果发现启动后 登陆不上
#首先就是查看日志
#docker logs olympiad-pgadmin --tail 100
13. 创建macOS数据库管理脚本
$ cat > scripts/db/mac_db_manager.sh << 'EOF'
#!/bin/bash
# macOS数据库管理脚本
set -e
case "$1" in
"start")
echo "🚀 启动数据库服务..."
docker-compose up -d postgres pgadmin redis
echo "✅ 服务已启动"
echo " PostgreSQL: localhost:5432"
echo " pgAdmin: http://localhost:5050"
echo " Redis: localhost:6379"
;;
"stop")
echo "🛑 停止数据库服务..."
docker-compose down
echo "✅ 服务已停止"
;;
"restart")
echo "🔄 重启数据库服务..."
docker-compose restart
echo "✅ 服务已重启"
;;
"status")
echo "📊 服务状态:"
docker-compose ps
;;
"logs")
echo "📝 查看日志..."
docker-compose logs -f postgres
;;
"reset")
read -p "⚠️ 确定要重置数据库吗？所有数据将被清除！(y/N): " confirm
if [[ $confirm == [yY] || $confirm == [yY][eE][sS] ]]; then
echo "🗑️ 重置数据库..."
docker-compose down -v
docker-compose up -d postgres
sleep 5
echo "✅ 数据库已重置"
else
echo "❌ 取消重置"
fi
;;
"backup")
echo "💾 备份数据库..."
BACKUP_FILE="backup_$(date +%Y%m%d_%H%M%S).sql"
docker-compose exec -T postgres pg_dump -U admin olympiad > "database/backups/${BACKUP_FILE}"
echo "✅ 备份完成: database/backups/${BACKUP_FILE}"
;;
"restore")
if [ -z "$2" ]; then
echo "❌ 请指定备份文件: $0 restore <backup_file>"
exit 1
fi
echo "🔄 恢复数据库..."
docker-compose exec -T postgres psql -U admin -d olympiad < "$2"
echo "✅ 恢复完成"
;;
"shell")
echo "🐚 进入PostgreSQL命令行..."
docker-compose exec postgres psql -U admin -d olympiad
;;
*)
echo "📖 用法: $0 {start|stop|restart|status|logs|reset|backup|restore|shell}"
echo ""
echo "命令说明:"
echo " start 启动数据库服务"
echo " stop 停止数据库服务"
echo " restart 重启数据库服务"
echo " status 查看服务状态"
echo " logs 查看日志"
echo " reset 重置数据库（危险！）"
echo " backup 备份数据库"
echo " restore 恢复数据库"
echo " shell 进入PostgreSQL命令行"
exit 1
;;
esac
EOF
$ chmod +x scripts/db/mac_db_manager.sh
# 创建备份目录
$ mkdir -p database/backups
# 测试脚本
$ ./scripts/db/mac_db_manager.sh status
✅ 今日完成清单
- 使用draw.io完成完整的数据库ER图设计
- 编写针对Apple Silicon优化的docker-compose.yml
- 创建PostgreSQL性能优化配置文件
- 设计9个核心数据表的详细结构
- 编写完整的数据库初始化脚本（表结构+索引+触发器）
- 插入初始的演示数据（用户+知识点+题目）
- 配置pgAdmin管理界面连接
- 创建环境变量配置文件
- 成功启动PostgreSQL、pgAdmin、Redis容器
- 验证数据库连接和初始数据
- 创建macOS特化的数据库管理脚本
🐛 macOS特有问题与解决方案
问题1：Docker端口占用
现象：5432端口被本地PostgreSQL占用
$ lsof -i :5432
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 1234 user 12u IPv6 0x1234567890 0t0 TCP *:postgresql (LISTEN)
解决方案：
# 停止本地PostgreSQL服务
$ brew services stop postgresql@15
# 或者卸载Homebrew的PostgreSQL（因为我们用Docker）
$ brew uninstall postgresql@15
问题2：Apple Silicon镜像兼容性
现象：某些镜像启动失败或性能差
解决方案：
- 明确指定平台：
platform: linux/amd64 - 使用兼容性更好的alpine版本
- 在Docker Desktop中启用VirtioFS加速（设置 → Experimental Features）
问题3：文件权限问题
现象：Docker容器无法写入本地目录
解决方案：
# 查看目录权限
$ ls -la database/data
# 修复权限
$ chmod 777 database/data # 开发环境可以宽松
# 或者设置正确的所有权
$ sudo chown -R $(whoami):staff database
💡 数据库设计亮点
1. 知识点树形结构设计
- 使用
parent_id实现无限层级 code字段便于程序化处理（如geometry.plane.area）level字段优化查询效率
2. 答题记录详细追踪
- 记录
time_spent和first_response_time分析思考过程 hesitation_points字段为后续AI分析预留knowledge_point_ids数组优化关联查询
3. JSONB字段的巧妙使用
problems.options: 灵活存储题目选项student_profiles.knowledge_mastery: 动态存储掌握度practice_sessions.config: 可扩展的练习配置
4. macOS特化优化
- 针对Apple Silicon指定平台
- 使用命名卷提高I/O性能
- 配置PostgreSQL参数适应M芯片架构
🧠 今日学习收获
-
PostgreSQL高级特性：
- JSONB字段的索引和查询优化
- 数组字段的GIN索引
- 触发器与存储过程
-
Docker on macOS最佳实践：
- Apple Silicon兼容性配置
- 资源限制与性能优化
- 开发环境容器化策略
-
数据库设计原则：
- 为分析优化的schema设计
- 兼顾灵活性和性能
- 预留扩展字段的策略
📈 下一工作时计划（3）
- 搭建FastAPI后端项目结构
- 实现SQLAlchemy ORM模型
- 创建用户认证系统（注册/登录）
- 实现题目的CRUD API
- 编写API文档和测试
🎯 明日重点关注
- SQLAlchemy模型映射：确保与今天设计的表结构完全对应
- JWT认证实现：安全的用户认证机制
- Pydantic模式验证：API请求/响应验证
- macOS开发体验：热重载、调试配置
📝 今日金句
"好的数据库设计不是终点，而是可扩展、可维护系统的基础。"
🌟 心情小结
看着精心设计的数据库表结构和完美运行的Docker容器，成就感满满。特别是看到ER图中各个实体间的清晰关系，以及通过pgAdmin直观地查看初始数据，对项目的信心大增。Mac上的开发体验一如既往地流畅，明天可以专注于业务逻辑实现了！
