create_tables.sql
6.46 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
CREATE TABLE IF NOT EXISTS "im_private_message" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"client_message_id" varchar(64) DEFAULT NULL COMMENT '客户端消息编号',
"sender_id" bigint NOT NULL COMMENT '发送人编号',
"receiver_id" bigint NOT NULL COMMENT '接收人编号',
"type" smallint NOT NULL COMMENT '消息类型',
"content" varchar(4096) DEFAULT NULL COMMENT '消息内容',
"status" tinyint NOT NULL COMMENT '消息状态',
"send_time" timestamp NOT NULL COMMENT '发送时间',
"creator" varchar(64) DEFAULT '',
"create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updater" varchar(64) DEFAULT '',
"update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"deleted" bit NOT NULL DEFAULT FALSE,
"tenant_id" bigint NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
) COMMENT 'IM 私聊消息表';
CREATE TABLE IF NOT EXISTS "im_group_message" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"client_message_id" varchar(64) DEFAULT NULL COMMENT '客户端消息编号',
"sender_id" bigint NOT NULL COMMENT '发送人编号',
"group_id" bigint NOT NULL COMMENT '群编号',
"type" smallint NOT NULL COMMENT '消息类型',
"content" varchar(4096) DEFAULT NULL COMMENT '消息内容',
"status" tinyint NOT NULL COMMENT '消息状态',
"send_time" timestamp NOT NULL COMMENT '发送时间',
"receiver_user_ids" varchar(1024) DEFAULT NULL COMMENT '定向接收用户编号列表',
"at_user_ids" varchar(1024) DEFAULT NULL COMMENT '@ 目标用户编号列表',
"receipt_status" tinyint NOT NULL DEFAULT 0 COMMENT '回执状态',
"creator" varchar(64) DEFAULT '',
"create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updater" varchar(64) DEFAULT '',
"update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"deleted" bit NOT NULL DEFAULT FALSE,
"tenant_id" bigint NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
) COMMENT 'IM 群聊消息表';
CREATE TABLE IF NOT EXISTS "im_group" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"name" varchar(64) NOT NULL COMMENT '群名称',
"owner_user_id" bigint NOT NULL COMMENT '群主用户编号',
"avatar" varchar(512) DEFAULT NULL COMMENT '群头像',
"notice" varchar(2048) DEFAULT NULL COMMENT '群公告',
"banned" bit DEFAULT FALSE COMMENT '是否封禁',
"banned_reason" varchar(512) DEFAULT NULL COMMENT '封禁原因',
"banned_time" timestamp DEFAULT NULL COMMENT '封禁时间',
"status" tinyint NOT NULL COMMENT '群状态',
"dissolved_time" timestamp DEFAULT NULL COMMENT '解散时间',
"muted_all" bit DEFAULT FALSE COMMENT '是否全群禁言',
"pinned_message_ids" varchar(128) DEFAULT NULL COMMENT '群置顶消息编号列表，逗号分隔',
"creator" varchar(64) DEFAULT '',
"create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updater" varchar(64) DEFAULT '',
"update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"deleted" bit NOT NULL DEFAULT FALSE,
"tenant_id" bigint NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
) COMMENT 'IM 群信息表';
CREATE TABLE IF NOT EXISTS "im_group_member" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"group_id" bigint NOT NULL COMMENT '群编号',
"user_id" bigint NOT NULL COMMENT '用户编号',
"display_user_name" varchar(64) DEFAULT NULL COMMENT '组内显示名',
"group_remark" varchar(64) DEFAULT NULL COMMENT '群备注',
"silent" bit DEFAULT FALSE COMMENT '是否免打扰',
"status" tinyint NOT NULL COMMENT '成员状态',
"role" tinyint NOT NULL DEFAULT 3 COMMENT '成员角色：1=群主 2=管理员 3=普通成员',
"join_time" timestamp DEFAULT NULL COMMENT '入群时间',
"quit_time" timestamp DEFAULT NULL COMMENT '退群时间',
"mute_end_time" timestamp DEFAULT NULL COMMENT '禁言到期时间',
"creator" varchar(64) DEFAULT '',
"create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updater" varchar(64) DEFAULT '',
"update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"deleted" bit NOT NULL DEFAULT FALSE,
"tenant_id" bigint NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
) COMMENT 'IM 群成员表';
CREATE TABLE IF NOT EXISTS "im_friend" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"user_id" bigint NOT NULL COMMENT '用户编号',
"friend_user_id" bigint NOT NULL COMMENT '好友用户编号',
"silent" bit DEFAULT FALSE COMMENT '是否免打扰',
"display_name" varchar(64) NOT NULL DEFAULT '' COMMENT '好友展示备注（仅自己可见）',
"add_source" tinyint DEFAULT NULL COMMENT '添加来源',
"pinned" bit DEFAULT FALSE COMMENT '是否置顶联系人',
"blocked" bit DEFAULT FALSE COMMENT '是否拉黑',
"status" tinyint NOT NULL COMMENT '好友状态',
"add_time" timestamp DEFAULT NULL COMMENT '添加好友时间',
"delete_time" timestamp DEFAULT NULL COMMENT '删除好友时间',
"creator" varchar(64) DEFAULT '',
"create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updater" varchar(64) DEFAULT '',
"update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"deleted" bit NOT NULL DEFAULT FALSE,
"tenant_id" bigint NOT NULL DEFAULT 0,
PRIMARY KEY ("id"),
CONSTRAINT "uk_user_friend" UNIQUE ("user_id", "friend_user_id", "tenant_id")
) COMMENT 'IM 好友关系表';
CREATE TABLE IF NOT EXISTS "im_friend_request" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"from_user_id" bigint NOT NULL COMMENT '发起方用户编号',
"to_user_id" bigint NOT NULL COMMENT '接收方用户编号',
"handle_result" tinyint NOT NULL DEFAULT 0 COMMENT '处理结果；0未处理；1同意；2拒绝',
"apply_content" varchar(255) DEFAULT NULL COMMENT '申请理由',
"handle_content" varchar(255) DEFAULT NULL COMMENT '处理理由',
"display_name" varchar(64) DEFAULT NULL COMMENT '发起方对接收方的备注',
"add_source" tinyint DEFAULT NULL COMMENT '添加来源',
"handle_time" timestamp DEFAULT NULL COMMENT '处理时间',
"creator" varchar(64) DEFAULT '',
"create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"updater" varchar(64) DEFAULT '',
"update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
"deleted" bit NOT NULL DEFAULT FALSE,
"tenant_id" bigint NOT NULL DEFAULT 0,
PRIMARY KEY ("id")
) COMMENT 'IM 好友申请记录表';