postgres_ry_vue_5.X.sql 71 KB

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