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 BA |
130 | "SELECT id, vid, cadence, created, score, white, black " + |
131 | "FROM Games "; | |
0234201f BA |
132 | if (uid > 0) query += |
133 | "WHERE " + | |
134 | " created < " + cursor + " AND " + | |
135 | " white <> " + uid + " AND " + | |
136 | " black <> " + uid + " "; | |
137 | query += | |
138 | "ORDER BY created DESC " + | |
139 | "LIMIT 20"; //TODO: 20 hard-coded... | |
140 | db.all(query, (err, games) => { | |
141 | // Query players names | |
142 | let pids = {}; | |
143 | games.forEach(g => { | |
144 | if (!pids[g.white]) pids[g.white] = true; | |
145 | if (!pids[g.black]) pids[g.black] = true; | |
146 | }); | |
147 | UserModel.getByIds(Object.keys(pids), (err2, users) => { | |
148 | let names = {}; | |
149 | users.forEach(u => { names[u.id] = u.name; }); | |
150 | cb( | |
f14572c4 | 151 | err, |
0234201f BA |
152 | games.map( |
153 | g => { | |
154 | return { | |
155 | id: g.id, | |
f14572c4 | 156 | vid: g.vid, |
0234201f | 157 | cadence: g.cadence, |
0234201f BA |
158 | created: g.created, |
159 | score: g.score, | |
f14572c4 BA |
160 | players: [ |
161 | { id: g.white, name: names[g.white] }, | |
162 | { id: g.black, name: names[g.black] } | |
163 | ] | |
0234201f BA |
164 | }; |
165 | } | |
166 | ) | |
167 | ); | |
168 | }); | |
169 | }); | |
170 | }); | |
171 | }, | |
172 | ||
173 | // For display on MyGames: registered user only | |
174 | getRunning: function(uid, cb) { | |
175 | db.serialize(function() { | |
aae89b49 | 176 | let query = |
f14572c4 | 177 | "SELECT g.id, g.cadence, g.created, " + |
0234201f | 178 | "g.white, g.black, v.name AS vname " + |
aae89b49 BA |
179 | "FROM Games g " + |
180 | "JOIN Variants v " + | |
181 | " ON g.vid = v.id " + | |
f14572c4 | 182 | "WHERE score = '*' AND (white = " + uid + " OR black = " + uid + ")"; |
0234201f BA |
183 | db.all(query, (err, games) => { |
184 | // Get movesCount (could be done in // with next query) | |
aae89b49 | 185 | query = |
0234201f BA |
186 | "SELECT gid, COUNT(*) AS nbMoves " + |
187 | "FROM Moves " + | |
188 | "WHERE gid IN " + "(" + games.map(g => g.id).join(",") + ") " + | |
189 | "GROUP BY gid"; | |
190 | db.all(query, (err, mstats) => { | |
191 | let movesCounts = {}; | |
192 | mstats.forEach(ms => { movesCounts[ms.gid] = ms.nbMoves; }); | |
193 | // Query player names | |
194 | let pids = {}; | |
195 | games.forEach(g => { | |
196 | if (!pids[g.white]) pids[g.white] = true; | |
197 | if (!pids[g.black]) pids[g.black] = true; | |
198 | }); | |
f14572c4 | 199 | UserModel.getByIds(Object.keys(pids), (err2, users) => { |
0234201f BA |
200 | let names = {}; |
201 | users.forEach(u => { names[u.id] = u.name; }); | |
202 | cb( | |
f14572c4 | 203 | null, |
0234201f BA |
204 | games.map( |
205 | g => { | |
206 | return { | |
207 | id: g.id, | |
0234201f | 208 | vname: g.vname, |
f14572c4 | 209 | cadence: g.cadence, |
0234201f BA |
210 | created: g.created, |
211 | score: g.score, | |
f14572c4 BA |
212 | movesCount: movesCounts[g.id] || 0, |
213 | players: [ | |
214 | { id: g.white, name: names[g.white] }, | |
215 | { id: g.black, name: names[g.black] } | |
216 | ] | |
0234201f BA |
217 | }; |
218 | } | |
219 | ) | |
aae89b49 | 220 | ); |
aae89b49 BA |
221 | }); |
222 | }); | |
223 | }); | |
0234201f BA |
224 | }); |
225 | }, | |
226 | ||
227 | // These games could be deleted on some side. movesCount not required | |
228 | getCompleted: function(uid, cursor, cb) { | |
dac39588 | 229 | db.serialize(function() { |
0234201f BA |
230 | let query = |
231 | "SELECT g.id, g.cadence, g.created, g.score, g.scoreMsg, " + | |
232 | "g.white, g.black, g.deletedByWhite, g.deletedByBlack, " + | |
233 | "v.name AS vname " + | |
234 | "FROM Games g " + | |
235 | "JOIN Variants v " + | |
236 | " ON g.vid = v.id " + | |
237 | "WHERE " + | |
11589e7c BA |
238 | " score <> '*' AND" + |
239 | " created < " + cursor + " AND" + | |
0234201f | 240 | " (" + |
11589e7c BA |
241 | " (" + |
242 | " white = " + uid + " AND" + | |
243 | " (deletedByWhite IS NULL OR NOT deletedByWhite)" + | |
244 | " )" + | |
245 | " OR " + | |
246 | " (" + | |
247 | " black = " + uid + " AND" + | |
248 | " (deletedByBlack IS NULL OR NOT deletedByBlack)" + | |
249 | " )" + | |
0234201f BA |
250 | " ) "; |
251 | query += | |
252 | "ORDER BY created DESC " + | |
253 | "LIMIT 20"; | |
254 | db.all(query, (err, games) => { | |
255 | // Query player names | |
256 | let pids = {}; | |
257 | games.forEach(g => { | |
258 | if (!pids[g.white]) pids[g.white] = true; | |
259 | if (!pids[g.black]) pids[g.black] = true; | |
260 | }); | |
f14572c4 | 261 | UserModel.getByIds(Object.keys(pids), (err2, users) => { |
0234201f BA |
262 | let names = {}; |
263 | users.forEach(u => { names[u.id] = u.name; }); | |
264 | cb( | |
f14572c4 | 265 | null, |
0234201f BA |
266 | games.map( |
267 | g => { | |
268 | return { | |
269 | id: g.id, | |
0234201f | 270 | vname: g.vname, |
f14572c4 | 271 | cadence: g.cadence, |
0234201f BA |
272 | created: g.created, |
273 | score: g.score, | |
274 | scoreMsg: g.scoreMsg, | |
f14572c4 BA |
275 | players: [ |
276 | { id: g.white, name: names[g.white] }, | |
277 | { id: g.black, name: names[g.black] } | |
278 | ], | |
0234201f BA |
279 | deletedByWhite: g.deletedByWhite, |
280 | deletedByBlack: g.deletedByBlack | |
281 | }; | |
282 | } | |
283 | ) | |
284 | ); | |
285 | }); | |
dac39588 BA |
286 | }); |
287 | }); | |
288 | }, | |
ab4f4bf2 | 289 | |
0234201f | 290 | getPlayers: function(id, cb) { |
411d23cd BA |
291 | db.serialize(function() { |
292 | const query = | |
0234201f BA |
293 | "SELECT white, black " + |
294 | "FROM Games " + | |
f14572c4 BA |
295 | "WHERE id = " + id; |
296 | db.get(query, (err, players) => { | |
411d23cd BA |
297 | return cb(err, players); |
298 | }); | |
299 | }); | |
300 | }, | |
301 | ||
0234201f | 302 | checkGameUpdate: function(obj) { |
58e7b94e | 303 | // Check all that is possible (required) in obj: |
866842c3 BA |
304 | return ( |
305 | ( | |
8be8238c | 306 | !obj.move || !!(obj.move.idx.toString().match(/^[0-9]+$/)) |
866842c3 | 307 | ) && ( |
188b4a8f | 308 | !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/)) |
c292ebb2 BA |
309 | ) && ( |
310 | !obj.rematchOffer || !!(obj.rematchOffer.match(/^[wbn]$/)) | |
866842c3 | 311 | ) && ( |
188b4a8f | 312 | !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9, /-]*$/)) |
866842c3 | 313 | ) && ( |
188b4a8f | 314 | !obj.score || !!(obj.score.match(/^[012?*\/-]+$/)) |
1ef65040 BA |
315 | ) && ( |
316 | !obj.chatRead || !(['w','b'].includes(obj.chatRead)) | |
866842c3 | 317 | ) && ( |
188b4a8f | 318 | !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/)) |
866842c3 BA |
319 | ) && ( |
320 | !obj.chat || UserModel.checkNameEmail({name: obj.chat.name}) | |
321 | ) | |
322 | ); | |
58e7b94e BA |
323 | }, |
324 | ||
866842c3 | 325 | // obj can have fields move, chat, fen, drawOffer and/or score + message |
0234201f | 326 | update: function(id, obj, cb) { |
dac39588 | 327 | db.parallelize(function() { |
f14572c4 | 328 | let updateQuery = |
3d55deea BA |
329 | "UPDATE Games " + |
330 | "SET "; | |
3837d4f7 | 331 | let modifs = ""; |
633959bf | 332 | // NOTE: if drawOffer is set, we should check that it's player's turn |
dfeb96ea | 333 | // A bit overcomplicated. Let's trust the client on that for now... |
f14572c4 BA |
334 | if (!!obj.drawOffer) { |
335 | if (obj.drawOffer == "n") | |
336 | // Special "None" update | |
633959bf BA |
337 | obj.drawOffer = ""; |
338 | modifs += "drawOffer = '" + obj.drawOffer + "',"; | |
339 | } | |
f14572c4 BA |
340 | if (!!obj.rematchOffer) { |
341 | if (obj.rematchOffer == "n") | |
342 | // Special "None" update | |
c292ebb2 BA |
343 | obj.rematchOffer = ""; |
344 | modifs += "rematchOffer = '" + obj.rematchOffer + "',"; | |
345 | } | |
f14572c4 | 346 | if (!!obj.fen) modifs += "fen = '" + obj.fen + "',"; |
aae89b49 BA |
347 | if (!!obj.deletedBy) { |
348 | const myColor = obj.deletedBy == 'w' ? "White" : "Black"; | |
349 | modifs += "deletedBy" + myColor + " = true,"; | |
350 | } | |
1ef65040 BA |
351 | if (!!obj.chatRead) { |
352 | const myColor = obj.chatRead == 'w' ? "White" : "Black"; | |
353 | modifs += "chatRead" + myColor + " = " + Date.now() + ","; | |
354 | } | |
f14572c4 BA |
355 | if (!!obj.score) { |
356 | modifs += "score = '" + obj.score + "'," + | |
357 | "scoreMsg = '" + obj.scoreMsg + "',"; | |
3837d4f7 | 358 | } |
f14572c4 BA |
359 | const finishAndSendQuery = () => { |
360 | modifs = modifs.slice(0, -1); //remove last comma | |
361 | if (modifs.length > 0) { | |
362 | updateQuery += modifs + " WHERE id = " + id; | |
363 | db.run(updateQuery); | |
364 | } | |
365 | cb(null); | |
366 | }; | |
367 | if (!!obj.move || (!!obj.score && obj.scoreMsg == "Time")) { | |
368 | // Security: only update moves if index is right, | |
369 | // and score with scoreMsg "Time" if really lost on time. | |
370 | let query = | |
371 | "SELECT MAX(idx) AS maxIdx, MAX(played) AS lastPlayed " + | |
fb68b0c2 BA |
372 | "FROM Moves " + |
373 | "WHERE gid = " + id; | |
f14572c4 BA |
374 | db.get(query, (err, ret) => { |
375 | if (!!obj.move ) { | |
376 | if (!ret.maxIdx || ret.maxIdx + 1 == obj.move.idx) { | |
377 | query = | |
378 | "INSERT INTO Moves (gid, squares, played, idx) VALUES " + | |
379 | "(" + id + ",?," + Date.now() + "," + obj.move.idx + ")"; | |
380 | db.run(query, JSON.stringify(obj.move.squares)); | |
381 | finishAndSendQuery(); | |
382 | } else cb({ errmsg: "Wrong move index" }); | |
383 | } else { | |
384 | if (ret.maxIdx < 2) cb({ errmsg: "Time not over" }); | |
385 | else { | |
386 | // We also need the game cadence | |
387 | query = | |
388 | "SELECT cadence " + | |
389 | "FROM Games " + | |
390 | "WHERE id = " + id; | |
391 | db.get(query, (err2, ret2) => { | |
1112f1fd | 392 | const daysTc = parseInt(ret2.cadence.match(/^[0-9]+/)[0]); |
f14572c4 BA |
393 | if (Date.now() - ret.lastPlayed > daysTc * 24 * 3600 * 1000) |
394 | finishAndSendQuery(); | |
395 | else cb({ errmsg: "Time not over" }); | |
396 | }); | |
397 | } | |
fb68b0c2 | 398 | } |
fb68b0c2 | 399 | }); |
f14572c4 BA |
400 | } else finishAndSendQuery(); |
401 | // NOTE: chat and delchat are mutually exclusive | |
402 | if (!!obj.chat) { | |
403 | const query = | |
dac39588 | 404 | "INSERT INTO Chats (gid, msg, name, added) VALUES (" |
41c80bb6 | 405 | + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")"; |
58e7b94e | 406 | db.run(query, obj.chat.msg); |
f14572c4 BA |
407 | } else if (obj.delchat) { |
408 | const query = | |
db1f1f9a BA |
409 | "DELETE " + |
410 | "FROM Chats " + | |
411 | "WHERE gid = " + id; | |
23ecf008 | 412 | db.run(query); |
db1f1f9a | 413 | } |
aae89b49 BA |
414 | if (!!obj.deletedBy) { |
415 | // Did my opponent delete it too? | |
416 | let selection = | |
417 | "deletedBy" + | |
418 | (obj.deletedBy == 'w' ? "Black" : "White") + | |
419 | " AS deletedByOpp"; | |
f14572c4 | 420 | const query = |
aae89b49 BA |
421 | "SELECT " + selection + " " + |
422 | "FROM Games " + | |
423 | "WHERE id = " + id; | |
424 | db.get(query, (err,ret) => { | |
425 | // If yes: just remove game | |
426 | if (!!ret.deletedByOpp) GameModel.remove(id); | |
427 | }); | |
428 | } | |
3d55deea BA |
429 | }); |
430 | }, | |
431 | ||
0234201f BA |
432 | remove: function(id_s) { |
433 | const suffix = | |
434 | Array.isArray(id_s) | |
435 | ? " IN (" + id_s.join(",") + ")" | |
436 | : " = " + id_s; | |
dac39588 BA |
437 | db.parallelize(function() { |
438 | let query = | |
439 | "DELETE FROM Games " + | |
0234201f | 440 | "WHERE id " + suffix; |
dac39588 BA |
441 | db.run(query); |
442 | query = | |
443 | "DELETE FROM Moves " + | |
0234201f | 444 | "WHERE gid " + suffix; |
dac39588 BA |
445 | db.run(query); |
446 | query = | |
447 | "DELETE FROM Chats " + | |
0234201f | 448 | "WHERE gid " + suffix; |
dac39588 BA |
449 | db.run(query); |
450 | }); | |
451 | }, | |
d431028c | 452 | |
0234201f | 453 | cleanGamesDb: function() { |
d431028c BA |
454 | const tsNow = Date.now(); |
455 | // 86400000 = 24 hours in milliseconds | |
456 | const day = 86400000; | |
457 | db.serialize(function() { | |
458 | let query = | |
0f7011a2 | 459 | "SELECT id, created " + |
0234201f BA |
460 | "FROM Games"; |
461 | db.all(query, (err, games) => { | |
462 | query = | |
463 | "SELECT gid, count(*) AS nbMoves, MAX(played) AS lastMaj " + | |
464 | "FROM Moves " + | |
465 | "GROUP BY gid"; | |
6f2f9437 | 466 | db.all(query, (err2, mstats) => { |
0234201f BA |
467 | // Reorganize moves data to avoid too many array lookups: |
468 | let movesGroups = {}; | |
469 | mstats.forEach(ms => { | |
470 | movesGroups[ms.gid] = { | |
471 | nbMoves: ms.nbMoves, | |
472 | lastMaj: ms.lastMaj | |
473 | }; | |
474 | }); | |
475 | // Remove games still not really started, | |
6f2f9437 | 476 | // with no action in the last 2 weeks: |
0234201f BA |
477 | let toRemove = []; |
478 | games.forEach(g => { | |
479 | if ( | |
480 | ( | |
481 | !movesGroups[g.id] && | |
6f2f9437 | 482 | tsNow - g.created > 14*day |
0234201f BA |
483 | ) |
484 | || | |
485 | ( | |
486 | movesGroups[g.id].nbMoves == 1 && | |
6f2f9437 | 487 | tsNow - movesGroups[g.id].lastMaj > 14*day |
0234201f BA |
488 | ) |
489 | ) { | |
490 | toRemove.push(g.id); | |
d431028c BA |
491 | } |
492 | }); | |
0234201f | 493 | if (toRemove.length > 0) GameModel.remove(toRemove); |
d431028c BA |
494 | }); |
495 | }); | |
496 | }); | |
0234201f | 497 | } |
00f2759e | 498 | } |
ab4f4bf2 BA |
499 | |
500 | module.exports = GameModel; |