1 var db
= require("../utils/database");
4 * Structure table Games:
6 * vid: integer (variant id)
7 * fenStart: varchar (initial position)
8 * fen: varchar (current position)
10 * score: varchar (result)
13 * Structure table Players:
18 * Structure table Moves:
20 * squares: varchar (description)
24 * Structure table Chats:
28 * sid: varchar (socket ID when sending message)
34 create: function(vid
, fen
, timeControl
, players
, cb
)
36 db
.serialize(function() {
39 + " (vid, fenStart, fen, score, timeControl, created, drawOffer)"
40 + " VALUES (" + vid
+ ",'" + fen
+ "','" + fen
+ "','*','"
41 + timeControl
+ "'," + Date
.now() + "," + false + ")";
42 db
.run(query
, function(err
) {
45 players
.forEach((p
,idx
) => {
46 const color
= (idx
==0 ? "w" : "b");
48 "INSERT INTO Players VALUES " +
49 "(" + this.lastID
+ "," + p
.id
+ ",'" + color
+ "')";
52 cb(null, {gid: this.lastID
});
57 // TODO: queries here could be async, and wait for all to complete
58 getOne: function(id
, cb
)
60 db
.serialize(function() {
61 // TODO: optimize queries?
63 "SELECT g.id, g.vid, g.fen, g.fenStart, g.timeControl, g.score, " +
69 db
.get(query
, (err
,gameInfo
) => {
73 "SELECT p.uid, p.color, u.name " +
77 "WHERE p.gid = " + id
;
78 db
.all(query
, (err2
,players
) => {
82 "SELECT squares, played, idx " +
85 db
.all(query
, (err3
,moves
) => {
89 "SELECT msg, name, sid, added " +
92 db
.all(query
, (err4
,chats
) => {
95 const game
= Object
.assign({},
103 return cb(null, game
);
111 getByUser: function(uid
, excluded
, cb
)
113 db
.serialize(function() {
114 // Next query is fine because a player appear at most once in a game
118 "WHERE uid " + (excluded
? "<>" : "=") + " " + uid
;
119 db
.all(query
, (err
,gameIds
) => {
122 gameIds
= gameIds
|| []; //might be empty
124 for (let i
=0; i
<gameIds
.length
; i
++)
126 GameModel
.getOne(gameIds
[i
]["gid"], (err2
,game
) => {
129 gameArray
.push(game
);
130 // Call callback function only when gameArray is complete:
131 if (i
== gameIds
.length
- 1)
132 return cb(null, gameArray
);
139 getPlayers: function(id
, cb
)
141 db
.serialize(function() {
146 db
.all(query
, (err
,players
) => {
147 return cb(err
, players
);
152 // obj can have fields move, chat, fen, drawOffer and/or score
153 update: function(id
, obj
)
155 db
.parallelize(function() {
161 modifs
+= "message = message || ' ' || '" + obj
.message
+ "',";
163 modifs
+= "drawOffer = " + obj
.drawOffer
+ ",";
165 modifs
+= "fen = '" + obj
.fen
+ "',";
167 modifs
+= "score = '" + obj
.score
+ "',";
168 modifs
= modifs
.slice(0,-1); //remove last comma
169 if (modifs
.length
> 0)
171 query
+= modifs
+ " WHERE id = " + id
;
178 "INSERT INTO Moves (gid, squares, played, idx) VALUES " +
179 "(" + id
+ ",'" + JSON
.stringify(m
.squares
) + "',"
180 + m
.played
+ "," + m
.idx
+ ")";
186 "INSERT INTO Chats (gid, msg, name, sid, added) VALUES " +
187 "(" + id
+ ",'" + obj
.chat
.msg
+ "','" + obj
.chat
.name
+
188 "','" + obj
.chat
.sid
+ "'," + Date
.now() + ")";
196 db
.parallelize(function() {
198 "DELETE FROM Games " +
202 "DELETE FROM Players " +
206 "DELETE FROM Moves " +
210 "DELETE FROM Chats " +
216 cleanGamesDb: function()
218 const tsNow
= Date
.now();
219 // 86400000 = 24 hours in milliseconds
220 const day
= 86400000;
221 db
.serialize(function() {
225 db
.all(query
, (err
,games
) => {
228 "SELECT max(played) AS lastMaj " +
230 "WHERE gid = " + g
.id
;
231 db
.get(query
, (err2
,updated
) => {
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
)
238 GameModel
.remove(g
.id
);
247 module
.exports
= GameModel
;