Fix parseInt() usage, rename Doubleorda --> Ordamirror, implement Clorange variant
[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 create: function(vid, fen, randomness, cadence, players, cb) {
51 db.serialize(function() {
52 let query =
53 "INSERT INTO Games " +
54 "(" +
55 "vid, fenStart, fen, randomness, " +
56 "white, black, " +
57 "cadence, created" +
58 ") " +
59 "VALUES " +
60 "(" +
61 vid + ",'" + fen + "','" + fen + "'," + randomness + "," +
62 players[0].id + "," + players[1].id + "," +
63 "'" + cadence + "'," + Date.now() +
64 ")";
65 db.run(query, function(err) {
66 cb(err, { id: this.lastID });
67 });
68 });
69 },
70
71 // TODO: some queries here could be async
72 getOne: function(id, cb) {
73 // NOTE: ignoring errors (shouldn't happen at this stage)
74 db.serialize(function() {
75 let query =
76 "SELECT " +
77 "g.id, g.fen, g.fenStart, g.cadence, g.created, " +
78 "g.white, g.black, g.score, g.scoreMsg, " +
79 "g.chatReadWhite, g.chatReadBlack, " +
80 "g.drawOffer, g.rematchOffer, v.name AS vname " +
81 "FROM Games g " +
82 "JOIN Variants v " +
83 " ON g.vid = v.id " +
84 "WHERE g.id = " + id;
85 db.get(query, (err, gameInfo) => {
86 if (!gameInfo) {
87 cb(err || { errmsg: "Game not found" }, undefined);
88 return;
89 }
90 query =
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"];
99 query =
100 "SELECT squares, played, idx " +
101 "FROM Moves " +
102 "WHERE gid = " + id;
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 });
120 });
121 });
122 });
123 });
124 },
125
126 // For display on Hall: no need for moves or chats
127 getObserved: function(uid, cursor, cb) {
128 db.serialize(function() {
129 let query =
130 "SELECT id, vid, cadence, created, score, white, black " +
131 "FROM Games " +
132 "WHERE created < " + cursor + " ";
133 if (uid > 0) {
134 query +=
135 " AND white <> " + uid + " " +
136 " AND black <> " + uid + " ";
137 }
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(
152 err,
153 games.map(
154 g => {
155 return {
156 id: g.id,
157 vid: g.vid,
158 cadence: g.cadence,
159 created: g.created,
160 score: g.score,
161 players: [
162 { id: g.white, name: names[g.white] },
163 { id: g.black, name: names[g.black] }
164 ]
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() {
177 let query =
178 "SELECT g.id, g.cadence, g.created, " +
179 "g.white, g.black, v.name AS vname " +
180 "FROM Games g " +
181 "JOIN Variants v " +
182 " ON g.vid = v.id " +
183 "WHERE score = '*' AND (white = " + uid + " OR black = " + uid + ")";
184 db.all(query, (err, games) => {
185 // Get movesCount (could be done in // with next query)
186 query =
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 });
200 UserModel.getByIds(Object.keys(pids), (err2, users) => {
201 let names = {};
202 users.forEach(u => { names[u.id] = u.name; });
203 cb(
204 null,
205 games.map(
206 g => {
207 return {
208 id: g.id,
209 vname: g.vname,
210 cadence: g.cadence,
211 created: g.created,
212 score: g.score,
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 ]
218 };
219 }
220 )
221 );
222 });
223 });
224 });
225 });
226 },
227
228 // These games could be deleted on some side. movesCount not required
229 getCompleted: function(uid, cursor, cb) {
230 db.serialize(function() {
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 " +
239 " score <> '*' AND" +
240 " created < " + cursor + " AND" +
241 " (" +
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 " )" +
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 });
262 UserModel.getByIds(Object.keys(pids), (err2, users) => {
263 let names = {};
264 users.forEach(u => { names[u.id] = u.name; });
265 cb(
266 null,
267 games.map(
268 g => {
269 return {
270 id: g.id,
271 vname: g.vname,
272 cadence: g.cadence,
273 created: g.created,
274 score: g.score,
275 scoreMsg: g.scoreMsg,
276 players: [
277 { id: g.white, name: names[g.white] },
278 { id: g.black, name: names[g.black] }
279 ],
280 deletedByWhite: g.deletedByWhite,
281 deletedByBlack: g.deletedByBlack
282 };
283 }
284 )
285 );
286 });
287 });
288 });
289 },
290
291 getPlayers: function(id, cb) {
292 db.serialize(function() {
293 const query =
294 "SELECT white, black " +
295 "FROM Games " +
296 "WHERE id = " + id;
297 db.get(query, (err, players) => {
298 return cb(err, players);
299 });
300 });
301 },
302
303 checkGameUpdate: function(obj) {
304 // Check all that is possible (required) in obj:
305 return (
306 (
307 !obj.move || !!(obj.move.idx.toString().match(/^[0-9]+$/))
308 ) && (
309 !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/))
310 ) && (
311 !obj.rematchOffer || !!(obj.rematchOffer.match(/^[wbn]$/))
312 ) && (
313 // TODO: check if commas are still used (probably not)
314 !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9,. /-]*$/))
315 ) && (
316 !obj.score || !!(obj.score.match(/^[012?*\/-]+$/))
317 ) && (
318 !obj.chatRead || ['w','b'].includes(obj.chatRead)
319 ) && (
320 !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/))
321 ) && (
322 !obj.chat || UserModel.checkNameEmail({name: obj.chat.name})
323 )
324 );
325 },
326
327 // obj can have fields move, chat, fen, drawOffer and/or score + message
328 update: function(id, obj, cb) {
329 db.parallelize(function() {
330 let updateQuery =
331 "UPDATE Games " +
332 "SET ";
333 let modifs = "";
334 // NOTE: if drawOffer is set, we should check that it's player's turn
335 // A bit overcomplicated. Let's trust the client on that for now...
336 if (!!obj.drawOffer) {
337 if (obj.drawOffer == "n")
338 // Special "None" update
339 obj.drawOffer = "";
340 modifs += "drawOffer = '" + obj.drawOffer + "',";
341 }
342 if (!!obj.rematchOffer) {
343 if (obj.rematchOffer == "n")
344 // Special "None" update
345 obj.rematchOffer = "";
346 modifs += "rematchOffer = '" + obj.rematchOffer + "',";
347 }
348 if (!!obj.fen) modifs += "fen = '" + obj.fen + "',";
349 if (!!obj.deletedBy) {
350 const myColor = obj.deletedBy == 'w' ? "White" : "Black";
351 modifs += "deletedBy" + myColor + " = true,";
352 }
353 if (!!obj.chatRead) {
354 const myColor = obj.chatRead == 'w' ? "White" : "Black";
355 modifs += "chatRead" + myColor + " = " + Date.now() + ",";
356 }
357 if (!!obj.score) {
358 modifs += "score = '" + obj.score + "'," +
359 "scoreMsg = '" + obj.scoreMsg + "',";
360 }
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 " +
374 "FROM Moves " +
375 "WHERE gid = " + id;
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();
384 }
385 else cb({ errmsg: "Wrong move index" });
386 }
387 else {
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) => {
396 const daysTc = parseInt(ret2.cadence.match(/^[0-9]+/)[0]);
397 if (Date.now() - ret.lastPlayed > daysTc * 24 * 3600 * 1000)
398 finishAndSendQuery();
399 else cb({ errmsg: "Time not over" });
400 });
401 }
402 }
403 });
404 }
405 else finishAndSendQuery();
406 // NOTE: chat and delchat are mutually exclusive
407 if (!!obj.chat) {
408 const query =
409 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
410 + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")";
411 db.run(query, obj.chat.msg);
412 }
413 else if (obj.delchat) {
414 const query =
415 "DELETE " +
416 "FROM Chats " +
417 "WHERE gid = " + id;
418 db.run(query);
419 }
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";
426 const query =
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 }
435 });
436 },
437
438 remove: function(id_s) {
439 const suffix =
440 Array.isArray(id_s)
441 ? " IN (" + id_s.join(",") + ")"
442 : " = " + id_s;
443 db.parallelize(function() {
444 let query =
445 "DELETE FROM Games " +
446 "WHERE id " + suffix;
447 db.run(query);
448 query =
449 "DELETE FROM Moves " +
450 "WHERE gid " + suffix;
451 db.run(query);
452 query =
453 "DELETE FROM Chats " +
454 "WHERE gid " + suffix;
455 db.run(query);
456 });
457 },
458
459 cleanGamesDb: function() {
460 const tsNow = Date.now();
461 // 86400000 = 24 hours in milliseconds
462 const day = 86400000;
463 db.serialize(function() {
464 let query =
465 "SELECT id, created " +
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";
472 db.all(query, (err2, mstats) => {
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,
482 // with no action in the last 2 weeks:
483 let toRemove = [];
484 games.forEach(g => {
485 if (
486 (
487 !movesGroups[g.id] &&
488 tsNow - g.created > 14*day
489 )
490 ||
491 (
492 !!movesGroups[g.id] &&
493 movesGroups[g.id].nbMoves == 1 &&
494 tsNow - movesGroups[g.id].lastMaj > 14*day
495 )
496 ) {
497 toRemove.push(g.id);
498 }
499 });
500 if (toRemove.length > 0) GameModel.remove(toRemove);
501 });
502 });
503 });
504 }
505 }
506
507 module.exports = GameModel;