* created: datetime
* drawOffer: char ('w','b' or '' for none)
* rematchOffer: char (similar to drawOffer)
- * randomness: integer
+ * options: varchar
* deletedByWhite: boolean
* deletedByBlack: boolean
+ * chatReadWhite: datetime
+ * chatReadBlack: datetime
*
* Structure table Moves:
* gid: ref game id
* added: datetime
*/
-const GameModel =
-{
+const GameModel = {
+
checkGameInfo: function(g) {
return (
g.vid.toString().match(/^[0-9]+$/) &&
g.cadence.match(/^[0-9dhms +]+$/) &&
- g.randomness.match(/^[0-2]$/) &&
g.fen.match(/^[a-zA-Z0-9, /-]*$/) &&
g.players.length == 2 &&
- g.players.every(p => p.toString().match(/^[0-9]+$/))
+ g.players.every(p => p.id.toString().match(/^[0-9]+$/))
);
},
- create: function(vid, fen, randomness, cadence, players, cb) {
+ incrementCounter: function(vid, cb) {
+ db.serialize(function() {
+ let query =
+ "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, randomness, " +
+ "vid, fenStart, fen, options, " +
"white, black, " +
"cadence, created" +
") " +
"VALUES " +
"(" +
- vid + ",'" + fen + "','" + fen + "'," + randomness + "," +
- "'" + players[0] + "','" + players[1] + "," +
+ vid + ",'" + fen + "','" + fen + "',?," +
+ players[0].id + "," + players[1].id + "," +
"'" + cadence + "'," + Date.now() +
")";
- db.run(query, function(err) {
- cb(err, { id: this.lastId });
+ db.run(query, options, function(err) {
+ cb(err, { id: this.lastID });
});
});
},
db.serialize(function() {
let query =
"SELECT " +
- "g.id, g.vid, g.fen, g.fenStart, g.cadence, g.created, " +
- "g.white, g.black, 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;
+ "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 squares, played, idx " +
- "FROM Moves " +
- "WHERE gid = " + id;
- db.all(query, (err3, moves) => {
+ "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 msg, name, added " +
- "FROM Chats " +
+ "SELECT squares, played, idx " +
+ "FROM Moves " +
"WHERE gid = " + id;
- db.all(query, (err4, chats) => {
- const game = Object.assign(
- {},
- gameInfo,
- {
- moves: moves,
- chats: chats
- }
- );
- cb(null, game);
+ db.all(query, (err3, moves) => {
+ query =
+ "SELECT msg, name, added " +
+ "FROM Chats " +
+ "WHERE gid = " + id;
+ db.all(query, (err4, chats) => {
+ const game = Object.assign(
+ {
+ players: players,
+ moves: moves,
+ chats: chats
+ },
+ gameInfo
+ );
+ cb(null, game);
+ });
});
});
});
getObserved: function(uid, cursor, cb) {
db.serialize(function() {
let query =
- "SELECT g.id, g.vid, g.cadence, g.created, " +
- " g.score, g.white, g.black " +
- "FROM Games g ";
- if (uid > 0) query +=
- "WHERE " +
- " created < " + cursor + " AND " +
- " white <> " + uid + " AND " +
- " black <> " + uid + " ";
+ "SELECT id, vid, cadence, options, 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...
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,
- vname: g.vname,
+ options: g.options,
created: g.created,
score: g.score,
- white: names[g.white],
- black: names[g.black]
+ players: [
+ { id: g.white, name: names[g.white] },
+ { id: g.black, name: names[g.black] }
+ ]
};
}
)
getRunning: function(uid, cb) {
db.serialize(function() {
let query =
- "SELECT g.id, g.cadence, g.created, g.score, " +
- "g.white, g.black, v.name AS vname " +
- "FROM Games g " +
- "JOIN Variants v " +
- " ON g.vid = v.id " +
- "WHERE white = " + uid + " OR black = " + uid;
+ "SELECT id, vid, cadence, options, 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 =
if (!pids[g.white]) pids[g.white] = true;
if (!pids[g.black]) pids[g.black] = true;
});
- UserModel.getByIds(pids, (err2, users) => {
+ 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,
- vname: g.vname,
+ options: g.options,
created: g.created,
score: g.score,
- movesCount: movesCounts[g.id],
- white: names[g.white],
- black: names[g.black]
+ movesCount: movesCounts[g.id] || 0,
+ players: [
+ { id: g.white, name: names[g.white] },
+ { id: g.black, name: names[g.black] }
+ ]
};
}
)
getCompleted: function(uid, cursor, cb) {
db.serialize(function() {
let query =
- "SELECT g.id, g.cadence, g.created, g.score, g.scoreMsg, " +
- "g.white, g.black, g.deletedByWhite, g.deletedByBlack, " +
- "v.name AS vname " +
- "FROM Games g " +
- "JOIN Variants v " +
- " ON g.vid = v.id " +
+ "SELECT id, vid, cadence, options, created, score, scoreMsg, " +
+ "white, black, deletedByWhite, deletedByBlack " +
+ "FROM Games " +
"WHERE " +
- " created < " + cursor + " AND " +
+ " score <> '*' AND" +
+ " created < " + cursor + " AND" +
" (" +
- " (" + uid + " = white AND NOT deletedByWhite) OR " +
- " (" + uid + " = black AND NOT deletedByBlack)" +
+ " (" +
+ " white = " + uid + " AND" +
+ " (deletedByWhite IS NULL OR NOT deletedByWhite)" +
+ " )" +
+ " OR " +
+ " (" +
+ " black = " + uid + " AND" +
+ " (deletedByBlack IS NULL OR NOT deletedByBlack)" +
+ " )" +
" ) ";
query +=
"ORDER BY created DESC " +
if (!pids[g.white]) pids[g.white] = true;
if (!pids[g.black]) pids[g.black] = true;
});
- UserModel.getByIds(pids, (err2, users) => {
+ 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,
- vname: g.vname,
+ options: g.options,
created: g.created,
score: g.score,
scoreMsg: g.scoreMsg,
- white: names[g.white],
- black: names[g.black],
+ players: [
+ { id: g.white, name: names[g.white] },
+ { id: g.black, name: names[g.black] }
+ ],
deletedByWhite: g.deletedByWhite,
deletedByBlack: g.deletedByBlack
};
const query =
"SELECT white, black " +
"FROM Games " +
- "WHERE gid = " + id;
- db.all(query, (err, players) => {
+ "WHERE id = " + id;
+ db.get(query, (err, players) => {
return cb(err, players);
});
});
// 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.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.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 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 = "";
// 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.rematchOffer)
- {
- if (obj.rematchOffer == "n") //special "None" update
+ if (!!obj.rematchOffer) {
+ if (obj.rematchOffer == "n")
+ // Special "None" update
obj.rematchOffer = "";
modifs += "rematchOffer = '" + obj.rematchOffer + "',";
}
- if (!!obj.fen)
- modifs += "fen = '" + obj.fen + "',";
- if (!!obj.score)
- modifs += "score = '" + obj.score + "',";
- if (!!obj.scoreMsg)
- modifs += "scoreMsg = '" + obj.scoreMsg + "',";
+ if (!!obj.fen) modifs += "fen = '" + obj.fen + "',";
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);
+ if (!!obj.chatRead) {
+ const myColor = obj.chatRead == 'w' ? "White" : "Black";
+ modifs += "chatRead" + myColor + " = " + Date.now() + ",";
}
- // NOTE: move, chat and delchat are mutually exclusive
- if (!!obj.move)
- {
- // Security: only update moves if index is right
- query =
- "SELECT MAX(idx) AS maxIdx " +
+ 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) => {
- 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);
+ 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 cb({errmsg:"Wrong move index"});
});
}
- else cb(null);
- if (!!obj.chat)
- {
- query =
+ 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)
- {
- query =
+ else if (obj.delchat) {
+ const query =
"DELETE " +
"FROM Chats " +
"WHERE gid = " + id;
"deletedBy" +
(obj.deletedBy == 'w' ? "Black" : "White") +
" AS deletedByOpp";
- query =
+ const query =
"SELECT " + selection + " " +
"FROM Games " +
"WHERE id = " + id;
const day = 86400000;
db.serialize(function() {
let query =
- "SELECT id, created " +
+ "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.get(query, (err2, mstats) => {
+ db.all(query, (err2, mstats) => {
// Reorganize moves data to avoid too many array lookups:
let movesGroups = {};
mstats.forEach(ms => {
};
});
// Remove games still not really started,
- // with no action in the last 3 months:
+ // with no action in the last 2 weeks, or result != '*':
let toRemove = [];
+ let lostOnTime = [ [], [] ];
games.forEach(g => {
if (
(
!movesGroups[g.id] &&
- tsNow - g.created > 91*day
+ (g.score != '*' || tsNow - g.created > 14*day)
)
||
(
+ !!movesGroups[g.id] &&
movesGroups[g.id].nbMoves == 1 &&
- tsNow - movesGroups[g.id].lastMaj > 91*day
+ (g.score != '*' || tsNow - movesGroups[g.id].lastMaj > 14*day)
)
) {
toRemove.push(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;