Commit | Line | Data |
---|---|---|
8d7e2786 BA |
1 | var db = require("../utils/database"); |
2 | ||
3 | /* | |
4 | * Structure: | |
c018b304 | 5 | * id: problem number (int) |
8d7e2786 BA |
6 | * uid: user id (int) |
7 | * vid: variant id (int) | |
8 | * added: timestamp | |
9 | * instructions: text | |
10 | * solution: text | |
11 | */ | |
12 | ||
936dc463 | 13 | // TODO: callback ? |
8d7e2786 BA |
14 | exports.create = function(vname, fen, instructions, solution) |
15 | { | |
16 | db.serialize(function() { | |
936dc463 BA |
17 | const vidQuery = |
18 | "SELECT id " + | |
19 | "FROM Variants " + | |
20 | "WHERE name = '" + vname + "'"; | |
21 | db.get(vidQuery, (err,variant) => { | |
22 | const insertQuery = | |
8d7e2786 BA |
23 | "INSERT INTO Problems (added, vid, fen, instructions, solution) VALUES " + |
24 | "(" + | |
25 | Date.now() + "," + | |
c018b304 | 26 | variant.id + "," + |
8d7e2786 BA |
27 | fen + "," + |
28 | instructions + "," + | |
29 | solution + | |
936dc463 BA |
30 | ")"; |
31 | db.run(insertQuery); | |
8d7e2786 BA |
32 | }); |
33 | }); | |
34 | } | |
35 | ||
36 | exports.getById = function(id, callback) | |
37 | { | |
38 | db.serialize(function() { | |
936dc463 | 39 | const query = |
8d7e2786 | 40 | "SELECT * FROM Problems " + |
936dc463 BA |
41 | "WHERE id ='" + id + "'"; |
42 | db.get(query, callback); | |
8d7e2786 BA |
43 | }); |
44 | } | |
45 | ||
936dc463 | 46 | exports.getOne = function(vname, pid, callback) |
8d7e2786 BA |
47 | { |
48 | db.serialize(function() { | |
936dc463 BA |
49 | const query = |
50 | "SELECT * " + | |
51 | "FROM Problems " + | |
52 | "WHERE id = " + pid; | |
53 | db.get(query, callback); | |
54 | }); | |
55 | } | |
56 | ||
57 | exports.fetchN = function(vname, uid, type, directionStr, lastDt, MaxNbProblems, callback) | |
58 | { | |
59 | db.serialize(function() { | |
60 | let typeLine = ""; | |
61 | if (uid > 0) | |
62 | typeLine = "AND id " + (type=="others" ? "!=" : "=") + " " + uid; | |
63 | const query = | |
8d7e2786 BA |
64 | "SELECT * FROM Problems " + |
65 | "WHERE vid = (SELECT id FROM Variants WHERE name = '" + vname + "') " + | |
936dc463 | 66 | " AND added " + directionStr + " " + lastDt + " " + typeLine + " " + |
8d7e2786 BA |
67 | "ORDER BY added " + (directionStr=="<" ? "DESC " : "") + |
68 | "LIMIT " + MaxNbProblems, | |
936dc463 | 69 | db.all(query, callback); |
8d7e2786 BA |
70 | }); |
71 | } | |
72 | ||
73 | exports.update = function(id, uid, fen, instructions, solution) | |
74 | { | |
75 | db.serialize(function() { | |
936dc463 | 76 | const query = |
8d7e2786 BA |
77 | "UPDATE Problems " + |
78 | "fen = " + fen + ", " + | |
79 | "instructions = " + instructions + ", " + | |
80 | "solution = " + solution + " " + | |
936dc463 BA |
81 | "WHERE id = " + id + " AND uid = " + uid; |
82 | db.run(query); | |
8d7e2786 BA |
83 | }); |
84 | } | |
85 | ||
86 | exports.remove = function(id, uid) | |
87 | { | |
88 | db.serialize(function() { | |
936dc463 | 89 | const query = |
8d7e2786 | 90 | "DELETE FROM Problems " + |
936dc463 BA |
91 | "WHERE id = " + id + " AND uid = " + uid; |
92 | db.run(query); | |
8d7e2786 BA |
93 | }); |
94 | } |