develop
/
digimeta-MultiSaas
12
0
포크 0
코드 이슈 0 풀 리퀘스트 0 릴리즈 149 위키 활동
数字人管理平台
25개 이상의 토픽을 선택하실 수 없습니다. Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1506 커밋
39 브랜치
647 MiB
 
 
 
 
 
 
브렌치: main
브랜치 태그
${ item.name }
${ searchTerm } 브랜치 생성
from 'main'
${ noResults }
digimeta-MultiSaas/sql/xueyi_2.sql

556 lines
37 KiB
Raw 고유링크 Blame 히스토리 

  1. CREATE DATABASE  IF NOT EXISTS `xy-cloud1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

  2. 

  3. SET NAMES utf8mb4;

  4. SET FOREIGN_KEY_CHECKS = 0;

  5. #

  6. USE `xy-cloud1`;

  7. 

  8. SET NAMES utf8mb4;

  9. 

  10. -- ----------------------------

  11. -- 1、部门表

  12. -- ----------------------------

  13. drop table if exists sys_dept;

  14. create table sys_dept (

  15.   id                        bigint              not null                                comment '部门id',

  16.   parent_id                 bigint              default 0                               comment '父部门id',

  17.   code                      varchar(64)         default null                            comment '部门编码',

  18.   name                      varchar(30)         default ''                              comment '部门名称',

  19.   level                     int                 not null                                comment '树层级',

  20.   ancestors                 varchar(500)        default ''                              comment '祖级列表',

  21.   leader                    varchar(20)         default ''                              comment '负责人',

  22.   phone                     varchar(11)         default ''                              comment '联系电话',

  23.   email                     varchar(50)         default ''                              comment '邮箱',

  24.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  25.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  26.   remark                    varchar(200)        default null                            comment '备注',

  27.   create_by                 bigint              default null                            comment '创建者',

  28.   create_time               datetime            default current_timestamp               comment '创建时间',

  29.   update_by                 bigint              default null                            comment '更新者',

  30.   update_time               datetime            on update current_timestamp             comment '更新时间',

  31.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  32.   tenant_id		            bigint	            not null                                comment '租户Id',

  33.   primary key (id)

  34. ) engine = innodb comment = '部门信息表';

  35. 

  36. -- ----------------------------

  37. -- 初始化-部门表数据

  38. -- ----------------------------

  39. insert into sys_dept (id, code, parent_id, level, ancestors, name, tenant_id)

  40. values (99, '99', 0, 1, '0', '雪忆科技', 1),

  41.        (100, '100', 0, 1, '0', '雪忆科技', 2),

  42.        (101, '101', 100, 2, '0,100', '深圳总公司', 2),

  43.        (102, '102',  100, 2, '0,100', '长沙分公司', 2),

  44.        (103, '103',  101, 3, '0,100,101', '研发部门', 2),

  45.        (104, '104',  101, 3, '0,100,101', '市场部门', 2),

  46.        (105, '105',  101, 3, '0,100,101', '测试部门', 2),

  47.        (106, '106',  101, 3, '0,100,101', '财务部门', 2),

  48.        (107, '107',  101, 3, '0,100,101', '运维部门', 2),

  49.        (108, '108',  102, 3, '0,100,102', '市场部门', 2),

  50.        (109, '109',  102, 3, '0,100,102', '财务部门', 2);

  51. 

  52. -- ----------------------------

  53. -- 2、岗位信息表

  54. -- ----------------------------

  55. drop table if exists sys_post;

  56. create table sys_post (

  57.   id                        bigint              not null                                comment '岗位Id',

  58.   dept_id		            bigint	            not null                                comment '部门Id',

  59.   code                      varchar(64)         default null                            comment '岗位编码',

  60.   name                      varchar(50)         not null                                comment '岗位名称',

  61.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  62.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  63.   remark                    varchar(200)        default null                            comment '备注',

  64.   create_by                 bigint              default null                            comment '创建者',

  65.   create_time               datetime            default current_timestamp               comment '创建时间',

  66.   update_by                 bigint              default null                            comment '更新者',

  67.   update_time               datetime            on update current_timestamp             comment '更新时间',

  68.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  69.   tenant_id		            bigint	            not null                                comment '租户Id',

  70.   primary key (id)

  71. ) engine = innodb comment = '岗位信息表';

  72. 

  73. -- ----------------------------

  74. -- 初始化-岗位信息表数据

  75. -- ----------------------------

  76. insert into sys_post (id, dept_id, code, name, tenant_id)

  77. values (1, 99, 'ceo', '超级管理员', 1),

  78.        (2, 100, 'ceo', '董事长', 2),

  79.        (3, 100, 'se', '项目经理', 2),

  80.        (4, 100, 'hr', '人力资源', 2),

  81.        (5, 100, 'user', '普通员工', 2);

  82. 

  83. -- ----------------------------

  84. -- 3、用户信息表

  85. -- ----------------------------

  86. drop table if exists sys_user;

  87. create table sys_user (

  88.   id                        bigint	            not null                                comment '用户Id',

  89.   code                      varchar(64)         default null                            comment '用户编码',

  90.   user_name                 varchar(30)         not null                                comment '用户账号',

  91.   nick_name                 varchar(30)         not null                                comment '用户昵称',

  92.   user_type                 varchar(2)          default '01'                            comment '用户类型（00超管用户 01普通用户）',

  93.   phone                     varchar(11)         default ''                              comment '手机号码',

  94.   email                     varchar(50)         default ''                              comment '用户邮箱',

  95.   sex                       char(1)             default '2'                             comment '用户性别（0男 1女 2保密）',

  96.   avatar                    varchar(100)        default ''                              comment '头像地址',

  97.   profile                   varchar(100)        default '这个人很懒，暂未留下什么'          comment '个人简介',

  98.   password                  varchar(100)        default ''                              comment '密码',

  99.   login_ip                  varchar(128)        default ''                              comment '最后登录IP',

  100.   login_date                datetime                                                    comment '最后登录时间',

  101.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  102.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  103.   remark                    varchar(200)        default null                            comment '备注',

  104.   create_by                 bigint              default null                            comment '创建者',

  105.   create_time               datetime            default current_timestamp               comment '创建时间',

  106.   update_by                 bigint              default null                            comment '更新者',

  107.   update_time               datetime            on update current_timestamp             comment '更新时间',

  108.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  109.   tenant_id		            bigint	            not null                                comment '租户Id',

  110.   primary key (id)

  111. ) engine = innodb comment = '用户信息表';

  112. 

  113. -- ----------------------------

  114. -- 初始化-用户信息表数据

  115. -- ----------------------------

  116. insert into sys_user (id, code, user_name, nick_name,user_type, avatar, password, remark, tenant_id)

  117. values (1, '001', 'admin', 'admin', '00', 'https://images.gitee.com/uploads/images/2021/1101/141155_f3dfce1d_7382127.jpeg', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '超级管理员', 1),

  118.        (2, '001', 'admin', 'admin', '00', 'https://images.gitee.com/uploads/images/2021/1101/141155_f3dfce1d_7382127.jpeg', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '系统管理员', 2),

  119.        (3, '002', 'xy', 'xy', '01', 'https://images.gitee.com/uploads/images/2021/1101/141155_f3dfce1d_7382127.jpeg', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '管理员', 2);

  120. 

  121. -- ----------------------------

  122. -- 4、用户-岗位关联表

  123. -- ----------------------------

  124. drop table if exists sys_user_post_merge;

  125. create table sys_user_post_merge (

  126.   id                        bigint	            not null                                comment 'id',

  127.   user_id                   bigint	            not null                                comment '用户Id',

  128.   post_id		            bigint	            not null                                comment '职位Id',

  129.   tenant_id		            bigint	            not null                                comment '租户Id',

  130.   primary key (id),

  131.   unique (user_id, post_id)

  132. ) engine = innodb comment = '用户-岗位关联表';

  133. 

  134. -- ----------------------------

  135. -- 初始化-用户-岗位关联表数据

  136. -- ----------------------------

  137. insert into sys_user_post_merge (id, user_id, post_id, tenant_id)

  138. values (1, 1, 1, 1),

  139.        (2, 2, 2, 2),

  140.        (3, 3, 3, 2);

  141. 

  142. -- ----------------------------

  143. -- 5、角色信息表

  144. -- ----------------------------

  145. drop table if exists sys_role;

  146. create table sys_role (

  147.   id                        bigint	            not null                                comment '角色Id',

  148.   code                      varchar(64)         default null                            comment '角色编码',

  149.   name                      varchar(30)         not null                                comment '角色名称',

  150.   role_key                  varchar(100)        default null                            comment '角色权限字符串',

  151.   data_scope                char(1)             default '1'                             comment '数据范围（1全部数据权限 2自定数据权限 3本部门数据权限 4本部门及以下数据权限 5本岗位数据权限  6仅本人数据权限）',

  152.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  153.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  154.   remark                    varchar(200)        default null                            comment '备注',

  155.   create_by                 bigint              default null                            comment '创建者',

  156.   create_time               datetime            default current_timestamp               comment '创建时间',

  157.   update_by                 bigint              default null                            comment '更新者',

  158.   update_time               datetime            on update current_timestamp             comment '更新时间',

  159.   del_flag		            tinyint             not null default 0                      comment '删除标志（0正常 1删除）',

  160.   tenant_id		            bigint	            not null                                comment '租户Id',

  161.   primary key (id)

  162. ) engine = innodb comment = '角色信息表';

  163. 

  164. -- ----------------------------

  165. -- 初始化-角色信息表数据

  166. -- ----------------------------

  167. insert into sys_role (id, tenant_id, code, name, role_key, remark)

  168. values (1, 1, '001', '超级管理员', 'admin', '超级管理员'),

  169.        (2, 1, '002', '管理员', 'common', '普通角色');

  170. 

  171. -- ----------------------------

  172. -- 6、租户和模块关联表

  173. -- ----------------------------

  174. drop table if exists sys_tenant_module_merge;

  175. create table sys_tenant_module_merge (

  176.   id                        bigint              not null                                comment 'id',

  177.   module_id                 bigint              not null                                comment '模块Id',

  178.   tenant_id		            bigint	            not null                                comment '租户Id',

  179.   primary key (id),

  180.   unique (module_id, tenant_id)

  181. ) engine = innodb comment = '租户和模块关联表';

  182. 

  183. -- ----------------------------

  184. -- 初始化-租户和模块关联表数据

  185. -- ----------------------------

  186. insert into sys_tenant_module_merge (id, module_id, tenant_id)

  187. values (1501811101547732994, 1, 2);

  188. 

  189. -- ----------------------------

  190. -- 7、租户和菜单关联表

  191. -- ----------------------------

  192. drop table if exists sys_tenant_menu_merge;

  193. create table sys_tenant_menu_merge (

  194.   id                        bigint              not null                                comment 'id',

  195.   menu_id                   bigint              not null                                comment '菜单Id',

  196.   tenant_id		            bigint	            not null                                comment '租户Id',

  197.   primary key (id),

  198.   unique (menu_id, tenant_id)

  199. ) engine = innodb comment = '租户和菜单关联表';

  200. 

  201. -- ----------------------------

  202. -- 初始化-租户和菜单关联表数据

  203. -- ----------------------------

  204. insert into sys_tenant_menu_merge (id, menu_id, tenant_id)

  205. values (1501811101614841858, 13000000, 2),

  206.        (1501811101614841863, 13010000, 2),

  207.        (1501811101614841873, 13010100, 2),

  208.        (1501811101614841874, 13010200, 2),

  209.        (1501811101614841875, 13010300, 2),

  210.        (1501811101614841876, 13010400, 2),

  211.        (1501811101614841877, 13010500, 2),

  212.        (1501811101614841878, 13010600, 2),

  213.        (1501811101614841879, 13010700, 2),

  214.        (1501811101614841864, 13020000, 2),

  215.        (1501811101614841880, 13020100, 2),

  216.        (1501811101614841881, 13020200, 2),

  217.        (1501811101614841882, 13020300, 2),

  218.        (1501811101614841883, 13020400, 2),

  219.        (1501811101614841884, 13020500, 2),

  220.        (1501811101614841885, 13020600, 2),

  221.        (1501811101614841886, 13020700, 2),

  222.        (1501811101614841865, 13030000, 2),

  223.        (1501811101614841887, 13030100, 2),

  224.        (1501811101614841888, 13030200, 2),

  225.        (1501811101614841889, 13030300, 2),

  226.        (1501811101614841890, 13030400, 2),

  227.        (1501811101614841891, 13030500, 2),

  228.        (1501811101614841892, 13030600, 2),

  229.        (1501811101614841893, 13030700, 2),

  230.        (1501811101614841859, 14000000, 2),

  231.        (1501811101614841866, 14010000, 2),

  232.        (1501811101614841894, 14010100, 2),

  233.        (1501811101614841895, 14010200, 2),

  234.        (1501811101614841896, 14010300, 2),

  235.        (1501811101614841897, 14010400, 2),

  236.        (1501811101614841898, 14010500, 2),

  237.        (1501811101614841899, 14010600, 2),

  238.        (1501811101614841900, 14010700, 2),

  239.        (1501811101614841867, 14020000, 2),

  240.        (1501811101614841901, 14020100, 2),

  241.        (1501811101614841902, 14020200, 2),

  242.        (1501811101614841903, 14020300, 2),

  243.        (1501811101614841904, 14020400, 2),

  244.        (1501811101614841905, 14020500, 2),

  245.        (1501811101614841906, 14020600, 2),

  246.        (1501811101614841907, 14020700, 2),

  247.        (1501811101614841868, 14030000, 2),

  248.        (1501811101614841908, 14030100, 2),

  249.        (1501811101614841909, 14030200, 2),

  250.        (1501811101614841910, 14030300, 2),

  251.        (1501811101614841911, 14030400, 2),

  252.        (1501811101614841912, 14030500, 2),

  253.        (1501811101614841913, 14030600, 2),

  254.        (1501811101614841914, 14030700, 2),

  255.        (1501811101614841915, 14030800, 2),

  256.        (1501811101614841860, 15000000, 2),

  257.        (1501811101614841869, 15010000, 2),

  258.        (1501811101614841916, 15010100, 2),

  259.        (1501811101614841917, 15010200, 2),

  260.        (1501811101614841918, 15010300, 2),

  261.        (1501811101614841919, 15010400, 2),

  262.        (1501811101614841920, 15010500, 2),

  263.        (1501811101614841921, 15010600, 2),

  264.        (1501811101614841922, 15010700, 2),

  265.        (1501811101614841861, 16000000, 2),

  266.        (1501811101614841870, 16010000, 2),

  267.        (1501811101614841923, 16010100, 2),

  268.        (1501811101614841924, 16010200, 2),

  269.        (1501811101614841925, 16010300, 2),

  270.        (1501811101614841926, 16010400, 2),

  271.        (1501811101614841927, 16010500, 2),

  272.        (1501811101614841928, 16010600, 2),

  273.        (1501811101614841929, 16010700, 2),

  274.        (1501811101614841930, 16010800, 2),

  275.        (1501811101614841862, 17000000, 2),

  276.        (1501811101614841871, 17010000, 2),

  277.        (1501811101614841931, 17010100, 2),

  278.        (1501811101614841872, 17020000, 2),

  279.        (1501811101614841932, 17020100, 2),

  280.        (1501811101614841937, 17020101, 2),

  281.        (1501811101614841938, 17020102, 2),

  282.        (1501811101614841933, 17020200, 2),

  283.        (1501811101614841934, 17020201, 2),

  284.        (1501811101614841935, 17020202, 2),

  285.        (1501811101614841936, 17020203, 2);

  286. 

  287. -- ----------------------------

  288. -- 8、角色和模块关联表

  289. -- ----------------------------

  290. drop table if exists sys_role_module_merge;

  291. create table sys_role_module_merge (

  292.   id                        bigint              not null                                comment 'id',

  293.   role_id                   bigint              not null                                comment '角色Id',

  294.   module_id                 bigint              not null                                comment '模块Id',

  295.   tenant_id		            bigint	            not null                                comment '租户Id',

  296.   primary key (id),

  297.   unique (role_id, module_id)

  298. ) engine = innodb comment = '角色和模块关联表';

  299. 

  300. -- ----------------------------

  301. -- 9、角色和菜单关联表

  302. -- ----------------------------

  303. drop table if exists sys_role_menu_merge;

  304. create table sys_role_menu_merge (

  305.   id                        bigint              not null                                comment 'id',

  306.   role_id                   bigint              not null                                comment '角色Id',

  307.   menu_id                   bigint              not null                                comment '菜单Id',

  308.   tenant_id		            bigint	            not null                                comment '租户Id',

  309.   primary key (id),

  310.   unique (role_id, menu_id)

  311. ) engine = innodb comment = '角色和菜单关联表';

  312. 

  313. -- ----------------------------

  314. -- 10、角色和部门关联表（权限范围）

  315. -- ----------------------------

  316. drop table if exists sys_role_dept_merge;

  317. create table sys_role_dept_merge (

  318.   id                        bigint              not null                                comment 'id',

  319.   role_id                   bigint              not null                                comment '角色Id',

  320.   dept_id                   bigint              not null                                comment '部门Id',

  321.   tenant_id		            bigint	            not null                                comment '租户Id',

  322.   primary key(id),

  323.   unique (role_id, dept_id)

  324. ) engine = innodb comment = '角色和部门-岗位关联表';

  325. 

  326. -- ----------------------------

  327. -- 11、角色和岗位关联表（权限范围）

  328. -- ----------------------------

  329. drop table if exists sys_role_post_merge;

  330. create table sys_role_post_merge (

  331.   id                        bigint              not null                                comment 'id',

  332.   role_id                   bigint              not null                                comment '角色Id',

  333.   post_id                   bigint              not null                                comment '岗位Id',

  334.   tenant_id		            bigint	            not null                                comment '租户Id',

  335.   primary key(id),

  336.   unique (role_id, post_id)

  337. ) engine = innodb comment = '角色和部门-岗位关联表';

  338. 

  339. -- ----------------------------

  340. -- 12、组织和角色关联表（角色绑定）

  341. -- ----------------------------

  342. drop table if exists sys_organize_role_merge;

  343. create table sys_organize_role_merge (

  344.   id		                bigint	            not null auto_increment                 comment 'id',

  345.   dept_id                   bigint              default null                            comment '部门id',

  346.   post_id                   bigint              default null                            comment '岗位id',

  347.   user_id                   bigint              default null                            comment '用户id',

  348.   role_id                   bigint              not null                                comment '角色Id',

  349.   tenant_id		            bigint	            not null                                comment '租户Id',

  350.   primary key(id),

  351.   unique (dept_id, post_id, user_id, role_id)

  352. ) engine = innodb auto_increment=1 comment = '组织和角色关联表';

  353. 

  354. -- ----------------------------

  355. -- 13、素材信息表|管理素材信息

  356. -- ----------------------------

  357. drop table if exists xy_material;

  358. create table xy_material (

  359.   id		                bigint	            not null                                comment '素材Id',

  360.   folder_id		            bigint	            not null default 0	                    comment '分类Id',

  361.   nick		                varchar(100)	    not null	                            comment '素材昵称',

  362.   name		                varchar(100)	    not null	                            comment '素材名称',

  363.   original_name	            varchar(100)	    not null	                            comment '原图名称',

  364.   url		                varchar(200)	    not null 	                            comment '素材地址',

  365.   original_url		        varchar(200)	    not null 	                            comment '原图地址',

  366.   size		                decimal(8,4)	    not null 	                            comment '素材大小',

  367.   type		                char(1)	            not null default '0'	                comment '素材类型（0默认素材 1系统素材）',

  368.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  369.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  370.   create_by                 bigint              default null                            comment '创建者',

  371.   create_time               datetime            default current_timestamp               comment '创建时间',

  372.   update_by                 bigint              default null                            comment '更新者',

  373.   update_time               datetime            on update current_timestamp             comment '更新时间',

  374.   del_flag		            tinyint             not null default 0                      comment '删除标志（0正常 1删除）',

  375.   tenant_id		            bigint	            not null                                comment '租户Id',

  376.   primary key (id)

  377. ) engine = innodb comment = '素材信息表';

  378. 

  379. -- ----------------------------

  380. -- 14、素材分类信息表

  381. -- ----------------------------

  382. drop table if exists xy_material_folder;

  383. create table xy_material_folder (

  384.   id		                bigint	            not null                                comment '分类Id',

  385.   parent_id		            bigint	            not null default 0                      comment '父类Id',

  386.   name		                varchar(100)	    not null	                            comment '分类名称',

  387.   level                     int                 not null                                comment '树层级',

  388.   ancestors                 varchar(500)        default ''                              comment '祖级列表',

  389.   type		                char(1)	            not null default '0'	                comment '分类类型（0默认文件夹 1系统文件夹）',

  390.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  391.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  392.   create_by                 bigint              default null                            comment '创建者',

  393.   create_time               datetime            default current_timestamp               comment '创建时间',

  394.   update_by                 bigint              default null                            comment '更新者',

  395.   update_time               datetime            on update current_timestamp             comment '更新时间',

  396.   del_flag		            tinyint             not null default 0                      comment '删除标志（0正常 1删除）',

  397.   tenant_id		            bigint	            not null                                comment '租户Id',

  398.   primary key (id)

  399. ) engine = innodb comment = '素材分类信息表';

  400. 

  401. -- ----------------------------

  402. -- 15、操作日志记录

  403. -- ----------------------------

  404. drop table if exists sys_operate_log;

  405. create table sys_operate_log (

  406.   id                        bigint              not null auto_increment                 comment '日志主键',

  407.   title                     varchar(50)         default ''                              comment '模块标题',

  408.   business_type             char(2)             default '00'                            comment '业务类型（0其它 1新增 2修改 3删除）',

  409.   method                    varchar(100)        default ''                              comment '方法名称',

  410.   request_method            varchar(10)         default ''                              comment '请求方式',

  411.   operate_type              char(2)             default '00'                            comment '操作类别（00其它 01后台 02手机端）',

  412.   user_id                   bigint              not null default -2                     comment '操作人员',

  413.   user_name                 varchar(50)         default null                            comment '操作人员账号',

  414.   user_nick                 varchar(50)         default null                            comment '操作人员名称',

  415.   url                       varchar(255)        default ''                              comment '请求URL',

  416.   ip                        varchar(128)        default ''                              comment '主机地址',

  417.   param                     varchar(2000)       default ''                              comment '请求参数',

  418.   location                  varchar(255)        default ''                              comment '操作地点',

  419.   json_result               varchar(2000)       default ''                              comment '返回参数',

  420.   status                    char(1)             default 0                               comment '操作状态（0正常 1异常）',

  421.   error_msg                 varchar(2000)       default ''                              comment '错误消息',

  422.   cost_time                 bigint              default 0                               comment '消耗时间',

  423.   operate_time              datetime            default current_timestamp               comment '操作时间',

  424.   del_time                  datetime            on update current_timestamp             comment '删除时间',

  425.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  426.   tenant_id		            bigint	            not null                                comment '租户Id',

  427.   primary key (id),

  428.   key idx_sys_operate_log_bt (business_type),

  429.   key idx_sys_operate_log_s  (status),

  430.   key idx_sys_operate_log_ot (operate_time)

  431. ) engine = innodb auto_increment=100 comment = '操作日志记录';

  432. 

  433. -- ----------------------------

  434. -- 16、系统访问记录

  435. -- ----------------------------

  436. drop table if exists sys_login_log;

  437. create table sys_login_log (

  438.   id                        bigint              not null auto_increment                 comment '访问Id',

  439.   enterprise_name           varchar(50)         default ''                              comment '企业账号',

  440.   user_id                   bigint              not null default -2                     comment '用户Id',

  441.   user_name                 varchar(50)         default ''                              comment '用户账号',

  442.   user_nick                 varchar(50)         default ''                              comment '用户名称',

  443.   ipaddr                    varchar(128)        default ''                              comment '登录IP地址',

  444.   status                    char(1)             default '0'                             comment '登录状态（0成功 1失败）',

  445.   msg                       varchar(255)        default ''                              comment '提示信息',

  446.   access_time               datetime            default current_timestamp               comment '访问时间',

  447.   del_time                  datetime            on update current_timestamp             comment '删除时间',

  448.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  449.   tenant_id		            bigint	            not null                                comment '租户Id',

  450.   primary key (id),

  451.   key idx_sys_login_log_s  (status),

  452.   key idx_sys_login_log_lt (access_time)

  453. ) engine = innodb auto_increment=100 comment = '系统访问记录';

  454. 

  455. -- ----------------------------

  456. -- 17、通知公告表

  457. -- ----------------------------

  458. drop table if exists sys_notice;

  459. create table sys_notice (

  460.   id                        bigint              not null                                comment '公告Id',

  461.   name                      varchar(50)         not null                                comment '公告标题',

  462.   type                      char(1)             not null default '0'                    comment '公告类型（0通知 1公告）',

  463.   content                   longblob            default null                            comment '公告内容',

  464.   status                    char(1)             default '0'                             comment '公告状态（0待发送 1已发送 2已关闭 3发送失败 4发送异常）',

  465.   remark                    varchar(200)        default null                            comment '备注',

  466.   create_by                 bigint              default null                            comment '创建者',

  467.   create_time               datetime            default current_timestamp               comment '创建时间',

  468.   update_by                 bigint              default null                            comment '更新者',

  469.   update_time               datetime            on update current_timestamp             comment '更新时间',

  470.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  471.   tenant_id		            bigint	            not null                                comment '租户Id',

  472.   primary key (id)

  473. ) engine = innodb comment = '通知公告表';

  474. 

  475. -- ----------------------------

  476. -- 18、通知公告记录表

  477. -- ----------------------------

  478. drop table if exists sys_notice_log;

  479. create table sys_notice_log (

  480.   id                        bigint              not null                                comment 'id',

  481.   notice_id                 bigint              not null                                comment '公告Id',

  482.   user_id                   bigint              not null                                comment '用户Id',

  483.   receive_status            char(1)             not null                                comment '发送状态（0成功 1失败）',

  484.   status                    char(1)             default '0'                             comment '阅读状态（0未读 1已读）',

  485.   remark                    varchar(200)        default null                            comment '备注',

  486.   create_time               datetime            default current_timestamp               comment '创建时间',

  487.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  488.   tenant_id		            bigint	            not null                                comment '租户Id',

  489.   primary key (id)

  490. ) engine = innodb comment = '通知公告记录表';

  491. 

  492. -- ----------------------------

  493. -- 19、定时任务调度日志表

  494. -- ----------------------------

  495. drop table if exists sys_job_log;

  496. create table sys_job_log (

  497.   id                        bigint              not null auto_increment                 comment '任务日志Id',

  498.   job_id                    bigint              not null                                comment '任务Id',

  499.   name                      varchar(64)         not null                                comment '任务名称',

  500.   job_group                 varchar(64)         not null                                comment '任务组名',

  501.   invoke_target             varchar(500)        not null                                comment '调用目标字符串',

  502.   invoke_tenant             varchar(500)        not null                                comment '调用租户字符串',

  503.   job_message               varchar(500)                                                comment '日志信息',

  504.   status                    char(1)             not null default '0'                    comment '执行状态（0正常 1失败）',

  505.   exception_info            varchar(2000)       default ''                              comment '异常信息',

  506.   create_time               datetime            default current_timestamp               comment '创建时间',

  507.   del_time                  datetime            on update current_timestamp             comment '删除时间',

  508.   del_flag		            tinyint             not null default 0                      comment '删除标志(0正常 1删除)',

  509.   tenant_id		            bigint	            not null                                comment '租户Id',

  510.   primary key (id)

  511. ) engine = innodb comment = '定时任务调度日志表';

  512. 

  513. -- ----------------------------

  514. -- 20、文件信息表

  515. -- ----------------------------

  516. drop table if exists sys_file;

  517. create table sys_file (

  518.   id		                bigint	            not null                                comment '文件Id',

  519.   folder_id		            bigint	            not null default 0	                    comment '分类Id',

  520.   name		                varchar(100)	    not null	                            comment '文件名称',

  521.   nick	                    varchar(100)	    default null	                        comment '文件别名',

  522.   url		                varchar(500)	    not null 	                            comment '文件地址',

  523.   size		                bigint	            not null default 0	                    comment '文件大小',

  524.   type		                char(1)	            not null default '0'	                comment '文件类型（0默认 1系统）',

  525.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  526.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  527.   create_by                 bigint              default null                            comment '创建者',

  528.   create_time               datetime            default current_timestamp               comment '创建时间',

  529.   update_by                 bigint              default null                            comment '更新者',

  530.   update_time               datetime            on update current_timestamp             comment '更新时间',

  531.   del_flag		            tinyint             not null default 0                      comment '删除标志（0正常 1删除）',

  532.   tenant_id		            bigint	            not null                                comment '租户Id',

  533.   primary key (id)

  534. ) engine = innodb comment = '文件信息表';

  535. 

  536. -- ----------------------------

  537. -- 21、文件分类信息表

  538. -- ----------------------------

  539. drop table if exists sys_file_folder;

  540. create table sys_file_folder (

  541.   id		                bigint	            not null                                comment '分类Id',

  542.   parent_id		            bigint	            not null default 0                      comment '父分类Id',

  543.   name		                varchar(100)	    not null	                            comment '分类名称',

  544.   level                     int                 not null                                comment '树层级',

  545.   ancestors                 varchar(500)        default ''                              comment '祖级列表',

  546.   type		                char(1)	            not null default '0'	                comment '分类类型（0默认文件夹 1系统文件夹）',

  547.   sort                      int unsigned        not null default 0                      comment '显示顺序',

  548.   status                    char(1)             not null default '0'                    comment '状态（0正常 1停用）',

  549.   create_by                 bigint              default null                            comment '创建者',

  550.   create_time               datetime            default current_timestamp               comment '创建时间',

  551.   update_by                 bigint              default null                            comment '更新者',

  552.   update_time               datetime            on update current_timestamp             comment '更新时间',

  553.   del_flag		            tinyint             not null default 0                      comment '删除标志（0正常 1删除）',

  554.   tenant_id		            bigint	            not null                                comment '租户Id',

  555.   primary key (id)

  556. ) engine = innodb comment = '文件分类信息表';