DatabaseCreation: ddl.sql

File ddl.sql, 14.9 KB (added by 231058, 5 days ago)
Line 
1CREATE TABLE linked_account_providers
2(
3 id BIGINT PRIMARY KEY,
4 name VARCHAR(50) NOT NULL UNIQUE
5);
6
7CREATE TABLE friendship_statuses
8(
9 id BIGINT PRIMARY KEY,
10 name VARCHAR(50) NOT NULL UNIQUE
11);
12
13CREATE TABLE subscription_plans
14(
15 id BIGINT PRIMARY KEY,
16 name VARCHAR(50) NOT NULL UNIQUE
17);
18
19CREATE TABLE notification_types
20(
21 id BIGINT PRIMARY KEY,
22 name VARCHAR(50) NOT NULL UNIQUE
23);
24
25CREATE TABLE team_member_roles
26(
27 id BIGINT PRIMARY KEY,
28 name VARCHAR(50) NOT NULL UNIQUE
29);
30
31CREATE TABLE match_statuses
32(
33 id BIGINT PRIMARY KEY,
34 name VARCHAR(50) NOT NULL UNIQUE
35);
36
37CREATE TABLE participant_results
38(
39 id BIGINT PRIMARY KEY,
40 name VARCHAR(50) NOT NULL UNIQUE
41);
42
43
44INSERT INTO linked_account_providers (id, name) VALUES
45(1, 'steam'),
46(2, 'discord'),
47(3, 'google'),
48(4, 'github');
49
50INSERT INTO friendship_statuses (id, name) VALUES
51(1, 'pending'),
52(2, 'accepted'),
53(3, 'rejected'),
54(4, 'blocked');
55
56INSERT INTO subscription_plans (id, name) VALUES
57(1, 'free'),
58(2, 'basic'),
59(3, 'premium'),
60(4, 'pro');
61
62INSERT INTO notification_types (id, name) VALUES
63(1, 'system'),
64(2, 'friend_request'),
65(3, 'message'),
66(4, 'achievement'),
67(5, 'tournament'),
68(6, 'subscription');
69
70INSERT INTO team_member_roles (id, name) VALUES
71(1, 'member'),
72(2, 'captain'),
73(3, 'coach'),
74(4, 'owner');
75
76INSERT INTO match_statuses (id, name) VALUES
77(1, 'scheduled'),
78(2, 'in_progress'),
79(3, 'completed'),
80(4, 'cancelled');
81
82INSERT INTO participant_results (id, name) VALUES
83(1, 'win'),
84(2, 'loss'),
85(3, 'draw');
86
87
88
89CREATE TABLE locations
90(
91 id BIGINT PRIMARY KEY,
92 latitude NUMERIC(9,6) NOT NULL CHECK (latitude BETWEEN -90 AND 90),
93 longitude NUMERIC(9,6) NOT NULL CHECK (longitude BETWEEN -180 AND 180),
94 country VARCHAR(100),
95 city VARCHAR(100),
96 UNIQUE (latitude, longitude)
97);
98
99CREATE TABLE users
100(
101 id BIGINT PRIMARY KEY,
102 username VARCHAR(30) NOT NULL UNIQUE,
103 email VARCHAR(100) NOT NULL UNIQUE,
104 password_hash TEXT NOT NULL,
105 location_id BIGINT NOT NULL DEFAULT 0
106 REFERENCES locations(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
107 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
108 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
109 CHECK (char_length(username) BETWEEN 3 AND 30)
110);
111
112CREATE TABLE profiles
113(
114 user_id BIGINT PRIMARY KEY
115 REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
116 avatar_url TEXT,
117 bio TEXT,
118 rank_points INT NOT NULL DEFAULT 1000 CHECK (rank_points >= 0)
119);
120
121CREATE TABLE developers
122(
123 id BIGINT PRIMARY KEY,
124 name VARCHAR(100) NOT NULL UNIQUE,
125 country VARCHAR(100)
126);
127
128CREATE TABLE genres
129(
130 id BIGINT PRIMARY KEY,
131 name VARCHAR(50) NOT NULL UNIQUE
132);
133
134CREATE TABLE games
135(
136 id BIGINT PRIMARY KEY,
137 title VARCHAR(100) NOT NULL UNIQUE,
138 release_date DATE NOT NULL CHECK (release_date <= CURRENT_DATE),
139 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
140);
141
142CREATE TABLE game_genres
143(
144 game_id BIGINT NOT NULL DEFAULT 0
145 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
146 genre_id BIGINT NOT NULL DEFAULT 0
147 REFERENCES genres(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
148 PRIMARY KEY (game_id, genre_id)
149);
150
151CREATE TABLE game_developers
152(
153 game_id BIGINT NOT NULL DEFAULT 0
154 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
155 developer_id BIGINT NOT NULL DEFAULT 0
156 REFERENCES developers(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
157 PRIMARY KEY (game_id, developer_id)
158);
159
160CREATE TABLE game_modes
161(
162 id BIGINT PRIMARY KEY,
163 game_id BIGINT NOT NULL DEFAULT 0
164 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
165 mode_name VARCHAR(50) NOT NULL,
166 UNIQUE (game_id, mode_name)
167);
168
169CREATE TABLE servers
170(
171 id BIGINT PRIMARY KEY,
172 game_id BIGINT NOT NULL DEFAULT 0
173 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
174 location_id BIGINT NOT NULL DEFAULT 0
175 REFERENCES locations(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
176 server_name VARCHAR(100) NOT NULL,
177 is_active BOOLEAN NOT NULL DEFAULT TRUE,
178 UNIQUE (game_id, server_name)
179);
180
181CREATE TABLE linked_accounts
182(
183 id BIGINT PRIMARY KEY,
184 user_id BIGINT NOT NULL DEFAULT 0
185 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
186 provider_id BIGINT NOT NULL DEFAULT 1
187 REFERENCES linked_account_providers(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
188 external_user_id VARCHAR(100) NOT NULL,
189 nickname VARCHAR(50),
190 avatar_url TEXT,
191 linked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
192 UNIQUE (provider_id, external_user_id),
193 UNIQUE (user_id, provider_id)
194);
195
196CREATE TABLE friendships
197(
198 id BIGINT PRIMARY KEY,
199 user_id_low BIGINT NOT NULL DEFAULT 0
200 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
201 user_id_high BIGINT NOT NULL DEFAULT 0
202 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
203 requested_by_user_id BIGINT NOT NULL DEFAULT 0
204 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
205 status_id BIGINT NOT NULL DEFAULT 1
206 REFERENCES friendship_statuses(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
207 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
208 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
209 CHECK (user_id_low < user_id_high),
210 CHECK (requested_by_user_id = user_id_low OR requested_by_user_id = user_id_high)
211);
212
213CREATE TABLE direct_messages
214(
215 id BIGINT PRIMARY KEY,
216 sender_id BIGINT NOT NULL DEFAULT 0
217 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
218 receiver_id BIGINT NOT NULL DEFAULT 0
219 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
220 content TEXT NOT NULL,
221 sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
222 read_at TIMESTAMP,
223 CHECK (sender_id <> receiver_id),
224 CHECK (read_at IS NULL OR read_at >= sent_at)
225);
226
227CREATE TABLE subscriptions
228(
229 id BIGINT PRIMARY KEY,
230 user_id BIGINT NOT NULL DEFAULT 0
231 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
232 plan_id BIGINT NOT NULL DEFAULT 1
233 REFERENCES subscription_plans(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
234 start_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
235 end_at TIMESTAMP,
236 is_active BOOLEAN NOT NULL DEFAULT TRUE,
237 CHECK (end_at IS NULL OR end_at > start_at)
238);
239
240CREATE TABLE payments
241(
242 id BIGINT PRIMARY KEY,
243 user_id BIGINT NOT NULL DEFAULT 0
244 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
245 subscription_id BIGINT NOT NULL DEFAULT 0
246 REFERENCES subscriptions(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
247 amount NUMERIC(12,2) NOT NULL CHECK (amount > 0),
248 currency_code CHAR(3) NOT NULL DEFAULT 'EUR',
249 paid_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
250 provider_reference VARCHAR(100) UNIQUE
251);
252
253CREATE TABLE preferences
254(
255 user_id BIGINT NOT NULL DEFAULT 0
256 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
257 game_id BIGINT NOT NULL DEFAULT 0
258 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
259 is_favorite BOOLEAN NOT NULL DEFAULT TRUE,
260 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
261 PRIMARY KEY (user_id, game_id)
262);
263
264CREATE TABLE notifications
265(
266 id BIGINT PRIMARY KEY,
267 user_id BIGINT NOT NULL DEFAULT 0
268 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
269 type_id BIGINT NOT NULL DEFAULT 1
270 REFERENCES notification_types(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
271 title VARCHAR(120) NOT NULL,
272 message TEXT NOT NULL,
273 is_read BOOLEAN NOT NULL DEFAULT FALSE,
274 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
275 preference_user_id BIGINT DEFAULT NULL,
276 preference_game_id BIGINT DEFAULT NULL,
277 FOREIGN KEY (preference_user_id, preference_game_id)
278 REFERENCES preferences(user_id, game_id) ON DELETE SET NULL ON UPDATE CASCADE
279);
280
281CREATE TABLE reviews
282(
283 id BIGINT PRIMARY KEY,
284 user_id BIGINT NOT NULL DEFAULT 0
285 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
286 game_id BIGINT NOT NULL DEFAULT 0
287 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
288 rating INT NOT NULL CHECK (rating BETWEEN 1 AND 10),
289 comment TEXT,
290 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
291 UNIQUE (user_id, game_id)
292);
293
294CREATE TABLE achievements
295(
296 id BIGINT PRIMARY KEY,
297 game_id BIGINT NOT NULL DEFAULT 0
298 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
299 title VARCHAR(100) NOT NULL,
300 description TEXT,
301 UNIQUE (game_id, title)
302);
303
304CREATE TABLE user_achievements
305(
306 id BIGINT PRIMARY KEY,
307 user_id BIGINT NOT NULL DEFAULT 0
308 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
309 achievement_id BIGINT NOT NULL DEFAULT 0
310 REFERENCES achievements(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
311 unlocked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
312);
313
314CREATE TABLE teams
315(
316 id BIGINT PRIMARY KEY,
317 name VARCHAR(50) NOT NULL UNIQUE,
318 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
319);
320
321CREATE TABLE team_memberships
322(
323 team_id BIGINT NOT NULL DEFAULT 0
324 REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
325 user_id BIGINT NOT NULL DEFAULT 0
326 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
327 role_id BIGINT NOT NULL DEFAULT 1
328 REFERENCES team_member_roles(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
329 joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
330 is_active BOOLEAN NOT NULL DEFAULT TRUE,
331 PRIMARY KEY (team_id, user_id)
332);
333
334CREATE TABLE tournaments
335(
336 id BIGINT PRIMARY KEY,
337 game_id BIGINT NOT NULL DEFAULT 0
338 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
339 name VARCHAR(100) NOT NULL,
340 organizer_team_id BIGINT NOT NULL DEFAULT 0
341 REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
342 prize_pool NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (prize_pool >= 0),
343 starts_at TIMESTAMP NOT NULL,
344 ends_at TIMESTAMP,
345 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
346 CHECK (ends_at IS NULL OR ends_at >= starts_at),
347 UNIQUE (game_id, name, starts_at)
348);
349
350CREATE TABLE tournament_teams
351(
352 tournament_id BIGINT NOT NULL DEFAULT 0
353 REFERENCES tournaments(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
354 team_id BIGINT NOT NULL DEFAULT 0
355 REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
356 joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
357 PRIMARY KEY (tournament_id, team_id)
358);
359
360
361CREATE TABLE matches
362(
363 id BIGINT,
364 game_id BIGINT NOT NULL DEFAULT 0 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
365 game_mode_id BIGINT NOT NULL DEFAULT 0 REFERENCES game_modes(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
366 tournament_id BIGINT NOT NULL DEFAULT 0 REFERENCES tournaments(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
367 server_id BIGINT NOT NULL DEFAULT 0 REFERENCES servers(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
368 status_id BIGINT NOT NULL DEFAULT 1 REFERENCES match_statuses(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
369 started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
370 ended_at TIMESTAMP,
371 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
372 CHECK (ended_at IS NULL OR started_at IS NULL OR ended_at >= started_at),
373 PRIMARY KEY (id, started_at)
374) PARTITION BY RANGE (started_at);
375
376CREATE TABLE matches_y2025 PARTITION OF matches
377 FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2026-01-01 00:00:00');
378
379CREATE TABLE matches_y2026 PARTITION OF matches
380 FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2027-01-01 00:00:00');
381
382CREATE TABLE matches_default PARTITION OF matches DEFAULT;
383
384
385CREATE TABLE match_teams
386(
387 match_id BIGINT NOT NULL DEFAULT 0,
388 match_started_at TIMESTAMP NOT NULL,
389 team_id BIGINT NOT NULL DEFAULT 0 REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
390 side_label VARCHAR(20),
391 PRIMARY KEY (match_id, team_id),
392 FOREIGN KEY (match_id, match_started_at) REFERENCES matches(id, started_at) ON DELETE CASCADE ON UPDATE CASCADE
393);
394
395CREATE TABLE match_participants
396(
397 id BIGINT PRIMARY KEY,
398 match_id BIGINT NOT NULL DEFAULT 0,
399 match_started_at TIMESTAMP NOT NULL,
400 user_id BIGINT NOT NULL DEFAULT 0 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
401 team_id BIGINT NOT NULL DEFAULT 0 REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
402 result_id BIGINT DEFAULT NULL REFERENCES participant_results(id) ON DELETE SET NULL ON UPDATE CASCADE,
403 score INT CHECK (score >= 0),
404 elo_change INT NOT NULL DEFAULT 0,
405 joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
406 UNIQUE (match_id, user_id),
407 FOREIGN KEY (match_id, team_id) REFERENCES match_teams(match_id, team_id),
408 FOREIGN KEY (match_id, match_started_at) REFERENCES matches(id, started_at) ON DELETE CASCADE ON UPDATE CASCADE
409);
410
411CREATE TABLE stat_types
412(
413 id BIGINT PRIMARY KEY,
414 game_id BIGINT NOT NULL DEFAULT 0
415 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
416 name VARCHAR(50) NOT NULL,
417 unit VARCHAR(20),
418 UNIQUE (game_id, name)
419);
420
421CREATE TABLE participant_stats
422(
423 match_participant_id BIGINT NOT NULL DEFAULT 0
424 REFERENCES match_participants(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
425 stat_type_id BIGINT NOT NULL DEFAULT 0
426 REFERENCES stat_types(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
427 value NUMERIC(12,2) NOT NULL DEFAULT 0,
428 PRIMARY KEY (match_participant_id, stat_type_id)
429);