Fix SQL query to get completed games
[vchess.git] / server / models / Problem.js
CommitLineData
c5c47010 1const db = require("../utils/database");
89021f18
BA
2
3/*
4 * Structure:
5 * id: integer
6 * added: datetime
7 * uid: user id (int)
8 * vid: variant id (int)
9 * fen: varchar (optional)
10 * instruction: text
11 * solution: text
12 */
13
0234201f
BA
14const ProblemModel = {
15 checkProblem: function(p) {
866842c3
BA
16 return (
17 p.id.toString().match(/^[0-9]+$/) &&
18 p.vid.toString().match(/^[0-9]+$/) &&
19 p.fen.match(/^[a-zA-Z0-9, /-]*$/)
20 );
89021f18
BA
21 },
22
0234201f 23 create: function(p, cb) {
89021f18
BA
24 db.serialize(function() {
25 const query =
26 "INSERT INTO Problems " +
27 "(added, uid, vid, fen, instruction, solution) " +
28 "VALUES " +
604b951e
BA
29 "(" + Date.now() + "," + p.uid + "," + p.vid + ",'" + p.fen + "',?,?)";
30 db.run(query, [p.instruction,p.solution], function(err) {
0234201f 31 cb(err, { id: this.lastID });
89021f18
BA
32 });
33 });
34 },
35
0234201f 36 getAll: function(cb) {
89021f18
BA
37 db.serialize(function() {
38 const query =
39 "SELECT * " +
40 "FROM Problems";
41 db.all(query, (err,problems) => {
866842c3 42 cb(err, problems);
89021f18
BA
43 });
44 });
45 },
46
0234201f 47 getOne: function(id, cb) {
89021f18
BA
48 db.serialize(function() {
49 const query =
50 "SELECT * " +
51 "FROM Problems " +
52 "WHERE id = " + id;
53 db.get(query, (err,problem) => {
866842c3 54 cb(err, problem);
89021f18
BA
55 });
56 });
57 },
58
0234201f 59 safeUpdate: function(prob, uid) {
89021f18 60 db.serialize(function() {
866842c3 61 const query =
89021f18
BA
62 "UPDATE Problems " +
63 "SET " +
64 "vid = " + prob.vid + "," +
604b951e
BA
65 "fen = '" + prob.fen + "'," +
66 "instruction = ?," +
67 "solution = ? " +
866842c3
BA
68 "WHERE id = " + prob.id + " AND uid = " + uid;
69 db.run(query, [prob.instruction,prob.solution]);
89021f18
BA
70 });
71 },
72
0234201f 73 safeRemove: function(id, uid) {
89021f18
BA
74 db.serialize(function() {
75 const query =
76 "DELETE FROM Problems " +
89021f18 77 "WHERE id = " + id + " AND uid = " + uid;
866842c3 78 db.run(query);
89021f18
BA
79 });
80 },
81}
82
83module.exports = ProblemModel;