232 lines
14 KiB
Go
232 lines
14 KiB
Go
-- =============================================
|
||
-- 学习系统完整初始化SQL脚本
|
||
-- 包含所有必要的数据字典、菜单权限、API权限配置
|
||
-- 执行顺序：先执行此文件，再根据需要执行角色权限配置
|
||
-- =============================================
|
||
|
||
-- 1. 插入学习系统数据字典
|
||
INSERT INTO `sys_dictionaries` (`name`, `type`, `status`, `desc`) VALUES
|
||
('课程难度', 'course_difficulty', 1, '课程难度等级字典'),
|
||
('课程状态', 'course_status', 1, '课程状态字典'),
|
||
('章节状态', 'chapter_status', 1, '章节状态字典'),
|
||
('知识点状态', 'knowledge_status', 1, '知识点状态字典'),
|
||
('题目类型', 'question_type', 1, '题目类型字典'),
|
||
('题目难度', 'question_difficulty', 1, '题目难度等级字典'),
|
||
('学习状态', 'learning_status', 1, '用户学习状态字典'),
|
||
('考试状态', 'exam_status', 1, '考试状态字典');
|
||
|
||
-- 2. 获取字典ID并插入字典详情
|
||
SET @course_difficulty_id = (SELECT id FROM sys_dictionaries WHERE type = 'course_difficulty');
|
||
SET @course_status_id = (SELECT id FROM sys_dictionaries WHERE type = 'course_status');
|
||
SET @chapter_status_id = (SELECT id FROM sys_dictionaries WHERE type = 'chapter_status');
|
||
SET @knowledge_status_id = (SELECT id FROM sys_dictionaries WHERE type = 'knowledge_status');
|
||
SET @question_type_id = (SELECT id FROM sys_dictionaries WHERE type = 'question_type');
|
||
SET @question_difficulty_id = (SELECT id FROM sys_dictionaries WHERE type = 'question_difficulty');
|
||
SET @learning_status_id = (SELECT id FROM sys_dictionaries WHERE type = 'learning_status');
|
||
SET @exam_status_id = (SELECT id FROM sys_dictionaries WHERE type = 'exam_status');
|
||
|
||
-- 3. 插入课程难度字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('初级', 'beginner', 1, 1, @course_difficulty_id),
|
||
('中级', 'intermediate', 1, 2, @course_difficulty_id),
|
||
('高级', 'advanced', 1, 3, @course_difficulty_id);
|
||
|
||
-- 4. 插入课程状态字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('草稿', 'draft', 1, 1, @course_status_id),
|
||
('已发布', 'published', 1, 2, @course_status_id),
|
||
('已下架', 'archived', 1, 3, @course_status_id);
|
||
|
||
-- 5. 插入章节状态字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('草稿', 'draft', 1, 1, @chapter_status_id),
|
||
('已发布', 'published', 1, 2, @chapter_status_id),
|
||
('隐藏', 'hidden', 1, 3, @chapter_status_id);
|
||
|
||
-- 6. 插入知识点状态字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('草稿', 'draft', 1, 1, @knowledge_status_id),
|
||
('已发布', 'published', 1, 2, @knowledge_status_id),
|
||
('隐藏', 'hidden', 1, 3, @knowledge_status_id);
|
||
|
||
-- 7. 插入题目类型字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('单选题', 'single_choice', 1, 1, @question_type_id),
|
||
('多选题', 'multiple_choice', 1, 2, @question_type_id),
|
||
('判断题', 'true_false', 1, 3, @question_type_id),
|
||
('填空题', 'fill_blank', 1, 4, @question_type_id);
|
||
|
||
-- 8. 插入题目难度字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('简单', 'easy', 1, 1, @question_difficulty_id),
|
||
('中等', 'medium', 1, 2, @question_difficulty_id),
|
||
('困难', 'hard', 1, 3, @question_difficulty_id);
|
||
|
||
-- 9. 插入学习状态字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('未开始', 'not_started', 1, 1, @learning_status_id),
|
||
('学习中', 'learning', 1, 2, @learning_status_id),
|
||
('已完成', 'completed', 1, 3, @learning_status_id),
|
||
('已暂停', 'paused', 1, 4, @learning_status_id);
|
||
|
||
-- 10. 插入考试状态字典详情
|
||
INSERT INTO `sys_dictionary_details` (`label`, `value`, `status`, `sort`, `sys_dictionary_id`) VALUES
|
||
('草稿', 'draft', 1, 1, @exam_status_id),
|
||
('已发布', 'published', 1, 2, @exam_status_id),
|
||
('已结束', 'ended', 1, 3, @exam_status_id),
|
||
('已取消', 'cancelled', 1, 4, @exam_status_id);
|
||
|
||
-- =============================================
|
||
-- 菜单权限配置
|
||
-- =============================================
|
||
|
||
-- 11. 插入主菜单
|
||
INSERT INTO `sys_base_menus` (`menu_level`, `parent_id`, `path`, `name`, `hidden`, `component`, `sort`, `title`, `icon`, `keep_alive`) VALUES
|
||
(0, 0, 'learning', 'learning', 0, 'view/routerHolder.vue', 3, '教学管理', 'management', 0),
|
||
(0, 0, 'student', 'student', 0, 'view/routerHolder.vue', 2, '在线学习', 'reading', 0);
|
||
|
||
-- 12. 获取主菜单ID
|
||
SET @learning_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'learning' AND menu_level = 0);
|
||
SET @student_menu_id = (SELECT id FROM sys_base_menus WHERE name = 'student' AND menu_level = 0);
|
||
|
||
-- 13. 插入教学管理子菜单
|
||
INSERT INTO `sys_base_menus` (`menu_level`, `parent_id`, `path`, `name`, `hidden`, `component`, `sort`, `title`, `icon`, `keep_alive`) VALUES
|
||
(1, @learning_menu_id, 'course', 'course', 0, 'view/learning/admin/course/course.vue', 1, '课程管理', 'notebook', 1),
|
||
(1, @learning_menu_id, 'chapter', 'chapter', 0, 'view/learning/admin/chapter/chapter.vue', 2, '章节管理', 'collection', 1),
|
||
(1, @learning_menu_id, 'knowledgePoint', 'knowledgePoint', 0, 'view/learning/admin/knowledgePoint/knowledgePoint.vue', 3, '知识点管理', 'document', 1),
|
||
(1, @learning_menu_id, 'question', 'question', 0, 'view/learning/admin/question/question.vue', 4, '题目管理', 'edit', 1),
|
||
(1, @learning_menu_id, 'exam', 'exam', 0, 'view/learning/admin/exam/exam.vue', 5, '考试管理', 'medal', 1),
|
||
(1, @learning_menu_id, 'learningRecords', 'learningRecords', 0, 'view/learning/admin/records/learningRecords.vue', 6, '学习记录', 'histogram', 1);
|
||
|
||
-- 14. 插入在线学习子菜单
|
||
INSERT INTO `sys_base_menus` (`menu_level`, `parent_id`, `path`, `name`, `hidden`, `component`, `sort`, `title`, `icon`, `keep_alive`) VALUES
|
||
(1, @student_menu_id, 'dashboard', 'dashboard', 0, 'view/learning/student/dashboard/dashboard.vue', 1, '学习首页', 'house', 1),
|
||
(1, @student_menu_id, 'myCourses', 'myCourses', 0, 'view/learning/student/courses/myCourses.vue', 2, '我的课程', 'notebook', 1),
|
||
(1, @student_menu_id, 'onlineExam', 'onlineExam', 0, 'view/learning/student/exam/onlineExam.vue', 3, '在线考试', 'medal', 1),
|
||
(1, @student_menu_id, 'wrongQuestions', 'wrongQuestions', 0, 'view/learning/student/wrongQuestion/wrongQuestions.vue', 4, '我的错题', 'warning', 1);
|
||
|
||
-- =============================================
|
||
-- API权限配置
|
||
-- =============================================
|
||
|
||
-- 15. 插入学习系统API权限
|
||
INSERT INTO `sys_apis` (`path`, `description`, `api_group`, `method`) VALUES
|
||
-- 课程管理API
|
||
('/course/createCourse', '创建课程', '课程管理', 'POST'),
|
||
('/course/deleteCourse', '删除课程', '课程管理', 'DELETE'),
|
||
('/course/deleteCourseByIds', '批量删除课程', '课程管理', 'DELETE'),
|
||
('/course/updateCourse', '更新课程', '课程管理', 'PUT'),
|
||
('/course/findCourse', '根据ID获取课程', '课程管理', 'GET'),
|
||
('/course/getCourseList', '获取课程列表', '课程管理', 'GET'),
|
||
('/course/getPublishedCourses', '获取已发布课程列表', '课程管理', 'GET'),
|
||
|
||
-- 章节管理API
|
||
('/chapter/createChapter', '创建章节', '章节管理', 'POST'),
|
||
('/chapter/deleteChapter', '删除章节', '章节管理', 'DELETE'),
|
||
('/chapter/deleteChapterByIds', '批量删除章节', '章节管理', 'DELETE'),
|
||
('/chapter/updateChapter', '更新章节', '章节管理', 'PUT'),
|
||
('/chapter/findChapter', '根据ID获取章节', '章节管理', 'GET'),
|
||
('/chapter/getChapterList', '获取章节列表', '章节管理', 'GET'),
|
||
('/chapter/getChaptersByCourse', '根据课程获取章节列表', '章节管理', 'GET'),
|
||
|
||
-- 知识点管理API
|
||
('/knowledgePoint/createKnowledgePoint', '创建知识点', '知识点管理', 'POST'),
|
||
('/knowledgePoint/deleteKnowledgePoint', '删除知识点', '知识点管理', 'DELETE'),
|
||
('/knowledgePoint/deleteKnowledgePointByIds', '批量删除知识点', '知识点管理', 'DELETE'),
|
||
('/knowledgePoint/updateKnowledgePoint', '更新知识点', '知识点管理', 'PUT'),
|
||
('/knowledgePoint/findKnowledgePoint', '根据ID获取知识点', '知识点管理', 'GET'),
|
||
('/knowledgePoint/getKnowledgePointList', '获取知识点列表', '知识点管理', 'GET'),
|
||
('/knowledgePoint/getKnowledgePointsByChapter', '根据章节获取知识点列表', '知识点管理', 'GET'),
|
||
|
||
-- 题目管理API
|
||
('/question/createQuestion', '创建题目', '题目管理', 'POST'),
|
||
('/question/deleteQuestion', '删除题目', '题目管理', 'DELETE'),
|
||
('/question/deleteQuestionByIds', '批量删除题目', '题目管理', 'DELETE'),
|
||
('/question/updateQuestion', '更新题目', '题目管理', 'PUT'),
|
||
('/question/findQuestion', '根据ID获取题目', '题目管理', 'GET'),
|
||
('/question/getQuestionList', '获取题目列表', '题目管理', 'GET'),
|
||
('/question/getQuestionsByKnowledgePoint', '根据知识点获取题目列表', '题目管理', 'GET'),
|
||
|
||
-- 考试管理API
|
||
('/exam/createExam', '创建考试', '考试管理', 'POST'),
|
||
('/exam/deleteExam', '删除考试', '考试管理', 'DELETE'),
|
||
('/exam/deleteExamByIds', '批量删除考试', '考试管理', 'DELETE'),
|
||
('/exam/updateExam', '更新考试', '考试管理', 'PUT'),
|
||
('/exam/publishExam', '发布考试', '考试管理', 'PUT'),
|
||
('/exam/findExam', '根据ID获取考试', '考试管理', 'GET'),
|
||
('/exam/getExamList', '获取考试列表', '考试管理', 'GET'),
|
||
('/exam/getActiveExams', '获取进行中的考试列表', '考试管理', 'GET'),
|
||
('/exam/getExamsByCourse', '根据课程获取考试列表', '考试管理', 'GET'),
|
||
('/exam/getExamStatistics', '获取考试统计', '考试管理', 'GET'),
|
||
|
||
-- 学习记录管理API
|
||
('/userLearning/createUserLearning', '创建学习记录', '学习记录管理', 'POST'),
|
||
('/userLearning/deleteUserLearning', '删除学习记录', '学习记录管理', 'DELETE'),
|
||
('/userLearning/deleteUserLearningByIds', '批量删除学习记录', '学习记录管理', 'DELETE'),
|
||
('/userLearning/updateUserLearning', '更新学习记录', '学习记录管理', 'PUT'),
|
||
('/userLearning/findUserLearning', '根据ID获取学习记录', '学习记录管理', 'GET'),
|
||
('/userLearning/getUserLearningList', '获取学习记录列表', '学习记录管理', 'GET'),
|
||
('/userLearning/getUserProgress', '获取用户学习进度', '学习记录管理', 'GET'),
|
||
('/userLearning/getLearningStatistics', '获取用户学习统计', '学习记录管理', 'GET'),
|
||
|
||
-- 错题本管理API
|
||
('/wrongQuestion/createWrongQuestion', '创建错题记录', '错题本管理', 'POST'),
|
||
('/wrongQuestion/deleteWrongQuestion', '删除错题记录', '错题本管理', 'DELETE'),
|
||
('/wrongQuestion/deleteWrongQuestionByIds', '批量删除错题记录', '错题本管理', 'DELETE'),
|
||
('/wrongQuestion/updateWrongQuestion', '更新错题记录', '错题本管理', 'PUT'),
|
||
('/wrongQuestion/markAsMastered', '标记题目为已掌握', '错题本管理', 'PUT'),
|
||
('/wrongQuestion/findWrongQuestion', '根据ID获取错题记录', '错题本管理', 'GET'),
|
||
('/wrongQuestion/getWrongQuestionList', '获取错题记录列表', '错题本管理', 'GET'),
|
||
('/wrongQuestion/getUserWrongQuestions', '获取用户错题列表', '错题本管理', 'GET'),
|
||
('/wrongQuestion/getWrongQuestionStatistics', '获取错题统计', '错题本管理', 'GET'),
|
||
|
||
-- 用户考试记录管理API
|
||
('/userExam/createUserExam', '创建用户考试记录', '用户考试记录管理', 'POST'),
|
||
('/userExam/deleteUserExam', '删除用户考试记录', '用户考试记录管理', 'DELETE'),
|
||
('/userExam/deleteUserExamByIds', '批量删除用户考试记录', '用户考试记录管理', 'DELETE'),
|
||
('/userExam/updateUserExam', '更新用户考试记录', '用户考试记录管理', 'PUT'),
|
||
('/userExam/startExam', '开始考试', '用户考试记录管理', 'POST'),
|
||
('/userExam/submitExam', '提交考试', '用户考试记录管理', 'PUT'),
|
||
('/userExam/findUserExam', '根据ID获取用户考试记录', '用户考试记录管理', 'GET'),
|
||
('/userExam/getUserExamList', '获取用户考试记录列表', '用户考试记录管理', 'GET'),
|
||
('/userExam/getUserExamHistory', '获取用户考试历史', '用户考试记录管理', 'GET'),
|
||
('/userExam/getUserExamStatistics', '获取用户考试统计', '用户考试记录管理', 'GET'),
|
||
('/userExam/getExamRanking', '获取考试排行榜', '用户考试记录管理', 'GET');
|
||
|
||
-- =============================================
|
||
-- 验证查询
|
||
-- =============================================
|
||
-- 验证字典是否创建成功
|
||
-- SELECT d.name, d.type, COUNT(dd.id) as detail_count
|
||
-- FROM sys_dictionaries d
|
||
-- LEFT JOIN sys_dictionary_details dd ON d.id = dd.sys_dictionary_id
|
||
-- WHERE d.type LIKE '%course%' OR d.type LIKE '%chapter%' OR d.type LIKE '%knowledge%'
|
||
-- OR d.type LIKE '%question%' OR d.type LIKE '%learning%' OR d.type LIKE '%exam%'
|
||
-- GROUP BY d.id, d.name, d.type
|
||
-- ORDER BY d.id;
|
||
|
||
-- 验证菜单是否创建成功
|
||
-- SELECT id, menu_level, parent_id, path, name, title, icon
|
||
-- FROM sys_base_menus
|
||
-- WHERE name IN ('learning', 'student') OR parent_id IN (
|
||
-- SELECT id FROM sys_base_menus WHERE name IN ('learning', 'student')
|
||
-- )
|
||
-- ORDER BY menu_level, parent_id, sort;
|
||
|
||
-- 验证API是否创建成功
|
||
-- SELECT api_group, COUNT(*) as api_count
|
||
-- FROM sys_apis
|
||
-- WHERE api_group IN ('课程管理', '章节管理', '知识点管理', '题目管理', '考试管理', '学习记录管理', '错题本管理')
|
||
-- GROUP BY api_group
|
||
-- ORDER BY api_group;
|
||
|
||
-- =============================================
|
||
-- 执行说明
|
||
-- =============================================
|
||
-- 1. 此脚本包含学习系统的完整初始化配置
|
||
-- 2. 包含8个数据字典和对应的字典详情
|
||
-- 3. 包含教学管理和在线学习两个主菜单及其子菜单
|
||
-- 4. 包含所有学习系统模块的API权限配置
|
||
-- 5. 执行后可在系统管理中查看和管理这些配置
|
||
-- 6. 如需配置用户角色权限，请执行 learning_system_roles.sql
|