单元测试模块
约 1573 字大约 5 分钟
2025-08-11
概述
单元测试模块是测评系统的核心,管理学生的测评记录、答题详情和成绩统计。该系统支持多种测评模式,包括练习、测试和考试,并提供详细的学习分析数据。
表设计
student_assessments 表 - 学生测评表
DDL 定义
-- 创建测评状态枚举
CREATE TYPE assessment_status AS ENUM ('in_progress', 'completed', 'abandoned');
CREATE TABLE IF NOT EXISTS public.student_assessments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
student_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
question_bank_id UUID NOT NULL REFERENCES public.question_banks(id) ON DELETE CASCADE,
status assessment_status NOT NULL DEFAULT 'in_progress',
total_questions INTEGER NOT NULL,
answered_questions INTEGER NOT NULL DEFAULT 0,
correct_answers INTEGER NOT NULL DEFAULT 0,
total_score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
max_possible_score DECIMAL(8,2) NOT NULL,
percentage_score DECIMAL(5,2) GENERATED ALWAYS AS (
CASE WHEN max_possible_score > 0
THEN (total_score / max_possible_score * 100)::DECIMAL(5,2)
ELSE 0 END
) STORED,
time_spent INTEGER NOT NULL DEFAULT 0, -- 用时(秒)
attempt_number INTEGER NOT NULL DEFAULT 1,
started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
completed_at TIMESTAMP WITH TIME ZONE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);字段说明
| 字段名 | 数据类型 | 约束 | 默认值 | 描述 |
|---|---|---|---|---|
id | UUID | PRIMARY KEY | gen_random_uuid() | 测评记录唯一标识 |
student_id | UUID | NOT NULL, FOREIGN KEY | - | 学生ID,关联auth.users表 |
question_bank_id | UUID | NOT NULL, FOREIGN KEY | - | 题库ID,关联question_banks表 |
status | assessment_status | NOT NULL | 'in_progress' | 测评状态 |
total_questions | INTEGER | NOT NULL | - | 总题目数量 |
answered_questions | INTEGER | NOT NULL | 0 | 已答题目数量 |
correct_answers | INTEGER | NOT NULL | 0 | 正确答案数量 |
total_score | DECIMAL(8,2) | NOT NULL | 0.00 | 总得分 |
max_possible_score | DECIMAL(8,2) | NOT NULL | - | 最高可能得分 |
percentage_score | DECIMAL(5,2) | GENERATED COLUMN | - | 百分制得分(自动计算) |
time_spent | INTEGER | NOT NULL | 0 | 用时(秒) |
attempt_number | INTEGER | NOT NULL | 1 | 尝试次数 |
started_at | TIMESTAMPTZ | NOT NULL | NOW() | 开始时间 |
completed_at | TIMESTAMPTZ | NULLABLE | NULL | 完成时间 |
created_at | TIMESTAMPTZ | NOT NULL | NOW() | 记录创建时间 |
测评状态枚举
| 枚举值 | 中文名称 | 描述 | 业务含义 |
|---|---|---|---|
in_progress | 进行中 | 测评正在进行 | 学生可以继续答题 |
completed | 已完成 | 测评已完成 | 不能再修改答案,可以查看结果 |
abandoned | 已放弃 | 测评被放弃 | 学生主动放弃或超时自动放弃 |
student_answers 表 - 学生答题详情表
DDL 定义
CREATE TABLE IF NOT EXISTS public.student_answers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
assessment_id UUID NOT NULL REFERENCES public.student_assessments(id) ON DELETE CASCADE,
question_id UUID NOT NULL REFERENCES public.questions(id) ON DELETE CASCADE,
student_answer JSONB NOT NULL, -- 学生答案
is_correct BOOLEAN,
score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
time_taken INTEGER, -- 答题用时(秒)
answered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(assessment_id, question_id)
);字段说明
| 字段名 | 数据类型 | 约束 | 默认值 | 描述 |
|---|---|---|---|---|
id | UUID | PRIMARY KEY | gen_random_uuid() | 答题记录唯一标识 |
assessment_id | UUID | NOT NULL, FOREIGN KEY | - | 测评记录ID |
question_id | UUID | NOT NULL, FOREIGN KEY | - | 题目ID |
student_answer | JSONB | NOT NULL | - | 学生答案(JSON格式) |
is_correct | BOOLEAN | NULLABLE | NULL | 是否正确(主观题可为NULL) |
score | DECIMAL(8,2) | NOT NULL | 0.00 | 得分 |
time_taken | INTEGER | NULLABLE | NULL | 答题用时(秒) |
answered_at | TIMESTAMPTZ | NOT NULL | NOW() | 答题时间 |
student_answer 字段格式说明
不同题型的答案格式:
// 单选题
{"answer": "B"}
// 多选题
{"answer": ["A", "C", "D"]}
// 判断题
{"answer": true}
// 主观题
{
"answer": "这是学生的文字答案",
"attachments": ["file1.pdf", "image1.jpg"],
"word_count": 150
}外键关系
student_assessments 表的外键关系
-- 学生关联
student_id REFERENCES auth.users(id) ON DELETE CASCADE
-- 题库关联
question_bank_id REFERENCES public.question_banks(id) ON DELETE CASCADEstudent_answers 表的外键关系
-- 测评记录关联
assessment_id REFERENCES public.student_assessments(id) ON DELETE CASCADE
-- 题目关联
question_id REFERENCES public.questions(id) ON DELETE CASCADE索引
基础索引
-- student_assessments 表索引
CREATE INDEX IF NOT EXISTS idx_assessments_student_id ON public.student_assessments(student_id);
CREATE INDEX IF NOT EXISTS idx_assessments_bank_id ON public.student_assessments(question_bank_id);
CREATE INDEX IF NOT EXISTS idx_assessments_status ON public.student_assessments(status);
CREATE INDEX IF NOT EXISTS idx_assessments_started_at ON public.student_assessments(started_at);
CREATE INDEX IF NOT EXISTS idx_assessments_completed_at ON public.student_assessments(completed_at);
-- student_answers 表索引
CREATE INDEX IF NOT EXISTS idx_answers_assessment_id ON public.student_answers(assessment_id);
CREATE INDEX IF NOT EXISTS idx_answers_question_id ON public.student_answers(question_id);
CREATE INDEX IF NOT EXISTS idx_answers_is_correct ON public.student_answers(is_correct);
CREATE INDEX IF NOT EXISTS idx_answers_answered_at ON public.student_answers(answered_at);复合索引
-- 复合索引优化常用查询
CREATE INDEX IF NOT EXISTS idx_assessments_student_status ON public.student_assessments(student_id, status);
CREATE INDEX IF NOT EXISTS idx_assessments_student_bank ON public.student_assessments(student_id, question_bank_id);特殊索引
-- JSONB 索引用于答案搜索
CREATE INDEX IF NOT EXISTS idx_answers_student_answer_gin ON public.student_answers USING GIN (student_answer);约束条件
唯一约束
- student_answers(assessment_id, question_id): 每个测评中每道题只能有一个答案记录
自动计算字段
- percentage_score: 百分制得分,根据 total_score 和 max_possible_score 自动计算
RLS策略
-- 学生测评访问控制
CREATE POLICY "学生查看自己的测评" ON public.student_assessments
FOR SELECT USING (
-- 学生可以查看自己的测评
student_id = auth.uid() OR
-- 管理员可以查看所有测评
get_user_role(auth.uid()) = 'admin'
);
CREATE POLICY "学生管理自己的测评" ON public.student_assessments
FOR ALL USING (
-- 学生可以管理自己的测评
student_id = auth.uid() OR
-- 管理员可以管理所有测评
get_user_role(auth.uid()) = 'admin'
);
-- 答题记录访问控制
CREATE POLICY "查看答题记录" ON public.student_answers
FOR SELECT USING (
-- 学生可以查看自己的答题记录
EXISTS (
SELECT 1 FROM public.student_assessments sa
WHERE sa.id = student_answers.assessment_id
AND sa.student_id = auth.uid()
) OR
-- 管理员可以查看
get_user_role(auth.uid()) = 'admin'
);
CREATE POLICY "管理答题记录" ON public.student_answers
FOR ALL USING (
-- 学生可以管理自己的答题记录
EXISTS (
SELECT 1 FROM public.student_assessments sa
WHERE sa.id = student_answers.assessment_id
AND sa.student_id = auth.uid()
) OR
get_user_role(auth.uid()) = 'admin'
);触发器
-- 测评统计更新触发器
CREATE TRIGGER update_assessment_statistics_trigger
AFTER INSERT OR UPDATE OR DELETE ON public.student_answers
FOR EACH ROW
EXECUTE FUNCTION update_assessment_statistics();
-- 答题自动评分触发器
CREATE TRIGGER auto_grade_answer_trigger
BEFORE INSERT OR UPDATE ON public.student_answers
FOR EACH ROW
EXECUTE FUNCTION auto_grade_answer();
-- 测评验证触发器
CREATE TRIGGER validate_assessment_trigger
BEFORE INSERT ON public.student_assessments
FOR EACH ROW
EXECUTE FUNCTION validate_assessment();
-- 答题验证触发器
CREATE TRIGGER validate_answer_trigger
BEFORE INSERT OR UPDATE ON public.student_answers
FOR EACH ROW
EXECUTE FUNCTION validate_answer();数据库函数
update_assessment_statistics()
- 用途: 自动更新测评统计信息
- 触发时机: AFTER INSERT OR UPDATE OR DELETE ON student_answers
- 返回类型: TRIGGER
- 业务逻辑:
- 更新已答题目数、正确答案数、总得分、用时
- 所有题目答完时自动完成测评
- 应用表: student_answers → student_assessments
auto_grade_answer()
- 用途: 自动评分答题记录
- 触发时机: BEFORE INSERT OR UPDATE ON student_answers
- 返回类型: TRIGGER
- 评分规则:
- 单选题/判断题: 完全匹配给满分
- 多选题: 答案集合完全匹配给满分
- 主观题: 设为未评分状态,需人工评分
- 应用表: student_answers
validate_assessment()
- 用途: 验证测评创建的合法性
- 触发时机: BEFORE INSERT ON student_assessments
- 返回类型: TRIGGER
- 验证规则:
- 检查学生权限
- 验证题库题目数量
- 计算最大可能得分
- 应用表: student_assessments
validate_answer()
- 用途: 验证答题记录的合法性
- 触发时机: BEFORE INSERT OR UPDATE ON student_answers
- 返回类型: TRIGGER
- 验证规则:
- 测评必须处于进行中状态
- 题目必须属于测评的题库
- 应用表: student_answers
版权所有
版权归属:Evoliant
许可证:MIT