create_tables.sql
10.5 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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
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(8192) 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(8192) 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 '是否全群禁言',
"join_approval" bit NOT NULL DEFAULT FALSE COMMENT '进群是否需群主 / 管理员审批；false 自由进群，true 需审批',
"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 '入群时间',
"add_source" tinyint DEFAULT NULL COMMENT '加入来源',
"inviter_user_id" bigint DEFAULT NULL COMMENT '邀请人用户编号；用户主动申请进群时为 NULL',
"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"),
CONSTRAINT "uk_im_friend_request" UNIQUE ("from_user_id", "to_user_id", "tenant_id")
) COMMENT 'IM 好友申请记录表';
CREATE TABLE IF NOT EXISTS "im_group_request" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"group_id" bigint NOT NULL COMMENT '群编号',
"user_id" bigint NOT NULL COMMENT '申请人 / 被邀请人用户编号',
"inviter_user_id" bigint DEFAULT NULL COMMENT '邀请人用户编号；NULL=主动申请；非NULL=被邀请待审批',
"apply_content" varchar(255) DEFAULT NULL COMMENT '申请理由',
"add_source" tinyint DEFAULT NULL COMMENT '加入来源',
"handle_result" tinyint NOT NULL DEFAULT 0 COMMENT '处理结果；0未处理；1同意；2拒绝',
"handle_user_id" bigint DEFAULT NULL COMMENT '处理人用户编号',
"handle_content" varchar(255) 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"),
CONSTRAINT "uk_im_group_request" UNIQUE ("group_id", "user_id", "tenant_id")
) COMMENT 'IM 加群申请记录表';
CREATE TABLE IF NOT EXISTS "im_face_pack" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"name" varchar(64) NOT NULL COMMENT '表情包名称',
"icon" varchar(512) DEFAULT NULL COMMENT '表情包图标（面板底部 tab 显示）',
"sort" int NOT NULL DEFAULT 0 COMMENT '排序',
"status" tinyint 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_face_pack_item" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"pack_id" bigint NOT NULL COMMENT '所属表情包编号',
"url" varchar(512) NOT NULL COMMENT '表情图 URL',
"name" varchar(64) DEFAULT NULL COMMENT '表情名（可选；如「狗头」「捂脸」）',
"width" int NOT NULL DEFAULT 0 COMMENT '渲染宽度（像素）',
"height" int NOT NULL DEFAULT 0 COMMENT '渲染高度（像素）',
"sort" int NOT NULL DEFAULT 0 COMMENT '排序',
"status" tinyint 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_face_user_item" (
"id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY COMMENT '编号',
"user_id" bigint NOT NULL COMMENT '所属用户编号',
"url" varchar(512) NOT NULL COMMENT '表情图 URL',
"name" varchar(64) DEFAULT NULL COMMENT '表情名（可选）',
"width" int NOT NULL DEFAULT 0 COMMENT '渲染宽度（像素）',
"height" int NOT NULL DEFAULT 0 COMMENT '渲染高度（像素）',
"sort" int 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 用户私有表情表';