145 lines
7.7 KiB
SQL
145 lines
7.7 KiB
SQL
|
-- 自动生成：incid_db 建库建表脚本
|
|||
|
|
CREATE DATABASE IF NOT EXISTS incid_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|||
|
|
USE incid_db;
|
|||
|
|
|
|||
|
|
CREATE TABLE accid_list (
|
|||
|
|
id INTEGER NOT NULL COMMENT '主键ID，自增' AUTO_INCREMENT,
|
|||
|
|
ticket_id VARCHAR(50) NOT NULL COMMENT '工单ID',
|
|||
|
|
model VARCHAR(100) COMMENT '型号',
|
|||
|
|
vin VARCHAR(50) NOT NULL COMMENT '车辆识别码(VIN)',
|
|||
|
|
creator VARCHAR(50) NOT NULL COMMENT '创建人（用户名，如ZHANMEN）',
|
|||
|
|
create_time DATETIME NOT NULL COMMENT '工单新建时间',
|
|||
|
|
occur_datetime DATETIME NOT NULL COMMENT '问题发生时间',
|
|||
|
|
case_description TEXT NOT NULL COMMENT 'Case描述',
|
|||
|
|
fo_solution TEXT NOT NULL COMMENT 'Fo解答',
|
|||
|
|
category VARCHAR(100) NOT NULL COMMENT '分类',
|
|||
|
|
keywords VARCHAR(200) NOT NULL COMMENT '关键词（多个用逗号分隔）',
|
|||
|
|
handle_status VARCHAR(20) COMMENT '处理状态：已完成/未完成',
|
|||
|
|
create_at DATETIME COMMENT '记录插入数据库时间',
|
|||
|
|
update_at DATETIME COMMENT '记录最后更新时间',
|
|||
|
|
bmbs_name VARCHAR(50) COMMENT 'BMBS联系人',
|
|||
|
|
accident_level ENUM('P1','P2','P3') COMMENT '事故评级',
|
|||
|
|
rdca_reporter VARCHAR(50) COMMENT 'RDCA报告人',
|
|||
|
|
carline VARCHAR(100) COMMENT '车系（关联字典表dict_code=carline）',
|
|||
|
|
software_version VARCHAR(50) COMMENT '软件版本',
|
|||
|
|
labels VARCHAR(100) COMMENT '事故类型标签（关联字典表dict_code=labels）',
|
|||
|
|
attachment_url VARCHAR(255) COMMENT '附件地址（多个用逗号分隔）',
|
|||
|
|
assignee_name VARCHAR(50) COMMENT '经办人（关联字典表/用户表）',
|
|||
|
|
affects_versions VARCHAR(100) COMMENT '受影响的版本（关联字典表dict_code=affects_versions）',
|
|||
|
|
components VARCHAR(100) COMMENT '零部件/组件（关联字典表dict_code=components）',
|
|||
|
|
fix_versions VARCHAR(100) COMMENT '修复方案对应的版本（关联字典表dict_code=fix_versions）',
|
|||
|
|
subtasks VARCHAR(200) NOT NULL COMMENT '子任务（多个用逗号分隔）',
|
|||
|
|
linked_work_items VARCHAR(200) NOT NULL COMMENT '关联的工作项（格式：类型:ID,类型:ID）',
|
|||
|
|
descriptions VARCHAR(255) NOT NULL COMMENT '补充描述（区别于case_description）',
|
|||
|
|
mviz_link VARCHAR(255) NOT NULL COMMENT 'mviz的链接',
|
|||
|
|
system_function VARCHAR(255) NOT NULL COMMENT 'system_function字段',
|
|||
|
|
image_ids VARCHAR(255) NOT NULL COMMENT '关联图片ID串，逗号分隔（如：1,2,3）',
|
|||
|
|
PRIMARY KEY (id)
|
|||
|
|
)CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC ENGINE=InnoDB COLLATE utf8mb4_unicode_ci;
|
|||
|
|
CREATE INDEX idx_carline ON accid_list (carline);
|
|||
|
|
CREATE INDEX idx_labels ON accid_list (labels);
|
|||
|
|
CREATE INDEX idx_components ON accid_list (components);
|
|||
|
|
CREATE INDEX idx_ticket_id ON accid_list (ticket_id);
|
|||
|
|
CREATE INDEX idx_affects_versions ON accid_list (affects_versions);
|
|||
|
|
CREATE INDEX idx_accident_level ON accid_list (accident_level);
|
|||
|
|
CREATE INDEX idx_create_time ON accid_list (create_time);
|
|||
|
|
CREATE INDEX idx_fix_versions ON accid_list (fix_versions);
|
|||
|
|
CREATE INDEX idx_creator ON accid_list (creator);
|
|||
|
|
CREATE INDEX idx_assignee_name ON accid_list (assignee_name);
|
|||
|
|
|
|||
|
|
CREATE TABLE dict_type (
|
|||
|
|
id INTEGER NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
|||
|
|
dict_code VARCHAR(50) NOT NULL COMMENT '字典编码（对应accid_list的字段名，如carline、labels）',
|
|||
|
|
dict_name VARCHAR(50) NOT NULL COMMENT '字典名称（字段中文名称，如车系、事故类型）',
|
|||
|
|
remark VARCHAR(200) COMMENT '备注（如“事故工单-车系下拉选项”）',
|
|||
|
|
status INTEGER NOT NULL COMMENT '状态：1-启用，0-禁用',
|
|||
|
|
create_time DATETIME COMMENT '创建时间',
|
|||
|
|
update_time DATETIME COMMENT '更新时间',
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
CONSTRAINT uk_dict_code UNIQUE (dict_code)
|
|||
|
|
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='字典类型表（管理需要下拉的字段）' ROW_FORMAT=Dynamic COLLATE utf8mb4_unicode_ci;
|
|||
|
|
|
|||
|
|
CREATE TABLE file_uploads (
|
|||
|
|
id INTEGER NOT NULL COMMENT '主键（自增数值ID）' AUTO_INCREMENT,
|
|||
|
|
file_name VARCHAR(255) NOT NULL COMMENT '原始文件名（如：事故图片1.jpg）',
|
|||
|
|
file_alias VARCHAR(255) NOT NULL COMMENT '存储别名（避免重名，如：20260210_153950_89782.jpg）',
|
|||
|
|
file_path VARCHAR(512) NOT NULL COMMENT '存储路径/访问URL（本地：/uploads/202602/xxx.jpg；OSS：https://xxx.oss-cn-beijing.aliyuncs.com/xxx.jpg）',
|
|||
|
|
file_size INTEGER NOT NULL COMMENT '文件大小（字节）',
|
|||
|
|
file_type VARCHAR(50) NOT NULL COMMENT '文件MIME类型（如：image/jpeg、image/png）',
|
|||
|
|
business_type VARCHAR(50) COMMENT '关联业务类型（如：case=案例、user=用户头像）',
|
|||
|
|
business_id INTEGER COMMENT '关联业务主键（案例ID/用户ID等，初始为NULL）',
|
|||
|
|
create_time DATETIME NOT NULL COMMENT '上传时间',
|
|||
|
|
create_by VARCHAR(50) COMMENT '上传人（用户ID/用户名）',
|
|||
|
|
status INTEGER NOT NULL COMMENT '状态：1=有效，0=逻辑删除',
|
|||
|
|
PRIMARY KEY (id)
|
|||
|
|
)CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC ENGINE=InnoDB COLLATE utf8mb4_unicode_ci;
|
|||
|
|
CREATE INDEX idx_business_type_id ON file_uploads (business_type, business_id);
|
|||
|
|
CREATE INDEX idx_create_time ON file_uploads (create_time);
|
|||
|
|
CREATE INDEX idx_status ON file_uploads (status);
|
|||
|
|
|
|||
|
|
CREATE TABLE users (
|
|||
|
|
id INTEGER NOT NULL AUTO_INCREMENT,
|
|||
|
|
username VARCHAR(50) NOT NULL,
|
|||
|
|
password VARCHAR(255) NOT NULL,
|
|||
|
|
status INTEGER COMMENT '用户状态：1-正常，0-禁用',
|
|||
|
|
created_at DATETIME COMMENT '创建时间',
|
|||
|
|
updated_at DATETIME COMMENT '更新时间',
|
|||
|
|
email VARCHAR(255) COMMENT '邮箱',
|
|||
|
|
avatar VARCHAR(255) COMMENT '头像（通常存储图片URL）',
|
|||
|
|
region VARCHAR(255) COMMENT '国家地区',
|
|||
|
|
phone VARCHAR(255) COMMENT '电话',
|
|||
|
|
brief VARCHAR(255) COMMENT '个人简介',
|
|||
|
|
third_party_account VARCHAR(255) COMMENT '第三方账号（如微信、QQ等）',
|
|||
|
|
position VARCHAR(255) COMMENT '职务',
|
|||
|
|
department VARCHAR(255) COMMENT '部门',
|
|||
|
|
label VARCHAR(255) COMMENT '个人标签（多个标签可逗号分隔）',
|
|||
|
|
created_by INTEGER,
|
|||
|
|
updated_by INTEGER,
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE (username),
|
|||
|
|
FOREIGN KEY(created_by) REFERENCES users (id),
|
|||
|
|
FOREIGN KEY(updated_by) REFERENCES users (id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE TABLE dict_item (
|
|||
|
|
id INTEGER NOT NULL COMMENT '主键ID' AUTO_INCREMENT,
|
|||
|
|
dict_id INTEGER NOT NULL COMMENT '关联字典类型表的ID',
|
|||
|
|
item_value VARCHAR(100) NOT NULL COMMENT '选项值（存储到accid_list的实际值，如“大众帕萨特”“P1”）',
|
|||
|
|
item_label VARCHAR(100) NOT NULL COMMENT '选项标签（前端显示的文本，如“大众帕萨特（车系）”）',
|
|||
|
|
sort INTEGER COMMENT '排序号（前端下拉框显示顺序，数字越小越靠前）',
|
|||
|
|
status INTEGER NOT NULL COMMENT '状态：1-启用，0-禁用（禁用后前端不显示）',
|
|||
|
|
remark VARCHAR(200) COMMENT '备注（如“2025款帕萨特专属”）',
|
|||
|
|
create_time DATETIME COMMENT '创建时间',
|
|||
|
|
update_time DATETIME COMMENT '更新时间',
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
FOREIGN KEY(dict_id) REFERENCES dict_type (id)
|
|||
|
|
)CHARSET=utf8mb4 ENGINE=InnoDB COMMENT='字典项表（管理每个字段的具体下拉选项）' ROW_FORMAT=Dynamic COLLATE utf8mb4_unicode_ci;
|
|||
|
|
CREATE INDEX idx_item_value ON dict_item (item_value);
|
|||
|
|
CREATE INDEX idx_dict_id ON dict_item (dict_id);
|
|||
|
|
|
|||
|
|
CREATE TABLE roles (
|
|||
|
|
id INTEGER NOT NULL AUTO_INCREMENT,
|
|||
|
|
name VARCHAR(50) NOT NULL COMMENT '角色名称',
|
|||
|
|
description VARCHAR(255) COMMENT '角色描述',
|
|||
|
|
status INTEGER COMMENT '状态：1-启用，0-禁用',
|
|||
|
|
created_at DATETIME,
|
|||
|
|
updated_at DATETIME,
|
|||
|
|
created_by INTEGER,
|
|||
|
|
updated_by INTEGER,
|
|||
|
|
PRIMARY KEY (id),
|
|||
|
|
UNIQUE (name),
|
|||
|
|
FOREIGN KEY(created_by) REFERENCES users (id),
|
|||
|
|
FOREIGN KEY(updated_by) REFERENCES users (id)
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
CREATE TABLE user_roles (
|
|||
|
|
user_id INTEGER NOT NULL,
|
|||
|
|
role_id INTEGER NOT NULL,
|
|||
|
|
created_at DATETIME,
|
|||
|
|
created_by INTEGER,
|
|||
|
|
PRIMARY KEY (user_id, role_id),
|
|||
|
|
FOREIGN KEY(user_id) REFERENCES users (id),
|
|||
|
|
FOREIGN KEY(role_id) REFERENCES roles (id),
|
|||
|
|
FOREIGN KEY(created_by) REFERENCES users (id)
|
|||
|
|
);
|