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
|
CREATE TABLE IF NOT EXISTS channels (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
alias_id INTEGER NOT NULL UNIQUE,
alias_name TEXT NOT NULL UNIQUE,
joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
opted_out_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS users (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
alias_id INTEGER NOT NULL UNIQUE,
alias_name TEXT NOT NULL UNIQUE,
joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
opted_out_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS words (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS channel_words (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
word_id BIGINT NOT NULL REFERENCES words(id) ON DELETE CASCADE,
usage_count BIGINT NOT NULL DEFAULT 0,
first_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
last_used_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP,
UNIQUE (channel_id, user_id, word_id)
);
CREATE TABLE IF NOT EXISTS emotes (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
platform VARCHAR(16) NOT NULL,
platform_id VARCHAR(64) NOT NULL,
UNIQUE (platform, platform_id)
);
|
