server/models/Problem.js

   1 const db = require("../utils/database");
   2
   3 /*
   4  * Structure:
   5  *   id: integer
   6  *   added: datetime
   7  *   uid: user id (int)
   8  *   vid: variant id (int)
   9  *   fen: varchar (optional)
  10  *   instruction: text
  11  *   solution: text
  12  */
  13
  14 const ProblemModel = {
  15
  16   checkProblem: function(p) {
  17     return (
  18       p.id.toString().match(/^[0-9]+$/) &&
  19       p.vid.toString().match(/^[0-9]+$/) &&
  20       p.fen.match(/^[a-zA-Z0-9, /-]*$/)
  21     );
  22   },
  23
  24   create: function(p, cb) {
  25     db.serialize(function() {
  26       const query =
  27         "INSERT INTO Problems " +
  28         "(added, uid, vid, fen, instruction, solution) " +
  29           "VALUES " +
  30         "(" + Date.now() + "," + p.uid + "," + p.vid + ",'" + p.fen  + "',?,?)";
  31       db.run(query, [p.instruction,p.solution], function(err) {
  32         cb(err, { id: this.lastID });
  33       });
  34     });
  35   },
  36
  37   getNext: function(uid, onlyMine, cursor, cb) {
  38     let condition = "";
  39     if (onlyMine) condition = "AND uid = " + uid + " ";
  40     else if (!!uid) condition = "AND uid <> " + uid + " ";
  41     db.serialize(function() {
  42       const query =
  43         "SELECT * " +
  44         "FROM Problems " +
  45         "WHERE added < " + cursor + " " +
  46         condition +
  47         "ORDER BY added DESC " +
  48         "LIMIT 20"; //TODO: 20 is arbitrary
  49       db.all(query, (err, problems) => {
  50         cb(err, problems);
  51       });
  52     });
  53   },
  54
  55   getOne: function(id, cb) {
  56     db.serialize(function() {
  57       const query =
  58         "SELECT * " +
  59         "FROM Problems " +
  60         "WHERE id = " + id;
  61       db.get(query, (err, problem) => {
  62         cb(err, problem);
  63       });
  64     });
  65   },
  66
  67   safeUpdate: function(prob, uid, devs) {
  68     db.serialize(function() {
  69       let whereClause = "WHERE id = " + prob.id;
  70       if (!devs.includes(uid)) whereClause += " AND uid = " + uid;
  71       const query =
  72         "UPDATE Problems " +
  73         "SET " +
  74           "vid = " + prob.vid + "," +
  75           "fen = '" + prob.fen + "'," +
  76           "instruction = ?," +
  77           "solution = ? " +
  78         whereClause;
  79       db.run(query, [prob.instruction, prob.solution]);
  80     });
  81   },
  82
  83   safeRemove: function(id, uid, devs) {
  84     db.serialize(function() {
  85       let whereClause = "WHERE id = " + id;
  86       if (!devs.includes(uid)) whereClause += " AND uid = " + uid;
  87       const query =
  88         "DELETE FROM Problems " +
  89         whereClause;
  90       db.run(query);
  91     });
  92   }
  93
  94 };
  95
  96 module.exports = ProblemModel;