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  *
  17  * Structure table Moves:
  18  *   gid: ref game id
  19  *   squares: varchar (description)
  20  *   message: text
  21  *   played: datetime
  22  *   idx: integer
  23  */
  24
  25 const GameModel =
  26 {
  27     create: function(vid, fen, timeControl, players, cb)
  28     {
  29         db.serialize(function() {
  30             let query =
  31                 "INSERT INTO Games (vid, fenStart, fen, score, timeControl) VALUES " +
  32         "(" + vid + ",'" + fen + "','" + fen + "','*','" + timeControl + "')";
  33       db.run(query, function(err) {
  34         if (!!err)
  35           return cb(err);
  36         players.forEach((p,idx) => {
  37           const color = (idx==0 ? "w" : "b");
  38           query =
  39             "INSERT INTO Players VALUES " +
  40             "(" + this.lastID + "," + p.id + ",'" + color + "')";
  41           db.run(query);
  42         });
  43         cb(null, {gid: this.lastID});
  44             });
  45         });
  46     },
  47
  48     // TODO: queries here could be async, and wait for all to complete
  49     getOne: function(id, cb)
  50     {
  51         db.serialize(function() {
  52       // TODO: optimize queries?
  53             let query =
  54                 "SELECT g.id, g.vid, g.fen, g.fenStart, g.timeControl, g.score, " +
  55           "v.name AS vname " +
  56                 "FROM Games g " +
  57         "JOIN Variants v " +
  58         "  ON g.vid = v.id " +
  59                 "WHERE g.id = " + id;
  60             db.get(query, (err,gameInfo) => {
  61                 if (!!err)
  62                     return cb(err);
  63                 query =
  64                     "SELECT p.uid, p.color, u.name " +
  65                     "FROM Players p " +
  66           "JOIN Users u " +
  67           "  ON p.uid = u.id " +
  68                     "WHERE p.gid = " + id;
  69                 db.all(query, (err2,players) => {
  70                     if (!!err2)
  71                         return cb(err2);
  72                     query =
  73                         "SELECT squares, message, played, idx " +
  74                         "FROM Moves " +
  75                         "WHERE gid = " + id;
  76                     db.all(query, (err3,moves) => {
  77                         if (!!err3)
  78                             return cb(err3);
  79                         const game = Object.assign({},
  80               gameInfo,
  81                           {
  82                               players: players,
  83                               moves: moves
  84               }
  85             );
  86                         return cb(null, game);
  87                     });
  88                 });
  89             });
  90         });
  91     },
  92
  93     getByUser: function(uid, excluded, cb)
  94     {
  95         db.serialize(function() {
  96             // Next query is fine because a player appear at most once in a game
  97             const query =
  98                 "SELECT gid " +
  99                 "FROM Players " +
 100                 "WHERE uid " + (excluded ? "<>" : "=") + " " + uid;
 101             db.run(query, (err,gameIds) => {
 102                 if (!!err)
 103                     return cb(err);
 104         gameIds = gameIds || []; //might be empty
 105                 let gameArray = [];
 106                 gameIds.forEach(gidRow => {
 107                     GameModel.getOne(gidRow["gid"], (err2,game) => {
 108                         if (!!err2)
 109                             return cb(err2);
 110                         gameArray.push(game);
 111                     });
 112                 });
 113                 return cb(null, gameArray);
 114             });
 115         });
 116     },
 117
 118   getPlayers: function(id, cb)
 119   {
 120     db.serialize(function() {
 121       const query =
 122         "SELECT id " +
 123         "FROM Players " +
 124         "WHERE gid = " + id;
 125       db.all(query, (err,players) => {
 126         return cb(err, players);
 127       });
 128     });
 129   },
 130
 131   // obj can have fields move, fen and/or score
 132   update: function(id, obj)
 133   {
 134         db.parallelize(function() {
 135       let query =
 136         "UPDATE Games " +
 137         "SET ";
 138       if (!!obj.fen)
 139         query += "fen = '" + obj.fen + "',";
 140       if (!!obj.score)
 141         query += "score = '" + obj.score + "',";
 142       query = query.slice(0,-1); //remove last comma
 143       query += " WHERE id = " + id;
 144       db.run(query);
 145       if (!!obj.move)
 146       {
 147         const m = obj.move;
 148         query =
 149           "INSERT INTO Moves (gid, squares, message, played, idx) VALUES " +
 150           "(" + id + ",'" + JSON.stringify(m.squares) + "','" + m.message +
 151             "'," + m.played + "," + m.idx + ")";
 152         db.run(query);
 153       }
 154     });
 155   },
 156
 157     remove: function(id)
 158     {
 159         db.parallelize(function() {
 160             let query =
 161                 "DELETE FROM Games " +
 162                 "WHERE id = " + id;
 163             db.run(query);
 164             query =
 165                 "DELETE FROM Players " +
 166                 "WHERE gid = " + id;
 167             db.run(query);
 168             query =
 169                 "DELETE FROM Moves " +
 170                 "WHERE gid = " + id;
 171             db.run(query);
 172         });
 173     },
 174 }
 175
 176 module.exports = GameModel;