postgres_ry_vue_5.X.sql 60 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993
  1. -- ----------------------------
  2. -- 1、部门表
  3. -- ----------------------------
  4. drop table if exists sys_dept;
  5. create table if not exists sys_dept
  6. (
  7. dept_id int8,
  8. parent_id int8 default 0,
  9. ancestors varchar(500)default ''::varchar,
  10. dept_name varchar(30) default ''::varchar,
  11. order_num int4 default 0,
  12. leader varchar(20) default null::varchar,
  13. phone varchar(11) default null::varchar,
  14. email varchar(50) default null::varchar,
  15. status char default '0'::bpchar,
  16. del_flag char default '0'::bpchar,
  17. create_dept int8,
  18. create_by int8,
  19. create_time timestamp,
  20. update_by int8,
  21. update_time timestamp,
  22. constraint "sys_dept_pk" primary key (dept_id)
  23. );
  24. comment on table sys_dept is '部门表';
  25. comment on column sys_dept.dept_id is '部门ID';
  26. comment on column sys_dept.parent_id is '父部门ID';
  27. comment on column sys_dept.ancestors is '祖级列表';
  28. comment on column sys_dept.dept_name is '部门名称';
  29. comment on column sys_dept.order_num is '显示顺序';
  30. comment on column sys_dept.leader is '负责人';
  31. comment on column sys_dept.phone is '联系电话';
  32. comment on column sys_dept.email is '邮箱';
  33. comment on column sys_dept.status is '部门状态(0正常 1停用)';
  34. comment on column sys_dept.del_flag is '删除标志(0代表存在 2代表删除)';
  35. comment on column sys_dept.create_dept is '创建部门';
  36. comment on column sys_dept.create_by is '创建者';
  37. comment on column sys_dept.create_time is '创建时间';
  38. comment on column sys_dept.update_by is '更新者';
  39. comment on column sys_dept.update_time is '更新时间';
  40. -- ----------------------------
  41. -- 初始化-部门表数据
  42. -- ----------------------------
  43. insert into sys_dept values(100, 0, '0', '若依科技', 0, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  44. insert into sys_dept values(101, 100, '0,100', '深圳总公司', 1, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  45. insert into sys_dept values(102, 100, '0,100', '长沙分公司', 2, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  46. insert into sys_dept values(103, 101, '0,100,101', '研发部门', 1, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  47. insert into sys_dept values(104, 101, '0,100,101', '市场部门', 2, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  48. insert into sys_dept values(105, 101, '0,100,101', '测试部门', 3, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  49. insert into sys_dept values(106, 101, '0,100,101', '财务部门', 4, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  50. insert into sys_dept values(107, 101, '0,100,101', '运维部门', 5, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  51. insert into sys_dept values(108, 102, '0,100,102', '市场部门', 1, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  52. insert into sys_dept values(109, 102, '0,100,102', '财务部门', 2, '若依', '15888888888', 'ry@qq.com', '0', '0', 103, 1, now(), null, null);
  53. -- ----------------------------
  54. -- 2、用户信息表
  55. -- ----------------------------
  56. drop table if exists sys_user;
  57. create table if not exists sys_user
  58. (
  59. user_id int8,
  60. dept_id int8,
  61. user_name varchar(30) not null,
  62. nick_name varchar(30) not null,
  63. user_type varchar(10) default 'sys_user'::varchar,
  64. email varchar(50) default ''::varchar,
  65. phonenumber varchar(11) default ''::varchar,
  66. sex char default '0'::bpchar,
  67. avatar int8,
  68. password varchar(100) default ''::varchar,
  69. status char default '0'::bpchar,
  70. del_flag char default '0'::bpchar,
  71. login_ip varchar(128) default ''::varchar,
  72. login_date timestamp,
  73. create_dept int8,
  74. create_by int8,
  75. create_time timestamp,
  76. update_by int8,
  77. update_time timestamp,
  78. remark varchar(500) default null::varchar,
  79. constraint "sys_user_pk" primary key (user_id)
  80. );
  81. comment on table sys_user is '用户信息表';
  82. comment on column sys_user.user_id is '用户ID';
  83. comment on column sys_user.dept_id is '部门ID';
  84. comment on column sys_user.user_name is '用户账号';
  85. comment on column sys_user.nick_name is '用户昵称';
  86. comment on column sys_user.user_type is '用户类型(sys_user系统用户)';
  87. comment on column sys_user.email is '用户邮箱';
  88. comment on column sys_user.phonenumber is '手机号码';
  89. comment on column sys_user.sex is '用户性别(0男 1女 2未知)';
  90. comment on column sys_user.avatar is '头像地址';
  91. comment on column sys_user.password is '密码';
  92. comment on column sys_user.status is '帐号状态(0正常 1停用)';
  93. comment on column sys_user.del_flag is '删除标志(0代表存在 2代表删除)';
  94. comment on column sys_user.login_ip is '最后登陆IP';
  95. comment on column sys_user.login_date is '最后登陆时间';
  96. comment on column sys_user.create_dept is '创建部门';
  97. comment on column sys_user.create_by is '创建者';
  98. comment on column sys_user.create_time is '创建时间';
  99. comment on column sys_user.update_by is '更新者';
  100. comment on column sys_user.update_time is '更新时间';
  101. comment on column sys_user.remark is '备注';
  102. -- ----------------------------
  103. -- 初始化-用户信息表数据
  104. -- ----------------------------
  105. insert into sys_user values(1, 103, 'admin', '疯狂的狮子Li', 'sys_user', 'crazyLionLi@163.com', '15888888888', '1', null, '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '0', '0', '127.0.0.1', now(), 103, 1, now(), null, null, '管理员');
  106. insert into sys_user values(2, 105, 'ry', '疯狂的狮子Li', 'sys_user', 'crazyLionLi@qq.com', '15666666666', '1', null, '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '0', '0', '127.0.0.1', now(), 103, 1, now(), null, null, '测试员');
  107. -- ----------------------------
  108. -- 3、岗位信息表
  109. -- ----------------------------
  110. drop table if exists sys_post;
  111. create table if not exists sys_post
  112. (
  113. post_id int8,
  114. post_code varchar(64) not null,
  115. post_name varchar(50) not null,
  116. post_sort int4 not null,
  117. status char not null,
  118. create_dept int8,
  119. create_by int8,
  120. create_time timestamp,
  121. update_by int8,
  122. update_time timestamp,
  123. remark varchar(500) default null::varchar,
  124. constraint "sys_post_pk" primary key (post_id)
  125. );
  126. comment on table sys_post is '岗位信息表';
  127. comment on column sys_post.post_id is '岗位ID';
  128. comment on column sys_post.post_code is '岗位编码';
  129. comment on column sys_post.post_name is '岗位名称';
  130. comment on column sys_post.post_sort is '显示顺序';
  131. comment on column sys_post.status is '状态(0正常 1停用)';
  132. comment on column sys_post.create_dept is '创建部门';
  133. comment on column sys_post.create_by is '创建者';
  134. comment on column sys_post.create_time is '创建时间';
  135. comment on column sys_post.update_by is '更新者';
  136. comment on column sys_post.update_time is '更新时间';
  137. comment on column sys_post.remark is '备注';
  138. -- ----------------------------
  139. -- 初始化-岗位信息表数据
  140. -- ----------------------------
  141. insert into sys_post values(1, 'ceo', '董事长', 1, '0', 103, 1, now(), null, null, '');
  142. insert into sys_post values(2, 'se', '项目经理', 2, '0', 103, 1, now(), null, null, '');
  143. insert into sys_post values(3, 'hr', '人力资源', 3, '0', 103, 1, now(), null, null, '');
  144. insert into sys_post values(4, 'user', '普通员工', 4, '0', 103, 1, now(), null, null, '');
  145. -- ----------------------------
  146. -- 4、角色信息表
  147. -- ----------------------------
  148. drop table if exists sys_role;
  149. create table if not exists sys_role
  150. (
  151. role_id int8,
  152. role_name varchar(30) not null,
  153. role_key varchar(100) not null,
  154. role_sort int4 not null,
  155. data_scope char default '1'::bpchar,
  156. menu_check_strictly bool default true,
  157. dept_check_strictly bool default true,
  158. status char not null,
  159. del_flag char default '0'::bpchar,
  160. create_dept int8,
  161. create_by int8,
  162. create_time timestamp,
  163. update_by int8,
  164. update_time timestamp,
  165. remark varchar(500) default null::varchar,
  166. constraint "sys_role_pk" primary key (role_id)
  167. );
  168. comment on table sys_role is '角色信息表';
  169. comment on column sys_role.role_id is '角色ID';
  170. comment on column sys_role.role_name is '角色名称';
  171. comment on column sys_role.role_key is '角色权限字符串';
  172. comment on column sys_role.role_sort is '显示顺序';
  173. comment on column sys_role.data_scope is '数据范围(1:全部数据权限 2:自定数据权限 3:本部门数据权限 4:本部门及以下数据权限)';
  174. comment on column sys_role.menu_check_strictly is '菜单树选择项是否关联显示';
  175. comment on column sys_role.dept_check_strictly is '部门树选择项是否关联显示';
  176. comment on column sys_role.status is '角色状态(0正常 1停用)';
  177. comment on column sys_role.del_flag is '删除标志(0代表存在 2代表删除)';
  178. comment on column sys_role.create_dept is '创建部门';
  179. comment on column sys_role.create_by is '创建者';
  180. comment on column sys_role.create_time is '创建时间';
  181. comment on column sys_role.update_by is '更新者';
  182. comment on column sys_role.update_time is '更新时间';
  183. comment on column sys_role.remark is '备注';
  184. -- ----------------------------
  185. -- 初始化-角色信息表数据
  186. -- ----------------------------
  187. insert into sys_role values('1', '超级管理员', 'admin', 1, '1', 't', 't', '0', '0', 103, 1, now(), null, null, '超级管理员');
  188. insert into sys_role values('2', '普通角色', 'common', 2, '2', 't', 't', '0', '0', 103, 1, now(), null, null, '普通角色');
  189. -- ----------------------------
  190. -- 5、菜单权限表
  191. -- ----------------------------
  192. drop table if exists sys_menu;
  193. create table if not exists sys_menu
  194. (
  195. menu_id int8,
  196. menu_name varchar(50) not null,
  197. parent_id int8 default 0,
  198. order_num int4 default 0,
  199. path varchar(200) default ''::varchar,
  200. component varchar(255) default null::varchar,
  201. query_param varchar(255) default null::varchar,
  202. is_frame char default '1'::bpchar,
  203. is_cache char default '0'::bpchar,
  204. menu_type char default ''::bpchar,
  205. visible char default '0'::bpchar,
  206. status char default '0'::bpchar,
  207. perms varchar(100) default null::varchar,
  208. icon varchar(100) default '#'::varchar,
  209. create_dept int8,
  210. create_by int8,
  211. create_time timestamp,
  212. update_by int8,
  213. update_time timestamp,
  214. remark varchar(500) default ''::varchar,
  215. constraint "sys_menu_pk" primary key (menu_id)
  216. );
  217. comment on table sys_menu is '菜单权限表';
  218. comment on column sys_menu.menu_id is '菜单ID';
  219. comment on column sys_menu.menu_name is '菜单名称';
  220. comment on column sys_menu.parent_id is '父菜单ID';
  221. comment on column sys_menu.order_num is '显示顺序';
  222. comment on column sys_menu.path is '路由地址';
  223. comment on column sys_menu.component is '组件路径';
  224. comment on column sys_menu.query_param is '路由参数';
  225. comment on column sys_menu.is_frame is '是否为外链(0是 1否)';
  226. comment on column sys_menu.is_cache is '是否缓存(0缓存 1不缓存)';
  227. comment on column sys_menu.menu_type is '菜单类型(M目录 C菜单 F按钮)';
  228. comment on column sys_menu.visible is '显示状态(0显示 1隐藏)';
  229. comment on column sys_menu.status is '菜单状态(0正常 1停用)';
  230. comment on column sys_menu.perms is '权限标识';
  231. comment on column sys_menu.icon is '菜单图标';
  232. comment on column sys_menu.create_dept is '创建部门';
  233. comment on column sys_menu.create_by is '创建者';
  234. comment on column sys_menu.create_time is '创建时间';
  235. comment on column sys_menu.update_by is '更新者';
  236. comment on column sys_menu.update_time is '更新时间';
  237. comment on column sys_menu.remark is '备注';
  238. -- ----------------------------
  239. -- 初始化-菜单信息表数据
  240. -- ----------------------------
  241. -- 一级菜单
  242. insert into sys_menu values('1', '系统管理', '0', '1', 'system', null, '', '1', '0', 'M', '0', '0', '', 'system', 103, 1, now(), null, null, '系统管理目录');
  243. insert into sys_menu values('2', '系统监控', '0', '2', 'monitor', null, '', '1', '0', 'M', '0', '0', '', 'monitor', 103, 1, now(), null, null, '系统监控目录');
  244. insert into sys_menu values('3', '系统工具', '0', '3', 'tool', null, '', '1', '0', 'M', '0', '0', '', 'tool', 103, 1, now(), null, null, '系统工具目录');
  245. insert into sys_menu values('4', 'PLUS官网', '0', '4', 'https://gitee.com/JavaLionLi/RuoYi-Vue-Plus', null, '', '0', '0', 'M', '0', '0', '', 'guide', 103, 1, now(), null, null, 'RuoYi-Vue-Plus官网地址');
  246. -- 二级菜单
  247. insert into sys_menu values('100', '用户管理', '1', '1', 'user', 'system/user/index', '', '1', '0', 'C', '0', '0', 'system:user:list', 'user', 103, 1, now(), null, null, '用户管理菜单');
  248. insert into sys_menu values('101', '角色管理', '1', '2', 'role', 'system/role/index', '', '1', '0', 'C', '0', '0', 'system:role:list', 'peoples', 103, 1, now(), null, null, '角色管理菜单');
  249. insert into sys_menu values('102', '菜单管理', '1', '3', 'menu', 'system/menu/index', '', '1', '0', 'C', '0', '0', 'system:menu:list', 'tree-table', 103, 1, now(), null, null, '菜单管理菜单');
  250. insert into sys_menu values('103', '部门管理', '1', '4', 'dept', 'system/dept/index', '', '1', '0', 'C', '0', '0', 'system:dept:list', 'tree', 103, 1, now(), null, null, '部门管理菜单');
  251. insert into sys_menu values('104', '岗位管理', '1', '5', 'post', 'system/post/index', '', '1', '0', 'C', '0', '0', 'system:post:list', 'post', 103, 1, now(), null, null, '岗位管理菜单');
  252. insert into sys_menu values('105', '字典管理', '1', '6', 'dict', 'system/dict/index', '', '1', '0', 'C', '0', '0', 'system:dict:list', 'dict', 103, 1, now(), null, null, '字典管理菜单');
  253. insert into sys_menu values('106', '参数设置', '1', '7', 'config', 'system/config/index', '', '1', '0', 'C', '0', '0', 'system:config:list', 'edit', 103, 1, now(), null, null, '参数设置菜单');
  254. insert into sys_menu values('107', '通知公告', '1', '8', 'notice', 'system/notice/index', '', '1', '0', 'C', '0', '0', 'system:notice:list', 'message', 103, 1, now(), null, null, '通知公告菜单');
  255. insert into sys_menu values('108', '日志管理', '1', '9', 'log', '', '', '1', '0', 'M', '0', '0', '', 'log', 103, 1, now(), null, null, '日志管理菜单');
  256. insert into sys_menu values('109', '在线用户', '2', '1', 'online', 'monitor/online/index', '', '1', '0', 'C', '0', '0', 'monitor:online:list', 'online', 103, 1, now(), null, null, '在线用户菜单');
  257. insert into sys_menu values('112', '缓存列表', '2', '6', 'cacheList', 'monitor/cache/list', '', '1', '0', 'C', '0', '0', 'monitor:cache:list', 'redis-list', 103, 1, now(), null, null, '缓存列表菜单');
  258. insert into sys_menu values('113', '缓存监控', '2', '5', 'cache', 'monitor/cache/index', '', '1', '0', 'C', '0', '0', 'monitor:cache:list', 'redis', 103, 1, now(), null, null, '缓存监控菜单');
  259. insert into sys_menu values('114', '表单构建', '3', '1', 'build', 'tool/build/index', '', '1', '0', 'C', '0', '0', 'tool:build:list', 'build', 103, 1, now(), null, null, '表单构建菜单');
  260. insert into sys_menu values('115', '代码生成', '3', '2', 'gen', 'tool/gen/index', '', '1', '0', 'C', '0', '0', 'tool:gen:list', 'code', 103, 1, now(), null, null, '代码生成菜单');
  261. -- springboot-admin监控
  262. insert into sys_menu values('117', 'Admin监控', '2', '5', 'Admin', 'monitor/admin/index', '', '1', '0', 'C', '0', '0', 'monitor:admin:list', 'dashboard', 103, 1, now(), null, null, 'Admin监控菜单');
  263. -- oss菜单
  264. insert into sys_menu values('118', '文件管理', '1', '10', 'oss', 'system/oss/index', '', '1', '0', 'C', '0', '0', 'system:oss:list', 'upload', 103, 1, now(), null, null, '文件管理菜单');
  265. -- xxl-job-admin控制台
  266. insert into sys_menu values('120', '任务调度中心', '2', '5', 'XxlJob', 'monitor/xxljob/index', '', '1', '0', 'C', '0', '0', 'monitor:xxljob:list', 'job', 103, 1, now(), null, null, 'Xxl-Job控制台菜单');
  267. -- 三级菜单
  268. insert into sys_menu values('500', '操作日志', '108', '1', 'operlog', 'monitor/operlog/index', '', '1', '0', 'C', '0', '0', 'monitor:operlog:list', 'form', 103, 1, now(), null, null, '操作日志菜单');
  269. insert into sys_menu values('501', '登录日志', '108', '2', 'logininfor', 'monitor/logininfor/index', '', '1', '0', 'C', '0', '0', 'monitor:logininfor:list', 'logininfor', 103, 1, now(), null, null, '登录日志菜单');
  270. -- 用户管理按钮
  271. insert into sys_menu values('1001', '用户查询', '100', '1', '', '', '', '1', '0', 'F', '0', '0', 'system:user:query', '#', 103, 1, now(), null, null, '');
  272. insert into sys_menu values('1002', '用户新增', '100', '2', '', '', '', '1', '0', 'F', '0', '0', 'system:user:add', '#', 103, 1, now(), null, null, '');
  273. insert into sys_menu values('1003', '用户修改', '100', '3', '', '', '', '1', '0', 'F', '0', '0', 'system:user:edit', '#', 103, 1, now(), null, null, '');
  274. insert into sys_menu values('1004', '用户删除', '100', '4', '', '', '', '1', '0', 'F', '0', '0', 'system:user:remove', '#', 103, 1, now(), null, null, '');
  275. insert into sys_menu values('1005', '用户导出', '100', '5', '', '', '', '1', '0', 'F', '0', '0', 'system:user:export', '#', 103, 1, now(), null, null, '');
  276. insert into sys_menu values('1006', '用户导入', '100', '6', '', '', '', '1', '0', 'F', '0', '0', 'system:user:import', '#', 103, 1, now(), null, null, '');
  277. insert into sys_menu values('1007', '重置密码', '100', '7', '', '', '', '1', '0', 'F', '0', '0', 'system:user:resetPwd', '#', 103, 1, now(), null, null, '');
  278. -- 角色管理按钮
  279. insert into sys_menu values('1008', '角色查询', '101', '1', '', '', '', '1', '0', 'F', '0', '0', 'system:role:query', '#', 103, 1, now(), null, null, '');
  280. insert into sys_menu values('1009', '角色新增', '101', '2', '', '', '', '1', '0', 'F', '0', '0', 'system:role:add', '#', 103, 1, now(), null, null, '');
  281. insert into sys_menu values('1010', '角色修改', '101', '3', '', '', '', '1', '0', 'F', '0', '0', 'system:role:edit', '#', 103, 1, now(), null, null, '');
  282. insert into sys_menu values('1011', '角色删除', '101', '4', '', '', '', '1', '0', 'F', '0', '0', 'system:role:remove', '#', 103, 1, now(), null, null, '');
  283. insert into sys_menu values('1012', '角色导出', '101', '5', '', '', '', '1', '0', 'F', '0', '0', 'system:role:export', '#', 103, 1, now(), null, null, '');
  284. -- 菜单管理按钮
  285. insert into sys_menu values('1013', '菜单查询', '102', '1', '', '', '', '1', '0', 'F', '0', '0', 'system:menu:query', '#', 103, 1, now(), null, null, '');
  286. insert into sys_menu values('1014', '菜单新增', '102', '2', '', '', '', '1', '0', 'F', '0', '0', 'system:menu:add', '#', 103, 1, now(), null, null, '');
  287. insert into sys_menu values('1015', '菜单修改', '102', '3', '', '', '', '1', '0', 'F', '0', '0', 'system:menu:edit', '#', 103, 1, now(), null, null, '');
  288. insert into sys_menu values('1016', '菜单删除', '102', '4', '', '', '', '1', '0', 'F', '0', '0', 'system:menu:remove', '#', 103, 1, now(), null, null, '');
  289. -- 部门管理按钮
  290. insert into sys_menu values('1017', '部门查询', '103', '1', '', '', '', '1', '0', 'F', '0', '0', 'system:dept:query', '#', 103, 1, now(), null, null, '');
  291. insert into sys_menu values('1018', '部门新增', '103', '2', '', '', '', '1', '0', 'F', '0', '0', 'system:dept:add', '#', 103, 1, now(), null, null, '');
  292. insert into sys_menu values('1019', '部门修改', '103', '3', '', '', '', '1', '0', 'F', '0', '0', 'system:dept:edit', '#', 103, 1, now(), null, null, '');
  293. insert into sys_menu values('1020', '部门删除', '103', '4', '', '', '', '1', '0', 'F', '0', '0', 'system:dept:remove', '#', 103, 1, now(), null, null, '');
  294. -- 岗位管理按钮
  295. insert into sys_menu values('1021', '岗位查询', '104', '1', '', '', '', '1', '0', 'F', '0', '0', 'system:post:query', '#', 103, 1, now(), null, null, '');
  296. insert into sys_menu values('1022', '岗位新增', '104', '2', '', '', '', '1', '0', 'F', '0', '0', 'system:post:add', '#', 103, 1, now(), null, null, '');
  297. insert into sys_menu values('1023', '岗位修改', '104', '3', '', '', '', '1', '0', 'F', '0', '0', 'system:post:edit', '#', 103, 1, now(), null, null, '');
  298. insert into sys_menu values('1024', '岗位删除', '104', '4', '', '', '', '1', '0', 'F', '0', '0', 'system:post:remove', '#', 103, 1, now(), null, null, '');
  299. insert into sys_menu values('1025', '岗位导出', '104', '5', '', '', '', '1', '0', 'F', '0', '0', 'system:post:export', '#', 103, 1, now(), null, null, '');
  300. -- 字典管理按钮
  301. insert into sys_menu values('1026', '字典查询', '105', '1', '#', '', '', '1', '0', 'F', '0', '0', 'system:dict:query', '#', 103, 1, now(), null, null, '');
  302. insert into sys_menu values('1027', '字典新增', '105', '2', '#', '', '', '1', '0', 'F', '0', '0', 'system:dict:add', '#', 103, 1, now(), null, null, '');
  303. insert into sys_menu values('1028', '字典修改', '105', '3', '#', '', '', '1', '0', 'F', '0', '0', 'system:dict:edit', '#', 103, 1, now(), null, null, '');
  304. insert into sys_menu values('1029', '字典删除', '105', '4', '#', '', '', '1', '0', 'F', '0', '0', 'system:dict:remove', '#', 103, 1, now(), null, null, '');
  305. insert into sys_menu values('1030', '字典导出', '105', '5', '#', '', '', '1', '0', 'F', '0', '0', 'system:dict:export', '#', 103, 1, now(), null, null, '');
  306. -- 参数设置按钮
  307. insert into sys_menu values('1031', '参数查询', '106', '1', '#', '', '', '1', '0', 'F', '0', '0', 'system:config:query', '#', 103, 1, now(), null, null, '');
  308. insert into sys_menu values('1032', '参数新增', '106', '2', '#', '', '', '1', '0', 'F', '0', '0', 'system:config:add', '#', 103, 1, now(), null, null, '');
  309. insert into sys_menu values('1033', '参数修改', '106', '3', '#', '', '', '1', '0', 'F', '0', '0', 'system:config:edit', '#', 103, 1, now(), null, null, '');
  310. insert into sys_menu values('1034', '参数删除', '106', '4', '#', '', '', '1', '0', 'F', '0', '0', 'system:config:remove', '#', 103, 1, now(), null, null, '');
  311. insert into sys_menu values('1035', '参数导出', '106', '5', '#', '', '', '1', '0', 'F', '0', '0', 'system:config:export', '#', 103, 1, now(), null, null, '');
  312. -- 通知公告按钮
  313. insert into sys_menu values('1036', '公告查询', '107', '1', '#', '', '', '1', '0', 'F', '0', '0', 'system:notice:query', '#', 103, 1, now(), null, null, '');
  314. insert into sys_menu values('1037', '公告新增', '107', '2', '#', '', '', '1', '0', 'F', '0', '0', 'system:notice:add', '#', 103, 1, now(), null, null, '');
  315. insert into sys_menu values('1038', '公告修改', '107', '3', '#', '', '', '1', '0', 'F', '0', '0', 'system:notice:edit', '#', 103, 1, now(), null, null, '');
  316. insert into sys_menu values('1039', '公告删除', '107', '4', '#', '', '', '1', '0', 'F', '0', '0', 'system:notice:remove', '#', 103, 1, now(), null, null, '');
  317. -- 操作日志按钮
  318. insert into sys_menu values('1040', '操作查询', '500', '1', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:operlog:query', '#', 103, 1, now(), null, null, '');
  319. insert into sys_menu values('1041', '操作删除', '500', '2', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:operlog:remove', '#', 103, 1, now(), null, null, '');
  320. insert into sys_menu values('1042', '日志导出', '500', '4', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:operlog:export', '#', 103, 1, now(), null, null, '');
  321. -- 登录日志按钮
  322. insert into sys_menu values('1043', '登录查询', '501', '1', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:logininfor:query', '#', 103, 1, now(), null, null, '');
  323. insert into sys_menu values('1044', '登录删除', '501', '2', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:logininfor:remove', '#', 103, 1, now(), null, null, '');
  324. insert into sys_menu values('1045', '日志导出', '501', '3', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:logininfor:export', '#', 103, 1, now(), null, null, '');
  325. insert into sys_menu values('1050', '账户解锁', '501', '4', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:logininfor:unlock', '#', 103, 1, now(), null, null, '');
  326. -- 在线用户按钮
  327. insert into sys_menu values('1046', '在线查询', '109', '1', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:online:query', '#', 103, 1, now(), null, null, '');
  328. insert into sys_menu values('1047', '批量强退', '109', '2', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:online:batchLogout', '#', 103, 1, now(), null, null, '');
  329. insert into sys_menu values('1048', '单条强退', '109', '3', '#', '', '', '1', '0', 'F', '0', '0', 'monitor:online:forceLogout', '#', 103, 1, now(), null, null, '');
  330. -- 代码生成按钮
  331. insert into sys_menu values('1055', '生成查询', '115', '1', '#', '', '', '1', '0', 'F', '0', '0', 'tool:gen:query', '#', 103, 1, now(), null, null, '');
  332. insert into sys_menu values('1056', '生成修改', '115', '2', '#', '', '', '1', '0', 'F', '0', '0', 'tool:gen:edit', '#', 103, 1, now(), null, null, '');
  333. insert into sys_menu values('1057', '生成删除', '115', '3', '#', '', '', '1', '0', 'F', '0', '0', 'tool:gen:remove', '#', 103, 1, now(), null, null, '');
  334. insert into sys_menu values('1058', '导入代码', '115', '2', '#', '', '', '1', '0', 'F', '0', '0', 'tool:gen:import', '#', 103, 1, now(), null, null, '');
  335. insert into sys_menu values('1059', '预览代码', '115', '4', '#', '', '', '1', '0', 'F', '0', '0', 'tool:gen:preview', '#', 103, 1, now(), null, null, '');
  336. insert into sys_menu values('1060', '生成代码', '115', '5', '#', '', '', '1', '0', 'F', '0', '0', 'tool:gen:code', '#', 103, 1, now(), null, null, '');
  337. -- oss相关按钮
  338. insert into sys_menu values('1600', '文件查询', '118', '1', '#', '', '', '1', '0', 'F', '0', '0', 'system:oss:query', '#', 103, 1, now(), null, null, '');
  339. insert into sys_menu values('1601', '文件上传', '118', '2', '#', '', '', '1', '0', 'F', '0', '0', 'system:oss:upload', '#', 103, 1, now(), null, null, '');
  340. insert into sys_menu values('1602', '文件下载', '118', '3', '#', '', '', '1', '0', 'F', '0', '0', 'system:oss:download', '#', 103, 1, now(), null, null, '');
  341. insert into sys_menu values('1603', '文件删除', '118', '4', '#', '', '', '1', '0', 'F', '0', '0', 'system:oss:remove', '#', 103, 1, now(), null, null, '');
  342. insert into sys_menu values('1604', '配置添加', '118', '5', '#', '', '', '1', '0', 'F', '0', '0', 'system:oss:add', '#', 103, 1, now(), null, null, '');
  343. insert into sys_menu values('1605', '配置编辑', '118', '6', '#', '', '', '1', '0', 'F', '0', '0', 'system:oss:edit', '#', 103, 1, now(), null, null, '');
  344. -- ----------------------------
  345. -- 6、用户和角色关联表 用户N-1角色
  346. -- ----------------------------
  347. drop table if exists sys_user_role;
  348. create table if not exists sys_user_role
  349. (
  350. user_id int8 not null,
  351. role_id int8 not null,
  352. constraint sys_user_role_pk primary key (user_id, role_id)
  353. );
  354. comment on table sys_user_role is '用户和角色关联表';
  355. comment on column sys_user_role.user_id is '用户ID';
  356. comment on column sys_user_role.role_id is '角色ID';
  357. -- ----------------------------
  358. -- 初始化-用户和角色关联表数据
  359. -- ----------------------------
  360. insert into sys_user_role values ('1', '1');
  361. insert into sys_user_role values ('2', '2');
  362. -- ----------------------------
  363. -- 7、角色和菜单关联表 角色1-N菜单
  364. -- ----------------------------
  365. drop table if exists sys_role_menu;
  366. create table if not exists sys_role_menu
  367. (
  368. role_id int8 not null,
  369. menu_id int8 not null,
  370. constraint sys_role_menu_pk primary key (role_id, menu_id)
  371. );
  372. comment on table sys_role_menu is '角色和菜单关联表';
  373. comment on column sys_role_menu.role_id is '角色ID';
  374. comment on column sys_role_menu.menu_id is '菜单ID';
  375. -- ----------------------------
  376. -- 初始化-角色和菜单关联表数据
  377. -- ----------------------------
  378. insert into sys_role_menu values ('2', '1');
  379. insert into sys_role_menu values ('2', '2');
  380. insert into sys_role_menu values ('2', '3');
  381. insert into sys_role_menu values ('2', '4');
  382. insert into sys_role_menu values ('2', '100');
  383. insert into sys_role_menu values ('2', '101');
  384. insert into sys_role_menu values ('2', '102');
  385. insert into sys_role_menu values ('2', '103');
  386. insert into sys_role_menu values ('2', '104');
  387. insert into sys_role_menu values ('2', '105');
  388. insert into sys_role_menu values ('2', '106');
  389. insert into sys_role_menu values ('2', '107');
  390. insert into sys_role_menu values ('2', '108');
  391. insert into sys_role_menu values ('2', '109');
  392. insert into sys_role_menu values ('2', '110');
  393. insert into sys_role_menu values ('2', '111');
  394. insert into sys_role_menu values ('2', '112');
  395. insert into sys_role_menu values ('2', '113');
  396. insert into sys_role_menu values ('2', '114');
  397. insert into sys_role_menu values ('2', '115');
  398. insert into sys_role_menu values ('2', '116');
  399. insert into sys_role_menu values ('2', '500');
  400. insert into sys_role_menu values ('2', '501');
  401. insert into sys_role_menu values ('2', '1000');
  402. insert into sys_role_menu values ('2', '1001');
  403. insert into sys_role_menu values ('2', '1002');
  404. insert into sys_role_menu values ('2', '1003');
  405. insert into sys_role_menu values ('2', '1004');
  406. insert into sys_role_menu values ('2', '1005');
  407. insert into sys_role_menu values ('2', '1006');
  408. insert into sys_role_menu values ('2', '1007');
  409. insert into sys_role_menu values ('2', '1008');
  410. insert into sys_role_menu values ('2', '1009');
  411. insert into sys_role_menu values ('2', '1010');
  412. insert into sys_role_menu values ('2', '1011');
  413. insert into sys_role_menu values ('2', '1012');
  414. insert into sys_role_menu values ('2', '1013');
  415. insert into sys_role_menu values ('2', '1014');
  416. insert into sys_role_menu values ('2', '1015');
  417. insert into sys_role_menu values ('2', '1016');
  418. insert into sys_role_menu values ('2', '1017');
  419. insert into sys_role_menu values ('2', '1018');
  420. insert into sys_role_menu values ('2', '1019');
  421. insert into sys_role_menu values ('2', '1020');
  422. insert into sys_role_menu values ('2', '1021');
  423. insert into sys_role_menu values ('2', '1022');
  424. insert into sys_role_menu values ('2', '1023');
  425. insert into sys_role_menu values ('2', '1024');
  426. insert into sys_role_menu values ('2', '1025');
  427. insert into sys_role_menu values ('2', '1026');
  428. insert into sys_role_menu values ('2', '1027');
  429. insert into sys_role_menu values ('2', '1028');
  430. insert into sys_role_menu values ('2', '1029');
  431. insert into sys_role_menu values ('2', '1030');
  432. insert into sys_role_menu values ('2', '1031');
  433. insert into sys_role_menu values ('2', '1032');
  434. insert into sys_role_menu values ('2', '1033');
  435. insert into sys_role_menu values ('2', '1034');
  436. insert into sys_role_menu values ('2', '1035');
  437. insert into sys_role_menu values ('2', '1036');
  438. insert into sys_role_menu values ('2', '1037');
  439. insert into sys_role_menu values ('2', '1038');
  440. insert into sys_role_menu values ('2', '1039');
  441. insert into sys_role_menu values ('2', '1040');
  442. insert into sys_role_menu values ('2', '1041');
  443. insert into sys_role_menu values ('2', '1042');
  444. insert into sys_role_menu values ('2', '1043');
  445. insert into sys_role_menu values ('2', '1044');
  446. insert into sys_role_menu values ('2', '1045');
  447. insert into sys_role_menu values ('2', '1050');
  448. insert into sys_role_menu values ('2', '1046');
  449. insert into sys_role_menu values ('2', '1047');
  450. insert into sys_role_menu values ('2', '1048');
  451. insert into sys_role_menu values ('2', '1055');
  452. insert into sys_role_menu values ('2', '1056');
  453. insert into sys_role_menu values ('2', '1057');
  454. insert into sys_role_menu values ('2', '1058');
  455. insert into sys_role_menu values ('2', '1059');
  456. insert into sys_role_menu values ('2', '1060');
  457. -- ----------------------------
  458. -- 8、角色和部门关联表 角色1-N部门
  459. -- ----------------------------
  460. drop table if exists sys_role_dept;
  461. create table if not exists sys_role_dept
  462. (
  463. role_id int8 not null,
  464. dept_id int8 not null,
  465. constraint sys_role_dept_pk primary key (role_id, dept_id)
  466. );
  467. comment on table sys_role_dept is '角色和部门关联表';
  468. comment on column sys_role_dept.role_id is '角色ID';
  469. comment on column sys_role_dept.dept_id is '部门ID';
  470. -- ----------------------------
  471. -- 初始化-角色和部门关联表数据
  472. -- ----------------------------
  473. insert into sys_role_dept values ('2', '100');
  474. insert into sys_role_dept values ('2', '101');
  475. insert into sys_role_dept values ('2', '105');
  476. -- ----------------------------
  477. -- 9、用户与岗位关联表 用户1-N岗位
  478. -- ----------------------------
  479. drop table if exists sys_user_post;
  480. create table if not exists sys_user_post
  481. (
  482. user_id int8 not null,
  483. post_id int8 not null,
  484. constraint sys_user_post_pk primary key (user_id, post_id)
  485. );
  486. comment on table sys_user_post is '用户与岗位关联表';
  487. comment on column sys_user_post.user_id is '用户ID';
  488. comment on column sys_user_post.post_id is '岗位ID';
  489. -- ----------------------------
  490. -- 初始化-用户与岗位关联表数据
  491. -- ----------------------------
  492. insert into sys_user_post values ('1', '1');
  493. insert into sys_user_post values ('2', '2');
  494. -- ----------------------------
  495. -- 10、操作日志记录
  496. -- ----------------------------
  497. drop table if exists sys_oper_log;
  498. create table if not exists sys_oper_log
  499. (
  500. oper_id int8,
  501. title varchar(50) default ''::varchar,
  502. business_type int4 default 0,
  503. method varchar(100) default ''::varchar,
  504. request_method varchar(10) default ''::varchar,
  505. operator_type int4 default 0,
  506. oper_name varchar(50) default ''::varchar,
  507. dept_name varchar(50) default ''::varchar,
  508. oper_url varchar(255) default ''::varchar,
  509. oper_ip varchar(128) default ''::varchar,
  510. oper_location varchar(255) default ''::varchar,
  511. oper_param varchar(2000) default ''::varchar,
  512. json_result varchar(2000) default ''::varchar,
  513. status int4 default 0,
  514. error_msg varchar(2000) default ''::varchar,
  515. oper_time timestamp,
  516. constraint sys_oper_log_pk primary key (oper_id)
  517. );
  518. comment on table sys_oper_log is '操作日志记录';
  519. comment on column sys_oper_log.oper_id is '日志主键';
  520. comment on column sys_oper_log.title is '模块标题';
  521. comment on column sys_oper_log.business_type is '业务类型(0其它 1新增 2修改 3删除)';
  522. comment on column sys_oper_log.method is '方法名称';
  523. comment on column sys_oper_log.request_method is '请求方式';
  524. comment on column sys_oper_log.operator_type is '操作类别(0其它 1后台用户 2手机端用户)';
  525. comment on column sys_oper_log.oper_name is '操作人员';
  526. comment on column sys_oper_log.dept_name is '部门名称';
  527. comment on column sys_oper_log.oper_url is '请求URL';
  528. comment on column sys_oper_log.oper_ip is '主机地址';
  529. comment on column sys_oper_log.oper_location is '操作地点';
  530. comment on column sys_oper_log.oper_param is '请求参数';
  531. comment on column sys_oper_log.json_result is '返回参数';
  532. comment on column sys_oper_log.status is '操作状态(0正常 1异常)';
  533. comment on column sys_oper_log.error_msg is '错误消息';
  534. comment on column sys_oper_log.oper_time is '操作时间';
  535. -- ----------------------------
  536. -- 11、字典类型表
  537. -- ----------------------------
  538. drop table if exists sys_dict_type;
  539. create table if not exists sys_dict_type
  540. (
  541. dict_id int8,
  542. dict_name varchar(100) default ''::varchar,
  543. dict_type varchar(100) default ''::varchar,
  544. status char default '0'::bpchar,
  545. create_dept int8,
  546. create_by int8,
  547. create_time timestamp,
  548. update_by int8,
  549. update_time timestamp,
  550. remark varchar(500) default null::varchar,
  551. constraint sys_dict_type_pk primary key (dict_id)
  552. );
  553. comment on table sys_dict_type is '字典类型表';
  554. comment on column sys_dict_type.dict_id is '字典主键';
  555. comment on column sys_dict_type.dict_name is '字典名称';
  556. comment on column sys_dict_type.dict_type is '字典类型';
  557. comment on column sys_dict_type.status is '状态(0正常 1停用)';
  558. comment on column sys_dict_type.create_dept is '创建部门';
  559. comment on column sys_dict_type.create_by is '创建者';
  560. comment on column sys_dict_type.create_time is '创建时间';
  561. comment on column sys_dict_type.update_by is '更新者';
  562. comment on column sys_dict_type.update_time is '更新时间';
  563. comment on column sys_dict_type.remark is '备注';
  564. insert into sys_dict_type values(1, '用户性别', 'sys_user_sex', '0', 103, 1, now(), null, null, '用户性别列表');
  565. insert into sys_dict_type values(2, '菜单状态', 'sys_show_hide', '0', 103, 1, now(), null, null, '菜单状态列表');
  566. insert into sys_dict_type values(3, '系统开关', 'sys_normal_disable', '0', 103, 1, now(), null, null, '系统开关列表');
  567. insert into sys_dict_type values(6, '系统是否', 'sys_yes_no', '0', 103, 1, now(), null, null, '系统是否列表');
  568. insert into sys_dict_type values(7, '通知类型', 'sys_notice_type', '0', 103, 1, now(), null, null, '通知类型列表');
  569. insert into sys_dict_type values(8, '通知状态', 'sys_notice_status', '0', 103, 1, now(), null, null, '通知状态列表');
  570. insert into sys_dict_type values(9, '操作类型', 'sys_oper_type', '0', 103, 1, now(), null, null, '操作类型列表');
  571. insert into sys_dict_type values(10, '系统状态', 'sys_common_status', '0', 103, 1, now(), null, null, '登录状态列表');
  572. -- ----------------------------
  573. -- 12、字典数据表
  574. -- ----------------------------
  575. drop table if exists sys_dict_data;
  576. create table if not exists sys_dict_data
  577. (
  578. dict_code int8,
  579. dict_sort int4 default 0,
  580. dict_label varchar(100) default ''::varchar,
  581. dict_value varchar(100) default ''::varchar,
  582. dict_type varchar(100) default ''::varchar,
  583. css_class varchar(100) default null::varchar,
  584. list_class varchar(100) default null::varchar,
  585. is_default char default 'N'::bpchar,
  586. status char default '0'::bpchar,
  587. create_dept int8,
  588. create_by int8,
  589. create_time timestamp,
  590. update_by int8,
  591. update_time timestamp,
  592. remark varchar(500) default null::varchar,
  593. constraint sys_dict_data_pk primary key (dict_code)
  594. );
  595. comment on table sys_dict_data is '字典数据表';
  596. comment on column sys_dict_data.dict_code is '字典编码';
  597. comment on column sys_dict_data.dict_sort is '字典排序';
  598. comment on column sys_dict_data.dict_label is '字典标签';
  599. comment on column sys_dict_data.dict_value is '字典键值';
  600. comment on column sys_dict_data.dict_type is '字典类型';
  601. comment on column sys_dict_data.css_class is '样式属性(其他样式扩展)';
  602. comment on column sys_dict_data.list_class is '表格回显样式';
  603. comment on column sys_dict_data.is_default is '是否默认(Y是 N否)';
  604. comment on column sys_dict_data.status is '状态(0正常 1停用)';
  605. comment on column sys_dict_data.create_dept is '创建部门';
  606. comment on column sys_dict_data.create_by is '创建者';
  607. comment on column sys_dict_data.create_time is '创建时间';
  608. comment on column sys_dict_data.update_by is '更新者';
  609. comment on column sys_dict_data.update_time is '更新时间';
  610. comment on column sys_dict_data.remark is '备注';
  611. insert into sys_dict_data values(1, 1, '男', '0', 'sys_user_sex', '', '', 'Y', '0', 103, 1, now(), null, null, '性别男');
  612. insert into sys_dict_data values(2, 2, '女', '1', 'sys_user_sex', '', '', 'N', '0', 103, 1, now(), null, null, '性别女');
  613. insert into sys_dict_data values(3, 3, '未知', '2', 'sys_user_sex', '', '', 'N', '0', 103, 1, now(), null, null, '性别未知');
  614. insert into sys_dict_data values(4, 1, '显示', '0', 'sys_show_hide', '', 'primary', 'Y', '0', 103, 1, now(), null, null, '显示菜单');
  615. insert into sys_dict_data values(5, 2, '隐藏', '1', 'sys_show_hide', '', 'danger', 'N', '0', 103, 1, now(), null, null, '隐藏菜单');
  616. insert into sys_dict_data values(6, 1, '正常', '0', 'sys_normal_disable', '', 'primary', 'Y', '0', 103, 1, now(), null, null, '正常状态');
  617. insert into sys_dict_data values(7, 2, '停用', '1', 'sys_normal_disable', '', 'danger', 'N', '0', 103, 1, now(), null, null, '停用状态');
  618. insert into sys_dict_data values(12, 1, '是', 'Y', 'sys_yes_no', '', 'primary', 'Y', '0', 103, 1, now(), null, null, '系统默认是');
  619. insert into sys_dict_data values(13, 2, '否', 'N', 'sys_yes_no', '', 'danger', 'N', '0', 103, 1, now(), null, null, '系统默认否');
  620. insert into sys_dict_data values(14, 1, '通知', '1', 'sys_notice_type', '', 'warning', 'Y', '0', 103, 1, now(), null, null, '通知');
  621. insert into sys_dict_data values(15, 2, '公告', '2', 'sys_notice_type', '', 'success', 'N', '0', 103, 1, now(), null, null, '公告');
  622. insert into sys_dict_data values(16, 1, '正常', '0', 'sys_notice_status', '', 'primary', 'Y', '0', 103, 1, now(), null, null, '正常状态');
  623. insert into sys_dict_data values(17, 2, '关闭', '1', 'sys_notice_status', '', 'danger', 'N', '0', 103, 1, now(), null, null, '关闭状态');
  624. insert into sys_dict_data values(29, 99, '其他', '0', 'sys_oper_type', '', 'info', 'N', '0', 103, 1, now(), null, null, '其他操作');
  625. insert into sys_dict_data values(18, 1, '新增', '1', 'sys_oper_type', '', 'info', 'N', '0', 103, 1, now(), null, null, '新增操作');
  626. insert into sys_dict_data values(19, 2, '修改', '2', 'sys_oper_type', '', 'info', 'N', '0', 103, 1, now(), null, null, '修改操作');
  627. insert into sys_dict_data values(20, 3, '删除', '3', 'sys_oper_type', '', 'danger', 'N', '0', 103, 1, now(), null, null, '删除操作');
  628. insert into sys_dict_data values(21, 4, '授权', '4', 'sys_oper_type', '', 'primary', 'N', '0', 103, 1, now(), null, null, '授权操作');
  629. insert into sys_dict_data values(22, 5, '导出', '5', 'sys_oper_type', '', 'warning', 'N', '0', 103, 1, now(), null, null, '导出操作');
  630. insert into sys_dict_data values(23, 6, '导入', '6', 'sys_oper_type', '', 'warning', 'N', '0', 103, 1, now(), null, null, '导入操作');
  631. insert into sys_dict_data values(24, 7, '强退', '7', 'sys_oper_type', '', 'danger', 'N', '0', 103, 1, now(), null, null, '强退操作');
  632. insert into sys_dict_data values(25, 8, '生成代码', '8', 'sys_oper_type', '', 'warning', 'N', '0', 103, 1, now(), null, null, '生成操作');
  633. insert into sys_dict_data values(26, 9, '清空数据', '9', 'sys_oper_type', '', 'danger', 'N', '0', 103, 1, now(), null, null, '清空操作');
  634. insert into sys_dict_data values(27, 1, '成功', '0', 'sys_common_status', '', 'primary', 'N', '0', 103, 1, now(), null, null, '正常状态');
  635. insert into sys_dict_data values(28, 2, '失败', '1', 'sys_common_status', '', 'danger', 'N', '0', 103, 1, now(), null, null, '停用状态');
  636. -- ----------------------------
  637. -- 13、参数配置表
  638. -- ----------------------------
  639. drop table if exists sys_config;
  640. create table if not exists sys_config
  641. (
  642. config_id int8,
  643. config_name varchar(100) default ''::varchar,
  644. config_key varchar(100) default ''::varchar,
  645. config_value varchar(500) default ''::varchar,
  646. config_type char default 'N'::bpchar,
  647. create_dept int8,
  648. create_by int8,
  649. create_time timestamp,
  650. update_by int8,
  651. update_time timestamp,
  652. remark varchar(500) default null::varchar,
  653. constraint sys_config_pk primary key (config_id)
  654. );
  655. comment on table sys_config is '参数配置表';
  656. comment on column sys_config.config_id is '参数主键';
  657. comment on column sys_config.config_name is '参数名称';
  658. comment on column sys_config.config_key is '参数键名';
  659. comment on column sys_config.config_value is '参数键值';
  660. comment on column sys_config.config_type is '系统内置(Y是 N否)';
  661. comment on column sys_config.create_dept is '创建部门';
  662. comment on column sys_config.create_by is '创建者';
  663. comment on column sys_config.create_time is '创建时间';
  664. comment on column sys_config.update_by is '更新者';
  665. comment on column sys_config.update_time is '更新时间';
  666. comment on column sys_config.remark is '备注';
  667. insert into sys_config values(1, '主框架页-默认皮肤样式名称', 'sys.index.skinName', 'skin-blue', 'Y', 103, 1, now(), null, null, '蓝色 skin-blue、绿色 skin-green、紫色 skin-purple、红色 skin-red、黄色 skin-yellow' );
  668. insert into sys_config values(2, '用户管理-账号初始密码', 'sys.user.initPassword', '123456', 'Y', 103, 1, now(), null, null, '初始化密码 123456' );
  669. insert into sys_config values(3, '主框架页-侧边栏主题', 'sys.index.sideTheme', 'theme-dark', 'Y', 103, 1, now(), null, null, '深色主题theme-dark,浅色主题theme-light' );
  670. insert into sys_config values(4, '账号自助-验证码开关', 'sys.account.captchaEnabled', 'true', 'Y', 103, 1, now(), null, null, '是否开启验证码功能(true开启,false关闭)');
  671. insert into sys_config values(5, '账号自助-是否开启用户注册功能', 'sys.account.registerUser', 'false', 'Y', 103, 1, now(), null, null, '是否开启注册用户功能(true开启,false关闭)');
  672. insert into sys_config values(11, 'OSS预览列表资源开关', 'sys.oss.previewListResource', 'true', 'Y', 103, 1, now(), null, null, 'true:开启, false:关闭');
  673. -- ----------------------------
  674. -- 14、系统访问记录
  675. -- ----------------------------
  676. drop table if exists sys_logininfor;
  677. create table if not exists sys_logininfor
  678. (
  679. info_id int8,
  680. user_name varchar(50) default ''::varchar,
  681. ipaddr varchar(128) default ''::varchar,
  682. login_location varchar(255) default ''::varchar,
  683. browser varchar(50) default ''::varchar,
  684. os varchar(50) default ''::varchar,
  685. status char default '0'::bpchar,
  686. msg varchar(255) default ''::varchar,
  687. login_time timestamp,
  688. constraint sys_logininfor_pk primary key (info_id)
  689. );
  690. comment on table sys_logininfor is '系统访问记录';
  691. comment on column sys_logininfor.info_id is '访问ID';
  692. comment on column sys_logininfor.user_name is '用户账号';
  693. comment on column sys_logininfor.ipaddr is '登录IP地址';
  694. comment on column sys_logininfor.login_location is '登录地点';
  695. comment on column sys_logininfor.browser is '浏览器类型';
  696. comment on column sys_logininfor.os is '操作系统';
  697. comment on column sys_logininfor.status is '登录状态(0成功 1失败)';
  698. comment on column sys_logininfor.msg is '提示消息';
  699. comment on column sys_logininfor.login_time is '访问时间';
  700. -- ----------------------------
  701. -- 17、通知公告表
  702. -- ----------------------------
  703. drop table if exists sys_notice;
  704. create table if not exists sys_notice
  705. (
  706. notice_id int8,
  707. notice_title varchar(50) not null,
  708. notice_type char not null,
  709. notice_content text,
  710. status char default '0'::bpchar,
  711. create_dept int8,
  712. create_by int8,
  713. create_time timestamp,
  714. update_by int8,
  715. update_time timestamp,
  716. remark varchar(255) default null::varchar,
  717. constraint sys_notice_pk primary key (notice_id)
  718. );
  719. comment on table sys_notice is '通知公告表';
  720. comment on column sys_notice.notice_id is '公告ID';
  721. comment on column sys_notice.notice_title is '公告标题';
  722. comment on column sys_notice.notice_type is '公告类型(1通知 2公告)';
  723. comment on column sys_notice.notice_content is '公告内容';
  724. comment on column sys_notice.status is '公告状态(0正常 1关闭)';
  725. comment on column sys_notice.create_dept is '创建部门';
  726. comment on column sys_notice.create_by is '创建者';
  727. comment on column sys_notice.create_time is '创建时间';
  728. comment on column sys_notice.update_by is '更新者';
  729. comment on column sys_notice.update_time is '更新时间';
  730. comment on column sys_notice.remark is '备注';
  731. -- ----------------------------
  732. -- 初始化-公告信息表数据
  733. -- ----------------------------
  734. insert into sys_notice values('1', '温馨提醒:2018-07-01 新版本发布啦', '2', '新版本内容', '0', 103, 1, now(), null, null, '管理员');
  735. insert into sys_notice values('2', '维护通知:2018-07-01 系统凌晨维护', '1', '维护内容', '0', 103, 1, now(), null, null, '管理员');
  736. -- ----------------------------
  737. -- 18、代码生成业务表
  738. -- ----------------------------
  739. drop table if exists gen_table;
  740. create table if not exists gen_table
  741. (
  742. table_id int8,
  743. table_name varchar(200) default ''::varchar,
  744. table_comment varchar(500) default ''::varchar,
  745. sub_table_name varchar(64) default ''::varchar,
  746. sub_table_fk_name varchar(64) default ''::varchar,
  747. class_name varchar(100) default ''::varchar,
  748. tpl_category varchar(200) default 'crud'::varchar,
  749. package_name varchar(100) default null::varchar,
  750. module_name varchar(30) default null::varchar,
  751. business_name varchar(30) default null::varchar,
  752. function_name varchar(50) default null::varchar,
  753. function_author varchar(50) default null::varchar,
  754. gen_type char default '0'::bpchar not null,
  755. gen_path varchar(200) default '/'::varchar,
  756. options varchar(1000) default null::varchar,
  757. create_by int8,
  758. create_time timestamp,
  759. update_by int8,
  760. update_time timestamp,
  761. remark varchar(500) default null::varchar,
  762. constraint gen_table_pk primary key (table_id)
  763. );
  764. comment on table gen_table is '代码生成业务表';
  765. comment on column gen_table.table_id is '编号';
  766. comment on column gen_table.table_name is '表名称';
  767. comment on column gen_table.table_comment is '表描述';
  768. comment on column gen_table.sub_table_name is '关联子表的表名';
  769. comment on column gen_table.sub_table_fk_name is '子表关联的外键名';
  770. comment on column gen_table.class_name is '实体类名称';
  771. comment on column gen_table.tpl_category is '使用的模板(CRUD单表操作 TREE树表操作)';
  772. comment on column gen_table.package_name is '生成包路径';
  773. comment on column gen_table.module_name is '生成模块名';
  774. comment on column gen_table.business_name is '生成业务名';
  775. comment on column gen_table.function_name is '生成功能名';
  776. comment on column gen_table.function_author is '生成功能作者';
  777. comment on column gen_table.gen_type is '生成代码方式(0zip压缩包 1自定义路径)';
  778. comment on column gen_table.gen_path is '生成路径(不填默认项目路径)';
  779. comment on column gen_table.options is '其它生成选项';
  780. comment on column gen_table.create_dept is '创建部门';
  781. comment on column gen_table.create_by is '创建者';
  782. comment on column gen_table.create_time is '创建时间';
  783. comment on column gen_table.update_by is '更新者';
  784. comment on column gen_table.update_time is '更新时间';
  785. comment on column gen_table.remark is '备注';
  786. -- ----------------------------
  787. -- 19、代码生成业务表字段
  788. -- ----------------------------
  789. drop table if exists gen_table_column;
  790. create table if not exists gen_table_column
  791. (
  792. column_id int8,
  793. table_id int8,
  794. column_name varchar(200) default null::varchar,
  795. column_comment varchar(500) default null::varchar,
  796. column_type varchar(100) default null::varchar,
  797. java_type varchar(500) default null::varchar,
  798. java_field varchar(200) default null::varchar,
  799. is_pk char default null::bpchar,
  800. is_increment char default null::bpchar,
  801. is_required char default null::bpchar,
  802. is_insert char default null::bpchar,
  803. is_edit char default null::bpchar,
  804. is_list char default null::bpchar,
  805. is_query char default null::bpchar,
  806. query_type varchar(200) default 'EQ'::varchar,
  807. html_type varchar(200) default null::varchar,
  808. dict_type varchar(200) default ''::varchar,
  809. sort int4,
  810. create_dept int8,
  811. create_by int8,
  812. create_time timestamp,
  813. update_by int8,
  814. update_time timestamp,
  815. constraint gen_table_column_pk primary key (column_id)
  816. );
  817. comment on table gen_table_column is '代码生成业务表字段';
  818. comment on column gen_table_column.column_id is '编号';
  819. comment on column gen_table_column.table_id is '归属表编号';
  820. comment on column gen_table_column.column_name is '列名称';
  821. comment on column gen_table_column.column_comment is '列描述';
  822. comment on column gen_table_column.column_type is '列类型';
  823. comment on column gen_table_column.java_type is 'JAVA类型';
  824. comment on column gen_table_column.java_field is 'JAVA字段名';
  825. comment on column gen_table_column.is_pk is '是否主键(1是)';
  826. comment on column gen_table_column.is_increment is '是否自增(1是)';
  827. comment on column gen_table_column.is_required is '是否必填(1是)';
  828. comment on column gen_table_column.is_insert is '是否为插入字段(1是)';
  829. comment on column gen_table_column.is_edit is '是否编辑字段(1是)';
  830. comment on column gen_table_column.is_list is '是否列表字段(1是)';
  831. comment on column gen_table_column.is_query is '是否查询字段(1是)';
  832. comment on column gen_table_column.query_type is '查询方式(等于、不等于、大于、小于、范围)';
  833. comment on column gen_table_column.html_type is '显示类型(文本框、文本域、下拉框、复选框、单选框、日期控件)';
  834. comment on column gen_table_column.dict_type is '字典类型';
  835. comment on column gen_table_column.sort is '排序';
  836. comment on column gen_table_column.create_dept is '创建部门';
  837. comment on column gen_table_column.create_by is '创建者';
  838. comment on column gen_table_column.create_time is '创建时间';
  839. comment on column gen_table_column.update_by is '更新者';
  840. comment on column gen_table_column.update_time is '更新时间';
  841. -- ----------------------------
  842. -- OSS对象存储表
  843. -- ----------------------------
  844. drop table if exists sys_oss;
  845. create table if not exists sys_oss
  846. (
  847. oss_id int8,
  848. file_name varchar(255) default ''::varchar not null,
  849. original_name varchar(255) default ''::varchar not null,
  850. file_suffix varchar(10) default ''::varchar not null,
  851. url varchar(500) default ''::varchar not null,
  852. create_dept int8,
  853. create_by int8,
  854. create_time timestamp,
  855. update_by int8,
  856. update_time timestamp,
  857. service varchar(20) default 'minio'::varchar,
  858. constraint sys_oss_pk primary key (oss_id)
  859. );
  860. comment on table sys_oss is 'OSS对象存储表';
  861. comment on column sys_oss.oss_id is '对象存储主键';
  862. comment on column sys_oss.file_name is '文件名';
  863. comment on column sys_oss.original_name is '原名';
  864. comment on column sys_oss.file_suffix is '文件后缀名';
  865. comment on column sys_oss.url is 'URL地址';
  866. comment on column sys_oss.create_by is '上传人';
  867. comment on column sys_oss.create_dept is '创建部门';
  868. comment on column sys_oss.create_time is '创建时间';
  869. comment on column sys_oss.update_by is '更新者';
  870. comment on column sys_oss.update_time is '更新时间';
  871. comment on column sys_oss.service is '服务商';
  872. -- ----------------------------
  873. -- OSS对象存储动态配置表
  874. -- ----------------------------
  875. drop table if exists sys_oss_config;
  876. create table if not exists sys_oss_config
  877. (
  878. oss_config_id int8,
  879. config_key varchar(20) default ''::varchar not null,
  880. access_key varchar(255) default ''::varchar,
  881. secret_key varchar(255) default ''::varchar,
  882. bucket_name varchar(255) default ''::varchar,
  883. prefix varchar(255) default ''::varchar,
  884. endpoint varchar(255) default ''::varchar,
  885. domain varchar(255) default ''::varchar,
  886. is_https char default 'N'::bpchar,
  887. region varchar(255) default ''::varchar,
  888. access_policy char(1) default '1'::bpchar not null,
  889. status char default '1'::bpchar,
  890. ext1 varchar(255) default ''::varchar,
  891. create_dept int8,
  892. create_by int8,
  893. create_time timestamp,
  894. update_by int8,
  895. update_time timestamp,
  896. remark varchar(500) default ''::varchar,
  897. constraint sys_oss_config_pk primary key (oss_config_id)
  898. );
  899. comment on table sys_oss_config is '对象存储配置表';
  900. comment on column sys_oss_config.oss_config_id is '主建';
  901. comment on column sys_oss_config.config_key is '配置key';
  902. comment on column sys_oss_config.access_key is 'accessKey';
  903. comment on column sys_oss_config.secret_key is '秘钥';
  904. comment on column sys_oss_config.bucket_name is '桶名称';
  905. comment on column sys_oss_config.prefix is '前缀';
  906. comment on column sys_oss_config.endpoint is '访问站点';
  907. comment on column sys_oss_config.domain is '自定义域名';
  908. comment on column sys_oss_config.is_https is '是否https(Y=是,N=否)';
  909. comment on column sys_oss_config.region is '域';
  910. comment on column sys_oss_config.access_policy is '桶权限类型(0=private 1=public 2=custom)';
  911. comment on column sys_oss_config.status is '状态(0=正常,1=停用)';
  912. comment on column sys_oss_config.ext1 is '扩展字段';
  913. comment on column sys_oss_config.create_dept is '创建部门';
  914. comment on column sys_oss_config.create_by is '创建者';
  915. comment on column sys_oss_config.create_time is '创建时间';
  916. comment on column sys_oss_config.update_by is '更新者';
  917. comment on column sys_oss_config.update_time is '更新时间';
  918. comment on column sys_oss_config.remark is '备注';
  919. insert into sys_oss_config values (1, 'minio', 'ruoyi', 'ruoyi123', 'ruoyi', '', '127.0.0.1:9000', '','N', '', '1', '0', '', 103, 1, now(), 1, now(), null);
  920. insert into sys_oss_config values (2, 'qiniu', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'ruoyi', '', 's3-cn-north-1.qiniucs.com', '','N', '', '1', '1', '', 103, 1, now(), 1, now(), null);
  921. insert into sys_oss_config values (3, 'aliyun', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'ruoyi', '', 'oss-cn-beijing.aliyuncs.com', '','N', '', '1', '1', '', 103, 1, now(), 1, now(), null);
  922. insert into sys_oss_config values (4, 'qcloud', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'ruoyi-1250000000', '', 'cos.ap-beijing.myqcloud.com', '','N', 'ap-beijing', '1', '1', '', 103, 1, now(), 1, now(), null);
  923. insert into sys_oss_config values (5, 'image', 'ruoyi', 'ruoyi123', 'ruoyi', 'image', '127.0.0.1:9000', '','N', '', '1', '1', '', 103, 1, now(), 1, now(), NULL);
  924. -- 字符串自动转时间 避免框架时间查询报错问题
  925. create or replace function cast_varchar_to_timestamp(varchar) returns timestamptz as $$
  926. select to_timestamp($1, 'yyyy-mm-dd hh24:mi:ss');
  927. $$ language sql strict ;
  928. create cast (varchar as timestamptz) with function cast_varchar_to_timestamp as IMPLICIT;