137 lines
6.6 KiB
SQL
137 lines
6.6 KiB
SQL
CREATE TABLE appinfo (
|
||
appid VARCHAR(64) NOT NULL DEFAULT '',
|
||
appname VARCHAR(64) NOT NULL DEFAULT '',
|
||
appsecret VARCHAR(64) NOT NULL DEFAULT '',
|
||
createtime BIGINT NOT NULL DEFAULT 0,
|
||
updatetime BIGINT NOT NULL DEFAULT 0,
|
||
status SMALLINT NOT NULL DEFAULT 10,
|
||
checkfriend SMALLINT NOT NULL DEFAULT 0,
|
||
checkblack SMALLINT NOT NULL DEFAULT 0,
|
||
machineretract SMALLINT NOT NULL DEFAULT 0,
|
||
hasnext SMALLINT NOT NULL DEFAULT 0,
|
||
PRIMARY KEY (appid)
|
||
);
|
||
COMMENT ON TABLE appinfo IS '平台信息表';
|
||
COMMENT ON COLUMN appinfo.appid IS '[平台标识]';
|
||
COMMENT ON COLUMN appinfo.appname IS '[平台名称]';
|
||
COMMENT ON COLUMN appinfo.appsecret IS '[校验码]';
|
||
COMMENT ON COLUMN appinfo.createtime IS '[创建时间]';
|
||
COMMENT ON COLUMN appinfo.updatetime IS '[修改时间]';
|
||
COMMENT ON COLUMN appinfo.status IS '[状态]10正常, 40停用，80删除';
|
||
COMMENT ON COLUMN appinfo.checkfriend IS '[是否检查好友关系，1是，0否]';
|
||
COMMENT ON COLUMN appinfo.checkblack IS '[是否检查拉黑关系，1是，0否]';
|
||
COMMENT ON COLUMN appinfo.machineretract IS '[是否需要机审撤回处理，1是，0否]';
|
||
COMMENT ON COLUMN appinfo.hasnext IS '[是否有下一步流程，1是，0否]';
|
||
|
||
CREATE TABLE channelmessage (
|
||
messageid VARCHAR(64) NOT NULL DEFAULT '',
|
||
content TEXT NOT NULL,
|
||
appid VARCHAR(8) NOT NULL DEFAULT '',
|
||
channeltype VARCHAR(15) NOT NULL DEFAULT '',
|
||
channelvalue VARCHAR(64) NOT NULL DEFAULT '',
|
||
senduserid INT NOT NULL DEFAULT 0,
|
||
createtime BIGINT NOT NULL DEFAULT 0,
|
||
status SMALLINT NOT NULL DEFAULT 10,
|
||
type VARCHAR(255) NOT NULL DEFAULT '',
|
||
title VARCHAR(255) NOT NULL DEFAULT '',
|
||
attachment VARCHAR(2048) NOT NULL DEFAULT '',
|
||
PRIMARY KEY (messageid)
|
||
);
|
||
COMMENT ON TABLE channelmessage IS '频道内容表';
|
||
COMMENT ON COLUMN channelmessage.messageid IS '[消息ID]';
|
||
COMMENT ON COLUMN channelmessage.content IS '[消息内容]';
|
||
COMMENT ON COLUMN channelmessage.appid IS '[平台ID]';
|
||
COMMENT ON COLUMN channelmessage.channeltype IS '[平台频道类型]';
|
||
COMMENT ON COLUMN channelmessage.channelvalue IS '[平台频道ID]';
|
||
COMMENT ON COLUMN channelmessage.senduserid IS '[发送人]';
|
||
COMMENT ON COLUMN channelmessage.createtime IS '[创建时间]';
|
||
COMMENT ON COLUMN channelmessage.status IS '[状态]80删除';
|
||
COMMENT ON COLUMN channelmessage.type IS '[消息类型]';
|
||
COMMENT ON COLUMN channelmessage.title IS '[消息标题]';
|
||
COMMENT ON COLUMN channelmessage.attachment IS '[消息附加信息]';
|
||
|
||
CREATE TABLE channeluser (
|
||
cuid VARCHAR(64) NOT NULL DEFAULT '',
|
||
appid VARCHAR(64) NOT NULL DEFAULT '',
|
||
channeltype VARCHAR(15) NOT NULL DEFAULT '',
|
||
channelvalue VARCHAR(64) NOT NULL DEFAULT '',
|
||
userid INT NOT NULL DEFAULT 0,
|
||
createtime BIGINT NOT NULL DEFAULT 0,
|
||
lastaccepttime BIGINT NOT NULL DEFAULT 0,
|
||
status SMALLINT NOT NULL DEFAULT 10,
|
||
identitytype SMALLINT NOT NULL DEFAULT 10,
|
||
PRIMARY KEY (cuid)
|
||
);
|
||
COMMENT ON TABLE channeluser IS '用户组信息表';
|
||
COMMENT ON COLUMN channeluser.cuid IS '[用户组ID]';
|
||
COMMENT ON COLUMN channeluser.appid IS '[平台ID]';
|
||
COMMENT ON COLUMN channeluser.channeltype IS '[平台频道类型]';
|
||
COMMENT ON COLUMN channeluser.channelvalue IS '[平台频道ID]';
|
||
COMMENT ON COLUMN channeluser.userid IS '[订阅用户]';
|
||
COMMENT ON COLUMN channeluser.createtime IS '[创建时间]';
|
||
COMMENT ON COLUMN channeluser.lastaccepttime IS '[最后接收时间]';
|
||
COMMENT ON COLUMN channeluser.status IS '[状态]10正常，42禁言';
|
||
COMMENT ON COLUMN channeluser.identitytype IS '[用户身份，0游客，10成员，20管理员，30群主]';
|
||
|
||
CREATE TABLE friendmessage (
|
||
messageid VARCHAR(64) NOT NULL DEFAULT '',
|
||
messagetype VARCHAR(32) NOT NULL DEFAULT '',
|
||
content TEXT NOT NULL,
|
||
senduserid INT NOT NULL DEFAULT 0,
|
||
receiveuserid INT NOT NULL DEFAULT 0,
|
||
createtime BIGINT NOT NULL DEFAULT 0,
|
||
status SMALLINT NOT NULL DEFAULT 20,
|
||
type VARCHAR(255) NOT NULL DEFAULT '',
|
||
title VARCHAR(255) NOT NULL DEFAULT '',
|
||
attachment VARCHAR(2048) NOT NULL DEFAULT '',
|
||
PRIMARY KEY (messageid)
|
||
);
|
||
COMMENT ON TABLE friendmessage IS '私聊内容表';
|
||
COMMENT ON COLUMN friendmessage.messageid IS '[私聊ID]发送人-时间戳';
|
||
COMMENT ON COLUMN friendmessage.messagetype IS '[消息类型]';
|
||
COMMENT ON COLUMN friendmessage.content IS '[私聊内容]';
|
||
COMMENT ON COLUMN friendmessage.senduserid IS '[发送人]';
|
||
COMMENT ON COLUMN friendmessage.receiveuserid IS '[接收人]';
|
||
COMMENT ON COLUMN friendmessage.createtime IS '[创建时间]';
|
||
COMMENT ON COLUMN friendmessage.status IS '[状态]10已发送，20未发送，21仅在线发送';
|
||
COMMENT ON COLUMN friendmessage.type IS '[消息类型]';
|
||
COMMENT ON COLUMN friendmessage.title IS '[消息标题]';
|
||
COMMENT ON COLUMN friendmessage.attachment IS '[消息附加信息]';
|
||
|
||
CREATE TABLE imuser (
|
||
userid INT NOT NULL,
|
||
guserid VARCHAR(64) NOT NULL DEFAULT '',
|
||
appid VARCHAR(8) NOT NULL DEFAULT '',
|
||
gender SMALLINT NOT NULL DEFAULT 0,
|
||
nickname VARCHAR(32) NOT NULL DEFAULT '',
|
||
face VARCHAR(128) NOT NULL DEFAULT '',
|
||
createtime BIGINT NOT NULL DEFAULT 0,
|
||
imtoken VARCHAR(125) DEFAULT '',
|
||
updatetime BIGINT NOT NULL DEFAULT 0,
|
||
lastaccepttime BIGINT NOT NULL DEFAULT 0,
|
||
status SMALLINT NOT NULL DEFAULT 10,
|
||
PRIMARY KEY (userid)
|
||
);
|
||
COMMENT ON TABLE imuser IS 'IM用户';
|
||
COMMENT ON COLUMN imuser.userid IS '[用户标识]';
|
||
COMMENT ON COLUMN imuser.guserid IS '[平台自用用户标识]';
|
||
COMMENT ON COLUMN imuser.appid IS '[关联平台标识]';
|
||
COMMENT ON COLUMN imuser.gender IS '[性别]';
|
||
COMMENT ON COLUMN imuser.nickname IS '[昵称]';
|
||
COMMENT ON COLUMN imuser.face IS '[头像]';
|
||
COMMENT ON COLUMN imuser.createtime IS '[创建时间]';
|
||
COMMENT ON COLUMN imuser.imtoken IS '[用户认证]';
|
||
COMMENT ON COLUMN imuser.updatetime IS '[修改时间]';
|
||
COMMENT ON COLUMN imuser.lastaccepttime IS '[接收时间]';
|
||
COMMENT ON COLUMN imuser.status IS '[状态]10正常，40停用，80删除';
|
||
|
||
--
|
||
INSERT INTO z_im.appinfo (appid, appname, appsecret, createtime, updatetime, status, checkfriend, checkblack, machineretract, hasnext) VALUES
|
||
('krlq2lp4', '应用A-开发服', '108319232efb4880bcb4f3323829692c', 0, 0, 10, 0, 0, 0, 0),
|
||
('xrkqglth', '应用A-测试服', '108319232efb4880bcb4f3323829692d', 0, 0, 10, 0, 0, 0, 0);
|
||
|
||
INSERT INTO imuser (userid, guserid, appid, gender, nickname, face, createtime, imtoken, updatetime, lastaccepttime, status) VALUES
|
||
(10001, '1784509137732849665', 'xrkqglth', 0, '', '', 1721373487374, 'c75ab6feda394d8c8b28371cda45ec12', 1748780429, 1748780429, 10),
|
||
(10002, '1784421470808907778', 'xrkqglth', 0, '', '', 1721377122233, '69ca1dcbebc645b78f84a9e88287382d', 1748780432, 1748780432, 10);
|
||
|