1 var 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
) {
37 if (!g
.vid
.toString().match(/^[0-9]+$/))
38 return "Wrong variant ID";
39 if (!g
.vname
.match(/^[a-zA-Z0-9]+$/))
40 return "Wrong variant name";
41 if (!g
.cadence
.match(/^[0-9dhms
+]+$/))
42 return "Wrong characters in time control";
43 if (!g
.fen
.match(/^[a-zA-Z0-9, /-]*$/))
44 return "Bad FEN string";
45 if (g
.players
.length
!= 2)
46 return "Need exactly 2 players";
47 if (g
.players
.some(p
=> !p
.id
.toString().match(/^[0-9]+$/)))
48 return "Wrong characters in player ID";
52 create: function(vid
, fen
, cadence
, players
, cb
)
54 db
.serialize(function() {
56 "INSERT INTO Games " +
57 "(vid, fenStart, fen, score, cadence, created, drawOffer) " +
59 "(" + vid
+ ",'" + fen
+ "','" + fen
+ "','*','" + cadence
+ "'," + Date
.now() + ",'')";
60 db
.run(query
, function(err
) {
63 players
.forEach((p
,idx
) => {
64 const color
= (idx
==0 ? "w" : "b");
66 "INSERT INTO Players VALUES " +
67 "(" + this.lastID
+ "," + p
.id
+ ",'" + color
+ "')";
70 cb(null, {gid: this.lastID
});
75 // TODO: some queries here could be async
76 getOne: function(id
, light
, cb
)
78 db
.serialize(function() {
80 // NOTE: g.scoreMsg can be NULL
81 // (in this case score = "*" and no reason to look at it)
82 "SELECT g.id, g.vid, g.fen, g.fenStart, g.cadence, g.score, " +
83 "g.scoreMsg, g.drawOffer, v.name AS vname " +
88 db
.get(query
, (err
,gameInfo
) => {
92 "SELECT p.uid, p.color, u.name " +
96 "WHERE p.gid = " + id
;
97 db
.all(query
, (err2
,players
) => {
102 const game
= Object
.assign({},
106 return cb(null, game
);
109 "SELECT squares, played, idx " +
112 db
.all(query
, (err3
,moves
) => {
116 "SELECT msg, name, added " +
119 db
.all(query
, (err4
,chats
) => {
122 const game
= Object
.assign({},
130 return cb(null, game
);
138 // For display on MyGames or Hall: no need for moves or chats
139 getByUser: function(uid
, excluded
, cb
)
141 db
.serialize(function() {
143 "SELECT DISTINCT gid " +
145 "WHERE uid " + (excluded
? "<>" : "=") + " " + uid
;
146 db
.all(query
, (err
,gameIds
) => {
149 if (gameIds
.length
== 0)
152 for (let i
=0; i
<gameIds
.length
; i
++)
154 GameModel
.getOne(gameIds
[i
]["gid"], true, (err2
,game
) => {
157 gameArray
.push(game
);
158 // Call callback function only when gameArray is complete:
159 if (i
== gameIds
.length
- 1)
160 return cb(null, gameArray
);
167 getPlayers: function(id
, cb
)
169 db
.serialize(function() {
174 db
.all(query
, (err
,players
) => {
175 return cb(err
, players
);
180 checkGameUpdate: function(obj
)
182 // Check all that is possible (required) in obj:
185 if (!obj
.move.played
.toString().match(/^[0-9]+$/))
186 return "Wrong move played time";
187 if (!obj
.move.idx
.toString().match(/^[0-9]+$/))
188 return "Wrong move index";
190 if (!!obj
.drawOffer
&& !obj
.drawOffer
.match(/^[wbtn]$/))
191 return "Wrong draw offer format";
192 if (!!obj
.fen
&& !obj
.fen
.match(/^[a-zA-Z0-9, /-]*$/))
193 return "Wrong FEN string";
194 if (!!obj
.score
&& !obj
.score
.match(/^[012?*\/-]+$/))
195 return "Wrong characters in score";
196 if (!!obj
.scoreMsg
&& !obj
.scoreMsg
.match(/^[a
-zA
-Z
]+$/))
197 return "Wrong characters in score message";
199 return UserModel
.checkNameEmail({name: obj
.chat
.name
});
203 // obj can have fields move, chat, fen, drawOffer and/or score
204 update: function(id
, obj
)
206 db
.parallelize(function() {
212 modifs
+= "message = message || ' ' || '" + obj
.message
+ "',";
213 // NOTE: if drawOffer is set, we should check that it's player's turn
214 // A bit overcomplicated. Let's trust the client on that for now...
217 if (obj
.drawOffer
== "n") //Special "None" update
219 modifs
+= "drawOffer = '" + obj
.drawOffer
+ "',";
222 modifs
+= "fen = '" + obj
.fen
+ "',";
224 modifs
+= "score = '" + obj
.score
+ "',";
226 modifs
+= "scoreMsg = '" + obj
.scoreMsg
+ "',";
227 modifs
= modifs
.slice(0,-1); //remove last comma
228 if (modifs
.length
> 0)
230 query
+= modifs
+ " WHERE id = " + id
;
237 "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
238 "(" + id
+ ",?," + m
.played
+ "," + m
.idx
+ ")";
239 db
.run(query
, JSON
.stringify(m
.squares
));
244 "INSERT INTO Chats (gid, msg, name, added) VALUES ("
245 + id
+ ",?,'" + obj
.chat
.name
+ "'," + Date
.now() + ")";
246 db
.run(query
, obj
.chat
.msg
);
253 db
.parallelize(function() {
255 "DELETE FROM Games " +
259 "DELETE FROM Players " +
263 "DELETE FROM Moves " +
267 "DELETE FROM Chats " +
273 cleanGamesDb: function()
275 const tsNow
= Date
.now();
276 // 86400000 = 24 hours in milliseconds
277 const day
= 86400000;
278 db
.serialize(function() {
280 "SELECT id,score,created " +
282 db
.all(query
, (err
,games
) => {
285 "SELECT max(played) AS lastMaj " +
287 "WHERE gid = " + g
.id
;
288 db
.get(query
, (err2
,updated
) => {
289 if (!updated
.lastMaj
)
291 if (tsNow
- g
.created
> 7*day
)
292 return GameModel
.remove(g
.id
);
294 else //at least one move
296 const lastMaj
= updated
.lastMaj
;
297 if (g
.score
!= "*" && tsNow
- lastMaj
> 7*day
||
298 g
.score
== "*" && tsNow
- lastMaj
> 91*day
)
300 GameModel
.remove(g
.id
);
310 module
.exports
= GameModel
;