嗨玩后端开发规范

数据库表调整记录


数据分析0.1版本调整

<pre><code>---------------- 权限赋值 ---------------- # 将所有序列权限赋值给对应用户 TODO: 将用户修改为对应的用户 GRANT ALL PRIVILEGES ON ALL sequences IN SCHEMA public TO eghdata; # 将所有数据表TODO: 将用户修改为对应的用户 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO eghdata; ---------------- 触发器 ----------------- -- 创建触发器函数 CREATE OR REPLACE FUNCTION update_row_update_time() RETURNS TRIGGER AS $$ BEGIN NEW.update_time = now(); RETURN NEW; END; $$ language 'plpgsql'; ---------------- 数据账号配置 ------------ -- 平台账号配置序列 CREATE SEQUENCE table_plf_acct_config_no_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 99999999 CACHE 1; -- 平台账号配置 CREATE TABLE "plf_acct_config" ( "no_" int4 NOT NULL DEFAULT nextval( 'table_plf_acct_config_no_seq' ), "plf_conf_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "app_code" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "plf_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "account" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "pwd" varchar(255) COLLATE "pg_catalog"."default" NOT NULL, "url" text COLLATE "pg_catalog"."default" NOT NULL, "cookies" text COLLATE "pg_catalog"."default", "status" int2 NOT NULL, "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, "update_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "plf_acct_config_pkey" PRIMARY KEY ("no_"), CONSTRAINT "unique_plf_conf_id" UNIQUE ("plf_conf_id"), CONSTRAINT "unique_app_code_and_plf_id" UNIQUE ("app_code", "plf_id") ); -- 数据表授权 TODO: 修改owner为实际用户 ALTER TABLE "public"."plf_acct_config" OWNER TO "root"; -- 平台账号配置字段注释 COMMENT ON COLUMN "plf_acct_config"."no_" IS '主键'; COMMENT ON COLUMN "plf_acct_config"."plf_conf_id" IS '配置ID'; COMMENT ON COLUMN "plf_acct_config"."app_code" IS 'app code'; COMMENT ON COLUMN "plf_acct_config"."plf_id" IS '平台ID'; COMMENT ON COLUMN "plf_acct_config"."account" IS '账号'; COMMENT ON COLUMN "plf_acct_config"."pwd" IS '密码'; COMMENT ON COLUMN "plf_acct_config"."url" IS '平台对应的链接'; COMMENT ON COLUMN "plf_acct_config"."cookies" IS '平台cookies'; COMMENT ON COLUMN "plf_acct_config"."status" IS '是否失效 1-生效 2-失效'; COMMENT ON COLUMN "plf_acct_config"."creation_time" IS '创建时间'; COMMENT ON COLUMN "plf_acct_config"."update_time" IS '更新时间'; -- 平台账号配置表唯一键注释 COMMENT ON CONSTRAINT "unique_plf_conf_id" ON "public"."plf_acct_config" IS '配置ID唯一'; COMMENT ON CONSTRAINT "unique_app_code_and_plf_id" ON "public"."plf_acct_config" IS '每个app code 对应平台ID唯一'; -- 设置平台账号配置触发器 CREATE TRIGGER update_plf_acct_config_update_at BEFORE UPDATE ON plf_acct_config FOR EACH ROW EXECUTE PROCEDURE update_row_update_time(); -- 开放账号序列 CREATE SEQUENCE table_open_account_no_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 99999999 CACHE 1; -- 开放账号 CREATE TABLE "open_account" ( "no_" int4 NOT NULL DEFAULT nextval( 'table_open_account_no_seq' ), "open_acct_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "app_name" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "app_code" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "aes_key" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "data_url" text COLLATE "pg_catalog"."default" NOT NULL, "table_prefix" varchar(255) COLLATE "pg_catalog"."default" NOT NULL, "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, "update_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "open_account_pkey" PRIMARY KEY ("no_"), CONSTRAINT "unique_open_acct_id" UNIQUE ("open_acct_id"), CONSTRAINT "unique_app_name_id" UNIQUE ("app_name"), CONSTRAINT "unique_app_code_id" UNIQUE ("app_code"), CONSTRAINT "unique_table_prefix" UNIQUE ("table_prefix") ); -- 数据表授权 TODO: 修改owner为实际用户 ALTER TABLE "public"."open_account" OWNER TO "root"; -- 开放账号字段注释 COMMENT ON COLUMN "open_account"."no_" IS '主键'; COMMENT ON COLUMN "open_account"."open_acct_id" IS '第三方配置ID'; COMMENT ON COLUMN "open_account"."app_name" IS '第三方名称'; COMMENT ON COLUMN "open_account"."app_code" IS '第三方ID'; COMMENT ON COLUMN "open_account"."aes_key" IS '验签密钥'; COMMENT ON COLUMN "open_account"."data_url" IS '获取开放数据接口地址'; COMMENT ON COLUMN "open_account"."table_prefix" IS '基础数据表前缀'; COMMENT ON COLUMN "plf_acct_config"."creation_time" IS '创建时间'; COMMENT ON COLUMN "plf_acct_config"."update_time" IS '更新时间'; -- 开放账号表唯一键注释 COMMENT ON CONSTRAINT "unique_open_acct_id" ON "public"."open_account" IS '第三方开放账号配置ID唯一'; COMMENT ON CONSTRAINT "unique_app_name_id" ON "public"."open_account" IS '第三方开放名称唯一'; COMMENT ON CONSTRAINT "unique_app_code_id" ON "public"."open_account" IS '第三方开放app code唯一'; COMMENT ON CONSTRAINT "unique_table_prefix" ON "public"."open_account" IS '所有表前缀唯一'; -- 设置开放账号触发器 CREATE TRIGGER update_open_account_update_at BEFORE UPDATE ON open_account FOR EACH ROW EXECUTE PROCEDURE update_row_update_time(); -- 白名单序列 CREATE SEQUENCE table_ip_whitelist_no_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 99999999 CACHE 1; -- 白名单 CREATE TABLE "ip_whitelist" ( "no_" int4 NOT NULL DEFAULT nextval( 'table_ip_whitelist_no_seq' ), "open_acct_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "ip" inet NOT NULL, "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, "update_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "ip_whitelist_pkey" PRIMARY KEY ("no_"), CONSTRAINT "unique_ip_and_open_id" UNIQUE ("open_acct_id", "ip") ); -- 数据表授权 TODO: 修改owner为实际用户 ALTER TABLE "public"."ip_whitelist" OWNER TO "root"; -- 白名单字段注释 COMMENT ON COLUMN "ip_whitelist"."no_" IS '主键'; COMMENT ON COLUMN "ip_whitelist"."open_acct_id" IS 'OpenAccountID'; COMMENT ON COLUMN "ip_whitelist"."ip" IS 'IP地址'; COMMENT ON COLUMN "plf_acct_config"."creation_time" IS '创建时间'; COMMENT ON COLUMN "plf_acct_config"."update_time" IS '更新时间'; -- 白名单唯一键注释 COMMENT ON CONSTRAINT "unique_ip_and_open_id" ON "public"."ip_whitelist" IS 'IP地址与开放接口唯一'; -- 设置开放账号触发器 CREATE TRIGGER update_ip_whitelist_update_at BEFORE UPDATE ON ip_whitelist FOR EACH ROW EXECUTE PROCEDURE update_row_update_time(); ---------------- 基础数据模板表 注: 无需执行, 只保留模板 ------------ -- 业务账号基础数据表 CREATE TABLE "public"."biz_acct_basis_data" ( "no_" int4 NOT NULL, "id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "dept_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "prev_dept_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "dept_number" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "stl_earnings" numeric(32,2) NOT NULL, "reality_stm" numeric(32,2) NOT NULL, "gift_stm" numeric(32,2) NOT NULL, "total_stm" numeric(32,2) NOT NULL, "date" int4 NOT NULL, "stl_mode" int2 NOT NULL, "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, "update_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "biz_acct_basis_data_pkey" PRIMARY KEY ("no_"), CONSTRAINT "unique_biz_acct_basis_data_id" UNIQUE ("id") ); ALTER TABLE "public"."biz_acct_basis_data" OWNER TO "root"; -- 业务账号基础数据表主键自增 CREATE SEQUENCE table_biz_acct_basis_data_no_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 99999999 CACHE 1; ALTER TABLE "biz_acct_basis_data" ALTER COLUMN no_ SET DEFAULT nextval( 'table_biz_acct_basis_data_no_seq' ); -- 业务账号基础数据表no索引 CREATE UNIQUE INDEX "biz_acct_basis_data_no" ON "public"."biz_acct_basis_data" USING btree ("no_" "pg_catalog"."int4_ops" ASC NULLS LAST); -- 业务账号基础数据表ID索引 CREATE UNIQUE INDEX "biz_acct_basis_data_id" ON "public"."biz_acct_basis_data" USING btree ("id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 业务账号基础数据表日期索引 CREATE INDEX "biz_acct_basis_data_date_index" ON "public"."biz_acct_basis_data" USING btree ("date" "pg_catalog"."int4_ops" ASC NULLS LAST); -- 业务账号基础数据表部门索引 CREATE INDEX "biz_acct_dept_basis_data_dept_id_index" ON "public"."biz_acct_basis_data" USING btree ("dept_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 业务账号基础数据表上级部门索引 CREATE INDEX "biz_acct_dept_basis_data_prev_dept_id_index" ON "public"."biz_acct_basis_data" USING btree ("prev_dept_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 业务账号基础数据表结算模式索引 CREATE INDEX "biz_acct_dept_basis_data_stl_mode_index" ON "public"."biz_acct_basis_data" USING btree ("stl_mode" "pg_catalog"."int2_ops" ASC NULLS LAST); -- 业务账号基础数据表字段注释 COMMENT ON COLUMN "biz_acct_basis_data"."no_" IS '主键'; COMMENT ON COLUMN "biz_acct_basis_data"."id" IS 'ID'; COMMENT ON COLUMN "biz_acct_basis_data"."dept_id" IS '部门ID'; COMMENT ON COLUMN "biz_acct_basis_data"."prev_dept_id" IS '上级部门ID'; COMMENT ON COLUMN "biz_acct_basis_data"."dept_number" IS '部门号码'; COMMENT ON COLUMN "biz_acct_basis_data"."stl_earnings" IS '结算收益'; COMMENT ON COLUMN "biz_acct_basis_data"."reality_stm" IS '真实流水'; COMMENT ON COLUMN "biz_acct_basis_data"."gift_stm" IS '礼物流水'; COMMENT ON COLUMN "biz_acct_basis_data"."total_stm" IS '总流水'; COMMENT ON COLUMN "biz_acct_basis_data"."date" IS '日期'; COMMENT ON COLUMN "biz_acct_basis_data"."stl_mode" IS '结算模式'; COMMENT ON COLUMN "biz_acct_basis_data"."creation_time" IS '创建时间'; COMMENT ON COLUMN "biz_acct_basis_data"."update_time" IS '更新时间'; -- 业务账号基础数据表唯一字段字段注释 COMMENT ON CONSTRAINT "unique_biz_acct_basis_data_id" ON "public"."biz_acct_basis_data" IS 'ID唯一'; -- 设置业务账号基础数据触发器 CREATE TRIGGER update_biz_acct_basis_data_update_at BEFORE UPDATE ON biz_acct_basis_data FOR EACH ROW EXECUTE PROCEDURE update_row_update_time(); -- 厅基础数据表 CREATE TABLE "public"."room_basis_data" ( "no_" int4 NOT NULL, "id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "dept_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "prev_dept_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "dept_number" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "stl_earnings" numeric(32,2) NOT NULL, "reality_stm" numeric(32,2) NOT NULL, "date" int4 NOT NULL, "stl_mode" int2 NOT NULL, "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, "update_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "room_basis_data_pkey" PRIMARY KEY ("no_"), CONSTRAINT "unique_room_basis_data_id" UNIQUE ("id") ); ALTER TABLE "public"."room_basis_data" OWNER TO "root"; -- 厅基础数据表主键自增 CREATE SEQUENCE table_room_basis_data_no_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 99999999 CACHE 1; ALTER TABLE "room_basis_data" ALTER COLUMN no_ SET DEFAULT nextval( 'table_room_basis_data_no_seq' ); -- 厅基础数据表no索引 CREATE UNIQUE INDEX "room_basis_data_no" ON "public"."room_basis_data" USING btree ("no_" "pg_catalog"."int4_ops" ASC NULLS LAST); -- 业务账号基础数据表ID索引 CREATE UNIQUE INDEX "room_basis_data_id" ON "public"."room_basis_data" USING btree ("id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 厅基础数据表日期索引 CREATE INDEX "room_basis_data_date_index" ON "public"."room_basis_data" USING btree ("date" "pg_catalog"."int4_ops" ASC NULLS LAST); -- 厅基础数据表部门索引 CREATE INDEX "room_basis_data_dept_id_index" ON "public"."room_basis_data" USING btree ("dept_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 上级部门索引 CREATE INDEX "room_basis_data_prev_dept_id_index" ON "public"."room_basis_data" USING btree ("prev_dept_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 厅基础数据表结算模式索引 CREATE INDEX "room_basis_data_stl_mde_index" ON "public"."room_basis_data" USING btree ("stl_mode" "pg_catalog"."int2_ops" ASC NULLS LAST); -- 厅基础数据表字段注释 COMMENT ON COLUMN "room_basis_data"."no_" IS '主键'; COMMENT ON COLUMN "room_basis_data"."id" IS 'ID'; COMMENT ON COLUMN "room_basis_data"."dept_id" IS '部门ID'; COMMENT ON COLUMN "room_basis_data"."prev_dept_id" IS '上级部门ID'; COMMENT ON COLUMN "room_basis_data"."dept_number" IS '部门号码'; COMMENT ON COLUMN "room_basis_data"."stl_earnings" IS '结算收益'; COMMENT ON COLUMN "room_basis_data"."reality_stm" IS '真实流水'; COMMENT ON COLUMN "room_basis_data"."date" IS '日期'; COMMENT ON COLUMN "room_basis_data"."stl_mode" IS '结算模式'; COMMENT ON COLUMN "room_basis_data"."creation_time" IS '创建时间'; COMMENT ON COLUMN "room_basis_data"."update_time" IS '更新时间'; -- 厅基础数据表唯一字段字段注释 COMMENT ON CONSTRAINT "unique_room_basis_data_id" ON "public"."room_basis_data" IS 'ID唯一'; -- 设置厅基础数据表触发器 CREATE TRIGGER update_room_basis_data_update_at BEFORE UPDATE ON room_basis_data FOR EACH ROW EXECUTE PROCEDURE update_row_update_time(); -- 厅数据表 CREATE TABLE "public"."room_dept_basis_data" ( "no_" int4 NOT NULL, "id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "dept_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "prev_dept_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "dept_number" varchar(128) COLLATE "pg_catalog"."default" NOT NULL, "stl_earnings" numeric(32,2) NOT NULL, "reality_stm" numeric(32,2) NOT NULL, "gift_stm" numeric(32,2) NOT NULL, "total_stm" numeric(32,2) NOT NULL, "date" int4 NOT NULL, "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, "update_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "room_dept_basis_data_pkey" PRIMARY KEY ("no_"), CONSTRAINT "unique_room_dept_basis_data_id" UNIQUE ("id") ); ALTER TABLE "public"."room_dept_basis_data" OWNER TO "root"; -- 厅数据表主键自增 CREATE SEQUENCE table_room_dept_basis_data_no_seq INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 99999999 CACHE 1; ALTER TABLE "room_dept_basis_data" ALTER COLUMN no_ SET DEFAULT nextval( 'table_room_dept_basis_data_no_seq' ); -- 厅数据表ID索引 CREATE UNIQUE INDEX "room_dept_basis_data_no" ON "public"."room_dept_basis_data" USING btree ("no_" "pg_catalog"."int4_ops" ASC NULLS LAST); -- 业务账号基础数据表ID索引 CREATE UNIQUE INDEX "room_dept_basis_data_id" ON "public"."biz_acct_basis_data" USING btree ("id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 厅数据表日期索引 CREATE INDEX "room_dept_basis_data_date_index" ON "public"."room_dept_basis_data" USING btree ("date" "pg_catalog"."int4_ops" ASC NULLS LAST); -- 厅数据表部门索引 CREATE INDEX "room_dept_basis_data_dept_id_index" ON "public"."room_dept_basis_data" USING btree ("dept_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 上级部门索引 CREATE INDEX "room_dept_basis_data_prev_dept_id_index" ON "public"."room_dept_basis_data" USING btree ("prev_dept_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST); -- 厅数据表字段注释 COMMENT ON COLUMN "room_dept_basis_data"."no_" IS '主键'; COMMENT ON COLUMN "room_dept_basis_data"."id" IS 'ID'; COMMENT ON COLUMN "room_dept_basis_data"."dept_id" IS '部门ID'; COMMENT ON COLUMN "room_dept_basis_data"."prev_dept_id" IS '上级部门ID'; COMMENT ON COLUMN "room_dept_basis_data"."dept_number" IS '部门号码'; COMMENT ON COLUMN "room_dept_basis_data"."stl_earnings" IS '结算收益'; COMMENT ON COLUMN "room_dept_basis_data"."reality_stm" IS '真实流水'; COMMENT ON COLUMN "room_dept_basis_data"."gift_stm" IS '礼物流水'; COMMENT ON COLUMN "room_dept_basis_data"."total_stm" IS '总流水'; COMMENT ON COLUMN "room_dept_basis_data"."date" IS '日期'; COMMENT ON COLUMN "room_dept_basis_data"."creation_time" IS '创建时间'; COMMENT ON COLUMN "room_dept_basis_data"."update_time" IS '更新时间'; -- 厅数据表唯一字段字段注释 COMMENT ON CONSTRAINT "unique_room_dept_basis_data_id" ON "public"."room_dept_basis_data" IS 'ID唯一'; -- 设置 厅数据表触发器 CREATE TRIGGER update_room_dept_basis_data_update_at BEFORE UPDATE ON room_dept_basis_data FOR EACH ROW EXECUTE PROCEDURE update_row_update_time(); </code></pre>

页面列表

ITEM_HTML