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 "g.id, g.fen, g.fenStart, g.cadence, g.created, " +
88 "g.white, g.black, g.score, g.scoreMsg, " +
89 "g.chatReadWhite, g.chatReadBlack, " +
90 "g.drawOffer, g.rematchOffer, v.name AS vname " +
95 db
.get(query
, (err
, gameInfo
) => {
97 cb(err
|| { errmsg: "Game not found" }, undefined);
103 "WHERE id IN (" + gameInfo
.white
+ "," + gameInfo
.black
+ ")";
104 db
.all(query
, (err2
, players
) => {
105 if (players
[0].id
== gameInfo
.black
) players
= players
.reverse();
106 // The original players' IDs info isn't required anymore
107 delete gameInfo
["white"];
108 delete gameInfo
["black"];
110 "SELECT squares, played, idx " +
113 db
.all(query
, (err3
, moves
) => {
115 "SELECT msg, name, added " +
118 db
.all(query
, (err4
, chats
) => {
119 const game
= Object
.assign(
136 // For display on Hall: no need for moves or chats
137 getObserved: function(uid
, cursor
, cb
) {
138 db
.serialize(function() {
140 "SELECT id, vid, cadence, created, score, white, black " +
142 "WHERE created < " + cursor
+ " ";
145 " AND white <> " + uid
+ " " +
146 " AND black <> " + uid
+ " ";
149 "ORDER BY created DESC " +
150 "LIMIT 20"; //TODO: 20 hard-coded...
151 db
.all(query
, (err
, games
) => {
152 // Query players names
155 if (!pids
[g
.white
]) pids
[g
.white
] = true;
156 if (!pids
[g
.black
]) pids
[g
.black
] = true;
158 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
160 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
172 { id: g
.white
, name: names
[g
.white
] },
173 { id: g
.black
, name: names
[g
.black
] }
184 // For display on MyGames: registered user only
185 getRunning: function(uid
, cb
) {
186 db
.serialize(function() {
188 "SELECT g.id, g.cadence, g.created, " +
189 "g.white, g.black, v.name AS vname " +
192 " ON g.vid = v.id " +
193 "WHERE score = '*' AND (white = " + uid
+ " OR black = " + uid
+ ")";
194 db
.all(query
, (err
, games
) => {
195 // Get movesCount (could be done in // with next query)
197 "SELECT gid, COUNT(*) AS nbMoves " +
199 "WHERE gid IN " + "(" + games
.map(g
=> g
.id
).join(",") + ") " +
201 db
.all(query
, (err
, mstats
) => {
202 let movesCounts
= {};
203 mstats
.forEach(ms
=> { movesCounts
[ms
.gid
] = ms
.nbMoves
; });
204 // Query player names
207 if (!pids
[g
.white
]) pids
[g
.white
] = true;
208 if (!pids
[g
.black
]) pids
[g
.black
] = true;
210 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
212 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
223 movesCount: movesCounts
[g
.id
] || 0,
225 { id: g
.white
, name: names
[g
.white
] },
226 { id: g
.black
, name: names
[g
.black
] }
238 // These games could be deleted on some side. movesCount not required
239 getCompleted: function(uid
, cursor
, cb
) {
240 db
.serialize(function() {
242 "SELECT g.id, g.cadence, g.created, g.score, g.scoreMsg, " +
243 "g.white, g.black, g.deletedByWhite, g.deletedByBlack, " +
247 " ON g.vid = v.id " +
249 " score <> '*' AND" +
250 " created < " + cursor
+ " AND" +
253 " white = " + uid
+ " AND" +
254 " (deletedByWhite IS NULL OR NOT deletedByWhite)" +
258 " black = " + uid
+ " AND" +
259 " (deletedByBlack IS NULL OR NOT deletedByBlack)" +
263 "ORDER BY created DESC " +
265 db
.all(query
, (err
, games
) => {
266 // Query player names
269 if (!pids
[g
.white
]) pids
[g
.white
] = true;
270 if (!pids
[g
.black
]) pids
[g
.black
] = true;
272 UserModel
.getByIds(Object
.keys(pids
), (err2
, users
) => {
274 users
.forEach(u
=> { names
[u
.id
] = u
.name
; });
285 scoreMsg: g
.scoreMsg
,
287 { id: g
.white
, name: names
[g
.white
] },
288 { id: g
.black
, name: names
[g
.black
] }
290 deletedByWhite: g
.deletedByWhite
,
291 deletedByBlack: g
.deletedByBlack
301 getPlayers: function(id
, cb
) {
302 db
.serialize(function() {
304 "SELECT white, black " +
307 db
.get(query
, (err
, players
) => {
308 return cb(err
, players
);
313 checkGameUpdate: function(obj
) {
314 // Check all that is possible (required) in obj:
317 !obj
.move || !!(obj
.move.idx
.toString().match(/^[0-9]+$/))
319 !obj
.drawOffer
|| !!(obj
.drawOffer
.match(/^[wbtn]$/))
321 !obj
.rematchOffer
|| !!(obj
.rematchOffer
.match(/^[wbn]$/))
323 // TODO: check if commas are still used (probably not)
324 !obj
.fen
|| !!(obj
.fen
.match(/^[a-zA-Z0-9,. /-]*$/))
326 !obj
.score
|| !!(obj
.score
.match(/^[012?*\/-]+$/))
328 !obj
.chatRead
|| ['w','b'].includes(obj
.chatRead
)
330 !obj
.scoreMsg
|| !!(obj
.scoreMsg
.match(/^[a
-zA
-Z
]+$/))
332 !obj
.chat
|| UserModel
.checkNameEmail({name: obj
.chat
.name
})
337 // obj can have fields move, chat, fen, drawOffer and/or score + message
338 update: function(id
, obj
, cb
) {
339 db
.parallelize(function() {
344 // NOTE: if drawOffer is set, we should check that it's player's turn
345 // A bit overcomplicated. Let's trust the client on that for now...
346 if (!!obj
.drawOffer
) {
347 if (obj
.drawOffer
== "n")
348 // Special "None" update
350 modifs
+= "drawOffer = '" + obj
.drawOffer
+ "',";
352 if (!!obj
.rematchOffer
) {
353 if (obj
.rematchOffer
== "n")
354 // Special "None" update
355 obj
.rematchOffer
= "";
356 modifs
+= "rematchOffer = '" + obj
.rematchOffer
+ "',";
358 if (!!obj
.fen
) modifs
+= "fen = '" + obj
.fen
+ "',";
359 if (!!obj
.deletedBy
) {
360 const myColor
= obj
.deletedBy
== 'w' ? "White" : "Black";
361 modifs
+= "deletedBy" + myColor
+ " = true,";
363 if (!!obj
.chatRead
) {
364 const myColor
= obj
.chatRead
== 'w' ? "White" : "Black";
365 modifs
+= "chatRead" + myColor
+ " = " + Date
.now() + ",";
368 modifs
+= "score = '" + obj
.score
+ "'," +
369 "scoreMsg = '" + obj
.scoreMsg
+ "',";
371 const finishAndSendQuery
= () => {
372 modifs
= modifs
.slice(0, -1); //remove last comma
373 if (modifs
.length
> 0) {
374 updateQuery
+= modifs
+ " WHERE id = " + id
;
379 if (!!obj
.move || (!!obj
.score
&& obj
.scoreMsg
== "Time")) {
380 // Security: only update moves if index is right,
381 // and score with scoreMsg "Time" if really lost on time.
383 "SELECT MAX(idx) AS maxIdx, MAX(played) AS lastPlayed " +
386 db
.get(query
, (err
, ret
) => {
388 if (!ret
.maxIdx
|| ret
.maxIdx
+ 1 == obj
.move.idx
) {
390 "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
391 "(" + id
+ ",?," + Date
.now() + "," + obj
.move.idx
+ ")";
392 db
.run(query
, JSON
.stringify(obj
.move.squares
));
393 finishAndSendQuery();
395 else cb({ errmsg: "Wrong move index" });
398 if (ret
.maxIdx
< 2) cb({ errmsg: "Time not over" });
400 // We also need the game cadence
405 db
.get(query
, (err2
, ret2
) => {
406 const daysTc
= parseInt(ret2
.cadence
.match(/^[0-9]+/)[0]);
407 if (Date
.now() - ret
.lastPlayed
> daysTc
* 24 * 3600 * 1000)
408 finishAndSendQuery();
409 else cb({ errmsg: "Time not over" });
415 else finishAndSendQuery();
416 // NOTE: chat and delchat are mutually exclusive
419 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
420 + id
+ ",?,'" + obj
.chat
.name
+ "'," + Date
.now() + ")";
421 db
.run(query
, obj
.chat
.msg
);
423 else if (obj
.delchat
) {
430 if (!!obj
.deletedBy
) {
431 // Did my opponent delete it too?
434 (obj
.deletedBy
== 'w' ? "Black" : "White") +
437 "SELECT " + selection
+ " " +
440 db
.get(query
, (err
,ret
) => {
441 // If yes: just remove game
442 if (!!ret
.deletedByOpp
) GameModel
.remove(id
);
448 remove: function(id_s
) {
451 ? " IN (" + id_s
.join(",") + ")"
453 db
.parallelize(function() {
455 "DELETE FROM Games " +
456 "WHERE id " + suffix
;
459 "DELETE FROM Moves " +
460 "WHERE gid " + suffix
;
463 "DELETE FROM Chats " +
464 "WHERE gid " + suffix
;
469 cleanGamesDb: function() {
470 const tsNow
= Date
.now();
471 // 86400000 = 24 hours in milliseconds
472 const day
= 86400000;
473 db
.serialize(function() {
475 "SELECT id, created " +
477 db
.all(query
, (err
, games
) => {
479 "SELECT gid, count(*) AS nbMoves, MAX(played) AS lastMaj " +
482 db
.all(query
, (err2
, mstats
) => {
483 // Reorganize moves data to avoid too many array lookups:
484 let movesGroups
= {};
485 mstats
.forEach(ms
=> {
486 movesGroups
[ms
.gid
] = {
491 // Remove games still not really started,
492 // with no action in the last 2 weeks:
497 !movesGroups
[g
.id
] &&
498 tsNow
- g
.created
> 14*day
502 !!movesGroups
[g
.id
] &&
503 movesGroups
[g
.id
].nbMoves
== 1 &&
504 tsNow
- movesGroups
[g
.id
].lastMaj
> 14*day
510 if (toRemove
.length
> 0) GameModel
.remove(toRemove
);
518 module
.exports
= GameModel
;