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)
11 * score: varchar (result)
12 * scoreMsg: varchar ("Time", "Mutual agreement"...)
14 * drawOffer: char ('w','b' or '' for none)
16 * Structure table Players:
21 * Structure table Moves:
23 * squares: varchar (description)
27 * Structure table Chats:
36 checkGameInfo: function(g
) {
38 g
.vid
.toString().match(/^[0-9]+$/) &&
39 g
.cadence
.match(/^[0-9dhms
+]+$/) &&
40 g
.fen
.match(/^[a-zA-Z0-9, /-]*$/) &&
41 g
.players
.length
== 2 &&
42 g
.players
.every(p
=> p
.uid
.toString().match(/^[0-9]+$/))
46 create: function(vid
, fen
, cadence
, players
, cb
)
48 db
.serialize(function() {
50 "INSERT INTO Games " +
51 "(vid, fenStart, fen, cadence, created) " +
53 "(" + vid
+ ",'" + fen
+ "','" + fen
+ "','" + cadence
+ "'," + Date
.now() + ")";
54 db
.run(query
, function(err
) {
59 players
.forEach((p
,idx
) => {
60 const color
= (idx
==0 ? "w" : "b");
62 "INSERT INTO Players VALUES " +
63 "(" + this.lastID
+ "," + p
.uid
+ ",'" + color
+ "')";
66 cb(null, {gid: this.lastID
});
72 // TODO: some queries here could be async
73 getOne: function(id
, cb
)
75 // NOTE: ignoring errors (shouldn't happen at this stage)
76 db
.serialize(function() {
78 "SELECT g.id, g.vid, g.fen, g.fenStart, g.cadence, g.created, g.score, " +
79 "g.scoreMsg, g.drawOffer, g.rematchOffer, v.name AS vname " +
84 db
.get(query
, (err
, gameInfo
) => {
86 "SELECT p.uid, p.color, u.name " +
90 "WHERE p.gid = " + id
;
91 db
.all(query
, (err2
, players
) => {
93 "SELECT squares, played, idx " +
96 db
.all(query
, (err3
, moves
) => {
98 "SELECT msg, name, added " +
101 db
.all(query
, (err4
, chats
) => {
102 const game
= Object
.assign(
119 // For display on MyGames or Hall: no need for moves or chats
120 getByUser: function(uid
, excluded
, cb
)
122 // Some fields are not required when showing a games list:
123 const getOneLight
= (id
, cb2
) => {
125 "SELECT g.id, g.vid, g.fen, g.cadence, g.created, g.score, " +
126 "g.scoreMsg, g.deletedByWhite, g.deletedByBlack, v.name AS vname " +
129 " ON g.vid = v.id " +
130 "WHERE g.id = " + id
;
131 db
.get(query
, (err
, gameInfo
) => {
133 "SELECT p.uid, p.color, u.name " +
136 " ON p.uid = u.id " +
137 "WHERE p.gid = " + id
;
138 db
.all(query
, (err2
, players
) => {
140 "SELECT COUNT(*) AS nbMoves " +
143 db
.get(query
, (err
,ret
) => {
144 const game
= Object
.assign(
149 movesCount: ret
.nbMoves
157 db
.serialize(function() {
160 // Special case anonymous user: show all games
162 "SELECT id AS gid " +
171 "HAVING COUNT(uid = " + uid
+ " OR NULL) " +
172 (excluded
? " = 0" : " > 0");
174 db
.all(query
, (err
,gameIds
) => {
175 if (err
|| gameIds
.length
== 0) cb(err
, []);
179 for (let i
=0; i
<gameIds
.length
; i
++) {
180 getOneLight(gameIds
[i
]["gid"], (game
) => {
181 gameArray
.push(game
);
182 gCounter
++; //TODO: let's hope this is atomic?!
183 // Call callback function only when gameArray is complete:
184 if (gCounter
== gameIds
.length
)
193 getPlayers: function(id
, cb
)
195 db
.serialize(function() {
200 db
.all(query
, (err
,players
) => {
201 return cb(err
, players
);
206 checkGameUpdate: function(obj
)
208 // Check all that is possible (required) in obj:
212 !!(obj
.move.played
.toString().match(/^[0-9]+$/)) &&
213 !!(obj
.move.idx
.toString().match(/^[0-9]+$/))
216 !obj
.drawOffer
|| !!(obj
.drawOffer
.match(/^[wbtn]$/))
218 !obj
.rematchOffer
|| !!(obj
.rematchOffer
.match(/^[wbn]$/))
220 !obj
.fen
|| !!(obj
.fen
.match(/^[a-zA-Z0-9, /-]*$/))
222 !obj
.score
|| !!(obj
.score
.match(/^[012?*\/-]+$/))
224 !obj
.scoreMsg
|| !!(obj
.scoreMsg
.match(/^[a
-zA
-Z
]+$/))
226 !obj
.chat
|| UserModel
.checkNameEmail({name: obj
.chat
.name
})
231 // obj can have fields move, chat, fen, drawOffer and/or score + message
232 update: function(id
, obj
, cb
)
234 db
.parallelize(function() {
239 // NOTE: if drawOffer is set, we should check that it's player's turn
240 // A bit overcomplicated. Let's trust the client on that for now...
243 if (obj
.drawOffer
== "n") //special "None" update
245 modifs
+= "drawOffer = '" + obj
.drawOffer
+ "',";
247 if (!!obj
.rematchOffer
)
249 if (obj
.rematchOffer
== "n") //special "None" update
250 obj
.rematchOffer
= "";
251 modifs
+= "rematchOffer = '" + obj
.rematchOffer
+ "',";
254 modifs
+= "fen = '" + obj
.fen
+ "',";
256 modifs
+= "score = '" + obj
.score
+ "',";
258 modifs
+= "scoreMsg = '" + obj
.scoreMsg
+ "',";
259 if (!!obj
.deletedBy
) {
260 const myColor
= obj
.deletedBy
== 'w' ? "White" : "Black";
261 modifs
+= "deletedBy" + myColor
+ " = true,";
263 modifs
= modifs
.slice(0,-1); //remove last comma
264 if (modifs
.length
> 0)
266 query
+= modifs
+ " WHERE id = " + id
;
269 // NOTE: move, chat and delchat are mutually exclusive
272 // Security: only update moves if index is right
274 "SELECT MAX(idx) AS maxIdx " +
277 db
.get(query
, (err
,ret
) => {
279 if (!ret
.maxIdx
|| ret
.maxIdx
+ 1 == m
.idx
) {
281 "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
282 "(" + id
+ ",?," + m
.played
+ "," + m
.idx
+ ")";
283 db
.run(query
, JSON
.stringify(m
.squares
));
286 else cb({errmsg:"Wrong move index"});
293 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
294 + id
+ ",?,'" + obj
.chat
.name
+ "'," + Date
.now() + ")";
295 db
.run(query
, obj
.chat
.msg
);
297 else if (obj
.delchat
)
305 if (!!obj
.deletedBy
) {
306 // Did my opponent delete it too?
309 (obj
.deletedBy
== 'w' ? "Black" : "White") +
312 "SELECT " + selection
+ " " +
315 db
.get(query
, (err
,ret
) => {
316 // If yes: just remove game
317 if (!!ret
.deletedByOpp
) GameModel
.remove(id
);
325 db
.parallelize(function() {
327 "DELETE FROM Games " +
331 "DELETE FROM Players " +
335 "DELETE FROM Moves " +
339 "DELETE FROM Chats " +
345 cleanGamesDb: function()
347 const tsNow
= Date
.now();
348 // 86400000 = 24 hours in milliseconds
349 const day
= 86400000;
350 db
.serialize(function() {
352 "SELECT id, created " +
354 db
.all(query
, (err
,games
) => {
357 "SELECT count(*) as nbMoves, max(played) AS lastMaj " +
359 "WHERE gid = " + g
.id
;
360 db
.get(query
, (err2
,mstats
) => {
361 // Remove games still not really started,
362 // with no action in the last 3 months:
363 if ((mstats
.nbMoves
== 0 && tsNow
- g
.created
> 91*day
) ||
364 (mstats
.nbMoves
== 1 && tsNow
- mstats
.lastMaj
> 91*day
))
366 GameModel
.remove(g
.id
);
375 module
.exports
= GameModel
;