Better 'more infos' section for Shogi and Xiangqi
[vchess.git] / server / models / Game.js
1 const db = require("../utils/database");
2 const UserModel = require("./User");
3
4 /*
5 * Structure table Games:
6 * id: game id (int)
7 * vid: integer (variant id)
8 * fenStart: varchar (initial position)
9 * fen: varchar (current position)
10 * white: integer
11 * black: integer
12 * cadence: string
13 * score: varchar (result)
14 * scoreMsg: varchar ("Time", "Mutual agreement"...)
15 * created: datetime
16 * drawOffer: char ('w','b' or '' for none)
17 * rematchOffer: char (similar to drawOffer)
18 * randomness: integer
19 * deletedByWhite: boolean
20 * deletedByBlack: boolean
21 * chatReadWhite: datetime
22 * chatReadBlack: datetime
23 *
24 * Structure table Moves:
25 * gid: ref game id
26 * squares: varchar (description)
27 * played: datetime
28 * idx: integer
29 *
30 * Structure table Chats:
31 * gid: game id (int)
32 * msg: varchar
33 * name: varchar
34 * added: datetime
35 */
36
37 const GameModel = {
38
39 checkGameInfo: function(g) {
40 return (
41 g.vid.toString().match(/^[0-9]+$/) &&
42 g.cadence.match(/^[0-9dhms +]+$/) &&
43 g.randomness.toString().match(/^[0-2]$/) &&
44 g.fen.match(/^[a-zA-Z0-9, /-]*$/) &&
45 g.players.length == 2 &&
46 g.players.every(p => p.id.toString().match(/^[0-9]+$/))
47 );
48 },
49
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
60 create: function(vid, fen, randomness, cadence, players, cb) {
61 db.serialize(function() {
62 let query =
63 "INSERT INTO Games " +
64 "(" +
65 "vid, fenStart, fen, randomness, " +
66 "white, black, " +
67 "cadence, created" +
68 ") " +
69 "VALUES " +
70 "(" +
71 vid + ",'" + fen + "','" + fen + "'," + randomness + "," +
72 players[0].id + "," + players[1].id + "," +
73 "'" + cadence + "'," + Date.now() +
74 ")";
75 db.run(query, function(err) {
76 cb(err, { id: this.lastID });
77 });
78 });
79 },
80
81 // TODO: some queries here could be async
82 getOne: function(id, cb) {
83 // NOTE: ignoring errors (shouldn't happen at this stage)
84 db.serialize(function() {
85 let query =
86 "SELECT " +
87 "g.id, g.fen, g.fenStart, g.cadence, g.created, " +
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 " +
92 "FROM Games g " +
93 "JOIN Variants v " +
94 " ON g.vid = v.id " +
95 "WHERE g.id = " + id;
96 db.get(query, (err, gameInfo) => {
97 if (!gameInfo) {
98 cb(err || { errmsg: "Game not found" }, undefined);
99 return;
100 }
101 query =
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"];
110 query =
111 "SELECT squares, played, idx " +
112 "FROM Moves " +
113 "WHERE gid = " + id;
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 });
131 });
132 });
133 });
134 });
135 },
136
137 // For display on Hall: no need for moves or chats
138 getObserved: function(uid, cursor, cb) {
139 db.serialize(function() {
140 let query =
141 "SELECT id, vid, cadence, created, score, white, black " +
142 "FROM Games " +
143 "WHERE created < " + cursor + " ";
144 if (uid > 0) {
145 query +=
146 " AND white <> " + uid + " " +
147 " AND black <> " + uid + " ";
148 }
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(
163 err,
164 games.map(
165 g => {
166 return {
167 id: g.id,
168 vid: g.vid,
169 cadence: g.cadence,
170 created: g.created,
171 score: g.score,
172 players: [
173 { id: g.white, name: names[g.white] },
174 { id: g.black, name: names[g.black] }
175 ]
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() {
188 let query =
189 "SELECT g.id, g.cadence, g.created, " +
190 "g.white, g.black, v.name AS vname " +
191 "FROM Games g " +
192 "JOIN Variants v " +
193 " ON g.vid = v.id " +
194 "WHERE score = '*' AND (white = " + uid + " OR black = " + uid + ")";
195 db.all(query, (err, games) => {
196 // Get movesCount (could be done in // with next query)
197 query =
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 });
211 UserModel.getByIds(Object.keys(pids), (err2, users) => {
212 let names = {};
213 users.forEach(u => { names[u.id] = u.name; });
214 cb(
215 null,
216 games.map(
217 g => {
218 return {
219 id: g.id,
220 vname: g.vname,
221 cadence: g.cadence,
222 created: g.created,
223 score: g.score,
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 ]
229 };
230 }
231 )
232 );
233 });
234 });
235 });
236 });
237 },
238
239 // These games could be deleted on some side. movesCount not required
240 getCompleted: function(uid, cursor, cb) {
241 db.serialize(function() {
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 " +
250 " score <> '*' AND" +
251 " created < " + cursor + " AND" +
252 " (" +
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 " )" +
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 });
273 UserModel.getByIds(Object.keys(pids), (err2, users) => {
274 let names = {};
275 users.forEach(u => { names[u.id] = u.name; });
276 cb(
277 null,
278 games.map(
279 g => {
280 return {
281 id: g.id,
282 vname: g.vname,
283 cadence: g.cadence,
284 created: g.created,
285 score: g.score,
286 scoreMsg: g.scoreMsg,
287 players: [
288 { id: g.white, name: names[g.white] },
289 { id: g.black, name: names[g.black] }
290 ],
291 deletedByWhite: g.deletedByWhite,
292 deletedByBlack: g.deletedByBlack
293 };
294 }
295 )
296 );
297 });
298 });
299 });
300 },
301
302 getPlayers: function(id, cb) {
303 db.serialize(function() {
304 const query =
305 "SELECT white, black " +
306 "FROM Games " +
307 "WHERE id = " + id;
308 db.get(query, (err, players) => {
309 return cb(err, players);
310 });
311 });
312 },
313
314 checkGameUpdate: function(obj) {
315 // Check all that is possible (required) in obj:
316 return (
317 (
318 !obj.move || !!(obj.move.idx.toString().match(/^[0-9]+$/))
319 ) && (
320 !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/))
321 ) && (
322 !obj.rematchOffer || !!(obj.rematchOffer.match(/^[wbn]$/))
323 ) && (
324 // TODO: check if commas are still used (probably not)
325 !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9,. /-]*$/))
326 ) && (
327 !obj.score || !!(obj.score.match(/^[012?*\/-]+$/))
328 ) && (
329 !obj.chatRead || ['w','b'].includes(obj.chatRead)
330 ) && (
331 !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/))
332 ) && (
333 !obj.chat || UserModel.checkNameEmail({name: obj.chat.name})
334 )
335 );
336 },
337
338 // obj can have fields move, chat, fen, drawOffer and/or score + message
339 update: function(id, obj, cb) {
340 db.parallelize(function() {
341 let updateQuery =
342 "UPDATE Games " +
343 "SET ";
344 let modifs = "";
345 // NOTE: if drawOffer is set, we should check that it's player's turn
346 // A bit overcomplicated. Let's trust the client on that for now...
347 if (!!obj.drawOffer) {
348 if (obj.drawOffer == "n")
349 // Special "None" update
350 obj.drawOffer = "";
351 modifs += "drawOffer = '" + obj.drawOffer + "',";
352 }
353 if (!!obj.rematchOffer) {
354 if (obj.rematchOffer == "n")
355 // Special "None" update
356 obj.rematchOffer = "";
357 modifs += "rematchOffer = '" + obj.rematchOffer + "',";
358 }
359 if (!!obj.fen) modifs += "fen = '" + obj.fen + "',";
360 if (!!obj.deletedBy) {
361 const myColor = obj.deletedBy == 'w' ? "White" : "Black";
362 modifs += "deletedBy" + myColor + " = true,";
363 }
364 if (!!obj.chatRead) {
365 const myColor = obj.chatRead == 'w' ? "White" : "Black";
366 modifs += "chatRead" + myColor + " = " + Date.now() + ",";
367 }
368 if (!!obj.score) {
369 modifs += "score = '" + obj.score + "'," +
370 "scoreMsg = '" + obj.scoreMsg + "',";
371 }
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 " +
385 "FROM Moves " +
386 "WHERE gid = " + id;
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();
395 }
396 else cb({ errmsg: "Wrong move index" });
397 }
398 else {
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) => {
407 const daysTc = parseInt(ret2.cadence.match(/^[0-9]+/)[0]);
408 if (Date.now() - ret.lastPlayed > daysTc * 24 * 3600 * 1000)
409 finishAndSendQuery();
410 else cb({ errmsg: "Time not over" });
411 });
412 }
413 }
414 });
415 }
416 else finishAndSendQuery();
417 // NOTE: chat and delchat are mutually exclusive
418 if (!!obj.chat) {
419 const query =
420 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
421 + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")";
422 db.run(query, obj.chat.msg);
423 }
424 else if (obj.delchat) {
425 const query =
426 "DELETE " +
427 "FROM Chats " +
428 "WHERE gid = " + id;
429 db.run(query);
430 }
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";
437 const query =
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 }
446 });
447 },
448
449 remove: function(id_s) {
450 const suffix =
451 Array.isArray(id_s)
452 ? " IN (" + id_s.join(",") + ")"
453 : " = " + id_s;
454 db.parallelize(function() {
455 let query =
456 "DELETE FROM Games " +
457 "WHERE id " + suffix;
458 db.run(query);
459 query =
460 "DELETE FROM Moves " +
461 "WHERE gid " + suffix;
462 db.run(query);
463 query =
464 "DELETE FROM Chats " +
465 "WHERE gid " + suffix;
466 db.run(query);
467 });
468 },
469
470 cleanGamesDb: function() {
471 const tsNow = Date.now();
472 // 86400000 = 24 hours in milliseconds
473 const day = 86400000;
474 db.serialize(function() {
475 let query =
476 "SELECT id, created, cadence, score " +
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";
483 db.all(query, (err2, mstats) => {
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,
493 // with no action in the last 2 weeks, or result != '*':
494 let toRemove = [];
495 let lostOnTime = [ [], [] ];
496 games.forEach(g => {
497 if (
498 (
499 !movesGroups[g.id] &&
500 (g.score != '*' || tsNow - g.created > 14*day)
501 )
502 ||
503 (
504 !!movesGroups[g.id] &&
505 movesGroups[g.id].nbMoves == 1 &&
506 (g.score != '*' || tsNow - movesGroups[g.id].lastMaj > 14*day)
507 )
508 ) {
509 toRemove.push(g.id);
510 }
511 // Set score if lost on time and >= 2 moves:
512 else if (
513 !!movesGroups[g.id] &&
514 movesGroups[g.id].nbMoves >= 2 &&
515 tsNow - movesGroups[g.id].lastMaj >
516 // cadence in days * nb seconds per day:
517 parseInt(g.cadence.slice(0, -1), 10) * day
518 ) {
519 lostOnTime[movesGroups[g.id].nbMoves % 2].push(g.id);
520 }
521 });
522 if (toRemove.length > 0) GameModel.remove(toRemove);
523 if (lostOnTime.some(l => l.length > 0)) {
524 db.parallelize(function() {
525 for (let i of [0, 1]) {
526 if (lostOnTime[i].length > 0) {
527 const score = (i == 0 ? "0-1" : "1-0");
528 const query =
529 "UPDATE Games " +
530 "SET score = '" + score + "', scoreMsg = 'Time' " +
531 "WHERE id IN (" + lostOnTime[i].join(',') + ")";
532 db.run(query);
533 }
534 }
535 });
536 }
537 });
538 });
539 });
540 }
541
542 };
543
544 module.exports = GameModel;