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(cursor, cb) {
  37     db.serialize(function() {
  38       const query =
  39         "SELECT * " +
  40         "FROM Problems " +
  41         "WHERE added < " + cursor + " " +
  42         "ORDER BY added DESC " +
  43         "LIMIT 20"; //TODO: 20 is arbitrary
  44       db.all(query, (err, problems) => {
  45         cb(err, problems);
  46       });
  47     });
  48   },
  49
  50   getOne: function(id, cb) {
  51     db.serialize(function() {
  52       const query =
  53         "SELECT * " +
  54         "FROM Problems " +
  55         "WHERE id = " + id;
  56       db.get(query, (err, problem) => {
  57         cb(err, problem);
  58       });
  59     });
  60   },
  61
  62   safeUpdate: function(prob, uid) {
  63     db.serialize(function() {
  64       const query =
  65         "UPDATE Problems " +
  66         "SET " +
  67           "vid = " + prob.vid + "," +
  68           "fen = '" + prob.fen + "'," +
  69           "instruction = ?," +
  70           "solution = ? " +
  71         "WHERE id = " + prob.id + " AND uid = " + uid;
  72       db.run(query, [prob.instruction, prob.solution]);
  73     });
  74   },
  75
  76   safeRemove: function(id, uid) {
  77     db.serialize(function() {
  78       const query =
  79         "DELETE FROM Problems " +
  80         "WHERE id = " + id + " AND uid = " + uid;
  81       db.run(query);
  82     });
  83   },
  84 }
  85
  86 module.exports = ProblemModel;