· 5 years ago · Jul 28, 2020, 08:24 PM
1
2SET @OLD_DEFAULT_STORAGE_ENGINE=@@default_storage_engine, default_storage_engine=INNODB;
3
4-- MySQL Script generated by MySQL Workbench
5-- Tue Jul 28 22:12:26 2020
6-- Model: New Model Version: 1.0
7-- MySQL Workbench Forward Engineering
8SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
9SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
10SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
11
12-- -----------------------------------------------------
13-- Schema dataone
14-- -----------------------------------------------------
15
16-- -----------------------------------------------------
17-- Schema dataone
18-- -----------------------------------------------------
19CREATE SCHEMA IF NOT EXISTS `dataone` DEFAULT CHARACTER SET utf8 ;
20USE `dataone` ;
21
22-- -----------------------------------------------------
23-- Table `dataone`.`clans`
24-- -----------------------------------------------------
25DROP TABLE IF EXISTS `dataone`.`clans` ;
26
27CREATE TABLE IF NOT EXISTS `dataone`.`clans` (
28 `clan_id` INT NOT NULL,
29 `name` VARCHAR(255) NOT NULL,
30 `exp` BIGINT NOT NULL DEFAULT 0,
31 `standard_rank_id` INT NULL DEFAULT NULL,
32 PRIMARY KEY (`clan_id`),
33 INDEX `clans_standard_rank_id_foreign` (`standard_rank_id` ASC) VISIBLE,
34 UNIQUE INDEX `clan_id_UNIQUE` (`clan_id` ASC) VISIBLE,
35 CONSTRAINT `clans_standard_rank_id_foreign`
36 FOREIGN KEY (`standard_rank_id`)
37 REFERENCES `dataone`.`ranks` (`rank_id`)
38 ON DELETE RESTRICT);
39
40
41-- -----------------------------------------------------
42-- Table `dataone`.`ranks`
43-- -----------------------------------------------------
44DROP TABLE IF EXISTS `dataone`.`ranks` ;
45
46CREATE TABLE IF NOT EXISTS `dataone`.`ranks` (
47 `rank_id` INT NOT NULL,
48 `name` VARCHAR(255) NOT NULL,
49 `clan_id` INT NOT NULL,
50 PRIMARY KEY (`rank_id`),
51 INDEX `ranks_clan_id_foreign` (`clan_id` ASC) VISIBLE,
52 UNIQUE INDEX `rank_id_UNIQUE` (`rank_id` ASC) VISIBLE,
53 CONSTRAINT `ranks_clan_id_foreign`
54 FOREIGN KEY (`clan_id`)
55 REFERENCES `dataone`.`clans` (`clan_id`)
56 ON DELETE CASCADE
57 ON UPDATE RESTRICT)
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `dataone`.`players`
63-- -----------------------------------------------------
64DROP TABLE IF EXISTS `dataone`.`players` ;
65
66CREATE TABLE IF NOT EXISTS `dataone`.`players` (
67 `player_id` CHAR(37) NOT NULL,
68 `clan_id` INT NULL DEFAULT NULL,
69 `rank_id` INT NULL DEFAULT NULL,
70 PRIMARY KEY (`player_id`),
71 INDEX `players_rank_id_foreign` (`rank_id` ASC) VISIBLE,
72 INDEX `players_clan_id_foreign` (`clan_id` ASC) VISIBLE,
73 UNIQUE INDEX `player_id_UNIQUE` (`player_id` ASC) VISIBLE,
74 CONSTRAINT `players_rank_id_foreign`
75 FOREIGN KEY (`rank_id`)
76 REFERENCES `dataone`.`ranks` (`rank_id`)
77 ON DELETE SET NULL,
78 CONSTRAINT `players_clan_id_foreign`
79 FOREIGN KEY (`clan_id`)
80 REFERENCES `dataone`.`clans` (`clan_id`)
81 ON DELETE CASCADE);
82
83
84-- -----------------------------------------------------
85-- Table `dataone`.`clan_statistics`
86-- -----------------------------------------------------
87DROP TABLE IF EXISTS `dataone`.`clan_statistics` ;
88
89CREATE TABLE IF NOT EXISTS `dataone`.`clan_statistics` (
90 `clan_id` INT NOT NULL,
91 `statistics_id` INT NOT NULL,
92 `value` BIGINT NOT NULL DEFAULT 0,
93 PRIMARY KEY (`clan_id`, `statistics_id`),
94 CONSTRAINT `clan_statistics_clans_foreign`
95 FOREIGN KEY (`clan_id`)
96 REFERENCES `dataone`.`clans` (`clan_id`)
97 ON DELETE CASCADE
98 ON UPDATE NO ACTION);
99
100
101-- -----------------------------------------------------
102-- Table `dataone`.`rank_permission`
103-- -----------------------------------------------------
104DROP TABLE IF EXISTS `dataone`.`rank_permission` ;
105
106CREATE TABLE IF NOT EXISTS `dataone`.`rank_permission` (
107 `rank_id` INT NOT NULL,
108 `permission_id` INT NOT NULL,
109 PRIMARY KEY (`rank_id`, `permission_id`),
110 CONSTRAINT `rank_permission_rank_id_foregin`
111 FOREIGN KEY (`rank_id`)
112 REFERENCES `dataone`.`ranks` (`rank_id`)
113 ON DELETE CASCADE
114 ON UPDATE NO ACTION);
115
116
117SET SQL_MODE=@OLD_SQL_MODE;
118SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
119SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
120
121SET default_storage_engine=@OLD_DEFAULT_STORAGE_ENGINE
122