X-Git-Url: https://git.auder.net/?p=vchess.git;a=blobdiff_plain;f=server%2Fmodels%2FGame.js;h=8cc526fe3cc69ab14d940a0df48219e2e867c1e7;hp=32b2a6624b8fdc9127d9e88d61ead7096787ed49;hb=eb2d61de8d569470fa329a484efe9bab420b2b82;hpb=99f12ac0a1dc9633db08bf2804d7920d060fc214 diff --git a/server/models/Game.js b/server/models/Game.js index 32b2a662..8cc526fe 100644 --- a/server/models/Game.js +++ b/server/models/Game.js @@ -1,4 +1,4 @@ -var db = require("../utils/database"); +const db = require("../utils/database"); const UserModel = require("./User"); /* @@ -7,16 +7,19 @@ const UserModel = require("./User"); * vid: integer (variant id) * fenStart: varchar (initial position) * fen: varchar (current position) - * timeControl: string + * white: integer + * black: integer + * cadence: string * score: varchar (result) * scoreMsg: varchar ("Time", "Mutual agreement"...) * created: datetime * drawOffer: char ('w','b' or '' for none) - * - * Structure table Players: - * gid: ref game id - * uid: ref user id - * color: character + * rematchOffer: char (similar to drawOffer) + * options: varchar + * deletedByWhite: boolean + * deletedByBlack: boolean + * chatReadWhite: datetime + * chatReadBlack: datetime * * Structure table Moves: * gid: ref game id @@ -31,96 +34,93 @@ const UserModel = require("./User"); * added: datetime */ -const GameModel = -{ +const GameModel = { + checkGameInfo: function(g) { - if (!g.vid.toString().match(/^[0-9]+$/)) - return "Wrong variant ID"; - if (!g.vname.match(/^[a-zA-Z0-9]+$/)) - return "Wrong variant name"; - if (!g.timeControl.match(/^[0-9dhms +]+$/)) - return "Wrong characters in time control"; - if (!g.fen.match(/^[a-zA-Z0-9, /-]*$/)) - return "Bad FEN string"; - if (g.players.length != 2) - return "Need exactly 2 players"; - if (g.players.some(p => !p.id.toString().match(/^[0-9]+$/))) - return "Wrong characters in player ID"; - return ""; + return ( + g.vid.toString().match(/^[0-9]+$/) && + g.cadence.match(/^[0-9dhms +]+$/) && + g.fen.match(/^[a-zA-Z0-9, /-]*$/) && + g.players.length == 2 && + g.players.every(p => p.id.toString().match(/^[0-9]+$/)) + ); }, - create: function(vid, fen, timeControl, players, cb) - { + incrementCounter: function(vid, cb) { db.serialize(function() { let query = - "INSERT INTO Games" - + " (vid, fenStart, fen, score, timeControl, created, drawOffer)" - + " VALUES (" + vid + ",'" + fen + "','" + fen + "','*','" - + timeControl + "'," + Date.now() + ",'')"; - db.run(query, function(err) { - if (!!err) - return cb(err); - players.forEach((p,idx) => { - const color = (idx==0 ? "w" : "b"); - query = - "INSERT INTO Players VALUES " + - "(" + this.lastID + "," + p.id + ",'" + color + "')"; - db.run(query); - }); - cb(null, {gid: this.lastID}); + "UPDATE GameStat " + + "SET total = total + 1 " + + "WHERE vid = " + vid; + db.run(query, cb); + }); + }, + + create: function(vid, fen, options, cadence, players, cb) { + db.serialize(function() { + let query = + "INSERT INTO Games " + + "(" + + "vid, fenStart, fen, options, " + + "white, black, " + + "cadence, created" + + ") " + + "VALUES " + + "(" + + vid + ",'" + fen + "','" + fen + "',?," + + players[0].id + "," + players[1].id + "," + + "'" + cadence + "'," + Date.now() + + ")"; + db.run(query, options, function(err) { + cb(err, { id: this.lastID }); }); }); }, - // TODO: queries here could be async, and wait for all to complete - getOne: function(id, cb) - { + // TODO: some queries here could be async + getOne: function(id, cb) { + // NOTE: ignoring errors (shouldn't happen at this stage) db.serialize(function() { - // TODO: optimize queries? let query = - // NOTE: g.scoreMsg can be NULL - // (in this case score = "*" and no reason to look at it) - "SELECT g.id, g.vid, g.fen, g.fenStart, g.timeControl, g.score, " + - "g.scoreMsg, g.drawOffer, v.name AS vname " + - "FROM Games g " + - "JOIN Variants v " + - " ON g.vid = v.id " + - "WHERE g.id = " + id; - db.get(query, (err,gameInfo) => { - if (!!err) - return cb(err); + "SELECT " + + "id, vid, fen, fenStart, cadence, created, " + + "white, black, options, score, scoreMsg, " + + "chatReadWhite, chatReadBlack, drawOffer, rematchOffer " + + "FROM Games " + + "WHERE id = " + id; + db.get(query, (err, gameInfo) => { + if (!gameInfo) { + cb(err || { errmsg: "Game not found" }, undefined); + return; + } query = - "SELECT p.uid, p.color, u.name " + - "FROM Players p " + - "JOIN Users u " + - " ON p.uid = u.id " + - "WHERE p.gid = " + id; - db.all(query, (err2,players) => { - if (!!err2) - return cb(err2); + "SELECT id, name " + + "FROM Users " + + "WHERE id IN (" + gameInfo.white + "," + gameInfo.black + ")"; + db.all(query, (err2, players) => { + if (players[0].id == gameInfo.black) players = players.reverse(); + // The original players' IDs info isn't required anymore + delete gameInfo["white"]; + delete gameInfo["black"]; query = "SELECT squares, played, idx " + "FROM Moves " + "WHERE gid = " + id; - db.all(query, (err3,moves) => { - if (!!err3) - return cb(err3); + db.all(query, (err3, moves) => { query = "SELECT msg, name, added " + "FROM Chats " + "WHERE gid = " + id; - db.all(query, (err4,chats) => { - if (!!err4) - return cb(err4); - const game = Object.assign({}, - gameInfo, + db.all(query, (err4, chats) => { + const game = Object.assign( { players: players, moves: moves, - chats: chats, - } + chats: chats + }, + gameInfo ); - return cb(null, game); + cb(null, game); }); }); }); @@ -128,175 +128,405 @@ const GameModel = }); }, - getByUser: function(uid, excluded, cb) - { + // For display on Hall: no need for moves or chats + getObserved: function(uid, cursor, cb) { db.serialize(function() { - const query = - "SELECT DISTINCT gid " + - "FROM Players " + - "WHERE uid " + (excluded ? "<>" : "=") + " " + uid; - db.all(query, (err,gameIds) => { - if (!!err) - return cb(err); - if (gameIds.length == 0) - return cb(null, []); - let gameArray = []; - for (let i=0; i { - if (!!err2) - return cb(err2); - gameArray.push(game); - // Call callback function only when gameArray is complete: - if (i == gameIds.length - 1) - return cb(null, gameArray); + let query = + "SELECT id, vid, cadence, created, score, white, black " + + "FROM Games " + + "WHERE created < " + cursor + " "; + if (uid > 0) { + query += + " AND white <> " + uid + " " + + " AND black <> " + uid + " "; + } + query += + "ORDER BY created DESC " + + "LIMIT 20"; //TODO: 20 hard-coded... + db.all(query, (err, games) => { + // Query players names + let pids = {}; + games.forEach(g => { + if (!pids[g.white]) pids[g.white] = true; + if (!pids[g.black]) pids[g.black] = true; + }); + UserModel.getByIds(Object.keys(pids), (err2, users) => { + let names = {}; + users.forEach(u => { names[u.id] = u.name; }); + cb( + err, + games.map( + g => { + return { + id: g.id, + vid: g.vid, + cadence: g.cadence, + created: g.created, + score: g.score, + players: [ + { id: g.white, name: names[g.white] }, + { id: g.black, name: names[g.black] } + ] + }; + } + ) + ); + }); + }); + }); + }, + + // For display on MyGames: registered user only + getRunning: function(uid, cb) { + db.serialize(function() { + let query = + "SELECT id, vid, cadence, created, white, black " + + "FROM Games " + + "WHERE score = '*' AND (white = " + uid + " OR black = " + uid + ")"; + db.all(query, (err, games) => { + // Get movesCount (could be done in // with next query) + query = + "SELECT gid, COUNT(*) AS nbMoves " + + "FROM Moves " + + "WHERE gid IN " + "(" + games.map(g => g.id).join(",") + ") " + + "GROUP BY gid"; + db.all(query, (err, mstats) => { + let movesCounts = {}; + mstats.forEach(ms => { movesCounts[ms.gid] = ms.nbMoves; }); + // Query player names + let pids = {}; + games.forEach(g => { + if (!pids[g.white]) pids[g.white] = true; + if (!pids[g.black]) pids[g.black] = true; }); - } + UserModel.getByIds(Object.keys(pids), (err2, users) => { + let names = {}; + users.forEach(u => { names[u.id] = u.name; }); + cb( + null, + games.map( + g => { + return { + id: g.id, + vid: g.vid, + cadence: g.cadence, + created: g.created, + score: g.score, + movesCount: movesCounts[g.id] || 0, + players: [ + { id: g.white, name: names[g.white] }, + { id: g.black, name: names[g.black] } + ] + }; + } + ) + ); + }); + }); }); }); }, - getPlayers: function(id, cb) - { + // These games could be deleted on some side. movesCount not required + getCompleted: function(uid, cursor, cb) { + db.serialize(function() { + let query = + "SELECT id, vid, cadence, created, score, scoreMsg, " + + "white, black, deletedByWhite, deletedByBlack " + + "FROM Games " + + "WHERE " + + " score <> '*' AND" + + " created < " + cursor + " AND" + + " (" + + " (" + + " white = " + uid + " AND" + + " (deletedByWhite IS NULL OR NOT deletedByWhite)" + + " )" + + " OR " + + " (" + + " black = " + uid + " AND" + + " (deletedByBlack IS NULL OR NOT deletedByBlack)" + + " )" + + " ) "; + query += + "ORDER BY created DESC " + + "LIMIT 20"; + db.all(query, (err, games) => { + // Query player names + let pids = {}; + games.forEach(g => { + if (!pids[g.white]) pids[g.white] = true; + if (!pids[g.black]) pids[g.black] = true; + }); + UserModel.getByIds(Object.keys(pids), (err2, users) => { + let names = {}; + users.forEach(u => { names[u.id] = u.name; }); + cb( + null, + games.map( + g => { + return { + id: g.id, + vid: g.vid, + cadence: g.cadence, + created: g.created, + score: g.score, + scoreMsg: g.scoreMsg, + players: [ + { id: g.white, name: names[g.white] }, + { id: g.black, name: names[g.black] } + ], + deletedByWhite: g.deletedByWhite, + deletedByBlack: g.deletedByBlack + }; + } + ) + ); + }); + }); + }); + }, + + getPlayers: function(id, cb) { db.serialize(function() { const query = - "SELECT id " + - "FROM Players " + - "WHERE gid = " + id; - db.all(query, (err,players) => { + "SELECT white, black " + + "FROM Games " + + "WHERE id = " + id; + db.get(query, (err, players) => { return cb(err, players); }); }); }, - checkGameUpdate: function(obj) - { + checkGameUpdate: function(obj) { // Check all that is possible (required) in obj: - if (!!obj.move) - { - if (!obj.move.played.toString().match(/^[0-9]+$/)) - return "Wrong move played time"; - if (!obj.move.idx.toString().match(/^[0-9]+$/)) - return "Wrong move index"; - } - if (!!obj.drawOffer && !obj.drawOffer.match(/^[wbtn]$/)) - return "Wrong draw offer format"; - if (!!obj.fen && !obj.fen.match(/^[a-zA-Z0-9, /-]*$/)) - return "Wrong FEN string"; - if (!!obj.score && !obj.score.match(/^[012?*\/-]+$/)) - return "Wrong characters in score"; - if (!!obj.scoreMsg && !obj.scoreMsg.match(/^[a-zA-Z ]+$/)) - return "Wrong characters in score message"; - if (!!obj.chat) - return UserModel.checkNameEmail({name: obj.chat.name}); - return ""; + return ( + ( + !obj.move || !!(obj.move.idx.toString().match(/^[0-9]+$/)) + ) && ( + !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/)) + ) && ( + !obj.rematchOffer || !!(obj.rematchOffer.match(/^[wbn]$/)) + ) && ( + !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9,.:{}\[\]" /-]*$/)) + ) && ( + !obj.score || !!(obj.score.match(/^[012?*\/-]+$/)) + ) && ( + !obj.chatRead || ['w','b'].includes(obj.chatRead) + ) && ( + !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/)) + ) && ( + !obj.chat || UserModel.checkNameEmail({name: obj.chat.name}) + ) + ); }, - // obj can have fields move, chat, fen, drawOffer and/or score - update: function(id, obj) - { + // obj can have fields move, chat, fen, drawOffer and/or score + message + update: function(id, obj, cb) { db.parallelize(function() { - let query = + let updateQuery = "UPDATE Games " + "SET "; let modifs = ""; - if (!!obj.message) - modifs += "message = message || ' ' || '" + obj.message + "',"; // NOTE: if drawOffer is set, we should check that it's player's turn // A bit overcomplicated. Let's trust the client on that for now... - if (!!obj.drawOffer) - { - if (obj.drawOffer == "n") //Special "None" update + if (!!obj.drawOffer) { + if (obj.drawOffer == "n") + // Special "None" update obj.drawOffer = ""; modifs += "drawOffer = '" + obj.drawOffer + "',"; } - if (!!obj.fen) - modifs += "fen = '" + obj.fen + "',"; - if (!!obj.score) - modifs += "score = '" + obj.score + "',"; - if (!!obj.scoreMsg) - modifs += "scoreMsg = '" + obj.scoreMsg + "',"; - modifs = modifs.slice(0,-1); //remove last comma - if (modifs.length > 0) - { - query += modifs + " WHERE id = " + id; - db.run(query); + if (!!obj.rematchOffer) { + if (obj.rematchOffer == "n") + // Special "None" update + obj.rematchOffer = ""; + modifs += "rematchOffer = '" + obj.rematchOffer + "',"; } - if (!!obj.move) - { - const m = obj.move; - query = - "INSERT INTO Moves (gid, squares, played, idx) VALUES " + - "(" + id + ",?," + m.played + "," + m.idx + ")"; - db.run(query, JSON.stringify(m.squares)); + if (!!obj.fen) modifs += "fen = '" + obj.fen + "',"; + if (!!obj.deletedBy) { + const myColor = obj.deletedBy == 'w' ? "White" : "Black"; + modifs += "deletedBy" + myColor + " = true,"; } - if (!!obj.chat) - { - query = + if (!!obj.chatRead) { + const myColor = obj.chatRead == 'w' ? "White" : "Black"; + modifs += "chatRead" + myColor + " = " + Date.now() + ","; + } + if (!!obj.score) { + modifs += "score = '" + obj.score + "'," + + "scoreMsg = '" + obj.scoreMsg + "',"; + } + const finishAndSendQuery = () => { + modifs = modifs.slice(0, -1); //remove last comma + if (modifs.length > 0) { + updateQuery += modifs + " WHERE id = " + id; + db.run(updateQuery); + } + cb(null); + }; + if (!!obj.move || (!!obj.score && obj.scoreMsg == "Time")) { + // Security: only update moves if index is right, + // and score with scoreMsg "Time" if really lost on time. + let query = + "SELECT MAX(idx) AS maxIdx, MAX(played) AS lastPlayed " + + "FROM Moves " + + "WHERE gid = " + id; + db.get(query, (err, ret) => { + if (!!obj.move ) { + if (!ret.maxIdx || ret.maxIdx + 1 == obj.move.idx) { + query = + "INSERT INTO Moves (gid, squares, played, idx) VALUES " + + "(" + id + ",?," + Date.now() + "," + obj.move.idx + ")"; + db.run(query, JSON.stringify(obj.move.squares)); + finishAndSendQuery(); + } + else cb({ errmsg: "Wrong move index" }); + } + else { + if (ret.maxIdx < 2) cb({ errmsg: "Time not over" }); + else { + // We also need the game cadence + query = + "SELECT cadence " + + "FROM Games " + + "WHERE id = " + id; + db.get(query, (err2, ret2) => { + const daysTc = parseInt(ret2.cadence.match(/^[0-9]+/)[0]); + if (Date.now() - ret.lastPlayed > daysTc * 24 * 3600 * 1000) + finishAndSendQuery(); + else cb({ errmsg: "Time not over" }); + }); + } + } + }); + } + else finishAndSendQuery(); + // NOTE: chat and delchat are mutually exclusive + if (!!obj.chat) { + const query = "INSERT INTO Chats (gid, msg, name, added) VALUES (" + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")"; db.run(query, obj.chat.msg); } + else if (obj.delchat) { + const query = + "DELETE " + + "FROM Chats " + + "WHERE gid = " + id; + db.run(query); + } + if (!!obj.deletedBy) { + // Did my opponent delete it too? + let selection = + "deletedBy" + + (obj.deletedBy == 'w' ? "Black" : "White") + + " AS deletedByOpp"; + const query = + "SELECT " + selection + " " + + "FROM Games " + + "WHERE id = " + id; + db.get(query, (err,ret) => { + // If yes: just remove game + if (!!ret.deletedByOpp) GameModel.remove(id); + }); + } }); }, - remove: function(id) - { + remove: function(id_s) { + const suffix = + Array.isArray(id_s) + ? " IN (" + id_s.join(",") + ")" + : " = " + id_s; db.parallelize(function() { let query = "DELETE FROM Games " + - "WHERE id = " + id; - db.run(query); - query = - "DELETE FROM Players " + - "WHERE gid = " + id; + "WHERE id " + suffix; db.run(query); query = "DELETE FROM Moves " + - "WHERE gid = " + id; + "WHERE gid " + suffix; db.run(query); query = "DELETE FROM Chats " + - "WHERE gid = " + id; + "WHERE gid " + suffix; db.run(query); }); }, - cleanGamesDb: function() - { + cleanGamesDb: function() { const tsNow = Date.now(); // 86400000 = 24 hours in milliseconds const day = 86400000; db.serialize(function() { let query = - "SELECT id,score,created " + - "FROM Games "; - db.all(query, (err,games) => { - games.forEach(g => { - query = - "SELECT max(played) AS lastMaj " + - "FROM Moves " + - "WHERE gid = " + g.id; - db.get(query, (err2,updated) => { - if (!updated.lastMaj) - { - if (tsNow - g.created > 7*day) - return GameModel.remove(g.id); + "SELECT id, created, cadence, score " + + "FROM Games"; + db.all(query, (err, games) => { + query = + "SELECT gid, count(*) AS nbMoves, MAX(played) AS lastMaj " + + "FROM Moves " + + "GROUP BY gid"; + db.all(query, (err2, mstats) => { + // Reorganize moves data to avoid too many array lookups: + let movesGroups = {}; + mstats.forEach(ms => { + movesGroups[ms.gid] = { + nbMoves: ms.nbMoves, + lastMaj: ms.lastMaj + }; + }); + // Remove games still not really started, + // with no action in the last 2 weeks, or result != '*': + let toRemove = []; + let lostOnTime = [ [], [] ]; + games.forEach(g => { + if ( + ( + !movesGroups[g.id] && + (g.score != '*' || tsNow - g.created > 14*day) + ) + || + ( + !!movesGroups[g.id] && + movesGroups[g.id].nbMoves == 1 && + (g.score != '*' || tsNow - movesGroups[g.id].lastMaj > 14*day) + ) + ) { + toRemove.push(g.id); } - else //at least one move - { - const lastMaj = updated.lastMaj; - if (g.score != "*" && tsNow - lastMaj > 7*day || - g.score == "*" && tsNow - lastMaj > 91*day) - { - GameModel.remove(g.id); - } + // Set score if lost on time and >= 2 moves: + else if ( + g.score == '*' && + !!movesGroups[g.id] && + movesGroups[g.id].nbMoves >= 2 && + tsNow - movesGroups[g.id].lastMaj > + // cadence in days * nb seconds per day: + parseInt(g.cadence.slice(0, -1), 10) * day + ) { + lostOnTime[movesGroups[g.id].nbMoves % 2].push(g.id); } }); + if (toRemove.length > 0) GameModel.remove(toRemove); + if (lostOnTime.some(l => l.length > 0)) { + db.parallelize(function() { + for (let i of [0, 1]) { + if (lostOnTime[i].length > 0) { + const score = (i == 0 ? "0-1" : "1-0"); + const query = + "UPDATE Games " + + "SET score = '" + score + "', scoreMsg = 'Time' " + + "WHERE id IN (" + lostOnTime[i].join(',') + ")"; + db.run(query); + } + } + }); + } }); }); }); - }, -} + } + +}; module.exports = GameModel;