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