-
Notifications
You must be signed in to change notification settings - Fork 0
/
online-chess.sql
94 lines (83 loc) · 2.55 KB
/
online-chess.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
-- Initialisation
CREATE DATABASE onlineChess;
USE onlineChess;
-- Tables
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE,
email VARCHAR(255) UNIQUE,
password VARCHAR(255)
);
CREATE TABLE user_info(
user_id INT,
user_rank ENUM('beginner', 'intermediate', 'advanced', 'expert') DEFAULT 'beginner',
user_points INT DEFAULT 1000,
KEY userID(user_id),
CONSTRAINT userID FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE games(
id INT AUTO_INCREMENT PRIMARY KEY,
timer VARCHAR(2),
moves TEXT NOT NULL,
user_id_light INT,
user_id_black INT,
started_at TIMESTAMP NOT NULL,
completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
KEY userID_Light(user_id_light),
CONSTRAINT userID_Light FOREIGN KEY(user_id_light) REFERENCES users(id) ON DELETE CASCADE,
KEY userID_Black(user_id_black),
CONSTRAINT userID_Black FOREIGN KEY(user_id_black) REFERENCES users(id) ON DELETE CASCADE
);
-- Procedures
DELIMITER $$
CREATE PROCEDURE createUser(
IN _username VARCHAR(255),
IN _email VARCHAR(255),
IN _password VARCHAR(255)
)
BEGIN
DECLARE userId INT;
INSERT INTO users(username, email, password) VALUES(_username, _email, _password);
SELECT id INTO userId FROM users WHERE username=_username;
INSERT INTO user_info(user_id) VALUE(userId);
END $$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE updateScores(
IN username_1 VARCHAR(255),
IN points_1 INT,
IN username_2 VARCHAR(255),
IN points_2 INT
)
BEGIN
DECLARE userId_1 INT;
DECLARE userId_2 INT;
DECLARE user_rank_1 VARCHAR(20) DEFAULT "beginner";
DECLARE user_rank_2 VARCHAR(20) DEFAULT "beginner";
SELECT id INTO userId_1 FROM users WHERE username=username_1;
SELECT id INTO userId_2 FROM users WHERE username=username_2;
IF points_1 < 2000 THEN
SET user_rank_1 := "beginner";
ELSEIF points_1 < 3000 THEN
SET user_rank_1 := "intermediate";
ELSEIF points_1 < 4000 THEN
SET user_rank_1 := "advanced";
ELSE
SET user_rank_1 := "expert";
END IF;
IF points_2 < 2000 THEN
SET user_rank_2 := "beginner";
ELSEIF points_2 < 3000 THEN
SET user_rank_2 := "intermediate";
ELSEIF points_2 < 4000 THEN
SET user_rank_2 := "advanced";
ELSE
SET user_rank_2 := "expert";
END IF;
UPDATE user_info SET user_points=points_1, user_rank=user_rank_1 WHERE user_id=userId_1;
UPDATE user_info SET user_points=points_2, user_rank=user_rank_2 WHERE user_id=userId_2;
END $$
DELIMITER ;
SELECT * FROM users;
SELECT * FROM user_info;
SELECT * FROM games;