定时任务xxl-job国产化改造，适配磐维数据库（PostgreSQL）-表结构

前言

因公司要求系统需要全面国产化改造，其中也涉及到定时任务xxl-job的改造。

使用的xxl-job版本为：2.5.0

定时任务xxl-job国产化改造，适配磐维数据库（PostgreSQL）-优快云博客

一、创建模式

1、创建schema

CREATE SCHEMA xxl_job AUTHORIZATION xxl;

二、修改表结构

1、xxl_job_info

-- xxl_job_info
CREATE TABLE xxl_job.xxl_job_info (
  id serial,
  job_group int4 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 int4 NOT NULL DEFAULT '0',
  executor_fail_retry_count int4 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 int4 NOT NULL DEFAULT '0',
  trigger_last_time int8 NOT NULL DEFAULT '0',
  trigger_next_time int8 NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);
ALTER TABLE xxl_job.xxl_job_info OWNER TO "xxl";
COMMENT ON COLUMN xxl_job.xxl_job_info.job_group IS '执行器主键ID';
COMMENT ON COLUMN xxl_job.xxl_job_info.author IS '作者';
COMMENT ON COLUMN xxl_job.xxl_job_info.alarm_email IS '报警邮件';
COMMENT ON COLUMN xxl_job.xxl_job_info.schedule_type IS '调度类型';
COMMENT ON COLUMN xxl_job.xxl_job_info.schedule_conf IS '调度配置，值含义取决于调度类型';
COMMENT ON COLUMN xxl_job.xxl_job_info.misfire_strategy IS '调度过期策略';
COMMENT ON COLUMN xxl_job.xxl_job_info.executor_route_strategy IS '执行器路由策略';
COMMENT ON COLUMN xxl_job.xxl_job_info.executor_handler IS '执行器任务handler';
COMMENT ON COLUMN xxl_job.xxl_job_info.executor_param IS '执行器任务参数';
COMMENT ON COLUMN xxl_job.xxl_job_info.executor_block_strategy IS '阻塞处理策略';
COMMENT ON COLUMN xxl_job.xxl_job_info.executor_timeout IS '任务执行超时时间，单位秒';
COMMENT ON COLUMN xxl_job.xxl_job_info.executor_fail_retry_count IS '失败重试次数';
COMMENT ON COLUMN xxl_job.xxl_job_info.glue_type IS 'GLUE类型';
COMMENT ON COLUMN xxl_job.xxl_job_info.glue_source IS 'GLUE源代码';
COMMENT ON COLUMN xxl_job.xxl_job_info.glue_remark IS 'GLUE备注';
COMMENT ON COLUMN xxl_job.xxl_job_info.glue_updatetime IS 'GLUE更新时间';
COMMENT ON COLUMN xxl_job.xxl_job_info.child_jobid IS '子任务ID，多个逗号分隔';
COMMENT ON COLUMN xxl_job.xxl_job_info.trigger_status IS '调度状态：0-停止，1-运行';
COMMENT ON COLUMN xxl_job.xxl_job_info.trigger_last_time IS '上次调度时间';
COMMENT ON COLUMN xxl_job.xxl_job_info.trigger_next_time IS '下次调度时间';

2、xxl_job_log

-- xxl_job_log
CREATE TABLE xxl_job.xxl_job_log (
  id serial,
  job_group int4 NOT NULL,
  job_id int4 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 int4 NOT NULL DEFAULT '0',
  trigger_time TIMESTAMP DEFAULT NULL,
  trigger_code int4 NOT NULL,
  trigger_msg text,
  handle_time TIMESTAMP DEFAULT NULL,
  handle_code int4 NOT NULL,
  handle_msg text,
  alarm_status int2 NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);
ALTER TABLE xxl_job.xxl_job_log OWNER TO "xxl";
CREATE INDEX "idx_trigger_time" ON "xxl_job"."xxl_job_log" USING btree (
  "trigger_time"
);
CREATE INDEX "idx_handle_code" ON "xxl_job"."xxl_job_log" USING btree (
  "handle_code"
);
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."job_group" IS '执行器主键ID';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."job_id" IS '任务，主键ID';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."executor_address" IS '执行器地址，本次执行的地址';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."executor_handler" IS '执行器任务handler';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."executor_param" IS '执行器任务参数';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."executor_sharding_param" IS '执行器任务分片参数，格式如 1/2';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."executor_fail_retry_count" IS '失败重试次数';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."trigger_time" IS '调度-时间';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."trigger_code" IS '调度-结果';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."trigger_msg" IS '调度-日志';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."handle_time" IS '执行-时间';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."handle_code" IS '执行-状态';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."handle_msg" IS '执行-日志';
COMMENT ON COLUMN "xxl_job"."xxl_job_log"."alarm_status" IS '告警状态：0-默认、1-无需告警、2-告警成功、3-告警失败';

3、xxl_job_log_report

-- xxl_job_log_report
CREATE TABLE xxl_job.xxl_job_log_report (
  id serial,
  trigger_day TIMESTAMP DEFAULT NULL,
  running_count int4 NOT NULL DEFAULT '0',
  suc_count int4 NOT NULL DEFAULT '0',
  fail_count int4 NOT NULL DEFAULT '0',
  update_time TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT "udx_trigger_day" UNIQUE ("trigger_day")
);
ALTER TABLE xxl_job.xxl_job_log_report OWNER TO "xxl";
COMMENT ON COLUMN "xxl_job"."xxl_job_log_report"."trigger_day" IS '调度-时间';
COMMENT ON COLUMN "xxl_job"."xxl_job_log_report"."running_count" IS '运行中-日志数量';
COMMENT ON COLUMN "xxl_job"."xxl_job_log_report"."suc_count" IS '执行成功-日志数量';
COMMENT ON COLUMN "xxl_job"."xxl_job_log_report"."fail_count" IS '执行失败-日志数量';

4、xxl_job_logglue

-- xxl_job_logglue
CREATE TABLE xxl_job.xxl_job_logglue (
  id serial,
  job_id int4 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,
  PRIMARY KEY (id)
);
ALTER TABLE xxl_job.xxl_job_logglue OWNER TO "xxl";
COMMENT ON COLUMN "xxl_job"."xxl_job_logglue"."job_id" IS '任务，主键ID';
COMMENT ON COLUMN "xxl_job"."xxl_job_logglue"."glue_type" IS 'GLUE类型';
COMMENT ON COLUMN "xxl_job"."xxl_job_logglue"."glue_source" IS 'GLUE源代码';
COMMENT ON COLUMN "xxl_job"."xxl_job_logglue"."glue_remark" IS 'GLUE备注';

5、xxl_job_registry

-- xxl_job_registry
CREATE TABLE xxl_job.xxl_job_registry (
  id serial,
  registry_group varchar(50) NOT NULL,
  registry_key varchar(255) NOT NULL,
  registry_value varchar(255) NOT NULL,
  update_time TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id)
);
ALTER TABLE xxl_job.xxl_job_registry OWNER TO "xxl";
CREATE INDEX "idx_rg_rk_rv" ON "xxl_job"."xxl_job_registry" USING btree (
  "registry_group","registry_key","registry_value"
);

6、xxl_job_group

-- xxl_job_group
CREATE TABLE xxl_job.xxl_job_group (
  id serial,
  app_name varchar(64) NOT NULL,
  title varchar(12) NOT NULL,
  address_type int4 NOT NULL DEFAULT '0',
  address_list text,
  update_time TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (id)
);
ALTER TABLE xxl_job.xxl_job_group OWNER TO "xxl";
COMMENT ON COLUMN "xxl_job"."xxl_job_group"."app_name" IS '执行器AppName';
COMMENT ON COLUMN "xxl_job"."xxl_job_group"."title" IS '执行器名称';
COMMENT ON COLUMN "xxl_job"."xxl_job_group"."address_type" IS '执行器地址类型：0=自动注册、1=手动录入';
COMMENT ON COLUMN "xxl_job"."xxl_job_group"."address_list" IS '执行器地址列表，多地址逗号分隔';

7、xxl_job_user

-- xxl_job_user
CREATE TABLE xxl_job.xxl_job_user (
  id serial,
  username varchar(50) NOT NULL,
  password varchar(50) NOT NULL,
  role int4 NOT NULL,
  permission varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT "udx_username" UNIQUE ("username")
);
ALTER TABLE xxl_job.xxl_job_user OWNER TO "xxl";
COMMENT ON COLUMN "xxl_job"."xxl_job_user"."username" IS '账号';
COMMENT ON COLUMN "xxl_job"."xxl_job_user"."password" IS '密码';
COMMENT ON COLUMN "xxl_job"."xxl_job_user"."role" IS '角色：0-普通用户、1-管理员';
COMMENT ON COLUMN "xxl_job"."xxl_job_user"."permission" IS '权限：执行器id列表，多个逗号分割';

8、xxl_job_lock

-- xxl_job_lock
CREATE TABLE xxl_job.xxl_job_lock (
  lock_name varchar(50) NOT NULL,
  PRIMARY KEY (lock_name)
);
ALTER TABLE xxl_job.xxl_job_lock OWNER TO "xxl";
COMMENT ON COLUMN "xxl_job"."xxl_job_lock"."lock_name" IS '锁名称';

三、插入基本信息

1、插入基本信息

INSERT INTO xxl_job.xxl_job_group(id, app_name, title, address_type, address_list, update_time) VALUES (1, 'xxl-job-executor-sample', '示例执行器', 0, NULL, '2018-11-03 22:21:31' );
INSERT INTO xxl_job.xxl_job_info(id, 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, '测试任务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.xxl_job_user(id, username, password, role, permission) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 1, NULL);
INSERT INTO xxl_job.xxl_job_lock ( lock_name) VALUES ( 'schedule_lock');

总结

因公司要求系统需要全面国产化改造，其中也涉及到定时任务xxl-job的改造，将xxl-job原有的mysql表结构调整为适配postgresql的表结构。

1、创建模式。

2、修改表结构。

3、插入基本信息。

最后因本人能力有限，有什么不对的地方望各位大佬指出好让我改进，多多包含，谢谢大家。

最后，需要表结构原文件的可以通过评论给我留言获取。