754 lines
26 KiB
SQL
754 lines
26 KiB
SQL
-- MySQL建表语句（从Oracle转换）
|
||
-- 系统管理表
|
||
|
||
-- 部门表
|
||
CREATE TABLE sys_dept (
|
||
dept_id INT NOT NULL AUTO_INCREMENT,
|
||
parent_id INT NULL,
|
||
ancestors VARCHAR(50) NULL,
|
||
dept_code VARCHAR(50) NULL,
|
||
dept_name VARCHAR(600) NOT NULL,
|
||
order_num INT NULL,
|
||
leader VARCHAR(30) NULL,
|
||
phone VARCHAR(20) NULL,
|
||
email VARCHAR(50) NULL,
|
||
status CHAR(1) NULL,
|
||
del_flag CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
PRIMARY KEY (dept_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
|
||
|
||
-- 用户表
|
||
CREATE TABLE sys_user (
|
||
user_id INT NOT NULL AUTO_INCREMENT,
|
||
user_code VARCHAR(100) NULL,
|
||
dept_id INT NULL,
|
||
user_name VARCHAR(100) NULL,
|
||
nick_name VARCHAR(30) NOT NULL,
|
||
user_type CHAR(2) NULL,
|
||
email VARCHAR(50) NULL,
|
||
phonenumber VARCHAR(20) NULL,
|
||
sex CHAR(1) NULL,
|
||
avatar VARCHAR(300) NULL,
|
||
password VARCHAR(100) NULL,
|
||
status CHAR(1) NULL,
|
||
del_flag CHAR(1) NULL,
|
||
login_ip VARCHAR(128) NULL,
|
||
login_date DATETIME NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (user_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
|
||
|
||
-- 岗位表
|
||
CREATE TABLE sys_post (
|
||
post_id INT NOT NULL AUTO_INCREMENT,
|
||
post_code VARCHAR(64) NULL,
|
||
post_name VARCHAR(50) NOT NULL,
|
||
post_sort INT NULL,
|
||
status CHAR(1) NULL,
|
||
del_flag CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (post_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='岗位表';
|
||
|
||
-- 角色表
|
||
CREATE TABLE sys_role (
|
||
role_id INT NOT NULL AUTO_INCREMENT,
|
||
role_name VARCHAR(90) NOT NULL,
|
||
role_key VARCHAR(200) NULL,
|
||
role_sort INT NULL,
|
||
data_scope CHAR(1) NULL,
|
||
menu_check_strictly INT NULL,
|
||
dept_check_strictly INT NULL,
|
||
status CHAR(1) NULL,
|
||
del_flag CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (role_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';
|
||
|
||
-- 菜单表
|
||
CREATE TABLE sys_menu (
|
||
menu_id INT NOT NULL AUTO_INCREMENT,
|
||
menu_name VARCHAR(120) NOT NULL,
|
||
parent_id INT NULL,
|
||
order_num INT NULL,
|
||
path VARCHAR(200) NULL,
|
||
component VARCHAR(300) NULL,
|
||
is_frame INT NULL,
|
||
is_cache INT NULL,
|
||
menu_type CHAR(1) NULL,
|
||
visible CHAR(1) NULL,
|
||
status CHAR(1) NULL,
|
||
del_flag CHAR(1) NULL,
|
||
perms VARCHAR(100) NULL,
|
||
icon VARCHAR(100) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (menu_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菜单表';
|
||
|
||
-- 用户角色关联表
|
||
CREATE TABLE sys_user_role (
|
||
user_id INT NOT NULL,
|
||
role_id INT NOT NULL,
|
||
PRIMARY KEY (user_id, role_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';
|
||
|
||
-- 角色菜单关联表
|
||
CREATE TABLE sys_role_menu (
|
||
role_id INT NOT NULL,
|
||
menu_id INT NOT NULL,
|
||
PRIMARY KEY (role_id, menu_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色菜单关联表';
|
||
|
||
-- 角色部门关联表
|
||
CREATE TABLE sys_role_dept (
|
||
role_id INT NOT NULL,
|
||
dept_id INT NOT NULL,
|
||
PRIMARY KEY (role_id, dept_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色部门关联表';
|
||
|
||
-- 用户岗位关联表
|
||
CREATE TABLE sys_user_post (
|
||
user_id INT NOT NULL,
|
||
post_id INT NOT NULL,
|
||
PRIMARY KEY (user_id, post_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户岗位关联表';
|
||
|
||
-- 操作日志表
|
||
CREATE TABLE sys_oper_log (
|
||
oper_id INT NOT NULL AUTO_INCREMENT,
|
||
title VARCHAR(120) NOT NULL,
|
||
business_type INT NULL,
|
||
method VARCHAR(120) NULL,
|
||
request_method VARCHAR(120) NULL,
|
||
operator_type INT NULL,
|
||
oper_name VARCHAR(50) NULL,
|
||
dept_name VARCHAR(120) NULL,
|
||
oper_url VARCHAR(300) NULL,
|
||
oper_ip VARCHAR(128) NULL,
|
||
oper_location VARCHAR(300) NULL,
|
||
oper_param TEXT NULL,
|
||
json_result TEXT NULL,
|
||
status CHAR(1) NULL,
|
||
error_msg TEXT NULL,
|
||
oper_time DATETIME NULL,
|
||
PRIMARY KEY (oper_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';
|
||
|
||
-- 字典类型表
|
||
CREATE TABLE sys_dict_type (
|
||
dict_id INT NOT NULL AUTO_INCREMENT,
|
||
dict_name VARCHAR(200) NOT NULL,
|
||
dict_type VARCHAR(50) NULL,
|
||
status CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (dict_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字典类型表';
|
||
|
||
-- 字典数据表
|
||
CREATE TABLE sys_dict_data (
|
||
dict_code INT NOT NULL AUTO_INCREMENT,
|
||
dict_sort INT NULL,
|
||
dict_label VARCHAR(100) NOT NULL,
|
||
dict_value VARCHAR(100) NOT NULL,
|
||
dict_type VARCHAR(100) NULL,
|
||
css_class VARCHAR(100) NULL,
|
||
list_class VARCHAR(100) NULL,
|
||
is_default CHAR(1) NULL,
|
||
status CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (dict_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字典数据表';
|
||
|
||
-- 参数配置表
|
||
CREATE TABLE sys_config (
|
||
config_id INT NOT NULL AUTO_INCREMENT,
|
||
config_name VARCHAR(100) NOT NULL,
|
||
config_key VARCHAR(100) NULL,
|
||
config_value VARCHAR(100) NULL,
|
||
config_type CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (config_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='参数配置表';
|
||
|
||
-- 登录日志表
|
||
CREATE TABLE sys_logininfor (
|
||
info_id INT NOT NULL AUTO_INCREMENT,
|
||
user_name VARCHAR(50) NOT NULL,
|
||
ipaddr VARCHAR(128) NULL,
|
||
login_location VARCHAR(300) NULL,
|
||
browser VARCHAR(50) NULL,
|
||
os VARCHAR(50) NULL,
|
||
status CHAR(1) NULL,
|
||
msg VARCHAR(600) NULL,
|
||
login_time DATETIME NULL,
|
||
PRIMARY KEY (info_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录日志表';
|
||
|
||
-- 定时任务表
|
||
CREATE TABLE sys_job (
|
||
job_id INT NOT NULL AUTO_INCREMENT,
|
||
job_name VARCHAR(120) NOT NULL,
|
||
job_group VARCHAR(120) NULL,
|
||
invoke_target VARCHAR(500) NULL,
|
||
cron_expression VARCHAR(300) NULL,
|
||
misfire_policy VARCHAR(20) NULL,
|
||
concurrent CHAR(1) NULL,
|
||
status CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (job_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务表';
|
||
|
||
-- 定时任务日志表
|
||
CREATE TABLE sys_job_log (
|
||
job_log_id INT NOT NULL AUTO_INCREMENT,
|
||
job_name VARCHAR(120) NOT NULL,
|
||
job_group VARCHAR(120) NULL,
|
||
invoke_target VARCHAR(500) NULL,
|
||
job_message VARCHAR(500) NULL,
|
||
status CHAR(1) NULL,
|
||
exception_info TEXT NULL,
|
||
create_time DATETIME NULL,
|
||
PRIMARY KEY (job_log_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务日志表';
|
||
|
||
-- 通知公告表
|
||
CREATE TABLE sys_notice (
|
||
notice_id INT NOT NULL AUTO_INCREMENT,
|
||
notice_title VARCHAR(120) NOT NULL,
|
||
notice_type CHAR(1) NULL,
|
||
notice_content TEXT NULL,
|
||
status CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (notice_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知公告表';
|
||
|
||
-- 用户组表
|
||
CREATE TABLE sys_group (
|
||
group_id INT NOT NULL AUTO_INCREMENT,
|
||
group_code VARCHAR(50) NOT NULL,
|
||
group_name VARCHAR(120) NOT NULL,
|
||
group_describe VARCHAR(200) NOT NULL,
|
||
status CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
PRIMARY KEY (group_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户组表';
|
||
|
||
-- 用户组成员表
|
||
CREATE TABLE sys_group_person (
|
||
group_code VARCHAR(50) NOT NULL,
|
||
person_code VARCHAR(120) NOT NULL,
|
||
order_id INT NULL,
|
||
PRIMARY KEY (group_code, person_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户组成员表';
|
||
|
||
-- 用户选择表
|
||
CREATE TABLE sys_user_choice (
|
||
id INT NOT NULL AUTO_INCREMENT,
|
||
choice_code VARCHAR(50) NOT NULL,
|
||
user_code VARCHAR(100) NULL,
|
||
order_id INT NULL,
|
||
PRIMARY KEY (id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户选择表';
|
||
|
||
-- 代码生成业务表
|
||
CREATE TABLE gen_table (
|
||
table_id INT NOT NULL AUTO_INCREMENT,
|
||
table_name VARCHAR(120) NOT NULL,
|
||
table_comment VARCHAR(500) NULL,
|
||
sub_table_name VARCHAR(120) NULL,
|
||
sub_table_fk_name VARCHAR(120) NULL,
|
||
class_name VARCHAR(100) NULL,
|
||
tpl_category VARCHAR(200) NULL,
|
||
package_name VARCHAR(100) NULL,
|
||
module_name VARCHAR(30) NULL,
|
||
business_name VARCHAR(30) NULL,
|
||
function_name VARCHAR(50) NULL,
|
||
function_author VARCHAR(50) NULL,
|
||
gen_type CHAR(1) NULL,
|
||
gen_path VARCHAR(200) NULL,
|
||
options VARCHAR(1000) NULL,
|
||
status CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (table_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代码生成业务表';
|
||
|
||
-- 代码生成业务表字段
|
||
CREATE TABLE gen_table_column (
|
||
column_id INT NOT NULL AUTO_INCREMENT,
|
||
table_id INT NOT NULL,
|
||
column_name VARCHAR(200) NOT NULL,
|
||
column_comment VARCHAR(500) NOT NULL,
|
||
column_type VARCHAR(100) NULL,
|
||
java_type VARCHAR(500) NULL,
|
||
java_field VARCHAR(200) NULL,
|
||
is_pk CHAR(1) NULL,
|
||
is_increment CHAR(1) NULL,
|
||
is_required CHAR(1) NULL,
|
||
is_insert CHAR(1) NULL,
|
||
is_edit CHAR(1) NULL,
|
||
is_list CHAR(1) NULL,
|
||
is_query CHAR(1) NULL,
|
||
query_type VARCHAR(200) NULL,
|
||
html_type VARCHAR(200) NULL,
|
||
dict_type VARCHAR(200) NULL,
|
||
sort INT NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
remark VARCHAR(600) NULL,
|
||
PRIMARY KEY (column_id, table_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代码生成业务表字段';
|
||
|
||
-- Quartz定时任务相关表
|
||
|
||
-- 存储每一个已配置的jobDetail的详细信息
|
||
CREATE TABLE qrtz_job_details (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
job_name VARCHAR(200) NOT NULL,
|
||
job_group VARCHAR(200) NOT NULL,
|
||
description VARCHAR(250) NULL,
|
||
job_class_name VARCHAR(250) NOT NULL,
|
||
is_durable VARCHAR(1) NOT NULL,
|
||
is_nonconcurrent VARCHAR(1) NOT NULL,
|
||
is_update_data VARCHAR(1) NOT NULL,
|
||
requests_recovery VARCHAR(1) NOT NULL,
|
||
job_data BLOB NULL,
|
||
PRIMARY KEY (sched_name, job_name, job_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz任务详情表';
|
||
|
||
-- 存储已配置的Trigger的信息
|
||
CREATE TABLE qrtz_triggers (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
trigger_name VARCHAR(200) NOT NULL,
|
||
trigger_group VARCHAR(200) NOT NULL,
|
||
job_name VARCHAR(200) NOT NULL,
|
||
job_group VARCHAR(200) NOT NULL,
|
||
description VARCHAR(250) NULL,
|
||
next_fire_time BIGINT NULL,
|
||
prev_fire_time BIGINT NULL,
|
||
priority BIGINT NULL,
|
||
trigger_state VARCHAR(16) NOT NULL,
|
||
trigger_type VARCHAR(8) NOT NULL,
|
||
start_time BIGINT NOT NULL,
|
||
end_time BIGINT NULL,
|
||
calendar_name VARCHAR(200) NULL,
|
||
misfire_instr SMALLINT NULL,
|
||
job_data BLOB NULL,
|
||
PRIMARY KEY (sched_name, trigger_name, trigger_group),
|
||
FOREIGN KEY (sched_name, job_name, job_group)
|
||
REFERENCES qrtz_job_details(sched_name, job_name, job_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz触发器表';
|
||
|
||
-- 存储简单的Trigger，包括重复次数、间隔以及已触发的次数
|
||
CREATE TABLE qrtz_simple_triggers (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
trigger_name VARCHAR(200) NOT NULL,
|
||
trigger_group VARCHAR(200) NOT NULL,
|
||
repeat_count INT NOT NULL,
|
||
repeat_interval BIGINT NOT NULL,
|
||
times_triggered INT NOT NULL,
|
||
PRIMARY KEY (sched_name, trigger_name, trigger_group),
|
||
FOREIGN KEY (sched_name, trigger_name, trigger_group)
|
||
REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz简单触发器表';
|
||
|
||
-- 存储Cron Trigger，包括Cron表达式和时区信息
|
||
CREATE TABLE qrtz_cron_triggers (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
trigger_name VARCHAR(200) NOT NULL,
|
||
trigger_group VARCHAR(200) NOT NULL,
|
||
cron_expression VARCHAR(120) NOT NULL,
|
||
time_zone_id VARCHAR(80),
|
||
PRIMARY KEY (sched_name, trigger_name, trigger_group),
|
||
FOREIGN KEY (sched_name, trigger_name, trigger_group)
|
||
REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz Cron触发器表';
|
||
|
||
-- 存储Trigger作为Blob类型存储(用于Quartz用户用JDBC创建他们自己定制的Trigger类型，JobStore并不知道如何存储实例的时候)
|
||
CREATE TABLE qrtz_blob_triggers (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
trigger_name VARCHAR(200) NOT NULL,
|
||
trigger_group VARCHAR(200) NOT NULL,
|
||
blob_data BLOB NULL,
|
||
PRIMARY KEY (sched_name, trigger_name, trigger_group),
|
||
FOREIGN KEY (sched_name, trigger_name, trigger_group)
|
||
REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz Blob触发器表';
|
||
|
||
-- 以Blob类型存储Quartz的日历信息，quartz可配置一个日历来指定一个时间范围
|
||
CREATE TABLE qrtz_calendars (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
calendar_name VARCHAR(200) NOT NULL,
|
||
calendar BLOB NOT NULL,
|
||
PRIMARY KEY (sched_name, calendar_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz日历表';
|
||
|
||
-- 存储已暂停的Trigger组的信息
|
||
CREATE TABLE qrtz_paused_trigger_grps (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
trigger_group VARCHAR(200) NOT NULL,
|
||
PRIMARY KEY (sched_name, trigger_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz暂停触发器组表';
|
||
|
||
-- 存储已触发Trigger的相关状态信息，以及相关联Job的执行信息
|
||
CREATE TABLE qrtz_fired_triggers (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
entry_id VARCHAR(95) NOT NULL,
|
||
trigger_name VARCHAR(200) NOT NULL,
|
||
trigger_group VARCHAR(200) NOT NULL,
|
||
instance_name VARCHAR(200) NOT NULL,
|
||
fired_time BIGINT NOT NULL,
|
||
sched_time BIGINT NOT NULL,
|
||
priority BIGINT NOT NULL,
|
||
state VARCHAR(16) NOT NULL,
|
||
job_name VARCHAR(200) NULL,
|
||
job_group VARCHAR(200) NULL,
|
||
is_nonconcurrent VARCHAR(1) NULL,
|
||
requests_recovery VARCHAR(1) NULL,
|
||
PRIMARY KEY (sched_name, entry_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz已触发触发器表';
|
||
|
||
-- 存储所有的Scheduler的状态信息，假如是在集群中，可以看到其他的Scheduler实例
|
||
CREATE TABLE qrtz_scheduler_state (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
instance_name VARCHAR(200) NOT NULL,
|
||
last_checkin_time BIGINT NOT NULL,
|
||
checkin_interval BIGINT NOT NULL,
|
||
PRIMARY KEY (sched_name, instance_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz调度器状态表';
|
||
|
||
-- 存储程序的悲观锁的信息(假如使用了悲观锁)
|
||
CREATE TABLE qrtz_locks (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
lock_name VARCHAR(40) NOT NULL,
|
||
PRIMARY KEY (sched_name, lock_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz锁表';
|
||
|
||
-- 存储Trigger的其他属性信息
|
||
CREATE TABLE qrtz_simprop_triggers (
|
||
sched_name VARCHAR(120) NOT NULL,
|
||
trigger_name VARCHAR(200) NOT NULL,
|
||
trigger_group VARCHAR(200) NOT NULL,
|
||
str_prop_1 VARCHAR(512) NULL,
|
||
str_prop_2 VARCHAR(512) NULL,
|
||
str_prop_3 VARCHAR(512) NULL,
|
||
int_prop_1 INT NULL,
|
||
int_prop_2 INT NULL,
|
||
long_prop_1 BIGINT NULL,
|
||
long_prop_2 BIGINT NULL,
|
||
dec_prop_1 DECIMAL(13,4) NULL,
|
||
dec_prop_2 DECIMAL(13,4) NULL,
|
||
bool_prop_1 VARCHAR(1) NULL,
|
||
bool_prop_2 VARCHAR(1) NULL,
|
||
PRIMARY KEY (sched_name, trigger_name, trigger_group),
|
||
FOREIGN KEY (sched_name, trigger_name, trigger_group)
|
||
REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz属性触发器表';
|
||
|
||
-- 创建Quartz相关索引
|
||
CREATE INDEX idx_qrtz_j_req_recovery ON qrtz_job_details(sched_name, requests_recovery);
|
||
CREATE INDEX idx_qrtz_j_grp ON qrtz_job_details(sched_name, job_group);
|
||
|
||
CREATE INDEX idx_qrtz_t_j ON qrtz_triggers(sched_name, job_name, job_group);
|
||
CREATE INDEX idx_qrtz_t_jg ON qrtz_triggers(sched_name, job_group);
|
||
CREATE INDEX idx_qrtz_t_c ON qrtz_triggers(sched_name, calendar_name);
|
||
CREATE INDEX idx_qrtz_t_g ON qrtz_triggers(sched_name, trigger_group);
|
||
CREATE INDEX idx_qrtz_t_state ON qrtz_triggers(sched_name, trigger_state);
|
||
CREATE INDEX idx_qrtz_t_n_state ON qrtz_triggers(sched_name, trigger_name, trigger_group, trigger_state);
|
||
CREATE INDEX idx_qrtz_t_n_g_state ON qrtz_triggers(sched_name, trigger_group, trigger_state);
|
||
CREATE INDEX idx_qrtz_t_next_fire_time ON qrtz_triggers(sched_name, next_fire_time);
|
||
CREATE INDEX idx_qrtz_t_nft_st ON qrtz_triggers(sched_name, trigger_state, next_fire_time);
|
||
CREATE INDEX idx_qrtz_t_nft_misfire ON qrtz_triggers(sched_name, misfire_instr, next_fire_time);
|
||
CREATE INDEX idx_qrtz_t_nft_st_misfire ON qrtz_triggers(sched_name, misfire_instr, next_fire_time, trigger_state);
|
||
CREATE INDEX idx_qrtz_t_nft_st_misfire_grp ON qrtz_triggers(sched_name, misfire_instr, next_fire_time, trigger_group, trigger_state);
|
||
|
||
CREATE INDEX idx_qrtz_ft_trig_inst_name ON qrtz_fired_triggers(sched_name, instance_name);
|
||
CREATE INDEX idx_qrtz_ft_inst_job_req_rcvry ON qrtz_fired_triggers(sched_name, instance_name, requests_recovery);
|
||
CREATE INDEX idx_qrtz_ft_j_g ON qrtz_fired_triggers(sched_name, job_name, job_group);
|
||
CREATE INDEX idx_qrtz_ft_jg ON qrtz_fired_triggers(sched_name, job_group);
|
||
CREATE INDEX idx_qrtz_ft_t_g ON qrtz_fired_triggers(sched_name, trigger_name, trigger_group);
|
||
CREATE INDEX idx_qrtz_ft_tg ON qrtz_fired_triggers(sched_name, trigger_group);
|
||
|
||
-- 文件管理表
|
||
CREATE TABLE fms_photo (
|
||
photo_id INT NOT NULL AUTO_INCREMENT,
|
||
file_name VARCHAR(600) NOT NULL,
|
||
photo_path VARCHAR(100) NOT NULL,
|
||
photo_url VARCHAR(100) NOT NULL,
|
||
original_name VARCHAR(600) NULL,
|
||
upload_name VARCHAR(100) NULL,
|
||
upload_dept VARCHAR(50) NULL,
|
||
upload_time DATETIME NULL,
|
||
status INT NULL,
|
||
PRIMARY KEY (photo_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图片管理表';
|
||
|
||
CREATE TABLE fms_files (
|
||
file_id INT NOT NULL AUTO_INCREMENT,
|
||
file_title VARCHAR(120) NULL,
|
||
file_describe VARCHAR(300) NULL,
|
||
file_name VARCHAR(600) NOT NULL,
|
||
file_path VARCHAR(100) NOT NULL,
|
||
file_url VARCHAR(100) NOT NULL,
|
||
file_suffix VARCHAR(30) NULL,
|
||
original_name VARCHAR(600) NULL,
|
||
upload_name VARCHAR(100) NULL,
|
||
upload_dept VARCHAR(50) NULL,
|
||
upload_time DATETIME NULL,
|
||
status INT NULL,
|
||
PRIMARY KEY (file_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件管理表';
|
||
|
||
-- 考试系统表
|
||
|
||
-- 考试类型表
|
||
CREATE TABLE exam_type (
|
||
type_id INT NOT NULL AUTO_INCREMENT,
|
||
parent_id INT NULL,
|
||
ancestors VARCHAR(50) NULL,
|
||
type_code VARCHAR(50) NULL,
|
||
type_name VARCHAR(120) NOT NULL,
|
||
order_id INT NULL,
|
||
status CHAR(1) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
update_by VARCHAR(100) NULL,
|
||
update_time DATETIME NULL,
|
||
PRIMARY KEY (type_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试类型表';
|
||
|
||
-- 题库表
|
||
CREATE TABLE exam_questions_bank (
|
||
bank_id INT NOT NULL AUTO_INCREMENT,
|
||
bank_code VARCHAR(50) NOT NULL,
|
||
bank_name VARCHAR(120) NOT NULL,
|
||
bank_describe VARCHAR(300) NULL,
|
||
bank_version VARCHAR(60) NULL,
|
||
exam_type INT NULL,
|
||
online_date DATETIME NULL,
|
||
picture_url VARCHAR(600) NULL,
|
||
radio_score INT NULL,
|
||
choice_score INT NULL,
|
||
judge_score INT NULL,
|
||
status INT NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_dept INT NULL,
|
||
create_time DATETIME NULL,
|
||
PRIMARY KEY (bank_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题库表';
|
||
|
||
-- 题库图片表
|
||
CREATE TABLE exam_bank_picture (
|
||
photo_id INT NOT NULL AUTO_INCREMENT,
|
||
photo_code VARCHAR(200) NOT NULL,
|
||
photo_path VARCHAR(200) NULL,
|
||
photo_url VARCHAR(200) NULL,
|
||
file_name VARCHAR(60) NULL,
|
||
original_name VARCHAR(200) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
status INT NULL,
|
||
PRIMARY KEY (photo_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题库图片表';
|
||
|
||
-- 题目内容表
|
||
CREATE TABLE exam_questions_content (
|
||
questions_code VARCHAR(50) NOT NULL,
|
||
questions_content TEXT NOT NULL,
|
||
PRIMARY KEY (questions_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目内容表';
|
||
|
||
-- 题目属性表
|
||
CREATE TABLE exam_questions_property (
|
||
bank_code VARCHAR(50) NOT NULL,
|
||
questions_id INT NOT NULL,
|
||
questions_code VARCHAR(50) NOT NULL,
|
||
questions_title VARCHAR(4000) NULL,
|
||
questions_type INT NULL,
|
||
questions_score INT NULL,
|
||
rate_number INT NULL,
|
||
right_answer VARCHAR(20) NULL,
|
||
answer_analyse VARCHAR(3000) NULL,
|
||
status INT NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_dept INT NULL,
|
||
create_time DATETIME NULL,
|
||
PRIMARY KEY (questions_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目属性表';
|
||
|
||
-- 题目答案表
|
||
CREATE TABLE exam_questions_answer (
|
||
questions_code VARCHAR(50) NOT NULL,
|
||
option_code CHAR(1) NOT NULL,
|
||
option_describe VARCHAR(600) NOT NULL,
|
||
is_right INT NULL,
|
||
order_id INT NULL,
|
||
PRIMARY KEY (questions_code, option_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目答案表';
|
||
|
||
-- 题目表
|
||
CREATE TABLE exam_questions (
|
||
bank_code VARCHAR(50) NOT NULL,
|
||
questions_id INT NOT NULL,
|
||
questions_code VARCHAR(50) NOT NULL,
|
||
questions_title VARCHAR(4000) NULL,
|
||
questions_content TEXT NULL,
|
||
questions_type INT NULL,
|
||
questions_score INT NULL,
|
||
rate_number INT NULL,
|
||
right_answer VARCHAR(20) NULL,
|
||
answer_analyse VARCHAR(3000) NULL,
|
||
status INT NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_dept INT NULL,
|
||
create_time DATETIME NULL,
|
||
PRIMARY KEY (questions_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目表';
|
||
|
||
-- 考试任务管理表
|
||
CREATE TABLE exam_task_manager (
|
||
exam_id INT NOT NULL AUTO_INCREMENT,
|
||
exam_code VARCHAR(50) NOT NULL,
|
||
exam_name VARCHAR(120) NOT NULL,
|
||
exam_describe VARCHAR(300) NULL,
|
||
build_type CHAR(1) NULL,
|
||
force_done CHAR(1) NULL,
|
||
exam_bank VARCHAR(600) NULL,
|
||
exam_bank_text VARCHAR(1200) NULL,
|
||
picture_url VARCHAR(200) NULL,
|
||
start_time DATETIME NULL,
|
||
end_time DATETIME NULL,
|
||
exam_duration VARCHAR(30) NULL,
|
||
status INT NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_dept INT NULL,
|
||
create_time DATETIME NULL,
|
||
PRIMARY KEY (exam_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务管理表';
|
||
|
||
-- 考试任务图片表
|
||
CREATE TABLE exam_task_picture (
|
||
photo_id INT NOT NULL AUTO_INCREMENT,
|
||
photo_code VARCHAR(200) NOT NULL,
|
||
photo_path VARCHAR(200) NULL,
|
||
photo_url VARCHAR(200) NULL,
|
||
file_name VARCHAR(60) NULL,
|
||
original_name VARCHAR(200) NULL,
|
||
create_by VARCHAR(100) NULL,
|
||
create_time DATETIME NULL,
|
||
status INT NULL,
|
||
PRIMARY KEY (photo_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务图片表';
|
||
|
||
-- 考试任务题目关联表
|
||
CREATE TABLE exam_task_questions (
|
||
exam_code VARCHAR(50) NOT NULL,
|
||
questions_code VARCHAR(50) NOT NULL,
|
||
questions_number INT NULL,
|
||
PRIMARY KEY (exam_code, questions_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务题目关联表';
|
||
|
||
-- 考试任务用户组关联表
|
||
CREATE TABLE exam_task_group (
|
||
exam_code VARCHAR(50) NOT NULL,
|
||
group_code VARCHAR(50) NOT NULL,
|
||
order_id INT NULL,
|
||
PRIMARY KEY (exam_code, group_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务用户组关联表';
|
||
|
||
-- 考试任务人员表
|
||
CREATE TABLE exam_task_person (
|
||
exam_code VARCHAR(50) NOT NULL,
|
||
user_code VARCHAR(120) NOT NULL,
|
||
task_code VARCHAR(50) NOT NULL,
|
||
start_time DATETIME NULL,
|
||
end_time DATETIME NULL,
|
||
status INT NULL,
|
||
PRIMARY KEY (exam_code, user_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务人员表';
|
||
|
||
-- 用户考试成绩表
|
||
CREATE TABLE exam_user_score (
|
||
exam_code VARCHAR(50) NOT NULL,
|
||
user_code VARCHAR(120) NOT NULL,
|
||
start_time DATETIME NULL,
|
||
end_time DATETIME NULL,
|
||
questions_score DECIMAL(8,2) NULL,
|
||
exam_score DECIMAL(8,2) NULL,
|
||
exam_number INT NULL,
|
||
answered_number INT NULL,
|
||
not_answered INT NULL,
|
||
status INT NULL,
|
||
PRIMARY KEY (exam_code, user_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户考试成绩表';
|
||
|
||
-- 考试答题记录表
|
||
CREATE TABLE exam_task_answer (
|
||
exam_code VARCHAR(50) NOT NULL,
|
||
user_code VARCHAR(100) NOT NULL,
|
||
questions_code VARCHAR(50) NOT NULL,
|
||
questions_number INT NOT NULL,
|
||
questions_type INT NOT NULL,
|
||
questions_answer VARCHAR(20) NULL,
|
||
is_mark CHAR(1) NULL,
|
||
is_current CHAR(1) NULL,
|
||
PRIMARY KEY (exam_code, questions_code, user_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试答题记录表';
|
||
|
||
-- 考试完成答案表
|
||
CREATE TABLE exam_finish_answer (
|
||
exam_code VARCHAR(50) NOT NULL,
|
||
user_code VARCHAR(100) NOT NULL,
|
||
questions_code VARCHAR(50) NOT NULL,
|
||
exam_score DECIMAL(8,2) NULL,
|
||
right_answer VARCHAR(20) NULL,
|
||
questions_answer VARCHAR(20) NULL,
|
||
questions_score INT NULL,
|
||
PRIMARY KEY (exam_code, questions_code, user_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试完成答案表'; |