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