[[278212]]

這是一系列關于構建“即時消息”應用的新帖子。你應該對這類應用并不陌生。有了它們的幫助，我們才可以與朋友暢聊無忌。Facebook Messenger、WhatsApp 和 Skype 就是其中的幾個例子。正如你所看到的那樣，這些應用允許我們發送圖片、傳輸視頻、錄制音頻、以及和一大幫子人聊天等等。當然，我們的教程應用將會盡量保持簡單，只在兩個用戶之間發送文本消息。

我們將會用 CockroachDB 作為 SQL 數據庫，用 Go 作為后端語言，并且用 JavaScript 來制作 web 應用。

這是頭篇帖子，我們將會講述數據庫的設計。

CREATE TABLE users (
    id SERIAL NOT NULL PRIMARY KEY,
    username STRING NOT NULL UNIQUE,
    avatar_url STRING,
    github_id INT NOT NULL UNIQUE
);

顯然，這個應用需要一些用戶。我們這里采用社交登錄的形式。由于我選用了 GitHub，所以這里需要保存一個對 GitHub 用戶 ID 的引用。

CREATE TABLE conversations (
    id SERIAL NOT NULL PRIMARY KEY,
    last_message_id INT,
    INDEX (last_message_id DESC)
);

每個對話都會引用最近一條消息。每當我們輸入一條新消息時，我們都會更新這個字段。我會在后面添加外鍵約束。

… 你可能會想，我們可以先對對話進行分組，然后再通過這樣的方式獲取最近一條消息。但這樣做會使查詢變得更加復雜。

CREATE TABLE participants (
    user_id INT NOT NULL REFERENCES users ON DELETE CASCADE,
    conversation_id INT NOT NULL REFERENCES conversations ON DELETE CASCADE,
    messages_read_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, conversation_id)
);

盡管之前我提到過對話只會在兩個用戶之間進行，但我們還是采用了允許向對話中添加多個參與者的設計。因此，在對話和用戶之間有一個參與者表。

為了知道用戶是否有未讀消息，我們在消息表中添加了“讀取時間”（messages_read_at）字段。每當用戶在對話中讀取消息時，我們都會更新它的值，這樣一來，我們就可以將它與對話中末尾一條消息的“創建時間”（created_at）字段進行比較。

CREATE TABLE messages (
    id SERIAL NOT NULL PRIMARY KEY,
    content STRING NOT NULL,
    user_id INT NOT NULL REFERENCES users ON DELETE CASCADE,
    conversation_id INT NOT NULL REFERENCES conversations ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    INDEX(created_at DESC)
);

盡管我們將消息表放在末尾，但它在應用中相當重要。我們用它來保存對創建它的用戶以及它所出現的對話的引用。而且還可以根據“創建時間”（created_at）來創建索引以完成對消息的排序。

ALTER TABLE conversations
ADD CONSTRAINT fk_last_message_id_ref_messages
FOREIGN KEY (last_message_id) REFERENCES messages ON DELETE SET NULL;

我在前面已經提到過這個外鍵約束了，不是嗎:D

有這四張表就足夠了。你也可以將這些查詢保存到一個文件中，并將其通過管道傳送到 Cockroach CLI。

首先，我們需要啟動一個新節點：

cockroach start --insecure --host 127.0.0.1

然后創建數據庫和這些表：

cockroach sql --insecure -e "CREATE DATABASE messenger"
cat schema.sql | cockroach sql --insecure -d messenger

這篇帖子就到這里。