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