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);