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, fenStart, score, timeControl) " +
  34                 "VALUES (" + vid + ",'" + fen + "','*','" + timeControl + "')";
  35       db.run(query, function(err) {
  36         if (!!err)
  37           return cb(err);
  38         players.forEach((p,idx) => {
  39           const color = (idx==0 ? "w" : "b");
  40           query =
  41             "INSERT INTO Players VALUES " +
  42             // Remaining time = -1 means "unstarted"
  43             "(" + this.lastID + "," + p.id + ",'" + color + "', -1)";
  44           db.run(query);
  45         });
  46         cb(null, {gid: this.lastID});
  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 * " +
  57                 "FROM Games " +
  58                 "WHERE id = " + id;
  59             db.get(query, (err,gameInfo) => {
  60                 if (!!err)
  61                     return cb(err);
  62                 query =
  63                     "SELECT uid, color, rtime " +
  64                     "FROM Players " +
  65                     "WHERE gid = " + id;
  66                 db.all(query, (err2,players) => {
  67                     if (!!err2)
  68                         return cb(err2);
  69                     query =
  70                         "SELECT move, message, played, idx, color " +
  71                         "FROM Moves " +
  72                         "WHERE gid = " + id;
  73                     db.all(query, (err3,moves) => {
  74                         if (!!err3)
  75                             return cb(err3);
  76                         const game = Object.assign({},
  77               gameInfo,
  78                           {
  79                               players: players,
  80                               moves: moves
  81               }
  82             );
  83                         return cb(null, game);
  84                     });
  85                 });
  86             });
  87         });
  88     },
  89
  90     getByUser: function(uid, excluded, cb)
  91     {
  92         db.serialize(function() {
  93             // Next query is fine because a player appear at most once in a game
  94             const query =
  95                 "SELECT gid " +
  96                 "FROM Players " +
  97                 "WHERE uid " + (excluded ? "<>" : "=") + " " + uid;
  98             db.run(query, (err,gameIds) => {
  99                 if (!!err)
 100                     return cb(err);
 101         gameIds = gameIds || []; //might be empty
 102                 let gameArray = [];
 103                 gameIds.forEach(gidRow => {
 104                     GameModel.getOne(gidRow["gid"], (err2,game) => {
 105                         if (!!err2)
 106                             return cb(err2);
 107                         gameArray.push(game);
 108                     });
 109                 });
 110                 return cb(null, gameArray);
 111             });
 112         });
 113     },
 114
 115   getPlayers: function(id, cb)
 116   {
 117     db.serialize(function() {
 118       const query =
 119         "SELECT id " +
 120         "FROM Players " +
 121         "WHERE gid = " + id;
 122       db.all(query, (err,players) => {
 123         return cb(err, players);
 124       });
 125     });
 126   },
 127
 128   // obj can have fields move, fen and/or score
 129   update: function(id, obj, cb)
 130   {
 131         db.serialize(function() {
 132       let query =
 133         "UPDATE Games " +
 134         "SET ";
 135       if (!!obj.move)
 136         query += "move = " + obj.move + ","; //TODO: already stringified?!
 137       if (!!obj.fen)
 138         query += "fen = " + obj.fen + ",";
 139       if (!!obj.score)
 140         query += "score = " + obj.score + ",";
 141       query = query.slice(0,-1); //remove last comma
 142       query += " WHERE gameId = " + id;
 143       db.run(query, (err) => {
 144         cb(err);
 145       });
 146     });
 147   },
 148
 149     remove: function(id)
 150     {
 151         db.parallelize(function() {
 152             let query =
 153                 "DELETE FROM Games " +
 154                 "WHERE id = " + id;
 155             db.run(query);
 156             query =
 157                 "DELETE FROM Players " +
 158                 "WHERE gid = " + id;
 159             db.run(query);
 160             query =
 161                 "DELETE FROM Moves " +
 162                 "WHERE gid = " + id;
 163             db.run(query);
 164         });
 165     },
 166 }
 167
 168 module.exports = GameModel;