Commit | Line | Data |
---|---|---|
c5c47010 BA |
1 | const db = require("../utils/database"); |
2 | const genToken = require("../utils/tokenGenerator"); | |
3 | const params = require("../config/parameters"); | |
4 | const sendEmail = require('../utils/mailer'); | |
be3fb7b5 | 5 | const { exec } = require("child_process"); |
8d7e2786 BA |
6 | |
7 | /* | |
8 | * Structure: | |
d17d2564 | 9 | * id: integer |
8d7e2786 BA |
10 | * name: varchar |
11 | * email: varchar | |
12 | * loginToken: token on server only | |
13 | * loginTime: datetime (validity) | |
14 | * sessionToken: token in cookies for authentication | |
15 | * notify: boolean (send email notifications for corr games) | |
83494c7f | 16 | * created: datetime |
dd10eb93 | 17 | * bio: text |
8d7e2786 BA |
18 | */ |
19 | ||
0234201f | 20 | const UserModel = { |
937c24ab | 21 | |
0234201f | 22 | checkNameEmail: function(o) { |
866842c3 | 23 | return ( |
188b4a8f BA |
24 | (!o.name || !!(o.name.match(/^[\w-]+$/))) && |
25 | (!o.email || !!(o.email.match(/^[\w.+-]+@[\w.+-]+$/))) | |
866842c3 | 26 | ); |
dac39588 | 27 | }, |
98db2082 | 28 | |
0234201f | 29 | create: function(name, email, notify, cb) { |
dac39588 | 30 | db.serialize(function() { |
866842c3 | 31 | const query = |
dac39588 BA |
32 | "INSERT INTO Users " + |
33 | "(name, email, notify, created) VALUES " + | |
866842c3 BA |
34 | "('" + name + "','" + email + "'," + notify + "," + Date.now() + ")"; |
35 | db.run(query, function(err) { | |
0234201f | 36 | cb(err, { id: this.lastID }); |
dac39588 BA |
37 | }); |
38 | }); | |
39 | }, | |
8d7e2786 | 40 | |
866842c3 | 41 | // Find one user by id, name, email, or token |
fccaa878 | 42 | getOne: function(by, value, fields, cb) { |
dac39588 BA |
43 | const delimiter = (typeof value === "string" ? "'" : ""); |
44 | db.serialize(function() { | |
45 | const query = | |
fccaa878 | 46 | "SELECT " + fields + " " + |
dac39588 BA |
47 | "FROM Users " + |
48 | "WHERE " + by + " = " + delimiter + value + delimiter; | |
49 | db.get(query, cb); | |
50 | }); | |
51 | }, | |
8d7e2786 | 52 | |
ed9c9c37 BA |
53 | getByIds: function(ids, cb) { |
54 | db.serialize(function() { | |
55 | const query = | |
56 | "SELECT id, name " + | |
1f49533d | 57 | "FROM Users " + |
ed9c9c37 BA |
58 | "WHERE id IN (" + ids + ")"; |
59 | db.all(query, cb); | |
60 | }); | |
1f49533d BA |
61 | }, |
62 | ||
80b38d46 | 63 | getBio: function(id, cb) { |
dd10eb93 BA |
64 | db.serialize(function() { |
65 | const query = | |
66 | "SELECT bio " + | |
67 | "FROM Users " + | |
68 | "WHERE id = " + id; | |
69 | db.get(query, cb); | |
70 | }); | |
71 | }, | |
72 | ||
dac39588 BA |
73 | ///////// |
74 | // MODIFY | |
8d7e2786 | 75 | |
0234201f | 76 | setLoginToken: function(token, id) { |
dac39588 BA |
77 | db.serialize(function() { |
78 | const query = | |
79 | "UPDATE Users " + | |
866842c3 | 80 | "SET loginToken = '" + token + "',loginTime = " + Date.now() + " " + |
0234201f | 81 | "WHERE id = " + id; |
866842c3 | 82 | db.run(query); |
dac39588 BA |
83 | }); |
84 | }, | |
8d7e2786 | 85 | |
dd10eb93 | 86 | setBio: function(id, bio) { |
d9a7a1e4 BA |
87 | db.serialize(function() { |
88 | const query = | |
89 | "UPDATE Users " + | |
dd10eb93 | 90 | "SET bio = ? " + |
0234201f | 91 | "WHERE id = " + id; |
dd10eb93 | 92 | db.run(query, bio); |
d9a7a1e4 BA |
93 | }); |
94 | }, | |
95 | ||
dac39588 BA |
96 | // Set session token only if empty (first login) |
97 | // NOTE: weaker security (but avoid to re-login everywhere after each logout) | |
2c5d7b20 | 98 | // TODO: option would be to reset all tokens periodically (every 3 months?) |
0234201f | 99 | trySetSessionToken: function(id, cb) { |
dac39588 | 100 | db.serialize(function() { |
866842c3 | 101 | let query = |
dac39588 BA |
102 | "SELECT sessionToken " + |
103 | "FROM Users " + | |
0234201f | 104 | "WHERE id = " + id; |
2c5d7b20 | 105 | db.get(query, (err, ret) => { |
dac39588 | 106 | const token = ret.sessionToken || genToken(params.token.length); |
2c5d7b20 BA |
107 | const setSessionToken = |
108 | (!ret.sessionToken ? (", sessionToken = '" + token + "'") : ""); | |
866842c3 | 109 | query = |
dac39588 | 110 | "UPDATE Users " + |
866842c3 | 111 | // Also empty the login token to invalidate future attempts |
6e0f2842 | 112 | "SET loginToken = NULL, loginTime = NULL " + |
2c5d7b20 | 113 | setSessionToken + " " + |
0234201f | 114 | "WHERE id = " + id; |
866842c3 BA |
115 | db.run(query); |
116 | cb(token); | |
dac39588 BA |
117 | }); |
118 | }); | |
119 | }, | |
ab4f4bf2 | 120 | |
0234201f | 121 | updateSettings: function(user) { |
dac39588 BA |
122 | db.serialize(function() { |
123 | const query = | |
124 | "UPDATE Users " + | |
125 | "SET name = '" + user.name + "'" + | |
126 | ", email = '" + user.email + "'" + | |
127 | ", notify = " + user.notify + " " + | |
128 | "WHERE id = " + user.id; | |
866842c3 | 129 | db.run(query); |
dac39588 BA |
130 | }); |
131 | }, | |
5d04793e BA |
132 | |
133 | ///////////////// | |
134 | // NOTIFICATIONS | |
135 | ||
0234201f | 136 | notify: function(user, message) { |
fe4c7e67 | 137 | const subject = "vchess.club - notification"; |
f53871db | 138 | const body = "Hello " + user.name + " !" + ` |
a749972c | 139 | ` + message; |
fe4c7e67 BA |
140 | sendEmail(params.mail.noreply, user.email, subject, body); |
141 | }, | |
142 | ||
0234201f | 143 | tryNotify: function(id, message) { |
323de224 | 144 | UserModel.getOne("id", id, "name, email, notify", (err, user) => { |
0234201f | 145 | if (!err && user.notify) UserModel.notify(user, message); |
2be5d614 | 146 | }); |
83494c7f BA |
147 | }, |
148 | ||
149 | //////////// | |
150 | // CLEANING | |
ab4f4bf2 | 151 | |
0234201f | 152 | cleanUsersDb: function() { |
83494c7f BA |
153 | const tsNow = Date.now(); |
154 | // 86400000 = 24 hours in milliseconds | |
155 | const day = 86400000; | |
156 | db.serialize(function() { | |
157 | const query = | |
a97bdbda | 158 | "SELECT id, sessionToken, created, name, email " + |
83494c7f BA |
159 | "FROM Users"; |
160 | db.all(query, (err, users) => { | |
0234201f | 161 | let toRemove = []; |
83494c7f | 162 | users.forEach(u => { |
0234201f | 163 | // Remove users unlogged for > 24h |
83494c7f | 164 | if (!u.sessionToken && tsNow - u.created > day) |
a97bdbda | 165 | { |
0d5335de | 166 | toRemove.push(u.id); |
059228c9 | 167 | UserModel.notify( |
a97bdbda BA |
168 | u, |
169 | "Your account has been deleted because " + | |
170 | "you didn't log in for 24h after registration" | |
171 | ); | |
a97bdbda | 172 | } |
83494c7f | 173 | }); |
0234201f | 174 | if (toRemove.length > 0) { |
e28519f2 | 175 | const remArg = toRemove.join(","); |
0234201f BA |
176 | db.run( |
177 | "DELETE FROM Users " + | |
e28519f2 | 178 | "WHERE id IN (" + remArg + ")" |
0234201f | 179 | ); |
e28519f2 BA |
180 | // Update tournament DB: |
181 | exec(params.tourneyPath + "/dbsync/delete_users.py " + remArg); | |
0234201f | 182 | } |
83494c7f BA |
183 | }); |
184 | }); | |
937c24ab BA |
185 | } |
186 | ||
187 | }; | |
d431028c | 188 | |
ab4f4bf2 | 189 | module.exports = UserModel; |