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 DELIMITER //
  46 CREATE FUNCTION get_streak (pid INT) RETURNS INT READS SQL DATA
  47         BEGIN
  48                 DECLARE streak INT DEFAULT 0;
  49                 DECLARE win BOOL;
  50                 DECLARE base BOOL DEFAULT 1;
  51                 DECLARE done BOOL DEFAULT 0;
  52                 DECLARE first BOOL DEFAULT 1;
  53                 DECLARE c CURSOR FOR SELECT IF(bid = pid, bid = winid, wid = winid) FROM player_game WHERE bid = pid OR wid = pid;
  54                 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  55                 OPEN c;
  56                 REPEAT
  57                         FETCH c INTO win;
  58                         IF NOT done THEN
  59                                 IF first THEN
  60                                         SET base = win;
  61                                         SET first = 0;
  62                                 END IF;
  63                                 IF win = base THEN
  64                                         IF base THEN
  65                                                 SET streak = streak + 1;
  66                                         ELSE
  67                                                 SET streak = streak - 1;
  68                                         END IF;
  69                                 ELSE
  70                                         SET done = 1;
  71                                 END IF;
  72                         END IF;
  73                 UNTIL done END REPEAT;
  74                 CLOSE c;
  75                 RETURN streak;
  76         END //
  77 DELIMITER ;
  78
  79 CREATE VIEW winstats AS
  80         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;
  81
  82 CREATE VIEW raw_ladder AS
  83         SELECT p.id, p.nick, p.rank, p.score, w.wincount, w.losscount, w.wincount/(w.wincount+w.losscount) AS winratio, 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 ORDER BY p.score DESC, (w.wincount/(w.wincount+w.losscount)) DESC;
  84
  85 CREATE VIEW ladder AS
  86         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, 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;
  87
  88 CREATE VIEW player_info AS
  89         SELECT p.id AS id, p.nick AS nick, p.rank AS rank, TRUNCATE(p.score, 2) AS score, p.last_update AS last_update FROM player AS p;