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 // TODO: callback ?
  14 exports.create = function(vid, fen, instructions, solution)
  15 {
  16     db.serialize(function() {
  17         const query =
  18             "INSERT INTO Problems (added, vid, fen, instructions, solution) VALUES " +
  19             "(" +
  20                 Date.now() + "," +
  21                 vid + "," +
  22                 fen + "," +
  23                 instructions + "," +
  24                 solution +
  25             ")";
  26         db.run(query);
  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 id " + (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 exports.update = function(id, uid, fen, instructions, solution)
  58 {
  59     db.serialize(function() {
  60         const query =
  61             "UPDATE Problems " +
  62                 "fen = " + fen + ", " +
  63                 "instructions = " + instructions + ", " +
  64                 "solution = " + solution + " " +
  65             "WHERE id = " + id + " AND uid = " + uid;
  66         db.run(query);
  67     });
  68 }
  69
  70 exports.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 }