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) { |
866842c3 BA |
37 | return ( |
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.id.toString().match(/^[0-9]+$/)) | |
43 | ); | |
58e7b94e BA |
44 | }, |
45 | ||
71468011 | 46 | create: function(vid, fen, cadence, players, cb) |
dac39588 BA |
47 | { |
48 | db.serialize(function() { | |
49 | let query = | |
71468011 | 50 | "INSERT INTO Games " + |
aae89b49 | 51 | "(vid, fenStart, fen, cadence, created) " + |
71468011 | 52 | "VALUES " + |
aae89b49 | 53 | "(" + vid + ",'" + fen + "','" + fen + "','" + cadence + "'," + Date.now() + ")"; |
8c564f46 | 54 | db.run(query, function(err) { |
866842c3 BA |
55 | if (err) |
56 | cb(err) | |
57 | else | |
58 | { | |
59 | players.forEach((p,idx) => { | |
60 | const color = (idx==0 ? "w" : "b"); | |
61 | query = | |
62 | "INSERT INTO Players VALUES " + | |
63 | "(" + this.lastID + "," + p.id + ",'" + color + "')"; | |
64 | db.run(query); | |
65 | }); | |
66 | cb(null, {gid: this.lastID}); | |
67 | } | |
dac39588 BA |
68 | }); |
69 | }); | |
70 | }, | |
00f2759e | 71 | |
71468011 | 72 | // TODO: some queries here could be async |
aae89b49 | 73 | getOne: function(id, cb) |
dac39588 | 74 | { |
866842c3 | 75 | // NOTE: ignoring errors (shouldn't happen at this stage) |
dac39588 | 76 | db.serialize(function() { |
dac39588 | 77 | let query = |
2f258c37 | 78 | "SELECT g.id, g.vid, g.fen, g.fenStart, g.cadence, g.created, g.score, " + |
aae89b49 | 79 | "g.scoreMsg, g.drawOffer, g.rematchOffer, v.name AS vname " + |
dac39588 | 80 | "FROM Games g " + |
f41ce580 BA |
81 | "JOIN Variants v " + |
82 | " ON g.vid = v.id " + | |
dac39588 | 83 | "WHERE g.id = " + id; |
aae89b49 | 84 | db.get(query, (err, gameInfo) => { |
dac39588 BA |
85 | query = |
86 | "SELECT p.uid, p.color, u.name " + | |
87 | "FROM Players p " + | |
f41ce580 BA |
88 | "JOIN Users u " + |
89 | " ON p.uid = u.id " + | |
dac39588 | 90 | "WHERE p.gid = " + id; |
aae89b49 BA |
91 | db.all(query, (err2, players) => { |
92 | query = | |
93 | "SELECT squares, played, idx " + | |
94 | "FROM Moves " + | |
95 | "WHERE gid = " + id; | |
96 | db.all(query, (err3, moves) => { | |
db1f1f9a | 97 | query = |
aae89b49 BA |
98 | "SELECT msg, name, added " + |
99 | "FROM Chats " + | |
db1f1f9a | 100 | "WHERE gid = " + id; |
aae89b49 | 101 | db.all(query, (err4, chats) => { |
db1f1f9a BA |
102 | const game = Object.assign({}, |
103 | gameInfo, | |
aae89b49 BA |
104 | { |
105 | players: players, | |
106 | moves: moves, | |
107 | chats: chats, | |
108 | } | |
db1f1f9a BA |
109 | ); |
110 | cb(null, game); | |
111 | }); | |
aae89b49 | 112 | }); |
dac39588 BA |
113 | }); |
114 | }); | |
115 | }); | |
116 | }, | |
00f2759e | 117 | |
71468011 | 118 | // For display on MyGames or Hall: no need for moves or chats |
dac39588 BA |
119 | getByUser: function(uid, excluded, cb) |
120 | { | |
aae89b49 BA |
121 | // Some fields are not required when showing a games list: |
122 | const getOneLight = (id, cb2) => { | |
123 | let query = | |
124 | "SELECT g.id, g.vid, g.fen, g.cadence, g.created, g.score, " + | |
125 | "g.scoreMsg, g.deletedByWhite, g.deletedByBlack, v.name AS vname " + | |
126 | "FROM Games g " + | |
127 | "JOIN Variants v " + | |
128 | " ON g.vid = v.id " + | |
129 | "WHERE g.id = " + id; | |
130 | db.get(query, (err, gameInfo) => { | |
131 | query = | |
132 | "SELECT p.uid, p.color, u.name " + | |
133 | "FROM Players p " + | |
134 | "JOIN Users u " + | |
135 | " ON p.uid = u.id " + | |
136 | "WHERE p.gid = " + id; | |
137 | db.all(query, (err2, players) => { | |
138 | query = | |
139 | "SELECT COUNT(*) AS nbMoves " + | |
140 | "FROM Moves " + | |
141 | "WHERE gid = " + id; | |
142 | db.get(query, (err,ret) => { | |
143 | const game = Object.assign({}, | |
144 | gameInfo, | |
145 | { | |
146 | players: players, | |
147 | movesCount: ret.nbMoves | |
148 | } | |
149 | ); | |
150 | cb2(game); | |
151 | }); | |
152 | }); | |
153 | }); | |
154 | }; | |
dac39588 | 155 | db.serialize(function() { |
2f258c37 | 156 | let query = ""; |
aae89b49 | 157 | if (uid == 0) { |
2f258c37 BA |
158 | // Special case anonymous user: show all games |
159 | query = | |
160 | "SELECT id AS gid " + | |
161 | "FROM Games"; | |
162 | } | |
aae89b49 | 163 | else { |
2f258c37 BA |
164 | // Registered user: |
165 | query = | |
166 | "SELECT gid " + | |
167 | "FROM Players " + | |
168 | "GROUP BY gid " + | |
169 | "HAVING COUNT(uid = " + uid + " OR NULL) " + | |
170 | (excluded ? " = 0" : " > 0"); | |
171 | } | |
dac39588 | 172 | db.all(query, (err,gameIds) => { |
aae89b49 BA |
173 | if (err || gameIds.length == 0) cb(err, []); |
174 | else { | |
866842c3 BA |
175 | let gameArray = []; |
176 | let gCounter = 0; | |
aae89b49 BA |
177 | for (let i=0; i<gameIds.length; i++) { |
178 | getOneLight(gameIds[i]["gid"], (game) => { | |
866842c3 BA |
179 | gameArray.push(game); |
180 | gCounter++; //TODO: let's hope this is atomic?! | |
181 | // Call callback function only when gameArray is complete: | |
182 | if (gCounter == gameIds.length) | |
183 | cb(null, gameArray); | |
184 | }); | |
185 | } | |
dac39588 BA |
186 | } |
187 | }); | |
188 | }); | |
189 | }, | |
ab4f4bf2 | 190 | |
411d23cd BA |
191 | getPlayers: function(id, cb) |
192 | { | |
193 | db.serialize(function() { | |
194 | const query = | |
1ad003ff | 195 | "SELECT uid " + |
411d23cd BA |
196 | "FROM Players " + |
197 | "WHERE gid = " + id; | |
198 | db.all(query, (err,players) => { | |
199 | return cb(err, players); | |
200 | }); | |
201 | }); | |
202 | }, | |
203 | ||
58e7b94e BA |
204 | checkGameUpdate: function(obj) |
205 | { | |
206 | // Check all that is possible (required) in obj: | |
866842c3 BA |
207 | return ( |
208 | ( | |
209 | !obj.move || ( | |
188b4a8f BA |
210 | !!(obj.move.played.toString().match(/^[0-9]+$/)) && |
211 | !!(obj.move.idx.toString().match(/^[0-9]+$/)) | |
866842c3 BA |
212 | ) |
213 | ) && ( | |
188b4a8f | 214 | !obj.drawOffer || !!(obj.drawOffer.match(/^[wbtn]$/)) |
866842c3 | 215 | ) && ( |
188b4a8f | 216 | !obj.fen || !!(obj.fen.match(/^[a-zA-Z0-9, /-]*$/)) |
866842c3 | 217 | ) && ( |
188b4a8f | 218 | !obj.score || !!(obj.score.match(/^[012?*\/-]+$/)) |
866842c3 | 219 | ) && ( |
188b4a8f | 220 | !obj.scoreMsg || !!(obj.scoreMsg.match(/^[a-zA-Z ]+$/)) |
866842c3 BA |
221 | ) && ( |
222 | !obj.chat || UserModel.checkNameEmail({name: obj.chat.name}) | |
223 | ) | |
224 | ); | |
58e7b94e BA |
225 | }, |
226 | ||
866842c3 | 227 | // obj can have fields move, chat, fen, drawOffer and/or score + message |
fb68b0c2 | 228 | update: function(id, obj, cb) |
3d55deea | 229 | { |
dac39588 | 230 | db.parallelize(function() { |
3d55deea BA |
231 | let query = |
232 | "UPDATE Games " + | |
233 | "SET "; | |
3837d4f7 | 234 | let modifs = ""; |
633959bf | 235 | // NOTE: if drawOffer is set, we should check that it's player's turn |
dfeb96ea | 236 | // A bit overcomplicated. Let's trust the client on that for now... |
866842c3 | 237 | if (obj.drawOffer) |
633959bf BA |
238 | { |
239 | if (obj.drawOffer == "n") //Special "None" update | |
240 | obj.drawOffer = ""; | |
241 | modifs += "drawOffer = '" + obj.drawOffer + "',"; | |
242 | } | |
aae89b49 | 243 | if (!!obj.fen) |
3837d4f7 | 244 | modifs += "fen = '" + obj.fen + "',"; |
aae89b49 | 245 | if (!!obj.score) |
3837d4f7 | 246 | modifs += "score = '" + obj.score + "',"; |
aae89b49 | 247 | if (!!obj.scoreMsg) |
dcd68c41 | 248 | modifs += "scoreMsg = '" + obj.scoreMsg + "',"; |
aae89b49 BA |
249 | if (!!obj.deletedBy) { |
250 | const myColor = obj.deletedBy == 'w' ? "White" : "Black"; | |
251 | modifs += "deletedBy" + myColor + " = true,"; | |
252 | } | |
3837d4f7 BA |
253 | modifs = modifs.slice(0,-1); //remove last comma |
254 | if (modifs.length > 0) | |
255 | { | |
256 | query += modifs + " WHERE id = " + id; | |
257 | db.run(query); | |
258 | } | |
23ecf008 | 259 | // NOTE: move, chat and delchat are mutually exclusive |
89ffc919 | 260 | if (!!obj.move) |
f41ce580 | 261 | { |
fb68b0c2 | 262 | // Security: only update moves if index is right |
f41ce580 | 263 | query = |
fb68b0c2 BA |
264 | "SELECT MAX(idx) AS maxIdx " + |
265 | "FROM Moves " + | |
266 | "WHERE gid = " + id; | |
267 | db.get(query, (err,ret) => { | |
268 | const m = obj.move; | |
269 | if (!ret.maxIdx || ret.maxIdx + 1 == m.idx) { | |
270 | query = | |
271 | "INSERT INTO Moves (gid, squares, played, idx) VALUES " + | |
272 | "(" + id + ",?," + m.played + "," + m.idx + ")"; | |
273 | db.run(query, JSON.stringify(m.squares)); | |
274 | cb(null); | |
275 | } | |
276 | else cb({errmsg:"Wrong move index"}); | |
277 | }); | |
f41ce580 | 278 | } |
fb68b0c2 | 279 | else cb(null); |
aae89b49 | 280 | if (!!obj.chat) |
3837d4f7 | 281 | { |
dac39588 BA |
282 | query = |
283 | "INSERT INTO Chats (gid, msg, name, added) VALUES (" | |
41c80bb6 | 284 | + id + ",?,'" + obj.chat.name + "'," + Date.now() + ")"; |
58e7b94e | 285 | db.run(query, obj.chat.msg); |
3837d4f7 | 286 | } |
db1f1f9a BA |
287 | else if (obj.delchat) |
288 | { | |
289 | query = | |
290 | "DELETE " + | |
291 | "FROM Chats " + | |
292 | "WHERE gid = " + id; | |
23ecf008 | 293 | db.run(query); |
db1f1f9a | 294 | } |
aae89b49 BA |
295 | if (!!obj.deletedBy) { |
296 | // Did my opponent delete it too? | |
297 | let selection = | |
298 | "deletedBy" + | |
299 | (obj.deletedBy == 'w' ? "Black" : "White") + | |
300 | " AS deletedByOpp"; | |
301 | query = | |
302 | "SELECT " + selection + " " + | |
303 | "FROM Games " + | |
304 | "WHERE id = " + id; | |
305 | db.get(query, (err,ret) => { | |
306 | // If yes: just remove game | |
307 | if (!!ret.deletedByOpp) GameModel.remove(id); | |
308 | }); | |
309 | } | |
3d55deea BA |
310 | }); |
311 | }, | |
312 | ||
dac39588 BA |
313 | remove: function(id) |
314 | { | |
315 | db.parallelize(function() { | |
316 | let query = | |
317 | "DELETE FROM Games " + | |
318 | "WHERE id = " + id; | |
319 | db.run(query); | |
320 | query = | |
321 | "DELETE FROM Players " + | |
322 | "WHERE gid = " + id; | |
323 | db.run(query); | |
324 | query = | |
325 | "DELETE FROM Moves " + | |
326 | "WHERE gid = " + id; | |
327 | db.run(query); | |
328 | query = | |
329 | "DELETE FROM Chats " + | |
330 | "WHERE gid = " + id; | |
331 | db.run(query); | |
332 | }); | |
333 | }, | |
d431028c BA |
334 | |
335 | cleanGamesDb: function() | |
336 | { | |
337 | const tsNow = Date.now(); | |
338 | // 86400000 = 24 hours in milliseconds | |
339 | const day = 86400000; | |
340 | db.serialize(function() { | |
341 | let query = | |
0f7011a2 | 342 | "SELECT id, created " + |
d431028c BA |
343 | "FROM Games "; |
344 | db.all(query, (err,games) => { | |
345 | games.forEach(g => { | |
346 | query = | |
fd41e587 | 347 | "SELECT count(*) as nbMoves, max(played) AS lastMaj " + |
d431028c BA |
348 | "FROM Moves " + |
349 | "WHERE gid = " + g.id; | |
fd41e587 BA |
350 | db.get(query, (err2,mstats) => { |
351 | // Remove games still not really started, | |
352 | // with no action in the last 3 months: | |
353 | if ((mstats.nbMoves == 0 && tsNow - g.created > 91*day) || | |
354 | (mstats.nbMoves == 1 && tsNow - mstats.lastMaj > 91*day)) | |
d431028c | 355 | { |
89021f18 | 356 | GameModel.remove(g.id); |
d431028c BA |
357 | } |
358 | }); | |
359 | }); | |
360 | }); | |
361 | }); | |
362 | }, | |
00f2759e | 363 | } |
ab4f4bf2 BA |
364 | |
365 | module.exports = GameModel; |