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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
|
-- Your SQL goes here
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 channel_preferences (
id INTEGER NOT NULL PRIMARY KEY REFERENCES channels(id) ON DELETE CASCADE,
prefix TEXT,
locale TEXT,
silent_mode BOOLEAN NOT NULL DEFAULT FALSE,
markov_responses BOOLEAN NOT NULL DEFAULT FALSE,
random_markov_responses BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE IF NOT EXISTS user_rights (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
level INTEGER NOT NULL DEFAULT 1,
CONSTRAINT unique_user_channel UNIQUE (user_id, channel_id)
);
CREATE TABLE IF NOT EXISTS actions (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
command TEXT NOT NULL,
arguments TEXT,
full_message TEXT NOT NULL,
sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS events (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
name TEXT NOT NULL,
event_type INTEGER NOT NULL,
is_massping BOOLEAN NOT NULL DEFAULT FALSE,
message TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS event_subscriptions (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
event_id INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS custom_commands (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
name TEXT NOT NULL,
message TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS custom_command_aliases (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
command_id INTEGER NOT NULL REFERENCES custom_commands(id) ON DELETE CASCADE,
name TEXT NOT NULL,
CONSTRAINT unique_command_alias UNIQUE (command_id, name)
);
CREATE TABLE IF NOT EXISTS timers (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
channel_id INTEGER NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
name TEXT NOT NULL,
message TEXT NOT NULL,
interval_sec INTEGER NOT NULL,
last_executed_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP
);
|
