前言
因公司要求系统需要全面国产化改造,其中也涉及到定时任务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、插入基本信息。
最后因本人能力有限,有什么不对的地方望各位大佬指出好让我改进,多多包含,谢谢大家。
最后,需要表结构原文件的可以通过评论给我留言获取。