QQ机器人v2.1版本
<pre><code class="language-sql">-- 语音房排行统计基础数据
CREATE TABLE &quot;audio_duration&quot; (
&quot;no_&quot; serial PRIMARY KEY,
&quot;gid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;cid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;bot_uid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;name&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;picture&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;in_time&quot; int4 NOT NULL,
&quot;out_time&quot; int4 NOT NULL,
&quot;duration&quot; int4 NOT NULL,
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;creation_time&quot; TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; TIMESTAMP ( 0 )
);
ALTER TABLE &quot;audio_duration&quot; OWNER TO &quot;haiwan&quot;;
-- 索引
CREATE INDEX &quot;audio_duration_in_time_index&quot; ON &quot;audio_duration&quot; USING btree ( &quot;in_time&quot; &quot;pg_catalog&quot;.&quot;int4_ops&quot; ASC NULLS LAST );
CREATE INDEX &quot;audio_duration_out_time_index&quot; ON &quot;audio_duration&quot; USING btree ( &quot;out_time&quot; &quot;pg_catalog&quot;.&quot;int4_ops&quot; ASC NULLS LAST );
CREATE INDEX &quot;audio_duration_gid_index&quot; ON &quot;audio_duration&quot; USING btree ( &quot;gid&quot; &quot;pg_catalog&quot;.&quot;varchar_ops&quot; ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;gid&quot; IS '频道ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;cid&quot; IS '子频道ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;bot_uid&quot; IS '频道用户ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;name&quot; IS '用户昵称';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;picture&quot; IS '头像';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;in_time&quot; IS '开始时间';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;out_time&quot; IS '结束时间';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration&quot;.&quot;duration&quot; IS '时长（分钟）';
CREATE TRIGGER &quot;update_at&quot; BEFORE UPDATE ON &quot;audio_duration&quot; FOR EACH ROW
EXECUTE PROCEDURE &quot;update_timestamp&quot; ();
-- 语音记录表
CREATE TABLE &quot;audio_duration_prepare&quot; (
&quot;no_&quot; serial PRIMARY KEY,
&quot;gid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;cid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;bot_uid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;name&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;picture&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;in_time&quot; int4 NOT NULL,
&quot;out_time&quot; int4,
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;creation_time&quot; TIMESTAMP (0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; TIMESTAMP (0)
);
ALTER TABLE &quot;audio_duration_prepare&quot; OWNER TO &quot;haiwan&quot;;
-- 索引
CREATE INDEX &quot;audio_duration_prepare_gid_index&quot; ON &quot;audio_duration_prepare&quot; USING btree ( &quot;gid&quot; &quot;pg_catalog&quot;.&quot;varchar_ops&quot; ASC NULLS LAST );
CREATE INDEX &quot;audio_duration_prepare_bot_uid_index&quot; ON &quot;audio_duration_prepare&quot; USING btree ( &quot;bot_uid&quot; &quot;pg_catalog&quot;.&quot;varchar_ops&quot; ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration_prepare&quot;.&quot;gid&quot; IS '频道ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration_prepare&quot;.&quot;cid&quot; IS '子频道ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration_prepare&quot;.&quot;bot_uid&quot; IS '频道用户ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration_prepare&quot;.&quot;name&quot; IS '用户昵称';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration_prepare&quot;.&quot;picture&quot; IS '头像';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration_prepare&quot;.&quot;in_time&quot; IS '加入时间';
COMMENT ON COLUMN &quot;public&quot;.&quot;audio_duration_prepare&quot;.&quot;out_time&quot; IS '离开时间';
CREATE TRIGGER &quot;update_at&quot; BEFORE UPDATE ON &quot;audio_duration_prepare&quot; FOR EACH ROW
EXECUTE PROCEDURE &quot;update_timestamp&quot; ();
-- 频道用户记录
CREATE TABLE &quot;guild_user&quot; (
&quot;no_&quot; serial PRIMARY KEY,
&quot;id&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;gid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;uid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;bot_uid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;uname&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;nick&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;avatar&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;roles&quot; json,
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;creation_time&quot; TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; TIMESTAMP ( 0 )
);
ALTER TABLE &quot;guild_user&quot; OWNER TO &quot;haiwan&quot;;
-- 索引
CREATE UNIQUE INDEX &quot;guild_user_no_index&quot; ON &quot;guild_user&quot; USING btree ( &quot;no_&quot; &quot;pg_catalog&quot;.&quot;int4_ops&quot; ASC NULLS LAST );
CREATE UNIQUE INDEX &quot;guild_user_id_index&quot; ON &quot;guild_user&quot; USING btree ( &quot;id&quot; &quot;pg_catalog&quot;.&quot;varchar_ops&quot; ASC NULLS LAST );
CREATE INDEX &quot;guild_user_gid_and_uid_index&quot; ON &quot;guild_user&quot; USING btree ( &quot;gid&quot; COLLATE &quot;pg_catalog&quot;.&quot;default&quot; &quot;pg_catalog&quot;.&quot;text_ops&quot; ASC NULLS LAST, &quot;uid&quot; COLLATE &quot;pg_catalog&quot;.&quot;default&quot; &quot;pg_catalog&quot;.&quot;text_ops&quot; ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN &quot;public&quot;.&quot;guild_user&quot;.&quot;gid&quot; IS '频道 ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;guild_user&quot;.&quot;uid&quot; IS '系统用户 ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;guild_user&quot;.&quot;bot_uid&quot; IS '频道用户 ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;guild_user&quot;.&quot;uname&quot; IS '用户昵称';
COMMENT ON COLUMN &quot;public&quot;.&quot;guild_user&quot;.&quot;nick&quot; IS '用户频道昵称';
COMMENT ON COLUMN &quot;public&quot;.&quot;guild_user&quot;.&quot;avatar&quot; IS '用户头像';
COMMENT ON COLUMN &quot;public&quot;.&quot;guild_user&quot;.&quot;roles&quot; IS '用户角色';
CREATE TRIGGER &quot;update_at&quot; BEFORE UPDATE ON &quot;guild_user&quot; FOR EACH ROW
EXECUTE PROCEDURE &quot;update_timestamp&quot; ( );
-- 用户授权表
CREATE TABLE &quot;user_permission&quot; (
&quot;no_&quot; serial PRIMARY KEY,
&quot;id&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;gid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;uid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;bot_uid&quot; VARCHAR COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;content&quot; json,
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;creation_time&quot; TIMESTAMP ( 0 ) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; TIMESTAMP ( 0 )
);
ALTER TABLE &quot;user_permission&quot; OWNER TO &quot;haiwan&quot;;
-- 索引
CREATE UNIQUE INDEX &quot;user_permission_no_index&quot; ON &quot;user_permission&quot; USING btree ( &quot;no_&quot; &quot;pg_catalog&quot;.&quot;int4_ops&quot; ASC NULLS LAST );
CREATE UNIQUE INDEX &quot;user_permission_id_index&quot; ON &quot;user_permission&quot; USING btree ( &quot;id&quot; &quot;pg_catalog&quot;.&quot;varchar_ops&quot; ASC NULLS LAST );
CREATE INDEX &quot;user_permission_gid_and_uid_index&quot; ON &quot;user_permission&quot; USING btree ( &quot;gid&quot; COLLATE &quot;pg_catalog&quot;.&quot;default&quot; &quot;pg_catalog&quot;.&quot;text_ops&quot; ASC NULLS LAST, &quot;uid&quot; COLLATE &quot;pg_catalog&quot;.&quot;default&quot; &quot;pg_catalog&quot;.&quot;text_ops&quot; ASC NULLS LAST );
-- 字段注释
COMMENT ON COLUMN &quot;public&quot;.&quot;user_permission&quot;.&quot;gid&quot; IS '频道 ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;user_permission&quot;.&quot;uid&quot; IS '系统用户 ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;user_permission&quot;.&quot;bot_uid&quot; IS '频道用户 ID';
COMMENT ON COLUMN &quot;public&quot;.&quot;user_permission&quot;.&quot;content&quot; IS '用户角色';
CREATE TRIGGER &quot;update_at&quot; BEFORE UPDATE ON &quot;user_permission&quot; FOR EACH ROW EXECUTE PROCEDURE &quot;update_timestamp&quot; ( );
-- 积分商城配置
CREATE TABLE &quot;public&quot;.&quot;points_mall_setting&quot; (
&quot;no_&quot; serial primary key,
&quot;creation_time&quot; timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; timestamp(0),
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;gid&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;open_status&quot; int4,
&quot;cover_img&quot; varchar(255) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;share_title&quot; varchar(255) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;share_img&quot; varchar(255) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;inventory_status&quot; int4,
&quot;total_class_status&quot; int4
);
ALTER TABLE &quot;public&quot;.&quot;points_mall_setting&quot; OWNER TO &quot;haiwan&quot;;
CREATE TRIGGER &quot;update_at&quot; BEFORE
UPDATE ON &quot;points_mall_setting&quot; FOR EACH ROW EXECUTE PROCEDURE &quot;update_timestamp&quot;();
-- 注释
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_setting&quot;.&quot;gid&quot; IS '频道 id';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_setting&quot;.&quot;open_status&quot; IS '1 未开启 2 已开启';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_setting&quot;.&quot;cover_img&quot; IS '商城封面设置';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_setting&quot;.&quot;share_title&quot; IS '商城分享标题';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_setting&quot;.&quot;share_img&quot; IS '商城分享图片';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_setting&quot;.&quot;inventory_status&quot; IS '显示商品库存 1 不显示 2 显示';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_setting&quot;.&quot;total_class_status&quot; IS '隐藏全部分类 1 隐藏 2 显示';
-- 积分商城商品分类
CREATE TABLE &quot;public&quot;.&quot;points_mall_type&quot; (
&quot;no_&quot; serial primary key,
&quot;creation_time&quot; timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; timestamp(0),
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;id&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;gid&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;name&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot; NOT NULL,
&quot;sort&quot; int4 NOT NULL
);
ALTER TABLE &quot;public&quot;.&quot;points_mall_type&quot; OWNER TO &quot;haiwan&quot;;
CREATE TRIGGER &quot;update_at&quot; BEFORE
UPDATE ON &quot;points_mall_type&quot; FOR EACH ROW EXECUTE PROCEDURE &quot;update_timestamp&quot;();
-- 注释
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_type&quot;.&quot;gid&quot; IS '频道 id';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_type&quot;.&quot;name&quot; IS '分类名称';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_type&quot;.&quot;sort&quot; IS '排序';
-- 积分商城奖品
CREATE TABLE &quot;public&quot;.&quot;points_mall_prizes&quot; (
&quot;no_&quot; serial primary key,
&quot;creation_time&quot; timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; timestamp(0),
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;id&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;gid&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;prize_img&quot; varchar(255) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;kind&quot; int4,
&quot;name&quot; varchar(255) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;surplus&quot; int4,
&quot;day_most&quot; int4,
&quot;expend_point&quot; int4,
&quot;upper_limit&quot; json,
&quot;prize_type_id&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;put_on&quot; int4,
&quot;introduce&quot; json,
&quot;exchange_group&quot; json,
&quot;write_info&quot; json,
&quot;other&quot; json,
&quot;sort&quot; int4
);
ALTER TABLE &quot;public&quot;.&quot;points_mall_prizes&quot; OWNER TO &quot;haiwan&quot;;
CREATE TRIGGER &quot;update_at&quot; BEFORE
UPDATE ON &quot;points_mall_prizes&quot; FOR EACH ROW EXECUTE PROCEDURE &quot;update_timestamp&quot;();
-- 注释
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;gid&quot; IS '频道 id';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;prize_img&quot; IS '奖品图片';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;kind&quot; IS '种类 1 自定义 2 现金红包 3 身分组 4 兑换码 5 补签卡';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;name&quot; IS '商品名称';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;surplus&quot; IS '商品剩余库存';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;day_most&quot; IS '每日最多兑换';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;expend_point&quot; IS '兑换消耗多少积分';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;upper_limit&quot; IS '用户兑换上限';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;prize_type_id&quot; IS '商城种类 id ';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;put_on&quot; IS '1:未上架 2:已上架';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;introduce&quot; IS '商品介绍';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;exchange_group&quot; IS '兑换身分组限制';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;write_info&quot; IS '需要填写得信息名称 为空是不填写';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;other&quot; IS '其他信息';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_prizes&quot;.&quot;sort&quot; IS '排序';
-- 积分商城奖品兑换记录
CREATE TABLE &quot;public&quot;.&quot;points_mall_exchange&quot; (
&quot;no_&quot; serial primary key,
&quot;creation_time&quot; timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
&quot;update_time&quot; timestamp(0),
&quot;is_available&quot; int4 NOT NULL DEFAULT 1,
&quot;id&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;gid&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;prizes_id&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;bot_uid&quot; varchar(64) COLLATE &quot;pg_catalog&quot;.&quot;default&quot;,
&quot;exchange_time&quot; int4,
&quot;verify_status&quot; int4 NOT NULL DEFAULT 1,
&quot;write_info&quot; json,
&quot;other&quot; json
);
ALTER TABLE &quot;public&quot;.&quot;points_mall_exchange&quot; OWNER TO &quot;haiwan&quot;;
CREATE TRIGGER &quot;update_at&quot; BEFORE
UPDATE ON &quot;points_mall_prizes&quot; FOR EACH ROW EXECUTE PROCEDURE &quot;update_timestamp&quot;();
-- 注释
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_exchange&quot;.&quot;gid&quot; IS '频道 id';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_exchange&quot;.&quot;prizes_id&quot; IS '奖品 id';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_exchange&quot;.&quot;bot_uid&quot; IS '兑奖用户 id';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_exchange&quot;.&quot;exchange_time&quot; IS '兑奖时间';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_exchange&quot;.&quot;verify_status&quot; IS '1 未核销 2 已核销';
COMMENT ON COLUMN &quot;public&quot;.&quot;points_mall_exchange&quot;.&quot;write_info&quot; IS '兑奖填写信息';</code></pre>