嗨玩后端开发规范

数据库表调整记录


QQ机器人v2.1版本

-- 语音房排行统计基础数据
CREATE TABLE "audio_duration" (
    "no_" serial PRIMARY KEY,
    "gid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
    "cid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
    "bot_uid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
    "name" VARCHAR COLLATE "pg_catalog"."default",
    "picture" VARCHAR COLLATE "pg_catalog"."default",
    "in_time" int4 NOT NULL,
    "out_time" int4 NOT NULL,
    "duration" int4 NOT NULL,
    "is_available" int4 NOT NULL DEFAULT 1,
    "creation_time" TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_time" TIMESTAMP ( 0 )
);
ALTER TABLE "audio_duration" OWNER TO "haiwan";
-- 索引
CREATE INDEX "audio_duration_in_time_index" ON "audio_duration" USING btree ( "in_time" "pg_catalog"."int4_ops" ASC NULLS LAST );
CREATE INDEX "audio_duration_out_time_index" ON "audio_duration" USING btree ( "out_time" "pg_catalog"."int4_ops" ASC NULLS LAST );
CREATE INDEX "audio_duration_gid_index" ON "audio_duration" USING btree ( "gid" "pg_catalog"."varchar_ops" ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN "public"."audio_duration"."gid" IS '频道ID';
COMMENT ON COLUMN "public"."audio_duration"."cid" IS '子频道ID';
COMMENT ON COLUMN "public"."audio_duration"."bot_uid" IS '频道用户ID';
COMMENT ON COLUMN "public"."audio_duration"."name" IS '用户昵称';
COMMENT ON COLUMN "public"."audio_duration"."picture" IS '头像';
COMMENT ON COLUMN "public"."audio_duration"."in_time" IS '开始时间';
COMMENT ON COLUMN "public"."audio_duration"."out_time" IS '结束时间';
COMMENT ON COLUMN "public"."audio_duration"."duration" IS '时长(分钟)';
CREATE TRIGGER "update_at" BEFORE UPDATE ON "audio_duration" FOR EACH ROW
EXECUTE PROCEDURE "update_timestamp" ();

-- 语音记录表
CREATE TABLE "audio_duration_prepare" (
    "no_" serial PRIMARY KEY,
    "gid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
    "cid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
    "bot_uid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
    "name" VARCHAR COLLATE "pg_catalog"."default",
    "picture" VARCHAR COLLATE "pg_catalog"."default",
    "in_time" int4 NOT NULL,
    "out_time" int4,
    "is_available" int4 NOT NULL DEFAULT 1,
    "creation_time" TIMESTAMP (0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "update_time" TIMESTAMP (0)
);
ALTER TABLE "audio_duration_prepare" OWNER TO "haiwan";
-- 索引
CREATE INDEX "audio_duration_prepare_gid_index" ON "audio_duration_prepare" USING btree ( "gid" "pg_catalog"."varchar_ops" ASC NULLS LAST );
CREATE INDEX "audio_duration_prepare_bot_uid_index" ON "audio_duration_prepare" USING btree ( "bot_uid" "pg_catalog"."varchar_ops" ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN "public"."audio_duration_prepare"."gid" IS '频道ID';
COMMENT ON COLUMN "public"."audio_duration_prepare"."cid" IS '子频道ID';
COMMENT ON COLUMN "public"."audio_duration_prepare"."bot_uid" IS '频道用户ID';
COMMENT ON COLUMN "public"."audio_duration_prepare"."name" IS '用户昵称';
COMMENT ON COLUMN "public"."audio_duration_prepare"."picture" IS '头像';
COMMENT ON COLUMN "public"."audio_duration_prepare"."in_time" IS '加入时间';
COMMENT ON COLUMN "public"."audio_duration_prepare"."out_time" IS '离开时间';
CREATE TRIGGER "update_at" BEFORE UPDATE ON "audio_duration_prepare" FOR EACH ROW
EXECUTE PROCEDURE "update_timestamp" ();

-- 频道用户记录
CREATE TABLE "guild_user" (
"no_" serial PRIMARY KEY,
"id" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"gid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"uid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"bot_uid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"uname" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"nick" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"avatar" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"roles" json,
"is_available" int4 NOT NULL DEFAULT 1,
"creation_time" TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"update_time" TIMESTAMP ( 0 )
);
ALTER TABLE "guild_user" OWNER TO "haiwan";
-- 索引
CREATE UNIQUE INDEX "guild_user_no_index" ON "guild_user" USING btree ( "no_" "pg_catalog"."int4_ops" ASC NULLS LAST );
CREATE UNIQUE INDEX "guild_user_id_index" ON "guild_user" USING btree ( "id" "pg_catalog"."varchar_ops" ASC NULLS LAST );
CREATE INDEX "guild_user_gid_and_uid_index" ON "guild_user" USING btree ( "gid" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST, "uid" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN "public"."guild_user"."gid" IS '频道 ID';
COMMENT ON COLUMN "public"."guild_user"."uid" IS '系统用户 ID';
COMMENT ON COLUMN "public"."guild_user"."bot_uid" IS '频道用户 ID';
COMMENT ON COLUMN "public"."guild_user"."uname" IS '用户昵称';
COMMENT ON COLUMN "public"."guild_user"."nick" IS '用户频道昵称';
COMMENT ON COLUMN "public"."guild_user"."avatar" IS '用户头像';
COMMENT ON COLUMN "public"."guild_user"."roles" IS '用户角色';
CREATE TRIGGER "update_at" BEFORE UPDATE ON "guild_user" FOR EACH ROW
EXECUTE PROCEDURE "update_timestamp" ( );

-- 用户授权表
CREATE TABLE "user_permission" (
"no_" serial PRIMARY KEY,
"id" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"gid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"uid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"bot_uid" VARCHAR COLLATE "pg_catalog"."default" NOT NULL,
"content" json,
"is_available" int4 NOT NULL DEFAULT 1,
"creation_time" TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP,
"update_time" TIMESTAMP ( 0 )
);
ALTER TABLE "user_permission" OWNER TO "haiwan";
-- 索引
CREATE UNIQUE INDEX "user_permission_no_index" ON "user_permission" USING btree ( "no_" "pg_catalog"."int4_ops" ASC NULLS LAST );
CREATE UNIQUE INDEX "user_permission_id_index" ON "user_permission" USING btree ( "id" "pg_catalog"."varchar_ops" ASC NULLS LAST );
CREATE INDEX "user_permission_gid_and_uid_index" ON "user_permission" USING btree ( "gid" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST, "uid" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN "public"."user_permission"."gid" IS '频道 ID';
COMMENT ON COLUMN "public"."user_permission"."uid" IS '系统用户 ID';
COMMENT ON COLUMN "public"."user_permission"."bot_uid" IS '频道用户 ID';
COMMENT ON COLUMN "public"."user_permission"."content" IS '用户角色';
CREATE TRIGGER "update_at" BEFORE UPDATE ON "user_permission" FOR EACH ROW EXECUTE PROCEDURE "update_timestamp" ( );

-- 积分商城配置
CREATE TABLE "public"."points_mall_setting" (
  "no_" serial primary key,
  "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(0),
  "is_available" int4 NOT NULL DEFAULT 1,
  "gid" varchar(64) COLLATE "pg_catalog"."default",
  "open_status" int4,
  "cover_img" varchar(255) COLLATE "pg_catalog"."default",
  "share_title" varchar(255) COLLATE "pg_catalog"."default",
  "share_img" varchar(255) COLLATE "pg_catalog"."default",
  "inventory_status" int4,
  "total_class_status" int4
);
ALTER TABLE "public"."points_mall_setting" OWNER TO "haiwan";
CREATE TRIGGER "update_at" BEFORE
UPDATE ON "points_mall_setting" FOR EACH ROW EXECUTE PROCEDURE "update_timestamp"();
-- 注释
COMMENT ON COLUMN "public"."points_mall_setting"."gid" IS '频道 id';
COMMENT ON COLUMN "public"."points_mall_setting"."open_status" IS '1 未开启 2 已开启';
COMMENT ON COLUMN "public"."points_mall_setting"."cover_img" IS '商城封面设置';
COMMENT ON COLUMN "public"."points_mall_setting"."share_title" IS '商城分享标题';
COMMENT ON COLUMN "public"."points_mall_setting"."share_img" IS '商城分享图片';
COMMENT ON COLUMN "public"."points_mall_setting"."inventory_status" IS '显示商品库存 1 不显示 2 显示';
COMMENT ON COLUMN "public"."points_mall_setting"."total_class_status" IS '隐藏全部分类  1 隐藏 2 显示';
-- 积分商城商品分类
CREATE TABLE "public"."points_mall_type" (
  "no_" serial primary key,
  "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(0),
  "is_available" int4 NOT NULL DEFAULT 1,
  "id" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "gid" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "name" varchar(64) COLLATE "pg_catalog"."default" NOT NULL,
  "sort" int4 NOT NULL
);
ALTER TABLE "public"."points_mall_type" OWNER TO "haiwan";
CREATE TRIGGER "update_at" BEFORE
UPDATE ON "points_mall_type" FOR EACH ROW EXECUTE PROCEDURE "update_timestamp"();
-- 注释
COMMENT ON COLUMN "public"."points_mall_type"."gid" IS '频道 id';
COMMENT ON COLUMN "public"."points_mall_type"."name" IS '分类名称';
COMMENT ON COLUMN "public"."points_mall_type"."sort" IS '排序';
-- 积分商城奖品
CREATE TABLE "public"."points_mall_prizes" (
  "no_" serial primary key,
  "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(0),
  "is_available" int4 NOT NULL DEFAULT 1,
  "id" varchar(64) COLLATE "pg_catalog"."default",
  "gid" varchar(64) COLLATE "pg_catalog"."default",
  "prize_img" varchar(255) COLLATE "pg_catalog"."default",
  "kind" int4,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "surplus" int4,
  "day_most" int4,
  "expend_point" int4,
  "upper_limit" json,
  "prize_type_id" varchar(64) COLLATE "pg_catalog"."default",
  "put_on" int4,
  "introduce" json,
  "exchange_group" json,
  "write_info" json,
  "other" json,
  "sort" int4
);
ALTER TABLE "public"."points_mall_prizes" OWNER TO "haiwan";
CREATE TRIGGER "update_at" BEFORE
UPDATE ON "points_mall_prizes" FOR EACH ROW EXECUTE PROCEDURE "update_timestamp"();
-- 注释
COMMENT ON COLUMN "public"."points_mall_prizes"."gid" IS '频道 id';
COMMENT ON COLUMN "public"."points_mall_prizes"."prize_img" IS '奖品图片';
COMMENT ON COLUMN "public"."points_mall_prizes"."kind" IS '种类 1 自定义 2 现金红包 3 身分组 4 兑换码 5 补签卡';
COMMENT ON COLUMN "public"."points_mall_prizes"."name" IS '商品名称';
COMMENT ON COLUMN "public"."points_mall_prizes"."surplus" IS '商品剩余库存';
COMMENT ON COLUMN "public"."points_mall_prizes"."day_most" IS '每日最多兑换';
COMMENT ON COLUMN "public"."points_mall_prizes"."expend_point" IS '兑换消耗多少积分';
COMMENT ON COLUMN "public"."points_mall_prizes"."upper_limit" IS '用户兑换上限';
COMMENT ON COLUMN "public"."points_mall_prizes"."prize_type_id" IS '商城种类 id ';
COMMENT ON COLUMN "public"."points_mall_prizes"."put_on" IS '1:未上架 2:已上架';
COMMENT ON COLUMN "public"."points_mall_prizes"."introduce" IS '商品介绍';
COMMENT ON COLUMN "public"."points_mall_prizes"."exchange_group" IS '兑换身分组限制';
COMMENT ON COLUMN "public"."points_mall_prizes"."write_info" IS '需要填写得信息名称 为空是不填写';
COMMENT ON COLUMN "public"."points_mall_prizes"."other" IS '其他信息';
COMMENT ON COLUMN "public"."points_mall_prizes"."sort" IS '排序';
-- 积分商城奖品兑换记录
CREATE TABLE "public"."points_mall_exchange" (
  "no_" serial primary key,
  "creation_time" timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time" timestamp(0),
  "is_available" int4 NOT NULL DEFAULT 1,
  "id" varchar(64) COLLATE "pg_catalog"."default",
  "gid" varchar(64) COLLATE "pg_catalog"."default",
  "prizes_id" varchar(64) COLLATE "pg_catalog"."default",
  "bot_uid" varchar(64) COLLATE "pg_catalog"."default",
  "exchange_time" int4,
  "verify_status" int4 NOT NULL DEFAULT 1,
  "write_info" json,
  "other" json
);
ALTER TABLE "public"."points_mall_exchange" OWNER TO "haiwan";
CREATE TRIGGER "update_at" BEFORE
UPDATE ON "points_mall_prizes" FOR EACH ROW EXECUTE PROCEDURE "update_timestamp"();
-- 注释
COMMENT ON COLUMN "public"."points_mall_exchange"."gid" IS '频道 id';
COMMENT ON COLUMN "public"."points_mall_exchange"."prizes_id" IS '奖品 id';
COMMENT ON COLUMN "public"."points_mall_exchange"."bot_uid" IS '兑奖用户 id';
COMMENT ON COLUMN "public"."points_mall_exchange"."exchange_time" IS '兑奖时间';
COMMENT ON COLUMN "public"."points_mall_exchange"."verify_status" IS '1 未核销 2 已核销';
COMMENT ON COLUMN "public"."points_mall_exchange"."write_info" IS '兑奖填写信息';

页面列表

ITEM_HTML