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