-- 函数 - 设置表的最后更新时间
|
CREATE OR REPLACE FUNCTION "public"."update_timestamp_column"()
|
RETURNS "pg_catalog"."trigger" AS $BODY$
|
BEGIN
|
NEW.update_time := current_timestamp;
|
RETURN NEW;
|
END;
|
$BODY$
|
LANGUAGE plpgsql VOLATILE
|
COST 100;
|
|
-- test_info - 测试信息表
|
DROP TABLE IF EXISTS "public"."test_info";
|
CREATE TABLE "public"."test_info" (
|
"id" int8 NOT NULL,
|
"name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"user_id" int8 NOT NULL DEFAULT 0,
|
"phone" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"balance" numeric(18,4) NOT NULL DEFAULT 0,
|
"sex" int2 NOT NULL DEFAULT 0,
|
"birthday" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"sort" int4 NOT NULL DEFAULT 0,
|
"status" int4 NOT NULL DEFAULT 0,
|
"comment" varchar(500) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::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"."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 '主键';
|
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 '电话';
|
COMMENT ON COLUMN "public"."test_info"."balance" IS 'balance余额';
|
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 '状态 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"."quartz_task_info" ADD CONSTRAINT "PK_quartz_task_info" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_quartz_task_info_create_time" ON "public"."quartz_task_info"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
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 '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- 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 '是否删除(逻辑删除)';
|
