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)
15 * Structure table Players:
20 * Structure table Moves:
22 * squares: varchar (description)
26 * Structure table Chats:
30 * sid: varchar (socket ID when sending message)
36 checkGameInfo: function(g
) {
37 if (!g
.id
.toString().match(/^[0-9]+$/))
38 return "Wrong game ID";
39 if (!g
.vid
.toString().match(/^[0-9]+$/))
40 return "Wrong variant ID";
41 if (!g
.vname
.match(/^[a-zA-Z0-9]+$/))
42 return "Wrong variant name";
43 if (!g
.timeControl
.match(/^[0-9dhms
+]+$/))
44 return "Wrong characters in time control";
45 if (!g
.fen
.match(/^[a-zA-Z0-9, /-]*$/))
46 return "Bad FEN string";
47 if (g
.players
.length
!= 2)
48 return "Need exactly 2 players";
49 if (g
.players
.some(p
=> !p
.id
.toString().match(/^[0-9]+$/)))
50 return "Wrong characters in player ID";
54 create: function(vid
, fen
, timeControl
, players
, cb
)
56 db
.serialize(function() {
59 + " (vid, fenStart, fen, score, timeControl, created, drawOffer)"
60 + " VALUES (" + vid
+ ",'" + fen
+ "','" + fen
+ "','*','"
61 + timeControl
+ "'," + Date
.now() + "," + false + ")";
62 db
.run(query
, function(err
) {
65 players
.forEach((p
,idx
) => {
66 const color
= (idx
==0 ? "w" : "b");
68 "INSERT INTO Players VALUES " +
69 "(" + this.lastID
+ "," + p
.id
+ ",'" + color
+ "')";
72 cb(null, {gid: this.lastID
});
77 // TODO: queries here could be async, and wait for all to complete
78 getOne: function(id
, cb
)
80 db
.serialize(function() {
81 // TODO: optimize queries?
83 "SELECT g.id, g.vid, g.fen, g.fenStart, g.timeControl, g.score, " +
89 db
.get(query
, (err
,gameInfo
) => {
93 "SELECT p.uid, p.color, u.name " +
97 "WHERE p.gid = " + id
;
98 db
.all(query
, (err2
,players
) => {
102 "SELECT squares, played, idx " +
105 db
.all(query
, (err3
,moves
) => {
109 "SELECT msg, name, sid, added " +
112 db
.all(query
, (err4
,chats
) => {
115 const game
= Object
.assign({},
123 return cb(null, game
);
131 getByUser: function(uid
, excluded
, cb
)
133 db
.serialize(function() {
134 // Next query is fine because a player appear at most once in a game
138 "WHERE uid " + (excluded
? "<>" : "=") + " " + uid
;
139 db
.all(query
, (err
,gameIds
) => {
142 gameIds
= gameIds
|| []; //might be empty
144 for (let i
=0; i
<gameIds
.length
; i
++)
146 GameModel
.getOne(gameIds
[i
]["gid"], (err2
,game
) => {
149 gameArray
.push(game
);
150 // Call callback function only when gameArray is complete:
151 if (i
== gameIds
.length
- 1)
152 return cb(null, gameArray
);
159 getPlayers: function(id
, cb
)
161 db
.serialize(function() {
166 db
.all(query
, (err
,players
) => {
167 return cb(err
, players
);
172 checkGameUpdate: function(obj
)
174 // Check all that is possible (required) in obj:
177 if (!obj
.move.played
.toString().match(/^[0-9]+$/))
178 return "Wrong move played time";
179 if (!obj
.move.idx
.toString().match(/^[0-9]+$/))
180 return "Wrong move index";
182 if (!!obj
.fen
&& !obj
.fen
.match(/^[a-zA-Z0-9, /-]*$/))
183 return "Wrong FEN string";
184 if (!!obj
.score
&& !obj
.score
.match(/^[012?*\/-]+$/))
185 return "Wrong characters in score";
188 if (!obj
.chat
.sid
.match(/^[a-zA-Z0-9]+$/))
189 return "Wrong user SID";
190 return UserModel
.checkNameEmail({name: obj
.chat
.name
});
195 // obj can have fields move, chat, fen, drawOffer and/or score
196 update: function(id
, obj
)
198 db
.parallelize(function() {
204 modifs
+= "message = message || ' ' || '" + obj
.message
+ "',";
205 if ([true,false].includes(obj
.drawOffer
))
206 modifs
+= "drawOffer = " + obj
.drawOffer
+ ",";
208 modifs
+= "fen = '" + obj
.fen
+ "',";
210 modifs
+= "score = '" + obj
.score
+ "',";
211 modifs
= modifs
.slice(0,-1); //remove last comma
212 if (modifs
.length
> 0)
214 query
+= modifs
+ " WHERE id = " + id
;
221 "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
222 "(" + id
+ ",?," + m
.played
+ "," + m
.idx
+ ")";
223 db
.run(query
, JSON
.stringify(m
.squares
));
228 "INSERT INTO Chats (gid, msg, name, sid, added) VALUES " +
229 "(" + id
+ ",?,'" + obj
.chat
.name
+ "','"
230 + obj
.chat
.sid
+ "'," + Date
.now() + ")";
231 db
.run(query
, obj
.chat
.msg
);
238 db
.parallelize(function() {
240 "DELETE FROM Games " +
244 "DELETE FROM Players " +
248 "DELETE FROM Moves " +
252 "DELETE FROM Chats " +
258 cleanGamesDb: function()
260 const tsNow
= Date
.now();
261 // 86400000 = 24 hours in milliseconds
262 const day
= 86400000;
263 db
.serialize(function() {
267 db
.all(query
, (err
,games
) => {
270 "SELECT max(played) AS lastMaj " +
272 "WHERE gid = " + g
.id
;
273 db
.get(query
, (err2
,updated
) => {
274 if (!updated
&& tsNow
- g
.created
> 7*day
)
275 return GameModel
.remove(g
.id
);
276 const lastMaj
= updated
.lastMaj
;
277 if (g
.score
!= "*" && tsNow
- lastMaj
> 7*day
||
278 g
.score
== "*" && tsNow
- lastMaj
> 91*day
)
280 GameModel
.remove(g
.id
);
289 module
.exports
= GameModel
;