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.all(query, (err,gameIds) => {
 102                 if (!!err)
 103                     return cb(err);
 104         gameIds = gameIds || []; //might be empty
 105                 let gameArray = [];
 106                 for (let i=0; i<gameIds.length; i++)
 107                 {
 108                     GameModel.getOne(gameIds[i]["gid"], (err2,game) => {
 109                         if (!!err2)
 110                             return cb(err2);
 111                         gameArray.push(game);
 112                         // Call callback function only when gameArray is complete:
 113                         if (i == gameIds.length - 1)
 114                             return cb(null, gameArray);
 115                     });
 116                 }
 117             });
 118         });
 119     },
 120
 121   getPlayers: function(id, cb)
 122   {
 123     db.serialize(function() {
 124       const query =
 125         "SELECT id " +
 126         "FROM Players " +
 127         "WHERE gid = " + id;
 128       db.all(query, (err,players) => {
 129         return cb(err, players);
 130       });
 131     });
 132   },
 133
 134   // obj can have fields move, fen and/or score
 135   update: function(id, obj)
 136   {
 137         db.parallelize(function() {
 138       let query =
 139         "UPDATE Games " +
 140         "SET ";
 141       if (!!obj.fen)
 142         query += "fen = '" + obj.fen + "',";
 143       if (!!obj.score)
 144         query += "score = '" + obj.score + "',";
 145       query = query.slice(0,-1); //remove last comma
 146       query += " WHERE id = " + id;
 147       db.run(query);
 148       if (!!obj.move)
 149       {
 150         const m = obj.move;
 151         query =
 152           "INSERT INTO Moves (gid, squares, message, played, idx) VALUES " +
 153           "(" + id + ",'" + JSON.stringify(m.squares) + "','" + m.message +
 154             "'," + m.played + "," + m.idx + ")";
 155         db.run(query);
 156       }
 157     });
 158   },
 159
 160     remove: function(id)
 161     {
 162         db.parallelize(function() {
 163             let query =
 164                 "DELETE FROM Games " +
 165                 "WHERE id = " + id;
 166             db.run(query);
 167             query =
 168                 "DELETE FROM Players " +
 169                 "WHERE gid = " + id;
 170             db.run(query);
 171             query =
 172                 "DELETE FROM Moves " +
 173                 "WHERE gid = " + id;
 174             db.run(query);
 175         });
 176     },
 177 }
 178
 179 module.exports = GameModel;