-- 函数 - 设置表的最后更新时间
|
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 '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- 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 '是否删除(逻辑删除)';
|
|
-- 初始数据
|
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);
|
|
|
-- ----------------------------
|
-- 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 '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- 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 '是否删除(逻辑删除)';
|
|
|
|
|
-- ----------------------------
|
-- admin_menu - 菜单
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."admin_menu";
|
CREATE TABLE "public"."admin_menu" (
|
"id" int8 NOT NULL,
|
"parent_id" int8 NOT NULL DEFAULT 0,
|
"type" int2 NOT NULL DEFAULT 0,
|
"name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"title" varchar(100) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"url" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"level" int2 NOT NULL DEFAULT 0,
|
"sort" int4 NOT NULL DEFAULT 0,
|
"icon" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"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"."admin_menu" ADD CONSTRAINT "PK_admin_menu" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_admin_menu_create_time" ON "public"."admin_menu"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_admin_menu" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."admin_menu"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."admin_menu" IS '菜单';
|
COMMENT ON COLUMN "public"."admin_menu"."id" IS '主键';
|
COMMENT ON COLUMN "public"."admin_menu"."type" IS '类型 EMenuType';
|
COMMENT ON COLUMN "public"."admin_menu"."parent_id" IS '父级ID';
|
COMMENT ON COLUMN "public"."admin_menu"."name" IS '菜单名称';
|
COMMENT ON COLUMN "public"."admin_menu"."title" IS '菜单标题';
|
COMMENT ON COLUMN "public"."admin_menu"."url" IS '前端资源路径';
|
COMMENT ON COLUMN "public"."admin_menu"."level" IS '菜单层级';
|
COMMENT ON COLUMN "public"."admin_menu"."sort" IS '排序值';
|
COMMENT ON COLUMN "public"."admin_menu"."icon" IS '图标';
|
COMMENT ON COLUMN "public"."admin_menu"."status" IS '状态 EState';
|
COMMENT ON COLUMN "public"."admin_menu"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."admin_menu"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."admin_menu"."is_delete" IS '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- Records of admin_menu
|
-- ----------------------------
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10001, 1, 0, '系统管理', '系统管理', '/system', 1, 1, 'system', 1, 1725152400354);
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10101, 2, 10001, '菜单管理', '菜单管理', '/menu', 2, 1, ' ', 1, 1725152400354);
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10102, 2, 10001, '权限管理', '权限管理', '/permission', 2, 2, ' ', 1, 1725152400354);
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10103, 2, 10001, '角色管理', '角色管理', '/role', 2, 1, ' ', 3, 1725152400354);
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10104, 2, 10001, '账号管理', '用户管理', '/user', 2, 1, ' ', 4, 1725152400354);
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10105, 2, 10001, '数据字典', '数据字典', '/dict', 2, 5, 'build', 1, 1725152400354);
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10106, 2, 10001, '任务调度', '任务调度', '/job', 2, 6, ' ', 3, 1725152400354);
|
INSERT INTO "public"."admin_menu"("id","type","parent_id","name","title","url","level","sort","icon","status","create_time") VALUES (10107, 2, 10001, '公告管理', '公告管理', '/notice', 2, 7, ' ', 3, 1725152400354);
|
|
|
-- ----------------------------
|
-- admin_permission - 权限
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."admin_permission";
|
CREATE TABLE "public"."admin_permission" (
|
"id" int8 NOT NULL,
|
"sn" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"menu_id" int8 NOT NULL DEFAULT 0,
|
"menu_url" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"icon" varchar(500) COLLATE "pg_catalog"."default" DEFAULT NULL::character varying,
|
"type" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::character varying,
|
"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"."admin_permission" ADD CONSTRAINT "PK_admin_permission" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_admin_permission_create_time" ON "public"."admin_permission"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_admin_permission" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."admin_permission"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."admin_permission" IS '权限';
|
COMMENT ON COLUMN "public"."admin_permission"."id" IS '主键';
|
COMMENT ON COLUMN "public"."admin_permission"."sn" IS '权限值';
|
COMMENT ON COLUMN "public"."admin_permission"."menu_id" IS '菜单id';
|
COMMENT ON COLUMN "public"."admin_permission"."menu_url" IS '前端资源url';
|
COMMENT ON COLUMN "public"."admin_permission"."name" IS '名称';
|
COMMENT ON COLUMN "public"."admin_permission"."icon" IS '图标';
|
COMMENT ON COLUMN "public"."admin_permission"."type" IS '权限类型 EOperation';
|
COMMENT ON COLUMN "public"."admin_permission"."status" IS '状态 EStatus';
|
COMMENT ON COLUMN "public"."admin_permission"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."admin_permission"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."admin_permission"."is_delete" IS '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- Records of admin_permission
|
-- ----------------------------
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10001, 'menu.list', 10101, '/menu', '菜单.查询', '', 'list', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10002, 'menu.add', 10101, '/menu', '菜单.添加', '', 'add', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10003, 'menu.update', 10101, '/menu', '菜单.编辑', '', 'update', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10004, 'menu.detail', 10101, '/menu', '菜单.详情', '', 'detail', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10005, 'menu.delete', 10101, '/menu', '菜单.删除', '', 'delete', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10006, 'permission.list', 10102, '/permission', '权限.查询', '', 'list', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10007, 'permission.add', 10102, '/permission', '权限.添加', '', 'add', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10008, 'permission.update', 10102, '/permission', '权限.编辑', '', 'update', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10009, 'permission.detail', 10102, '/permission', '权限.详情', '', 'detail', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10010, 'permission.delete', 10102, '/permission', '权限.删除', '', 'delete', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10011, 'role.list', 10103, '/role', '角色.查询', '', 'list', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10012, 'role.add', 10103, '/role', '角色.添加', '', 'add', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10013, 'role.update', 10103, '/role', '角色.编辑', '', 'update', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10014, 'role.detail', 10103, '/role', '角色.详情', '', 'detail', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10015, 'role.delete', 10103, '/role', '角色.删除', '', 'delete', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10016, 'user.list', 10104, '/user', '用户.查询', '', 'list', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10017, 'user.add', 10104, '/user', '用户.添加', '', 'add', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10018, 'user.update', 10104, '/user', '用户.编辑', '', 'update', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10019, 'user.detail', 10104, '/user', '用户.详情', '', 'detail', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10020, 'user.delete', 10104, '/user', '用户.删除', '', 'delete', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10021, 'notice.list', 10201, '/notice', '公告.查询', '', 'list', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10022, 'notice.add', 10201, '/notice', '公告.添加', '', 'add', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10023, 'notice.update', 10201, '/notice', '公告.编辑', '', 'update', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10024, 'notice.detail', 10201, '/notice', '公告.详情', '', 'detail', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10025, 'notice.delete', 10201, '/notice', '公告.删除', '', 'delete', 1, 1722685247123);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10026, 'dict.list', 10105, '/dict', '数据字典.搜索', '', 'list', 1, 1723446992261);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10027, 'dict.add', 10105, '/dict', '数据字典.新增', '', 'add', 1, 1723446992261);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10028, 'dict.update', 10105, '/dict', '数据字典.修改', '', 'update', 1, 1723446992261);
|
INSERT INTO "public"."admin_permission"("id","sn","menu_id","menu_url","name","icon","type","status","create_time") VALUES (10029, 'dict.detail', 10105, '/dict', '数据字典.详情', '', 'detail', 1, 1723446992261);
|
|
-- ----------------------------
|
-- admin_role - 角色
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."admin_role";
|
CREATE TABLE "public"."admin_role" (
|
"id" int8 NOT NULL,
|
"name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"description" varchar(500) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"status" int4 NOT NULL DEFAULT 0,
|
"sort" 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"."admin_role" ADD CONSTRAINT "PK_admin_role" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_admin_role_create_time" ON "public"."admin_role"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_admin_role" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."admin_role"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."admin_role" IS '角色';
|
COMMENT ON COLUMN "public"."admin_role"."id" IS '主键';
|
COMMENT ON COLUMN "public"."admin_role"."name" IS '角色名称';
|
COMMENT ON COLUMN "public"."admin_role"."description" IS '描述';
|
COMMENT ON COLUMN "public"."admin_role"."status" IS '状态 EState';
|
COMMENT ON COLUMN "public"."admin_role"."sort" IS '排序值';
|
COMMENT ON COLUMN "public"."admin_role"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."admin_role"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."admin_role"."is_delete" IS '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- Records of admin_role
|
-- ----------------------------
|
|
-- ----------------------------
|
-- admin_role_menu_relation - 角色菜单关联关系
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."admin_role_menu_relation";
|
CREATE TABLE "public"."admin_role_menu_relation" (
|
"id" int8 NOT NULL,
|
"role_id" int8 NOT NULL DEFAULT 0,
|
"menu_id" int8 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"."admin_role_menu_relation" ADD CONSTRAINT "PK_admin_role_menu_relation" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_admin_role_menu_relation_create_time" ON "public"."admin_role_menu_relation"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_admin_role_menu_relation" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."admin_role_menu_relation"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."admin_role_menu_relation" IS '角色菜单关联关系';
|
COMMENT ON COLUMN "public"."admin_role_menu_relation"."id" IS '主键';
|
COMMENT ON COLUMN "public"."admin_role_menu_relation"."role_id" IS '角色id';
|
COMMENT ON COLUMN "public"."admin_role_menu_relation"."menu_id" IS '菜单id';
|
COMMENT ON COLUMN "public"."admin_role_menu_relation"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."admin_role_menu_relation"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."admin_role_menu_relation"."is_delete" IS '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- Records of admin_role_menu_relation
|
-- ----------------------------
|
|
-- ----------------------------
|
-- admin_role_permission_relation - 角色权限关联关系
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."admin_role_permission_relation";
|
CREATE TABLE "public"."admin_role_permission_relation" (
|
"id" int8 NOT NULL,
|
"role_id" int8 NOT NULL DEFAULT 0,
|
"permission_id" int8 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"."admin_role_permission_relation" ADD CONSTRAINT "PK_admin_role_permission_relation" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_admin_role_permission_relation_create_time" ON "public"."admin_role_permission_relation"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_admin_role_permission_relation" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."admin_role_permission_relation"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."admin_role_permission_relation" IS '角色权限关联关系';
|
COMMENT ON COLUMN "public"."admin_role_permission_relation"."id" IS '主键';
|
COMMENT ON COLUMN "public"."admin_role_permission_relation"."role_id" IS '角色id';
|
COMMENT ON COLUMN "public"."admin_role_permission_relation"."permission_id" IS '权限id';
|
COMMENT ON COLUMN "public"."admin_role_permission_relation"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."admin_role_permission_relation"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."admin_role_permission_relation"."is_delete" IS '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- Records of admin_role_permission_relation
|
-- ----------------------------
|
|
-- ----------------------------
|
-- admin_role_relation - 用户和角色关联关系
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."admin_role_relation";
|
CREATE TABLE "public"."admin_role_relation" (
|
"id" int8 NOT NULL,
|
"admin_id" int8 NOT NULL DEFAULT 0,
|
"role_id" int8 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"."admin_role_relation" ADD CONSTRAINT "PK_admin_role_relation" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_admin_role_relation_create_time" ON "public"."admin_role_relation"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_admin_role_relation" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."admin_role_relation"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."admin_role_relation" IS '用户和角色关联关系';
|
COMMENT ON COLUMN "public"."admin_role_relation"."id" IS '主键';
|
COMMENT ON COLUMN "public"."admin_role_relation"."admin_id" IS '管理员id';
|
COMMENT ON COLUMN "public"."admin_role_relation"."role_id" IS '角色id';
|
COMMENT ON COLUMN "public"."admin_role_relation"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."admin_role_relation"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."admin_role_relation"."is_delete" IS '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- Records of admin_role_relation
|
-- ----------------------------
|
|
-- ----------------------------
|
-- admin_user - 管理员帐号
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."admin_user";
|
CREATE TABLE "public"."admin_user" (
|
"id" int8 NOT NULL,
|
"user_type" int2 NOT NULL DEFAULT 0,
|
"user_rel_id" int8 NOT NULL DEFAULT 0,
|
"user_name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"password" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"nick_name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"head_img" varchar(500) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"contact" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"email" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"status" int2 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"."admin_user" ADD CONSTRAINT "PK_admin_user" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_admin_user_create_time" ON "public"."admin_user"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_admin_user" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."admin_user"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."admin_user" IS '管理员帐号';
|
COMMENT ON COLUMN "public"."admin_user"."id" IS '主键';
|
COMMENT ON COLUMN "public"."admin_user"."user_type" IS '用户类型 EUserType';
|
COMMENT ON COLUMN "public"."admin_user"."user_rel_id" IS '用户关联id，和“user_type”对应，如：客户id等';
|
COMMENT ON COLUMN "public"."admin_user"."user_name" IS '用户名';
|
COMMENT ON COLUMN "public"."admin_user"."password" IS '密码';
|
COMMENT ON COLUMN "public"."admin_user"."nick_name" IS '昵称';
|
COMMENT ON COLUMN "public"."admin_user"."head_img" IS '头像';
|
COMMENT ON COLUMN "public"."admin_user"."contact" IS '联系方式';
|
COMMENT ON COLUMN "public"."admin_user"."email" IS '联系邮箱';
|
COMMENT ON COLUMN "public"."admin_user"."status" IS '状态 EState';
|
COMMENT ON COLUMN "public"."admin_user"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."admin_user"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."admin_user"."is_delete" IS '是否删除(逻辑删除)';
|
|
|
|
|
-- ----------------------------
|
-- sys_dict_type - 字典类型
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."sys_dict_type";
|
CREATE TABLE "public"."sys_dict_type" (
|
"id" int8 NOT NULL,
|
"dict_name" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"dict_type" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"status" int2 NOT NULL DEFAULT (0)::smallint,
|
"remark" 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)::smallint
|
)
|
;
|
-- 设置主键
|
ALTER TABLE "public"."sys_dict_type" ADD CONSTRAINT "PK_sys_dict_type" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_sys_dict_type_create_time" ON "public"."sys_dict_type"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_sys_dict_type" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."sys_dict_type"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."sys_dict_type" IS '字典类型';
|
COMMENT ON COLUMN "public"."sys_dict_type"."id" IS '主键';
|
COMMENT ON COLUMN "public"."sys_dict_type"."dict_name" IS '字典名称';
|
COMMENT ON COLUMN "public"."sys_dict_type"."dict_type" IS '字典类型';
|
COMMENT ON COLUMN "public"."sys_dict_type"."status" IS '状态';
|
COMMENT ON COLUMN "public"."sys_dict_type"."remark" IS '备注';
|
COMMENT ON COLUMN "public"."sys_dict_type"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."sys_dict_type"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."sys_dict_type"."is_delete" IS '是否删除(逻辑删除)';
|
|
-- ----------------------------
|
-- sys_dict_data - 字典数据
|
-- ----------------------------
|
DROP TABLE IF EXISTS "public"."sys_dict_data";
|
CREATE TABLE "public"."sys_dict_data" (
|
"id" int8 NOT NULL,
|
"dict_label" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"dict_value" varchar(20000) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"dict_type" varchar(200) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"status" int2 NOT NULL DEFAULT 0,
|
"remark" 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"."sys_dict_data" ADD CONSTRAINT "PK_sys_dict_data" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_sys_dict_data_create_time" ON "public"."sys_dict_data"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_sys_dict_data" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."sys_dict_data"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."sys_dict_data" IS '字典数据';
|
COMMENT ON COLUMN "public"."sys_dict_data"."id" IS '主键';
|
COMMENT ON COLUMN "public"."sys_dict_data"."dict_label" IS '字典标题';
|
COMMENT ON COLUMN "public"."sys_dict_data"."dict_value" IS '字典键值';
|
COMMENT ON COLUMN "public"."sys_dict_data"."dict_type" IS '字典类型';
|
COMMENT ON COLUMN "public"."sys_dict_data"."status" IS '状态';
|
COMMENT ON COLUMN "public"."sys_dict_data"."remark" IS '备注';
|
COMMENT ON COLUMN "public"."sys_dict_data"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."sys_dict_data"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."sys_dict_data"."is_delete" IS '是否删除(逻辑删除)';
|
|
|
-- notice - 公告
|
DROP TABLE IF EXISTS "public"."notice";
|
CREATE TABLE "public"."notice" (
|
"id" bigint NOT NULL DEFAULT 0,
|
"publish_user" varchar(100) NOT NULL DEFAULT NULL::character varying,
|
"title" varchar(50) NOT NULL DEFAULT NULL::character varying,
|
"summary" varchar(100) NOT NULL DEFAULT NULL::character varying,
|
"content" text,
|
"sort" int4 NOT NULL DEFAULT 0,
|
"status" smallint NOT NULL DEFAULT 0,
|
"create_time" int8 NOT NULL DEFAULT 0,
|
"update_time" timestamp(3) NOT NULL DEFAULT pg_systimestamp(),
|
"create_user" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"create_user_name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"update_user" varchar(100) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"update_user_name" varchar(100) COLLATE "pg_catalog"."default" NOT NULL DEFAULT NULL::character varying,
|
"is_delete" int2 NOT NULL DEFAULT 0
|
);
|
-- 设置主键
|
ALTER TABLE "public"."notice" ADD CONSTRAINT "PK_notice" PRIMARY KEY ("id");
|
-- 索引
|
CREATE INDEX "IDX_notice_create_time" ON "public"."notice"("create_time");
|
-- 触发器：自动赋值最后更新时间
|
CREATE TRIGGER "TG_notice" BEFORE INSERT OR UPDATE OF "update_time" ON "public"."notice"
|
FOR EACH ROW
|
EXECUTE PROCEDURE "public"."update_timestamp_column"();
|
-- 添加描述
|
COMMENT ON TABLE "public"."notice" IS '公告';
|
COMMENT ON COLUMN "public"."notice"."id" IS '主键';
|
COMMENT ON COLUMN "public"."notice"."publish_user" IS '发布人';
|
COMMENT ON COLUMN "public"."notice"."title" IS '标题';
|
COMMENT ON COLUMN "public"."notice"."summary" IS '摘要';
|
COMMENT ON COLUMN "public"."notice"."content" IS '内容';
|
COMMENT ON COLUMN "public"."notice"."sort" IS '排序';
|
COMMENT ON COLUMN "public"."notice"."status" IS '状态(EState)';
|
COMMENT ON COLUMN "public"."notice"."create_time" IS '数据创建时间';
|
COMMENT ON COLUMN "public"."notice"."update_time" IS '最后更新时间';
|
COMMENT ON COLUMN "public"."notice"."create_user" IS '数据创建人';
|
COMMENT ON COLUMN "public"."notice"."create_user_name" IS '数据创建人名称';
|
COMMENT ON COLUMN "public"."notice"."update_user" IS '最后更新人';
|
COMMENT ON COLUMN "public"."notice"."update_user_name" IS '最后更新人名称';
|
COMMENT ON COLUMN "public"."notice"."is_delete" IS '是否删除(逻辑删除)';
|
