tocc-sq/sql/ry_postgresql.sql

-- ----------------------------
-- 1、部门表
-- ----------------------------
drop table if exists sys_dept;
create table sys_dept
(
    dept_id     BIGSERIAL not null,
    parent_id   int8        default 0,
    ancestors   varchar(50) default '',
    dept_name   varchar(30) default '',
    order_num   int4 default 0,
    leader      varchar(20) default null,
    phone       varchar(11) default null,
    email       varchar(50) default null,
    status      char(1)     default '0',
    del_flag    char(1)     default '0',
    create_by   varchar(64) default '',
    create_time timestamp(0),
    update_by   varchar(64) default '',
    update_time timestamp(0),
    CONSTRAINT sys_dept_pkey primary key (dept_id)
);
COMMENT
ON COLUMN sys_dept.dept_id IS '部门id';
COMMENT
ON COLUMN sys_dept.parent_id IS '父部门id';
COMMENT
ON COLUMN sys_dept.ancestors IS '祖级列表';
COMMENT
ON COLUMN sys_dept.dept_name IS '部门名称';
COMMENT
ON COLUMN sys_dept.order_num IS '显示顺序';
COMMENT
ON COLUMN sys_dept.leader IS '负责人';
COMMENT
ON COLUMN sys_dept.phone IS '联系电话';
COMMENT
ON COLUMN sys_dept.email IS '邮箱';
COMMENT
ON COLUMN sys_dept.status IS '部门状态（0正常 1停用）';
COMMENT
ON COLUMN sys_dept.del_flag IS '删除标志（0代表存在 2代表删除）';
COMMENT
ON COLUMN sys_dept.create_by IS '创建者';
COMMENT
ON COLUMN sys_dept.create_time IS '创建时间';
COMMENT
ON COLUMN sys_dept.update_by IS '更新者';
COMMENT
ON COLUMN sys_dept.update_time IS '更新时间';
COMMENT
ON TABLE sys_dept IS '部门表';

-- ----------------------------
-- 初始化-部门表数据
-- ----------------------------
insert into sys_dept
values (100, 0, '0', '新通智能', 0, '新通', '15888888888', '', '0', '0', 'admin', now(), '', null);


-- ----------------------------
-- 2、用户信息表
-- ----------------------------
drop table if exists sys_user;
create table sys_user
(
    user_id     BIGSERIAL   not null,
    dept_id     int8         default null,
    user_name   varchar(30) not null,
    nick_name   varchar(30) not null,
    user_type   varchar(2)   default '00',
    email       varchar(50)  default '',
    phonenumber varchar(11)  default '',
    sex         char(1)      default '0',
    avatar      varchar(100) default '',
    password    varchar(100) default '',
    status      char(1)      default '0',
    del_flag    char(1)      default '0',
    login_ip    varchar(128) default '',
    login_date  timestamp(0),
    create_by   varchar(64)  default '',
    create_time timestamp(0),
    update_by   varchar(64)  default '',
    update_time timestamp(0),
    remark      varchar(500) default null,
    CONSTRAINT sys_user_pkey primary key (user_id)
);
COMMENT
ON COLUMN sys_user.user_id IS '用户ID';
COMMENT
ON COLUMN sys_user.dept_id IS '部门ID';
COMMENT
ON COLUMN sys_user.user_name IS '用户账号';
COMMENT
ON COLUMN sys_user.nick_name IS '用户昵称';
COMMENT
ON COLUMN sys_user.user_type IS '用户类型（00系统用户）';
COMMENT
ON COLUMN sys_user.email IS '用户邮箱';
COMMENT
ON COLUMN sys_user.phonenumber IS '手机号码';
COMMENT
ON COLUMN sys_user.sex IS '用户性别（0男 1女 2未知）';
COMMENT
ON COLUMN sys_user.avatar IS '头像地址';
COMMENT
ON COLUMN sys_user.password IS '密码';
COMMENT
ON COLUMN sys_user.status IS '帐号状态（0正常 1停用）';
COMMENT
ON COLUMN sys_user.del_flag IS '删除标志（0代表存在 2代表删除）';
COMMENT
ON COLUMN sys_user.login_ip IS '最后登录IP';
COMMENT
ON COLUMN sys_user.login_date IS '最后登录时间';
COMMENT
ON COLUMN sys_user.create_by IS '创建者';
COMMENT
ON COLUMN sys_user.create_time IS '创建时间';
COMMENT
ON COLUMN sys_user.update_by IS '更新者';
COMMENT
ON COLUMN sys_user.update_time IS '更新时间';
COMMENT
ON COLUMN sys_user.remark IS '备注';
COMMENT
ON TABLE sys_user IS '用户信息表';

-- ----------------------------
-- 初始化-用户信息表数据
-- ----------------------------
insert into sys_user
values (1, 100, 'admin', '若依', '00', 'ry@163.com', '15888888888', '1', '', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', '0', '0', '127.0.0.1', now(), 'admin', now(), '', null, '管理员');


-- ----------------------------
-- 3、岗位信息表
-- ----------------------------
drop table if exists sys_post;
create table sys_post
(
    post_id BIGSERIAL not null              ,
        post_code varchar (64) not null,
        post_name varchar (50) not null,
        post_sort int4 not null,
        status char (1) not null,
        create_by varchar (64) default '',
        create_time timestamp (0),
        update_by varchar (64) default '',
        update_time timestamp (0),
        remark varchar (500) default null,
        CONSTRAINT sys_post_pkey primary key (post_id)
);
COMMENT
ON COLUMN sys_post.post_id IS '岗位ID';
COMMENT
ON COLUMN sys_post.post_code IS '岗位编码';
COMMENT
ON COLUMN sys_post.post_name IS '岗位名称';
COMMENT
ON COLUMN sys_post.post_sort IS '显示顺序';
COMMENT
ON COLUMN sys_post.status IS '状态（0正常 1停用）';
COMMENT
ON COLUMN sys_post.create_by IS '创建者';
COMMENT
ON COLUMN sys_post.create_time IS '创建时间';
COMMENT
ON COLUMN sys_post.update_by IS '更新者';
COMMENT
ON COLUMN sys_post.update_time IS '更新时间';
COMMENT
ON COLUMN sys_post.remark IS '备注';
COMMENT
ON TABLE sys_post IS '岗位信息表';

-- ----------------------------
-- 初始化-岗位信息表数据
-- ----------------------------
insert into sys_post
values (1, 'ceo', '董事长', 1, '0', 'admin', now(), '', null, '');
insert into sys_post
values (4, 'user', '普通员工', 4, '0', 'admin', now(), '', null, '');


-- ----------------------------
-- 4、角色信息表
-- ----------------------------
drop table if exists sys_role;
create table sys_role
(
    role_id             BIGSERIAL    not null,
    role_name           varchar(30)  not null,
    role_key            varchar(100) not null,
    role_sort           int4         not null,
    data_scope          char(1)      default '1',
    menu_check_strictly bool         default '1',
    dept_check_strictly bool         default '1',
    status              char(1)      not null,
    del_flag            char(1)      default '0',
    create_by           varchar(64)  default '',
    create_time         timestamp(0),
    update_by           varchar(64)  default '',
    update_time         timestamp(0),
    remark              varchar(500) default null,
    CONSTRAINT sys_role_pkey primary key (role_id)
);
COMMENT
ON COLUMN sys_role.role_id IS '角色ID';
COMMENT
ON COLUMN sys_role.role_name IS '角色名称';
COMMENT
ON COLUMN sys_role.role_key IS '角色权限字符串';
COMMENT
ON COLUMN sys_role.role_sort IS '显示顺序';
COMMENT
ON COLUMN sys_role.data_scope IS '数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）';
COMMENT
ON COLUMN sys_role.menu_check_strictly IS '菜单树选择项是否关联显示';
COMMENT
ON COLUMN sys_role.dept_check_strictly IS '部门树选择项是否关联显示';
COMMENT
ON COLUMN sys_role.status IS '角色状态（0正常 1停用）';
COMMENT
ON COLUMN sys_role.del_flag IS '删除标志（0代表存在 2代表删除）';
COMMENT
ON COLUMN sys_role.create_by IS '创建者';
COMMENT
ON COLUMN sys_role.create_time IS '创建时间';
COMMENT
ON COLUMN sys_role.update_by IS '更新者';
COMMENT
ON COLUMN sys_role.update_time IS '更新时间';
COMMENT
ON COLUMN sys_role.remark IS '备注';
COMMENT
ON TABLE sys_role IS '角色信息表';
-- ----------------------------
-- 初始化-角色信息表数据
-- ----------------------------
insert into sys_role
values ('1', '超级管理员', 'admin', 1, 1, true, true, '0', '0', 'admin', now(), '', null, '超级管理员');
insert into sys_role
values ('2', '普通角色', 'common', 2, 2, true, true, '0', '0', 'admin', now(), '', null, '普通角色');


-- ----------------------------
-- 5、菜单权限表
-- ----------------------------
drop table if exists sys_menu;
create table sys_menu
(
    menu_id     BIGSERIAL   not null,
    menu_name   varchar(50) not null,
    parent_id   int8         default 0,
    order_num   int4         default 0,
    path        varchar(200) default '',
    component   varchar(255) default null,
    is_frame    int4         default 1,
    is_cache    int4         default 0,
    menu_type   char(1)      default '',
    visible     char(1)      default 0,
    status      char(1)      default 0,
    perms       varchar(100) default null,
    icon        varchar(100) default '#',
    create_by   varchar(64)  default '',
    create_time timestamp(0),
    update_by   varchar(64)  default '',
    update_time timestamp(0),
    remark      varchar(500) default '',
    CONSTRAINT sys_menu_pkey primary key (menu_id)
);
COMMENT
ON COLUMN sys_menu.menu_id IS '菜单ID';
COMMENT
ON COLUMN sys_menu.menu_name IS '菜单名称';
COMMENT
ON COLUMN sys_menu.parent_id IS '父菜单ID';
COMMENT
ON COLUMN sys_menu.order_num IS '显示顺序';
COMMENT
ON COLUMN sys_menu.path IS '路由地址';
COMMENT
ON COLUMN sys_menu.component IS '组件路径';
COMMENT
ON COLUMN sys_menu.is_frame IS '是否为外链（0是 1否）';
COMMENT
ON COLUMN sys_menu.is_cache IS '是否缓存（0缓存 1不缓存）';
COMMENT
ON COLUMN sys_menu.menu_type IS '菜单类型（M目录 C菜单 F按钮）';
COMMENT
ON COLUMN sys_menu.visible IS '菜单状态（0显示 1隐藏）';
COMMENT
ON COLUMN sys_menu.status IS '菜单状态（0正常 1停用）';
COMMENT
ON COLUMN sys_menu.perms IS '权限标识';
COMMENT
ON COLUMN sys_menu.icon IS '菜单图标';
COMMENT
ON COLUMN sys_menu.create_by IS '创建者';
COMMENT
ON COLUMN sys_menu.create_time IS '创建时间';
COMMENT
ON COLUMN sys_menu.update_by IS '更新者';
COMMENT
ON COLUMN sys_menu.update_time IS '更新时间';
COMMENT
ON COLUMN sys_menu.remark IS '备注';
COMMENT
ON TABLE sys_menu IS '菜单权限表';

-- ----------------------------
-- 初始化-菜单信息表数据
-- ----------------------------
-- 一级菜单
insert into sys_menu
values ('1', '系统管理', '0', '1', 'system', null, 1, 0, 'M', '0', '0', '', 'system', 'admin', now(), '', null, '系统管理目录');
insert into sys_menu
values ('2', '系统监控', '0', '2', 'monitor', null, 1, 0, 'M', '0', '0', '', 'monitor', 'admin', now(), '', null, '系统监控目录');
insert into sys_menu
values ('3', '系统工具', '0', '3', 'tool', null, 1, 0, 'M', '0', '0', '', 'tool', 'admin', now(), '', null, '系统工具目录');
-- 二级菜单
insert into sys_menu
values ('100', '用户管理', '1', '1', 'user', 'system/user/index', 1, 0, 'C', '0', '0', 'system:user:list', 'user', 'admin', now(), '', null, '用户管理菜单');
insert into sys_menu
values ('101', '角色管理', '1', '2', 'role', 'system/role/index', 1, 0, 'C', '0', '0', 'system:role:list', 'peoples', 'admin', now(), '', null, '角色管理菜单');
insert into sys_menu
values ('102', '菜单管理', '1', '3', 'menu', 'system/menu/index', 1, 0, 'C', '0', '0', 'system:menu:list', 'tree-table', 'admin', now(), '', null, '菜单管理菜单');
insert into sys_menu
values ('103', '部门管理', '1', '4', 'dept', 'system/dept/index', 1, 0, 'C', '0', '0', 'system:dept:list', 'tree', 'admin', now(), '', null, '部门管理菜单');
insert into sys_menu
values ('104', '岗位管理', '1', '5', 'post', 'system/post/index', 1, 0, 'C', '0', '0', 'system:post:list', 'post', 'admin', now(), '', null, '岗位管理菜单');
insert into sys_menu
values ('105', '字典管理', '1', '6', 'dict', 'system/dict/index', 1, 0, 'C', '0', '0', 'system:dict:list', 'dict', 'admin', now(), '', null, '字典管理菜单');
insert into sys_menu
values ('106', '参数设置', '1', '7', 'config', 'system/config/index', 1, 0, 'C', '0', '0', 'system:config:list', 'edit', 'admin', now(), '', null, '参数设置菜单');
insert into sys_menu
values ('107', '通知公告', '1', '8', 'notice', 'system/notice/index', 1, 0, 'C', '0', '0', 'system:notice:list', 'message', 'admin', now(), '', null, '通知公告菜单');
insert into sys_menu
values ('108', '日志管理', '1', '9', 'log', '', 1, 0, 'M', '0', '0', '', 'log', 'admin', now(), '', null, '日志管理菜单');
insert into sys_menu
values ('109', '在线用户', '2', '1', 'online', 'monitor/online/index', 1, 0, 'C', '0', '0', 'monitor:online:list', 'online', 'admin', now(), '', null, '在线用户菜单');
insert into sys_menu
values ('110', '定时任务', '2', '2', 'job', 'monitor/job/index', 1, 0, 'C', '0', '0', 'monitor:job:list', 'job', 'admin', now(), '', null, '定时任务菜单');
insert into sys_menu
values ('111', '数据监控', '2', '3', 'druid', 'monitor/druid/index', 1, 0, 'C', '0', '0', 'monitor:druid:list', 'druid', 'admin', now(), '', null, '数据监控菜单');
insert into sys_menu values('112',  '服务监控', '2',   '4', 'server',     'monitor/server/index',     1, 0, 'C', '0', '0', 'monitor:server:list',     'server',        'admin', now(), '', null, '服务监控菜单');
insert into sys_menu
values ('113', '缓存监控', '2', '5', 'cache', 'monitor/cache/index', 1, 0, 'C', '0', '0', 'monitor:cache:list', 'redis', 'admin', now(), '', null, '缓存监控菜单');
insert into sys_menu
values ('114', '表单构建', '3', '1', 'build', 'tool/build/index', 1, 0, 'C', '0', '0', 'tool:build:list', 'build', 'admin', now(), '', null, '表单构建菜单');
insert into sys_menu
values ('115', '代码生成', '3', '2', 'gen', 'tool/gen/index', 1, 0, 'C', '0', '0', 'tool:gen:list', 'code', 'admin', now(), '', null, '代码生成菜单');
insert into sys_menu
values ('116', '系统接口', '3', '3', 'swagger', 'tool/swagger/index', 1, 0, 'C', '0', '0', 'tool:swagger:list', 'swagger', 'admin', now(), '', null, '系统接口菜单');

-- 三级菜单
insert into sys_menu
values ('500', '操作日志', '108', '1', 'operlog', 'monitor/operlog/index', 1, 0, 'C', '0', '0', 'monitor:operlog:list', 'form', 'admin', now(), '', null, '操作日志菜单');
insert into sys_menu
values ('501', '登录日志', '108', '2', 'logininfor', 'monitor/logininfor/index', 1, 0, 'C', '0', '0', 'monitor:logininfor:list', 'logininfor', 'admin', now(), '', null, '登录日志菜单');
-- 用户管理按钮
insert into sys_menu
values ('1001', '用户查询', '100', '1', '', '', 1, 0, 'F', '0', '0', 'system:user:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1002', '用户新增', '100', '2', '', '', 1, 0, 'F', '0', '0', 'system:user:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1003', '用户修改', '100', '3', '', '', 1, 0, 'F', '0', '0', 'system:user:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1004', '用户删除', '100', '4', '', '', 1, 0, 'F', '0', '0', 'system:user:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1005', '用户导出', '100', '5', '', '', 1, 0, 'F', '0', '0', 'system:user:export', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1006', '用户导入', '100', '6', '', '', 1, 0, 'F', '0', '0', 'system:user:import', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1007', '重置密码', '100', '7', '', '', 1, 0, 'F', '0', '0', 'system:user:resetPwd', '#', 'admin', now(), '', null, '');
-- 角色管理按钮
insert into sys_menu
values ('1008', '角色查询', '101', '1', '', '', 1, 0, 'F', '0', '0', 'system:role:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1009', '角色新增', '101', '2', '', '', 1, 0, 'F', '0', '0', 'system:role:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1010', '角色修改', '101', '3', '', '', 1, 0, 'F', '0', '0', 'system:role:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1011', '角色删除', '101', '4', '', '', 1, 0, 'F', '0', '0', 'system:role:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1012', '角色导出', '101', '5', '', '', 1, 0, 'F', '0', '0', 'system:role:export', '#', 'admin', now(), '', null, '');
-- 菜单管理按钮
insert into sys_menu
values ('1013', '菜单查询', '102', '1', '', '', 1, 0, 'F', '0', '0', 'system:menu:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1014', '菜单新增', '102', '2', '', '', 1, 0, 'F', '0', '0', 'system:menu:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1015', '菜单修改', '102', '3', '', '', 1, 0, 'F', '0', '0', 'system:menu:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1016', '菜单删除', '102', '4', '', '', 1, 0, 'F', '0', '0', 'system:menu:remove', '#', 'admin', now(), '', null, '');
-- 部门管理按钮
insert into sys_menu
values ('1017', '部门查询', '103', '1', '', '', 1, 0, 'F', '0', '0', 'system:dept:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1018', '部门新增', '103', '2', '', '', 1, 0, 'F', '0', '0', 'system:dept:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1019', '部门修改', '103', '3', '', '', 1, 0, 'F', '0', '0', 'system:dept:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1020', '部门删除', '103', '4', '', '', 1, 0, 'F', '0', '0', 'system:dept:remove', '#', 'admin', now(), '', null, '');
-- 岗位管理按钮
insert into sys_menu
values ('1021', '岗位查询', '104', '1', '', '', 1, 0, 'F', '0', '0', 'system:post:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1022', '岗位新增', '104', '2', '', '', 1, 0, 'F', '0', '0', 'system:post:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1023', '岗位修改', '104', '3', '', '', 1, 0, 'F', '0', '0', 'system:post:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1024', '岗位删除', '104', '4', '', '', 1, 0, 'F', '0', '0', 'system:post:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1025', '岗位导出', '104', '5', '', '', 1, 0, 'F', '0', '0', 'system:post:export', '#', 'admin', now(), '', null, '');
-- 字典管理按钮
insert into sys_menu
values ('1026', '字典查询', '105', '1', '#', '', 1, 0, 'F', '0', '0', 'system:dict:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1027', '字典新增', '105', '2', '#', '', 1, 0, 'F', '0', '0', 'system:dict:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1028', '字典修改', '105', '3', '#', '', 1, 0, 'F', '0', '0', 'system:dict:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1029', '字典删除', '105', '4', '#', '', 1, 0, 'F', '0', '0', 'system:dict:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1030', '字典导出', '105', '5', '#', '', 1, 0, 'F', '0', '0', 'system:dict:export', '#', 'admin', now(), '', null, '');
-- 参数设置按钮
insert into sys_menu
values ('1031', '参数查询', '106', '1', '#', '', 1, 0, 'F', '0', '0', 'system:config:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1032', '参数新增', '106', '2', '#', '', 1, 0, 'F', '0', '0', 'system:config:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1033', '参数修改', '106', '3', '#', '', 1, 0, 'F', '0', '0', 'system:config:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1034', '参数删除', '106', '4', '#', '', 1, 0, 'F', '0', '0', 'system:config:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1035', '参数导出', '106', '5', '#', '', 1, 0, 'F', '0', '0', 'system:config:export', '#', 'admin', now(), '', null, '');
-- 通知公告按钮
insert into sys_menu
values ('1036', '公告查询', '107', '1', '#', '', 1, 0, 'F', '0', '0', 'system:notice:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1037', '公告新增', '107', '2', '#', '', 1, 0, 'F', '0', '0', 'system:notice:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1038', '公告修改', '107', '3', '#', '', 1, 0, 'F', '0', '0', 'system:notice:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1039', '公告删除', '107', '4', '#', '', 1, 0, 'F', '0', '0', 'system:notice:remove', '#', 'admin', now(), '', null, '');
-- 操作日志按钮
insert into sys_menu
values ('1040', '操作查询', '500', '1', '#', '', 1, 0, 'F', '0', '0', 'monitor:operlog:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1041', '操作删除', '500', '2', '#', '', 1, 0, 'F', '0', '0', 'monitor:operlog:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1042', '日志导出', '500', '4', '#', '', 1, 0, 'F', '0', '0', 'monitor:operlog:export', '#', 'admin', now(), '', null, '');
-- 登录日志按钮
insert into sys_menu
values ('1043', '登录查询', '501', '1', '#', '', 1, 0, 'F', '0', '0', 'monitor:logininfor:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1044', '登录删除', '501', '2', '#', '', 1, 0, 'F', '0', '0', 'monitor:logininfor:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1045', '日志导出', '501', '3', '#', '', 1, 0, 'F', '0', '0', 'monitor:logininfor:export', '#', 'admin', now(), '', null, '');
-- 在线用户按钮
insert into sys_menu
values ('1046', '在线查询', '109', '1', '#', '', 1, 0, 'F', '0', '0', 'monitor:online:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1047', '批量强退', '109', '2', '#', '', 1, 0, 'F', '0', '0', 'monitor:online:batchLogout', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1048', '单条强退', '109', '3', '#', '', 1, 0, 'F', '0', '0', 'monitor:online:forceLogout', '#', 'admin', now(), '', null, '');
-- 定时任务按钮
insert into sys_menu
values ('1049', '任务查询', '110', '1', '#', '', 1, 0, 'F', '0', '0', 'monitor:job:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1050', '任务新增', '110', '2', '#', '', 1, 0, 'F', '0', '0', 'monitor:job:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1051', '任务修改', '110', '3', '#', '', 1, 0, 'F', '0', '0', 'monitor:job:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1052', '任务删除', '110', '4', '#', '', 1, 0, 'F', '0', '0', 'monitor:job:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1053', '状态修改', '110', '5', '#', '', 1, 0, 'F', '0', '0', 'monitor:job:changeStatus', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1054', '任务导出', '110', '7', '#', '', 1, 0, 'F', '0', '0', 'monitor:job:export', '#', 'admin', now(), '', null, '');
-- 代码生成按钮
insert into sys_menu
values ('1055', '生成查询', '115', '1', '#', '', 1, 0, 'F', '0', '0', 'tool:gen:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1056', '生成修改', '115', '2', '#', '', 1, 0, 'F', '0', '0', 'tool:gen:edit', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1057', '生成删除', '115', '3', '#', '', 1, 0, 'F', '0', '0', 'tool:gen:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1058', '导入代码', '115', '2', '#', '', 1, 0, 'F', '0', '0', 'tool:gen:import', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1059', '预览代码', '115', '4', '#', '', 1, 0, 'F', '0', '0', 'tool:gen:preview', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1060', '生成代码', '115', '5', '#', '', 1, 0, 'F', '0', '0', 'tool:gen:code', '#', 'admin', now(), '', null, '');


-- ----------------------------
-- 6、用户和角色关联表  用户N-1角色
-- ----------------------------
drop table if exists sys_user_role;
create table sys_user_role
(
    user_id int8 not null,
    role_id int8 not null,
    CONSTRAINT sys_user_role_pkey primary key (user_id, role_id)
);
COMMENT
ON COLUMN sys_user_role.user_id IS '用户ID';
COMMENT
ON COLUMN sys_user_role.role_id IS '角色ID';
COMMENT
ON TABLE sys_user_role IS '用户和角色关联表';

-- ----------------------------
-- 初始化-用户和角色关联表数据
-- ----------------------------
insert into sys_user_role
values ('1', '1');


-- ----------------------------
-- 7、角色和菜单关联表  角色1-N菜单
-- ----------------------------
drop table if exists sys_role_menu;
create table sys_role_menu
(
    role_id int8 not null,
    menu_id int8 not null,
    CONSTRAINT sys_role_menu_pkey primary key (role_id, menu_id)
);
COMMENT
ON COLUMN sys_role_menu.role_id IS '角色ID';
COMMENT
ON COLUMN sys_role_menu.menu_id IS '菜单ID';
COMMENT
ON TABLE sys_role_menu IS '角色和菜单关联表';

-- ----------------------------
-- 8、角色和部门关联表  角色1-N部门
-- ----------------------------
drop table if exists sys_role_dept;
create table sys_role_dept
(
    role_id int8 not null,
    dept_id int8 not null,
    CONSTRAINT sys_role_dept_pkey primary key (role_id, dept_id)
);
COMMENT
ON COLUMN sys_role_dept.role_id IS '角色ID';
COMMENT
ON COLUMN sys_role_dept.dept_id IS '部门ID';
COMMENT
ON TABLE sys_role_dept IS '角色和部门关联表';



-- ----------------------------
-- 9、用户与岗位关联表  用户1-N岗位
-- ----------------------------
drop table if exists sys_user_post;
create table sys_user_post
(
    user_id int8 not null,
    post_id int8 not null,
    CONSTRAINT sys_user_post_pkey primary key (user_id, post_id)
);
COMMENT
ON COLUMN sys_user_post.user_id IS '用户ID';
COMMENT
ON COLUMN sys_user_post.post_id IS '岗位ID';
COMMENT
ON TABLE sys_user_post IS '用户与岗位关联表';

-- ----------------------------
-- 初始化-用户与岗位关联表数据
-- ----------------------------
insert into sys_user_post
values ('1', '1');


-- ----------------------------
-- 10、操作日志记录
-- ----------------------------
drop table if exists sys_oper_log;
create table sys_oper_log
(
    oper_id        BIGSERIAL not null,
    title          varchar(50)   default '',
    business_type  int4          default 0,
    method         varchar(100)  default '',
    request_method varchar(10)   default '',
    operator_type  int4          default 0,
    oper_name      varchar(50)   default '',
    dept_name      varchar(50)   default '',
    oper_url       varchar(255)  default '',
    oper_ip        varchar(128)  default '',
    oper_location  varchar(255)  default '',
    oper_param     varchar(2000) default '',
    json_result    varchar(2000) default '',
    status         char(1)       default 0,
    error_msg      varchar(2000) default '',
    oper_time      timestamp(0),
    CONSTRAINT sys_oper_log_pkey primary key (oper_id)
);
COMMENT
ON COLUMN sys_oper_log.oper_id IS '日志主键';
COMMENT
ON COLUMN sys_oper_log.title IS '模块标题';
COMMENT
ON COLUMN sys_oper_log.business_type IS '业务类型（0其它 1新增 2修改 3删除）';
COMMENT
ON COLUMN sys_oper_log.method IS '方法名称';
COMMENT
ON COLUMN sys_oper_log.request_method IS '请求方式';
COMMENT
ON COLUMN sys_oper_log.operator_type IS '操作类别（0其它 1后台用户 2手机端用户）';
COMMENT
ON COLUMN sys_oper_log.oper_name IS '操作人员';
COMMENT
ON COLUMN sys_oper_log.dept_name IS '部门名称';
COMMENT
ON COLUMN sys_oper_log.oper_url IS '请求URL';
COMMENT
ON COLUMN sys_oper_log.oper_ip IS '主机地址';
COMMENT
ON COLUMN sys_oper_log.oper_location IS '操作地点';
COMMENT
ON COLUMN sys_oper_log.oper_param IS '请求参数';
COMMENT
ON COLUMN sys_oper_log.json_result IS '返回参数';
COMMENT
ON COLUMN sys_oper_log.status IS '操作状态（0正常 1异常）';
COMMENT
ON COLUMN sys_oper_log.error_msg IS '错误消息';
COMMENT
ON COLUMN sys_oper_log.oper_time IS '操作时间';
COMMENT
ON TABLE sys_oper_log IS '操作日志记录';


-- ----------------------------
-- 11、字典类型表
-- ----------------------------
drop table if exists sys_dict_type;
create table sys_dict_type
(
    dict_id     BIGSERIAL not null,
    dict_name   varchar(100) default '',
    dict_type   varchar(100) default '' unique,
    status      char(1)      default '0',
    create_by   varchar(64)  default '',
    create_time timestamp(0),
    update_by   varchar(64)  default '',
    update_time timestamp(0),
    remark      varchar(500) default null,
    CONSTRAINT sys_dict_type_pkey primary key (dict_id)
);
COMMENT
ON COLUMN sys_dict_type.dict_id IS '字典主键';
COMMENT
ON COLUMN sys_dict_type.dict_name IS '字典名称';
COMMENT
ON COLUMN sys_dict_type.dict_type IS '字典类型';
COMMENT
ON COLUMN sys_dict_type.status IS '状态（0正常 1停用）';
COMMENT
ON COLUMN sys_dict_type.create_by IS '创建者';
COMMENT
ON COLUMN sys_dict_type.create_time IS '创建时间';
COMMENT
ON COLUMN sys_dict_type.update_by IS '更新者';
COMMENT
ON COLUMN sys_dict_type.update_time IS '更新时间';
COMMENT
ON COLUMN sys_dict_type.remark IS '备注';
COMMENT
ON TABLE sys_dict_type IS '字典类型表';

insert into sys_dict_type
values (1, '用户性别', 'sys_user_sex', '0', 'admin', now(), '', null, '用户性别列表');
insert into sys_dict_type
values (2, '菜单状态', 'sys_show_hide', '0', 'admin', now(), '', null, '菜单状态列表');
insert into sys_dict_type
values (3, '系统开关', 'sys_normal_disable', '0', 'admin', now(), '', null, '系统开关列表');
insert into sys_dict_type
values (4, '任务状态', 'sys_job_status', '0', 'admin', now(), '', null, '任务状态列表');
insert into sys_dict_type
values (5, '任务分组', 'sys_job_group', '0', 'admin', now(), '', null, '任务分组列表');
insert into sys_dict_type
values (6, '系统是否', 'sys_yes_no', '0', 'admin', now(), '', null, '系统是否列表');
insert into sys_dict_type
values (7, '通知类型', 'sys_notice_type', '0', 'admin', now(), '', null, '通知类型列表');
insert into sys_dict_type
values (8, '通知状态', 'sys_notice_status', '0', 'admin', now(), '', null, '通知状态列表');
insert into sys_dict_type
values (9, '操作类型', 'sys_oper_type', '0', 'admin', now(), '', null, '操作类型列表');
insert into sys_dict_type
values (10, '系统状态', 'sys_common_status', '0', 'admin', now(), '', null, '登录状态列表');


-- ----------------------------
-- 12、字典数据表
-- ----------------------------
drop table if exists sys_dict_data;
create table sys_dict_data
(
    dict_code   BIGSERIAL not null,
    dict_sort   int4         default 0,
    dict_label  varchar(100) default '',
    dict_value  varchar(100) default '',
    dict_type   varchar(100) default '',
    css_class   varchar(100) default null,
    list_class  varchar(100) default null,
    is_default  char(1)      default 'N',
    status      char(1)      default '0',
    create_by   varchar(64)  default '',
    create_time timestamp(0),
    update_by   varchar(64)  default '',
    update_time timestamp(0),
    remark      varchar(500) default null,
    CONSTRAINT sys_dict_data_pkey primary key (dict_code)
);
COMMENT
ON COLUMN sys_dict_data.dict_code IS '字典编码';
COMMENT
ON COLUMN sys_dict_data.dict_sort IS '字典排序';
COMMENT
ON COLUMN sys_dict_data.dict_label IS '字典标签';
COMMENT
ON COLUMN sys_dict_data.dict_value IS '字典键值';
COMMENT
ON COLUMN sys_dict_data.dict_type IS '字典类型';
COMMENT
ON COLUMN sys_dict_data.css_class IS '样式属性（其他样式扩展）';
COMMENT
ON COLUMN sys_dict_data.list_class IS '表格回显样式';
COMMENT
ON COLUMN sys_dict_data.is_DEFAULT IS '是否默认（Y是 N否）';
COMMENT
ON COLUMN sys_dict_data.status IS '状态（0正常 1停用）';
COMMENT
ON COLUMN sys_dict_data.create_by IS '创建者';
COMMENT
ON COLUMN sys_dict_data.create_time IS '创建时间';
COMMENT
ON COLUMN sys_dict_data.update_by IS '更新者';
COMMENT
ON COLUMN sys_dict_data.update_time IS '更新时间';
COMMENT
ON COLUMN sys_dict_data.remark IS '备注';
COMMENT
ON TABLE sys_dict_data IS '字典数据表';

insert into sys_dict_data
values (1, 1, '男', '0', 'sys_user_sex', '', '', 'Y', '0', 'admin', now(), '', null, '性别男');
insert into sys_dict_data
values (2, 2, '女', '1', 'sys_user_sex', '', '', 'N', '0', 'admin', now(), '', null, '性别女');
insert into sys_dict_data
values (3, 3, '未知', '2', 'sys_user_sex', '', '', 'N', '0', 'admin', now(), '', null, '性别未知');
insert into sys_dict_data
values (4, 1, '显示', '0', 'sys_show_hide', '', 'primary', 'Y', '0', 'admin', now(), '', null, '显示菜单');
insert into sys_dict_data
values (5, 2, '隐藏', '1', 'sys_show_hide', '', 'danger', 'N', '0', 'admin', now(), '', null, '隐藏菜单');
insert into sys_dict_data
values (6, 1, '正常', '0', 'sys_normal_disable', '', 'primary', 'Y', '0', 'admin', now(), '', null, '正常状态');
insert into sys_dict_data
values (7, 2, '停用', '1', 'sys_normal_disable', '', 'danger', 'N', '0', 'admin', now(), '', null, '停用状态');
insert into sys_dict_data
values (8, 1, '正常', '0', 'sys_job_status', '', 'primary', 'Y', '0', 'admin', now(), '', null, '正常状态');
insert into sys_dict_data
values (9, 2, '暂停', '1', 'sys_job_status', '', 'danger', 'N', '0', 'admin', now(), '', null, '停用状态');
insert into sys_dict_data
values (10, 1, '默认', 'DEFAULT', 'sys_job_group', '', '', 'Y', '0', 'admin', now(), '', null, '默认分组');
insert into sys_dict_data
values (11, 2, '系统', 'SYSTEM', 'sys_job_group', '', '', 'N', '0', 'admin', now(), '', null, '系统分组');
insert into sys_dict_data
values (12, 1, '是', 'Y', 'sys_yes_no', '', 'primary', 'Y', '0', 'admin', now(), '', null, '系统默认是');
insert into sys_dict_data
values (13, 2, '否', 'N', 'sys_yes_no', '', 'danger', 'N', '0', 'admin', now(), '', null, '系统默认否');
insert into sys_dict_data
values (14, 1, '通知', '1', 'sys_notice_type', '', 'warning', 'Y', '0', 'admin', now(), '', null, '通知');
insert into sys_dict_data
values (15, 2, '公告', '2', 'sys_notice_type', '', 'success', 'N', '0', 'admin', now(), '', null, '公告');
insert into sys_dict_data
values (16, 1, '正常', '0', 'sys_notice_status', '', 'primary', 'Y', '0', 'admin', now(), '', null, '正常状态');
insert into sys_dict_data
values (17, 2, '关闭', '1', 'sys_notice_status', '', 'danger', 'N', '0', 'admin', now(), '', null, '关闭状态');
insert into sys_dict_data
values (18, 1, '新增', '1', 'sys_oper_type', '', 'info', 'N', '0', 'admin', now(), '', null, '新增操作');
insert into sys_dict_data
values (19, 2, '修改', '2', 'sys_oper_type', '', 'info', 'N', '0', 'admin', now(), '', null, '修改操作');
insert into sys_dict_data
values (20, 3, '删除', '3', 'sys_oper_type', '', 'danger', 'N', '0', 'admin', now(), '', null, '删除操作');
insert into sys_dict_data
values (21, 4, '授权', '4', 'sys_oper_type', '', 'primary', 'N', '0', 'admin', now(), '', null, '授权操作');
insert into sys_dict_data
values (22, 5, '导出', '5', 'sys_oper_type', '', 'warning', 'N', '0', 'admin', now(), '', null, '导出操作');
insert into sys_dict_data
values (23, 6, '导入', '6', 'sys_oper_type', '', 'warning', 'N', '0', 'admin', now(), '', null, '导入操作');
insert into sys_dict_data
values (24, 7, '强退', '7', 'sys_oper_type', '', 'danger', 'N', '0', 'admin', now(), '', null, '强退操作');
insert into sys_dict_data
values (25, 8, '生成代码', '8', 'sys_oper_type', '', 'warning', 'N', '0', 'admin', now(), '', null, '生成操作');
insert into sys_dict_data
values (26, 9, '清空数据', '9', 'sys_oper_type', '', 'danger', 'N', '0', 'admin', now(), '', null, '清空操作');
insert into sys_dict_data
values (27, 1, '成功', '0', 'sys_common_status', '', 'primary', 'N', '0', 'admin', now(), '', null, '正常状态');
insert into sys_dict_data
values (28, 2, '失败', '1', 'sys_common_status', '', 'danger', 'N', '0', 'admin', now(), '', null, '停用状态');


-- ----------------------------
-- 13、参数配置表
-- ----------------------------
drop table if exists sys_config;
create table sys_config
(
    config_id    BIGSERIAL not null,
    config_name  varchar(100) default '',
    config_key   varchar(100) default '',
    config_value varchar(500) default '',
    config_type  char(1)      default 'N',
    create_by    varchar(64)  default '',
    create_time  timestamp(0),
    update_by    varchar(64)  default '',
    update_time  timestamp(0),
    remark       varchar(500) default null,
    CONSTRAINT sys_config_pkey primary key (config_id)
);
COMMENT
ON COLUMN sys_config.config_id IS '参数主键';
COMMENT
ON COLUMN sys_config.config_name IS '参数名称';
COMMENT
ON COLUMN sys_config.config_key IS '参数键名';
COMMENT
ON COLUMN sys_config.config_value IS '参数键值';
COMMENT
ON COLUMN sys_config.config_type IS '系统内置（Y是 N否）';
COMMENT
ON COLUMN sys_config.create_by IS '创建者';
COMMENT
ON COLUMN sys_config.create_time IS '创建时间';
COMMENT
ON COLUMN sys_config.update_by IS '更新者';
COMMENT
ON COLUMN sys_config.update_time IS '更新时间';
COMMENT
ON COLUMN sys_config.remark IS '备注';
COMMENT
ON TABLE sys_config IS '参数配置表';

insert into sys_config
values (1, '主框架页-默认皮肤样式名称', 'sys.index.skinName', 'skin-blue', 'Y', 'admin', now(), '', null, '蓝色 skin-blue、绿色 skin-green、紫色 skin-purple、红色 skin-red、黄色 skin-yellow');
insert into sys_config
values (2, '用户管理-账号初始密码', 'sys.user.initPassword', '123456', 'Y', 'admin', now(), '', null, '初始化密码 123456');
insert into sys_config
values (3, '主框架页-侧边栏主题', 'sys.index.sideTheme', 'theme-dark', 'Y', 'admin', now(), '', null, '深色主题theme-dark，浅色主题theme-light');
insert into sys_config
values (4, '账号自助-验证码开关', 'sys.account.captchaOnOff', 'true', 'Y', 'admin', now(), '', null, '是否开启验证码功能（true开启，false关闭）');
insert into sys_config
values (5, '账号自助-是否开启用户注册功能', 'sys.account.registerUser', 'false', 'Y', 'admin', now(), '', null, '是否开启注册用户功能（true开启，false关闭）');


-- ----------------------------
-- 14、系统访问记录
-- ----------------------------
drop table if exists sys_logininfor;
create table sys_logininfor
(
    info_id        BIGSERIAL not null,
    user_name      varchar(50)  default '',
    ipaddr         varchar(128) default '',
    login_location varchar(255) default '',
    browser        varchar(50)  default '',
    os             varchar(50)  default '',
    status         char(1)      default '0',
    msg            varchar(255) default '',
    login_time     timestamp(0),
    CONSTRAINT sys_logininfor_pkey primary key (info_id)
);
COMMENT
ON COLUMN sys_logininfor.info_id IS '访问ID';
COMMENT
ON COLUMN sys_logininfor.user_name IS '用户账号';
COMMENT
ON COLUMN sys_logininfor.ipaddr IS '登录IP地址';
COMMENT
ON COLUMN sys_logininfor.login_location IS '登录地点';
COMMENT
ON COLUMN sys_logininfor.browser IS '浏览器类型';
COMMENT
ON COLUMN sys_logininfor.os IS '操作系统';
COMMENT
ON COLUMN sys_logininfor.status IS '登录状态（0成功 1失败）';
COMMENT
ON COLUMN sys_logininfor.msg IS '提示消息';
COMMENT
ON COLUMN sys_logininfor.login_time IS '访问时间';
COMMENT
ON TABLE sys_logininfor IS '系统访问记录';

-- ----------------------------
-- 15、定时任务调度表
-- ----------------------------
drop table if exists sys_job;
create table sys_job
(
    job_id          BIGSERIAL    not null,
    job_name        varchar(64)  default '',
    job_group       varchar(64)  default 'DEFAULT',
    invoke_target   varchar(500) not null,
    cron_expression varchar(255) default '',
    misfire_policy  varchar(20)  default '3',
    concurrent      char(1)      default '1',
    status          char(1)      default '0',
    create_by       varchar(64)  default '',
    create_time     timestamp(0),
    update_by       varchar(64)  default '',
    update_time     timestamp(0),
    remark          varchar(500) default '',
    CONSTRAINT sys_job_pkey primary key (job_id, job_name, job_group)
);
COMMENT
ON COLUMN sys_job.job_id IS '任务ID';
COMMENT
ON COLUMN sys_job.job_name IS '任务名称';
COMMENT
ON COLUMN sys_job.job_group IS '任务组名';
COMMENT
ON COLUMN sys_job.invoke_target IS '调用目标字符串';
COMMENT
ON COLUMN sys_job.cron_expression IS 'cron执行表达式';
COMMENT
ON COLUMN sys_job.misfire_policy IS '计划执行错误策略（1立即执行 2执行一次 3放弃执行）';
COMMENT
ON COLUMN sys_job.concurrent IS '是否并发执行（0允许 1禁止）';
COMMENT
ON COLUMN sys_job.status IS '状态（0正常 1暂停）';
COMMENT
ON COLUMN sys_job.create_by IS '创建者';
COMMENT
ON COLUMN sys_job.create_time IS '创建时间';
COMMENT
ON COLUMN sys_job.update_by IS '更新者';
COMMENT
ON COLUMN sys_job.update_time IS '更新时间';
COMMENT
ON COLUMN sys_job.remark IS '备注信息';
COMMENT
ON TABLE sys_job IS '定时任务调度表';

-- ----------------------------
-- 16、定时任务调度日志表
-- ----------------------------
drop table if exists sys_job_log;
create table sys_job_log
(
    job_log_id     BIGSERIAL    not null,
    job_name       varchar(64)  not null,
    job_group      varchar(64)  not null,
    invoke_target  varchar(500) not null,
    job_message    varchar(500),
    status         char(1)       default '0',
    exception_info varchar(2000) default '',
    create_time    timestamp(0),
    CONSTRAINT sys_job_log_pkey primary key (job_log_id)
);
COMMENT
ON COLUMN sys_job_log.job_log_id IS '任务日志ID';
COMMENT
ON COLUMN sys_job_log.job_name IS '任务名称';
COMMENT
ON COLUMN sys_job_log.job_group IS '任务组名';
COMMENT
ON COLUMN sys_job_log.invoke_target IS '调用目标字符串';
COMMENT
ON COLUMN sys_job_log.job_message IS '日志信息';
COMMENT
ON COLUMN sys_job_log.status IS '执行状态（0正常 1失败）';
COMMENT
ON COLUMN sys_job_log.exception_info IS '异常信息';
COMMENT
ON COLUMN sys_job_log.create_time IS '创建时间';
COMMENT
ON TABLE sys_job_log IS '定时任务调度日志表';


-- ----------------------------
-- 17、通知公告表
-- ----------------------------
drop table if exists sys_notice;
create table sys_notice
(
    notice_id      BIGSERIAL   not null,
    notice_title   varchar(50) not null,
    notice_type    char(1)     not null,
    notice_content text         default null,
    status         char(1)      default '0',
    create_by      varchar(64)  default '',
    create_time    timestamp(0),
    update_by      varchar(64)  default '',
    update_time    timestamp(0),
    remark         varchar(255) default null,
    CONSTRAINT sys_notice_pkey primary key (notice_id)
);
COMMENT
ON COLUMN sys_notice.notice_id IS '公告ID';
COMMENT
ON COLUMN sys_notice.notice_title IS '公告标题';
COMMENT
ON COLUMN sys_notice.notice_type IS '公告类型（1通知 2公告）';
COMMENT
ON COLUMN sys_notice.notice_content IS '公告内容';
COMMENT
ON COLUMN sys_notice.status IS '公告状态（0正常 1关闭）';
COMMENT
ON COLUMN sys_notice.create_by IS '创建者';
COMMENT
ON COLUMN sys_notice.create_time IS '创建时间';
COMMENT
ON COLUMN sys_notice.update_by IS '更新者';
COMMENT
ON COLUMN sys_notice.update_time IS '更新时间';
COMMENT
ON COLUMN sys_notice.remark IS '备注';
COMMENT
ON TABLE sys_notice IS '通知公告表';



-- ----------------------------
-- 18、代码生成业务表
-- ----------------------------
drop table if exists gen_table;
create table gen_table
(
    table_id          BIGSERIAL not null,
    table_name        varchar(200) default '',
    table_comment     varchar(500) default '',
    sub_table_name    varchar(64)  default null,
    sub_table_fk_name varchar(64)  default null,
    class_name        varchar(100) default '',
    tpl_category      varchar(200) default 'crud',
    package_name      varchar(100),
    module_name       varchar(30),
    business_name     varchar(30),
    function_name     varchar(50),
    function_author   varchar(50),
    gen_type          char(1)      default '0',
    gen_path          varchar(200) default '/',
    options           varchar(1000),
    create_by         varchar(64)  default '',
    create_time       timestamp(0),
    update_by         varchar(64)  default '',
    update_time       timestamp(0),
    remark            varchar(500) default null,
    CONSTRAINT gen_table_pkey primary key (table_id)
);
COMMENT
ON COLUMN gen_table.table_id IS '编号';
COMMENT
ON COLUMN gen_table.table_name IS '表名称';
COMMENT
ON COLUMN gen_table.table_comment IS '表描述';
COMMENT
ON COLUMN gen_table.sub_table_name IS '关联子表的表名';
COMMENT
ON COLUMN gen_table.sub_table_fk_name IS '子表关联的外键名';
COMMENT
ON COLUMN gen_table.class_name IS '实体类名称';
COMMENT
ON COLUMN gen_table.tpl_category IS '使用的模板（crud单表操作 tree树表操作）';
COMMENT
ON COLUMN gen_table.package_name IS '生成包路径';
COMMENT
ON COLUMN gen_table.module_name IS '生成模块名';
COMMENT
ON COLUMN gen_table.business_name IS '生成业务名';
COMMENT
ON COLUMN gen_table.function_name IS '生成功能名';
COMMENT
ON COLUMN gen_table.function_author IS '生成功能作者';
COMMENT
ON COLUMN gen_table.gen_type IS '生成代码方式（0zip压缩包 1自定义路径）';
COMMENT
ON COLUMN gen_table.gen_path IS '生成路径（不填默认项目路径）';
COMMENT
ON COLUMN gen_table.options IS '其它生成选项';
COMMENT
ON COLUMN gen_table.create_by IS '创建者';
COMMENT
ON COLUMN gen_table.create_time IS '创建时间';
COMMENT
ON COLUMN gen_table.update_by IS '更新者';
COMMENT
ON COLUMN gen_table.update_time IS '更新时间';
COMMENT
ON COLUMN gen_table.remark IS '备注';
COMMENT
ON TABLE gen_table IS '代码生成业务表';


-- ----------------------------
-- 19、代码生成业务表字段
-- ----------------------------
drop table if exists gen_table_column;
create table gen_table_column
(
    column_id      BIGSERIAL not null,
    table_id       int8,
    column_name    varchar(200),
    column_comment varchar(500),
    column_type    varchar(100),
    java_type      varchar(500),
    java_field     varchar(200),
    is_pk          char(1),
    is_increment   char(1),
    is_required    char(1),
    is_insert      char(1),
    is_edit        char(1),
    is_list        char(1),
    is_query       char(1),
    query_type     varchar(200) default 'EQ',
    html_type      varchar(200),
    dict_type      varchar(200) default '',
    sort           int4,
    create_by      varchar(64)  default '',
    create_time    timestamp(0),
    update_by      varchar(64)  default '',
    update_time    timestamp(0),
    CONSTRAINT gen_table_column_pkey primary key (column_id)
);
COMMENT
ON COLUMN gen_table_column.column_id IS '编号';
COMMENT
ON COLUMN gen_table_column.table_id IS '归属表编号';
COMMENT
ON COLUMN gen_table_column.column_name IS '列名称';
COMMENT
ON COLUMN gen_table_column.column_comment IS '列描述';
COMMENT
ON COLUMN gen_table_column.column_type IS '列类型';
COMMENT
ON COLUMN gen_table_column.java_type IS 'JAVA类型';
COMMENT
ON COLUMN gen_table_column.java_field IS 'JAVA字段名';
COMMENT
ON COLUMN gen_table_column.is_pk IS '是否主键（1是）';
COMMENT
ON COLUMN gen_table_column.is_increment IS '是否自增（1是）';
COMMENT
ON COLUMN gen_table_column.is_required IS '是否必填（1是）';
COMMENT
ON COLUMN gen_table_column.is_insert IS '是否为插入字段（1是）';
COMMENT
ON COLUMN gen_table_column.is_edit IS '是否编辑字段（1是）';
COMMENT
ON COLUMN gen_table_column.is_list IS '是否列表字段（1是）';
COMMENT
ON COLUMN gen_table_column.is_query IS '是否查询字段（1是）';
COMMENT
ON COLUMN gen_table_column.query_type IS '查询方式（等于、不等于、大于、小于、范围）';
COMMENT
ON COLUMN gen_table_column.html_type IS '显示类型（文本框、文本域、下拉框、复选框、单选框、日期控件）';
COMMENT
ON COLUMN gen_table_column.dict_type IS '字典类型';
COMMENT
ON COLUMN gen_table_column.sort IS '排序';
COMMENT
ON COLUMN gen_table_column.create_by IS '创建者';
COMMENT
ON COLUMN gen_table_column.create_time IS '创建时间';
COMMENT
ON COLUMN gen_table_column.update_by IS '更新者';
COMMENT
ON COLUMN gen_table_column.update_time IS '更新时间';
COMMENT
ON TABLE gen_table_column IS '代码生成业务表字段';


-- ----------------------------
-- Function structure for find_in_set
-- ----------------------------
CREATE
OR REPLACE FUNCTION find_in_set(BIGINT, VARCHAR)
    RETURNS BOOLEAN
    AS $BODY$
DECLARE
STR ALIAS FOR $1;
    STRS
ALIAS FOR $2;
    POS
INTEGER;
    STATUS
BOOLEAN;
BEGIN
SELECT POSITION(',' || STR || ',' IN ',' || STRS || ',')
INTO POS;
IF
POS > 0 THEN STATUS = TRUE;
ELSE STATUS = FALSE;
END IF;
RETURN STATUS;
END;
$BODY$
LANGUAGE PLPGSQL;



-- ----------------------------
-- 用来处理 代码生成的视图  仿 mysql的information_schema.tables 和 information_schema.columns
-- ----------------------------

CREATE
OR REPLACE view view_self_table
as
select datname                                                   as table_catalog,
       pg_get_userbyid(relowner)                                 AS tableowner,
       nspname                                                   as table_schema,
       relname                                                   as table_name,
       cast(obj_description(relfilenode, 'pg_class') as varchar) as table_comment,
       now()                                                        create_time,
       now()                                                        update_time
from pg_class c
         left join pg_namespace pg_ns on
    pg_ns."oid" = c.relnamespace
         left join pg_database on relowner = datdba
where relname in (select tablename from pg_tables);


CREATE
OR REPLACE view view_self_table_columns
as
select table_catalog,
       table_schema,
       table_name,
       ordinal_position                                          as sort,
       column_name,
       data_type                                                 as TypeName,
       (case
            when (is_nullable = 'no' and contype != 'p') then '1'
            else null
           end)                                                  as is_required,
       (case
            when contype = 'p' then '1'
            else '0'
           end)                                                  as is_pk,
       coalesce(character_maximum_length, numeric_precision, -1) as Length,
       numeric_scale                                             as scale,
       case
           is_nullable
           when 'NO' then 0
           else 1
           end                                                   as canNull,
       column_DEFAULT                                            as DEFAULTval,
       case
           when position('nextval' in column_DEFAULT) > 0 then 1
           else 0
           end                                                   as IsIdentity,
       (case
            when position('nextval' in column_DEFAULT) > 0 then 1
            else 0
           end)                                                  as is_increment,
       c.DeText                                                  as column_comment,
       c.typname                                                 as column_type,
       c.contype,
       ordinal_position
from information_schema.columns
         left join (select datname,
                           pg_get_userbyid(relowner) AS tableowner,
                           nspname,
                           relname,
                           attname,
                           description               as DeText,
                           typname,
                           pg_cons.contype
                    from pg_class
                             left join pg_attribute pg_attr on
                        pg_attr.attrelid = pg_class.oid
                             left join pg_description pg_desc on
                            pg_desc.objoid = pg_attr.attrelid
                            and pg_desc.objsubid = pg_attr.attnum
                             left join pg_namespace pg_ns on
                        pg_ns."oid" = pg_class.relnamespace
                             left join pg_database on relowner = datdba
                             left join pg_type on pg_attr.atttypid = pg_type."oid"
                             left join (select pg_con.*, unnest(conkey) conkey_new from pg_constraint pg_con) pg_cons on
                            pg_attr.attrelid = pg_class.oid
                            and pg_attr.attnum = pg_cons.conkey_new and pg_cons.conrelid = pg_class.oid
                    where pg_attr.attnum > 0
                      and pg_attr.attrelid = pg_class.oid
) c
                   on table_catalog = datname and table_schema = nspname and table_name = relname and
                      column_name = attname;


CREATE
INDEX dict_type ON sys_dict_type USING btree (
  dict_type  pg_catalog.text_ops ASC NULLS LAST
);


-- ----------------------------
-- 1、存储每一个已配置的 jobDetail 的详细信息
-- ----------------------------
drop table if exists qrtz_job_details CASCADE;
CREATE TABLE qrtz_job_details
(
    SCHED_NAME        VARCHAR(120) NOT NULL,
    JOB_NAME          VARCHAR(200) NOT NULL,
    JOB_GROUP         VARCHAR(200) NOT NULL,
    DESCRIPTION       VARCHAR(250) NULL,
    JOB_CLASS_NAME    VARCHAR(250) NOT NULL,
    IS_DURABLE        BOOL         NOT NULL,
    IS_NONCONCURRENT  BOOL         NOT NULL,
    IS_UPDATE_DATA    BOOL         NOT NULL,
    REQUESTS_RECOVERY BOOL         NOT NULL,
    JOB_DATA          BYTEA NULL,
    PRIMARY KEY (SCHED_NAME, JOB_NAME, JOB_GROUP)
);

-- ----------------------------
-- 2、 存储已配置的 Trigger 的信息
-- ----------------------------
drop table if exists qrtz_triggers CASCADE;
CREATE TABLE qrtz_triggers
(
    SCHED_NAME     VARCHAR(120) NOT NULL,
    TRIGGER_NAME   VARCHAR(200) NOT NULL,
    TRIGGER_GROUP  VARCHAR(200) NOT NULL,
    JOB_NAME       VARCHAR(200) NOT NULL,
    JOB_GROUP      VARCHAR(200) NOT NULL,
    DESCRIPTION    VARCHAR(250) NULL,
    NEXT_FIRE_TIME BIGINT NULL,
    PREV_FIRE_TIME BIGINT NULL,
    PRIORITY       INTEGER NULL,
    TRIGGER_STATE  VARCHAR(16)  NOT NULL,
    TRIGGER_TYPE   VARCHAR(8)   NOT NULL,
    START_TIME     BIGINT       NOT NULL,
    END_TIME       BIGINT NULL,
    CALENDAR_NAME  VARCHAR(200) NULL,
    MISFIRE_INSTR  SMALLINT NULL,
    JOB_DATA       BYTEA NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, JOB_NAME, JOB_GROUP)
        REFERENCES QRTZ_JOB_DETAILS (SCHED_NAME, JOB_NAME, JOB_GROUP)
);
-- ----------------------------
-- 3、 存储简单的 Trigger，包括重复次数，间隔，以及已触发的次数
-- ----------------------------
drop table if exists qrtz_simple_triggers CASCADE;
CREATE TABLE qrtz_simple_triggers
(
    SCHED_NAME      VARCHAR(120) NOT NULL,
    TRIGGER_NAME    VARCHAR(200) NOT NULL,
    TRIGGER_GROUP   VARCHAR(200) NOT NULL,
    REPEAT_COUNT    BIGINT       NOT NULL,
    REPEAT_INTERVAL BIGINT       NOT NULL,
    TIMES_TRIGGERED BIGINT       NOT NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);
-- ----------------------------
-- 4、 存储 Cron Trigger，包括 Cron 表达式和时区信息
-- ----------------------------
drop table if exists qrtz_cron_triggers CASCADE;
CREATE TABLE qrtz_cron_triggers
(
    SCHED_NAME      VARCHAR(120) NOT NULL,
    TRIGGER_NAME    VARCHAR(200) NOT NULL,
    TRIGGER_GROUP   VARCHAR(200) NOT NULL,
    CRON_EXPRESSION VARCHAR(120) NOT NULL,
    TIME_ZONE_ID    VARCHAR(80),
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);

-- ----------------------------
-- 5、 Trigger 作为 Blob 类型存储(用于 Quartz 用户用 JDBC 创建他们自己定制的 Trigger 类型，JobStore 并不知道如何存储实例的时候)
-- ----------------------------
drop table if exists qrtz_blob_triggers CASCADE;
CREATE TABLE qrtz_blob_triggers
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    TRIGGER_NAME  VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    BLOB_DATA     BYTEA NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);

-- ----------------------------
-- 6、 以 Blob 类型存储存放日历信息， quartz可配置一个日历来指定一个时间范围
-- ----------------------------
drop table if exists qrtz_calendars CASCADE;
CREATE TABLE qrtz_calendars
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    CALENDAR_NAME VARCHAR(200) NOT NULL,
    CALENDAR      BYTEA        NOT NULL,
    PRIMARY KEY (SCHED_NAME, CALENDAR_NAME)
);
-- ----------------------------
-- 7、 存储已暂停的 Trigger 组的信息
-- ----------------------------
drop table if exists qrtz_paused_trigger_grps CASCADE;
CREATE TABLE qrtz_paused_trigger_grps
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_GROUP)
);

-- ----------------------------
-- 8、 存储与已触发的 Trigger 相关的状态信息，以及相联 Job 的执行信息
-- ----------------------------
drop table if exists qrtz_fired_triggers CASCADE;
CREATE TABLE qrtz_fired_triggers
(
    SCHED_NAME        VARCHAR(120) NOT NULL,
    ENTRY_ID          VARCHAR(95)  NOT NULL,
    TRIGGER_NAME      VARCHAR(200) NOT NULL,
    TRIGGER_GROUP     VARCHAR(200) NOT NULL,
    INSTANCE_NAME     VARCHAR(200) NOT NULL,
    FIRED_TIME        BIGINT       NOT NULL,
    SCHED_TIME        BIGINT       NOT NULL,
    PRIORITY          INTEGER      NOT NULL,
    STATE             VARCHAR(16)  NOT NULL,
    JOB_NAME          VARCHAR(200) NULL,
    JOB_GROUP         VARCHAR(200) NULL,
    IS_NONCONCURRENT  BOOL NULL,
    REQUESTS_RECOVERY BOOL NULL,
    PRIMARY KEY (SCHED_NAME, ENTRY_ID)
);

-- ----------------------------
-- 9、 存储少量的有关 Scheduler 的状态信息，假如是用于集群中，可以看到其他的 Scheduler 实例
-- ----------------------------
drop table if exists qrtz_scheduler_state CASCADE;
CREATE TABLE qrtz_scheduler_state
(
    SCHED_NAME        VARCHAR(120) NOT NULL,
    INSTANCE_NAME     VARCHAR(200) NOT NULL,
    LAST_CHECKIN_TIME BIGINT       NOT NULL,
    CHECKIN_INTERVAL  BIGINT       NOT NULL,
    PRIMARY KEY (SCHED_NAME, INSTANCE_NAME)
);

-- ----------------------------
-- 10、 存储程序的悲观锁的信息(假如使用了悲观锁)
-- ----------------------------
drop table if exists qrtz_locks CASCADE;
CREATE TABLE qrtz_locks
(
    SCHED_NAME VARCHAR(120) NOT NULL,
    LOCK_NAME  VARCHAR(40)  NOT NULL,
    PRIMARY KEY (SCHED_NAME, LOCK_NAME)
);

drop table if exists qrtz_simprop_triggers CASCADE;
CREATE TABLE qrtz_simprop_triggers
(
    SCHED_NAME    VARCHAR(120) NOT NULL,
    TRIGGER_NAME  VARCHAR(200) NOT NULL,
    TRIGGER_GROUP VARCHAR(200) NOT NULL,
    STR_PROP_1    VARCHAR(512) NULL,
    STR_PROP_2    VARCHAR(512) NULL,
    STR_PROP_3    VARCHAR(512) NULL,
    INT_PROP_1    INT NULL,
    INT_PROP_2    INT NULL,
    LONG_PROP_1   BIGINT NULL,
    LONG_PROP_2   BIGINT NULL,
    DEC_PROP_1    NUMERIC(13, 4) NULL,
    DEC_PROP_2    NUMERIC(13, 4) NULL,
    BOOL_PROP_1   BOOL NULL,
    BOOL_PROP_2   BOOL NULL,
    PRIMARY KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP),
    FOREIGN KEY (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
        REFERENCES QRTZ_TRIGGERS (SCHED_NAME, TRIGGER_NAME, TRIGGER_GROUP)
);

create
index idx_qrtz_j_req_recovery on qrtz_job_details(SCHED_NAME,REQUESTS_RECOVERY);
create
index idx_qrtz_j_grp on qrtz_job_details(SCHED_NAME,JOB_GROUP);
create
index idx_qrtz_t_j on qrtz_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
create
index idx_qrtz_t_jg on qrtz_triggers(SCHED_NAME,JOB_GROUP);
create
index idx_qrtz_t_c on qrtz_triggers(SCHED_NAME,CALENDAR_NAME);
create
index idx_qrtz_t_g on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP);
create
index idx_qrtz_t_state on qrtz_triggers(SCHED_NAME,TRIGGER_STATE);
create
index idx_qrtz_t_n_state on qrtz_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
create
index idx_qrtz_t_n_g_state on qrtz_triggers(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
create
index idx_qrtz_t_next_fire_time on qrtz_triggers(SCHED_NAME,NEXT_FIRE_TIME);
create
index idx_qrtz_t_nft_st on qrtz_triggers(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
create
index idx_qrtz_t_nft_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);
create
index idx_qrtz_t_nft_st_misfire on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
create
index idx_qrtz_t_nft_st_misfire_grp on qrtz_triggers(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);
create
index idx_qrtz_ft_trig_inst_name on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME);
create
index idx_qrtz_ft_inst_job_req_rcvry on qrtz_fired_triggers(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
create
index idx_qrtz_ft_j_g on qrtz_fired_triggers(SCHED_NAME,JOB_NAME,JOB_GROUP);
create
index idx_qrtz_ft_jg on qrtz_fired_triggers(SCHED_NAME,JOB_GROUP);
create
index idx_qrtz_ft_t_g on qrtz_fired_triggers(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
create
index idx_qrtz_ft_tg on qrtz_fired_triggers(SCHED_NAME,TRIGGER_GROUP);

COMMENT
ON COLUMN QRTZ_JOB_DETAILS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.job_name IS '任务名称';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.job_group IS '任务组名';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.description IS '相关介绍';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.job_class_name IS '执行任务类名称';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.is_durable IS '是否持久化';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.is_nonconcurrent IS '是否并发';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.is_update_data IS '是否更新数据';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.requests_recovery IS '是否接受恢复执行';
COMMENT
ON COLUMN QRTZ_JOB_DETAILS.job_data IS '存放持久化job对象';
COMMENT
ON TABLE QRTZ_JOB_DETAILS IS '任务详细信息表';
COMMENT
ON COLUMN QRTZ_TRIGGERS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_TRIGGERS.TRIGGER_NAME IS '触发器的名字';
COMMENT
ON COLUMN QRTZ_TRIGGERS.trigger_group IS '触发器所属组的名字';
COMMENT
ON COLUMN QRTZ_TRIGGERS.job_name IS 'qrtz_job_details表job_name的外键';
COMMENT
ON COLUMN QRTZ_TRIGGERS.job_group IS 'qrtz_job_details表job_group的外键';
COMMENT
ON COLUMN QRTZ_TRIGGERS.description IS '相关介绍';
COMMENT
ON COLUMN QRTZ_TRIGGERS.next_fire_time IS '上一次触发时间（毫秒）';
COMMENT
ON COLUMN QRTZ_TRIGGERS.prev_fire_time IS '下一次触发时间（默认为-1表示不触发）';
COMMENT
ON COLUMN QRTZ_TRIGGERS.priority IS '优先级';
COMMENT
ON COLUMN QRTZ_TRIGGERS.trigger_state IS '触发器状态';
COMMENT
ON COLUMN QRTZ_TRIGGERS.trigger_type IS '触发器的类型';
COMMENT
ON COLUMN QRTZ_TRIGGERS.start_time IS '开始时间';
COMMENT
ON COLUMN QRTZ_TRIGGERS.end_time IS '结束时间';
COMMENT
ON COLUMN QRTZ_TRIGGERS.calendar_name IS '日程表名称';
COMMENT
ON COLUMN QRTZ_TRIGGERS.misfire_instr IS '补偿执行的策略';
COMMENT
ON COLUMN QRTZ_TRIGGERS.job_data IS '存放持久化job对象';
COMMENT
ON TABLE QRTZ_TRIGGERS IS '触发器详细信息表';
COMMENT
ON COLUMN QRTZ_SIMPLE_TRIGGERS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_SIMPLE_TRIGGERS.TRIGGER_NAME IS 'qrtz_triggers表trigger_ name的外键';
COMMENT
ON COLUMN QRTZ_SIMPLE_TRIGGERS.trigger_group IS 'qrtz_triggers表trigger_group的外键';
COMMENT
ON COLUMN QRTZ_SIMPLE_TRIGGERS.repeat_count IS '重复的次数统计';
COMMENT
ON COLUMN QRTZ_SIMPLE_TRIGGERS.repeat_interval IS '重复的间隔时间';
COMMENT
ON COLUMN QRTZ_SIMPLE_TRIGGERS.times_triggered IS '已经触发的次数';
COMMENT
ON TABLE QRTZ_SIMPLE_TRIGGERS IS '简单触发器的信息表';
COMMENT
ON COLUMN QRTZ_CRON_TRIGGERS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_CRON_TRIGGERS.TRIGGER_NAME IS 'qrtz_triggers表trigger_name的外键';
COMMENT
ON COLUMN QRTZ_CRON_TRIGGERS.trigger_group IS 'qrtz_triggers表trigger_group的外键';
COMMENT
ON COLUMN QRTZ_CRON_TRIGGERS.cron_expression IS 'cron表达式';
COMMENT
ON COLUMN QRTZ_CRON_TRIGGERS.time_zone_id IS '时区';
COMMENT
ON TABLE QRTZ_CRON_TRIGGERS IS 'Cron类型的触发器表';
COMMENT
ON COLUMN QRTZ_BLOB_TRIGGERS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_BLOB_TRIGGERS.TRIGGER_NAME IS 'qrtz_triggers表trigger_name的外键';
COMMENT
ON COLUMN QRTZ_BLOB_TRIGGERS.trigger_group IS 'qrtz_triggers表trigger_group的外键';
COMMENT
ON COLUMN QRTZ_BLOB_TRIGGERS.blob_data IS '存放持久化Trigger对象';
COMMENT
ON TABLE QRTZ_BLOB_TRIGGERS IS 'Blob类型的触发器表';
COMMENT
ON COLUMN QRTZ_CALENDARS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_CALENDARS.calendar_name IS '日历名称';
COMMENT
ON COLUMN QRTZ_CALENDARS.calendar IS '存放持久化calendar对象';
COMMENT
ON TABLE QRTZ_CALENDARS IS '日历信息表';
COMMENT
ON COLUMN QRTZ_PAUSED_TRIGGER_GRPS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_PAUSED_TRIGGER_GRPS.trigger_group IS 'qrtz_triggers表trigger_group的外键';
COMMENT
ON TABLE QRTZ_PAUSED_TRIGGER_GRPS IS '暂停的触发器表';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.entry_id IS '调度器实例id';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.TRIGGER_NAME IS 'qrtz_triggers表trigger_name的外键';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.trigger_group IS 'qrtz_triggers表trigger_group的外键';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.instance_name IS '调度器实例名';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.fired_time IS '触发的时间';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.sched_time IS '定时器制定的时间';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.priority IS '优先级';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.STATE IS '状态';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.job_name IS '任务名称';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.job_group IS '任务组名';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.is_nonconcurrent IS '是否并发';
COMMENT
ON COLUMN QRTZ_FIRED_TRIGGERS.requests_recovery IS '是否接受恢复执行';
COMMENT
ON TABLE QRTZ_FIRED_TRIGGERS IS '已触发的触发器表';
COMMENT
ON COLUMN QRTZ_SCHEDULER_STATE.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_SCHEDULER_STATE.instance_name IS '之前配置文件中org.quartz.scheduler.instanceId配置的名字，就会写入该字段';
COMMENT
ON COLUMN QRTZ_SCHEDULER_STATE.last_checkin_time IS '上次检查时间';
COMMENT
ON COLUMN QRTZ_SCHEDULER_STATE.checkin_interval IS '检查间隔时间';
COMMENT
ON TABLE QRTZ_SCHEDULER_STATE IS '调度器状态表';
COMMENT
ON COLUMN QRTZ_LOCKS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_LOCKS.lock_name IS '悲观锁名称';
COMMENT
ON TABLE QRTZ_LOCKS IS '存储的悲观锁信息表';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.sched_name IS '调度名称';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.TRIGGER_NAME IS 'qrtz_triggers表trigger_ name的外键';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.trigger_group IS 'qrtz_triggers表trigger_group的外键';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.str_prop_1 IS 'String类型的trigger的第一个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.str_prop_2 IS 'String类型的trigger的第二个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.str_prop_3 IS 'String类型的trigger的第三个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.int_prop_1 IS 'int类型的trigger的第一个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.int_prop_2 IS 'int类型的trigger的第二个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.long_prop_1 IS 'long类型的trigger的第一个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.long_prop_2 IS 'long类型的trigger的第二个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.dec_prop_1 IS 'decimal类型的trigger的第一个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.dec_prop_2 IS 'decimal类型的trigger的第二个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.bool_prop_1 IS 'Boolean类型的trigger的第一个参数';
COMMENT
ON COLUMN QRTZ_SIMPROP_TRIGGERS.bool_prop_2 IS 'Boolean类型的trigger的第二个参数';
COMMENT
ON TABLE QRTZ_SIMPROP_TRIGGERS IS '同步机制的行锁表';


-- ----------------------------
-- OSS云存储表
-- ----------------------------
drop table if exists sys_oss;
create table sys_oss
(
    oss_id        BIGSERIAL    not null                 ,
    file_name     varchar(64)  not null default ''      ,
    original_name varchar(64)  not null default ''      ,
    file_suffix   varchar(10)  not null default ''      ,
    url           varchar(200) not null                 ,
    create_time   timestamp(0)          default null    ,
    create_by     varchar(64)           default ''      ,
    update_time   timestamp(0)          default null    ,
    update_by     varchar(64)           default ''      ,
    service       varchar(10)  not null default 'minio' ,
    CONSTRAINT sys_oss_pkey primary key (oss_id)
);
COMMENT
ON COLUMN sys_oss.oss_id         is '云存储主键';
COMMENT
ON COLUMN sys_oss.file_name      is '文件名';
COMMENT
ON COLUMN sys_oss.original_name is '原名';
COMMENT
ON COLUMN sys_oss.file_suffix is '文件后缀名';
COMMENT
ON COLUMN sys_oss.url is 'URL地址';
COMMENT
ON COLUMN sys_oss.create_time is '创建时间';
COMMENT
ON COLUMN sys_oss.create_by is '上传人';
COMMENT
ON COLUMN sys_oss.update_time is '更新时间';
COMMENT
ON COLUMN sys_oss.update_by is '更新人';
COMMENT
ON COLUMN sys_oss.service is '服务商';
COMMENT
ON TABLE  sys_oss is 'OSS云存储表';
-- ----------------------------
-- OSS云存储动态配置表
-- ----------------------------
drop table if exists sys_oss_config;
create table sys_oss_config
(
    oss_config_id BIGSERIAL    not null             ,
    config_key    varchar(255) not null default ''  ,
    access_key    varchar(255)          default ''  ,
    secret_key    varchar(255)          default ''  ,
    bucket_name   varchar(255)          default ''  ,
    prefix        varchar(255)          default ''  ,
    endpoint      varchar(255)          default ''  ,
    is_https      char(1)               default 'N' ,
    region        varchar(255)          default ''  ,
    status        char(1)               default '1' ,
    ext1          varchar(255)          default ''  ,
    create_by     varchar(64)           default ''  ,
    create_time   timestamp(0)          default null,
    update_by     varchar(64)           default ''  ,
    update_time   timestamp(0)          default null,
    remark        varchar(500)          default null,
    CONSTRAINT sys_oss_config_pkey primary key (oss_config_id)
);
COMMENT
ON COLUMN sys_oss_config.oss_config_id is '主建';
COMMENT
ON COLUMN sys_oss_config.config_key    is '配置key';
COMMENT
ON COLUMN sys_oss_config.access_key    is 'accessKey';
COMMENT
ON COLUMN sys_oss_config.secret_key    is '秘钥';
COMMENT
ON COLUMN sys_oss_config.bucket_name   is '桶名称';
COMMENT
ON COLUMN sys_oss_config.prefix        is '前缀';
COMMENT
ON COLUMN sys_oss_config.endpoint      is '访问站点';
COMMENT
ON COLUMN sys_oss_config.is_https      is '是否https（Y=是,N=否）';
COMMENT
ON COLUMN sys_oss_config.region        is '域';
COMMENT
ON COLUMN sys_oss_config.status        is '状态（0=正常,1=停用）';
COMMENT
ON COLUMN sys_oss_config.ext1          is '扩展字段';
COMMENT
ON COLUMN sys_oss_config.create_by     is '创建者';
COMMENT
ON COLUMN sys_oss_config.create_time   is '创建时间';
COMMENT
ON COLUMN sys_oss_config.update_by     is '更新者';
COMMENT
ON COLUMN sys_oss_config.update_time   is '更新时间';
COMMENT
ON COLUMN sys_oss_config.remark        is '备注';
COMMENT
ON TABLE  sys_oss_config               is '云存储配置表';

insert into sys_config
values (11, 'OSS预览列表资源开关', 'sys.oss.previewListResource', 'true', 'Y', 'admin', now(), '', null, 'true:开启, false:关闭');

insert into sys_menu
values ('118', '文件管理', '1', '10', 'oss', 'system/oss/index', 1, 0, 'C', '0', '0', 'system:oss:list', 'upload', 'admin', now(), '', null, '文件管理菜单');

insert into sys_menu
values ('1600', '文件查询', '118', '1', '#', '', 1, 0, 'F', '0', '0', 'system:oss:query', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1601', '文件上传', '118', '2', '#', '', 1, 0, 'F', '0', '0', 'system:oss:upload', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1602', '文件下载', '118', '3', '#', '', 1, 0, 'F', '0', '0', 'system:oss:download', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1603', '文件删除', '118', '4', '#', '', 1, 0, 'F', '0', '0', 'system:oss:remove', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1604', '配置添加', '118', '5', '#', '', 1, 0, 'F', '0', '0', 'system:oss:add', '#', 'admin', now(), '', null, '');
insert into sys_menu
values ('1605', '配置编辑', '118', '6', '#', '', 1, 0, 'F', '0', '0', 'system:oss:edit', '#', 'admin', now(), '', null, '');

insert into sys_oss_config
values (1, 'minio', 'ruoyi', 'ruoyi123', 'ruoyi', '', 'http://localhost:9000', 'N', '', '0', '', 'admin', now(), 'admin', now(), NULL);
insert into sys_oss_config
values (2, 'qiniu', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'ruoyi', '', 'http://XXX.XXXX.com', 'N', 'z0', '1', '', 'admin', now(), 'admin', now(), NULL);
insert into sys_oss_config
values (3, 'aliyun', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'ruoyi', '', 'http://oss-cn-beijing.aliyuncs.com', 'N', '', '1', '', 'admin', now(), 'admin', now(), NULL);
insert into sys_oss_config
values (4, 'qcloud', 'XXXXXXXXXXXXXXX', 'XXXXXXXXXXXXXXX', 'ruoyi-1250000000', '', 'http://cos.ap-beijing.myqcloud.com', 'N', 'ap-beijing', '1', '', 'admin', now(), 'admin', now(), NULL);