998211c483
- 从服务器拉取完整代码 - 按框架规范整理项目结构 - 配置 Drone CI 测试环境部署 - 包含后端(FastAPI)、前端(Vue3)、管理端 技术栈: Vue3 + TypeScript + FastAPI + MySQL
132 lines
6.3 KiB
SQL
132 lines
6.3 KiB
SQL
-- 课程相关表结构
|
||
-- 用于手动创建课程模块所需的数据库表
|
||
|
||
-- 1. 课程表
|
||
CREATE TABLE IF NOT EXISTS `courses` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(200) NOT NULL COMMENT '课程名称',
|
||
`description` TEXT COMMENT '课程描述',
|
||
`category` ENUM('technology', 'management', 'business', 'general') NOT NULL DEFAULT 'general' COMMENT '课程分类',
|
||
`status` ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft' COMMENT '课程状态',
|
||
`cover_image` VARCHAR(500) COMMENT '封面图片URL',
|
||
`duration_hours` FLOAT COMMENT '课程时长(小时)',
|
||
`difficulty_level` INT COMMENT '难度等级(1-5)',
|
||
`tags` JSON COMMENT '标签列表',
|
||
`published_at` DATETIME COMMENT '发布时间',
|
||
`publisher_id` INT COMMENT '发布人ID',
|
||
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序顺序',
|
||
`is_featured` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否推荐',
|
||
|
||
-- 基础字段
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
-- 软删除字段
|
||
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
||
`deleted_at` DATETIME,
|
||
|
||
-- 审计字段
|
||
`created_by` INT,
|
||
`updated_by` INT,
|
||
|
||
PRIMARY KEY (`id`),
|
||
INDEX `idx_courses_status` (`status`),
|
||
INDEX `idx_courses_category` (`category`),
|
||
INDEX `idx_courses_is_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程表';
|
||
|
||
-- 2. 课程资料表
|
||
CREATE TABLE IF NOT EXISTS `course_materials` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`course_id` INT NOT NULL COMMENT '课程ID',
|
||
`name` VARCHAR(200) NOT NULL COMMENT '资料名称',
|
||
`description` TEXT COMMENT '资料描述',
|
||
`file_url` VARCHAR(500) NOT NULL COMMENT '文件URL',
|
||
`file_type` VARCHAR(50) NOT NULL COMMENT '文件类型',
|
||
`file_size` INT NOT NULL COMMENT '文件大小(字节)',
|
||
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序顺序',
|
||
|
||
-- 基础字段
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
-- 软删除字段
|
||
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
||
`deleted_at` DATETIME,
|
||
|
||
PRIMARY KEY (`id`),
|
||
FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE,
|
||
INDEX `idx_course_materials_course_id` (`course_id`),
|
||
INDEX `idx_course_materials_is_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='课程资料表';
|
||
|
||
-- 3. 知识点表
|
||
CREATE TABLE IF NOT EXISTS `knowledge_points` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`course_id` INT NOT NULL COMMENT '课程ID',
|
||
`name` VARCHAR(200) NOT NULL COMMENT '知识点名称',
|
||
`description` TEXT COMMENT '知识点描述',
|
||
`parent_id` INT COMMENT '父知识点ID',
|
||
`level` INT NOT NULL DEFAULT 1 COMMENT '层级深度',
|
||
`path` VARCHAR(500) COMMENT '路径(如: 1.2.3)',
|
||
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序顺序',
|
||
`weight` FLOAT NOT NULL DEFAULT 1.0 COMMENT '权重',
|
||
`is_required` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否必修',
|
||
`estimated_hours` FLOAT COMMENT '预计学习时间(小时)',
|
||
|
||
-- 基础字段
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
-- 软删除字段
|
||
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
||
`deleted_at` DATETIME,
|
||
|
||
PRIMARY KEY (`id`),
|
||
FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`parent_id`) REFERENCES `knowledge_points`(`id`) ON DELETE CASCADE,
|
||
INDEX `idx_knowledge_points_course_id` (`course_id`),
|
||
INDEX `idx_knowledge_points_parent_id` (`parent_id`),
|
||
INDEX `idx_knowledge_points_is_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='知识点表';
|
||
|
||
-- 4. 成长路径表
|
||
CREATE TABLE IF NOT EXISTS `growth_paths` (
|
||
`id` INT NOT NULL AUTO_INCREMENT,
|
||
`name` VARCHAR(200) NOT NULL COMMENT '路径名称',
|
||
`description` TEXT COMMENT '路径描述',
|
||
`target_role` VARCHAR(100) COMMENT '目标角色',
|
||
`courses` JSON COMMENT '课程列表[{course_id, order, is_required}]',
|
||
`estimated_duration_days` INT COMMENT '预计完成天数',
|
||
`is_active` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否启用',
|
||
`sort_order` INT NOT NULL DEFAULT 0 COMMENT '排序顺序',
|
||
|
||
-- 基础字段
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
||
-- 软删除字段
|
||
`is_deleted` BOOLEAN NOT NULL DEFAULT FALSE,
|
||
`deleted_at` DATETIME,
|
||
|
||
PRIMARY KEY (`id`),
|
||
INDEX `idx_growth_paths_is_active` (`is_active`),
|
||
INDEX `idx_growth_paths_is_deleted` (`is_deleted`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='成长路径表';
|
||
|
||
-- 添加测试数据
|
||
INSERT INTO `courses` (`name`, `description`, `category`, `status`, `difficulty_level`, `tags`) VALUES
|
||
('Python基础入门', 'Python编程语言基础教程，适合零基础学员', 'technology', 'published', 1, '["Python", "编程基础", "入门"]'),
|
||
('项目管理实战', '敏捷项目管理方法论与实践', 'management', 'published', 3, '["项目管理", "敏捷", "Scrum"]'),
|
||
('商业分析技巧', '商业数据分析与决策支持', 'business', 'draft', 2, '["数据分析", "商业智能", "Excel"]');
|
||
|
||
-- 为Python课程添加知识点
|
||
INSERT INTO `knowledge_points` (`course_id`, `name`, `description`, `parent_id`, `level`, `path`, `sort_order`, `weight`, `is_required`, `estimated_hours`) VALUES
|
||
(1, 'Python基础语法', 'Python语言基础语法知识', NULL, 1, '1', 1, 1.0, TRUE, 5.0),
|
||
(1, '变量与数据类型', '学习Python中的变量定义和基本数据类型', 1, 2, '1.1', 1, 0.8, TRUE, 2.0),
|
||
(1, '控制流程', '条件语句和循环结构', 1, 2, '1.2', 2, 0.9, TRUE, 3.0);
|
||
|
||
-- 添加成长路径
|
||
INSERT INTO `growth_paths` (`name`, `description`, `target_role`, `courses`, `estimated_duration_days`) VALUES
|
||
('Python开发工程师', '从零基础到Python开发工程师的成长路径', 'Python开发工程师', '[{"course_id": 1, "order": 1, "is_required": true}]', 90);
|