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