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 incrementCounter: function(vid
, cb
) {
51 db
.serialize(function() {
54 "SET total = total + 1 " +
60 create: function(vid
, fen
, randomness
, cadence
, players
, cb
) {
61 db
.serialize(function() {
63 "INSERT INTO Games " +
65 "vid, fenStart, fen, randomness, " +
71 vid
+ ",'" + fen
+ "','" + fen
+ "'," + randomness
+ "," +
72 players
[0].id
+ "," + players
[1].id
+ "," +
73 "'" + cadence
+ "'," + Date
.now() +
75 db
.run(query
, function(err
) {
76 cb(err
, { id: this.lastID
});
81 // TODO: some queries here could be async
82 getOne: function(id
, cb
) {
83 // NOTE: ignoring errors (shouldn't happen at this stage)
84 db
.serialize(function() {
87 "id, vid, fen, fenStart, cadence, created, " +
88 "white, black, randomness, score, scoreMsg, " +
89 "chatReadWhite, chatReadBlack, drawOffer, rematchOffer " +
92 db
.get(query
, (err
, gameInfo
) => {
94 cb(err
|| { errmsg: "Game not found" }, undefined);
100 "WHERE id IN (" + gameInfo
.white
+ "," + gameInfo
.black
+ ")";
101 db
.all(query
, (err2
, players
) => {
102 if (players
[0].id
== gameInfo
.black
) players
= players
.reverse();
103 // The original players' IDs info isn't required anymore
104 delete gameInfo
["white"];
105 delete gameInfo
["black"];
107 "SELECT squares, played, idx " +
110 db
.all(query
, (err3
, moves
) => {
112 "SELECT msg, name, added " +
115 db
.all(query
, (err4
, chats
) => {
116 const game
= Object
.assign(
132 // For display on Hall: no need for moves or chats
133 getObserved: function(uid
, cursor
, cb
) {
134 db
.serialize(function() {
136 "SELECT id, vid, cadence, created, score, white, black " +
138 "WHERE created < " + cursor
+ " ";
141 " AND white <> " + uid
+ " " +
142 " AND black <> " + uid
+ " ";
145 "ORDER BY created DESC " +
146 "LIMIT 20"; //TODO: 20 hard-coded...
147 db
.all(query
, (err
, games
) => {
148 // Query players names
151 if (!pids
[g
.white
]) pids
[g
.white
] = true;
152 if (!pids
[g
.black
]) pids
[g
.black
] = true;
154 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
156 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
168 { id: g
.white
, name: names
[g
.white
] },
169 { id: g
.black
, name: names
[g
.black
] }
180 // For display on MyGames: registered user only
181 getRunning: function(uid
, cb
) {
182 db
.serialize(function() {
184 "SELECT id, vid, cadence, created, white, black " +
186 "WHERE score = '*' AND (white = " + uid
+ " OR black = " + uid
+ ")";
187 db
.all(query
, (err
, games
) => {
188 // Get movesCount (could be done in // with next query)
190 "SELECT gid, COUNT(*) AS nbMoves " +
192 "WHERE gid IN " + "(" + games
.map(g
=> g
.id
).join(",") + ") " +
194 db
.all(query
, (err
, mstats
) => {
195 let movesCounts
= {};
196 mstats
.forEach(ms
=> { movesCounts
[ms
.gid
] = ms
.nbMoves
; });
197 // Query player names
200 if (!pids
[g
.white
]) pids
[g
.white
] = true;
201 if (!pids
[g
.black
]) pids
[g
.black
] = true;
203 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
205 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
216 movesCount: movesCounts
[g
.id
] || 0,
218 { id: g
.white
, name: names
[g
.white
] },
219 { id: g
.black
, name: names
[g
.black
] }
231 // These games could be deleted on some side. movesCount not required
232 getCompleted: function(uid
, cursor
, cb
) {
233 db
.serialize(function() {
235 "SELECT id, vid, cadence, created, score, scoreMsg, " +
236 "white, black, deletedByWhite, deletedByBlack " +
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 !obj
.fen
|| !!(obj
.fen
.match(/^[a-zA-Z0-9,.:{}\[\]" /-]*$/))
315 !obj
.score
|| !!(obj
.score
.match(/^[012?*\/-]+$/))
317 !obj
.chatRead
|| ['w','b'].includes(obj
.chatRead
)
319 !obj
.scoreMsg
|| !!(obj
.scoreMsg
.match(/^[a
-zA
-Z
]+$/))
321 !obj
.chat
|| UserModel
.checkNameEmail({name: obj
.chat
.name
})
326 // obj can have fields move, chat, fen, drawOffer and/or score + message
327 update: function(id
, obj
, cb
) {
328 db
.parallelize(function() {
333 // NOTE: if drawOffer is set, we should check that it's player's turn
334 // A bit overcomplicated. Let's trust the client on that for now...
335 if (!!obj
.drawOffer
) {
336 if (obj
.drawOffer
== "n")
337 // Special "None" update
339 modifs
+= "drawOffer = '" + obj
.drawOffer
+ "',";
341 if (!!obj
.rematchOffer
) {
342 if (obj
.rematchOffer
== "n")
343 // Special "None" update
344 obj
.rematchOffer
= "";
345 modifs
+= "rematchOffer = '" + obj
.rematchOffer
+ "',";
347 if (!!obj
.fen
) modifs
+= "fen = '" + obj
.fen
+ "',";
348 if (!!obj
.deletedBy
) {
349 const myColor
= obj
.deletedBy
== 'w' ? "White" : "Black";
350 modifs
+= "deletedBy" + myColor
+ " = true,";
352 if (!!obj
.chatRead
) {
353 const myColor
= obj
.chatRead
== 'w' ? "White" : "Black";
354 modifs
+= "chatRead" + myColor
+ " = " + Date
.now() + ",";
357 modifs
+= "score = '" + obj
.score
+ "'," +
358 "scoreMsg = '" + obj
.scoreMsg
+ "',";
360 const finishAndSendQuery
= () => {
361 modifs
= modifs
.slice(0, -1); //remove last comma
362 if (modifs
.length
> 0) {
363 updateQuery
+= modifs
+ " WHERE id = " + id
;
368 if (!!obj
.move || (!!obj
.score
&& obj
.scoreMsg
== "Time")) {
369 // Security: only update moves if index is right,
370 // and score with scoreMsg "Time" if really lost on time.
372 "SELECT MAX(idx) AS maxIdx, MAX(played) AS lastPlayed " +
375 db
.get(query
, (err
, ret
) => {
377 if (!ret
.maxIdx
|| ret
.maxIdx
+ 1 == obj
.move.idx
) {
379 "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
380 "(" + id
+ ",?," + Date
.now() + "," + obj
.move.idx
+ ")";
381 db
.run(query
, JSON
.stringify(obj
.move.squares
));
382 finishAndSendQuery();
384 else cb({ errmsg: "Wrong move index" });
387 if (ret
.maxIdx
< 2) cb({ errmsg: "Time not over" });
389 // We also need the game cadence
394 db
.get(query
, (err2
, ret2
) => {
395 const daysTc
= parseInt(ret2
.cadence
.match(/^[0-9]+/)[0]);
396 if (Date
.now() - ret
.lastPlayed
> daysTc
* 24 * 3600 * 1000)
397 finishAndSendQuery();
398 else cb({ errmsg: "Time not over" });
404 else finishAndSendQuery();
405 // NOTE: chat and delchat are mutually exclusive
408 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
409 + id
+ ",?,'" + obj
.chat
.name
+ "'," + Date
.now() + ")";
410 db
.run(query
, obj
.chat
.msg
);
412 else if (obj
.delchat
) {
419 if (!!obj
.deletedBy
) {
420 // Did my opponent delete it too?
423 (obj
.deletedBy
== 'w' ? "Black" : "White") +
426 "SELECT " + selection
+ " " +
429 db
.get(query
, (err
,ret
) => {
430 // If yes: just remove game
431 if (!!ret
.deletedByOpp
) GameModel
.remove(id
);
437 remove: function(id_s
) {
440 ? " IN (" + id_s
.join(",") + ")"
442 db
.parallelize(function() {
444 "DELETE FROM Games " +
445 "WHERE id " + suffix
;
448 "DELETE FROM Moves " +
449 "WHERE gid " + suffix
;
452 "DELETE FROM Chats " +
453 "WHERE gid " + suffix
;
458 cleanGamesDb: function() {
459 const tsNow
= Date
.now();
460 // 86400000 = 24 hours in milliseconds
461 const day
= 86400000;
462 db
.serialize(function() {
464 "SELECT id, created, cadence, score " +
466 db
.all(query
, (err
, games
) => {
468 "SELECT gid, count(*) AS nbMoves, MAX(played) AS lastMaj " +
471 db
.all(query
, (err2
, mstats
) => {
472 // Reorganize moves data to avoid too many array lookups:
473 let movesGroups
= {};
474 mstats
.forEach(ms
=> {
475 movesGroups
[ms
.gid
] = {
480 // Remove games still not really started,
481 // with no action in the last 2 weeks, or result != '*':
483 let lostOnTime
= [ [], [] ];
487 !movesGroups
[g
.id
] &&
488 (g
.score
!= '*' || tsNow
- g
.created
> 14*day
)
492 !!movesGroups
[g
.id
] &&
493 movesGroups
[g
.id
].nbMoves
== 1 &&
494 (g
.score
!= '*' || tsNow
- movesGroups
[g
.id
].lastMaj
> 14*day
)
499 // Set score if lost on time and >= 2 moves:
502 !!movesGroups
[g
.id
] &&
503 movesGroups
[g
.id
].nbMoves
>= 2 &&
504 tsNow
- movesGroups
[g
.id
].lastMaj
>
505 // cadence in days * nb seconds per day:
506 parseInt(g
.cadence
.slice(0, -1), 10) * day
508 lostOnTime
[movesGroups
[g
.id
].nbMoves
% 2].push(g
.id
);
511 if (toRemove
.length
> 0) GameModel
.remove(toRemove
);
512 if (lostOnTime
.some(l
=> l
.length
> 0)) {
513 db
.parallelize(function() {
514 for (let i
of [0, 1]) {
515 if (lostOnTime
[i
].length
> 0) {
516 const score
= (i
== 0 ? "0-1" : "1-0");
519 "SET score = '" + score
+ "', scoreMsg = 'Time' " +
520 "WHERE id IN (" + lostOnTime
[i
].join(',') + ")";
533 module
.exports
= GameModel
;