83 lines
3.9 KiB
PL/PgSQL
83 lines
3.9 KiB
PL/PgSQL
-- 创建训练项目表 (AK Training Projects)
|
||
-- 这是训练活动的模板/项目定义，不同于训练计划(ak_training_plans)
|
||
-- 统一使用ak_前缀命名约定
|
||
|
||
CREATE TABLE public.ak_training_projects (
|
||
id SERIAL PRIMARY KEY, -- 项目ID，使用SERIAL自增
|
||
name VARCHAR(128) NOT NULL, -- 项目名称
|
||
description TEXT, -- 项目描述
|
||
category VARCHAR(64) NOT NULL DEFAULT 'general', -- 项目分类
|
||
difficulty INTEGER NOT NULL DEFAULT 1 CHECK (difficulty >= 1 AND difficulty <= 5), -- 难度等级 1-5
|
||
difficulty_level INTEGER, -- 备用难度等级字段，与代码类型兼容
|
||
duration INTEGER NOT NULL DEFAULT 30, -- 持续时间（分钟）
|
||
duration_minutes INTEGER, -- 备用时长字段，与代码类型兼容
|
||
equipment_needed TEXT, -- 所需设备
|
||
instructions TEXT, -- 训练指导
|
||
training_points TEXT, -- 训练要点
|
||
scoring_criteria TEXT, -- 评分标准
|
||
image_url VARCHAR(512), -- 项目图片URL
|
||
status VARCHAR(16) DEFAULT 'active' CHECK (status IN ('active', 'inactive')), -- 状态
|
||
usage_count INTEGER DEFAULT 0, -- 使用次数统计
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT now(), -- 创建时间
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() -- 更新时间
|
||
);
|
||
|
||
-- 添加表注释
|
||
COMMENT ON TABLE public.ak_training_projects IS '训练项目表 - 定义可复用的训练活动模板';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_ak_training_projects_category ON public.ak_training_projects(category);
|
||
CREATE INDEX idx_ak_training_projects_difficulty ON public.ak_training_projects(difficulty);
|
||
CREATE INDEX idx_ak_training_projects_status ON public.ak_training_projects(status);
|
||
CREATE INDEX idx_ak_training_projects_created_at ON public.ak_training_projects(created_at);
|
||
|
||
-- 插入示例数据
|
||
INSERT INTO public.ak_training_projects (name, description, category, difficulty, duration, training_points, scoring_criteria, status) VALUES
|
||
('5公里跑步', '标准5公里长跑训练', 'running', 2, 30, '保持匀速，注意呼吸节奏', '时间、心率、配速', 'active'),
|
||
('俯卧撑训练', '标准俯卧撑力量训练', 'strength', 1, 15, '动作标准，控制节奏', '数量、动作质量', 'active'),
|
||
('篮球基础', '篮球基本技能训练', 'basketball', 2, 45, '运球、投篮、传球基础', '技术动作、配合意识', 'active'),
|
||
('游泳训练', '自由泳技术训练', 'swimming', 3, 60, '动作协调，呼吸配合', '距离、技术、时间', 'active'),
|
||
('瑜伽基础', '基础瑜伽体式练习', 'yoga', 1, 30, '动作缓慢，保持呼吸', '柔韧性、平衡性', 'active'),
|
||
('足球基础', '足球基本技能训练', 'football', 2, 45, '传球、射门、带球基础', '技术动作、配合', 'active'),
|
||
('羽毛球训练', '羽毛球基础训练', 'badminton', 2, 40, '发球、接球、步法', '技术、反应速度', 'active'),
|
||
('力量训练', '综合力量训练', 'strength', 3, 45, '正确姿势，循序渐进', '力量、耐力', 'active');
|
||
|
||
-- 创建更新触发器函数（如果不存在）
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = now();
|
||
RETURN NEW;
|
||
END;
|
||
$$ language 'plpgsql';
|
||
|
||
-- 为表创建更新触发器
|
||
CREATE TRIGGER update_ak_training_projects_updated_at
|
||
BEFORE UPDATE ON public.ak_training_projects
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- 创建数据库视图，为了兼容不带ak_前缀的查询
|
||
CREATE OR REPLACE VIEW public.training_projects AS
|
||
SELECT
|
||
id,
|
||
name,
|
||
description,
|
||
category,
|
||
difficulty,
|
||
difficulty_level,
|
||
duration,
|
||
duration_minutes,
|
||
equipment_needed,
|
||
instructions,
|
||
training_points,
|
||
scoring_criteria,
|
||
image_url,
|
||
status,
|
||
usage_count,
|
||
created_at,
|
||
updated_at
|
||
FROM public.ak_training_projects;
|
||
|
||
-- 添加视图注释
|
||
COMMENT ON VIEW public.training_projects IS '训练项目视图 - 提供对ak_training_projects的兼容访问';
|