242 lines
8.1 KiB
PL/PgSQL
242 lines
8.1 KiB
PL/PgSQL
-- 部署 ak_training_projects 表 (使用UUID主键)
|
|
-- 创建日期: 2025-06-09
|
|
-- 说明: 标准化训练项目表，使用 ak_ 前缀命名规范和UUID主键
|
|
|
|
-- 1. 启用 UUID 扩展
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- 2. 创建 ak_training_projects 表
|
|
CREATE TABLE IF NOT EXISTS ak_training_projects (
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
sport_type VARCHAR(100) NOT NULL,
|
|
difficulty_level VARCHAR(50) NOT NULL DEFAULT 'beginner',
|
|
duration_minutes INTEGER NOT NULL DEFAULT 30,
|
|
equipment_required TEXT[],
|
|
target_age_group VARCHAR(50),
|
|
objectives TEXT[],
|
|
instructions TEXT NOT NULL,
|
|
video_url VARCHAR(500),
|
|
image_url VARCHAR(500),
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
|
|
-- 约束
|
|
CONSTRAINT ak_training_projects_difficulty_check
|
|
CHECK (difficulty_level IN ('beginner', 'intermediate', 'advanced')),
|
|
CONSTRAINT ak_training_projects_duration_check
|
|
CHECK (duration_minutes > 0 AND duration_minutes <= 480),
|
|
CONSTRAINT ak_training_projects_title_length_check
|
|
CHECK (LENGTH(title) >= 3)
|
|
);
|
|
|
|
-- 3. 创建索引
|
|
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_sport_type
|
|
ON ak_training_projects(sport_type);
|
|
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_difficulty
|
|
ON ak_training_projects(difficulty_level);
|
|
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_active
|
|
ON ak_training_projects(is_active);
|
|
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_created_by
|
|
ON ak_training_projects(created_by);
|
|
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_created_at
|
|
ON ak_training_projects(created_at);
|
|
|
|
-- 4. 创建触发器更新 updated_at
|
|
CREATE OR REPLACE FUNCTION update_ak_training_projects_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS trigger_ak_training_projects_updated_at ON ak_training_projects;
|
|
CREATE TRIGGER trigger_ak_training_projects_updated_at
|
|
BEFORE UPDATE ON ak_training_projects
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_ak_training_projects_updated_at();
|
|
|
|
-- 5. 设置 RLS (Row Level Security) 策略
|
|
ALTER TABLE ak_training_projects ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 查看策略（所有用户可查看激活的项目）
|
|
CREATE POLICY "ak_training_projects_select_policy" ON ak_training_projects
|
|
FOR SELECT USING (is_active = true);
|
|
|
|
-- 插入策略（认证用户可创建）
|
|
CREATE POLICY "ak_training_projects_insert_policy" ON ak_training_projects
|
|
FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);
|
|
|
|
-- 更新策略（创建者可更新）
|
|
CREATE POLICY "ak_training_projects_update_policy" ON ak_training_projects
|
|
FOR UPDATE USING (auth.uid() = created_by);
|
|
|
|
-- 删除策略（创建者可删除）
|
|
CREATE POLICY "ak_training_projects_delete_policy" ON ak_training_projects
|
|
FOR DELETE USING (auth.uid() = created_by);
|
|
|
|
-- 6. 插入示例数据 (使用明确的UUID值)
|
|
INSERT INTO ak_training_projects (
|
|
id, title, description, sport_type, difficulty_level, duration_minutes,
|
|
equipment_required, target_age_group, objectives, instructions,
|
|
video_url, image_url
|
|
) VALUES
|
|
(
|
|
'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
|
|
'基础篮球运球训练',
|
|
'适合初学者的篮球运球基础技能训练项目',
|
|
'basketball',
|
|
'beginner',
|
|
30,
|
|
ARRAY['篮球', '训练锥'],
|
|
'8-16岁',
|
|
ARRAY['掌握基础运球技巧', '提高手眼协调能力', '建立运球节奏感'],
|
|
'1. 热身5分钟\n2. 原地运球练习10分钟\n3. 行进间运球10分钟\n4. 变向运球练习5分钟',
|
|
'https://example.com/basketball_dribbling.mp4',
|
|
'https://example.com/basketball_dribbling.jpg'
|
|
),
|
|
(
|
|
'b2c3d4e5-f6a7-8901-bcde-f23456789012',
|
|
'足球传球技术训练',
|
|
'提高足球传球准确性和技术的专项训练',
|
|
'football',
|
|
'intermediate',
|
|
45,
|
|
ARRAY['足球', '标志桶', '球门'],
|
|
'10-18岁',
|
|
ARRAY['提高传球精度', '增强脚法技术', '培养团队配合意识'],
|
|
'1. 热身跑步10分钟\n2. 短传练习15分钟\n3. 长传练习15分钟\n4. 实战传球演练5分钟',
|
|
'https://example.com/football_passing.mp4',
|
|
'https://example.com/football_passing.jpg'
|
|
),
|
|
(
|
|
'c3d4e5f6-a7b8-9012-cdef-345678901234',
|
|
'网球正手击球训练',
|
|
'网球正手击球技术的系统性训练课程',
|
|
'tennis',
|
|
'beginner',
|
|
40,
|
|
ARRAY['网球拍', '网球', '练习网'],
|
|
'12岁以上',
|
|
ARRAY['掌握正手击球姿势', '提高击球稳定性', '增强击球力量'],
|
|
'1. 拉伸热身5分钟\n2. 挥拍动作练习10分钟\n3. 对墙击球15分钟\n4. 对打练习10分钟',
|
|
'https://example.com/tennis_forehand.mp4',
|
|
'https://example.com/tennis_forehand.jpg'
|
|
),
|
|
(
|
|
'd4e5f6a7-b8c9-0123-def0-456789012345',
|
|
'游泳自由泳技术',
|
|
'自由泳游泳技术的完整训练方案',
|
|
'swimming',
|
|
'intermediate',
|
|
60,
|
|
ARRAY['游泳池', '浮板', '游泳眼镜'],
|
|
'8岁以上',
|
|
ARRAY['掌握自由泳动作要领', '提高游泳耐力', '改善游泳技术'],
|
|
'1. 陆上热身10分钟\n2. 水中适应10分钟\n3. 分解动作练习20分钟\n4. 完整动作游泳20分钟',
|
|
'https://example.com/swimming_freestyle.mp4',
|
|
'https://example.com/swimming_freestyle.jpg'
|
|
),
|
|
(
|
|
'e5f6a7b8-c9d0-1234-ef01-567890123456',
|
|
'乒乓球发球技术',
|
|
'乒乓球各种发球技术的专项训练',
|
|
'table_tennis',
|
|
'advanced',
|
|
35,
|
|
ARRAY['乒乓球拍', '乒乓球', '球台'],
|
|
'10岁以上',
|
|
ARRAY['掌握多种发球技术', '提高发球质量', '增强比赛竞争力'],
|
|
'1. 热身活动5分钟\n2. 基础发球练习15分钟\n3. 旋转发球练习10分钟\n4. 实战发球演练5分钟',
|
|
'https://example.com/tabletennis_serve.mp4',
|
|
'https://example.com/tabletennis_serve.jpg'
|
|
),
|
|
(
|
|
'f6a7b8c9-d0e1-2345-f012-678901234567',
|
|
'羽毛球步法训练',
|
|
'羽毛球场地移动步法的系统训练',
|
|
'badminton',
|
|
'intermediate',
|
|
50,
|
|
ARRAY['羽毛球拍', '羽毛球', '标志桶'],
|
|
'12岁以上',
|
|
ARRAY['提高场地移动速度', '掌握正确步法', '增强场地覆盖能力'],
|
|
'1. 热身跑步10分钟\n2. 基础步法练习20分钟\n3. 组合步法训练15分钟\n4. 实战步法应用5分钟',
|
|
'https://example.com/badminton_footwork.mp4',
|
|
'https://example.com/badminton_footwork.jpg'
|
|
),
|
|
(
|
|
'a7b8c9d0-e1f2-3456-0123-789012345678',
|
|
'排球垫球基础',
|
|
'排球垫球技术的入门训练课程',
|
|
'volleyball',
|
|
'beginner',
|
|
30,
|
|
ARRAY['排球', '训练网'],
|
|
'10-16岁',
|
|
ARRAY['掌握垫球基本动作', '提高垫球稳定性', '培养球感'],
|
|
'1. 热身运动5分钟\n2. 垫球动作练习10分钟\n3. 对墙垫球10分钟\n4. 配合垫球练习5分钟',
|
|
'https://example.com/volleyball_bump.mp4',
|
|
'https://example.com/volleyball_bump.jpg'
|
|
),
|
|
(
|
|
'b8c9d0e1-f2a3-4567-1234-890123456789',
|
|
'田径短跑技术',
|
|
'100米短跑技术训练的专业课程',
|
|
'track_and_field',
|
|
'advanced',
|
|
55,
|
|
ARRAY['跑道', '起跑器', '秒表'],
|
|
'14岁以上',
|
|
ARRAY['改善起跑技术', '提高跑步效率', '增强爆发力'],
|
|
'1. 动态热身15分钟\n2. 起跑技术练习15分钟\n3. 加速跑练习15分钟\n4. 冲刺跑训练10分钟',
|
|
'https://example.com/sprint_technique.mp4',
|
|
'https://example.com/sprint_technique.jpg'
|
|
);
|
|
|
|
-- 7. 创建兼容性视图（可选，如果需要支持遗留代码）
|
|
CREATE OR REPLACE VIEW training_projects AS
|
|
SELECT
|
|
id,
|
|
title,
|
|
description,
|
|
sport_type,
|
|
difficulty_level,
|
|
duration_minutes,
|
|
equipment_required,
|
|
target_age_group,
|
|
objectives,
|
|
instructions,
|
|
video_url,
|
|
image_url,
|
|
is_active,
|
|
created_by,
|
|
created_at,
|
|
updated_at
|
|
FROM ak_training_projects;
|
|
|
|
-- 8. 验证数据
|
|
SELECT
|
|
COUNT(*) as total_projects,
|
|
COUNT(CASE WHEN is_active THEN 1 END) as active_projects,
|
|
COUNT(DISTINCT sport_type) as sport_types,
|
|
COUNT(DISTINCT difficulty_level) as difficulty_levels
|
|
FROM ak_training_projects;
|
|
|
|
-- 9. 显示示例UUID
|
|
SELECT
|
|
'UUID示例数据已插入' as status,
|
|
id,
|
|
title,
|
|
sport_type
|
|
FROM ak_training_projects
|
|
ORDER BY title
|
|
LIMIT 3;
|
|
|
|
-- 部署完成提示
|
|
SELECT 'ak_training_projects 表 (UUID版本) 已成功创建并插入示例数据' as deployment_status;
|