This repository has been archived by the owner on Jan 24, 2021. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 15
/
create.sql
227 lines (163 loc) · 6.67 KB
/
create.sql
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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
SET FOREIGN_KEY_CHECKS=0;
USE reminders;
CREATE TABLE reminders.guilds (
id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
guild BIGINT UNSIGNED UNIQUE NOT NULL,
name VARCHAR(100),
prefix VARCHAR(5) DEFAULT '$' NOT NULL,
timezone VARCHAR(32) DEFAULT 'UTC' NOT NULL,
default_channel_id INT UNSIGNED,
default_username VARCHAR(32) DEFAULT 'Reminder' NOT NULL,
default_avatar VARCHAR(512) DEFAULT 'https://raw.githubusercontent.com/reminder-bot/logos/master/Remind_Me_Bot_Logo_PPic.jpg' NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (default_channel_id) REFERENCES reminders.channels(id) ON DELETE SET NULL
);
CREATE TABLE reminders.channels (
id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
channel BIGINT UNSIGNED UNIQUE NOT NULL,
name VARCHAR(100),
nudge SMALLINT NOT NULL DEFAULT 0,
blacklisted BOOL NOT NULL DEFAULT FALSE,
webhook_id BIGINT UNSIGNED UNIQUE,
webhook_token TEXT,
paused BOOL NOT NULL DEFAULT 0,
paused_until TIMESTAMP,
guild_id INT UNSIGNED,
PRIMARY KEY (id),
FOREIGN KEY (guild_id) REFERENCES reminders.guilds(id) ON DELETE CASCADE
);
CREATE TABLE reminders.users (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
user BIGINT UNSIGNED UNIQUE NOT NULL,
name VARCHAR(37) NOT NULL,
dm_channel INT UNSIGNED UNIQUE NOT NULL,
language VARCHAR(2) DEFAULT 'EN' NOT NULL,
timezone VARCHAR(32), # nullable s.t it can default to server timezone
allowed_dm BOOLEAN DEFAULT 1 NOT NULL,
patreon BOOL NOT NULL DEFAULT 0,
PRIMARY KEY (id),
FOREIGN KEY (dm_channel) REFERENCES reminders.channels(id) ON DELETE RESTRICT
);
CREATE TABLE reminders.roles (
id INT UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
role BIGINT UNSIGNED UNIQUE NOT NULL,
name VARCHAR(100),
guild_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (guild_id) REFERENCES reminders.guilds(id) ON DELETE CASCADE
);
CREATE TABLE reminders.embeds (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
title VARCHAR(256) NOT NULL DEFAULT '',
description VARCHAR(2048) NOT NULL DEFAULT '',
image_url VARCHAR(512),
thumbnail_url VARCHAR(512),
footer VARCHAR(2048) NOT NULL DEFAULT '',
footer_icon VARCHAR(512),
color MEDIUMINT UNSIGNED NOT NULL DEFAULT 0x0,
PRIMARY KEY (id)
);
CREATE TABLE reminders.messages (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
content VARCHAR(2048) NOT NULL DEFAULT '',
tts BOOL NOT NULL DEFAULT 0,
embed_id INT UNSIGNED,
attachment MEDIUMBLOB,
attachment_name VARCHAR(260),
PRIMARY KEY (id),
FOREIGN KEY (embed_id) REFERENCES reminders.embeds(id) ON DELETE SET NULL
);
CREATE TABLE reminders.reminders (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
uid VARCHAR(64) UNIQUE NOT NULL,
name VARCHAR(24) NOT NULL DEFAULT 'Reminder',
message_id INT UNSIGNED NOT NULL,
channel_id INT UNSIGNED NOT NULL,
`time` INT UNSIGNED DEFAULT 0 NOT NULL,
`interval` INT UNSIGNED DEFAULT NULL,
enabled BOOLEAN DEFAULT 1 NOT NULL,
avatar VARCHAR(512) DEFAULT 'https://raw.githubusercontent.com/reminder-bot/logos/master/Remind_Me_Bot_Logo_PPic.jpg' NOT NULL,
username VARCHAR(32) DEFAULT 'Reminder' NOT NULL,
method ENUM('remind', 'natural', 'dashboard', 'todo'),
set_at TIMESTAMP DEFAULT (UNIX_TIMESTAMP()),
set_by INT UNSIGNED,
PRIMARY KEY (id),
FOREIGN KEY (message_id) REFERENCES reminders.messages(id) ON DELETE RESTRICT,
FOREIGN KEY (channel_id) REFERENCES reminders.channels(id) ON DELETE CASCADE,
FOREIGN KEY (set_by) REFERENCES reminders.users(id) ON DELETE SET NULL
);
CREATE TRIGGER message_cleanup AFTER DELETE ON reminders.reminders
FOR EACH ROW
DELETE FROM reminders.messages WHERE id = OLD.message_id;
CREATE TRIGGER embed_cleanup AFTER DELETE ON reminders.messages
FOR EACH ROW
DELETE FROM reminders.embeds WHERE id = OLD.embed_id;
CREATE TABLE reminders.todos (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
user_id INT UNSIGNED,
guild_id INT UNSIGNED,
channel_id INT UNSIGNED,
value VARCHAR(2000) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES reminders.users(id) ON DELETE SET NULL,
FOREIGN KEY (guild_id) REFERENCES reminders.guilds(id) ON DELETE CASCADE,
FOREIGN KEY (channel_id) REFERENCES reminders.channels(id) ON DELETE SET NULL
);
CREATE TABLE reminders.command_restrictions (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
guild_id INT UNSIGNED NOT NULL,
role_id INT UNSIGNED NOT NULL,
command ENUM('todos', 'natural', 'remind', 'interval', 'timer', 'del', 'look', 'alias') NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (guild_id) REFERENCES reminders.guilds(id) ON DELETE CASCADE,
FOREIGN KEY (role_id) REFERENCES reminders.roles(id) ON DELETE CASCADE,
UNIQUE KEY (`role_id`, `command`)
);
CREATE TABLE reminders.timers (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
start_time TIMESTAMP NOT NULL DEFAULT (UNIX_TIMESTAMP()),
name VARCHAR(32) NOT NULL,
owner BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE reminders.events (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
`time` TIMESTAMP NOT NULL DEFAULT (UNIX_TIMESTAMP()),
event_name ENUM('edit', 'enable', 'disable', 'delete') NOT NULL,
bulk_count INT UNSIGNED,
guild_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED,
reminder_id INT UNSIGNED,
PRIMARY KEY (id),
FOREIGN KEY (guild_id) REFERENCES reminders.guilds(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES reminders.users(id) ON DELETE SET NULL,
FOREIGN KEY (reminder_id) REFERENCES reminders.reminders(id) ON DELETE SET NULL
);
CREATE TABLE reminders.command_aliases (
id INT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
guild_id INT UNSIGNED NOT NULL,
name VARCHAR(12) NOT NULL,
command VARCHAR(2048) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (guild_id) REFERENCES reminders.guilds(id) ON DELETE CASCADE,
UNIQUE KEY (`guild_id`, `name`)
);
CREATE TABLE reminders.guild_users (
guild INT UNSIGNED NOT NULL,
user INT UNSIGNED NOT NULL,
can_access BOOL NOT NULL DEFAULT 0,
FOREIGN KEY (guild) REFERENCES reminders.guilds(id) ON DELETE CASCADE,
FOREIGN KEY (user) REFERENCES reminders.users(id) ON DELETE CASCADE,
UNIQUE KEY (guild, user)
);
CREATE EVENT reminders.event_cleanup
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
ON COMPLETION PRESERVE
DO DELETE FROM reminders.events WHERE `time` < DATE_SUB(NOW(), INTERVAL 5 DAY);
CREATE TABLE reminders.languages (
id SMALLINT UNSIGNED AUTO_INCREMENT UNIQUE NOT NULL,
name VARCHAR(20) NOT NULL,
code VARCHAR(2) NOT NULL,
PRIMARY KEY (id)
);
-- MUST RUN to_database.py TO FORM STRING STORES