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