server/models/User.js

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