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 | |
00f2759e BA |
21 | * |
22 | * Structure table Moves: | |
ab4f4bf2 | 23 | * gid: ref game id |
f41ce580 | 24 | * squares: varchar (description) |
00f2759e BA |
25 | * played: datetime |
26 | * idx: integer | |
3837d4f7 BA |
27 | * |
28 | * Structure table Chats: | |
29 | * gid: game id (int) | |
30 | * msg: varchar | |
31 | * name: varchar | |
3837d4f7 | 32 | * added: datetime |
00f2759e BA |
33 | */ |
34 | ||
ab4f4bf2 | 35 | const GameModel = |
00f2759e | 36 | { |
58e7b94e | 37 | checkGameInfo: function(g) { |
866842c3 BA |
38 | return ( |
39 | g.vid.toString().match(/^[0-9]+$/) && | |
40 | g.cadence.match(/^[0-9dhms +]+$/) && | |
0234201f | 41 | g.randomness.match(/^[0-2]$/) && |
866842c3 BA |
42 | g.fen.match(/^[a-zA-Z0-9, /-]*$/) && |
43 | g.players.length == 2 && | |
0234201f | 44 | g.players.every(p => p.toString().match(/^[0-9]+$/)) |
866842c3 | 45 | ); |
58e7b94e BA |
46 | }, |
47 | ||
0234201f | 48 | create: function(vid, fen, randomness, cadence, players, cb) { |
dac39588 BA |
49 | db.serialize(function() { |
50 | let query = | |
71468011 | 51 | "INSERT INTO Games " + |
0234201f BA |
52 | "(" + |
53 | "vid, fenStart, fen, randomness, " + | |
54 | "white, black, " + | |
55 | "cadence, created" + | |
56 | ") " + | |
71468011 | 57 | "VALUES " + |
0234201f BA |
58 | "(" + |
59 | vid + ",'" + fen + "','" + fen + "'," + randomness + "," + | |
60 | "'" + players[0] + "','" + players[1] + "," + | |
61 | "'" + cadence + "'," + Date.now() + | |
62 | ")"; | |
8c564f46 | 63 | db.run(query, function(err) { |
0234201f | 64 | cb(err, { id: this.lastId }); |
dac39588 BA |
65 | }); |
66 | }); | |
67 | }, | |
00f2759e | 68 | |
71468011 | 69 | // TODO: some queries here could be async |
0234201f | 70 | getOne: function(id, cb) { |
866842c3 | 71 | // NOTE: ignoring errors (shouldn't happen at this stage) |
dac39588 | 72 | db.serialize(function() { |
dac39588 | 73 | let query = |
0234201f BA |
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 " + | |
dac39588 | 78 | "FROM Games g " + |
f41ce580 BA |
79 | "JOIN Variants v " + |
80 | " ON g.vid = v.id " + | |
dac39588 | 81 | "WHERE g.id = " + id; |
aae89b49 | 82 | db.get(query, (err, gameInfo) => { |
dac39588 | 83 | query = |
0234201f BA |
84 | "SELECT squares, played, idx " + |
85 | "FROM Moves " + | |
86 | "WHERE gid = " + id; | |
87 | db.all(query, (err3, moves) => { | |
aae89b49 | 88 | query = |
0234201f BA |
89 | "SELECT msg, name, added " + |
90 | "FROM Chats " + | |
aae89b49 | 91 | "WHERE gid = " + id; |
0234201f BA |
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); | |
aae89b49 | 102 | }); |
dac39588 BA |
103 | }); |
104 | }); | |
105 | }); | |
106 | }, | |
00f2759e | 107 | |
0234201f BA |
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() { | |
aae89b49 | 156 | let query = |
0234201f BA |
157 | "SELECT g.id, g.cadence, g.created, g.score, " + |
158 | "g.white, g.black, v.name AS vname " + | |
aae89b49 BA |
159 | "FROM Games g " + |
160 | "JOIN Variants v " + | |
161 | " ON g.vid = v.id " + | |
0234201f BA |
162 | "WHERE white = " + uid + " OR black = " + uid; |
163 | db.all(query, (err, games) => { | |
164 | // Get movesCount (could be done in // with next query) | |
aae89b49 | 165 | query = |
0234201f BA |
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 | ) | |
aae89b49 | 197 | ); |
aae89b49 BA |
198 | }); |
199 | }); | |
200 | }); | |
0234201f BA |
201 | }); |
202 | }, | |
203 | ||
204 | // These games could be deleted on some side. movesCount not required | |
205 | getCompleted: function(uid, cursor, cb) { | |
dac39588 | 206 | db.serialize(function() { |
0234201f BA |
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 | }); | |
dac39588 BA |
252 | }); |
253 | }); | |
254 | }, | |
ab4f4bf2 | 255 | |
0234201f | 256 | getPlayers: function(id, cb) { |
411d23cd BA |
257 | db.serialize(function() { |
258 | const query = | |
0234201f BA |
259 | "SELECT white, black " + |
260 | "FROM Games " + | |
411d23cd | 261 | "WHERE gid = " + id; |
0234201f | 262 | db.all(query, (err, players) => { |
411d23cd BA |
263 | return cb(err, players); |
264 | }); | |
265 | }); | |
266 | }, | |
267 | ||
0234201f | 268 | checkGameUpdate: function(obj) { |
58e7b94e | 269 | // Check all that is possible (required) in obj: |
866842c3 BA |
270 | return ( |
271 | ( | |
272 | !obj.move || ( | |
188b4a8f BA |
273 | !!(obj.move.played.toString().match(/^[0-9]+$/)) && |
274 | !!(obj.move.idx.toString().match(/^[0-9]+$/)) | |
866842c3 BA |
275 | ) |
276 | ) && ( | |
188b4a8f | 277 | !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/)) |
c292ebb2 BA |
278 | ) && ( |
279 | !obj.rematchOffer || !!(obj.rematchOffer.match(/^[wbn]$/)) | |
866842c3 | 280 | ) && ( |
188b4a8f | 281 | !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9, /-]*$/)) |
866842c3 | 282 | ) && ( |
188b4a8f | 283 | !obj.score || !!(obj.score.match(/^[012?*\/-]+$/)) |
866842c3 | 284 | ) && ( |
188b4a8f | 285 | !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/)) |
866842c3 BA |
286 | ) && ( |
287 | !obj.chat || UserModel.checkNameEmail({name: obj.chat.name}) | |
288 | ) | |
289 | ); | |
58e7b94e BA |
290 | }, |
291 | ||
866842c3 | 292 | // obj can have fields move, chat, fen, drawOffer and/or score + message |
0234201f | 293 | update: function(id, obj, cb) { |
dac39588 | 294 | db.parallelize(function() { |
3d55deea BA |
295 | let query = |
296 | "UPDATE Games " + | |
297 | "SET "; | |
3837d4f7 | 298 | let modifs = ""; |
633959bf | 299 | // NOTE: if drawOffer is set, we should check that it's player's turn |
dfeb96ea | 300 | // A bit overcomplicated. Let's trust the client on that for now... |
c292ebb2 | 301 | if (!!obj.drawOffer) |
633959bf | 302 | { |
c292ebb2 | 303 | if (obj.drawOffer == "n") //special "None" update |
633959bf BA |
304 | obj.drawOffer = ""; |
305 | modifs += "drawOffer = '" + obj.drawOffer + "',"; | |
306 | } | |
c292ebb2 BA |
307 | if (!!obj.rematchOffer) |
308 | { | |
309 | if (obj.rematchOffer == "n") //special "None" update | |
310 | obj.rematchOffer = ""; | |
311 | modifs += "rematchOffer = '" + obj.rematchOffer + "',"; | |
312 | } | |
aae89b49 | 313 | if (!!obj.fen) |
3837d4f7 | 314 | modifs += "fen = '" + obj.fen + "',"; |
aae89b49 | 315 | if (!!obj.score) |
3837d4f7 | 316 | modifs += "score = '" + obj.score + "',"; |
aae89b49 | 317 | if (!!obj.scoreMsg) |
dcd68c41 | 318 | modifs += "scoreMsg = '" + obj.scoreMsg + "',"; |
aae89b49 BA |
319 | if (!!obj.deletedBy) { |
320 | const myColor = obj.deletedBy == 'w' ? "White" : "Black"; | |
321 | modifs += "deletedBy" + myColor + " = true,"; | |
322 | } | |
3837d4f7 BA |
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 | } | |
23ecf008 | 329 | // NOTE: move, chat and delchat are mutually exclusive |
89ffc919 | 330 | if (!!obj.move) |
f41ce580 | 331 | { |
fb68b0c2 | 332 | // Security: only update moves if index is right |
f41ce580 | 333 | query = |
fb68b0c2 BA |
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 | }); | |
f41ce580 | 348 | } |
fb68b0c2 | 349 | else cb(null); |
aae89b49 | 350 | if (!!obj.chat) |
3837d4f7 | 351 | { |
dac39588 BA |
352 | query = |
353 | "INSERT INTO Chats (gid, msg, name, added) VALUES (" | |
41c80bb6 | 354 | + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")"; |
58e7b94e | 355 | db.run(query, obj.chat.msg); |
3837d4f7 | 356 | } |
db1f1f9a BA |
357 | else if (obj.delchat) |
358 | { | |
359 | query = | |
360 | "DELETE " + | |
361 | "FROM Chats " + | |
362 | "WHERE gid = " + id; | |
23ecf008 | 363 | db.run(query); |
db1f1f9a | 364 | } |
aae89b49 BA |
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 | } | |
3d55deea BA |
380 | }); |
381 | }, | |
382 | ||
0234201f BA |
383 | remove: function(id_s) { |
384 | const suffix = | |
385 | Array.isArray(id_s) | |
386 | ? " IN (" + id_s.join(",") + ")" | |
387 | : " = " + id_s; | |
dac39588 BA |
388 | db.parallelize(function() { |
389 | let query = | |
390 | "DELETE FROM Games " + | |
0234201f | 391 | "WHERE id " + suffix; |
dac39588 BA |
392 | db.run(query); |
393 | query = | |
394 | "DELETE FROM Moves " + | |
0234201f | 395 | "WHERE gid " + suffix; |
dac39588 BA |
396 | db.run(query); |
397 | query = | |
398 | "DELETE FROM Chats " + | |
0234201f | 399 | "WHERE gid " + suffix; |
dac39588 BA |
400 | db.run(query); |
401 | }); | |
402 | }, | |
d431028c | 403 | |
0234201f | 404 | cleanGamesDb: function() { |
d431028c BA |
405 | const tsNow = Date.now(); |
406 | // 86400000 = 24 hours in milliseconds | |
407 | const day = 86400000; | |
408 | db.serialize(function() { | |
409 | let query = | |
0f7011a2 | 410 | "SELECT id, created " + |
0234201f BA |
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); | |
d431028c BA |
442 | } |
443 | }); | |
0234201f | 444 | if (toRemove.length > 0) GameModel.remove(toRemove); |
d431028c BA |
445 | }); |
446 | }); | |
447 | }); | |
0234201f | 448 | } |
00f2759e | 449 | } |
ab4f4bf2 BA |
450 | |
451 | module.exports = GameModel; |