153 lines
5.1 KiB
SQL
153 lines
5.1 KiB
SQL
-- 简化版评分标准JSON迁移脚本
|
||
-- 适用于当前数据库结构 (title, sport_type, difficulty_level, is_active)
|
||
-- 执行日期：2025-06-11
|
||
|
||
-- 步骤1：创建备份表
|
||
CREATE TABLE IF NOT EXISTS ak_training_projects_backup_20250611 AS
|
||
SELECT * FROM ak_training_projects;
|
||
|
||
-- 步骤2：检查备份是否成功
|
||
DO $$
|
||
DECLARE
|
||
backup_count INTEGER;
|
||
original_count INTEGER;
|
||
BEGIN
|
||
SELECT COUNT(*) INTO backup_count FROM ak_training_projects_backup_20250611;
|
||
SELECT COUNT(*) INTO original_count FROM ak_training_projects;
|
||
|
||
IF backup_count = original_count THEN
|
||
RAISE NOTICE '备份成功: % 条记录已备份', backup_count;
|
||
ELSE
|
||
RAISE EXCEPTION '备份失败: 原表 % 条记录，备份表 % 条记录', original_count, backup_count;
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 步骤3：确保 scoring_criteria 字段为 JSONB 类型
|
||
DO $$
|
||
BEGIN
|
||
-- 检查并转换字段类型
|
||
IF EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'ak_training_projects'
|
||
AND column_name = 'scoring_criteria'
|
||
AND data_type != 'jsonb'
|
||
) THEN
|
||
ALTER TABLE ak_training_projects
|
||
ALTER COLUMN scoring_criteria TYPE JSONB
|
||
USING CASE
|
||
WHEN scoring_criteria IS NULL OR scoring_criteria = '' THEN NULL
|
||
WHEN scoring_criteria ~ '^[\s\t\n\r]*\{.*\}[\s\t\n\r]*$' THEN scoring_criteria::JSONB
|
||
ELSE jsonb_build_object('legacy_text', scoring_criteria)
|
||
END;
|
||
|
||
RAISE NOTICE 'scoring_criteria 字段已转换为 JSONB 类型';
|
||
ELSE
|
||
RAISE NOTICE 'scoring_criteria 字段已经是 JSONB 类型';
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 步骤4：为没有评分标准的项目添加默认JSON结构
|
||
UPDATE ak_training_projects
|
||
SET scoring_criteria = jsonb_build_object(
|
||
'criteria', jsonb_build_array(
|
||
jsonb_build_object(
|
||
'min_score', 90,
|
||
'max_score', 100,
|
||
'description', '优秀：表现卓越，超出预期'
|
||
),
|
||
jsonb_build_object(
|
||
'min_score', 80,
|
||
'max_score', 89,
|
||
'description', '良好：表现良好，符合要求'
|
||
),
|
||
jsonb_build_object(
|
||
'min_score', 70,
|
||
'max_score', 79,
|
||
'description', '及格：基本达标，有待改进'
|
||
),
|
||
jsonb_build_object(
|
||
'min_score', 0,
|
||
'max_score', 69,
|
||
'description', '不及格：未达标准，需要重练'
|
||
)
|
||
),
|
||
'scoring_method', 'comprehensive',
|
||
'weight_distribution', jsonb_build_object(
|
||
'technique', 0.4,
|
||
'effort', 0.3,
|
||
'improvement', 0.3
|
||
)
|
||
)
|
||
WHERE scoring_criteria IS NULL
|
||
OR scoring_criteria = '{}'
|
||
OR jsonb_typeof(scoring_criteria) != 'object'
|
||
OR NOT (scoring_criteria ? 'criteria');
|
||
|
||
-- 步骤5：创建索引以提高查询性能
|
||
CREATE INDEX IF NOT EXISTS idx_ak_training_projects_scoring_criteria
|
||
ON ak_training_projects USING GIN (scoring_criteria);
|
||
|
||
-- 步骤6：验证迁移结果
|
||
DO $$
|
||
DECLARE
|
||
total_projects INTEGER;
|
||
projects_with_criteria INTEGER;
|
||
projects_with_valid_structure INTEGER;
|
||
BEGIN
|
||
-- 统计总数
|
||
SELECT COUNT(*) INTO total_projects FROM ak_training_projects;
|
||
|
||
-- 统计有评分标准的项目
|
||
SELECT COUNT(*) INTO projects_with_criteria
|
||
FROM ak_training_projects
|
||
WHERE scoring_criteria IS NOT NULL;
|
||
|
||
-- 统计有效JSON结构的项目
|
||
SELECT COUNT(*) INTO projects_with_valid_structure
|
||
FROM ak_training_projects
|
||
WHERE scoring_criteria ? 'criteria'
|
||
AND jsonb_typeof(scoring_criteria->'criteria') = 'array'
|
||
AND jsonb_array_length(scoring_criteria->'criteria') > 0;
|
||
|
||
RAISE NOTICE '=== 迁移验证结果 ===';
|
||
RAISE NOTICE '总项目数: %', total_projects;
|
||
RAISE NOTICE '有评分标准的项目: %', projects_with_criteria;
|
||
RAISE NOTICE '有效JSON结构的项目: %', projects_with_valid_structure;
|
||
|
||
IF projects_with_valid_structure = total_projects THEN
|
||
RAISE NOTICE '✅ 迁移成功！所有项目都有有效的评分标准JSON结构';
|
||
ELSE
|
||
RAISE WARNING '⚠️ 有 % 个项目的评分标准结构可能需要检查',
|
||
total_projects - projects_with_valid_structure;
|
||
END IF;
|
||
END $$;
|
||
|
||
-- 步骤7：显示示例数据
|
||
DO $$
|
||
DECLARE
|
||
example_record RECORD;
|
||
BEGIN
|
||
RAISE NOTICE '=== 示例数据 ===';
|
||
|
||
FOR example_record IN
|
||
SELECT
|
||
title,
|
||
scoring_criteria->'criteria' as criteria,
|
||
jsonb_array_length(scoring_criteria->'criteria') as criteria_count
|
||
FROM ak_training_projects
|
||
WHERE scoring_criteria IS NOT NULL
|
||
LIMIT 3
|
||
LOOP
|
||
RAISE NOTICE '项目: %', example_record.title;
|
||
RAISE NOTICE ' 评分标准数量: %', example_record.criteria_count;
|
||
RAISE NOTICE ' 评分标准: %', example_record.criteria;
|
||
RAISE NOTICE '---';
|
||
END LOOP;
|
||
END $$;
|
||
|
||
-- 完成提示
|
||
SELECT '🎉 评分标准JSON结构化迁移完成！' as status;
|
||
|
||
-- 清理建议（可选执行）
|
||
-- DROP TABLE IF EXISTS ak_training_projects_backup_20250611;
|