Commit | Line | Data |
---|---|---|
c5c47010 | 1 | const db = require("../utils/database"); |
58e7b94e | 2 | const UserModel = require("./User"); |
00f2759e BA |
3 | |
4 | /* | |
5 | * Structure table Games: | |
6 | * id: game id (int) | |
7 | * vid: integer (variant id) | |
ab4f4bf2 BA |
8 | * fenStart: varchar (initial position) |
9 | * fen: varchar (current position) | |
71468011 | 10 | * cadence: string |
00f2759e | 11 | * score: varchar (result) |
dcd68c41 | 12 | * scoreMsg: varchar ("Time", "Mutual agreement"...) |
83494c7f | 13 | * created: datetime |
dfeb96ea | 14 | * drawOffer: char ('w','b' or '' for none) |
00f2759e BA |
15 | * |
16 | * Structure table Players: | |
17 | * gid: ref game id | |
18 | * uid: ref user id | |
19 | * color: character | |
20 | * | |
21 | * Structure table Moves: | |
ab4f4bf2 | 22 | * gid: ref game id |
f41ce580 | 23 | * squares: varchar (description) |
00f2759e BA |
24 | * played: datetime |
25 | * idx: integer | |
3837d4f7 BA |
26 | * |
27 | * Structure table Chats: | |
28 | * gid: game id (int) | |
29 | * msg: varchar | |
30 | * name: varchar | |
3837d4f7 | 31 | * added: datetime |
00f2759e BA |
32 | */ |
33 | ||
ab4f4bf2 | 34 | const GameModel = |
00f2759e | 35 | { |
58e7b94e | 36 | checkGameInfo: function(g) { |
58e7b94e BA |
37 | if (!g.vid.toString().match(/^[0-9]+$/)) |
38 | return "Wrong variant ID"; | |
71468011 | 39 | if (!g.cadence.match(/^[0-9dhms +]+$/)) |
58e7b94e BA |
40 | return "Wrong characters in time control"; |
41 | if (!g.fen.match(/^[a-zA-Z0-9, /-]*$/)) | |
42 | return "Bad FEN string"; | |
43 | if (g.players.length != 2) | |
44 | return "Need exactly 2 players"; | |
45 | if (g.players.some(p => !p.id.toString().match(/^[0-9]+$/))) | |
46 | return "Wrong characters in player ID"; | |
47 | return ""; | |
48 | }, | |
49 | ||
71468011 | 50 | create: function(vid, fen, cadence, players, cb) |
dac39588 BA |
51 | { |
52 | db.serialize(function() { | |
53 | let query = | |
71468011 BA |
54 | "INSERT INTO Games " + |
55 | "(vid, fenStart, fen, score, cadence, created, drawOffer) " + | |
56 | "VALUES " + | |
57 | "(" + vid + ",'" + fen + "','" + fen + "','*','" + cadence + "'," + Date.now() + ",'')"; | |
8c564f46 | 58 | db.run(query, function(err) { |
cf742aaf BA |
59 | if (!!err) |
60 | return cb(err); | |
8c564f46 BA |
61 | players.forEach((p,idx) => { |
62 | const color = (idx==0 ? "w" : "b"); | |
2be5d614 BA |
63 | query = |
64 | "INSERT INTO Players VALUES " + | |
f41ce580 | 65 | "(" + this.lastID + "," + p.id + ",'" + color + "')"; |
2be5d614 BA |
66 | db.run(query); |
67 | }); | |
68 | cb(null, {gid: this.lastID}); | |
dac39588 BA |
69 | }); |
70 | }); | |
71 | }, | |
00f2759e | 72 | |
71468011 BA |
73 | // TODO: some queries here could be async |
74 | getOne: function(id, light, cb) | |
dac39588 BA |
75 | { |
76 | db.serialize(function() { | |
dac39588 | 77 | let query = |
dcd68c41 BA |
78 | // NOTE: g.scoreMsg can be NULL |
79 | // (in this case score = "*" and no reason to look at it) | |
2f258c37 | 80 | "SELECT g.id, g.vid, g.fen, g.fenStart, g.cadence, g.created, g.score, " + |
633959bf | 81 | "g.scoreMsg, g.drawOffer, v.name AS vname " + |
dac39588 | 82 | "FROM Games g " + |
f41ce580 BA |
83 | "JOIN Variants v " + |
84 | " ON g.vid = v.id " + | |
dac39588 BA |
85 | "WHERE g.id = " + id; |
86 | db.get(query, (err,gameInfo) => { | |
87 | if (!!err) | |
88 | return cb(err); | |
89 | query = | |
90 | "SELECT p.uid, p.color, u.name " + | |
91 | "FROM Players p " + | |
f41ce580 BA |
92 | "JOIN Users u " + |
93 | " ON p.uid = u.id " + | |
dac39588 BA |
94 | "WHERE p.gid = " + id; |
95 | db.all(query, (err2,players) => { | |
96 | if (!!err2) | |
97 | return cb(err2); | |
71468011 BA |
98 | if (light) |
99 | { | |
100 | const game = Object.assign({}, | |
101 | gameInfo, | |
102 | {players: players} | |
103 | ); | |
104 | return cb(null, game); | |
105 | } | |
dac39588 BA |
106 | query = |
107 | "SELECT squares, played, idx " + | |
108 | "FROM Moves " + | |
109 | "WHERE gid = " + id; | |
110 | db.all(query, (err3,moves) => { | |
111 | if (!!err3) | |
112 | return cb(err3); | |
113 | query = | |
dcd68c41 | 114 | "SELECT msg, name, added " + |
3837d4f7 BA |
115 | "FROM Chats " + |
116 | "WHERE gid = " + id; | |
dac39588 BA |
117 | db.all(query, (err4,chats) => { |
118 | if (!!err4) | |
119 | return cb(err4); | |
120 | const game = Object.assign({}, | |
3837d4f7 BA |
121 | gameInfo, |
122 | { | |
123 | players: players, | |
124 | moves: moves, | |
125 | chats: chats, | |
126 | } | |
127 | ); | |
dac39588 | 128 | return cb(null, game); |
3837d4f7 | 129 | }); |
dac39588 BA |
130 | }); |
131 | }); | |
132 | }); | |
133 | }); | |
134 | }, | |
00f2759e | 135 | |
71468011 | 136 | // For display on MyGames or Hall: no need for moves or chats |
dac39588 BA |
137 | getByUser: function(uid, excluded, cb) |
138 | { | |
139 | db.serialize(function() { | |
2f258c37 BA |
140 | let query = ""; |
141 | if (uid == 0) | |
142 | { | |
143 | // Special case anonymous user: show all games | |
144 | query = | |
145 | "SELECT id AS gid " + | |
146 | "FROM Games"; | |
147 | } | |
148 | else | |
149 | { | |
150 | // Registered user: | |
151 | query = | |
152 | "SELECT gid " + | |
153 | "FROM Players " + | |
154 | "GROUP BY gid " + | |
155 | "HAVING COUNT(uid = " + uid + " OR NULL) " + | |
156 | (excluded ? " = 0" : " > 0"); | |
157 | } | |
dac39588 | 158 | db.all(query, (err,gameIds) => { |
2f258c37 BA |
159 | if (!!err || gameIds.length == 0) |
160 | return cb(err, []); | |
dac39588 | 161 | let gameArray = []; |
2f258c37 | 162 | let kounter = 0; |
dac39588 BA |
163 | for (let i=0; i<gameIds.length; i++) |
164 | { | |
71468011 | 165 | GameModel.getOne(gameIds[i]["gid"], true, (err2,game) => { |
dac39588 BA |
166 | if (!!err2) |
167 | return cb(err2); | |
168 | gameArray.push(game); | |
2f258c37 | 169 | kounter++; //TODO: let's hope this is atomic?! |
dac39588 | 170 | // Call callback function only when gameArray is complete: |
2f258c37 | 171 | if (kounter == gameIds.length) |
dac39588 BA |
172 | return cb(null, gameArray); |
173 | }); | |
174 | } | |
175 | }); | |
176 | }); | |
177 | }, | |
ab4f4bf2 | 178 | |
411d23cd BA |
179 | getPlayers: function(id, cb) |
180 | { | |
181 | db.serialize(function() { | |
182 | const query = | |
1ad003ff | 183 | "SELECT uid " + |
411d23cd BA |
184 | "FROM Players " + |
185 | "WHERE gid = " + id; | |
186 | db.all(query, (err,players) => { | |
187 | return cb(err, players); | |
188 | }); | |
189 | }); | |
190 | }, | |
191 | ||
58e7b94e BA |
192 | checkGameUpdate: function(obj) |
193 | { | |
194 | // Check all that is possible (required) in obj: | |
195 | if (!!obj.move) | |
196 | { | |
197 | if (!obj.move.played.toString().match(/^[0-9]+$/)) | |
198 | return "Wrong move played time"; | |
199 | if (!obj.move.idx.toString().match(/^[0-9]+$/)) | |
200 | return "Wrong move index"; | |
201 | } | |
633959bf BA |
202 | if (!!obj.drawOffer && !obj.drawOffer.match(/^[wbtn]$/)) |
203 | return "Wrong draw offer format"; | |
58e7b94e BA |
204 | if (!!obj.fen && !obj.fen.match(/^[a-zA-Z0-9, /-]*$/)) |
205 | return "Wrong FEN string"; | |
206 | if (!!obj.score && !obj.score.match(/^[012?*\/-]+$/)) | |
207 | return "Wrong characters in score"; | |
dcd68c41 BA |
208 | if (!!obj.scoreMsg && !obj.scoreMsg.match(/^[a-zA-Z ]+$/)) |
209 | return "Wrong characters in score message"; | |
58e7b94e | 210 | if (!!obj.chat) |
58e7b94e | 211 | return UserModel.checkNameEmail({name: obj.chat.name}); |
58e7b94e BA |
212 | return ""; |
213 | }, | |
214 | ||
3837d4f7 | 215 | // obj can have fields move, chat, fen, drawOffer and/or score |
f41ce580 | 216 | update: function(id, obj) |
3d55deea | 217 | { |
dac39588 | 218 | db.parallelize(function() { |
3d55deea BA |
219 | let query = |
220 | "UPDATE Games " + | |
221 | "SET "; | |
3837d4f7 | 222 | let modifs = ""; |
63ca2b89 | 223 | if (!!obj.message) |
3837d4f7 | 224 | modifs += "message = message || ' ' || '" + obj.message + "',"; |
633959bf | 225 | // NOTE: if drawOffer is set, we should check that it's player's turn |
dfeb96ea BA |
226 | // A bit overcomplicated. Let's trust the client on that for now... |
227 | if (!!obj.drawOffer) | |
633959bf BA |
228 | { |
229 | if (obj.drawOffer == "n") //Special "None" update | |
230 | obj.drawOffer = ""; | |
231 | modifs += "drawOffer = '" + obj.drawOffer + "',"; | |
232 | } | |
3d55deea | 233 | if (!!obj.fen) |
3837d4f7 | 234 | modifs += "fen = '" + obj.fen + "',"; |
3d55deea | 235 | if (!!obj.score) |
3837d4f7 | 236 | modifs += "score = '" + obj.score + "',"; |
dcd68c41 BA |
237 | if (!!obj.scoreMsg) |
238 | modifs += "scoreMsg = '" + obj.scoreMsg + "',"; | |
3837d4f7 BA |
239 | modifs = modifs.slice(0,-1); //remove last comma |
240 | if (modifs.length > 0) | |
241 | { | |
242 | query += modifs + " WHERE id = " + id; | |
243 | db.run(query); | |
244 | } | |
f41ce580 BA |
245 | if (!!obj.move) |
246 | { | |
afc42601 | 247 | const m = obj.move; |
f41ce580 | 248 | query = |
63ca2b89 | 249 | "INSERT INTO Moves (gid, squares, played, idx) VALUES " + |
58e7b94e BA |
250 | "(" + id + ",?," + m.played + "," + m.idx + ")"; |
251 | db.run(query, JSON.stringify(m.squares)); | |
f41ce580 | 252 | } |
3837d4f7 BA |
253 | if (!!obj.chat) |
254 | { | |
dac39588 BA |
255 | query = |
256 | "INSERT INTO Chats (gid, msg, name, added) VALUES (" | |
41c80bb6 | 257 | + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")"; |
58e7b94e | 258 | db.run(query, obj.chat.msg); |
3837d4f7 | 259 | } |
3d55deea BA |
260 | }); |
261 | }, | |
262 | ||
dac39588 BA |
263 | remove: function(id) |
264 | { | |
265 | db.parallelize(function() { | |
266 | let query = | |
267 | "DELETE FROM Games " + | |
268 | "WHERE id = " + id; | |
269 | db.run(query); | |
270 | query = | |
271 | "DELETE FROM Players " + | |
272 | "WHERE gid = " + id; | |
273 | db.run(query); | |
274 | query = | |
275 | "DELETE FROM Moves " + | |
276 | "WHERE gid = " + id; | |
277 | db.run(query); | |
278 | query = | |
279 | "DELETE FROM Chats " + | |
280 | "WHERE gid = " + id; | |
281 | db.run(query); | |
282 | }); | |
283 | }, | |
d431028c BA |
284 | |
285 | cleanGamesDb: function() | |
286 | { | |
287 | const tsNow = Date.now(); | |
288 | // 86400000 = 24 hours in milliseconds | |
289 | const day = 86400000; | |
290 | db.serialize(function() { | |
291 | let query = | |
0f7011a2 | 292 | "SELECT id, created " + |
d431028c BA |
293 | "FROM Games "; |
294 | db.all(query, (err,games) => { | |
295 | games.forEach(g => { | |
296 | query = | |
fd41e587 | 297 | "SELECT count(*) as nbMoves, max(played) AS lastMaj " + |
d431028c BA |
298 | "FROM Moves " + |
299 | "WHERE gid = " + g.id; | |
fd41e587 BA |
300 | db.get(query, (err2,mstats) => { |
301 | // Remove games still not really started, | |
302 | // with no action in the last 3 months: | |
303 | if ((mstats.nbMoves == 0 && tsNow - g.created > 91*day) || | |
304 | (mstats.nbMoves == 1 && tsNow - mstats.lastMaj > 91*day)) | |
d431028c | 305 | { |
89021f18 | 306 | GameModel.remove(g.id); |
d431028c BA |
307 | } |
308 | }); | |
309 | }); | |
310 | }); | |
311 | }); | |
312 | }, | |
00f2759e | 313 | } |
ab4f4bf2 BA |
314 | |
315 | module.exports = GameModel; |