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