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