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