-- DROP DATABASE IF EXISTS `se-cloud`; CREATE DATABASE IF NOT EXISTS `se-cloud` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; USE `se-cloud`; -- ---------------------------- -- 01、软件表 -- ---------------------------- drop table if exists sys_soft; create table sys_soft ( soft_id bigint(20) not null auto_increment comment '软件ID', name varchar(200) not null comment '名称', type char(1) default '0' comment '类型（0-应用软件，1-数据库，2-中间件，3-操作系统，4-Docker容器）', lic varchar(500) comment '许可', descr varchar(500) comment '描述', args varchar(8000) comment '参数', status char(1) default 0 comment '状态（0正常 1停用）', create_by varchar(64) default '' comment '创建者', create_time datetime comment '创建时间', update_by varchar(64) default '' comment '更新者', update_time datetime comment '更新时间', remark varchar(500) default '' comment '备注', primary key (soft_id) ) engine=innodb auto_increment=1 comment = '软件表'; select * from sys_soft order by soft_id; insert into sys_soft (name, type, args) values ('se-mysql', 4, 'se-mysql'); insert into sys_soft (name, type, args) values ('se-redis', 4, 'se-redis'); insert into sys_soft (name, type, args) values ('se-system', 4, 'se-system'); insert into sys_soft (name, type, args) values ('se-wgcloud', 4, 'se-wgcloud'); -- 菜单 SQL insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values('软件', '1', '8', 'soft', 'system/soft/index', 1, 0, 'C', '0', '0', 'system:soft:list', '#', 'admin', sysdate(), '', null, '软件菜单'); -- 按钮父菜单ID SELECT @parentId := LAST_INSERT_ID(); -- 按钮 SQL insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values('软件查询', @parentId, '1', '#', '', 1, 0, 'F', '0', '0', 'system:soft:query', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values('软件新增', @parentId, '2', '#', '', 1, 0, 'F', '0', '0', 'system:soft:add', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values('软件修改', @parentId, '3', '#', '', 1, 0, 'F', '0', '0', 'system:soft:edit', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values('软件删除', @parentId, '4', '#', '', 1, 0, 'F', '0', '0', 'system:soft:remove', '#', 'admin', sysdate(), '', null, ''); insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark) values('软件导出', @parentId, '5', '#', '', 1, 0, 'F', '0', '0', 'system:soft:export', '#', 'admin', sysdate(), '', null, ''); update sys_menu set mark = 'sys' where menu_id >= @parentId; -- ---------------------------- -- 02、硬件表 -- ---------------------------- drop table if exists sys_hard; create table sys_hard ( hard_id bigint(20) not null auto_increment comment '硬件ID', name varchar(200) not null comment '名称', cpu varchar(20) comment 'CPU', mem varchar(200) comment '内存', disk varchar(200) comment '硬盘', net varchar(200) comment '网络', descr varchar(500) comment '描述', args varchar(8000) comment '参数', status char(1) default 0 comment '状态（0正常 1停用）', create_by varchar(64) default '' comment '创建者', create_time datetime comment '创建时间', update_by varchar(64) default '' comment '更新者', update_time datetime comment '更新时间', remark varchar(500) default '' comment '备注', primary key (hard_id) ) engine=innodb auto_increment=1 comment = '硬件表'; select * from sys_hard order by hard_id; -- ---------------------------- -- 03、配置表 -- ---------------------------- drop table if exists sys_cfg; create table sys_cfg ( cfg_id bigint(20) not null auto_increment comment '配置ID', name varchar(200) not null comment '名称', hard varchar(4000) comment '硬件', soft varchar(4000) comment '软件', args varchar(4000) comment '参数', status char(1) default 0 comment '状态（0正常 1停用）', create_by varchar(64) default '' comment '创建者', create_time datetime comment '创建时间', update_by varchar(64) default '' comment '更新者', update_time datetime comment '更新时间', remark varchar(500) default '' comment '备注', primary key (cfg_id) ) engine=innodb auto_increment=1 comment = '配置表'; select * from sys_cfg order by cfg_id; -- ---------------------------- -- 04、资源表 -- ---------------------------- drop table if exists sys_res; create table sys_res ( res_id bigint(20) not null auto_increment comment '资源ID', name varchar(200) not null comment '名称', type char(1) default '0' comment '类型（0-其它，1-IP，2-数据库，3-消息中间件，4-共享文件，5-分系统软件，6-引擎）', descr varchar(500) comment '描述', args varchar(4000) comment '参数', status char(1) default 0 comment '状态（0正常 1停用）', create_by varchar(64) default '' comment '创建者', create_time datetime comment '创建时间', update_by varchar(64) default '' comment '更新者', update_time datetime comment '更新时间', remark varchar(500) default '' comment '备注', primary key (res_id) ) engine=innodb auto_increment=1 comment = '资源表'; select * from sys_res order by res_id; -- ---------------------------- -- 05、接口表 -- ---------------------------- drop table if exists sys_inte; create table sys_inte ( inte_id bigint(20) not null auto_increment comment '接口ID', name varchar(200) not null comment '名称', type char(1) default '0' comment '类型', descr varchar(500) comment '描述', url varchar(4000) comment 'URL', data varchar(500) comment '数据', args varchar(4000) comment '参数', status char(1) default 0 comment '状态（0正常 1停用）', create_by varchar(64) default '' comment '创建者', create_time datetime comment '创建时间', update_by varchar(64) default '' comment '更新者', update_time datetime comment '更新时间', remark varchar(500) default '' comment '备注', primary key (inte_id) ) engine=innodb auto_increment=1 comment = '接口表'; select * from sys_inte order by inte_id; -- ---------------------------- -- 06、告警表 -- ---------------------------- drop table if exists sys_warn; create table sys_warn ( warn_id bigint(20) not null auto_increment comment '告警ID', name varchar(200) not null comment '名称', type char(1) default '0' comment '类型', descr varchar(500) comment '描述', data varchar(500) comment '数据', args varchar(4000) comment '参数', status char(1) default 0 comment '状态（0正常 1停用）', create_by varchar(64) default '' comment '创建者', create_time datetime comment '创建时间', update_by varchar(64) default '' comment '更新者', update_time datetime comment '更新时间', remark varchar(500) default '' comment '备注', primary key (warn_id) ) engine=innodb auto_increment=1 comment = '告警表'; select * from sys_warn order by warn_id; -- ---------------------------- -- 07、控制表 -- ---------------------------- drop table if exists sys_ctrl; create table sys_ctrl ( ctrl_id bigint(20) not null auto_increment comment '控制ID', name varchar(200) not null comment '名称', type char(1) default '0' comment '类型', descr varchar(500) comment '描述', data varchar(500) comment '数据', args varchar(4000) comment '参数', status char(1) default 0 comment '状态（0正常 1停用）', create_by varchar(64) default '' comment '创建者', create_time datetime comment '创建时间', update_by varchar(64) default '' comment '更新者', update_time datetime comment '更新时间', remark varchar(500) default '' comment '备注', primary key (ctrl_id) ) engine=innodb auto_increment=1 comment = '控制表'; select * from sys_ctrl order by ctrl_id; -- ---------------------------- -- 08、控制日志表 -- ---------------------------- drop table if exists sys_ctrl_log; create table sys_ctrl_log ( log_id bigint(20) not null auto_increment comment '日志ID', title varchar(100) not null comment '标题', type char(1) default '0' comment '类型', ip varchar(50) comment 'IP', url varchar(4000) comment 'URL', method varchar(20) comment '方法', args varchar(4000) comment '参数', msg varchar(2000) comment '消息', oper varchar(50) comment '操作员', time datetime default now() comment '操作时间', remark varchar(500) default '' comment '备注', primary key (log_id) ) engine=innodb auto_increment=1 comment = '控制日志表'; select * from sys_ctrl_log order by log_id; -- ---------------------------- -- 09、角色控制表 -- ---------------------------- drop table if exists sys_role_ctrl; create table sys_role_ctrl ( role_id bigint(20) not null comment '角色ID', ctrl_id bigint(20) not null comment '控制ID', primary key(role_id, ctrl_id) ) engine=innodb comment = '角色控制表'; select * from sys_role_ctrl; -- ---------------------------- -- 10、角色接口表 -- ---------------------------- drop table if exists sys_role_inte; create table sys_role_inte ( role_id bigint(20) not null comment '角色ID', inte_id bigint(20) not null comment '接口ID', primary key(role_id, inte_id) ) engine=innodb comment = '角色接口表'; select * from sys_role_inte; -- ---------------------------- -- 11、角色资源表 -- ---------------------------- drop table if exists sys_role_res; create table sys_role_res ( role_id bigint(20) not null comment '角色ID', res_id bigint(20) not null comment '资源ID', primary key(role_id, res_id) ) engine=innodb comment = '角色资源表'; select * from sys_role_res;