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