X-Git-Url: https://git.auder.net/?a=blobdiff_plain;f=server%2Fmodels%2FGame.js;h=caa15c39579c992c1e474cfa294147726a6edd24;hb=584f81b93154313a3856112400b7df98e0eb2632;hp=77bbd27a602fe696d4978fb83a552fada88389c2;hpb=83494c7fbd83fafa28c5a434a1c83f56c17e3d04;p=vchess.git diff --git a/server/models/Game.js b/server/models/Game.js index 77bbd27a..caa15c39 100644 --- a/server/models/Game.js +++ b/server/models/Game.js @@ -1,4 +1,5 @@ -var db = require("../utils/database"); +const db = require("../utils/database"); +const UserModel = require("./User"); /* * Structure table Games: @@ -6,9 +7,11 @@ var db = require("../utils/database"); * vid: integer (variant id) * fenStart: varchar (initial position) * fen: varchar (current position) - * timeControl: string + * 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 @@ -18,113 +21,180 @@ var db = require("../utils/database"); * Structure table Moves: * gid: ref game id * squares: varchar (description) - * message: text * played: datetime * idx: integer + * + * Structure table Chats: + * gid: game id (int) + * msg: varchar + * name: varchar + * added: datetime */ const GameModel = { - create: function(vid, fen, timeControl, players, cb) - { - db.serialize(function() { - let query = - "INSERT INTO Games (vid, fenStart, fen, score, timeControl, created)" - + " VALUES (" + vid + ",'" + fen + "','" + fen + "','*','" - + timeControl + "'," + Date.now() + ")"; + checkGameInfo: function(g) { + 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.uid.toString().match(/^[0-9]+$/)) + ); + }, + + create: function(vid, fen, cadence, players, cb) + { + db.serialize(function() { + let query = + "INSERT INTO Games " + + "(vid, fenStart, fen, cadence, created) " + + "VALUES " + + "(" + vid + ",'" + fen + "','" + fen + "','" + cadence + "'," + Date.now() + ")"; db.run(query, function(err) { - if (!!err) - return cb(err); - players.forEach((p,idx) => { - const color = (idx==0 ? "w" : "b"); + if (err) + cb(err) + else + { + players.forEach((p,idx) => { + const color = (idx==0 ? "w" : "b"); + query = + "INSERT INTO Players VALUES " + + "(" + this.lastID + "," + p.uid + ",'" + color + "')"; + db.run(query); + }); + cb(null, {gid: this.lastID}); + } + }); + }); + }, + + // TODO: some queries here could be async + getOne: function(id, cb) + { + // NOTE: ignoring errors (shouldn't happen at this stage) + db.serialize(function() { + let query = + "SELECT g.id, g.vid, g.fen, g.fenStart, g.cadence, g.created, g.score, " + + "g.scoreMsg, g.drawOffer, g.rematchOffer, v.name AS vname " + + "FROM Games g " + + "JOIN Variants v " + + " ON g.vid = v.id " + + "WHERE g.id = " + id; + db.get(query, (err, gameInfo) => { + 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) => { query = - "INSERT INTO Players VALUES " + - "(" + this.lastID + "," + p.id + ",'" + color + "')"; - db.run(query); + "SELECT squares, played, idx " + + "FROM Moves " + + "WHERE gid = " + id; + db.all(query, (err3, moves) => { + query = + "SELECT msg, name, added " + + "FROM Chats " + + "WHERE gid = " + id; + db.all(query, (err4, chats) => { + const game = Object.assign( + {}, + gameInfo, + { + players: players, + moves: moves, + chats: chats, + } + ); + cb(null, game); + }); + }); }); - cb(null, {gid: this.lastID}); - }); - }); - }, + }); + }); + }, - // TODO: queries here could be async, and wait for all to complete - getOne: function(id, cb) - { - db.serialize(function() { - // TODO: optimize queries? - let query = - "SELECT g.id, g.vid, g.fen, g.fenStart, g.timeControl, g.score, " + - "v.name AS vname " + - "FROM Games g " + + // For display on MyGames or Hall: no need for moves or chats + getByUser: function(uid, excluded, cb) + { + // Some fields are not required when showing a games list: + const getOneLight = (id, cb2) => { + let query = + "SELECT g.id, g.vid, g.fen, g.cadence, g.created, g.score, " + + "g.scoreMsg, g.deletedByWhite, g.deletedByBlack, 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); - query = - "SELECT p.uid, p.color, u.name " + - "FROM Players p " + + "WHERE g.id = " + id; + db.get(query, (err, gameInfo) => { + 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); - query = - "SELECT squares, message, played, idx " + - "FROM Moves " + - "WHERE gid = " + id; - db.all(query, (err3,moves) => { - if (!!err3) - return cb(err3); - const game = Object.assign({}, + "WHERE p.gid = " + id; + db.all(query, (err2, players) => { + query = + "SELECT COUNT(*) AS nbMoves " + + "FROM Moves " + + "WHERE gid = " + id; + db.get(query, (err,ret) => { + const game = Object.assign( + {}, gameInfo, - { - players: players, - moves: moves + { + players: players, + movesCount: ret.nbMoves } ); - return cb(null, game); - }); - }); - }); - }); - }, - - getByUser: function(uid, excluded, cb) - { - db.serialize(function() { - // Next query is fine because a player appear at most once in a game - const query = - "SELECT gid " + - "FROM Players " + - "WHERE uid " + (excluded ? "<>" : "=") + " " + uid; - db.all(query, (err,gameIds) => { - if (!!err) - return cb(err); - gameIds = gameIds || []; //might be empty - 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); - }); - } - }); - }); - }, + cb2(game); + }); + }); + }); + }; + db.serialize(function() { + let query = ""; + if (uid == 0) { + // Special case anonymous user: show all games + query = + "SELECT id AS gid " + + "FROM Games"; + } + else { + // Registered user: + query = + "SELECT gid " + + "FROM Players " + + "GROUP BY gid " + + "HAVING COUNT(uid = " + uid + " OR NULL) " + + (excluded ? " = 0" : " > 0"); + } + db.all(query, (err,gameIds) => { + if (err || gameIds.length == 0) cb(err, []); + else { + let gameArray = []; + let gCounter = 0; + for (let i=0; i { + gameArray.push(game); + gCounter++; //TODO: let's hope this is atomic?! + // Call callback function only when gameArray is complete: + if (gCounter == gameIds.length) + cb(null, gameArray); + }); + } + } + }); + }); + }, getPlayers: function(id, cb) { db.serialize(function() { const query = - "SELECT id " + + "SELECT uid " + "FROM Players " + "WHERE gid = " + id; db.all(query, (err,players) => { @@ -133,49 +203,144 @@ const GameModel = }); }, - // obj can have fields move, fen and/or score - update: function(id, obj) + checkGameUpdate: function(obj) + { + // Check all that is possible (required) in obj: + return ( + ( + !obj.move || ( + !!(obj.move.played.toString().match(/^[0-9]+$/)) && + !!(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.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 + message + update: function(id, obj, cb) { - db.parallelize(function() { + db.parallelize(function() { let query = "UPDATE Games " + "SET "; + let modifs = ""; + // 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 + obj.drawOffer = ""; + modifs += "drawOffer = '" + obj.drawOffer + "',"; + } + if (!!obj.rematchOffer) + { + if (obj.rematchOffer == "n") //special "None" update + obj.rematchOffer = ""; + modifs += "rematchOffer = '" + obj.rematchOffer + "',"; + } if (!!obj.fen) - query += "fen = '" + obj.fen + "',"; + modifs += "fen = '" + obj.fen + "',"; if (!!obj.score) - query += "score = '" + obj.score + "',"; - query = query.slice(0,-1); //remove last comma - query += " WHERE id = " + id; - db.run(query); + modifs += "score = '" + obj.score + "',"; + if (!!obj.scoreMsg) + modifs += "scoreMsg = '" + obj.scoreMsg + "',"; + if (!!obj.deletedBy) { + const myColor = obj.deletedBy == 'w' ? "White" : "Black"; + modifs += "deletedBy" + myColor + " = true,"; + } + modifs = modifs.slice(0,-1); //remove last comma + if (modifs.length > 0) + { + query += modifs + " WHERE id = " + id; + db.run(query); + } + // NOTE: move, chat and delchat are mutually exclusive if (!!obj.move) { - const m = obj.move; + // Security: only update moves if index is right query = - "INSERT INTO Moves (gid, squares, message, played, idx) VALUES " + - "(" + id + ",'" + JSON.stringify(m.squares) + "','" + m.message + - "'," + m.played + "," + m.idx + ")"; + "SELECT MAX(idx) AS maxIdx " + + "FROM Moves " + + "WHERE gid = " + id; + db.get(query, (err,ret) => { + const m = obj.move; + if (!ret.maxIdx || ret.maxIdx + 1 == m.idx) { + query = + "INSERT INTO Moves (gid, squares, played, idx) VALUES " + + "(" + id + ",?," + m.played + "," + m.idx + ")"; + db.run(query, JSON.stringify(m.squares)); + cb(null); + } + else cb({errmsg:"Wrong move index"}); + }); + } + else cb(null); + if (!!obj.chat) + { + 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) + { + 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"; + 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) - { - db.parallelize(function() { - let query = - "DELETE FROM Games " + - "WHERE id = " + id; - db.run(query); - query = - "DELETE FROM Players " + - "WHERE gid = " + id; - db.run(query); - query = - "DELETE FROM Moves " + - "WHERE gid = " + id; - db.run(query); - }); - }, + remove: function(id) + { + db.parallelize(function() { + let query = + "DELETE FROM Games " + + "WHERE id = " + id; + db.run(query); + query = + "DELETE FROM Players " + + "WHERE gid = " + id; + db.run(query); + query = + "DELETE FROM Moves " + + "WHERE gid = " + id; + db.run(query); + query = + "DELETE FROM Chats " + + "WHERE gid = " + id; + db.run(query); + }); + }, cleanGamesDb: function() { @@ -184,20 +349,19 @@ const GameModel = const day = 86400000; db.serialize(function() { let query = - "SELECT id,score " + + "SELECT id, created " + "FROM Games "; db.all(query, (err,games) => { games.forEach(g => { query = - "SELECT max(played) AS lastMaj " + + "SELECT count(*) as nbMoves, max(played) AS lastMaj " + "FROM Moves " + "WHERE gid = " + g.id; - db.get(query, (err2,updated) { - if (!updated && tsNow - g.created > 7*day) - return GameModel.remove(g.id); - const lastMaj = updated.lastMaj; - if (g.score != "*" && tsNow - lastMaj > 7*day || - g.score == "*" && tsNow - lastMaj > 91*day) + db.get(query, (err2,mstats) => { + // Remove games still not really started, + // with no action in the last 3 months: + if ((mstats.nbMoves == 0 && tsNow - g.created > 91*day) || + (mstats.nbMoves == 1 && tsNow - mstats.lastMaj > 91*day)) { GameModel.remove(g.id); }