diff options
| -rw-r--r-- | database.sql | 45 |
1 files changed, 45 insertions, 0 deletions
diff --git a/database.sql b/database.sql new file mode 100644 index 0000000..c15b2f2 --- /dev/null +++ b/database.sql @@ -0,0 +1,45 @@ +CREATE TABLE IF NOT EXISTS servers ( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + host TEXT NOT NULL UNIQUE, + port INTEGER NOT NULL, + nick TEXT NOT NULL, + pass TEXT NOT NULL, + capabilities TEXT NOT NULL +); + +CREATE TABLE IF NOT EXISTS rooms ( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE, + `name` TEXT NOT NULL UNIQUE, + joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(), + departed_at TIMESTAMP, + INDEX (`name`), + INDEX (id, server_id) +); + +CREATE TABLE IF NOT EXISTS users ( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + server_id INTEGER NOT NULL REFERENCES servers(id) ON DELETE CASCADE, + nick TEXT NOT NULL, + joined_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(), + departed_at TIMESTAMP, + INDEX (nick), + INDEX (server_id) +); + +CREATE TABLE IF NOT EXISTS messages ( + id BIGINT PRIMARY KEY AUTO_INCREMENT, + room_id INTEGER NOT NULL REFERENCES rooms(id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, + command VARCHAR(32) NOT NULL, + + params TEXT, + tags TEXT, + + sent_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(), + INDEX (room_id), + INDEX (user_id), + INDEX (command), + INDEX (room_id, sent_at), + FULLTEXT INDEX ft_message (params) +);
\ No newline at end of file |