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                 db.get("SELECT last_insert_rowid() AS rowid", (err2,lastId) => {
  39                     players.forEach(p => {
  40                         query =
  41                             "INSERT INTO Players VALUES " +
  42               // Remaining time = -1 means "unstarted"
  43                             "(" + lastId["rowid"] + "," + p.id + "," + p.color + ", -1)";
  44                         db.run(query, cb);
  45                     });
  46                 });
  47             });
  48         });
  49     },
  50
  51     // TODO: queries here could be async, and wait for all to complete
  52     getOne: function(id, cb)
  53     {
  54         db.serialize(function() {
  55             let query =
  56                 "SELECT v.name AS vname, g.fen, g.fenStart, g.score " +
  57                 "FROM Games g " +
  58                 "JOIN Variants v " +
  59                 "  ON g.vid = v.id "
  60                 "WHERE id = " + id;
  61             db.get(query, (err,gameInfo) => {
  62                 if (!!err)
  63                     return cb(err);
  64                 query =
  65                     "SELECT p.uid AS id, p.color, p.rtime, u.name " +
  66                     "FROM Players p " +
  67                     "JOIN Users u " +
  68                     "  ON p.uid = u.id " +
  69                     "WHERE p.gid = " + id;
  70                 db.run(query, (err2,players) => {
  71                     if (!!err2)
  72                         return cb(err2);
  73                     query =
  74                         "SELECT move AS desc, message, played, idx, color " +
  75                         "FROM Moves " +
  76                         "WHERE gid = " + id;
  77                     db.run(query, (err3,moves) => {
  78                         if (!!err3)
  79                             return cb(err3);
  80                         const game = {
  81                             id: id,
  82                             vname: gameInfo.vname,
  83                             fenStart: gameInfo.fenStart,
  84                             fen: gameInfo.fen,
  85                             score: gameInfo.score,
  86                             players: players,
  87                             moves: moves,
  88                         };
  89                         return cb(null, game);
  90                     });
  91                 });
  92             });
  93         });
  94     },
  95
  96     getByUser: function(uid, excluded, cb)
  97     {
  98         db.serialize(function() {
  99             // Next query is fine because a player appear at most once in a game
 100             const query =
 101                 "SELECT gid " +
 102                 "FROM Players " +
 103                 "WHERE uid " + (excluded ? "<>" : "=") + " " + uid;
 104             db.run(query, (err,gameIds) => {
 105                 if (!!err)
 106                     return cb(err);
 107         gameIds = gameIds || []; //might be empty
 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;