nim/server/sql/migrate.sql

-- Upgrade script for existing databases. Ignore errors if a column already exists.
USE im;

ALTER TABLE im_user
    ADD COLUMN phone VARCHAR(20) NOT NULL DEFAULT '' AFTER nickname;
ALTER TABLE im_user
    ADD COLUMN signature VARCHAR(128) NOT NULL DEFAULT '' AFTER phone;
ALTER TABLE im_user
    ADD KEY idx_phone (phone);

CREATE TABLE IF NOT EXISTS im_sms_code (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    phone       VARCHAR(20)     NOT NULL,
    scene       VARCHAR(16)     NOT NULL,
    code        VARCHAR(8)      NOT NULL,
    expire_at   DATETIME        NOT NULL,
    used        TINYINT         NOT NULL DEFAULT 0,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_phone_scene (phone, scene, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_friend (
    uid         BIGINT UNSIGNED NOT NULL,
    friend_uid  BIGINT UNSIGNED NOT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (uid, friend_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_conversation (
    uid           BIGINT UNSIGNED NOT NULL,
    peer_uid      BIGINT UNSIGNED NOT NULL,
    last_msg_id   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_content  VARCHAR(256)    NOT NULL DEFAULT '',
    last_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unread        INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (uid, peer_uid),
    KEY idx_uid_time (uid, last_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO im_user (id, username, password, nickname, phone, signature, status)
VALUES (1, 'filehelper', '-', 'filehelper', '', 'files', 2)
ON DUPLICATE KEY UPDATE nickname=VALUES(nickname), signature=VALUES(signature), status=VALUES(status);

ALTER TABLE im_user
    ADD COLUMN allow_search_account TINYINT NOT NULL DEFAULT 1 AFTER signature;
ALTER TABLE im_user
    ADD COLUMN allow_search_phone TINYINT NOT NULL DEFAULT 1 AFTER allow_search_account;
ALTER TABLE im_user
    ADD COLUMN allow_add_friend TINYINT NOT NULL DEFAULT 1 AFTER allow_search_phone;

CREATE TABLE IF NOT EXISTS im_friend_request (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    from_uid    BIGINT UNSIGNED NOT NULL,
    to_uid      BIGINT UNSIGNED NOT NULL,
    status      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待处理 1已同意 2已拒绝',
    message     VARCHAR(64)     NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_from_to (from_uid, to_uid),
    KEY idx_to_status (to_uid, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE im_friend_request
    ADD COLUMN message VARCHAR(64) NOT NULL DEFAULT '' AFTER status;

ALTER TABLE im_friend
    ADD COLUMN remark VARCHAR(64) NOT NULL DEFAULT '' AFTER friend_uid;

CREATE TABLE IF NOT EXISTS im_sticker_pack (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(64)     NOT NULL,
    version     INT             NOT NULL DEFAULT 1,
    sort_order  INT             NOT NULL DEFAULT 0,
    status      TINYINT         NOT NULL DEFAULT 1,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_sticker (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    pack_id     BIGINT UNSIGNED NOT NULL,
    code        VARCHAR(64)     NOT NULL,
    name        VARCHAR(64)     NOT NULL DEFAULT '',
    file_path   VARCHAR(255)    NOT NULL,
    sha256      VARCHAR(64)     NOT NULL DEFAULT '',
    mime        VARCHAR(32)     NOT NULL DEFAULT 'image/png',
    sort_order  INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uk_pack_code (pack_id, code),
    KEY idx_pack (pack_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE im_user ADD COLUMN avatar_ver INT NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS im_file (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    from_uid    BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(255)    NOT NULL,
    mime        VARCHAR(64)     NOT NULL DEFAULT 'application/octet-stream',
    size        INT             NOT NULL DEFAULT 0,
    sha256      VARCHAR(64)     NOT NULL DEFAULT '',
    file_path   VARCHAR(255)    NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_from (from_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO im_sticker_pack (id, name, version, sort_order, status)
VALUES (1, '默认表情', 1, 1, 1), (2, '手势', 1, 2, 1)
ON DUPLICATE KEY UPDATE name=VALUES(name), status=VALUES(status);

ALTER TABLE im_message
    ADD COLUMN sender_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '发送方已删除' AFTER content;
ALTER TABLE im_message
    ADD COLUMN receiver_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '接收方已删除' AFTER sender_deleted;

CREATE TABLE IF NOT EXISTS im_favorite (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uid           BIGINT UNSIGNED NOT NULL,
    kind          TINYINT         NOT NULL DEFAULT 1 COMMENT '1文本 2图片 3文件 4名片 5笔记',
    title         VARCHAR(128)    NOT NULL DEFAULT '',
    content       MEDIUMTEXT      NOT NULL,
    src_msg_id    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    src_peer_uid  BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_uid_time (uid, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name          VARCHAR(64)     NOT NULL DEFAULT '',
    owner_uid     BIGINT UNSIGNED NOT NULL,
    join_mode     TINYINT         NOT NULL DEFAULT 1 COMMENT '0开放 1审核 2禁止',
    member_count  INT             NOT NULL DEFAULT 0,
    avatar_ver    INT             NOT NULL DEFAULT 0,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_owner (owner_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_member (
    group_id    BIGINT UNSIGNED NOT NULL,
    uid         BIGINT UNSIGNED NOT NULL,
    role        TINYINT         NOT NULL DEFAULT 0 COMMENT '0成员 1管理员 2群主',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, uid),
    KEY idx_uid (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_request (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id    BIGINT UNSIGNED NOT NULL,
    from_uid    BIGINT UNSIGNED NOT NULL,
    status      TINYINT         NOT NULL DEFAULT 0 COMMENT '0待处理 1已同意 2已拒绝',
    message     VARCHAR(64)     NOT NULL DEFAULT '',
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_group_from (group_id, from_uid),
    KEY idx_group_status (group_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_message (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id    BIGINT UNSIGNED NOT NULL,
    from_uid    BIGINT UNSIGNED NOT NULL DEFAULT 0,
    msg_type    TINYINT         NOT NULL DEFAULT 1,
    content     TEXT            NOT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_group_id (group_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_msg_deleted (
    uid     BIGINT UNSIGNED NOT NULL,
    msg_id  BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (uid, msg_id),
    KEY idx_msg (msg_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS im_group_inbox (
    uid           BIGINT UNSIGNED NOT NULL,
    group_id      BIGINT UNSIGNED NOT NULL,
    last_msg_id   BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_content  VARCHAR(256)    NOT NULL DEFAULT '',
    last_time     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unread        INT             NOT NULL DEFAULT 0,
    PRIMARY KEY (uid, group_id),
    KEY idx_uid_time (uid, last_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE im_group ADD COLUMN avatar_ver INT NOT NULL DEFAULT 0;

ALTER TABLE im_user ADD COLUMN file_max BIGINT NOT NULL DEFAULT 0 COMMENT '单文件上传上限字节，0表示用配置 file_max';