-- 函数 - 设置表的最后更新时间 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 '是否删除(逻辑删除)';