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