server/models/Game.js

   1 var db = require("../utils/database");
   2
   3 /*
   4  * Structure table Games:
   5  *   id: game id (int)
   6  *   vid: integer (variant id)
   7  *   fenStart: varchar (initial position)
   8  *   fen: varchar (current position)
   9  *   timeControl: string
  10  *   score: varchar (result)
  11  *
  12  * Structure table Players:
  13  *   gid: ref game id
  14  *   uid: ref user id
  15  *   color: character
  16  *   rtime: real (remaining time)
  17  *
  18  * Structure table Moves:
  19  *   gid: ref game id
  20  *   move: varchar (description)
  21  *   message: text
  22  *   played: datetime
  23  *   idx: integer
  24  *   color: character
  25  */
  26
  27 const GameModel =
  28 {
  29     create: function(vid, fen, timeControl, players, cb)
  30     {
  31         db.serialize(function() {
  32             let query =
  33                 "INSERT INTO Games (vid, fen, timeControl) " +
  34                 "VALUES (" + vid + ",'" + fen + "'," + timeControl + ")";
  35             db.run(insertQuery, err => {
  36                 if (!!err)
  37                     return cb(err);
  38         players.forEach(p => {
  39           query =
  40             "INSERT INTO Players VALUES " +
  41             // Remaining time = -1 means "unstarted"
  42             "(" + this.lastID + "," + p.id + "," + p.color + ", -1)";
  43           db.run(query);
  44         });
  45         cb(null, {gid: this.lastID});
  46             });
  47         });
  48     },
  49
  50     // TODO: queries here could be async, and wait for all to complete
  51     getOne: function(id, cb)
  52     {
  53         db.serialize(function() {
  54             let query =
  55                 "SELECT v.name AS vname, g.fen, g.fenStart, g.score " +
  56                 "FROM Games g " +
  57                 "JOIN Variants v " +
  58                 "  ON g.vid = v.id "
  59                 "WHERE id = " + id;
  60             db.get(query, (err,gameInfo) => {
  61                 if (!!err)
  62                     return cb(err);
  63                 query =
  64                     "SELECT p.uid AS id, p.color, p.rtime, u.name " +
  65                     "FROM Players p " +
  66                     "JOIN Users u " +
  67                     "  ON p.uid = u.id " +
  68                     "WHERE p.gid = " + id;
  69                 db.run(query, (err2,players) => {
  70                     if (!!err2)
  71                         return cb(err2);
  72                     query =
  73                         "SELECT move AS desc, message, played, idx, color " +
  74                         "FROM Moves " +
  75                         "WHERE gid = " + id;
  76                     db.run(query, (err3,moves) => {
  77                         if (!!err3)
  78                             return cb(err3);
  79                         const game = {
  80                             id: id,
  81                             vname: gameInfo.vname,
  82                             fenStart: gameInfo.fenStart,
  83                             fen: gameInfo.fen,
  84                             score: gameInfo.score,
  85                             players: players,
  86                             moves: moves,
  87                         };
  88                         return cb(null, game);
  89                     });
  90                 });
  91             });
  92         });
  93     },
  94
  95     getByUser: function(uid, excluded, cb)
  96     {
  97         db.serialize(function() {
  98             // Next query is fine because a player appear at most once in a game
  99             const query =
 100                 "SELECT gid " +
 101                 "FROM Players " +
 102                 "WHERE uid " + (excluded ? "<>" : "=") + " " + uid;
 103             db.run(query, (err,gameIds) => {
 104                 if (!!err)
 105                     return cb(err);
 106         gameIds = gameIds || []; //might be empty
 107                 let gameArray = [];
 108                 gameIds.forEach(gidRow => {
 109                     GameModel.getOne(gidRow["gid"], (err2,game) => {
 110                         if (!!err2)
 111                             return cb(err2);
 112                         gameArray.push(game);
 113                     });
 114                 });
 115                 return cb(null, gameArray);
 116             });
 117         });
 118     },
 119
 120     remove: function(id)
 121     {
 122         db.parallelize(function() {
 123             let query =
 124                 "DELETE FROM Games " +
 125                 "WHERE id = " + id;
 126             db.run(query);
 127             query =
 128                 "DELETE FROM Players " +
 129                 "WHERE gid = " + id;
 130             db.run(query);
 131             query =
 132                 "DELETE FROM Moves " +
 133                 "WHERE gid = " + id;
 134             db.run(query);
 135         });
 136     },
 137 }
 138
 139 module.exports = GameModel;