From 00f2759e16ec73fa1ecd0254a9c9018530d71892 Mon Sep 17 00:00:00 2001 From: Benjamin Auder Date: Fri, 18 Jan 2019 13:11:32 +0100 Subject: [PATCH] Draft Game and Challenge models --- _tmp/TODO | 6 +- db/create.sql | 4 +- models/Challenge.js | 133 +++++++++--------- models/Game.js | 106 +++++++++++++- models/Problem.js | 3 - .../javascripts/components/correspondance.js | 4 +- routes/all.js | 2 +- routes/{playing.js => games.js} | 3 +- 8 files changed, 185 insertions(+), 76 deletions(-) rename routes/{playing.js => games.js} (98%) diff --git a/_tmp/TODO b/_tmp/TODO index fcc2b42b..868d4d3e 100644 --- a/_tmp/TODO +++ b/_tmp/TODO @@ -1,5 +1,7 @@ -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 diff --git a/db/create.sql b/db/create.sql index 5f463bfc..75e1b1cd 100644 --- a/db/create.sql +++ b/db/create.sql @@ -36,6 +36,7 @@ create table Challenges ( added datetime, uid integer, vid integer, + nbPlayers integer, foreign key (uid) references Users(id), foreign key (vid) references Variants(id) ); @@ -44,7 +45,6 @@ create table Challenges ( create table WillPlay ( cid integer, uid integer, - yes boolean, foreign key (cid) references Challenges(id), foreign key (uid) references Users(id) ); @@ -53,7 +53,7 @@ create table Games ( id integer primary key, vid integer, fen varchar, --initial position - score varchar, + score varchar default '*', foreign key (vid) references Variants(id) ); diff --git a/models/Challenge.js b/models/Challenge.js index 9980921c..adb40224 100644 --- a/models/Challenge.js +++ b/models/Challenge.js @@ -1,79 +1,82 @@ 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); }); } diff --git a/models/Game.js b/models/Game.js index d279514f..72f1ea89 100644 --- a/models/Game.js +++ b/models/Game.js @@ -1 +1,105 @@ -//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); + }); +} diff --git a/models/Problem.js b/models/Problem.js index 99e5b620..8f3a302c 100644 --- a/models/Problem.js +++ b/models/Problem.js @@ -21,9 +21,6 @@ exports.create = function(uid, vid, fen, instructions, solution, cb) return cb(err); db.get("SELECT last_insert_rowid() AS rowid", cb); }); -// const stmt = db.prepare(query); -// stmt.run(instructions, solution); -// stmt.finalize(); }); } diff --git a/public/javascripts/components/correspondance.js b/public/javascripts/components/correspondance.js index 47a37757..87de3eb8 100644 --- a/public/javascripts/components/correspondance.js +++ b/public/javascripts/components/correspondance.js @@ -1,9 +1,11 @@ Vue.component("my-correspondance", { - //TODO template: `

TODO: load from server, show timeControl + players + link "play"

Also tab for current challenges + button "new game"

`, + created: function() { + //TODO + }, }); diff --git a/routes/all.js b/routes/all.js index 0989b3fb..85815a3b 100644 --- a/routes/all.js +++ b/routes/all.js @@ -3,7 +3,7 @@ var router = require("express").Router(); 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")); diff --git a/routes/playing.js b/routes/games.js similarity index 98% rename from routes/playing.js rename to routes/games.js index 3bdfa35c..808c258c 100644 --- a/routes/playing.js +++ b/routes/games.js @@ -92,7 +92,8 @@ router.get("/gamesbyplayer", access.logged, access.ajax, (req,res) => { }); }); -// 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); -- 2.44.0