-Après ajout d'un problème, passer display à "mine"
-Résoudre le pb "no more problems" when navigating after loading one pb
+Use better-sqlite3 instead of node-sqlite3:
+https://www.npmjs.com/package/better-sqlite3
+
+CRON task remove unlogged users, finished corr games after 7 days, individual challenges older than 7 days
tell opponent that I got the move, for him to start timer (and lose...)
--> no, not needed and impossible if everybody is offline
added datetime,
uid integer,
vid integer,
+ nbPlayers integer,
foreign key (uid) references Users(id),
foreign key (vid) references Variants(id)
);
create table WillPlay (
cid integer,
uid integer,
- yes boolean,
foreign key (cid) references Challenges(id),
foreign key (uid) references Users(id)
);
id integer primary key,
vid integer,
fen varchar, --initial position
- score varchar,
+ score varchar default '*',
foreign key (vid) references Variants(id)
);
var db = require("../utils/database");
/*
- * Structure:
- * _id: BSON id
- * vid: variant ID
- * from: player ID
- * to: player ID, undefined if automatch
+ * Structure table Challenges:
+ * id: integer
+ * added: datetime
+ * uid: user id (int)
+ * vid: variant id (int)
+ * nbPlayers: integer
+ *
+ * Structure table WillPlay:
+ * cid: ref challenge id
+ * uid: ref user id
*/
-exports.create = function(vid, from, to, callback)
+exports.create = function(uid, vid, nbPlayers, cb)
{
- let chall = {
- "vid": vid,
- "from": from
- };
- if (!!to)
- chall.to = to;
- db.challenges.insert(chall, callback);
-}
-
-//////////
-// GETTERS
-
-exports.getById = function(cid, callback)
-{
- db.challenges.findOne({_id: cid}, callback);
-}
-
-// For index page: obtain challenges that the player can accept
-exports.getByPlayer = function(uid, callback)
-{
- db.challenges.aggregate(
- {$match: {$or: [
- {"to": uid},
- {$and: [{"from": {$ne: uid}}, {"to": {$exists: false}}]}
- ]}},
- {$project: {_id:0, vid:1}},
- {$group: {_id:"$vid", count:{$sum:1}}},
- callback);
-}
-
-// For variant page (challenges related to a player)
-exports.getByVariant = function(uid, vid, callback)
-{
- db.challenges.find({$and: [
- {"vid": vid},
- {$or: [
- {"to": uid},
- {"from": uid},
- {"to": {$exists: false}},
- ]}
- ]}, callback);
+ db.serialize({
+ let query =
+ "INSERT INTO Challenges (added, uid, vid, nbPlayers) " +
+ "VALUES (" + Date.now() + "," + uid + "," + vid + "," + nbPlayers + ")";
+ db.run(insertQuery, err => {
+ if (!!err)
+ return cb(err);
+ db.get("SELECT last_insert_rowid() AS rowid", (err2,lastId) => {
+ query =
+ "INSERT INTO WillPlay VALUES " +
+ "(" + lastId["rowid"] + "," + uid + ")";
+ db.run(query, cb);
+ });
+ });
+ });
+ });
}
-//////////
-// REMOVAL
-
-exports.remove = function(cid, callback)
+exports.getOne = function(id, cb)
{
- db.challenges.remove({_id: cid}, callback);
+ db.serialize(function() {
+ let query =
+ "SELECT * " +
+ "FROM Challenges c " +
+ "JOIN Variants v " +
+ " ON c.vid = v.id "
+ "WHERE id = " + id;
+ db.get(query, (err,challengeInfo) => {
+ if (!!err)
+ return cb(err);
+ query =
+ "SELECT w.uid AS id, u.name " +
+ "FROM WillPlay w " +
+ "JOIN Users u " +
+ " ON w.uid = u.id " +
+ "WHERE w.cid = " + id;
+ db.run(query, (err2,players) => {
+ if (!!err2)
+ return cb(err2);
+ const challenge = {
+ id: id,
+ vname: challengeInfo.name,
+ added: challengeInfo.added,
+ nbPlayers: challengeInfo.nbPlayers,
+ players: players, //currently in
+ };
+ return cb(null, challenge);
+ });
+ });
+ });
}
-// Remove challenges older than 1 month, and 1to1 older than 36h
-exports.removeOld = function()
+exports.remove = function(id)
{
- var tsNow = new Date().getTime();
- // 86400000 = 24 hours in milliseconds
- var day = 86400000;
- db.challenges.find({}, (err,challengeArray) => {
- challengeArray.forEach( c => {
- if (c._id.getTimestamp() + 30*day < tsNow //automatch
- || (!!c.to && c._id.getTimestamp() + 1.5*day < tsNow)) //1 to 1
- {
- db.challenges.remove({"_id": c._id});
- }
- });
+ db.parallelize(function() {
+ let query =
+ "DELETE FROM Challenges " +
+ "WHERE id = " + id;
+ db.run(query);
+ query =
+ "DELETE FROM WillPlay " +
+ "WHERE cid = " + id;
+ db.run(query);
});
}
-//TODO:
+var db = require("../utils/database");
+
+/*
+ * Structure table Games:
+ * id: game id (int)
+ * vid: integer (variant id)
+ * fen: varchar (initial position)
+ * mainTime: integer
+ * increment: integer
+ * score: varchar (result)
+ *
+ * Structure table Players:
+ * gid: ref game id
+ * uid: ref user id
+ * color: character
+ *
+ * Structure table Moves:
+ * move: varchar (description)
+ * played: datetime
+ * idx: integer
+ * color: character
+ */
+
+exports.create = function(vid, fen, mainTime, increment, players, cb)
+{
+ db.serialize({
+ let query =
+ "INSERT INTO Games (vid, fen, mainTime, increment) " +
+ "VALUES (" + vid + ",'" + fen + "'," + mainTime + "," + increment + ")";
+ db.run(insertQuery, err => {
+ if (!!err)
+ return cb(err);
+ db.get("SELECT last_insert_rowid() AS rowid", (err2,lastId) => {
+ players.forEach(p => {
+ query =
+ "INSERT INTO Players VALUES " +
+ "(" + lastId["rowid"] + "," + p.id + "," + p.color + ")";
+ db.run(query, cb);
+ });
+ });
+ });
+ });
+}
+
+// TODO: queries here could be async, and wait for all to complete
+exports.getOne = function(id, cb)
+{
+ db.serialize(function() {
+ let query =
+ "SELECT v.name AS vname, g.fen, g.score " +
+ "FROM Games g " +
+ "JOIN Variants v " +
+ " ON g.vid = v.id "
+ "WHERE id = " + id;
+ db.get(query, (err,gameInfo) => {
+ if (!!err)
+ return cb(err);
+ query =
+ "SELECT p.uid AS id, p.color, u.name " +
+ "FROM Players p " +
+ "JOIN Users u " +
+ " ON p.uid = u.id " +
+ "WHERE p.gid = " + id;
+ db.run(query, (err2,players) => {
+ if (!!err2)
+ return cb(err2);
+ query =
+ "SELECT move AS desc, played, idx, color " +
+ "FROM Moves " +
+ "WHERE gid = " + id;
+ db.run(query, (err3,moves) => {
+ if (!!err3)
+ return cb(err3);
+ const game = {
+ id: id,
+ vname: gameInfo.vname,
+ fen: gameInfo.fen,
+ score: gameInfo.score,
+ players: players,
+ moves: moves,
+ };
+ return cb(null, game);
+ });
+ });
+ });
+ });
+}
+
+exports.remove = function(id)
+{
+ db.parallelize(function() {
+ let query =
+ "DELETE FROM Games " +
+ "WHERE id = " + id;
+ db.run(query);
+ query =
+ "DELETE FROM Players " +
+ "WHERE gid = " + id;
+ db.run(query);
+ query =
+ "DELETE FROM Moves " +
+ "WHERE gid = " + id;
+ db.run(query);
+ });
+}
return cb(err);
db.get("SELECT last_insert_rowid() AS rowid", cb);
});
-// const stmt = db.prepare(query);
-// stmt.run(instructions, solution);
-// stmt.finalize();
});
}
Vue.component("my-correspondance", {
- //TODO
template: `
<div class="col-sm-12 col-md-10 col-md-offset-1 col-lg-8 col-lg-offset-2">
<p>TODO: load from server, show timeControl + players + link "play"</p>
<p>Also tab for current challenges + button "new game"</p>
</div>
`,
+ created: function() {
+ //TODO
+ },
});
router.use("/", require("./index"));
router.use("/", require("./users"));
router.use("/", require("./messages"));
-//router.use("/", require("./playing"));
+//router.use("/", require("./games"));
//router.use("/", require("./challenge"));
router.use("/", require("./problems"));
router.use("/", require("./variant"));
});
});
-// TODO: if newmove fail, takeback in GUI // TODO: check move structure
+// TODO: if newmove fail, takeback in GUI
+// TODO: check move structure
// TODO: for corr games, move should contain an optional "message" field ("corr chat" !)
router.post("/moves", access.logged, access.ajax, (req,res) => {
let gid = ObjectId(req.body.gid);
projects / vchess.git / commitdiff