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(vname, fen, instructions, solution)
  14 {
  15     db.serialize(function() {
  16         db.get("SELECT id FROM Variants WHERE name = '" + vname + "'", (err,variant) => {
  17             db.run(
  18                 "INSERT INTO Problems (added, vid, fen, instructions, solution) VALUES " +
  19                 "(" +
  20                     Date.now() + "," +
  21                     variant._id + "," +
  22                     fen + "," +
  23                     instructions + "," +
  24                     solution +
  25                 ")");
  26         });
  27     });
  28 }
  29
  30 exports.getById = function(id, callback)
  31 {
  32     db.serialize(function() {
  33         db.get(
  34             "SELECT * FROM Problems " +
  35             "WHERE id ='" + id + "'",
  36             callback);
  37     });
  38 }
  39
  40 exports.fetchN = function(vname, directionStr, lastDt, MaxNbProblems, callback)
  41 {
  42     db.serialize(function() {
  43         db.all(
  44             "SELECT * FROM Problems " +
  45             "WHERE vid = (SELECT id FROM Variants WHERE name = '" + vname + "') " +
  46             "  AND added " + directionStr + " " + lastDt + " " +
  47             "ORDER BY added " + (directionStr=="<" ? "DESC " : "") +
  48             "LIMIT " + MaxNbProblems,
  49             callback);
  50     });
  51 }
  52
  53 exports.update = function(id, uid, fen, instructions, solution)
  54 {
  55     db.serialize(function() {
  56         db.run(
  57             "UPDATE Problems " +
  58                 "fen = " + fen + ", " +
  59                 "instructions = " + instructions + ", " +
  60                 "solution = " + solution + " " +
  61             "WHERE id = " + id + " AND uid = " + uid);
  62     });
  63 }
  64
  65 exports.remove = function(id, uid)
  66 {
  67     db.serialize(function() {
  68         db.run(
  69             "DELETE FROM Problems " +
  70             "WHERE id = " + id + " AND uid = " + uid);
  71     });
  72 }