hoppinworld_backend

Unnamed repository; edit this file 'description' to name the repository.
Log | Files | Refs | LICENSE

commit 25a8b1e360c390790b6a126a7dad2137a41dfd08
parent b4773968b2a5dbc682e65a77ef87903415170add
Author: Joël Lupien (Jojolepro) <jojolepromain@gmail.com>
Date:   Sun,  7 Oct 2018 16:32:36 -0400

Database init

Diffstat:
M.gitignore | 2++
Adiesel.toml | 5+++++
Amigrations/.gitkeep | 0
Amigrations/2018-10-07-202524_init/down.sql | 42++++++++++++++++++++++++++++++++++++++++++
Amigrations/2018-10-07-202524_init/up.sql | 109+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
5 files changed, 158 insertions(+), 0 deletions(-)

diff --git a/.gitignore b/.gitignore @@ -1,2 +1,3 @@ /target **/*.rs.bk +/.env+ \ No newline at end of file diff --git a/diesel.toml b/diesel.toml @@ -0,0 +1,5 @@ +# For documentation on how to configure this file, +# see diesel.rs/guides/configuring-diesel-cli + +[print_schema] +file = "src/schema.rs" diff --git a/migrations/.gitkeep b/migrations/.gitkeep diff --git a/migrations/2018-10-07-202524_init/down.sql b/migrations/2018-10-07-202524_init/down.sql @@ -0,0 +1,42 @@ +-- This file should undo anything in `up.sql` +ALTER TABLE `Map` DROP FOREIGN KEY `Map_fk0`; + +ALTER TABLE `Replay` DROP FOREIGN KEY `Replay_fk0`; + +ALTER TABLE `Score` DROP FOREIGN KEY `Score_fk0`; + +ALTER TABLE `Score` DROP FOREIGN KEY `Score_fk1`; + +ALTER TABLE `UserStat` DROP FOREIGN KEY `UserStat_fk0`; + +ALTER TABLE `UserBadge` DROP FOREIGN KEY `UserBadge_fk0`; + +ALTER TABLE `UserBadge` DROP FOREIGN KEY `UserBadge_fk1`; + +ALTER TABLE `PasswordReset` DROP FOREIGN KEY `PasswordReset_fk0`; + +ALTER TABLE `UserRole` DROP FOREIGN KEY `UserRole_fk0`; + +ALTER TABLE `UserRole` DROP FOREIGN KEY `UserRole_fk1`; + +DROP TABLE IF EXISTS `User`; + +DROP TABLE IF EXISTS `Role`; + +DROP TABLE IF EXISTS `Map`; + +DROP TABLE IF EXISTS `Replay`; + +DROP TABLE IF EXISTS `Score`; + +DROP TABLE IF EXISTS `MapStatus`; + +DROP TABLE IF EXISTS `UserStat`; + +DROP TABLE IF EXISTS `Badge`; + +DROP TABLE IF EXISTS `UserBadge`; + +DROP TABLE IF EXISTS `PasswordReset`; + +DROP TABLE IF EXISTS `UserRole`; diff --git a/migrations/2018-10-07-202524_init/up.sql b/migrations/2018-10-07-202524_init/up.sql @@ -0,0 +1,109 @@ +-- Your SQL goes here +CREATE TABLE `User` ( + `id` int NOT NULL AUTO_INCREMENT, + `username` varchar NOT NULL, + `email` varchar NOT NULL, + `password` varchar NOT NULL, + `token` varchar, + PRIMARY KEY (`id`) +); + +CREATE TABLE `Role` ( + `id` int NOT NULL AUTO_INCREMENT, + `friendly_name` varchar NOT NULL, + `display_name` varchar NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `Map` ( + `id` int NOT NULL AUTO_INCREMENT, + `status` int NOT NULL, + `name` varchar NOT NULL, + `segment_count` bit NOT NULL, + `path` varchar, + PRIMARY KEY (`id`) +); + +CREATE TABLE `Replay` ( + `id` int NOT NULL AUTO_INCREMENT, + `scoreid` int NOT NULL, + `path` varchar NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `Score` ( + `id` int NOT NULL AUTO_INCREMENT, + `userid` int NOT NULL, + `mapid` int NOT NULL, + `segment_times` varchar NOT NULL, + `strafes` int, + `jumps` int, + `total_time` FLOAT NOT NULL, + `max_speed` FLOAT NOT NULL, + `average_speed` FLOAT NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `MapStatus` ( + `id` int NOT NULL AUTO_INCREMENT, + `friendly_name` varchar NOT NULL, + `display_name` varchar NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `UserStat` ( + `id` int NOT NULL AUTO_INCREMENT, + `userid` int NOT NULL, + `jumps` bigint NOT NULL, + `strafes` bigint NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `Badge` ( + `id` int NOT NULL AUTO_INCREMENT, + `friendly_name` varchar NOT NULL, + `display_name` varchar NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `UserBadge` ( + `id` int NOT NULL AUTO_INCREMENT, + `userid` int NOT NULL, + `badgeid` int NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `PasswordReset` ( + `id` int NOT NULL AUTO_INCREMENT, + `userid` int NOT NULL, + `token` varchar NOT NULL, + `valid_until` DATETIME NOT NULL, + PRIMARY KEY (`id`) +); + +CREATE TABLE `UserRole` ( + `id` int NOT NULL AUTO_INCREMENT, + `userid` int NOT NULL, + `roleid` int NOT NULL, + PRIMARY KEY (`id`) +); + +ALTER TABLE `Map` ADD CONSTRAINT `Map_fk0` FOREIGN KEY (`status`) REFERENCES `MapStatus`(`id`); + +ALTER TABLE `Replay` ADD CONSTRAINT `Replay_fk0` FOREIGN KEY (`scoreid`) REFERENCES `Score`(`id`); + +ALTER TABLE `Score` ADD CONSTRAINT `Score_fk0` FOREIGN KEY (`userid`) REFERENCES `User`(`id`); + +ALTER TABLE `Score` ADD CONSTRAINT `Score_fk1` FOREIGN KEY (`mapid`) REFERENCES `Map`(`id`); + +ALTER TABLE `UserStat` ADD CONSTRAINT `UserStat_fk0` FOREIGN KEY (`userid`) REFERENCES `User`(`id`); + +ALTER TABLE `UserBadge` ADD CONSTRAINT `UserBadge_fk0` FOREIGN KEY (`userid`) REFERENCES `User`(`id`); + +ALTER TABLE `UserBadge` ADD CONSTRAINT `UserBadge_fk1` FOREIGN KEY (`badgeid`) REFERENCES `Badge`(`id`); + +ALTER TABLE `PasswordReset` ADD CONSTRAINT `PasswordReset_fk0` FOREIGN KEY (`userid`) REFERENCES `User`(`id`); + +ALTER TABLE `UserRole` ADD CONSTRAINT `UserRole_fk0` FOREIGN KEY (`userid`) REFERENCES `User`(`id`); + +ALTER TABLE `UserRole` ADD CONSTRAINT `UserRole_fk1` FOREIGN KEY (`roleid`) REFERENCES `Role`(`id`);