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