Commit | Line | Data |
---|---|---|
00f2759e BA |
1 | var db = require("../utils/database"); |
2 | ||
3 | /* | |
4 | * Structure table Games: | |
5 | * id: game id (int) | |
6 | * vid: integer (variant id) | |
ab4f4bf2 BA |
7 | * fenStart: varchar (initial position) |
8 | * fen: varchar (current position) | |
a9b131f1 | 9 | * timeControl: string |
00f2759e | 10 | * score: varchar (result) |
83494c7f | 11 | * created: datetime |
00f2759e BA |
12 | * |
13 | * Structure table Players: | |
14 | * gid: ref game id | |
15 | * uid: ref user id | |
16 | * color: character | |
17 | * | |
18 | * Structure table Moves: | |
ab4f4bf2 | 19 | * gid: ref game id |
f41ce580 | 20 | * squares: varchar (description) |
00f2759e BA |
21 | * played: datetime |
22 | * idx: integer | |
3837d4f7 BA |
23 | * |
24 | * Structure table Chats: | |
25 | * gid: game id (int) | |
26 | * msg: varchar | |
27 | * name: varchar | |
28 | * sid: varchar (socket ID when sending message) | |
29 | * added: datetime | |
00f2759e BA |
30 | */ |
31 | ||
ab4f4bf2 | 32 | const GameModel = |
00f2759e | 33 | { |
a9b131f1 | 34 | create: function(vid, fen, timeControl, players, cb) |
ab4f4bf2 | 35 | { |
88667455 | 36 | db.serialize(function() { |
ab4f4bf2 | 37 | let query = |
b7cbbda1 BA |
38 | "INSERT INTO Games" |
39 | + " (vid, fenStart, fen, score, timeControl, created, drawOffer)" | |
d431028c | 40 | + " VALUES (" + vid + ",'" + fen + "','" + fen + "','*','" |
b7cbbda1 | 41 | + timeControl + "'," + Date.now() + "," + false + ")"; |
8c564f46 | 42 | db.run(query, function(err) { |
cf742aaf BA |
43 | if (!!err) |
44 | return cb(err); | |
8c564f46 BA |
45 | players.forEach((p,idx) => { |
46 | const color = (idx==0 ? "w" : "b"); | |
2be5d614 BA |
47 | query = |
48 | "INSERT INTO Players VALUES " + | |
f41ce580 | 49 | "(" + this.lastID + "," + p.id + ",'" + color + "')"; |
2be5d614 BA |
50 | db.run(query); |
51 | }); | |
52 | cb(null, {gid: this.lastID}); | |
00f2759e BA |
53 | }); |
54 | }); | |
ab4f4bf2 | 55 | }, |
00f2759e | 56 | |
ab4f4bf2 BA |
57 | // TODO: queries here could be async, and wait for all to complete |
58 | getOne: function(id, cb) | |
59 | { | |
60 | db.serialize(function() { | |
f41ce580 | 61 | // TODO: optimize queries? |
ab4f4bf2 | 62 | let query = |
f41ce580 BA |
63 | "SELECT g.id, g.vid, g.fen, g.fenStart, g.timeControl, g.score, " + |
64 | "v.name AS vname " + | |
65 | "FROM Games g " + | |
66 | "JOIN Variants v " + | |
67 | " ON g.vid = v.id " + | |
68 | "WHERE g.id = " + id; | |
ab4f4bf2 BA |
69 | db.get(query, (err,gameInfo) => { |
70 | if (!!err) | |
71 | return cb(err); | |
00f2759e | 72 | query = |
f41ce580 BA |
73 | "SELECT p.uid, p.color, u.name " + |
74 | "FROM Players p " + | |
75 | "JOIN Users u " + | |
76 | " ON p.uid = u.id " + | |
77 | "WHERE p.gid = " + id; | |
fd7aea36 | 78 | db.all(query, (err2,players) => { |
ab4f4bf2 BA |
79 | if (!!err2) |
80 | return cb(err2); | |
81 | query = | |
3837d4f7 | 82 | "SELECT squares, played, idx " + |
ab4f4bf2 BA |
83 | "FROM Moves " + |
84 | "WHERE gid = " + id; | |
fd7aea36 | 85 | db.all(query, (err3,moves) => { |
ab4f4bf2 BA |
86 | if (!!err3) |
87 | return cb(err3); | |
3837d4f7 BA |
88 | query = |
89 | "SELECT msg, name, sid, added " + | |
90 | "FROM Chats " + | |
91 | "WHERE gid = " + id; | |
92 | db.all(query, (err4,chats) => { | |
93 | if (!!err4) | |
94 | return cb(err4); | |
95 | const game = Object.assign({}, | |
96 | gameInfo, | |
97 | { | |
98 | players: players, | |
99 | moves: moves, | |
100 | chats: chats, | |
101 | } | |
102 | ); | |
103 | return cb(null, game); | |
104 | }); | |
ab4f4bf2 | 105 | }); |
00f2759e BA |
106 | }); |
107 | }); | |
108 | }); | |
ab4f4bf2 | 109 | }, |
00f2759e | 110 | |
5d04793e | 111 | getByUser: function(uid, excluded, cb) |
ab4f4bf2 BA |
112 | { |
113 | db.serialize(function() { | |
114 | // Next query is fine because a player appear at most once in a game | |
115 | const query = | |
116 | "SELECT gid " + | |
117 | "FROM Players " + | |
5d04793e | 118 | "WHERE uid " + (excluded ? "<>" : "=") + " " + uid; |
afd3240d | 119 | db.all(query, (err,gameIds) => { |
ab4f4bf2 BA |
120 | if (!!err) |
121 | return cb(err); | |
098cd7f1 | 122 | gameIds = gameIds || []; //might be empty |
ab4f4bf2 | 123 | let gameArray = []; |
afd3240d BA |
124 | for (let i=0; i<gameIds.length; i++) |
125 | { | |
126 | GameModel.getOne(gameIds[i]["gid"], (err2,game) => { | |
ab4f4bf2 BA |
127 | if (!!err2) |
128 | return cb(err2); | |
129 | gameArray.push(game); | |
afd3240d BA |
130 | // Call callback function only when gameArray is complete: |
131 | if (i == gameIds.length - 1) | |
132 | return cb(null, gameArray); | |
ab4f4bf2 | 133 | }); |
afd3240d | 134 | } |
ab4f4bf2 BA |
135 | }); |
136 | }); | |
137 | }, | |
138 | ||
411d23cd BA |
139 | getPlayers: function(id, cb) |
140 | { | |
141 | db.serialize(function() { | |
142 | const query = | |
143 | "SELECT id " + | |
144 | "FROM Players " + | |
145 | "WHERE gid = " + id; | |
146 | db.all(query, (err,players) => { | |
147 | return cb(err, players); | |
148 | }); | |
149 | }); | |
150 | }, | |
151 | ||
3837d4f7 | 152 | // obj can have fields move, chat, fen, drawOffer and/or score |
f41ce580 | 153 | update: function(id, obj) |
3d55deea | 154 | { |
f41ce580 | 155 | db.parallelize(function() { |
3d55deea BA |
156 | let query = |
157 | "UPDATE Games " + | |
158 | "SET "; | |
3837d4f7 | 159 | let modifs = ""; |
63ca2b89 | 160 | if (!!obj.message) |
3837d4f7 | 161 | modifs += "message = message || ' ' || '" + obj.message + "',"; |
b7cbbda1 | 162 | if (!!obj.drawOffer) |
3837d4f7 | 163 | modifs += "drawOffer = " + obj.drawOffer + ","; |
3d55deea | 164 | if (!!obj.fen) |
3837d4f7 | 165 | modifs += "fen = '" + obj.fen + "',"; |
3d55deea | 166 | if (!!obj.score) |
3837d4f7 BA |
167 | modifs += "score = '" + obj.score + "',"; |
168 | modifs = modifs.slice(0,-1); //remove last comma | |
169 | if (modifs.length > 0) | |
170 | { | |
171 | query += modifs + " WHERE id = " + id; | |
172 | db.run(query); | |
173 | } | |
f41ce580 BA |
174 | if (!!obj.move) |
175 | { | |
afc42601 | 176 | const m = obj.move; |
f41ce580 | 177 | query = |
63ca2b89 BA |
178 | "INSERT INTO Moves (gid, squares, played, idx) VALUES " + |
179 | "(" + id + ",'" + JSON.stringify(m.squares) + "'," | |
180 | + m.played + "," + m.idx + ")"; | |
f41ce580 BA |
181 | db.run(query); |
182 | } | |
3837d4f7 BA |
183 | if (!!obj.chat) |
184 | { | |
185 | query = | |
186 | "INSERT INTO Chats (gid, msg, name, sid, added) VALUES " + | |
187 | "(" + id + ",'" + obj.chat.msg + "','" + obj.chat.name + | |
188 | "','" + obj.chat.sid + "'," + Date.now() + ")"; | |
189 | db.run(query); | |
190 | } | |
3d55deea BA |
191 | }); |
192 | }, | |
193 | ||
ab4f4bf2 BA |
194 | remove: function(id) |
195 | { | |
196 | db.parallelize(function() { | |
197 | let query = | |
198 | "DELETE FROM Games " + | |
199 | "WHERE id = " + id; | |
200 | db.run(query); | |
201 | query = | |
202 | "DELETE FROM Players " + | |
203 | "WHERE gid = " + id; | |
204 | db.run(query); | |
205 | query = | |
206 | "DELETE FROM Moves " + | |
207 | "WHERE gid = " + id; | |
208 | db.run(query); | |
3837d4f7 BA |
209 | query = |
210 | "DELETE FROM Chats " + | |
211 | "WHERE gid = " + id; | |
212 | db.run(query); | |
ab4f4bf2 BA |
213 | }); |
214 | }, | |
d431028c BA |
215 | |
216 | cleanGamesDb: function() | |
217 | { | |
218 | const tsNow = Date.now(); | |
219 | // 86400000 = 24 hours in milliseconds | |
220 | const day = 86400000; | |
221 | db.serialize(function() { | |
222 | let query = | |
223 | "SELECT id,score " + | |
224 | "FROM Games "; | |
225 | db.all(query, (err,games) => { | |
226 | games.forEach(g => { | |
227 | query = | |
228 | "SELECT max(played) AS lastMaj " + | |
229 | "FROM Moves " + | |
230 | "WHERE gid = " + g.id; | |
f21cd6d9 | 231 | db.get(query, (err2,updated) => { |
d431028c BA |
232 | if (!updated && tsNow - g.created > 7*day) |
233 | return GameModel.remove(g.id); | |
234 | const lastMaj = updated.lastMaj; | |
235 | if (g.score != "*" && tsNow - lastMaj > 7*day || | |
236 | g.score == "*" && tsNow - lastMaj > 91*day) | |
237 | { | |
238 | GameModel.remove(g.id); | |
239 | } | |
240 | }); | |
241 | }); | |
242 | }); | |
243 | }); | |
244 | }, | |
00f2759e | 245 | } |
ab4f4bf2 BA |
246 | |
247 | module.exports = GameModel; |