SQL

   1 CREATE TABLE player (
   2         id INT AUTO_INCREMENT PRIMARY KEY,
   3
   4         nick VARCHAR(16) NOT NULL,
   5         rank INT, # conv. to int (<=0 are dans), NULL = [-] or [?]
   6
   7         last_update TIMESTAMP NULL,
   8
   9         score DOUBLE NOT NULL DEFAULT 100
  10 ) ENGINE=InnoDB;
  11
  12 CREATE TABLE game (
  13         id INT AUTO_INCREMENT PRIMARY KEY,
  14
  15         time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  16         winner ENUM ("black", "white") NOT NULL,
  17         url VARCHAR(255) NOT NULL, UNIQUE(url)
  18 ) ENGINE=InnoDB;
  19
  20 CREATE TABLE ladder_game (
  21         id INT PRIMARY KEY,
  22         FOREIGN KEY (id) REFERENCES game (id),
  23
  24         black INT, FOREIGN KEY (black) REFERENCES player (id),
  25         white INT, FOREIGN KEY (white) REFERENCES player (id),
  26
  27         black_score_old DOUBLE NOT NULL,
  28         white_score_old DOUBLE NOT NULL,
  29         black_score_new DOUBLE NOT NULL,
  30         white_score_new DOUBLE NOT NULL
  31 );
  32
  33 CREATE VIEW full_game AS
  34         SELECT g.id, DATE(g.time) AS date, g.winner, g.url, lg.black, lg.white, IF(g.winner = 'black', lg.black, lg.white) AS winid, IF(g.winner = 'black', lg.black_score_new-lg.black_score_old, lg.white_score_new-lg.white_score_old) AS scoredelta FROM game AS g, ladder_game AS lg WHERE g.id = lg.id ORDER BY g.time DESC;
  35
  36 CREATE VIEW player_game AS
  37         SELECT fg.white AS wid, w.nick AS wnick, fg.black AS bid, b.nick AS bnick, fg.url AS url, fg.date AS date, TRUNCATE(fg.scoredelta, 2) AS scoredelta, fg.winid AS winid FROM full_game AS fg LEFT JOIN player AS b ON b.id = black LEFT JOIN player AS w ON w.id = white;
  38
  39 CREATE VIEW last_game_per_player AS
  40         SELECT (SELECT fg.id FROM full_game AS fg WHERE fg.black = p.id OR fg.white = p.id ORDER BY fg.id DESC LIMIT 1) AS gid, p.id AS pid FROM player AS p;
  41
  42 CREATE VIEW player_last_game AS
  43         SELECT p.id AS pid, p.nick AS nick, UNIX_TIMESTAMP(p.last_update) AS last_update, l.time AS last_game, IF(fg.winid = p.id, fg.scoredelta, 0) AS scoredelta FROM player AS p LEFT JOIN last_game_per_player AS lgpp ON lgpp.pid = p.id LEFT JOIN game AS l ON l.id = lgpp.gid LEFT JOIN full_game AS fg ON fg.id = lgpp.gid;
  44
  45 CREATE VIEW player_games_this_month AS
  46         SELECT p.id AS pid, (SELECT COUNT(*) FROM player_game AS pg WHERE (pg.wid = pid OR pg.bid = pid) AND YEAR(pg.date) = YEAR(CURDATE()) AND MONTH(pg.date) = MONTH(CURDATE())) AS games FROM player AS p;
  47
  48 DELIMITER //
  49 CREATE FUNCTION get_streak (pid INT) RETURNS INT READS SQL DATA
  50         BEGIN
  51                 DECLARE streak INT DEFAULT 0;
  52                 DECLARE win BOOL;
  53                 DECLARE base BOOL DEFAULT 1;
  54                 DECLARE done BOOL DEFAULT 0;
  55                 DECLARE first BOOL DEFAULT 1;
  56                 DECLARE c CURSOR FOR SELECT IF(bid = pid, bid = winid, wid = winid) FROM player_game WHERE bid = pid OR wid = pid;
  57                 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  58                 OPEN c;
  59                 REPEAT
  60                         FETCH c INTO win;
  61                         IF NOT done THEN
  62                                 IF first THEN
  63                                         SET base = win;
  64                                         SET first = 0;
  65                                 END IF;
  66                                 IF win = base THEN
  67                                         IF base THEN
  68                                                 SET streak = streak + 1;
  69                                         ELSE
  70                                                 SET streak = streak - 1;
  71                                         END IF;
  72                                 ELSE
  73                                         SET done = 1;
  74                                 END IF;
  75                         END IF;
  76                 UNTIL done END REPEAT;
  77                 CLOSE c;
  78                 RETURN streak;
  79         END //
  80 DELIMITER ;
  81
  82 CREATE VIEW winstats AS
  83         SELECT p.id AS id, (SELECT COUNT(*) FROM full_game AS fg WHERE (fg.black = p.id AND fg.winner = 'black') OR (fg.white = p.id AND fg.winner = 'white')) AS wincount, (SELECT COUNT(*) FROM full_game AS fg WHERE (fg.black = p.id AND fg.winner = 'white') OR (fg.white = p.id AND fg.winner = 'black')) AS losscount FROM player AS p;
  84
  85 CREATE VIEW raw_ladder AS
  86         SELECT p.id, p.nick, p.rank, p.score, w.wincount, w.losscount, w.wincount/(w.wincount+w.losscount) AS winratio, pgm.games AS mgames, plg.last_game < DATE_SUB(NOW(), INTERVAL 7 DAY) AS inactive, plg.last_game, plg.scoredelta, get_streak(p.id) AS streak FROM player AS p LEFT JOIN winstats AS w ON w.id = p.id LEFT JOIN player_last_game AS plg ON plg.pid = p.id LEFT JOIN player_games_this_month AS pgm ON pgm.pid = p.id ORDER BY p.score DESC, (w.wincount/(w.wincount+w.losscount)) DESC;
  87
  88 CREATE VIEW ladder AS
  89         SELECT id, CONCAT(nick, ' [', IF(rank IS NOT NULL,IF(rank>0,CONCAT(rank,'k'),CONCAT(1-rank,'d')),'-'),']') AS player, TRUNCATE(score, 2) AS score, wincount, losscount, CONCAT(TRUNCATE(100*winratio,2),'%') AS winratio, mgames, CONCAT('<span', IF(inactive, ' class="inactive"', ''), '>', DATE(last_game), '</span>') AS lastgame, TRUNCATE(scoredelta, 2) AS scoredelta, IF((streak < 3), '', CONCAT('<span class="', IF(inactive, 'inactive-streak', IF(streak < 0, 'loss-streak', 'win-streak')), '">', ABS(streak), ' ', IF(streak < 0, 'losses', 'wins'), '</span>')) AS streak FROM raw_ladder;
  90
  91 CREATE VIEW player_info AS
  92         SELECT p.id AS id, p.nick AS nick, p.rank AS rank, TRUNCATE(p.score, 2) AS score, pgm.games AS mgames, p.last_update AS last_update FROM player AS p LEFT JOIN player_games_this_month AS pgm ON pgm.pid = p.id;