xxl-job最新版2.3.1修改支持Pgsql、PostgreSQL版本

xxl-job原生支持Mysql，不支持pgsql，要适配Pgsql的需要修改部分代码。
xxl-job最新版2.3.1修改支持Pgsql、PostgreSQL版本
下面的所有修改基于xxl-job的2.3.1版本
下面的所有修改基于xxl-job的2.3.1版本
下面的所有修改基于xxl-job的2.3.1版本

1、修改Sql脚本

CREATE TABLE xxl_job_info (
  id serial constraint xxl_job_info_pkey primary key,
  job_group integer NOT NULL,
  job_desc varchar(255) NOT NULL,
  add_time timestamp DEFAULT NULL,
  update_time timestamp DEFAULT NULL,
  author varchar(64) DEFAULT NULL ,
  alarm_email varchar(255) DEFAULT NULL ,
  schedule_type varchar(50) NOT NULL DEFAULT 'NONE' ,
  schedule_conf varchar(128) DEFAULT NULL,
  misfire_strategy varchar(50) NOT NULL DEFAULT 'DO_NOTHING' ,
  executor_route_strategy varchar(50) DEFAULT NULL ,
  executor_handler varchar(255) DEFAULT NULL ,
  executor_param varchar(512) DEFAULT NULL ,
  executor_block_strategy varchar(50) DEFAULT NULL ,
  executor_timeout integer NOT NULL DEFAULT '0',
  executor_fail_retry_count integer NOT NULL DEFAULT '0' ,
  glue_type varchar(50) NOT NULL ,
  glue_source text ,
  glue_remark varchar(128) DEFAULT NULL ,
  glue_updatetime timestamp DEFAULT NULL ,
  child_jobid varchar(255) DEFAULT NULL ,
  trigger_status smallint NOT NULL DEFAULT '0' ,
  trigger_last_time bigint NOT NULL DEFAULT '0' ,
  trigger_next_time bigint NOT NULL DEFAULT '0'
);

comment on table xxl_job_info is '任务信息表';
comment on column xxl_job_info.id  is '主键';
comment on column xxl_job_info.job_group  is '执行器主键ID';
comment on column xxl_job_info.job_desc  is '任务描述';
comment on column xxl_job_info.add_time  is '任务创建时间';
comment on column xxl_job_info.update_time  is '任务更新时间';
comment on column xxl_job_info.author  is '作者';
comment on column xxl_job_info.alarm_email  is '报警邮件';
comment on column xxl_job_info.schedule_type  is '调度类型';
comment on column xxl_job_info.schedule_conf  is '调度配置，值含义取决于调度类型';
comment on column xxl_job_info.misfire_strategy  is '调度过期策略';
comment on column xxl_job_info.executor_route_strategy  is '执行器路由策略';
comment on column xxl_job_info.executor_handler  is '执行器任务handler';
comment on column xxl_job_info.executor_param is '执行器任务参数';
comment on column xxl_job_info.executor_block_strategy  is '阻塞处理策略';
comment on column xxl_job_info.executor_timeout  is '任务执行超时时间，单位秒';
comment on column xxl_job_info.executor_fail_retry_count  is '失败重试次数';
comment on column xxl_job_info.glue_type  is 'GLUE类型';
comment on column xxl_job_info.glue_source  is 'GLUE源代码';
comment on column xxl_job_info.glue_remark  is 'GLUE备注';
comment on column xxl_job_info.glue_updatetime  is 'GLUE更新时间';
comment on column xxl_job_info.child_jobid  is '子任务ID，多个逗号分隔';
comment on column xxl_job_info.trigger_status  is '调度状态：0-停止，1-运行';
comment on column xxl_job_info.trigger_last_time  is '上次调度时间';
comment on column xxl_job_info.trigger_next_time  is '下次调度时间';


CREATE TABLE xxl_job_log (
  id serial constraint xxl_job_log_pkey primary key,
  job_group integer NOT NULL,
  job_id integer NOT NULL,
  executor_address varchar(255) DEFAULT NULL,
  executor_handler varchar(255) DEFAULT NULL,
  executor_param varchar(512) DEFAULT NULL,
  executor_sharding_param varchar(20) DEFAULT NULL ,
  executor_fail_retry_count integer NOT NULL DEFAULT '0',
  trigger_time timestamp DEFAULT NULL,
  trigger_code integer NOT NULL,
  trigger_msg text,
  handle_time timestamp DEFAULT NULL,
  handle_code integer NOT NULL,
  handle_msg text ,
  alarm_status smallint NOT NULL DEFAULT '0'
) ;

CREATE INDEX I_trigger_time ON xxl_job_log (trigger_time);
CREATE INDEX I_handle_code ON xxl_job_log (handle_code);
comment on table xxl_job_log is '任务日志表';
comment on column xxl_job_log.id  is '主键';
comment on column xxl_job_log.job_group  is '执行器主键ID';
comment on column xxl_job_log.job_id  is '任务，主键ID';
comment on column xxl_job_log.executor_address  is '执行器地址，本次执行的地址';
comment on column xxl_job_log.executor_handler  is '执行器任务handler';
comment on column xxl_job_log.executor_param  is '执行器任务参数';
comment on column xxl_job_log.executor_sharding_param  is '执行器任务分片参数，格式如 1/2';
comment on column xxl_job_log.executor_fail_retry_count  is '失败重试次数';
comment on column xxl_job_log.trigger_time  is '调度-时间';
comment on column xxl_job_log.trigger_code  is '调度-结果';
comment on column xxl_job_log.trigger_msg  is '调度-日志';
comment on column xxl_job_log.handle_time  is '执行-时间';
comment on column xxl_job_log.handle_code  is '执行-状态';
comment on column xxl_job_log.handle_msg  is '执行-日志';
comment on column xxl_job_log.alarm_status  is '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败';


create or replace function upd_timestamp() returns trigger as
$$
begin
    new.update_time = current_timestamp;
    return new;
end
$$
    language plpgsql;


CREATE TABLE xxl_job_log_report (
  id serial constraint xxl_job_log_report_pkey primary key,
  trigger_day timestamp DEFAULT NULL,
  running_count integer NOT NULL DEFAULT '0',
  suc_count integer NOT NULL DEFAULT '0',
  fail_count integer NOT NULL DEFAULT '0',
  update_time timestamp DEFAULT NULL
);

CREATE UNIQUE INDEX i_trigger_day ON xxl_job_log_report (trigger_day);
comment on column  xxl_job_log_report.id  is '主键';
comment on column  xxl_job_log_report.trigger_day  is '调度-时间';
comment on column  xxl_job_log_report.running_count  is '运行中-日志数量';
comment on column  xxl_job_log_report.suc_count  is '执行成功-日志数量';
comment on column  xxl_job_log_report.fail_count  is '执行失败-日志数量';
comment on column  xxl_job_log_report.update_time  is '更新时间';


CREATE TABLE xxl_job_logglue (
  id serial constraint xxl_job_logglue_pkey primary key,
  job_id integer NOT NULL,
  glue_type varchar(50) DEFAULT NULL,
  glue_source text,
  glue_remark varchar(128) NOT NULL,
  add_time timestamp DEFAULT NULL,
  update_time timestamp DEFAULT NULL
);

create trigger t_xxl_job_logglue_update_time before update on xxl_job_logglue for each row execute procedure upd_timestamp();
comment on table xxl_job_logglue is '任务GLUE日志表';
comment on column xxl_job_logglue.id  is '主键';
comment on column xxl_job_logglue.job_id  is '任务，主键ID';
comment on column xxl_job_logglue.glue_type  is 'GLUE类型';
comment on column xxl_job_logglue.glue_source  is 'GLUE源代码';
comment on column xxl_job_logglue.glue_remark  is 'GLUE备注';
comment on column xxl_job_logglue.add_time  is '创建时间';
comment on column xxl_job_logglue.update_time  is '修改时间';





CREATE TABLE xxl_job_registry (
  id serial constraint xxl_job_registry_pkey primary key,
  registry_group varchar(50) NOT NULL,
  registry_key varchar(255) NOT NULL,
  registry_value varchar(255) NOT NULL,
  update_time timestamp DEFAULT NULL
);
CREATE INDEX i_g_k_v ON xxl_job_registry (registry_group,registry_key,registry_value);
comment on table xxl_job_registry is '任务注册表';
comment on column xxl_job_registry.id  is '主键';
comment on column xxl_job_registry.registry_group  is '注册分组';
comment on column xxl_job_registry.registry_key  is '注册键';
comment on column xxl_job_registry.registry_value  is '注册值';
comment on column xxl_job_registry.update_time  is '更新时间';


CREATE TABLE xxl_job_group (
  id serial constraint xxl_job_group_pkey primary key,
  app_name varchar(64) NOT NULL,
  title varchar(12) NOT NULL,
  address_type smallint NOT NULL DEFAULT '0',
  address_list text ,
  update_time timestamp DEFAULT NULL
);

comment on table xxl_job_group is '任务分组表';
comment on column xxl_job_group.id  is '主键';
comment on column xxl_job_group.app_name  is '执行器AppName';
comment on column xxl_job_group.title  is '执行器名称';
comment on column xxl_job_group.address_type  is '执行器地址类型：0=自动注册、1=手动录入';
comment on column xxl_job_group.address_list  is '执行器地址列表，多地址逗号分隔';


CREATE TABLE xxl_job_user (
  id serial constraint xxl_job_user_pkey primary key,
  username varchar(50) NOT NULL,
  password varchar(50) NOT NULL,
  role smallint NOT NULL,
  permission varchar(255) DEFAULT NULL
) ;


CREATE UNIQUE INDEX i_username ON xxl_job_user (username);
comment on table xxl_job_user is '任务用户表';
comment on column xxl_job_user.id  is '主键';
comment on column xxl_job_user.username  is '账号';
comment on column xxl_job_user.password  is '密码';
comment on column xxl_job_user.role  is '角色：0-普通用户、1-管理员';
comment on column xxl_job_user.permission  is '权限：执行器ID列表，多个逗号分割';


CREATE TABLE xxl_job_lock (
  lock_name varchar(50) NOT NULL,
  PRIMARY KEY (lock_name)
);

comment on table xxl_job_lock is '任务锁表';
comment on column xxl_job_lock.lock_name  is '锁名称';

INSERT INTO xxl_job_group( app_name, title, address_type, address_list, update_time) VALUES ('xxl-job-executor-sample', '示例执行器', 0, NULL, '2018-11-03 22:21:31' );
INSERT INTO xxl_job_info( job_group, job_desc, add_time, update_time, author, alarm_email, schedule_type, schedule_conf, misfire_strategy, executor_route_strategy, executor_handler, executor_param, executor_block_strategy, executor_timeout, executor_fail_retry_count, glue_type, glue_source, glue_remark, glue_updatetime, child_jobid) VALUES ( 1, '测试任务1', '2018-11-03 22:21:31', '2018-11-03 22:21:31', 'XXL', '', 'CRON', '0 0 0 * * ? *', 'DO_NOTHING', 'FIRST', 'demoJobHandler', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2018-11-03 22:21:31', '');
INSERT INTO xxl_job_user( username, password, role, permission) VALUES ( 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
INSERT INTO xxl_job_lock ( lock_name) VALUES ( 'schedule_lock');

2、修改Mapper文件

2.1、 涉及分页的sql修改

LIMIT #{offset}, #{pagesize}
修改为：
LIMIT #{pagesize} offset #{offset}

LIMIT 0, #{limit}
修改为：
LIMIT #{limit}

2.2、非运算符替换

全局搜索：findFailJobLogIds，将！替换为 not

2.3、时间转换函数修改

XxlJobRegistryMapper.xml文件
全局搜索（两处修改）：WHERE t.update_time <![CDATA[ < ]]> DATE_ADD(#{nowTime},INTERVAL -#{timeout} SECOND)
替换为：WHERE t.update_time <![CDATA[ < ]]> ((select NOW()) -INTERVAL '${timeout} S')

2.4、as语句替换

pgsql的as语句不加双引号会变成全小写，造成xxl-job首页数据异常。
XxlJobLogMapper.xml文件findLogReport方法的triggerDayCount，triggerDayCountRunning，triggerDayCountSuc都加上英文双引号。

3、修改properties配置文件

数据库配置修改为pgsql

### xxl-job, datasource
spring.datasource.url=jdbc:postgresql://127.0.0.1:5432/test
spring.datasource.username=postgres
spring.datasource.password=123456
spring.datasource.driver-class-name=org.postgresql.Driver

4、pom文件修改

增加pgsql的依赖

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.4.0</version>
</dependency>