45 lines
1.1 KiB
SQL
45 lines
1.1 KiB
SQL
CREATE TABLE Players (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
elo INT
|
|
);
|
|
|
|
CREATE TABLE Games (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
added DATETIME NOT NULL,
|
|
score INT NOT NULL,
|
|
overtime BOOL NOT NULL,
|
|
author INT,
|
|
team0player0 INT,
|
|
team0player1 INT,
|
|
team1player0 INT,
|
|
team1player1 INT,
|
|
FOREIGN KEY (author) REFERENCES Players(id) on DELETE SET NULL,
|
|
FOREIGN KEY (team0player0) REFERENCES Players(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (team0player1) REFERENCES Players(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (team1player0) REFERENCES Players(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (team1player1) REFERENCES Players(id) ON DELETE SET NULL
|
|
);
|
|
|
|
CREATE TABLE GameResults (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
game INT,
|
|
player INT,
|
|
startElo INT,
|
|
endElo INT,
|
|
FOREIGN KEY (game) REFERENCES Games(id) on DELETE SET NULL,
|
|
FOREIGN KEY (player) REFERENCES Players(id) on DELETE SET NULL
|
|
)
|
|
|
|
DELIMITER $$
|
|
CREATE TRIGGER UpdateCurrentELO
|
|
AFTER INSERT ON GameResults
|
|
FOR EACH ROW
|
|
BEGIN
|
|
UPDATE Players
|
|
SET elo = NEW.endElo
|
|
WHERE id = NEW.player;
|
|
END$$
|
|
|
|
DELIMITER ;
|