server/models/Game.js

   1 const db = require("../utils/database");
   2 const UserModel = require("./User");
   3
   4 /*
   5  * Structure table Games:
   6  *   id: game id (int)
   7  *   vid: integer (variant id)
   8  *   fenStart: varchar (initial position)
   9  *   fen: varchar (current position)
  10  *   white: integer
  11  *   black: integer
  12  *   cadence: string
  13  *   score: varchar (result)
  14  *   scoreMsg: varchar ("Time", "Mutual agreement"...)
  15  *   created: datetime
  16  *   drawOffer: char ('w','b' or '' for none)
  17  *   rematchOffer: char (similar to drawOffer)
  18  *   randomness: integer
  19  *   deletedByWhite: boolean
  20  *   deletedByBlack: boolean
  21  *
  22  * Structure table Moves:
  23  *   gid: ref game id
  24  *   squares: varchar (description)
  25  *   played: datetime
  26  *   idx: integer
  27  *
  28  * Structure table Chats:
  29  *   gid: game id (int)
  30  *   msg: varchar
  31  *   name: varchar
  32  *   added: datetime
  33  */
  34
  35 const GameModel =
  36 {
  37   checkGameInfo: function(g) {
  38     return (
  39       g.vid.toString().match(/^[0-9]+$/) &&
  40       g.cadence.match(/^[0-9dhms +]+$/) &&
  41       g.randomness.match(/^[0-2]$/) &&
  42       g.fen.match(/^[a-zA-Z0-9, /-]*$/) &&
  43       g.players.length == 2 &&
  44       g.players.every(p => p.toString().match(/^[0-9]+$/))
  45     );
  46   },
  47
  48   create: function(vid, fen, randomness, cadence, players, cb) {
  49     db.serialize(function() {
  50       let query =
  51         "INSERT INTO Games " +
  52         "(" +
  53           "vid, fenStart, fen, randomness, " +
  54           "white, black, " +
  55           "cadence, created" +
  56         ") " +
  57         "VALUES " +
  58         "(" +
  59           vid + ",'" + fen + "','" + fen + "'," + randomness + "," +
  60           "'" + players[0] + "','" + players[1] + "," +
  61           "'" + cadence + "'," + Date.now() +
  62         ")";
  63       db.run(query, function(err) {
  64         cb(err, { id: this.lastId });
  65       });
  66     });
  67   },
  68
  69   // TODO: some queries here could be async
  70   getOne: function(id, cb) {
  71     // NOTE: ignoring errors (shouldn't happen at this stage)
  72     db.serialize(function() {
  73       let query =
  74         "SELECT " +
  75           "g.id, g.vid, g.fen, g.fenStart, g.cadence, g.created, " +
  76           "g.white, g.black, g.score, g.scoreMsg, " +
  77           "g.drawOffer, g.rematchOffer, v.name AS vname " +
  78         "FROM Games g " +
  79         "JOIN Variants v " +
  80         "  ON g.vid = v.id " +
  81         "WHERE g.id = " + id;
  82       db.get(query, (err, gameInfo) => {
  83         query =
  84           "SELECT squares, played, idx " +
  85           "FROM Moves " +
  86           "WHERE gid = " + id;
  87         db.all(query, (err3, moves) => {
  88           query =
  89             "SELECT msg, name, added " +
  90             "FROM Chats " +
  91             "WHERE gid = " + id;
  92           db.all(query, (err4, chats) => {
  93             const game = Object.assign(
  94               {},
  95               gameInfo,
  96               {
  97                 moves: moves,
  98                 chats: chats
  99               }
 100             );
 101             cb(null, game);
 102           });
 103         });
 104       });
 105     });
 106   },
 107
 108   // For display on Hall: no need for moves or chats
 109   getObserved: function(uid, cursor, cb) {
 110     db.serialize(function() {
 111       let query =
 112         "SELECT g.id, g.vid, g.cadence, g.created, " +
 113         "       g.score, g.white, g.black " +
 114         "FROM Games g ";
 115       if (uid > 0) query +=
 116         "WHERE " +
 117         "  created < " + cursor + " AND " +
 118         "  white <> " + uid + " AND " +
 119         "  black <> " + uid + " ";
 120       query +=
 121         "ORDER BY created DESC " +
 122         "LIMIT 20"; //TODO: 20 hard-coded...
 123       db.all(query, (err, games) => {
 124         // Query players names
 125         let pids = {};
 126         games.forEach(g => {
 127           if (!pids[g.white]) pids[g.white] = true;
 128           if (!pids[g.black]) pids[g.black] = true;
 129         });
 130         UserModel.getByIds(Object.keys(pids), (err2, users) => {
 131           let names = {};
 132           users.forEach(u => { names[u.id] = u.name; });
 133           cb(
 134             games.map(
 135               g => {
 136                 return {
 137                   id: g.id,
 138                   cadence: g.cadence,
 139                   vname: g.vname,
 140                   created: g.created,
 141                   score: g.score,
 142                   white: names[g.white],
 143                   black: names[g.black]
 144                 };
 145               }
 146             )
 147           );
 148         });
 149       });
 150     });
 151   },
 152
 153   // For display on MyGames: registered user only
 154   getRunning: function(uid, cb) {
 155     db.serialize(function() {
 156       let query =
 157         "SELECT g.id, g.cadence, g.created, g.score, " +
 158           "g.white, g.black, v.name AS vname " +
 159         "FROM Games g " +
 160         "JOIN Variants v " +
 161         "  ON g.vid = v.id " +
 162         "WHERE white = " + uid + " OR black = " + uid;
 163       db.all(query, (err, games) => {
 164         // Get movesCount (could be done in // with next query)
 165         query =
 166           "SELECT gid, COUNT(*) AS nbMoves " +
 167           "FROM Moves " +
 168           "WHERE gid IN " + "(" + games.map(g => g.id).join(",") + ") " +
 169           "GROUP BY gid";
 170         db.all(query, (err, mstats) => {
 171           let movesCounts = {};
 172           mstats.forEach(ms => { movesCounts[ms.gid] = ms.nbMoves; });
 173           // Query player names
 174           let pids = {};
 175           games.forEach(g => {
 176             if (!pids[g.white]) pids[g.white] = true;
 177             if (!pids[g.black]) pids[g.black] = true;
 178           });
 179           UserModel.getByIds(pids, (err2, users) => {
 180             let names = {};
 181             users.forEach(u => { names[u.id] = u.name; });
 182             cb(
 183               games.map(
 184                 g => {
 185                   return {
 186                     id: g.id,
 187                     cadence: g.cadence,
 188                     vname: g.vname,
 189                     created: g.created,
 190                     score: g.score,
 191                     movesCount: movesCounts[g.id],
 192                     white: names[g.white],
 193                     black: names[g.black]
 194                   };
 195                 }
 196               )
 197             );
 198           });
 199         });
 200       });
 201     });
 202   },
 203
 204   // These games could be deleted on some side. movesCount not required
 205   getCompleted: function(uid, cursor, cb) {
 206     db.serialize(function() {
 207       let query =
 208         "SELECT g.id, g.cadence, g.created, g.score, g.scoreMsg, " +
 209           "g.white, g.black, g.deletedByWhite, g.deletedByBlack, " +
 210           "v.name AS vname " +
 211         "FROM Games g " +
 212         "JOIN Variants v " +
 213         "  ON g.vid = v.id " +
 214         "WHERE " +
 215         "  created < " + cursor + " AND " +
 216         "  (" +
 217         "    (" + uid + " = white AND NOT deletedByWhite) OR " +
 218         "    (" + uid + " = black AND NOT deletedByBlack)" +
 219         "  ) ";
 220       query +=
 221         "ORDER BY created DESC " +
 222         "LIMIT 20";
 223       db.all(query, (err, games) => {
 224         // Query player names
 225         let pids = {};
 226         games.forEach(g => {
 227           if (!pids[g.white]) pids[g.white] = true;
 228           if (!pids[g.black]) pids[g.black] = true;
 229         });
 230         UserModel.getByIds(pids, (err2, users) => {
 231           let names = {};
 232           users.forEach(u => { names[u.id] = u.name; });
 233           cb(
 234             games.map(
 235               g => {
 236                 return {
 237                   id: g.id,
 238                   cadence: g.cadence,
 239                   vname: g.vname,
 240                   created: g.created,
 241                   score: g.score,
 242                   scoreMsg: g.scoreMsg,
 243                   white: names[g.white],
 244                   black: names[g.black],
 245                   deletedByWhite: g.deletedByWhite,
 246                   deletedByBlack: g.deletedByBlack
 247                 };
 248               }
 249             )
 250           );
 251         });
 252       });
 253     });
 254   },
 255
 256   getPlayers: function(id, cb) {
 257     db.serialize(function() {
 258       const query =
 259         "SELECT white, black " +
 260         "FROM Games " +
 261         "WHERE gid = " + id;
 262       db.all(query, (err, players) => {
 263         return cb(err, players);
 264       });
 265     });
 266   },
 267
 268   checkGameUpdate: function(obj) {
 269     // Check all that is possible (required) in obj:
 270     return (
 271       (
 272         !obj.move || (
 273           !!(obj.move.played.toString().match(/^[0-9]+$/)) &&
 274           !!(obj.move.idx.toString().match(/^[0-9]+$/))
 275         )
 276       ) && (
 277         !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/))
 278       ) && (
 279         !obj.rematchOffer || !!(obj.rematchOffer.match(/^[wbn]$/))
 280       ) && (
 281         !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9, /-]*$/))
 282       ) && (
 283         !obj.score || !!(obj.score.match(/^[012?*\/-]+$/))
 284       ) && (
 285         !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/))
 286       ) && (
 287         !obj.chat || UserModel.checkNameEmail({name: obj.chat.name})
 288       )
 289     );
 290   },
 291
 292   // obj can have fields move, chat, fen, drawOffer and/or score + message
 293   update: function(id, obj, cb) {
 294     db.parallelize(function() {
 295       let query =
 296         "UPDATE Games " +
 297         "SET ";
 298       let modifs = "";
 299       // NOTE: if drawOffer is set, we should check that it's player's turn
 300       // A bit overcomplicated. Let's trust the client on that for now...
 301       if (!!obj.drawOffer)
 302       {
 303         if (obj.drawOffer == "n") //special "None" update
 304           obj.drawOffer = "";
 305         modifs += "drawOffer = '" + obj.drawOffer + "',";
 306       }
 307       if (!!obj.rematchOffer)
 308       {
 309         if (obj.rematchOffer == "n") //special "None" update
 310           obj.rematchOffer = "";
 311         modifs += "rematchOffer = '" + obj.rematchOffer + "',";
 312       }
 313       if (!!obj.fen)
 314         modifs += "fen = '" + obj.fen + "',";
 315       if (!!obj.score)
 316         modifs += "score = '" + obj.score + "',";
 317       if (!!obj.scoreMsg)
 318         modifs += "scoreMsg = '" + obj.scoreMsg + "',";
 319       if (!!obj.deletedBy) {
 320         const myColor = obj.deletedBy == 'w' ? "White" : "Black";
 321         modifs += "deletedBy" + myColor + " = true,";
 322       }
 323       modifs = modifs.slice(0,-1); //remove last comma
 324       if (modifs.length > 0)
 325       {
 326         query += modifs + " WHERE id = " + id;
 327         db.run(query);
 328       }
 329       // NOTE: move, chat and delchat are mutually exclusive
 330       if (!!obj.move)
 331       {
 332         // Security: only update moves if index is right
 333         query =
 334           "SELECT MAX(idx) AS maxIdx " +
 335           "FROM Moves " +
 336           "WHERE gid = " + id;
 337         db.get(query, (err,ret) => {
 338           const m = obj.move;
 339           if (!ret.maxIdx || ret.maxIdx + 1 == m.idx) {
 340             query =
 341               "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
 342               "(" + id + ",?," + m.played + "," + m.idx + ")";
 343             db.run(query, JSON.stringify(m.squares));
 344             cb(null);
 345           }
 346           else cb({errmsg:"Wrong move index"});
 347         });
 348       }
 349       else cb(null);
 350       if (!!obj.chat)
 351       {
 352         query =
 353           "INSERT INTO Chats (gid, msg, name, added) VALUES ("
 354             + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")";
 355         db.run(query, obj.chat.msg);
 356       }
 357       else if (obj.delchat)
 358       {
 359         query =
 360           "DELETE " +
 361           "FROM Chats " +
 362           "WHERE gid = " + id;
 363         db.run(query);
 364       }
 365       if (!!obj.deletedBy) {
 366         // Did my opponent delete it too?
 367         let selection =
 368           "deletedBy" +
 369           (obj.deletedBy == 'w' ? "Black" : "White") +
 370           " AS deletedByOpp";
 371         query =
 372           "SELECT " + selection + " " +
 373           "FROM Games " +
 374           "WHERE id = " + id;
 375         db.get(query, (err,ret) => {
 376           // If yes: just remove game
 377           if (!!ret.deletedByOpp) GameModel.remove(id);
 378         });
 379       }
 380     });
 381   },
 382
 383   remove: function(id_s) {
 384     const suffix =
 385       Array.isArray(id_s)
 386         ? " IN (" + id_s.join(",") + ")"
 387         : " = " + id_s;
 388     db.parallelize(function() {
 389       let query =
 390         "DELETE FROM Games " +
 391         "WHERE id " + suffix;
 392       db.run(query);
 393       query =
 394         "DELETE FROM Moves " +
 395         "WHERE gid " + suffix;
 396       db.run(query);
 397       query =
 398         "DELETE FROM Chats " +
 399         "WHERE gid " + suffix;
 400       db.run(query);
 401     });
 402   },
 403
 404   cleanGamesDb: function() {
 405     const tsNow = Date.now();
 406     // 86400000 = 24 hours in milliseconds
 407     const day = 86400000;
 408     db.serialize(function() {
 409       let query =
 410         "SELECT id, created " +
 411         "FROM Games";
 412       db.all(query, (err, games) => {
 413         query =
 414           "SELECT gid, count(*) AS nbMoves, MAX(played) AS lastMaj " +
 415           "FROM Moves " +
 416           "GROUP BY gid";
 417         db.get(query, (err2, mstats) => {
 418           // Reorganize moves data to avoid too many array lookups:
 419           let movesGroups = {};
 420           mstats.forEach(ms => {
 421             movesGroups[ms.gid] = {
 422               nbMoves: ms.nbMoves,
 423               lastMaj: ms.lastMaj
 424             };
 425           });
 426           // Remove games still not really started,
 427           // with no action in the last 3 months:
 428           let toRemove = [];
 429           games.forEach(g => {
 430             if (
 431               (
 432                 !movesGroups[g.id] &&
 433                 tsNow - g.created > 91*day
 434               )
 435               ||
 436               (
 437                 movesGroups[g.id].nbMoves == 1 &&
 438                 tsNow - movesGroups[g.id].lastMaj > 91*day
 439               )
 440             ) {
 441               toRemove.push(g.id);
 442             }
 443           });
 444           if (toRemove.length > 0) GameModel.remove(toRemove);
 445         });
 446       });
 447     });
 448   }
 449 }
 450
 451 module.exports = GameModel;