server/models/Problem.js

   1 var db = require("../utils/database");
   2
   3 /*
   4  * Structure:
   5  *   id: problem number (int)
   6  *   uid: user id (int)
   7  *   vid: variant id (int)
   8  *   added: timestamp
   9  *   instructions: text
  10  *   solution: text
  11  */
  12
  13 const ProblemModel =
  14 {
  15     create: function(uid, vid, fen, instructions, solution, cb)
  16     {
  17         db.serialize(function() {
  18             const insertQuery =
  19                 "INSERT INTO Problems (added, uid, vid, fen, instructions, solution) " +
  20                 "VALUES (" + Date.now() + "," + uid + "," + vid + ",'" + fen + "',?,?)";
  21             db.run(insertQuery, [instructions, solution], err => {
  22                 if (!!err)
  23                     return cb(err);
  24                 db.get("SELECT last_insert_rowid() AS rowid", cb);
  25             });
  26         });
  27     },
  28
  29     getOne: function(id, callback)
  30     {
  31         db.serialize(function() {
  32             const query =
  33                 "SELECT * " +
  34                 "FROM Problems " +
  35                 "WHERE id = " + id;
  36             db.get(query, callback);
  37         });
  38     },
  39
  40     fetchN: function(vid, uid, type, directionStr, lastDt, MaxNbProblems, callback)
  41     {
  42         db.serialize(function() {
  43             let typeLine = "";
  44             if (uid > 0)
  45                 typeLine = "AND uid " + (type=="others" ? "!=" : "=") + " " + uid;
  46             const query =
  47                 "SELECT * FROM Problems " +
  48                 "WHERE vid = " + vid +
  49                 "  AND added " + directionStr + " " + lastDt + " " + typeLine + " " +
  50                 "ORDER BY added " + (directionStr=="<" ? "DESC " : "") +
  51                 "LIMIT " + MaxNbProblems;
  52             db.all(query, callback);
  53         });
  54     },
  55
  56     // TODO: update fails (but insert is OK)
  57     update: function(id, uid, fen, instructions, solution, cb)
  58     {
  59         db.serialize(function() {
  60             const query =
  61                 "UPDATE Problems SET " +
  62                     "fen = '" + fen + "', " +
  63                     "instructions = ?, " +
  64                     "solution = ? " +
  65                 "WHERE id = " + id + " AND uid = " + uid;
  66             db.run(query, [instructions,solution], cb);
  67         });
  68     },
  69
  70     remove: function(id, uid)
  71     {
  72         db.serialize(function() {
  73             const query =
  74                 "DELETE FROM Problems " +
  75                 "WHERE id = " + id + " AND uid = " + uid;
  76             db.run(query);
  77         });
  78     },
  79 }
  80
  81 module.exports = ProblemModel;