998211c483
- 从服务器拉取完整代码 - 按框架规范整理项目结构 - 配置 Drone CI 测试环境部署 - 包含后端(FastAPI)、前端(Vue3)、管理端 技术栈: Vue3 + TypeScript + FastAPI + MySQL
51 lines
2.5 KiB
SQL
51 lines
2.5 KiB
SQL
-- 创建任务表
|
||
CREATE TABLE `tasks` (
|
||
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
`title` VARCHAR(200) NOT NULL COMMENT '任务标题',
|
||
`description` TEXT COMMENT '任务描述',
|
||
`priority` ENUM('low', 'medium', 'high') DEFAULT 'medium' COMMENT '优先级',
|
||
`status` ENUM('pending', 'ongoing', 'completed', 'expired') DEFAULT 'pending' COMMENT '任务状态',
|
||
`creator_id` INT NOT NULL COMMENT '创建人ID',
|
||
`deadline` DATETIME COMMENT '截止时间',
|
||
`requirements` JSON COMMENT '任务要求配置',
|
||
`progress` INT DEFAULT 0 COMMENT '完成进度(0-100)',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
`is_deleted` BOOLEAN DEFAULT FALSE,
|
||
INDEX `idx_status` (`status`),
|
||
INDEX `idx_creator` (`creator_id`),
|
||
INDEX `idx_deadline` (`deadline`),
|
||
FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务表';
|
||
|
||
-- 创建任务课程关联表
|
||
CREATE TABLE `task_courses` (
|
||
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
`task_id` INT NOT NULL COMMENT '任务ID',
|
||
`course_id` INT NOT NULL COMMENT '课程ID',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE KEY `uk_task_course` (`task_id`, `course_id`),
|
||
FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`course_id`) REFERENCES `courses`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务课程关联表';
|
||
|
||
-- 创建任务分配表
|
||
CREATE TABLE `task_assignments` (
|
||
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
||
`task_id` INT NOT NULL COMMENT '任务ID',
|
||
`user_id` INT NOT NULL COMMENT '分配用户ID',
|
||
`team_id` INT DEFAULT NULL COMMENT '团队ID（如果按团队分配）',
|
||
`status` ENUM('not_started', 'in_progress', 'completed') DEFAULT 'not_started' COMMENT '完成状态',
|
||
`progress` INT DEFAULT 0 COMMENT '个人完成进度(0-100)',
|
||
`completed_at` DATETIME COMMENT '完成时间',
|
||
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
UNIQUE KEY `uk_task_user` (`task_id`, `user_id`),
|
||
INDEX `idx_status` (`status`),
|
||
FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE,
|
||
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='任务分配表';
|
||
|
||
|
||
|