· 6 years ago · Oct 24, 2019, 08:24 PM
1DROP DATABASE DiscordStats;
2DROP USER 'DiscordStatsBot'@'localhost';
3
4CREATE DATABASE IF NOT EXISTS DiscordStats;
5USE DiscordStats;
6
7CREATE USER IF NOT EXISTS 'DiscordStatsBot'@'localhost' IDENTIFIED WITH mysql_native_password BY '???';
8CREATE USER IF NOT EXISTS 'DiscordStatsWeb'@'localhost' IDENTIFIED WITH mysql_native_password BY '???';
9
10GRANT SELECT, INSERT, UPDATE, DELETE, DROP, SHOW VIEW ON DiscordStats.* TO 'DiscordStatsBot'@'localhost';
11GRANT CREATE VIEW, CREATE USER, GRANT OPTION, FILE, LOCK TABLES ON *.* TO 'DiscordStatsBot'@'localhost';
12
13GRANT SELECT ON DiscordStats.* TO 'DiscordStatsWeb'@'localhost';
14
15FLUSH PRIVILEGES;
16
17CREATE TABLE IF NOT EXISTS Users(
18 id VARCHAR(32) NOT NULL PRIMARY KEY,
19 userName VARCHAR(32) NOT NULL,
20 userTag VARCHAR(6) NOT NULL
21);
22CREATE INDEX userid USING HASH ON Users(id);
23
24CREATE TABLE IF NOT EXISTS Guilds(
25 id VARCHAR(32) NOT NULL PRIMARY KEY,
26 name VARCHAR(100) NOT NULL,
27 ownerID VARCHAR(32) NOT NULL
28);
29CREATE INDEX guildid USING HASH ON Guilds(id);
30
31CREATE TABLE IF NOT EXISTS GuildUser(
32 guildId VARCHAR(32) NOT NULL,
33 user VARCHAR(32) NOT NULL,
34 nickname VARCHAR(100) DEFAULT NULL
35);
36
37CREATE TABLE IF NOT EXISTS Channels(
38 id VARCHAR(32) NOT NULL PRIMARY KEY,
39 name VARCHAR(100) NOT NULL,
40 guildId VARCHAR(32) NOT NULL,
41 type ENUM('Voice', 'Text') NOT NULL
42);
43CREATE INDEX channelid USING HAS ON Channels(id);
44
45CREATE TABLE IF NOT EXISTS Messages(
46 id VARCHAR(32) NOT NULL,
47 time DATETIME NOT NULL,
48 author VARCHAR(32) NOT NULL,
49 channel VARCHAR(32) NOT NULL
50);
51
52CREATE TABLE IF NOT EXISTS ChannelLog(
53 event ENUM('ChannelNameChanged', 'ChannelCreated', 'ChannelDeleted') NOT NULL,
54 guildId VARCHAR(32) NOT NULL,
55 channelId VARCHAR(32) NOT NULL,
56 name VARCHAR(100) NOT NULL,
57 time DATETIME NOT NULL
58);
59
60CREATE TABLE IF NOT EXISTS GuildLog(
61 event ENUM('GuildNameChanged','GuildCreated', 'GuildDeleted') NOT NULL,
62 guildId VARCHAR(32) NOT NULL,
63 name VARCHAR(100) NOT NULL,
64 time DATETIME NOT NULL
65);
66
67CREATE TABLE IF NOT EXISTS GuildUserLog(
68 event ENUM('GuildUserNameChanged', 'GuildUserCreated', 'GuildUserDeleted') NOT NULL,
69 user VARCHAR(32) NOT NULL,
70 guildId VARCHAR(32) NOT NULL,
71 nickname VARCHAR(100) DEFAULT NULL,
72 time DATETIME NOT NULL
73);
74
75CREATE TABLE IF NOT EXISTS MessageLog(
76 event ENUM('MessageEdited', 'MessageDeleted') NOT NULL,
77 id VARCHAR(32) NOT NULL,
78 channel VARCHAR(32) NOT NULL,
79 user VARCHAR(32) NOT NULL,
80 time DATETIME NOT NULL
81);
82
83DELIMITER //
84CREATE TRIGGER MessageDeleted AFTER DELETE ON Messages FOR EACH ROW
85BEGIN
86 INSERT INTO MessageLog VALUES(
87 'MessageDeleted',
88 OLD.id,
89 OLD.channel,
90 OLD.author,
91 NOW()
92 );
93END;//
94
95CREATE TRIGGER ChannelCreated AFTER INSERT ON Channels FOR EACH ROW
96BEGIN
97 INSERT INTO ChannelLog VALUES(
98 'ChannelCreated',
99 NEW.guildId,
100 NEW.id,
101 NEW.name,
102 NOW()
103 );
104END;//
105
106CREATE TRIGGER ChannelEdited AFTER UPDATE ON Channels FOR EACH ROW
107BEGIN
108 INSERT INTO ChannelLog VALUES(
109 'ChannelNameChanged',
110 NEW.guildId,
111 NEW.id,
112 NEW.name,
113 NOW()
114 );
115END;//
116
117CREATE TRIGGER ChannelDeleted AFTER DELETE ON Channels FOR EACH ROW
118BEGIN
119 INSERT INTO ChannelLog VALUES(
120 'ChannelDeleted',
121 OLD.guildId,
122 OLD.id,
123 OLD.name,
124 NOW()
125 );
126END;//
127
128CREATE TRIGGER GuildCreated AFTER INSERT ON Guilds FOR EACH ROW
129BEGIN
130 INSERT INTO GuildLog VALUES(
131 'GuildCreated',
132 NEW.id,
133 NEW.name,
134 NOW()
135 );
136END;//
137
138CREATE TRIGGER GuildDeleted AFTER DELETE ON Guilds FOR EACH ROW
139BEGIN
140 INSERT INTO GuildLog VALUES(
141 'GuildDeleted',
142 OLD.id,
143 OLD.name,
144 NOW()
145 );
146END;//
147
148CREATE TRIGGER GuildEdited AFTER UPDATE ON Guilds FOR EACH ROW
149BEGIN
150 INSERT INTO GuildLog VALUES(
151 'GuildNameChanged',
152 NEW.id,
153 NEW.name,
154 NOW()
155 );
156END;//
157
158CREATE TRIGGER GuildUserCreated AFTER INSERT ON GuildUser FOR EACH ROW
159BEGIN
160 INSERT INTO GuildUserLog VALUES(
161 'GuildUserCreated',
162 NEW.user,
163 NEW.guildId,
164 NEW.nickname,
165 NOW()
166 );
167END;//
168
169CREATE TRIGGER GuildUserNameChanged AFTER UPDATE ON GuildUser FOR EACH ROW
170BEGIN
171 INSERT INTO GuildUserLog VALUES(
172 'GuildUserNameChanged',
173 NEW.user,
174 NEW.guildId,
175 NEW.nickname,
176 NOW()
177 );
178END;//
179
180CREATE TRIGGER GuildUserDeleted AFTER DELETE ON GuildUser FOR EACH ROW
181BEGIN
182 INSERT INTO GuildUserLog VALUES(
183 'GuildUserDeleted',
184 OLD.user,
185 OLD.guildId,
186 OLD.nickname,
187 NOW()
188 );
189END;//
190
191DELIMITER ;