Refactor Games structure on server: no longer use an extra 'Players' table
[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 " (" + uid + " = white AND NOT deletedByWhite) OR " +
239 " (" + uid + " = black AND NOT deletedByBlack)" +
240 " ) ";
241 query +=
242 "ORDER BY created DESC " +
243 "LIMIT 20";
244 db.all(query, (err, games) => {
245 // Query player names
246 let pids = {};
247 games.forEach(g => {
248 if (!pids[g.white]) pids[g.white] = true;
249 if (!pids[g.black]) pids[g.black] = true;
250 });
251 UserModel.getByIds(Object.keys(pids), (err2, users) => {
252 let names = {};
253 users.forEach(u => { names[u.id] = u.name; });
254 cb(
255 null,
256 games.map(
257 g => {
258 return {
259 id: g.id,
260 vname: g.vname,
261 cadence: g.cadence,
262 created: g.created,
263 score: g.score,
264 scoreMsg: g.scoreMsg,
265 players: [
266 { id: g.white, name: names[g.white] },
267 { id: g.black, name: names[g.black] }
268 ],
269 deletedByWhite: g.deletedByWhite,
270 deletedByBlack: g.deletedByBlack
271 };
272 }
273 )
274 );
275 });
276 });
277 });
278 },
279
280 getPlayers: function(id, cb) {
281 db.serialize(function() {
282 const query =
283 "SELECT white, black " +
284 "FROM Games " +
285 "WHERE id = " + id;
286 db.get(query, (err, players) => {
287 return cb(err, players);
288 });
289 });
290 },
291
292 checkGameUpdate: function(obj) {
293 // Check all that is possible (required) in obj:
294 return (
295 (
296 !obj.move || (
297 !!(obj.move.played.toString().match(/^[0-9]+$/)) &&
298 !!(obj.move.idx.toString().match(/^[0-9]+$/))
299 )
300 ) && (
301 !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/))
302 ) && (
303 !obj.rematchOffer || !!(obj.rematchOffer.match(/^[wbn]$/))
304 ) && (
305 !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9, /-]*$/))
306 ) && (
307 !obj.score || !!(obj.score.match(/^[012?*\/-]+$/))
308 ) && (
309 !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/))
310 ) && (
311 !obj.chat || UserModel.checkNameEmail({name: obj.chat.name})
312 )
313 );
314 },
315
316 // obj can have fields move, chat, fen, drawOffer and/or score + message
317 update: function(id, obj, cb) {
318 db.parallelize(function() {
319 let updateQuery =
320 "UPDATE Games " +
321 "SET ";
322 let modifs = "";
323 // NOTE: if drawOffer is set, we should check that it's player's turn
324 // A bit overcomplicated. Let's trust the client on that for now...
325 if (!!obj.drawOffer) {
326 if (obj.drawOffer == "n")
327 // Special "None" update
328 obj.drawOffer = "";
329 modifs += "drawOffer = '" + obj.drawOffer + "',";
330 }
331 if (!!obj.rematchOffer) {
332 if (obj.rematchOffer == "n")
333 // Special "None" update
334 obj.rematchOffer = "";
335 modifs += "rematchOffer = '" + obj.rematchOffer + "',";
336 }
337 if (!!obj.fen) modifs += "fen = '" + obj.fen + "',";
338 if (!!obj.deletedBy) {
339 const myColor = obj.deletedBy == 'w' ? "White" : "Black";
340 modifs += "deletedBy" + myColor + " = true,";
341 }
342 if (!!obj.score) {
343 modifs += "score = '" + obj.score + "'," +
344 "scoreMsg = '" + obj.scoreMsg + "',";
345 }
346 const finishAndSendQuery = () => {
347 modifs = modifs.slice(0, -1); //remove last comma
348 if (modifs.length > 0) {
349 updateQuery += modifs + " WHERE id = " + id;
350 db.run(updateQuery);
351 }
352 cb(null);
353 };
354 if (!!obj.move || (!!obj.score && obj.scoreMsg == "Time")) {
355 // Security: only update moves if index is right,
356 // and score with scoreMsg "Time" if really lost on time.
357 let query =
358 "SELECT MAX(idx) AS maxIdx, MAX(played) AS lastPlayed " +
359 "FROM Moves " +
360 "WHERE gid = " + id;
361 db.get(query, (err, ret) => {
362 if (!!obj.move ) {
363 if (!ret.maxIdx || ret.maxIdx + 1 == obj.move.idx) {
364 query =
365 "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
366 "(" + id + ",?," + Date.now() + "," + obj.move.idx + ")";
367 db.run(query, JSON.stringify(obj.move.squares));
368 finishAndSendQuery();
369 } else cb({ errmsg: "Wrong move index" });
370 } else {
371 if (ret.maxIdx < 2) cb({ errmsg: "Time not over" });
372 else {
373 // We also need the game cadence
374 query =
375 "SELECT cadence " +
376 "FROM Games " +
377 "WHERE id = " + id;
378 db.get(query, (err2, ret2) => {
379 const daysTc = parseInt(ret2.cadence.match(/\(^[0-9]+\)/)[0]);
380 if (Date.now() - ret.lastPlayed > daysTc * 24 * 3600 * 1000)
381 finishAndSendQuery();
382 else cb({ errmsg: "Time not over" });
383 });
384 }
385 }
386 });
387 } else finishAndSendQuery();
388 // NOTE: chat and delchat are mutually exclusive
389 if (!!obj.chat) {
390 const query =
391 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
392 + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")";
393 db.run(query, obj.chat.msg);
394 } else if (obj.delchat) {
395 const query =
396 "DELETE " +
397 "FROM Chats " +
398 "WHERE gid = " + id;
399 db.run(query);
400 }
401 if (!!obj.deletedBy) {
402 // Did my opponent delete it too?
403 let selection =
404 "deletedBy" +
405 (obj.deletedBy == 'w' ? "Black" : "White") +
406 " AS deletedByOpp";
407 const query =
408 "SELECT " + selection + " " +
409 "FROM Games " +
410 "WHERE id = " + id;
411 db.get(query, (err,ret) => {
412 // If yes: just remove game
413 if (!!ret.deletedByOpp) GameModel.remove(id);
414 });
415 }
416 });
417 },
418
419 remove: function(id_s) {
420 const suffix =
421 Array.isArray(id_s)
422 ? " IN (" + id_s.join(",") + ")"
423 : " = " + id_s;
424 db.parallelize(function() {
425 let query =
426 "DELETE FROM Games " +
427 "WHERE id " + suffix;
428 db.run(query);
429 query =
430 "DELETE FROM Moves " +
431 "WHERE gid " + suffix;
432 db.run(query);
433 query =
434 "DELETE FROM Chats " +
435 "WHERE gid " + suffix;
436 db.run(query);
437 });
438 },
439
440 cleanGamesDb: function() {
441 const tsNow = Date.now();
442 // 86400000 = 24 hours in milliseconds
443 const day = 86400000;
444 db.serialize(function() {
445 let query =
446 "SELECT id, created " +
447 "FROM Games";
448 db.all(query, (err, games) => {
449 query =
450 "SELECT gid, count(*) AS nbMoves, MAX(played) AS lastMaj " +
451 "FROM Moves " +
452 "GROUP BY gid";
453 db.get(query, (err2, mstats) => {
454 // Reorganize moves data to avoid too many array lookups:
455 let movesGroups = {};
456 mstats.forEach(ms => {
457 movesGroups[ms.gid] = {
458 nbMoves: ms.nbMoves,
459 lastMaj: ms.lastMaj
460 };
461 });
462 // Remove games still not really started,
463 // with no action in the last 3 months:
464 let toRemove = [];
465 games.forEach(g => {
466 if (
467 (
468 !movesGroups[g.id] &&
469 tsNow - g.created > 91*day
470 )
471 ||
472 (
473 movesGroups[g.id].nbMoves == 1 &&
474 tsNow - movesGroups[g.id].lastMaj > 91*day
475 )
476 ) {
477 toRemove.push(g.id);
478 }
479 });
480 if (toRemove.length > 0) GameModel.remove(toRemove);
481 });
482 });
483 });
484 }
485 }
486
487 module.exports = GameModel;