· 8 years ago · Apr 27, 2018, 12:12 AM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Apr 26 20:05:41 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-- Schema civplayers
15-- -----------------------------------------------------
16
17-- -----------------------------------------------------
18-- Schema civplayers
19-- -----------------------------------------------------
20CREATE SCHEMA IF NOT EXISTS `civplayers` DEFAULT CHARACTER SET utf8mb4 ;
21USE `civplayers` ;
22
23-- -----------------------------------------------------
24-- Table `civplayers`.`civilizations`
25-- -----------------------------------------------------
26CREATE TABLE IF NOT EXISTS `civplayers`.`civilizations` (
27 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
28 `name` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
29 `leader` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
30 `created_at` TIMESTAMP NULL DEFAULT NULL,
31 `updated_at` TIMESTAMP NULL DEFAULT NULL,
32 PRIMARY KEY (`id`))
33ENGINE = InnoDB
34DEFAULT CHARACTER SET = utf8mb4
35COLLATE = utf8mb4_unicode_ci;
36
37
38-- -----------------------------------------------------
39-- Table `civplayers`.`users`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS `civplayers`.`users` (
42 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
43 `name` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
44 `email` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
45 `password` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
46 `remember_token` VARCHAR(100) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NULL DEFAULT NULL,
47 `created_at` TIMESTAMP NULL DEFAULT NULL,
48 `updated_at` TIMESTAMP NULL DEFAULT NULL,
49 PRIMARY KEY (`id`),
50 UNIQUE INDEX `users_email_unique` (`email` ASC))
51ENGINE = InnoDB
52AUTO_INCREMENT = 2
53DEFAULT CHARACTER SET = utf8mb4
54COLLATE = utf8mb4_unicode_ci;
55
56
57-- -----------------------------------------------------
58-- Table `civplayers`.`leagues`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `civplayers`.`leagues` (
61 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
62 `owner_id` INT(10) UNSIGNED NOT NULL,
63 `name` VARCHAR(200) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
64 `description` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
65 `created_at` TIMESTAMP NULL DEFAULT NULL,
66 `updated_at` TIMESTAMP NULL DEFAULT NULL,
67 PRIMARY KEY (`id`),
68 INDEX `leagues_owner_id_foreign` (`owner_id` ASC),
69 CONSTRAINT `leagues_owner_id_foreign`
70 FOREIGN KEY (`owner_id`)
71 REFERENCES `civplayers`.`users` (`id`))
72ENGINE = InnoDB
73DEFAULT CHARACTER SET = utf8mb4
74COLLATE = utf8mb4_unicode_ci;
75
76
77-- -----------------------------------------------------
78-- Table `civplayers`.`match_options`
79-- -----------------------------------------------------
80CREATE TABLE IF NOT EXISTS `civplayers`.`match_options` (
81 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
82 `type` VARCHAR(60) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
83 `title` VARCHAR(60) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
84 `created_at` TIMESTAMP NULL DEFAULT NULL,
85 `updated_at` TIMESTAMP NULL DEFAULT NULL,
86 PRIMARY KEY (`id`))
87ENGINE = InnoDB
88DEFAULT CHARACTER SET = utf8mb4
89COLLATE = utf8mb4_unicode_ci;
90
91
92-- -----------------------------------------------------
93-- Table `civplayers`.`seasons`
94-- -----------------------------------------------------
95CREATE TABLE IF NOT EXISTS `civplayers`.`seasons` (
96 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
97 `league_id` INT(10) UNSIGNED NOT NULL,
98 `name` VARCHAR(200) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
99 `description` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
100 `starts_on` TIMESTAMP NOT NULL,
101 `ends_on` TIMESTAMP NOT NULL,
102 `created_at` TIMESTAMP NULL DEFAULT NULL,
103 `updated_at` TIMESTAMP NULL DEFAULT NULL,
104 PRIMARY KEY (`id`),
105 INDEX `seasons_league_id_foreign` (`league_id` ASC),
106 CONSTRAINT `seasons_league_id_foreign`
107 FOREIGN KEY (`league_id`)
108 REFERENCES `civplayers`.`leagues` (`id`))
109ENGINE = InnoDB
110DEFAULT CHARACTER SET = utf8mb4
111COLLATE = utf8mb4_unicode_ci;
112
113
114-- -----------------------------------------------------
115-- Table `civplayers`.`matches`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `civplayers`.`matches` (
118 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
119 `created_by` INT(10) UNSIGNED NOT NULL,
120 `season_id` INT(10) UNSIGNED NOT NULL,
121 `title` VARCHAR(60) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
122 `status_code` INT(10) UNSIGNED NOT NULL DEFAULT '1',
123 `created_at` TIMESTAMP NULL DEFAULT NULL,
124 `updated_at` TIMESTAMP NULL DEFAULT NULL,
125 PRIMARY KEY (`id`),
126 INDEX `matches_created_by_foreign` (`created_by` ASC),
127 INDEX `matches_season_id_foreign` (`season_id` ASC),
128 CONSTRAINT `matches_created_by_foreign`
129 FOREIGN KEY (`created_by`)
130 REFERENCES `civplayers`.`users` (`id`),
131 CONSTRAINT `matches_season_id_foreign`
132 FOREIGN KEY (`season_id`)
133 REFERENCES `civplayers`.`seasons` (`id`))
134ENGINE = InnoDB
135DEFAULT CHARACTER SET = utf8mb4
136COLLATE = utf8mb4_unicode_ci;
137
138
139-- -----------------------------------------------------
140-- Table `civplayers`.`match_settings`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `civplayers`.`match_settings` (
143 `match_id` INT(10) UNSIGNED NOT NULL,
144 `match_option_id` INT(10) UNSIGNED NOT NULL,
145 `value_code` INT(10) UNSIGNED NOT NULL,
146 PRIMARY KEY (`match_id`, `match_option_id`),
147 INDEX `match_settings_match_option_id_foreign` (`match_option_id` ASC),
148 CONSTRAINT `match_settings_match_id_foreign`
149 FOREIGN KEY (`match_id`)
150 REFERENCES `civplayers`.`matches` (`id`),
151 CONSTRAINT `match_settings_match_option_id_foreign`
152 FOREIGN KEY (`match_option_id`)
153 REFERENCES `civplayers`.`match_options` (`id`))
154ENGINE = InnoDB
155DEFAULT CHARACTER SET = utf8mb4
156COLLATE = utf8mb4_unicode_ci;
157
158
159-- -----------------------------------------------------
160-- Table `civplayers`.`migrations`
161-- -----------------------------------------------------
162CREATE TABLE IF NOT EXISTS `civplayers`.`migrations` (
163 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
164 `migration` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
165 `batch` INT(11) NOT NULL,
166 PRIMARY KEY (`id`))
167ENGINE = InnoDB
168AUTO_INCREMENT = 21
169DEFAULT CHARACTER SET = utf8mb4
170COLLATE = utf8mb4_unicode_ci;
171
172
173-- -----------------------------------------------------
174-- Table `civplayers`.`password_resets`
175-- -----------------------------------------------------
176CREATE TABLE IF NOT EXISTS `civplayers`.`password_resets` (
177 `email` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
178 `token` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
179 `created_at` TIMESTAMP NULL DEFAULT NULL,
180 INDEX `password_resets_email_index` (`email` ASC))
181ENGINE = InnoDB
182DEFAULT CHARACTER SET = utf8mb4
183COLLATE = utf8mb4_unicode_ci;
184
185
186-- -----------------------------------------------------
187-- Table `civplayers`.`players`
188-- -----------------------------------------------------
189CREATE TABLE IF NOT EXISTS `civplayers`.`players` (
190 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
191 `player_id` INT(10) UNSIGNED NOT NULL,
192 `username` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
193 `discord_name` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
194 `discord_discriminator` INT(11) NOT NULL,
195 `discord_id` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
196 `avatar` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
197 `created_at` TIMESTAMP NULL DEFAULT NULL,
198 `updated_at` TIMESTAMP NULL DEFAULT NULL,
199 PRIMARY KEY (`id`),
200 UNIQUE INDEX `players_username_unique` (`username` ASC),
201 UNIQUE INDEX `players_discord_id_unique` (`discord_id` ASC),
202 UNIQUE INDEX `players_avatar_unique` (`avatar` ASC),
203 INDEX `players_player_id_foreign` (`player_id` ASC),
204 CONSTRAINT `players_player_id_foreign`
205 FOREIGN KEY (`player_id`)
206 REFERENCES `civplayers`.`users` (`id`))
207ENGINE = InnoDB
208DEFAULT CHARACTER SET = utf8mb4
209COLLATE = utf8mb4_unicode_ci;
210
211
212-- -----------------------------------------------------
213-- Table `civplayers`.`player_match`
214-- -----------------------------------------------------
215CREATE TABLE IF NOT EXISTS `civplayers`.`player_match` (
216 `player_id` INT(10) UNSIGNED NOT NULL,
217 `match_id` INT(10) UNSIGNED NOT NULL,
218 `civilization_id` INT(10) UNSIGNED NOT NULL,
219 `status_code` INT(10) UNSIGNED NOT NULL,
220 `position` INT(10) UNSIGNED NOT NULL,
221 `civilization` INT(10) UNSIGNED NOT NULL,
222 PRIMARY KEY (`player_id`, `match_id`),
223 INDEX `player_match_match_id_foreign` (`match_id` ASC),
224 INDEX `player_match_civilization_id_foreign` (`civilization_id` ASC),
225 CONSTRAINT `player_match_civilization_id_foreign`
226 FOREIGN KEY (`civilization_id`)
227 REFERENCES `civplayers`.`civilizations` (`id`),
228 CONSTRAINT `player_match_match_id_foreign`
229 FOREIGN KEY (`match_id`)
230 REFERENCES `civplayers`.`matches` (`id`),
231 CONSTRAINT `player_match_player_id_foreign`
232 FOREIGN KEY (`player_id`)
233 REFERENCES `civplayers`.`players` (`id`))
234ENGINE = InnoDB
235DEFAULT CHARACTER SET = utf8mb4
236COLLATE = utf8mb4_unicode_ci;
237
238
239SET SQL_MODE=@OLD_SQL_MODE;
240SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
241SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;