· 8 years ago · Apr 17, 2018, 12:34 AM
1-- MySQL Script generated by MySQL Workbench
2-- Mon Apr 16 20:32:47 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`minecraft_users`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`minecraft_users` (
24 `uuid` CHAR(32) NOT NULL,
25 `username` VARCHAR(32) NOT NULL,
26 `first_join` TIMESTAMP NOT NULL,
27 `last_seen` TIMESTAMP NOT NULL,
28 `in_game` TINYINT NOT NULL,
29 `time_played` DATETIME NULL,
30 `friends` CHAR(32) NULL,
31 PRIMARY KEY (`uuid`),
32 INDEX `fk_minecraft_users_minecraft_users1_idx` (`friends` ASC),
33 CONSTRAINT `fk_minecraft_users_minecraft_users1`
34 FOREIGN KEY (`friends`)
35 REFERENCES `mydb`.`minecraft_users` (`uuid`)
36 ON DELETE NO ACTION
37 ON UPDATE NO ACTION)
38ENGINE = InnoDB;
39
40
41-- -----------------------------------------------------
42-- Table `mydb`.`web_users`
43-- -----------------------------------------------------
44CREATE TABLE IF NOT EXISTS `mydb`.`web_users` (
45 `uuid` CHAR(32) NOT NULL,
46 `rank` VARCHAR(45) NULL,
47 PRIMARY KEY (`uuid`),
48 CONSTRAINT `fk_web_users_minecraft_users1`
49 FOREIGN KEY (`uuid`)
50 REFERENCES `mydb`.`minecraft_users` (`uuid`)
51 ON DELETE NO ACTION
52 ON UPDATE NO ACTION)
53ENGINE = InnoDB;
54
55
56-- -----------------------------------------------------
57-- Table `mydb`.`servers`
58-- -----------------------------------------------------
59CREATE TABLE IF NOT EXISTS `mydb`.`servers` (
60 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
61 `name` VARCHAR(16) NOT NULL COMMENT 'The name of the server',
62 `abbreviation` VARCHAR(8) NULL COMMENT 'Simple abbreviation for the servers name',
63 `port` SMALLINT UNSIGNED NOT NULL COMMENT 'Port number of the server',
64 `max_players` SMALLINT UNSIGNED NOT NULL COMMENT 'The max numbers of players that can play on the server at the same time',
65 `gamemode_ids` INT UNSIGNED NOT NULL,
66 PRIMARY KEY (`id`),
67 UNIQUE INDEX `name_UNIQUE` (`name` ASC),
68 UNIQUE INDEX `port_UNIQUE` (`port` ASC),
69 INDEX `fk_servers_gamemodes1_idx` (`gamemode_ids` ASC),
70 CONSTRAINT `server_gamemode_id`
71 FOREIGN KEY (`gamemode_ids`)
72 REFERENCES `mydb`.`gamemodes` (`id`)
73 ON DELETE NO ACTION
74 ON UPDATE CASCADE)
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`gamemodes`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `mydb`.`gamemodes` (
82 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
83 `name` VARCHAR(64) NOT NULL,
84 `abbreviation` VARCHAR(8) NULL,
85 `image` VARCHAR(32) NULL,
86 `description` VARCHAR(256) NULL,
87 `server_ids` INT UNSIGNED NULL,
88 PRIMARY KEY (`id`),
89 UNIQUE INDEX `gamemode_UNIQUE` (`name` ASC),
90 UNIQUE INDEX `gamemode_id_UNIQUE` (`id` ASC),
91 INDEX `fk_gamemodes_servers_idx` (`server_ids` ASC),
92 CONSTRAINT `gamemode_servers`
93 FOREIGN KEY (`server_ids`)
94 REFERENCES `mydb`.`servers` (`id`)
95 ON DELETE NO ACTION
96 ON UPDATE CASCADE)
97ENGINE = InnoDB;
98
99
100-- -----------------------------------------------------
101-- Table `mydb`.`scores`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `mydb`.`scores` (
104 `uuid` CHAR(32) NOT NULL,
105 `gamemode_id` INT UNSIGNED NOT NULL,
106 `total` BIGINT UNSIGNED NOT NULL,
107 `yearly` BIGINT UNSIGNED NOT NULL,
108 `monthly` BIGINT UNSIGNED NOT NULL,
109 `weekly` BIGINT UNSIGNED NOT NULL,
110 `daily` BIGINT UNSIGNED NOT NULL,
111 PRIMARY KEY (`uuid`, `gamemode_id`),
112 INDEX `fk_scores_minecraft_users1_idx` (`uuid` ASC),
113 CONSTRAINT `scores_gamemode`
114 FOREIGN KEY (`gamemode_id`)
115 REFERENCES `mydb`.`gamemodes` (`id`)
116 ON DELETE CASCADE
117 ON UPDATE NO ACTION,
118 CONSTRAINT `scores_users`
119 FOREIGN KEY (`uuid`)
120 REFERENCES `mydb`.`minecraft_users` (`uuid`)
121 ON DELETE CASCADE
122 ON UPDATE NO ACTION)
123ENGINE = InnoDB;
124
125
126-- -----------------------------------------------------
127-- Table `mydb`.`user`
128-- -----------------------------------------------------
129CREATE TABLE IF NOT EXISTS `mydb`.`user` (
130 `username` VARCHAR(16) NOT NULL,
131 `email` VARCHAR(255) NULL,
132 `password` VARCHAR(32) NOT NULL,
133 `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP);
134
135
136-- -----------------------------------------------------
137-- Table `mydb`.`inventory`
138-- -----------------------------------------------------
139CREATE TABLE IF NOT EXISTS `mydb`.`inventory` (
140 `id` BIGINT UNSIGNED NOT NULL,
141 `position` TINYINT UNSIGNED NOT NULL,
142 `item_name` VARCHAR(128) NOT NULL,
143 `item_id` SMALLINT UNSIGNED NOT NULL,
144 `amount` SMALLINT UNSIGNED NOT NULL,
145 `durability` INT NOT NULL,
146 `lores` VARCHAR(1024) NULL,
147 PRIMARY KEY (`id`, `position`))
148ENGINE = InnoDB;
149
150
151-- -----------------------------------------------------
152-- Table `mydb`.`player_data`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `mydb`.`player_data` (
155 `uuid` CHAR(32) NOT NULL,
156 `gamemode_id` INT UNSIGNED NOT NULL,
157 `inventory_id` BIGINT UNSIGNED NOT NULL,
158 PRIMARY KEY (`uuid`, `gamemode_id`),
159 INDEX `fk_scores_minecraft_users1_idx` (`uuid` ASC),
160 INDEX `fk_player_data_inventory1_idx` (`inventory_id` ASC),
161 CONSTRAINT `gamemode_link`
162 FOREIGN KEY (`gamemode_id`)
163 REFERENCES `mydb`.`gamemodes` (`id`)
164 ON DELETE CASCADE
165 ON UPDATE NO ACTION,
166 CONSTRAINT `player_data_link`
167 FOREIGN KEY (`uuid`)
168 REFERENCES `mydb`.`minecraft_users` (`uuid`)
169 ON DELETE CASCADE
170 ON UPDATE NO ACTION,
171 CONSTRAINT `inventory_address`
172 FOREIGN KEY (`inventory_id`)
173 REFERENCES `mydb`.`inventory` (`id`)
174 ON DELETE CASCADE
175 ON UPDATE CASCADE)
176ENGINE = InnoDB;
177
178
179-- -----------------------------------------------------
180-- Table `mydb`.`achievements`
181-- -----------------------------------------------------
182CREATE TABLE IF NOT EXISTS `mydb`.`achievements` (
183 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
184 `name` VARCHAR(45) NULL,
185 PRIMARY KEY (`id`))
186ENGINE = InnoDB;
187
188
189SET SQL_MODE=@OLD_SQL_MODE;
190SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
191SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;