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