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