96 lines
5.4 KiB
PL/PgSQL
96 lines
5.4 KiB
PL/PgSQL
-- 创建训练项目表 (Training Projects)
|
||
-- 这是训练活动的模板/项目定义，不同于训练计划(training_plans)
|
||
|
||
CREATE TABLE public.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.training_projects IS '训练项目表 - 定义可复用的训练活动模板';
|
||
|
||
-- 创建索引
|
||
CREATE INDEX idx_training_projects_category ON public.training_projects(category);
|
||
CREATE INDEX idx_training_projects_difficulty ON public.training_projects(difficulty);
|
||
CREATE INDEX idx_training_projects_status ON public.training_projects(status);
|
||
CREATE INDEX idx_training_projects_created_at ON public.training_projects(created_at);
|
||
|
||
-- 插入一些示例数据
|
||
INSERT INTO public.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');
|
||
|
||
-- 创建ak_training_projects表（与现有naming convention保持一致）
|
||
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);
|
||
|
||
-- 插入一些示例数据到ak_training_projects
|
||
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');
|
||
|
||
-- 更新触发器函数
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = now();
|
||
RETURN NEW;
|
||
END;
|
||
$$ language 'plpgsql';
|
||
|
||
-- 为两个表创建更新触发器
|
||
CREATE TRIGGER update_training_projects_updated_at
|
||
BEFORE UPDATE ON public.training_projects
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER update_ak_training_projects_updated_at
|
||
BEFORE UPDATE ON public.ak_training_projects
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|