Commit | Line | Data |
---|---|---|
00f2759e | 1 | var 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) | |
a9b131f1 | 10 | * timeControl: string |
00f2759e | 11 | * score: varchar (result) |
dcd68c41 | 12 | * scoreMsg: varchar ("Time", "Mutual agreement"...) |
83494c7f | 13 | * created: datetime |
58e7b94e | 14 | * drawOffer: boolean |
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 BA |
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"; | |
51 | return ""; | |
52 | }, | |
53 | ||
a9b131f1 | 54 | create: function(vid, fen, timeControl, players, cb) |
ab4f4bf2 | 55 | { |
88667455 | 56 | db.serialize(function() { |
ab4f4bf2 | 57 | let query = |
b7cbbda1 BA |
58 | "INSERT INTO Games" |
59 | + " (vid, fenStart, fen, score, timeControl, created, drawOffer)" | |
d431028c | 60 | + " VALUES (" + vid + ",'" + fen + "','" + fen + "','*','" |
b7cbbda1 | 61 | + timeControl + "'," + Date.now() + "," + false + ")"; |
8c564f46 | 62 | db.run(query, function(err) { |
cf742aaf BA |
63 | if (!!err) |
64 | return cb(err); | |
8c564f46 BA |
65 | players.forEach((p,idx) => { |
66 | const color = (idx==0 ? "w" : "b"); | |
2be5d614 BA |
67 | query = |
68 | "INSERT INTO Players VALUES " + | |
f41ce580 | 69 | "(" + this.lastID + "," + p.id + ",'" + color + "')"; |
2be5d614 BA |
70 | db.run(query); |
71 | }); | |
72 | cb(null, {gid: this.lastID}); | |
00f2759e BA |
73 | }); |
74 | }); | |
ab4f4bf2 | 75 | }, |
00f2759e | 76 | |
ab4f4bf2 BA |
77 | // TODO: queries here could be async, and wait for all to complete |
78 | getOne: function(id, cb) | |
79 | { | |
80 | db.serialize(function() { | |
f41ce580 | 81 | // TODO: optimize queries? |
ab4f4bf2 | 82 | let query = |
dcd68c41 BA |
83 | // NOTE: g.scoreMsg can be NULL |
84 | // (in this case score = "*" and no reason to look at it) | |
f41ce580 | 85 | "SELECT g.id, g.vid, g.fen, g.fenStart, g.timeControl, g.score, " + |
dcd68c41 | 86 | "g.scoreMsg, v.name AS vname " + |
f41ce580 BA |
87 | "FROM Games g " + |
88 | "JOIN Variants v " + | |
89 | " ON g.vid = v.id " + | |
90 | "WHERE g.id = " + id; | |
ab4f4bf2 BA |
91 | db.get(query, (err,gameInfo) => { |
92 | if (!!err) | |
93 | return cb(err); | |
00f2759e | 94 | query = |
f41ce580 BA |
95 | "SELECT p.uid, p.color, u.name " + |
96 | "FROM Players p " + | |
97 | "JOIN Users u " + | |
98 | " ON p.uid = u.id " + | |
99 | "WHERE p.gid = " + id; | |
fd7aea36 | 100 | db.all(query, (err2,players) => { |
ab4f4bf2 BA |
101 | if (!!err2) |
102 | return cb(err2); | |
103 | query = | |
3837d4f7 | 104 | "SELECT squares, played, idx " + |
ab4f4bf2 BA |
105 | "FROM Moves " + |
106 | "WHERE gid = " + id; | |
fd7aea36 | 107 | db.all(query, (err3,moves) => { |
ab4f4bf2 BA |
108 | if (!!err3) |
109 | return cb(err3); | |
3837d4f7 | 110 | query = |
dcd68c41 | 111 | "SELECT msg, name, added " + |
3837d4f7 BA |
112 | "FROM Chats " + |
113 | "WHERE gid = " + id; | |
114 | db.all(query, (err4,chats) => { | |
115 | if (!!err4) | |
116 | return cb(err4); | |
117 | const game = Object.assign({}, | |
118 | gameInfo, | |
119 | { | |
120 | players: players, | |
121 | moves: moves, | |
122 | chats: chats, | |
123 | } | |
124 | ); | |
125 | return cb(null, game); | |
126 | }); | |
ab4f4bf2 | 127 | }); |
00f2759e BA |
128 | }); |
129 | }); | |
130 | }); | |
ab4f4bf2 | 131 | }, |
00f2759e | 132 | |
5d04793e | 133 | getByUser: function(uid, excluded, cb) |
ab4f4bf2 BA |
134 | { |
135 | db.serialize(function() { | |
136 | // Next query is fine because a player appear at most once in a game | |
137 | const query = | |
138 | "SELECT gid " + | |
139 | "FROM Players " + | |
5d04793e | 140 | "WHERE uid " + (excluded ? "<>" : "=") + " " + uid; |
afd3240d | 141 | db.all(query, (err,gameIds) => { |
ab4f4bf2 BA |
142 | if (!!err) |
143 | return cb(err); | |
098cd7f1 | 144 | gameIds = gameIds || []; //might be empty |
ab4f4bf2 | 145 | let gameArray = []; |
afd3240d BA |
146 | for (let i=0; i<gameIds.length; i++) |
147 | { | |
148 | GameModel.getOne(gameIds[i]["gid"], (err2,game) => { | |
ab4f4bf2 BA |
149 | if (!!err2) |
150 | return cb(err2); | |
151 | gameArray.push(game); | |
afd3240d BA |
152 | // Call callback function only when gameArray is complete: |
153 | if (i == gameIds.length - 1) | |
154 | return cb(null, gameArray); | |
ab4f4bf2 | 155 | }); |
afd3240d | 156 | } |
ab4f4bf2 BA |
157 | }); |
158 | }); | |
159 | }, | |
160 | ||
411d23cd BA |
161 | getPlayers: function(id, cb) |
162 | { | |
163 | db.serialize(function() { | |
164 | const query = | |
165 | "SELECT id " + | |
166 | "FROM Players " + | |
167 | "WHERE gid = " + id; | |
168 | db.all(query, (err,players) => { | |
169 | return cb(err, players); | |
170 | }); | |
171 | }); | |
172 | }, | |
173 | ||
58e7b94e BA |
174 | checkGameUpdate: function(obj) |
175 | { | |
176 | // Check all that is possible (required) in obj: | |
177 | if (!!obj.move) | |
178 | { | |
179 | if (!obj.move.played.toString().match(/^[0-9]+$/)) | |
180 | return "Wrong move played time"; | |
181 | if (!obj.move.idx.toString().match(/^[0-9]+$/)) | |
182 | return "Wrong move index"; | |
183 | } | |
184 | if (!!obj.fen && !obj.fen.match(/^[a-zA-Z0-9, /-]*$/)) | |
185 | return "Wrong FEN string"; | |
186 | if (!!obj.score && !obj.score.match(/^[012?*\/-]+$/)) | |
187 | return "Wrong characters in score"; | |
dcd68c41 BA |
188 | if (!!obj.scoreMsg && !obj.scoreMsg.match(/^[a-zA-Z ]+$/)) |
189 | return "Wrong characters in score message"; | |
58e7b94e | 190 | if (!!obj.chat) |
58e7b94e | 191 | return UserModel.checkNameEmail({name: obj.chat.name}); |
58e7b94e BA |
192 | return ""; |
193 | }, | |
194 | ||
3837d4f7 | 195 | // obj can have fields move, chat, fen, drawOffer and/or score |
f41ce580 | 196 | update: function(id, obj) |
3d55deea | 197 | { |
f41ce580 | 198 | db.parallelize(function() { |
3d55deea BA |
199 | let query = |
200 | "UPDATE Games " + | |
201 | "SET "; | |
3837d4f7 | 202 | let modifs = ""; |
63ca2b89 | 203 | if (!!obj.message) |
3837d4f7 | 204 | modifs += "message = message || ' ' || '" + obj.message + "',"; |
58e7b94e | 205 | if ([true,false].includes(obj.drawOffer)) |
3837d4f7 | 206 | modifs += "drawOffer = " + obj.drawOffer + ","; |
3d55deea | 207 | if (!!obj.fen) |
3837d4f7 | 208 | modifs += "fen = '" + obj.fen + "',"; |
3d55deea | 209 | if (!!obj.score) |
3837d4f7 | 210 | modifs += "score = '" + obj.score + "',"; |
dcd68c41 BA |
211 | if (!!obj.scoreMsg) |
212 | modifs += "scoreMsg = '" + obj.scoreMsg + "',"; | |
3837d4f7 BA |
213 | modifs = modifs.slice(0,-1); //remove last comma |
214 | if (modifs.length > 0) | |
215 | { | |
216 | query += modifs + " WHERE id = " + id; | |
217 | db.run(query); | |
218 | } | |
f41ce580 BA |
219 | if (!!obj.move) |
220 | { | |
afc42601 | 221 | const m = obj.move; |
f41ce580 | 222 | query = |
63ca2b89 | 223 | "INSERT INTO Moves (gid, squares, played, idx) VALUES " + |
58e7b94e BA |
224 | "(" + id + ",?," + m.played + "," + m.idx + ")"; |
225 | db.run(query, JSON.stringify(m.squares)); | |
f41ce580 | 226 | } |
3837d4f7 BA |
227 | if (!!obj.chat) |
228 | { | |
229 | query = | |
dcd68c41 BA |
230 | "INSERT INTO Chats (gid, msg, name, added) VALUES (" |
231 | + id + ",?,'" + obj.chat.name + "'," + "," + Date.now() + ")"; | |
58e7b94e | 232 | db.run(query, obj.chat.msg); |
3837d4f7 | 233 | } |
3d55deea BA |
234 | }); |
235 | }, | |
236 | ||
ab4f4bf2 BA |
237 | remove: function(id) |
238 | { | |
239 | db.parallelize(function() { | |
240 | let query = | |
241 | "DELETE FROM Games " + | |
242 | "WHERE id = " + id; | |
243 | db.run(query); | |
244 | query = | |
245 | "DELETE FROM Players " + | |
246 | "WHERE gid = " + id; | |
247 | db.run(query); | |
248 | query = | |
249 | "DELETE FROM Moves " + | |
250 | "WHERE gid = " + id; | |
251 | db.run(query); | |
3837d4f7 BA |
252 | query = |
253 | "DELETE FROM Chats " + | |
254 | "WHERE gid = " + id; | |
255 | db.run(query); | |
ab4f4bf2 BA |
256 | }); |
257 | }, | |
d431028c BA |
258 | |
259 | cleanGamesDb: function() | |
260 | { | |
261 | const tsNow = Date.now(); | |
262 | // 86400000 = 24 hours in milliseconds | |
263 | const day = 86400000; | |
264 | db.serialize(function() { | |
265 | let query = | |
266 | "SELECT id,score " + | |
267 | "FROM Games "; | |
268 | db.all(query, (err,games) => { | |
269 | games.forEach(g => { | |
270 | query = | |
271 | "SELECT max(played) AS lastMaj " + | |
272 | "FROM Moves " + | |
273 | "WHERE gid = " + g.id; | |
f21cd6d9 | 274 | db.get(query, (err2,updated) => { |
d431028c BA |
275 | if (!updated && tsNow - g.created > 7*day) |
276 | return GameModel.remove(g.id); | |
277 | const lastMaj = updated.lastMaj; | |
278 | if (g.score != "*" && tsNow - lastMaj > 7*day || | |
279 | g.score == "*" && tsNow - lastMaj > 91*day) | |
280 | { | |
281 | GameModel.remove(g.id); | |
282 | } | |
283 | }); | |
284 | }); | |
285 | }); | |
286 | }); | |
287 | }, | |
00f2759e | 288 | } |
ab4f4bf2 BA |
289 | |
290 | module.exports = GameModel; |