1 const db
= require("../utils/database");
2 const UserModel
= require("./User");
5 * Structure table Games:
7 * vid: integer (variant id)
8 * fenStart: varchar (initial position)
9 * fen: varchar (current position)
13 * score: varchar (result)
14 * scoreMsg: varchar ("Time", "Mutual agreement"...)
16 * drawOffer: char ('w','b' or '' for none)
17 * rematchOffer: char (similar to drawOffer)
19 * deletedByWhite: boolean
20 * deletedByBlack: boolean
21 * chatReadWhite: datetime
22 * chatReadBlack: datetime
24 * Structure table Moves:
26 * squares: varchar (description)
30 * Structure table Chats:
39 checkGameInfo: function(g
) {
41 g
.vid
.toString().match(/^[0-9]+$/) &&
42 g
.cadence
.match(/^[0-9dhms
+]+$/) &&
43 g
.randomness
.toString().match(/^[0-2]$/) &&
44 g
.fen
.match(/^[a-zA-Z0-9, /-]*$/) &&
45 g
.players
.length
== 2 &&
46 g
.players
.every(p
=> p
.id
.toString().match(/^[0-9]+$/))
50 create: function(vid
, fen
, randomness
, cadence
, players
, cb
) {
51 db
.serialize(function() {
53 "INSERT INTO Games " +
55 "vid, fenStart, fen, randomness, " +
61 vid
+ ",'" + fen
+ "','" + fen
+ "'," + randomness
+ "," +
62 players
[0].id
+ "," + players
[1].id
+ "," +
63 "'" + cadence
+ "'," + Date
.now() +
65 db
.run(query
, function(err
) {
66 cb(err
, { id: this.lastID
});
71 // TODO: some queries here could be async
72 getOne: function(id
, cb
) {
73 // NOTE: ignoring errors (shouldn't happen at this stage)
74 db
.serialize(function() {
77 "g.id, g.fen, g.fenStart, g.cadence, g.created, " +
78 "g.white, g.black, g.score, g.scoreMsg, " +
79 "g.chatReadWhite, g.chatReadBlack, " +
80 "g.drawOffer, g.rematchOffer, v.name AS vname " +
85 db
.get(query
, (err
, gameInfo
) => {
87 cb(err
|| { errmsg: "Game not found" }, undefined);
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"];
100 "SELECT squares, played, idx " +
103 db
.all(query
, (err3
, moves
) => {
105 "SELECT msg, name, added " +
108 db
.all(query
, (err4
, chats
) => {
109 const game
= Object
.assign(
126 // For display on Hall: no need for moves or chats
127 getObserved: function(uid
, cursor
, cb
) {
128 db
.serialize(function() {
130 "SELECT id, vid, cadence, created, score, white, black " +
132 "WHERE created < " + cursor
+ " ";
135 " AND white <> " + uid
+ " " +
136 " AND black <> " + uid
+ " ";
139 "ORDER BY created DESC " +
140 "LIMIT 20"; //TODO: 20 hard-coded...
141 db
.all(query
, (err
, games
) => {
142 // Query players names
145 if (!pids
[g
.white
]) pids
[g
.white
] = true;
146 if (!pids
[g
.black
]) pids
[g
.black
] = true;
148 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
150 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
162 { id: g
.white
, name: names
[g
.white
] },
163 { id: g
.black
, name: names
[g
.black
] }
174 // For display on MyGames: registered user only
175 getRunning: function(uid
, cb
) {
176 db
.serialize(function() {
178 "SELECT g.id, g.cadence, g.created, " +
179 "g.white, g.black, v.name AS vname " +
182 " ON g.vid = v.id " +
183 "WHERE score = '*' AND (white = " + uid
+ " OR black = " + uid
+ ")";
184 db
.all(query
, (err
, games
) => {
185 // Get movesCount (could be done in // with next query)
187 "SELECT gid, COUNT(*) AS nbMoves " +
189 "WHERE gid IN " + "(" + games
.map(g
=> g
.id
).join(",") + ") " +
191 db
.all(query
, (err
, mstats
) => {
192 let movesCounts
= {};
193 mstats
.forEach(ms
=> { movesCounts
[ms
.gid
] = ms
.nbMoves
; });
194 // Query player names
197 if (!pids
[g
.white
]) pids
[g
.white
] = true;
198 if (!pids
[g
.black
]) pids
[g
.black
] = true;
200 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
202 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
213 movesCount: movesCounts
[g
.id
] || 0,
215 { id: g
.white
, name: names
[g
.white
] },
216 { id: g
.black
, name: names
[g
.black
] }
228 // These games could be deleted on some side. movesCount not required
229 getCompleted: function(uid
, cursor
, cb
) {
230 db
.serialize(function() {
232 "SELECT g.id, g.cadence, g.created, g.score, g.scoreMsg, " +
233 "g.white, g.black, g.deletedByWhite, g.deletedByBlack, " +
237 " ON g.vid = v.id " +
239 " score <> '*' AND" +
240 " created < " + cursor
+ " AND" +
243 " white = " + uid
+ " AND" +
244 " (deletedByWhite IS NULL OR NOT deletedByWhite)" +
248 " black = " + uid
+ " AND" +
249 " (deletedByBlack IS NULL OR NOT deletedByBlack)" +
253 "ORDER BY created DESC " +
255 db
.all(query
, (err
, games
) => {
256 // Query player names
259 if (!pids
[g
.white
]) pids
[g
.white
] = true;
260 if (!pids
[g
.black
]) pids
[g
.black
] = true;
262 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
264 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
275 scoreMsg: g
.scoreMsg
,
277 { id: g
.white
, name: names
[g
.white
] },
278 { id: g
.black
, name: names
[g
.black
] }
280 deletedByWhite: g
.deletedByWhite
,
281 deletedByBlack: g
.deletedByBlack
291 getPlayers: function(id
, cb
) {
292 db
.serialize(function() {
294 "SELECT white, black " +
297 db
.get(query
, (err
, players
) => {
298 return cb(err
, players
);
303 checkGameUpdate: function(obj
) {
304 // Check all that is possible (required) in obj:
307 !obj
.move || !!(obj
.move.idx
.toString().match(/^[0-9]+$/))
309 !obj
.drawOffer
|| !!(obj
.drawOffer
.match(/^[wbtn]$/))
311 !obj
.rematchOffer
|| !!(obj
.rematchOffer
.match(/^[wbn]$/))
313 // TODO: check if commas are still used (probably not)
314 !obj
.fen
|| !!(obj
.fen
.match(/^[a-zA-Z0-9,. /-]*$/))
316 !obj
.score
|| !!(obj
.score
.match(/^[012?*\/-]+$/))
318 !obj
.chatRead
|| ['w','b'].includes(obj
.chatRead
)
320 !obj
.scoreMsg
|| !!(obj
.scoreMsg
.match(/^[a
-zA
-Z
]+$/))
322 !obj
.chat
|| UserModel
.checkNameEmail({name: obj
.chat
.name
})
327 // obj can have fields move, chat, fen, drawOffer and/or score + message
328 update: function(id
, obj
, cb
) {
329 db
.parallelize(function() {
334 // NOTE: if drawOffer is set, we should check that it's player's turn
335 // A bit overcomplicated. Let's trust the client on that for now...
336 if (!!obj
.drawOffer
) {
337 if (obj
.drawOffer
== "n")
338 // Special "None" update
340 modifs
+= "drawOffer = '" + obj
.drawOffer
+ "',";
342 if (!!obj
.rematchOffer
) {
343 if (obj
.rematchOffer
== "n")
344 // Special "None" update
345 obj
.rematchOffer
= "";
346 modifs
+= "rematchOffer = '" + obj
.rematchOffer
+ "',";
348 if (!!obj
.fen
) modifs
+= "fen = '" + obj
.fen
+ "',";
349 if (!!obj
.deletedBy
) {
350 const myColor
= obj
.deletedBy
== 'w' ? "White" : "Black";
351 modifs
+= "deletedBy" + myColor
+ " = true,";
353 if (!!obj
.chatRead
) {
354 const myColor
= obj
.chatRead
== 'w' ? "White" : "Black";
355 modifs
+= "chatRead" + myColor
+ " = " + Date
.now() + ",";
358 modifs
+= "score = '" + obj
.score
+ "'," +
359 "scoreMsg = '" + obj
.scoreMsg
+ "',";
361 const finishAndSendQuery
= () => {
362 modifs
= modifs
.slice(0, -1); //remove last comma
363 if (modifs
.length
> 0) {
364 updateQuery
+= modifs
+ " WHERE id = " + id
;
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.
373 "SELECT MAX(idx) AS maxIdx, MAX(played) AS lastPlayed " +
376 db
.get(query
, (err
, ret
) => {
378 if (!ret
.maxIdx
|| ret
.maxIdx
+ 1 == obj
.move.idx
) {
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();
385 else cb({ errmsg: "Wrong move index" });
388 if (ret
.maxIdx
< 2) cb({ errmsg: "Time not over" });
390 // We also need the game cadence
395 db
.get(query
, (err2
, ret2
) => {
396 const daysTc
= parseInt(ret2
.cadence
.match(/^[0-9]+/)[0]);
397 if (Date
.now() - ret
.lastPlayed
> daysTc
* 24 * 3600 * 1000)
398 finishAndSendQuery();
399 else cb({ errmsg: "Time not over" });
405 else finishAndSendQuery();
406 // NOTE: chat and delchat are mutually exclusive
409 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
410 + id
+ ",?,'" + obj
.chat
.name
+ "'," + Date
.now() + ")";
411 db
.run(query
, obj
.chat
.msg
);
413 else if (obj
.delchat
) {
420 if (!!obj
.deletedBy
) {
421 // Did my opponent delete it too?
424 (obj
.deletedBy
== 'w' ? "Black" : "White") +
427 "SELECT " + selection
+ " " +
430 db
.get(query
, (err
,ret
) => {
431 // If yes: just remove game
432 if (!!ret
.deletedByOpp
) GameModel
.remove(id
);
438 remove: function(id_s
) {
441 ? " IN (" + id_s
.join(",") + ")"
443 db
.parallelize(function() {
445 "DELETE FROM Games " +
446 "WHERE id " + suffix
;
449 "DELETE FROM Moves " +
450 "WHERE gid " + suffix
;
453 "DELETE FROM Chats " +
454 "WHERE gid " + suffix
;
459 cleanGamesDb: function() {
460 const tsNow
= Date
.now();
461 // 86400000 = 24 hours in milliseconds
462 const day
= 86400000;
463 db
.serialize(function() {
465 "SELECT id, created " +
467 db
.all(query
, (err
, games
) => {
469 "SELECT gid, count(*) AS nbMoves, MAX(played) AS lastMaj " +
472 db
.all(query
, (err2
, mstats
) => {
473 // Reorganize moves data to avoid too many array lookups:
474 let movesGroups
= {};
475 mstats
.forEach(ms
=> {
476 movesGroups
[ms
.gid
] = {
481 // Remove games still not really started,
482 // with no action in the last 2 weeks:
487 !movesGroups
[g
.id
] &&
488 tsNow
- g
.created
> 14*day
492 !!movesGroups
[g
.id
] &&
493 movesGroups
[g
.id
].nbMoves
== 1 &&
494 tsNow
- movesGroups
[g
.id
].lastMaj
> 14*day
500 if (toRemove
.length
> 0) GameModel
.remove(toRemove
);
507 module
.exports
= GameModel
;