数据分析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>