· 5 years ago · Jun 03, 2020, 04:08 PM
1CREATE DATABASE IF NOT EXISTS Nolydia DEFAULT CHARACTER SET utf8;
2
3DROP TABLE IF EXISTS achievement_categories;
4CREATE TABLE achievement_categories
5(
6 id SMALLINT AUTO_INCREMENT,
7 name VARCHAR(255),
8 description TEXT,
9 PRIMARY KEY (id)
10);
11
12INSERT INTO achievement_categories(name, description)
13VALUES ('general', 'General achievements');
14
15DROP TABLE IF EXISTS achievement_levels;
16CREATE TABLE achievement_levels
17(
18 achievement_id SMALLINT,
19 level INT,
20 progress INT,
21 reward INT,
22 PRIMARY KEY (achievement_id)
23);
24
25DROP TABLE IF EXISTS achievement_progresses;
26CREATE TABLE achievement_progresses
27(
28 owner_uuid VARCHAR(255),
29 achievement_id SMALLINT,
30 level INT,
31 progress INT,
32 unlock_date DATE
33);
34
35DROP TABLE IF EXISTS achievements;
36CREATE TABLE achievements
37(
38 id SMALLINT AUTO_INCREMENT,
39 name VARCHAR(255),
40 description VARCHAR(255),
41 category_id VARCHAR(255),
42 PRIMARY KEY (id)
43);
44
45INSERT INTO achievements(name, description, category_id)
46VALUES ('First connection', 'Connect to the server for the the first time', 1);
47
48DROP TABLE IF EXISTS games;
49CREATE TABLE games
50(
51 id SMALLINT AUTO_INCREMENT,
52 name VARCHAR(255),
53 PRIMARY KEY (id)
54);
55
56INSERT INTO games(name)
57VALUES ('Bedwars'),
58 ('Skywars')
59;
60
61DROP TABLE IF EXISTS guild_profiles;
62CREATE TABLE guild_profiles
63(
64 uuid VARCHAR(40),
65 guild_uuid VARCHAR(40),
66 role_id SMALLINT,
67 experience INT(11),
68 adhesion_date DATETIME,
69 PRIMARY KEY (uuid)
70);
71
72DROP TABLE IF EXISTS guild_roles;
73CREATE TABLE guild_roles
74(
75 id SMALLINT,
76 name VARCHAR(255),
77 tag VARCHAR(255),
78 tag_color VARCHAR(255),
79 PRIMARY KEY (id)
80);
81
82# TODO insert guild roles
83
84DROP TABLE IF EXISTS guilds;
85CREATE TABLE guilds
86(
87 uuid VARCHAR(40),
88 name VARCHAR(255),
89 motd TEXT,
90 level SMALLINT,
91 experience INT(11),
92 creation_date DATETIME,
93 PRIMARY KEY (uuid)
94);
95
96DROP TABLE IF EXISTS `groups`;
97CREATE TABLE `groups`
98(
99 id SMALLINT AUTO_INCREMENT,
100 name VARCHAR(255),
101 tag VARCHAR(255),
102 tag_color VARCHAR(5),
103 PRIMARY KEY (id)
104);
105
106INSERT INTO `groups`(name, tag, tag_color)
107VALUES ('Joueur', 'Joueur', '&f'),
108 ('VIP', 'VIP', '&a'),
109 ('VIP+', 'VIP+', '&b'),
110 ('Ami', 'Ami', '&d'),
111 ('Builder', 'Builder', '&9'),
112 ('Développeur', 'Dev', '&e'),
113 ('Modérateur', 'Modo', '&6'),
114 ('Administrateur', 'Admin', '&c')
115;
116
117DROP TABLE IF EXISTS maps;
118CREATE TABLE maps
119(
120 name VARCHAR(255),
121 size SMALLINT,
122 game_id SMALLINT,
123 game_mode VARCHAR(255),
124 team_size SMALLINT
125);
126
127INSERT INTO maps (name, size, game_id, game_mode, team_size)
128VALUES ('Default', 12, 1, 'Solo', 1),
129 ('Default', 12, 2, 'Solo', 1)
130;
131
132DROP TABLE IF EXISTS players;
133CREATE TABLE players
134(
135 uuid VARCHAR(40),
136 coins INT,
137 group_id SMALLINT,
138 guild_uuid VARCHAR(40),
139 last_connection_date DATETIME,
140 registration_date DATETIME,
141 PRIMARY KEY (uuid)
142);
143
144DROP TABLE IF EXISTS proxy_permissions;
145CREATE TABLE proxy_permissions
146(
147 group_id SMALLINT AUTO_INCREMENT,
148 proxy_command_party BOOLEAN,
149 proxy_command_friends BOOLEAN,
150 proxy_command_server BOOLEAN,
151 PRIMARY KEY (group_id)
152);
153
154INSERT INTO proxy_permissions(proxy_command_party, proxy_command_friends, proxy_command_server)
155VALUES (1, 1, 0),
156 (1, 1, 0),
157 (1, 1, 0),
158 (1, 1, 0),
159 (1, 1, 0),
160 (1, 1, 0),
161 (1, 1, 0),
162 (1, 1, 1)
163;
164
165DROP TABLE IF EXISTS servers_configuration;
166CREATE TABLE servers_configuration
167(
168 game_id INT AUTO_INCREMENT,
169 model_folder VARCHAR(255),
170 min_ram INT,
171 max_ram INT,
172 PRIMARY KEY (game_id)
173);
174
175INSERT INTO servers_configuration(model_folder, min_ram, max_ram)
176VALUES ('Bedwars', 1024, 1024),
177 ('Skywars', 1024, 1024)
178;