From 6a49247e27307c35bc8e4dd4ce368c7d8850318a Mon Sep 17 00:00:00 2001
From: liulin <lin.liu@88.com>
Date: 星期日, 27 四月 2025 11:06:46 +0800
Subject: [PATCH] 修改运行状态判断

---
 sql/init.sql |  147 +++++++++++++++++++++++++++++++++++++++++++++++--
 1 files changed, 141 insertions(+), 6 deletions(-)

diff --git a/sql/init.sql b/sql/init.sql
index 70b33fa..69a181c 100644
--- a/sql/init.sql
+++ b/sql/init.sql
@@ -1,3 +1,4 @@
+-- 函数 - 设置表的最后更新时间
 CREATE OR REPLACE FUNCTION "public"."update_timestamp_column"()
   RETURNS "pg_catalog"."trigger" AS $BODY$
 BEGIN
@@ -6,7 +7,7 @@
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
-  COST 100
+  COST 100;
 
 -- test_info - 测试信息表
 DROP TABLE IF EXISTS "public"."test_info";
@@ -26,8 +27,17 @@
   "is_delete" int2 NOT NULL DEFAULT 0
 )
 ;
+-- 设置主键
+ALTER TABLE "public"."test_info" ADD CONSTRAINT "PK_test_info_pkey" PRIMARY KEY ("id");
+-- 索引
+CREATE INDEX "IDX_test_info_create_time" ON "public"."test_info"("create_time");
+-- 触发器：自动赋值最后更新时间
+CREATE TRIGGER "TG_test_info" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."test_info"
+FOR EACH ROW
+EXECUTE PROCEDURE "public"."update_timestamp_column"();
+-- 添加描述
 COMMENT ON TABLE "public"."test_info" IS '测试信息表';
-COMMENT ON COLUMN "public"."test_info"."id" IS '主键id';
+COMMENT ON COLUMN "public"."test_info"."id" IS '主键';
 COMMENT ON COLUMN "public"."test_info"."name" IS '名称';
 COMMENT ON COLUMN "public"."test_info"."user_id" IS '用户id';
 COMMENT ON COLUMN "public"."test_info"."phone" IS '电话';
@@ -35,16 +45,141 @@
 COMMENT ON COLUMN "public"."test_info"."sex" IS '性别 男=1,女=2,其他=3';
 COMMENT ON COLUMN "public"."test_info"."birthday" IS '生日';
 COMMENT ON COLUMN "public"."test_info"."sort" IS '排序值';
-COMMENT ON COLUMN "public"."test_info"."status" IS '状态 正常=200,停用=300,删除=400';
+COMMENT ON COLUMN "public"."test_info"."status" IS '状态 EState';
 COMMENT ON COLUMN "public"."test_info"."comment" IS '备注';
 COMMENT ON COLUMN "public"."test_info"."create_time" IS '数据创建时间';
 COMMENT ON COLUMN "public"."test_info"."update_time" IS '最后更新时间';
 COMMENT ON COLUMN "public"."test_info"."is_delete" IS '是否删除(逻辑删除)';
+
+-- ----------------------------
+-- Table structure for quartz_task_info
+-- ----------------------------
+DROP TABLE IF EXISTS "public"."quartz_task_info";
+CREATE TABLE "public"."quartz_task_info" (
+  "id" int8 NOT NULL,
+  "version" int8 NOT NULL DEFAULT 0,
+  "task_no" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "task_name" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "scheduler_rule" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "status" int2 NOT NULL DEFAULT 0,
+  "executor" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "frozen_time" int8 NOT NULL DEFAULT 0,
+  "unfrozen_time" int8 NOT NULL DEFAULT 0,
+  "send_type" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "url" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "execute_parameter" varchar(2000) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "last_time" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "last_parameter" varchar(2000) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "last_status" int4 NOT NULL DEFAULT 0,
+  "create_time" int8 NOT NULL DEFAULT 0,
+  "update_time" timestamp(3) NOT NULL DEFAULT pg_systimestamp(),
+  "is_delete" int2 NOT NULL DEFAULT 0
+)
+;
 -- 设置主键
-ALTER TABLE "public"."test_info" ADD CONSTRAINT "PK_test_info" PRIMARY KEY ("id");
+ALTER TABLE "public"."quartz_task_info" ADD CONSTRAINT "PK_quartz_task_info" PRIMARY KEY ("id");
 -- 索引
-CREATE INDEX "IDX_test_info_create_time" ON "public"."test_info"("create_time");
+CREATE INDEX "IDX_quartz_task_info_create_time" ON "public"."quartz_task_info"("create_time");
 -- 触发器：自动赋值最后更新时间
-CREATE TRIGGER "TG_test_info" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."test_info"
+CREATE TRIGGER "TG_quartz_task_info" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."quartz_task_info"
 FOR EACH ROW
 EXECUTE PROCEDURE "public"."update_timestamp_column"();
+-- 添加描述
+COMMENT ON TABLE "public"."quartz_task_info" IS '定时任务信息';
+COMMENT ON COLUMN "public"."quartz_task_info"."id" IS '主键';
+COMMENT ON COLUMN "public"."quartz_task_info"."version" IS '版本号：需要乐观锁控制';
+COMMENT ON COLUMN "public"."quartz_task_info"."task_no" IS '任务编号';
+COMMENT ON COLUMN "public"."quartz_task_info"."task_name" IS '任务名称';
+COMMENT ON COLUMN "public"."quartz_task_info"."scheduler_rule" IS '定时规则表达式';
+COMMENT ON COLUMN "public"."quartz_task_info"."status" IS '冻结状态';
+COMMENT ON COLUMN "public"."quartz_task_info"."executor" IS '执行者(job方法名)';
+COMMENT ON COLUMN "public"."quartz_task_info"."frozen_time" IS '冻结时间';
+COMMENT ON COLUMN "public"."quartz_task_info"."unfrozen_time" IS '解冻时间';
+COMMENT ON COLUMN "public"."quartz_task_info"."send_type" IS '发送方式';
+COMMENT ON COLUMN "public"."quartz_task_info"."url" IS '请求地址';
+COMMENT ON COLUMN "public"."quartz_task_info"."execute_parameter" IS '执行参数';
+COMMENT ON COLUMN "public"."quartz_task_info"."last_time" IS '上次执行时间';
+COMMENT ON COLUMN "public"."quartz_task_info"."last_parameter" IS '上次执行参数';
+COMMENT ON COLUMN "public"."quartz_task_info"."last_status" IS '上次执行结果';
+COMMENT ON COLUMN "public"."quartz_task_info"."create_time" IS '数据创建时间';
+COMMENT ON COLUMN "public"."quartz_task_info"."update_time" IS '最后更新时间';
+COMMENT ON COLUMN "public"."quartz_task_info"."is_delete" IS '是否删除(逻辑删除)';
+
+-- 初始数据
+INSERT INTO "public"."quartz_task_info" ("id", "version", "task_no", "task_name", "scheduler_rule", "status", "executor", "frozen_time", "unfrozen_time", "send_type", "url", "execute_parameter", "last_time", "last_parameter", "last_status", "create_time")
+VALUES (1830908333901479937, 1725357538032, 'T565596601381429248', '清理任务调度日志', '0 0 1 * * ?', 1, 'clearQuartzLog', 0, 0, '1', '', '', '', '', 200, 1725357538032);
+
+
+-- ----------------------------
+-- Table structure for quartz_task_record
+-- ----------------------------
+DROP TABLE IF EXISTS "public"."quartz_task_record";
+CREATE TABLE "public"."quartz_task_record" (
+  "id" int8 NOT NULL,
+  "task_no" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "task_name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "execute_parameter" varchar(2000) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "start_time" int8 NOT NULL DEFAULT 0,
+  "end_time" int8 NOT NULL DEFAULT 0,
+  "execute_times" int8 NOT NULL DEFAULT 0,
+  "task_status" int4 NOT NULL DEFAULT 0,
+  "fail_reason" varchar(8000) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "create_time" int8 NOT NULL DEFAULT 0,
+  "update_time" timestamp(3) NOT NULL DEFAULT pg_systimestamp(),
+  "is_delete" int2 NOT NULL DEFAULT 0
+)
+;
+-- 设置主键
+ALTER TABLE "public"."quartz_task_record" ADD CONSTRAINT "PK_quartz_task_record" PRIMARY KEY ("id");
+-- 索引
+CREATE INDEX "IDX_quartz_task_record_create_time" ON "public"."quartz_task_record"("create_time");
+-- 触发器：自动赋值最后更新时间
+CREATE TRIGGER "TG_quartz_task_record" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."quartz_task_record"
+FOR EACH ROW
+EXECUTE PROCEDURE "public"."update_timestamp_column"();
+-- 添加描述
+COMMENT ON TABLE "public"."quartz_task_record" IS '定时任务执行记录';
+COMMENT ON COLUMN "public"."quartz_task_record"."id" IS '主键';
+COMMENT ON COLUMN "public"."quartz_task_record"."task_no" IS '任务编号';
+COMMENT ON COLUMN "public"."quartz_task_record"."task_name" IS '任务名称';
+COMMENT ON COLUMN "public"."quartz_task_record"."execute_parameter" IS '执行参数';
+COMMENT ON COLUMN "public"."quartz_task_record"."start_time" IS '开始执行时间';
+COMMENT ON COLUMN "public"."quartz_task_record"."end_time" IS '执行结束时间';
+COMMENT ON COLUMN "public"."quartz_task_record"."execute_times" IS '执行耗时(ms)';
+COMMENT ON COLUMN "public"."quartz_task_record"."task_status" IS '任务状态';
+COMMENT ON COLUMN "public"."quartz_task_record"."fail_reason" IS '失败错误描述';
+COMMENT ON COLUMN "public"."quartz_task_record"."create_time" IS '数据创建时间';
+COMMENT ON COLUMN "public"."quartz_task_record"."update_time" IS '最后更新时间';
+COMMENT ON COLUMN "public"."quartz_task_record"."is_delete" IS '是否删除(逻辑删除)';
+
+-- ----------------------------
+-- Table structure for quartz_task_error
+-- ----------------------------
+DROP TABLE IF EXISTS "public"."quartz_task_error";
+CREATE TABLE "public"."quartz_task_error" (
+  "id" int8 NOT NULL,
+  "task_execute_record_id" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "error_key" varchar(2000) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
+  "error_value" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::text,
+  "create_time" int8 NOT NULL DEFAULT 0,
+  "update_time" timestamp(3) NOT NULL DEFAULT pg_systimestamp(),
+  "is_delete" int2 NOT NULL DEFAULT 0
+)
+;
+-- 设置主键
+ALTER TABLE "public"."quartz_task_error" ADD CONSTRAINT "PK_quartz_task_error" PRIMARY KEY ("id");
+-- 索引
+CREATE INDEX "IDX_quartz_task_error_create_time" ON "public"."quartz_task_error"("create_time");
+-- 触发器：自动赋值最后更新时间
+CREATE TRIGGER "TG_quartz_task_error" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."quartz_task_error"
+FOR EACH ROW
+EXECUTE PROCEDURE "public"."update_timestamp_column"();
+-- 添加描述
+COMMENT ON TABLE "public"."quartz_task_error" IS '定时任务错误信息';
+COMMENT ON COLUMN "public"."quartz_task_error"."id" IS '主键';
+COMMENT ON COLUMN "public"."quartz_task_error"."task_execute_record_id" IS '任务执行记录id';
+COMMENT ON COLUMN "public"."quartz_task_error"."error_key" IS '关键字';
+COMMENT ON COLUMN "public"."quartz_task_error"."error_value" IS '详细信息';
+COMMENT ON COLUMN "public"."quartz_task_error"."create_time" IS '数据创建时间';
+COMMENT ON COLUMN "public"."quartz_task_error"."update_time" IS '最后更新时间';
+COMMENT ON COLUMN "public"."quartz_task_error"."is_delete" IS '是否删除(逻辑删除)';

--
Gitblit v1.9.3