数据表设计

1. im_users 用户表

CREATE TABLE im_users (
  id             BIGINT PRIMARY KEY,
  username       VARCHAR(255) NOT NULL UNIQUE,
  password       VARCHAR(255) DEFAULT NULL,
  display_name   varchar,
  avatar_url     varchar,
  background_url varchar,
  extra          JSON DEFAULT NULL,
  remark         VARCHAR(256),
  creator        VARCHAR(64) DEFAULT '',
  create_time    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater        VARCHAR(64) DEFAULT '',
  update_time    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted        SMALLINT default 0,
  tenant_id      BIGINT NOT NULL DEFAULT 0
);

2. im_messages_history 表

此表用于存储用户之间和群组之间交换的所有消息的历史记录，支持各种消息类型，并包含消息确认和序列化所需的字段。

表结构

drop table if exists im_messages_history;
CREATE TABLE im_messages_history (
  id             BIGINT  PRIMARY KEY,
  session_id     bigint not null,
  cmd            INT NOT NULL,
  sequence       BIGINT NOT NULL,
  from_user_id   varchar NOT NULL,
  to_user_id     varchar DEFAULT NULL,
  group_id       BIGINT DEFAULT NULL,
  msg_type       varchar NOT NULL,
  content        JSONB NOT NULL,
  status         smallint NOT NULL DEFAULT 1,
  timestamp      BIGINT not null,
  extra          JSON DEFAULT NULL,
  deleted        SMALLINT default 0,
  create_time    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  creator VARCHAR(64) DEFAULT '',
  tenant_id BIGINT NOT NULL DEFAULT 0
);

字段描述

• id: 每条消息的唯一标识符。
• cmd: 表示消息类型的命令代码。
• sequence: 用于跟踪和确认的唯一消息序列号。
• ack: 被确认的消息序列号（如果适用）。
• from_user_id: 发送者的用户 ID。
• to_user_id: 接收者的用户 ID（用于一对一消息）。
• group_id: 群组 ID（用于群组消息）。
• msg_type: 消息内容的类型。
• content: 存储消息内容的 JSON 字段，基于 msg_type 进行变化。
• status: 消息的状态（如发送、已送达、已读）。
• extra: 额外数据的 JSON 字段。
• create_time: 消息创建的时间戳。

说明

• 约束条件: 确保消息要么是发给用户，要么是发给群组，不能同时存在。
• 索引: 优化消息检索和跟踪的查询。
• 外键: 保持与 users 和 im_groups 表的参照完整性。
• 内容字段: 使用 JSON 格式存储消息内容，以灵活适应各种消息类型。

---

3. im_users_activate 表

drop table if exists im_users_activate;
CREATE TABLE im_users_activate (
  id      varchar  PRIMARY KEY,
  online bool,
  remark VARCHAR(256),
  creator VARCHAR(64) DEFAULT '',
  create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updater VARCHAR(64) DEFAULT '',
  update_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted SMALLINT default 0,
  tenant_id BIGINT NOT NULL DEFAULT 0
);

1. 自动更新 update_time 字段

在 PostgreSQL 中，可以通过触发器（Trigger）和触发器函数（Trigger Function）实现 update_time 字段的自动更新。

实现步骤：

1. 创建触发器函数：

   CREATE OR REPLACE FUNCTION update_update_time()
   RETURNS TRIGGER AS $$
   BEGIN
     NEW.update_time = CURRENT_TIMESTAMP;
     RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;

2. 创建触发器：

   CREATE TRIGGER set_im_users_activate_update_time
   BEFORE UPDATE ON im_users_activate
   FOR EACH ROW
   EXECUTE FUNCTION update_update_time();

这样，每次更新 im_users_activate 表的数据时，update_time 会自动更新为当前时间。

---

2. 获取 CURRENT_TIMESTAMP 和 update_time 的差值

可以使用 age 函数计算 CURRENT_TIMESTAMP 和 update_time 的时间差。

示例查询：

SELECT
  id,
  age(CURRENT_TIMESTAMP, update_time) AS time_difference
FROM
  im_users_activate;

• age 函数会返回时间差的结果，格式化为 interval 类型，例如 1 day 2 hours.

INSERT INTO im_users_activate (id, online) VALUES ('0', TRUE);
update im_users_activate set online=FALSE where id='0';
SELECT age(CURRENT_TIMESTAMP, update_time) AS time_difference FROM im_users_activate where id='0';

返回值

00:01:11.005462
0 years 0 mons 0 days 0 hours 0 mins 9.319295 secs

3. 获取时间戳格式的差值

返回时间差（以秒为单位的时间戳，并且类型为 long），可以使用 PostgreSQL 的 EXTRACT(EPOCH FROM ...) 函数。这个函数会计算两个时间戳之间的差异，并以秒为单位返回结果。为了确保返回的类型为 long（在 PostgreSQL 中对应 BIGINT），可以进行类型转换。

以下是修改后的 SQL 查询：

SELECT
  id,
  EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - update_time))::BIGINT AS time_difference_seconds
FROM
  im_users_activate;

解释：

1. EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - update_time)):

   • 计算当前时间 (CURRENT_TIMESTAMP) 和 update_time 之间的时间差。
   • EXTRACT(EPOCH FROM ...) 将这个时间差转换为以秒为单位的数值。

2. ::BIGINT:

   • 将计算得到的秒数转换为 BIGINT 类型，以确保结果是一个长整型。

3. AS time_difference_seconds:

   • 为结果列指定一个更具描述性的别名 time_difference_seconds。

示例结果：

假设 im_users_activate 表的数据如下：

id	update_time
1	2024-04-25 12:00:00
2	2024-04-26 15:30:00

执行上述查询后，结果可能类似于：

id	time_difference_seconds
1	24883200
2	24768000

（具体数值取决于 CURRENT_TIMESTAMP 的值）

示例

INSERT INTO im_users_activate (id, online) VALUES ('0', TRUE);
update im_users_activate set online=FALSE where id='0';
SELECT EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - update_time))::BIGINT AS time_difference_seconds FROM im_users_activate where id='0';

result

276

4. im_friends 表

此表管理用户之间的好友关系，支持好友请求、接受、阻止等功能。

表结构

drop table if exists im_friends;
CREATE TABLE im_friends (
  id               BIGINT PRIMARY KEY,
  user_id     varchar NOT NULL,
  friend_user_id       varchar NOT NULL,
  session_id       bigint,
  blocked          SMALLINT default 0,
  status           smallint NOT NULL DEFAULT 1,
  create_time      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "update_time"    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted SMALLINT default 0,
  tenant_id BIGINT NOT NULL DEFAULT 0
);

字段描述

• id: 每条好友关系记录的唯一标识符。
• user_id: 用户 ID。
• friend_user_id: 好友的用户 ID。
• blocked: 是否已经屏蔽
• status: 好友关系的状态（0：待处理，1：已接受，2：已阻止）。
• create_time: 好友关系创建的时间戳。
• update_time: 好友关系最后更新时间的时间戳。

说明

• 唯一约束: 确保每个好友对是唯一的。
• 双向关系: 为每个好友关系存储两条记录，以便从任一用户的角度进行查询。

---

5. im_groups 和 im_group_members 表

这些表用于管理群组信息和群组成员，支持群组创建、解散、成员管理和角色分配。

im_groups 表结构

CREATE TABLE im_groups (
  group_id          BIGINT UNSIGNED  PRIMARY KEY,
  group_name        VARCHAR(255) NOT NULL,
  creator_user_id   BIGINT UNSIGNED NOT NULL,
  description       TEXT DEFAULT NULL,
  create_time       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  update_time       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status            TINYINT UNSIGNED NOT NULL DEFAULT 1,
  extra             JSON DEFAULT NULL,
  deleted           SMALLINT default 0,
  tenant_id         BIGINT NOT NULL DEFAULT 0,
  -- 索引
  KEY idx_creator_user_id (creator_user_id),
  KEY idx_group_name (group_name),
  CONSTRAINT fk_creator_user FOREIGN KEY (creator_user_id) REFERENCES users(user_id)
);

im_group_members 表结构

CREATE TABLE im_group_members (
  id              BIGINT UNSIGNED  PRIMARY KEY,
  group_id        BIGINT UNSIGNED NOT NULL,
  user_id         BIGINT UNSIGNED NOT NULL,
  role            TINYINT UNSIGNED NOT NULL DEFAULT 1,
  mute            BOOLEAN NOT NULL DEFAULT FALSE,
  join_time       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  extra           JSON DEFAULT NULL,
  deleted         SMALLINT default 0,
  tenant_id       BIGINT NOT NULL DEFAULT 0,
  -- 约束条件
  UNIQUE KEY uniq_group_user (group_id, user_id),
  -- 索引
  KEY idx_group_id (group_id),
  KEY idx_user_id (user_id),
  CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES im_groups(group_id),
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id)
);

im_groups 表字段描述

• group_id: 每个群组的唯一标识符。
• group_name: 群组名称。
• creator_user_id: 创建群组的用户 ID。
• description: 群组的可选描述。
• create_time: 群组创建的时间戳。
• update_time: 群组最后更新时间的时间戳。
• status: 群组的状态（1：活跃，0：解散）。
• extra: 额外数据的 JSON 字段。

im_group_members 表字段描述

• id: 每条群组成员记录的唯一标识符。
• group_id: 群组 ID。
• user_id: 群组成员的用户 ID。
• role: 成员在群组中的角色（1：成员，2：管理员，3：所有者）。
• mute: 指示成员是否已静音群组。
• join_time: 用户加入群组的时间戳。
• extra: 额外数据的 JSON 字段。

说明

• 群组成员: 通过 im_group_members 表进行管理，支持角色和静音状态。
• 约束条件: 确保每个群组和用户的唯一成员记录。
• 索引: 优化群组成员和管理的查询。
• 外键: 保持群组和用户之间的参照完整性。