diff options
Diffstat (limited to 'server.js')
| -rw-r--r-- | server.js | 1749 |
1 files changed, 805 insertions, 944 deletions
@@ -29,6 +29,7 @@ let session_store = new SQLiteStore(); let db = new sqlite3(process.env.DATABASE || "./db"); db.pragma("journal_mode = WAL"); db.pragma("synchronous = NORMAL"); +db.pragma("foreign_keys = ON"); let app = express(); @@ -88,6 +89,14 @@ io.use(passport_socket.authorize({ app.use(express.static('public')); +/* + * MISC FUNCTIONS + */ + +function SQL(s) { + return db.prepare(s); +} + function LOG(req, ...msg) { let name; if (req.isAuthenticated()) @@ -122,30 +131,6 @@ function human_date(time) { return date.toISOString().substring(0,10); } -function humanize(rows) { - for (let row of rows) { - row.ctime = human_date(row.ctime); - row.mtime = human_date(row.mtime); - } -} - -function linkify_player_names(games) { - for (let i = 0; i < games.length; ++i) { - let game = games[i]; - if (game.player_names) { - game.player_names = game.player_names - .split(", ") - .map(x => `<a href="/user/${x}">${x}</a>`) - .join(", "); - } - } -} - -function humanize_one(row) { - row.ctime = human_date(row.ctime); - row.mtime = human_date(row.mtime); -} - function is_email(email) { return email.match(/^[a-zA-Z0-9.!#$%&'*+/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$/); } @@ -182,42 +167,56 @@ function get_avatar(mail) { } /* - * USER PROFILES + * USER AUTHENTICATION */ -const sql_blacklist_ip = db.prepare("SELECT COUNT(*) FROM blacklist_ip WHERE ip = ?").raw(); -const sql_blacklist_mail = db.prepare("SELECT COUNT(*) AS count FROM blacklist_mail WHERE ? LIKE mail").raw(); +const SQL_BLACKLIST_IP = SQL("SELECT EXISTS ( SELECT 1 FROM blacklist_ip WHERE ip=? )").pluck(); +const SQL_BLACKLIST_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM blacklist_mail WHERE ? LIKE mail )").pluck(); + +const SQL_EXISTS_USER_NAME = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE name=? )").pluck(); +const SQL_EXISTS_USER_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE mail=? )").pluck(); + +const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail,password,salt) VALUES (?,?,?,?) RETURNING user_id,name,mail,notify"); + +const SQL_SELECT_USER = SQL("SELECT * FROM user_view WHERE user_id=?"); +const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?"); +const SQL_SELECT_LOGIN_BY_MAIL = SQL("SELECT * FROM user_login_view WHERE mail=?"); +const SQL_SELECT_LOGIN_BY_NAME = SQL("SELECT * FROM user_login_view WHERE name=?"); +const SQL_SELECT_USER_PROFILE = SQL("SELECT * FROM user_profile_view WHERE name=?"); + +const SQL_OFFLINE_USER = SQL("SELECT * FROM user_view NATURAL JOIN user_last_seen WHERE user_id=? AND datetime('now') > datetime(atime,?)"); + +const SQL_UPDATE_USER_NOTIFY = SQL("UPDATE users SET notify=? WHERE user_id=?"); +const SQL_UPDATE_USER_NAME = SQL("UPDATE users SET name=? WHERE user_id=?"); +const SQL_UPDATE_USER_MAIL = SQL("UPDATE users SET mail=? WHERE user_id=?"); +const SQL_UPDATE_USER_ABOUT = SQL("UPDATE users SET about=? WHERE user_id=?"); +const SQL_UPDATE_USER_PASSWORD = SQL("UPDATE users SET password=? WHERE user_id=?"); +const SQL_UPDATE_USER_LAST_SEEN = SQL("INSERT OR REPLACE INTO user_last_seen (user_id,atime,aip) VALUES (?,datetime('now'),?)"); + +const SQL_FIND_TOKEN = SQL("SELECT token FROM tokens WHERE user_id=? AND datetime('now') < datetime(time, '+5 minutes')").pluck(); +const SQL_CREATE_TOKEN = SQL("INSERT OR REPLACE INTO tokens (user_id,token,time) VALUES (?, lower(hex(randomblob(16))), datetime('now')) RETURNING token").pluck(); +const SQL_VERIFY_TOKEN = SQL("SELECT EXISTS ( SELECT 1 FROM tokens WHERE user_id=? AND datetime('now') < datetime(time, '+20 minutes') AND token=? )").pluck(); + +const SQL_COUNT_INBOX = SQL("SELECT COUNT(*) FROM messages WHERE to_id=? AND read=0 AND deleted_from_inbox=0").pluck(); function is_blacklisted(ip, mail) { - if (sql_blacklist_ip.get(ip)[0] !== 0) + if (SQL_BLACKLIST_IP.get(ip) === 1) return true; - if (sql_blacklist_mail.get(mail)[0] !== 0) + if (SQL_BLACKLIST_MAIL.get(mail) === 1) return true; return false; } -const sql_deserialize_user = db.prepare("SELECT user_id, name, mail, notifications FROM users WHERE user_id = ?"); -const sql_update_last_seen = db.prepare("UPDATE users SET aip = ?, atime = datetime('now') WHERE user_id = ?"); -const sql_login_select = db.prepare("SELECT user_id, name, mail, password, salt FROM users WHERE name = ? OR mail = ?"); - -const sql_subscribe = db.prepare("UPDATE users SET notifications = 1 WHERE user_id = ?"); -const sql_unsubscribe = db.prepare("UPDATE users SET notifications = 0 WHERE user_id = ?"); - -const sql_count_unread_messages = db.prepare("SELECT COUNT(*) FROM messages WHERE to_id = ? AND read = 0 AND deleted_from_inbox = 0").pluck(); -const sql_fetch_user_by_name = db.prepare("SELECT * FROM users WHERE user_id = ? OR name = ?"); -const sql_fetch_user_by_id = db.prepare("SELECT * FROM users WHERE user_id = ?"); - passport.serializeUser(function (user, done) { return done(null, user.user_id); }); passport.deserializeUser(function (user_id, done) { try { - let row = sql_deserialize_user.get(user_id); - if (!row) + let user = SQL_SELECT_USER.get(user_id); + if (!user) return done(null, false); - row.unread = () => sql_count_unread_messages.get(user_id); - return done(null, row); + return done(null, user); } catch (err) { console.log(err); return done(null, false); @@ -229,25 +228,22 @@ function local_login(req, name_or_mail, password, done) { if (!is_email(name_or_mail)) name_or_mail = clean_user_name(name_or_mail); LOG(req, "POST /login", name_or_mail); - let row = sql_login_select.get(name_or_mail, name_or_mail); - if (!row) + let user = SQL_SELECT_LOGIN_BY_NAME.get(name_or_mail); + if (!user) + user = SQL_SELECT_LOGIN_BY_MAIL.get(name_or_mail); + if (!user) return setTimeout(() => done(null, false, req.flash('message', "User not found.")), 1000); - if (is_blacklisted(req.connection.remoteAddress, row.mail)) + if (is_blacklisted(req.connection.remoteAddress, user.mail)) return setTimeout(() => done(null, false, req.flash('message', "Sorry, but this IP or account has been banned.")), 1000); - let hash = hash_password(password, row.salt); - if (hash !== row.password) + let hash = hash_password(password, user.salt); + if (hash !== user.password) return setTimeout(() => done(null, false, req.flash('message', "Wrong password.")), 1000); - sql_update_last_seen.run(req.connection.remoteAddress, row.user_id); - done(null, row); + done(null, user); } catch (err) { done(null, false, req.flash('message', err.toString())); } } -const sql_signup_check = db.prepare("SELECT user_id, name FROM users WHERE name = ? OR mail = ?"); -const sql_signup_insert = db.prepare("INSERT INTO users (name, mail, password, salt, ctime, cip, atime, aip, notifications) VALUES (?,?,?,?,datetime('now'),?,datetime('now'),?,0)"); -const sql_signup_login = db.prepare("SELECT user_id, name FROM users WHERE name = ? AND password = ?"); - function local_signup(req, name, password, done) { try { let mail = req.body.mail; @@ -263,15 +259,14 @@ function local_signup(req, name, password, done) { return done(null, false, req.flash('message', "Password is too long!")); if (!is_email(mail)) return done(null, false, req.flash('message', "Invalid mail address!")); - let row = sql_signup_check.get(name, mail); - if (row) - return done(null, false, req.flash('message', "User name or mail is already taken.")); + if (SQL_EXISTS_USER_NAME.get(name)) + return done(null, false, req.flash('message', "That name is already taken.")); + if (SQL_EXISTS_USER_MAIL.get(mail)) + return done(null, false, req.flash('message', "That mail is already taken.")); let salt = crypto.randomBytes(32).toString('hex'); let hash = hash_password(password, salt); - let ip = req.connection.remoteAddress; - sql_signup_insert.run(name, mail, hash, salt, ip, ip); - row = sql_signup_login.get(name, hash); - done(null, row); + let user = SQL_INSERT_USER.get(name, mail, hash, salt); + done(null, user); } catch (err) { done(null, false, req.flash('message', err.toString())); } @@ -283,24 +278,46 @@ passport.use('local-signup', new passport_local.Strategy({ passReqToCallback: tr app.use(passport.initialize()); app.use(passport.session()); -function update_last_seen(req) { - sql_update_last_seen.run(req.connection.remoteAddress, req.user.user_id); +function touch_user(req) { + req.user.unread = SQL_COUNT_INBOX.get(req.user.user_id); + SQL_UPDATE_USER_LAST_SEEN.run(req.user.user_id, req.connection.remoteAddress); +} + +function must_not_be_logged_in(req, res, next) { + if (SQL_BLACKLIST_IP.get(req.connection.remoteAddress) === 1) + return res.redirect('/banned'); + return next(); } function must_be_logged_in(req, res, next) { + if (SQL_BLACKLIST_IP.get(req.connection.remoteAddress) === 1) + return res.redirect('/banned'); if (!req.isAuthenticated()) { req.session.redirect = req.originalUrl; return res.redirect('/login'); } - if (sql_blacklist_ip.get(req.connection.remoteAddress)[0] !== 0) - return res.redirect('/banned'); - if (sql_blacklist_mail.get(req.user.mail)[0] !== 0) + touch_user(req); + return next(); +} + +function may_be_logged_in(req, res, next) { + if (SQL_BLACKLIST_IP.get(req.connection.remoteAddress) === 1) return res.redirect('/banned'); - update_last_seen(req); + if (req.isAuthenticated()) + touch_user(req); return next(); } -app.get('/about', function (req, res) { +app.get('/', may_be_logged_in, function (req, res) { + res.render('index.ejs', { user: req.user, flash: req.flash('message') }); +}); + +app.get('/banned', function (req, res) { + LOG(req, "GET /banned"); + res.render('banned.ejs', { user: req.user }); +}); + +app.get('/about', may_be_logged_in, function (req, res) { res.render('about.ejs', { user: req.user }); }); @@ -310,19 +327,18 @@ app.get('/logout', function (req, res) { res.redirect('/login'); }); -app.get('/banned', function (req, res) { - LOG(req, "GET /banned"); - res.render('banned.ejs', { user: req.user, message: req.flash('message') }); -}); - app.get('/login', function (req, res) { + if (req.isAuthenticated()) + return res.redirect('/'); LOG(req, "GET /login"); - res.render('login.ejs', { user: req.user, message: req.flash('message') }); + res.render('login.ejs', { user: req.user, flash: req.flash('message') }); }); app.get('/signup', function (req, res) { + if (req.isAuthenticated()) + return res.redirect('/'); LOG(req, "GET /signup"); - res.render('signup.ejs', { user: req.user, message: req.flash('message') }); + res.render('signup.ejs', { user: req.user, flash: req.flash('message') }); }); app.post('/login', @@ -345,395 +361,666 @@ app.post('/signup', }) ); +app.get('/forgot_password', must_not_be_logged_in, function (req, res) { + LOG(req, "GET /forgot_password"); + res.render('forgot_password.ejs', { user: req.user, flash: req.flash('message') }); +}); + +app.post('/forgot_password', must_not_be_logged_in, function (req, res) { + LOG(req, "POST /forgot_password"); + let mail = req.body.mail; + let user = SQL_SELECT_LOGIN_BY_MAIL.get(mail); + if (user) { + let token = SQL_FIND_TOKEN.get(user.user_id); + if (!token) { + token = SQL_CREATE_TOKEN.run(user.user_id); + mail_password_reset_token(user, token); + } + req.flash('message', "A password reset token has been sent to " + mail + "."); + return res.redirect('/reset_password/' + mail); + } + req.flash('message', "User not found."); + return res.redirect('/forgot_password'); +}); + +app.get('/reset_password', must_not_be_logged_in, function (req, res) { + LOG(req, "GET /reset_password"); + res.render('reset_password.ejs', { user: null, mail: "", token: "", flash: req.flash('message') }); +}); + +app.get('/reset_password/:mail', must_not_be_logged_in, function (req, res) { + let mail = req.params.mail; + LOG(req, "GET /reset_password", mail); + res.render('reset_password.ejs', { user: null, mail: mail, token: "", flash: req.flash('message') }); +}); + +app.get('/reset_password/:mail/:token', must_not_be_logged_in, function (req, res) { + let mail = req.params.mail; + let token = req.params.token; + LOG(req, "GET /reset_password", mail, token); + res.render('reset_password.ejs', { user: null, mail: mail, token: token, flash: req.flash('message') }); +}); + +app.post('/reset_password', must_not_be_logged_in, function (req, res) { + let mail = req.body.mail; + let token = req.body.token; + let password = req.body.password; + LOG(req, "POST /reset_password", mail, token); + let user = SQL_SELECT_LOGIN_BY_MAIL.get(mail); + if (!user) { + req.flash('message', "User not found."); + return res.redirect('/reset_password/'+mail+'/'+token); + } + if (password.length < 4) { + req.flash('message', "Password is too short!"); + return res.redirect('/reset_password/'+mail+'/'+token); + } + if (!SQL_VERIFY_TOKEN.get(user.user_id, token)) { + req.flash('message', "Invalid or expired token!"); + return res.redirect('/reset_password/'+mail); + } + let hash = hash_password(password, user.salt); + SQL_UPDATE_USER_PASSWORD.run(hash, user.user_id); + return res.redirect('/login'); +}); + app.get('/change_password', must_be_logged_in, function (req, res) { LOG(req, "GET /change_password"); - res.render('change_password.ejs', { user: req.user, message: req.flash('message') }); + res.render('change_password.ejs', { user: req.user, flash: req.flash('message') }); +}); + +app.post('/change_password', must_be_logged_in, function (req, res) { + let oldpass = req.body.password; + let newpass = req.body.newpass; + LOG(req, "POST /change_password", req.user.name); + // Get full user record including password and salt + let user = SQL_SELECT_LOGIN_BY_MAIL.get(req.user.mail); + if (newpass.length < 4) { + req.flash('message', "Password is too short!"); + return res.redirect('/change_password'); + } + let oldhash = hash_password(oldpass, user.salt); + if (oldhash !== user.password) { + req.flash('message', "Wrong password."); + return res.redirect('/change_password'); + } + let newhash = hash_password(newpass, user.salt); + SQL_UPDATE_USER_PASSWORD.run(newhash, user.user_id); + req.flash('message', "Your password has been updated."); + return res.redirect('/profile'); +}); + +/* + * USER PROFILE + */ + +app.get('/subscribe', must_be_logged_in, function (req, res) { + LOG(req, "GET /subscribe"); + SQL_UPDATE_USER_NOTIFY.run(1, req.user.user_id); + res.redirect('/profile'); +}); + +app.get('/unsubscribe', must_be_logged_in, function (req, res) { + LOG(req, "GET /unsubscribe"); + SQL_UPDATE_USER_NOTIFY.run(0, req.user.user_id); + res.redirect('/profile'); }); app.get('/change_name', must_be_logged_in, function (req, res) { LOG(req, "GET /change_name"); - res.render('change_name.ejs', { user: req.user, message: req.flash('message') }); + res.render('change_name.ejs', { user: req.user, flash: req.flash('message') }); +}); + +app.post('/change_name', must_be_logged_in, function (req, res) { + let newname = clean_user_name(req.body.newname); + LOG(req, "POST /change_name", req.user, req.body, newname); + if (!is_valid_user_name(newname)) { + req.flash('message', "Invalid user name!"); + return res.redirect('/change_name'); + } + if (SQL_EXISTS_USER_NAME.get(newname)) { + req.flash('message', "That name is already taken!"); + return res.redirect('/change_name'); + } + SQL_UPDATE_USER_NAME.run(newname, req.user.user_id); + return res.redirect('/profile'); }); app.get('/change_mail', must_be_logged_in, function (req, res) { LOG(req, "GET /change_mail"); - res.render('change_mail.ejs', { user: req.user, message: req.flash('message') }); + res.render('change_mail.ejs', { user: req.user, flash: req.flash('message') }); +}); + +app.post('/change_mail', must_be_logged_in, function (req, res) { + let newmail = req.body.newmail; + LOG(req, "POST /change_mail", req.user, req.body); + if (!is_email(newmail)) { + req.flash('message', "Invalid mail address!"); + return res.redirect('/change_mail'); + } + if (SQL_EXISTS_USER_MAIL.get(newmail)) { + req.flash('message', "That mail address is already taken!"); + return res.redirect('/change_mail'); + } + SQL_UPDATE_USER_MAIL.run(newmail, req.user.user_id); + return res.redirect('/profile'); }); app.get('/change_about', must_be_logged_in, function (req, res) { LOG(req, "GET /change_about"); - let about = sql_fetch_user_by_id.get(req.user.user_id).about; - res.render('change_about.ejs', { user: req.user, about: about || "", message: req.flash('message') }); + let about = SQL_SELECT_USER_PROFILE.get(req.user.name).about; + res.render('change_about.ejs', { user: req.user, about: about || "" }); }); -app.get('/subscribe', must_be_logged_in, function (req, res) { - LOG(req, "GET /subscribe"); - sql_subscribe.run(req.user.user_id); - res.redirect('/profile'); +app.post('/change_about', must_be_logged_in, function (req, res) { + LOG(req, "POST /change_about", req.user.name); + SQL_UPDATE_USER_ABOUT.run(req.body.about, req.user.user_id); + return res.redirect('/profile'); }); -app.get('/unsubscribe', must_be_logged_in, function (req, res) { - LOG(req, "GET /unsubscribe"); - sql_unsubscribe.run(req.user.user_id); - res.redirect('/profile'); +app.get('/user/:who_name', may_be_logged_in, function (req, res) { + LOG(req, "GET /user/" + req.params.who_name); + let who = SQL_SELECT_USER_PROFILE.get(req.params.who_name); + if (who) { + who.avatar = get_avatar(who.mail); + who.ctime = human_date(who.ctime); + who.atime = human_date(who.atime); + res.render('user.ejs', { user: req.user, who: who }); + } else { + return res.status(404).send("Invalid user name."); + } +}); + +app.get('/users', may_be_logged_in, function (req, res) { + LOG(req, "GET /users"); + let rows = db.prepare("SELECT * FROM user_profile_view ORDER BY atime DESC").all(); + rows.forEach(row => { + row.avatar = get_avatar(row.mail); + row.ctime = human_date(row.ctime); + row.atime = human_date(row.atime); + }); + res.render('users.ejs', { user: req.user, userList: rows }); }); /* - * FORGOT AND CHANGE PASSWORD + * MESSAGES */ -const sql_select_salt = db.prepare("SELECT salt FROM users WHERE user_id = ?").pluck(); -const sql_find_user_by_mail = db.prepare("SELECT * FROM users WHERE mail = ?"); - -const sql_find_token = db.prepare(` - SELECT token FROM tokens WHERE user_id = ? AND datetime('now') < datetime(time, '+5 minutes') - `).pluck(); -const sql_verify_token = db.prepare(` - SELECT COUNT(*) FROM tokens WHERE user_id = ? AND datetime('now') < datetime(time, '+20 minutes') AND token = ? - `).pluck(); -const sql_create_token = db.prepare(` - INSERT OR REPLACE INTO tokens VALUES ( ?, lower(hex(randomblob(16))), datetime('now') ) - `); +const MESSAGE_LIST_INBOX = db.prepare(` + SELECT message_id, from_name, subject, time, read + FROM message_view + WHERE to_id=? AND deleted_from_inbox=0 + ORDER BY message_id DESC`); -app.get('/forgot_password', function (req, res) { - LOG(req, "GET /forgot_password"); - res.render('forgot_password.ejs', { user: req.user, message: req.flash('message') }); +const MESSAGE_LIST_OUTBOX = db.prepare(` + SELECT message_id, to_name, subject, time, 1 as read + FROM message_view + WHERE from_id=? AND deleted_from_outbox=0 + ORDER BY message_id DESC`); + +const MESSAGE_FETCH = db.prepare("SELECT * FROM message_view WHERE message_id=? AND ( from_id=? OR to_id=? )"); +const MESSAGE_SEND = db.prepare("INSERT INTO messages (from_id,to_id,subject,body) VALUES (?,?,?,?)"); +const MESSAGE_MARK_READ = db.prepare("UPDATE messages SET read=1 WHERE message_id=? AND read = 0"); +const MESSAGE_DELETE_INBOX = db.prepare("UPDATE messages SET deleted_from_inbox=1 WHERE message_id=? AND to_id=?"); +const MESSAGE_DELETE_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox=1 WHERE message_id=? AND from_id=?"); +const MESSAGE_DELETE_ALL_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox=1 WHERE from_id=?"); + +app.get('/inbox', must_be_logged_in, function (req, res) { + LOG(req, "GET /inbox"); + let messages = MESSAGE_LIST_INBOX.all(req.user.user_id); + for (let i = 0; i < messages.length; ++i) + messages[i].time = human_date(messages[i].time); + res.set("Cache-Control", "no-store"); + res.render('message_inbox.ejs', { + user: req.user, + messages: messages, + }); }); -app.get('/reset_password', function (req, res) { - LOG(req, "GET /reset_password"); - res.render('reset_password.ejs', { user: null, mail: "", token: "", message: req.flash('message') }); +app.get('/outbox', must_be_logged_in, function (req, res) { + LOG(req, "GET /outbox"); + let messages = MESSAGE_LIST_OUTBOX.all(req.user.user_id); + for (let i = 0; i < messages.length; ++i) + messages[i].time = human_date(messages[i].time); + res.set("Cache-Control", "no-store"); + res.render('message_outbox.ejs', { + user: req.user, + messages: messages, + }); }); -app.get('/reset_password/:mail', function (req, res) { - let mail = req.params.mail; - LOG(req, "GET /reset_password", mail); - res.render('reset_password.ejs', { user: null, mail: mail, token: "", message: req.flash('message') }); +app.get('/message/read/:message_id', must_be_logged_in, function (req, res) { + LOG(req, "GET /message/" + req.params.message_id); + let message_id = req.params.message_id | 0; + let message = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id); + if (!message) + return res.status(404).send("Invalid message ID."); + if (message.to_id === req.user.user_id && message.read === 0) { + MESSAGE_MARK_READ.run(message_id); + req.user.unread --; + } + message.time = human_date(message.time); + message.body = linkify_post(message.body); + res.render('message_read.ejs', { + user: req.user, + message: message, + }); }); -app.get('/reset_password/:mail/:token', function (req, res) { - let mail = req.params.mail; - let token = req.params.token; - LOG(req, "GET /reset_password", mail, token); - res.render('reset_password.ejs', { user: null, mail: mail, token: token, message: req.flash('message') }); +app.get('/message/send', must_be_logged_in, function (req, res) { + res.render('message_send.ejs', { + user: req.user, + to_name: "", + subject: "", + body: "", + }); }); -app.post('/forgot_password', function (req, res) { - LOG(req, "POST /forgot_password"); - try { - if (sql_blacklist_ip.get(req.connection.remoteAddress)[0] !== 0) - return res.redirect('/banned'); - let mail = req.body.mail; - let user = sql_find_user_by_mail.get(mail); - if (user) { - let token = sql_find_token.get(user.user_id); - if (!token) { - sql_create_token.run(user.user_id); - token = sql_find_token.get(user.user_id); - mail_password_reset_token(user, token); - } - req.flash('message', "A password reset token has been sent to " + mail + "."); - if (is_email(mail)) - return res.redirect('/reset_password/' + mail); - return res.redirect('/reset_password/'); - } - req.flash('message', "User not found."); - return res.redirect('/forgot_password'); - } catch (err) { - console.log(err); - req.flash('message', err.message); - return res.redirect('/forgot_password'); - } +app.get('/message/send/:to_name', must_be_logged_in, function (req, res) { + LOG(req, "GET /message/send/" + req.params.to_name); + let to_name = req.params.to_name; + res.render('message_send.ejs', { + user: req.user, + to_name: to_name, + subject: "", + body: "", + }); }); -app.post('/reset_password', function (req, res) { - let mail = req.body.mail; - let token = req.body.token; - let password = req.body.password; - try { - LOG(req, "POST /reset_password", mail, token); - let user = sql_find_user_by_mail.get(mail); - if (!user) { - req.flash('message', "User not found."); - return res.redirect('/reset_password/'+mail+'/'+token); - } - if (password.length < 4) { - req.flash('message', "Password is too short!"); - return res.redirect('/reset_password/'+mail+'/'+token); - } - if (!sql_verify_token.get(user.user_id, token)) { - req.flash('message', "Invalid or expired token!"); - return res.redirect('/reset_password/'+mail); - } - let salt = sql_select_salt.get(user.user_id); - if (!salt) { - req.flash('message', "User not found."); - return res.redirect('/reset_password/'+mail+'/'+token); - } - let hash = hash_password(password, salt); - db.prepare("UPDATE users SET password = ? WHERE user_id = ?").run(hash, user.user_id); - req.flash('message', "Your password has been updated."); - return res.redirect('/login'); - } catch (err) { - console.log(err); - req.flash('message', err.message); - return res.redirect('/reset_password/'+mail+'/'+token); +app.post('/message/send', must_be_logged_in, function (req, res) { + LOG(req, "POST /message/send/"); + let to_name = req.body.to.trim(); + let subject = req.body.subject.trim(); + let body = req.body.body.trim(); + let to_user = SQL_SELECT_USER_BY_NAME.get(to_name); + if (!to_user) { + return res.render('message_send.ejs', { + user: req.user, + to_id: 0, + to_name: to_name, + subject: subject, + body: body, + flash: "Cannot find that user." + }); } + let info = MESSAGE_SEND.run(req.user.user_id, to_user.user_id, subject, body); + if (to_user.notify) + mail_new_message(to_user, info.lastInsertRowid, req.user.name, subject, body) + res.redirect('/inbox'); }); -app.post('/change_password', must_be_logged_in, function (req, res) { - try { - let name = req.user.name; - let password = req.body.password; - let newpass = req.body.newpass; - LOG(req, "POST /change_password", name); - if (newpass.length < 4) { - req.flash('message', "Password is too short!"); - return res.redirect('/change_password'); - } - let salt = sql_select_salt.get(req.user.user_id); - if (!salt) { - req.flash('message', "User not found."); - return res.redirect('/change_password'); - } - let hash = hash_password(password, salt); - let user_row = db.prepare("SELECT user_id, name FROM users WHERE name = ? AND password = ?").get(name, hash); - if (!user_row) { - req.flash('message', "Wrong password."); - return res.redirect('/change_password'); - } - hash = hash_password(newpass, salt); - db.prepare("UPDATE users SET password = ? WHERE user_id = ?").run(hash, user_row.user_id); - req.flash('message', "Your password has been updated."); - return res.redirect('/profile'); - } catch (err) { - console.log(err); - req.flash('message', err.message); - return res.redirect('/change_password'); - } +function quote_body(message) { + let when = new Date(message.time).toDateString(); + let who = message.from_name; + let what = message.body.split("\n").join("\n> "); + return "\n\n" + "On " + when + " " + who + " wrote:\n> " + what + "\n"; +} + +app.get('/message/reply/:message_id', must_be_logged_in, function (req, res) { + LOG(req, "POST /message/reply/" + req.params.message_id); + let message_id = req.params.message_id | 0; + let message = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id); + if (!message) + return res.status(404).send("Invalid message ID."); + return res.render('message_send.ejs', { + user: req.user, + to_id: message.from_id, + to_name: message.from_name, + subject: message.subject.startsWith("Re: ") ? message.subject : "Re: " + message.subject, + body: quote_body(message), + }); }); -const sql_is_name_taken = db.prepare("SELECT EXISTS ( SELECT 1 FROM users WHERE name = ? )").pluck(); -const sql_change_name = db.prepare("UPDATE users SET name = ? WHERE user_id = ?"); +app.get('/message/delete/:message_id', must_be_logged_in, function (req, res) { + LOG(req, "POST /message/delete/" + req.params.message_id); + let message_id = req.params.message_id | 0; + MESSAGE_DELETE_INBOX.run(message_id, req.user.user_id); + MESSAGE_DELETE_OUTBOX.run(message_id, req.user.user_id); + res.redirect('/inbox'); +}); -const sql_is_mail_taken = db.prepare("SELECT EXISTS ( SELECT 1 FROM users WHERE mail = ? )").pluck(); -const sql_change_mail = db.prepare("UPDATE users SET mail = ? WHERE user_id = ?"); -const sql_change_about = db.prepare("UPDATE users SET about = ? WHERE user_id = ?"); +app.get('/outbox/delete', must_be_logged_in, function (req, res) { + LOG(req, "POST /outbox/delete"); + MESSAGE_DELETE_ALL_OUTBOX.run(req.user.user_id); + res.redirect('/outbox'); +}); -app.post('/change_name', must_be_logged_in, function (req, res) { - try { - let newname = clean_user_name(req.body.newname); - LOG(req, "POST /change_name", req.user, req.body, newname); - if (!is_valid_user_name(newname)) { - req.flash('message', "Invalid user name!"); - return res.redirect('/change_name'); - } - if (sql_is_name_taken.get(newname)) { - req.flash('message', "That name is already taken!"); - return res.redirect('/change_name'); - } - sql_change_name.run(newname, req.user.user_id); - req.flash('message', "Your name has been changed."); - return res.redirect('/profile'); - } catch (err) { - console.log(err); - req.flash('message', err.message); - return res.redirect('/change_name'); +/* + * FORUM + */ + +const FORUM_PAGE_SIZE = 15; + +const FORUM_COUNT_THREADS = db.prepare("SELECT COUNT(*) FROM threads").pluck(); +const FORUM_LIST_THREADS = db.prepare("SELECT * FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?"); +const FORUM_GET_THREAD = db.prepare("SELECT * FROM thread_view WHERE thread_id=?"); +const FORUM_LIST_POSTS = db.prepare("SELECT * FROM post_view WHERE thread_id=?"); +const FORUM_GET_POST = db.prepare("SELECT * FROM post_view WHERE post_id=?"); +const FORUM_NEW_THREAD = db.prepare("INSERT INTO threads (author_id,subject) VALUES (?,?)"); +const FORUM_NEW_POST = db.prepare("INSERT INTO posts (thread_id,author_id,body) VALUES (?,?,?)"); +const FORUM_EDIT_POST = db.prepare("UPDATE posts SET body=?, mtime=datetime('now') WHERE post_id=? AND author_id=? RETURNING thread_id").pluck(); + +function show_forum_page(req, res, page) { + let thread_count = FORUM_COUNT_THREADS.get(); + let page_count = Math.ceil(thread_count / FORUM_PAGE_SIZE); + let threads = FORUM_LIST_THREADS.all(FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1)); + for (let thread of threads) { + thread.ctime = human_date(thread.ctime); + thread.mtime = human_date(thread.mtime); } + res.set("Cache-Control", "no-store"); + res.render('forum_view.ejs', { + user: req.user, + threads: threads, + current_page: page, + page_count: page_count, + }); +} + +function linkify_post(text) { + text = text.replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">"); + text = text.replace(/https?:\/\/\S+/, (match) => { + if (match.endsWith(".jpg") || match.endsWith(".png") || match.endsWith(".svg")) + return `<a href="${match}"><img src="${match}"></a>`; + return `<a href="${match}">${match}</a>`; + }); + return text; +} + +app.get('/forum', may_be_logged_in, function (req, res) { + LOG(req, "GET /forum"); + show_forum_page(req, res, 1); }); -app.post('/change_mail', must_be_logged_in, function (req, res) { - try { - let newmail = req.body.newmail; - LOG(req, "POST /change_mail", req.user, req.body); - if (!is_email(newmail)) { - req.flash('message', "Invalid mail address!"); - return res.redirect('/change_mail'); - } - if (sql_is_mail_taken.get(newmail)) { - req.flash('message', "That mail address is already taken!"); - return res.redirect('/change_mail'); - } - sql_change_mail.run(newmail, req.user.user_id); - req.flash('message', "Your mail address has been changed."); - return res.redirect('/profile'); - } catch (err) { - console.log(err); - req.flash('message', err.message); - return res.redirect('/change_mail'); - } +app.get('/forum/page/:page', may_be_logged_in, function (req, res) { + LOG(req, "GET /forum/page/" + req.params.page); + show_forum_page(req, res, req.params.page | 0); }); -app.post('/change_about', must_be_logged_in, function (req, res) { - try { - LOG(req, "POST /change_about", req.user.name); - sql_change_about.run(req.body.about, req.user.user_id); - return res.redirect('/profile'); - } catch (err) { - console.log(err); - req.flash('message', err.message); - return res.redirect('/change_about'); +app.get('/forum/thread/:thread_id', may_be_logged_in, function (req, res) { + LOG(req, "GET /forum/thread/" + req.params.thread_id); + let thread_id = req.params.thread_id | 0; + let thread = FORUM_GET_THREAD.get(thread_id); + let posts = FORUM_LIST_POSTS.all(thread_id); + if (!thread) + return res.status(404).send("Invalid thread ID."); + for (let i = 0; i < posts.length; ++i) { + posts[i].body = linkify_post(posts[i].body); + posts[i].edited = posts[i].mtime !== posts[i].ctime; + posts[i].ctime = human_date(posts[i].ctime); + posts[i].mtime = human_date(posts[i].mtime); } + res.set("Cache-Control", "no-store"); + res.render('forum_thread.ejs', { + user: req.user, + thread: thread, + posts: posts, + }); +}); + +app.get('/forum/post', must_be_logged_in, function (req, res) { + LOG(req, "GET /forum/post"); + res.render('forum_post.ejs', { + user: req.user, + }); +}); + +app.post('/forum/post', must_be_logged_in, function (req, res) { + LOG(req, "POST /forum/post"); + let user_id = req.user.user_id; + let subject = req.body.subject.trim(); + let body = req.body.body; + if (subject.length === 0) + subject = "Untitled"; + let thread_id = FORUM_NEW_THREAD.run(user_id, subject).lastInsertRowid; + FORUM_NEW_POST.run(thread_id, user_id, body); + res.redirect('/forum/thread/'+thread_id); +}); + +app.get('/forum/edit/:post_id', must_be_logged_in, function (req, res) { + // TODO: edit subject if editing first post + LOG(req, "GET /forum/edit/" + req.params.post_id); + let post_id = req.params.post_id | 0; + let post = FORUM_GET_POST.get(post_id); + if (!post || post.author_id != req.user.user_id) + return res.status(404).send("Invalid post ID."); + post.ctime = human_date(post.ctime); + post.mtime = human_date(post.mtime); + res.render('forum_edit.ejs', { + user: req.user, + post: post, + }); +}); + +app.post('/forum/edit/:post_id', must_be_logged_in, function (req, res) { + LOG(req, "POST /forum/edit/" + req.params.post_id); + let user_id = req.user.user_id; + let post_id = req.params.post_id | 0; + let body = req.body.body; + let thread_id = FORUM_EDIT_POST.get(body, post_id, user_id); + res.redirect('/forum/thread/'+thread_id); +}); + +app.get('/forum/reply/:post_id', must_be_logged_in, function (req, res) { + LOG(req, "GET /forum/reply/" + req.params.post_id); + let post_id = req.params.post_id | 0; + let post = FORUM_GET_POST.get(post_id); + if (!post) + return res.status(404).send("Invalid post ID."); + let thread = FORUM_GET_THREAD.get(post.thread_id); + post.body = linkify_post(post.body); + post.edited = post.mtime !== post.ctime; + post.ctime = human_date(post.ctime); + post.mtime = human_date(post.mtime); + res.render('forum_reply.ejs', { + user: req.user, + thread: thread, + post: post, + }); +}); + +app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) { + LOG(req, "POST /forum/reply/" + req.params.thread_id); + let thread_id = req.params.thread_id | 0; + let user_id = req.user.user_id; + let body = req.body.body; + FORUM_NEW_POST.run(thread_id, user_id, body); + res.redirect('/forum/thread/'+thread_id); }); /* * GAME LOBBY */ -const QUERY_LIST_GAMES_OF_TITLE = db.prepare(` - SELECT *, - EXISTS ( - SELECT 1 FROM players - WHERE players.game_id = game_view.game_id - AND user_id = $user_id - AND active_role IN ( 'All', 'Both', role ) - ) AS is_your_turn - FROM game_view - WHERE title_id = $title_id AND private = 0 - AND EXISTS ( - SELECT 1 FROM players - WHERE players.game_id = game_view.game_id - AND user_id = game_view.owner_id - ) - ORDER BY status ASC, mtime DESC -`); +let TITLES = {}; +let RULES = {}; +let ROLES = {}; -const QUERY_LIST_GAMES_OF_USER = db.prepare(` - SELECT *, - EXISTS ( - SELECT 1 FROM players - WHERE players.game_id = game_view.game_id - AND user_id = $user_id - AND active_role IN ( 'All', 'Both', role ) - ) AS is_your_turn - FROM game_view - WHERE owner_id = $user_id - OR EXISTS ( - SELECT 1 FROM players - WHERE players.game_id = game_view.game_id - AND user_id = $user_id - ) - ORDER BY status ASC, mtime DESC -`); +function load_rules() { + const SQL_SELECT_TITLES = SQL("SELECT * FROM titles"); + const SQL_SELECT_TITLE_ROLES = SQL("SELECT role FROM roles WHERE title_id=?").pluck(); + for (let title of SQL_SELECT_TITLES.all()) { + let title_id = title.title_id; + if (fs.existsSync(__dirname + "/public/" + title_id + "/rules.js")) { + console.log("Loading rules for " + title_id); + try { + TITLES[title_id] = title; + RULES[title_id] = require("./public/" + title_id + "/rules.js"); + ROLES[title_id] = SQL_SELECT_TITLE_ROLES.all(title_id); + } catch (err) { + console.log(err); + } + } else { + console.log("Cannot find rules for " + title_id); + } + } +} -const QUERY_PLAYERS = db.prepare("SELECT role, user_id, user_name FROM player_view WHERE game_id = ?"); -const QUERY_PLAYERS_FULL = db.prepare(` - SELECT - players.user_id, - players.role, - users.name, - users.mail, - users.notifications - FROM players - JOIN users ON players.user_id = users.user_id - WHERE players.game_id = ? -`); +load_rules(); -const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?"); -const QUERY_TITLE = db.prepare("SELECT * FROM titles WHERE title_id = ?"); -const QUERY_ROLES = db.prepare("SELECT role FROM roles WHERE title_id = ? ORDER BY rowid").pluck(); -const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner_id = ?"); -const QUERY_TITLE_FROM_GAME = db.prepare("SELECT title_id FROM games WHERE game_id = ?").pluck(); -const QUERY_ROLE_FROM_GAME_AND_USER = db.prepare("SELECT role FROM players WHERE game_id = ? AND user_id = ?").pluck(); -const QUERY_IS_SOLO = db.prepare("SELECT COUNT(DISTINCT user_id) = 1 FROM players WHERE game_id = ?").pluck(); -const QUERY_IS_RANDOM = db.prepare("SELECT random FROM games WHERE game_id = ?").pluck(); - -const QUERY_JOIN_GAME_TRY = db.prepare("INSERT OR IGNORE INTO players (user_id, game_id, role) VALUES (?,?,?)"); -const QUERY_JOIN_GAME = db.prepare("INSERT INTO players (user_id, game_id, role) VALUES (?,?,?)"); -const QUERY_PART_GAME = db.prepare("DELETE FROM players WHERE game_id = ? AND role = ?"); -const QUERY_START_GAME = db.prepare("UPDATE games SET status = 1, state = ?, active = ?, mtime = datetime('now') WHERE game_id = ?"); -const QUERY_CREATE_GAME = db.prepare(` - INSERT INTO games - (owner_id,title_id,scenario,options,private,random,ctime,mtime,description,status,state) - VALUES - (?,?,?,?,?,?,datetime('now'),datetime('now'),?,0,NULL) -`); -const QUERY_UPDATE_GAME_SET_PRIVATE = db.prepare("UPDATE games SET private = 1 WHERE game_id = ?"); -const QUERY_ASSIGN_ROLE = db.prepare("UPDATE players SET role = ? WHERE game_id = ? AND user_id = ? AND role = ?"); +const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,private,random,description) VALUES (?,?,?,?,?,?,?)"); +const SQL_DELETE_GAME = SQL("DELETE FROM games WHERE game_id=? AND owner_id=?"); + +const SQL_SELECT_GAME_CHAT = SQL("SELECT chat_id,time,name,message FROM game_chat_view WHERE game_id=? AND chat_id>?").raw(); +const SQL_INSERT_GAME_CHAT = SQL("INSERT INTO game_chat (game_id,user_id,message) VALUES (?,?,?) RETURNING chat_id,time,'',message").raw(); + +const SQL_SELECT_GAME_STATE = SQL("SELECT state FROM game_state WHERE game_id=?").pluck(); +const SQL_UPDATE_GAME_STATE = SQL("INSERT OR REPLACE INTO game_state (game_id,state,active,mtime) VALUES (?,?,?,datetime('now'))"); +const SQL_UPDATE_GAME_RESULT = SQL("UPDATE games SET status=?, result=? WHERE game_id=?"); +const SQL_UPDATE_GAME_PRIVATE = SQL("UPDATE games SET private=1 WHERE game_id=?"); +const SQL_INSERT_REPLAY = SQL("INSERT INTO game_replay (game_id,role,action,arguments) VALUES (?,?,?,?)"); -const QUERY_COUNT_OPEN_GAMES = db.prepare("SELECT COUNT(*) FROM games WHERE owner_id = ? AND status = 0").pluck(); -const QUERY_DELETE_GAME = db.prepare("DELETE FROM games WHERE game_id = ?"); +const SQL_SELECT_GAME = SQL("SELECT * FROM games WHERE game_id=?"); +const SQL_SELECT_GAME_VIEW = SQL("SELECT * FROM game_view WHERE game_id=?"); +const SQL_SELECT_GAME_FULL_VIEW = SQL("SELECT * FROM game_full_view WHERE game_id=?"); +const SQL_SELECT_GAME_TITLE = SQL("SELECT title_id FROM games WHERE game_id=?").pluck(); +const SQL_SELECT_GAME_RANDOM = SQL("SELECT random FROM games WHERE game_id=?").pluck(); -const QUERY_REMATCH_FIND = db.prepare(` - SELECT game_id FROM games WHERE status<3 AND description=? -`).pluck(); +const SQL_SELECT_PLAYERS = SQL("SELECT * FROM players NATURAL JOIN user_view WHERE game_id=?"); +const SQL_SELECT_PLAYERS_JOIN = SQL("SELECT role, user_id, name FROM players NATURAL JOIN users WHERE game_id=?"); +const SQL_SELECT_PLAYER_ROLE = SQL("SELECT role FROM players WHERE game_id=? AND user_id=?").pluck(); +const SQL_INSERT_PLAYER_ROLE = SQL("INSERT OR IGNORE INTO players (game_id,role,user_id) VALUES (?,?,?)"); +const SQL_DELETE_PLAYER_ROLE = SQL("DELETE FROM players WHERE game_id=? AND role=?"); +const SQL_UPDATE_PLAYER_ROLE = db.prepare("UPDATE players SET role=? WHERE game_id=? AND role=? AND user_id=?"); -const QUERY_REMATCH_CREATE = db.prepare(` +const SQL_SELECT_OPEN_GAMES = db.prepare("SELECT * FROM games WHERE status=0"); +const SQL_COUNT_OPEN_GAMES = SQL("SELECT COUNT(*) FROM games WHERE owner_id=? AND status=0").pluck(); + +const SQL_SELECT_REMATCH = SQL("SELECT game_id FROM games WHERE status < 3 AND description=?").pluck(); +const SQL_INSERT_REMATCH = SQL(` INSERT INTO games - (owner_id, title_id, scenario, options, private, random, ctime, mtime, description, status, state) + (owner_id, title_id, scenario, options, private, random, description) SELECT - $user_id, title_id, scenario, options, private, random, datetime('now'), datetime('now'), $magic, 0, NULL + $user_id, title_id, scenario, options, private, random, $magic FROM games WHERE game_id = $game_id AND NOT EXISTS ( SELECT * FROM games WHERE description=$magic ) `); -let RULES = {}; -let ROLES = {}; -for (let title_id of db.prepare("SELECT * FROM titles").pluck().all()) { - if (fs.existsSync(__dirname + "/public/" + title_id + "/rules.js")) { - console.log("Loading rules for " + title_id); - try { - RULES[title_id] = require("./public/" + title_id + "/rules.js"); - ROLES[title_id] = QUERY_ROLES.all(title_id); - } catch (err) { - console.log(err); - } - } else { - console.log("Cannot find rules for " + title_id); +const QUERY_LIST_GAMES = SQL(` + SELECT * FROM game_view + WHERE private=0 AND status < 2 + ORDER BY status ASC, mtime DESC + `); + +const QUERY_LIST_GAMES_OF_TITLE = SQL(` + SELECT * FROM game_view + WHERE private=0 AND title_id=$title_id + ORDER BY status ASC, mtime DESC + `); + +const QUERY_LIST_GAMES_OF_USER = SQL(` + SELECT * FROM game_view + WHERE owner_id=$user_id OR game_id IN ( SELECT game_id FROM players WHERE players.user_id=$user_id ) + ORDER BY status ASC, mtime DESC + `); + +function is_active(game, players, user_id) { + if (game.status !== 1 || user_id === 0) + return false; + let active = game.active; + for (let i = 0; i < players.length; ++i) { + let p = players[i]; + if ((p.user_id === user_id) && (active === 'All' || active === 'Both' || active === p.role)) + return true; + } + return false; +} + +function is_shared(game, players, user_id) { + let n = 0; + for (let i = 0; i < players.length; ++i) + if (players[i].user_id === user_id) + ++n; + return n > 1; +} + +function is_solo(players) { + return players.every(p => p.user_id === players[0].user_id) +} + +function annotate_games(games, user_id) { + for (let i = 0; i < games.length; ++i) { + let game = games[i]; + let players = SQL_SELECT_PLAYERS_JOIN.all(game.game_id); + game.player_names = players.map(p => { + let name = p.name.replace(/ /g, '\xa0'); + return p.user_id > 0 ? `<a href="/user/${p.name}">${name}</a>` : name; + }).join(", "); + game.is_active = is_active(game, players, user_id); + game.is_shared = is_shared(game, players, user_id); + game.ctime = human_date(game.ctime); + game.mtime = human_date(game.mtime); } } -app.get('/', function (req, res) { - res.render('index.ejs', { user: req.user, message: req.flash('message') }); +app.get('/games', may_be_logged_in, function (req, res) { + LOG(req, "GET /join"); + let games; + if (req.isAuthenticated()) { + games = QUERY_LIST_GAMES.all(); + annotate_games(games, req.user.user_id); + } else { + games = QUERY_LIST_GAMES.all({user_id: 0}); + annotate_games(games, 0); + } + let open_games = games.filter(game => game.status === 0); + let active_games = games.filter(game => game.status === 1); + res.set("Cache-Control", "no-store"); + res.render('games.ejs', { + user: req.user, + open_games: open_games, + active_games: active_games, + }); }); app.get('/profile', must_be_logged_in, function (req, res) { LOG(req, "GET /profile"); let avatar = get_avatar(req.user.mail); let games = QUERY_LIST_GAMES_OF_USER.all({user_id: req.user.user_id}); - humanize(games); - linkify_player_names(games); + annotate_games(games, req.user.user_id); let open_games = games.filter(game => game.status === 0); let active_games = games.filter(game => game.status === 1); let finished_games = games.filter(game => game.status === 2); res.set("Cache-Control", "no-store"); - res.render('profile.ejs', { user: req.user, avatar: avatar, + res.render('profile.ejs', { + user: req.user, + avatar: avatar, open_games: open_games, active_games: active_games, finished_games: finished_games, - message: req.flash('message') }); }); -app.get('/info/:title_id', function (req, res) { +app.get('/info/:title_id', may_be_logged_in, function (req, res) { LOG(req, "GET /info/" + req.params.title_id); let title_id = req.params.title_id; - let title = QUERY_TITLE.get(title_id); + let title = TITLES[title_id]; if (!title) - return res.status(404).send("That title doesn't exist."); + return res.status(404).send("Invalid title."); if (req.isAuthenticated()) { - let games = QUERY_LIST_GAMES_OF_TITLE.all({user_id: req.user.user_id, title_id: title_id}); - humanize(games); - linkify_player_names(games); + let games = QUERY_LIST_GAMES_OF_TITLE.all({title_id: title_id}); + annotate_games(games, req.user.user_id); let open_games = games.filter(game => game.status === 0); let active_games = games.filter(game => game.status === 1); let finished_games = games.filter(game => game.status === 2); res.set("Cache-Control", "no-store"); - res.render('info.ejs', { user: req.user, title: title, + res.render('info.ejs', { + user: req.user, + title: title, open_games: open_games, active_games: active_games, finished_games: finished_games, - message: req.flash('message') }); } else { res.set("Cache-Control", "no-store"); - res.render('info.ejs', { user: req.user, title: title, + res.render('info.ejs', { + user: req.user, + title: title, open_games: [], active_games: [], finished_games: [], - message: req.flash('message') }); } }); @@ -741,10 +1028,15 @@ app.get('/info/:title_id', function (req, res) { app.get('/create/:title_id', must_be_logged_in, function (req, res) { LOG(req, "GET /create/" + req.params.title_id); let title_id = req.params.title_id; - let title = QUERY_TITLE.get(title_id); + let title = TITLES[title_id]; if (!title) - return res.status(404).send("That title doesn't exist."); - res.render('create.ejs', { user: req.user, message: req.flash('message'), title: title, scenarios: RULES[title_id].scenarios }); + return res.status(404).send("Invalid title."); + res.render('create.ejs', { + user: req.user, + title: title, + scenarios: RULES[title_id].scenarios, + flash: req.flash('message') + }); }); function options_json_replacer(key, value) { @@ -767,49 +1059,38 @@ app.post('/create/:title_id', must_be_logged_in, function (req, res) { let scenario = req.body.scenario; let options = JSON.stringify(req.body, options_json_replacer); LOG(req, "POST /create/" + req.params.title_id, scenario, options, priv, JSON.stringify(descr)); - try { - let count = QUERY_COUNT_OPEN_GAMES.get(user_id); - if (count >= MAX_OPEN_GAMES) { - req.flash('message', "You have too many open games!"); - return res.redirect('/create/'+title_id); - } - if (!(title_id in RULES)) { - return res.status(404).send("That title doesn't exist."); - } - if (!RULES[title_id].scenarios.includes(scenario)) { - return res.status(404).send("That scenario doesn't exist."); - } - let info = QUERY_CREATE_GAME.run(user_id, title_id, scenario, options, priv ? 1 : 0, rand ? 1 : 0, descr); - res.redirect('/join/'+info.lastInsertRowid); - } catch (err) { - req.flash('message', err.toString()); + let count = SQL_COUNT_OPEN_GAMES.get(user_id); + if (count >= MAX_OPEN_GAMES) { + req.flash('message', "You have too many open games!"); return res.redirect('/create/'+title_id); } + if (!(title_id in RULES)) { + return res.send("Invalid title."); + } + if (!RULES[title_id].scenarios.includes(scenario)) { + return res.send("Invalid scenario."); + } + let info = SQL_INSERT_GAME.run(user_id, title_id, scenario, options, priv ? 1 : 0, rand ? 1 : 0, descr); + res.redirect('/join/'+info.lastInsertRowid); }); app.get('/delete/:game_id', must_be_logged_in, function (req, res) { let game_id = req.params.game_id; LOG(req, "GET /delete/" + game_id); - try { - let game = QUERY_GAME_OWNER.get(game_id, req.user.user_id); - if (!game) { - req.flash('message', "Only the game owner can delete the game!"); - return res.redirect('/join/'+game_id); - } - QUERY_DELETE_GAME.run(game_id); + let title_id = SQL_SELECT_GAME_TITLE.get(game_id); + let info = SQL_DELETE_GAME.run(game_id, req.user.user_id); + if (info.changes === 0) + return res.send("Not authorized to delete that game ID."); + if (info.changes === 1) update_join_clients_deleted(game_id); - res.redirect('/info/'+game.title_id); - } catch (err) { - req.flash('message', err.toString()); - return res.redirect('/join/'+game_id); - } + res.redirect('/info/'+title_id); }); function join_rematch(req, res, game_id, role) { - let is_random = QUERY_IS_RANDOM.get(game_id); + let is_random = SQL_SELECT_GAME_RANDOM.get(game_id); if (is_random) { for (let i = 1; i <= 6; ++i) { - let info = QUERY_JOIN_GAME_TRY.run(req.user.user_id, game_id, 'Random ' + i); + let info = SQL_INSERT_PLAYER_ROLE.run(game_id, 'Random ' + i, req.user.user_id); if (info.changes === 1) { update_join_clients_players(game_id); break; @@ -817,7 +1098,7 @@ function join_rematch(req, res, game_id, role) { } return res.redirect('/join/'+game_id); } else { - let info = QUERY_JOIN_GAME_TRY.run(req.user.user_id, game_id, role); + let info = SQL_INSERT_PLAYER_ROLE.run(game_id, role, req.user.user_id); if (info.changes === 1) update_join_clients_players(game_id); return res.redirect('/join/'+game_id); @@ -828,22 +1109,17 @@ app.get('/rematch/:old_game_id/:role', must_be_logged_in, function (req, res) { LOG(req, "GET /rematch/" + req.params.old_game_id); let old_game_id = req.params.old_game_id | 0; let role = req.params.role; - try { - let magic = "\u{1F503} " + old_game_id; - let new_game_id = 0; - let info = QUERY_REMATCH_CREATE.run({user_id: req.user.user_id, game_id: old_game_id, magic: magic}); - if (info.changes === 1) - new_game_id = info.lastInsertRowid; - else - new_game_id = QUERY_REMATCH_FIND.get(magic); - if (new_game_id) - return join_rematch(req, res, new_game_id, role); - req.flash('message', "Can't create or find rematch game!"); - return res.redirect('/join/'+old_game_id); - } catch (err) { - req.flash('message', err.toString()); - return res.redirect('/join/'+old_game_id); - } + let magic = "\u{1F503} " + old_game_id; + let new_game_id = 0; + let info = SQL_INSERT_REMATCH.run({user_id: req.user.user_id, game_id: old_game_id, magic: magic}); + if (info.changes === 1) + new_game_id = info.lastInsertRowid; + else + new_game_id = SQL_SELECT_REMATCH.get(magic); + if (new_game_id) + return join_rematch(req, res, new_game_id, role); + req.flash('message', "Can't create or find rematch game!"); + return res.redirect('/join/'+old_game_id); }); let join_clients = {}; @@ -862,7 +1138,7 @@ function update_join_clients_deleted(game_id) { function update_join_clients_game(game_id) { let list = join_clients[game_id]; if (list && list.length > 0) { - let game = QUERY_GAME.get(game_id); + let game = SQL_SELECT_GAME_VIEW.get(game_id); for (let res of list) { res.write("retry: 15000\n"); res.write("event: game\n"); @@ -874,7 +1150,7 @@ function update_join_clients_game(game_id) { function update_join_clients_players(game_id) { let list = join_clients[game_id]; if (list && list.length > 0) { - let players = QUERY_PLAYERS.all(game_id); + let players = SQL_SELECT_PLAYERS_JOIN.all(game_id); let ready = RULES[list.title_id].ready(list.scenario, list.options, players); for (let res of list) { res.write("retry: 15000\n"); @@ -889,11 +1165,11 @@ function update_join_clients_players(game_id) { app.get('/join/:game_id', must_be_logged_in, function (req, res) { LOG(req, "GET /join/" + req.params.game_id); let game_id = req.params.game_id | 0; - let game = QUERY_GAME.get(game_id); + let game = SQL_SELECT_GAME_VIEW.get(game_id); if (!game) - return res.status(404).send("That game doesn't exist."); - let roles = QUERY_ROLES.all(game.title_id); - let players = QUERY_PLAYERS.all(game_id); + return res.status(404).send("Invalid game ID."); + let roles = ROLES[game.title_id]; + let players = SQL_SELECT_PLAYERS_JOIN.all(game_id); let ready = (game.status === 0) && RULES[game.title_id].ready(game.scenario, game.options, players); res.set("Cache-Control", "no-store"); res.render('join.ejs', { @@ -902,15 +1178,15 @@ app.get('/join/:game_id', must_be_logged_in, function (req, res) { roles: roles, players: players, ready: ready, - message: req.flash('message') + flash: req.flash('message') }); }); app.get('/join-events/:game_id', must_be_logged_in, function (req, res) { LOG(req, "GET /join-events/" + req.params.game_id); let game_id = req.params.game_id | 0; - let players = QUERY_PLAYERS.all(game_id); - let game = QUERY_GAME.get(game_id); + let game = SQL_SELECT_GAME_VIEW.get(game_id); + let players = SQL_SELECT_PLAYERS_JOIN.all(game_id); res.setHeader("Cache-Control", "no-store"); res.setHeader("Content-Type", "text/event-stream"); @@ -945,13 +1221,12 @@ app.get('/join/:game_id/:role', must_be_logged_in, function (req, res) { LOG(req, "GET /join/" + req.params.game_id + "/" + req.params.role); let game_id = req.params.game_id | 0; let role = req.params.role; - try { - QUERY_JOIN_GAME.run(req.user.user_id, game_id, role); + let info = SQL_INSERT_PLAYER_ROLE.run(game_id, role, req.user.user_id); + if (info.changes === 1) { update_join_clients_players(game_id); res.send("SUCCESS"); - } catch (err) { - console.log(err); - res.send(err.toString()); + } else { + res.send("Could not join game."); } }); @@ -959,14 +1234,9 @@ app.get('/part/:game_id/:role', must_be_logged_in, function (req, res) { LOG(req, "GET /part/" + req.params.game_id + "/" + req.params.role); let game_id = req.params.game_id | 0; let role = req.params.role; - try { - QUERY_PART_GAME.run(game_id, role); - update_join_clients_players(game_id); - res.send("SUCCESS"); - } catch (err) { - console.log(err); - res.send(err.toString()); - } + SQL_DELETE_PLAYER_ROLE.run(game_id, role); + update_join_clients_players(game_id); + res.send("SUCCESS"); }); function assign_random_roles(game, players) { @@ -976,77 +1246,63 @@ function assign_random_roles(game, players) { list.splice(k, 1); return r; } - let roles = QUERY_ROLES.all(game.title_id); + let roles = ROLES[game.title_id]; for (let p of players) { let old_role = p.role; p.role = pick_random_item(roles); - console.log("ASSIGN ROLE", "(" + p.user_name + ")", old_role, "->", p.role); - QUERY_ASSIGN_ROLE.run(p.role, game.game_id, p.user_id, old_role); + console.log("ASSIGN ROLE", "(" + p.name + ")", old_role, "->", p.role); + SQL_UPDATE_PLAYER_ROLE.run(p.role, game.game_id, old_role, p.user_id); } } app.get('/start/:game_id', must_be_logged_in, function (req, res) { LOG(req, "GET /start/" + req.params.game_id); let game_id = req.params.game_id | 0; - try { - let game = QUERY_GAME_OWNER.get(game_id, req.user.user_id); - if (!game) - return res.send("Only the game owner can start the game!"); - if (game.status !== 0) - return res.send("The game is already started!"); - let players = QUERY_PLAYERS.all(game_id); - if (!RULES[game.title_id].ready(game.scenario, game.options, players)) - return res.send("Invalid player configuration!"); - if (game.random) { - assign_random_roles(game, players); - update_join_clients_players(game_id); - } - let seed = random_seed(); - let state = RULES[game.title_id].setup(seed, game.scenario, game.options, players); - put_replay(game_id, null, 'setup', [seed, game.scenario, game.options, players]); - QUERY_START_GAME.run(JSON.stringify(state), state.active, game_id); - let is_solo = players.every(p => p.user_id === players[0].user_id); - if (is_solo) - QUERY_UPDATE_GAME_SET_PRIVATE.run(game_id); - update_join_clients_game(game_id); - res.send("SUCCESS"); - } catch (err) { - console.log(err); - res.send(err.toString()); + let game = SQL_SELECT_GAME.get(game_id); + if (game.owner_id !== req.user.user_id) + return res.send("Not authorized to start that game ID."); + if (game.status !== 0) + return res.send("The game is already started."); + let players = SQL_SELECT_PLAYERS.all(game_id); + if (!RULES[game.title_id].ready(game.scenario, game.options, players)) + return res.send("Invalid scenario/options/player configuration!"); + if (game.random) { + assign_random_roles(game, players); + players = SQL_SELECT_PLAYERS.all(game_id); + update_join_clients_players(game_id); } + let seed = random_seed(); + let state = RULES[game.title_id].setup(seed, game.scenario, game.options, players); + put_replay(game_id, null, 'setup', [seed, game.scenario, game.options, players]); + SQL_UPDATE_GAME_RESULT.run(1, null, game_id); + SQL_UPDATE_GAME_STATE.run(game_id, JSON.stringify(state), state.active); + if (is_solo(players)) + SQL_UPDATE_GAME_PRIVATE.run(game_id); + update_join_clients_game(game_id); + res.send("SUCCESS"); }); app.get('/play/:game_id/:role', must_be_logged_in, function (req, res) { LOG(req, "GET /play/" + req.params.game_id + "/" + req.params.role); let game_id = req.params.game_id | 0; let role = req.params.role; - try { - let title = QUERY_TITLE_FROM_GAME.get(game_id); - if (!title) - return res.redirect('/join/'+game_id); - res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role); - } catch (err) { - req.flash('message', err.toString()); + let title = SQL_SELECT_GAME_TITLE.get(game_id); + if (!title) return res.redirect('/join/'+game_id); - } + res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role); }); app.get('/play/:game_id', must_be_logged_in, function (req, res) { LOG(req, "GET /play/" + req.params.game_id); let game_id = req.params.game_id | 0; let user_id = req.user.user_id | 0; - try { - let title = QUERY_TITLE_FROM_GAME.get(game_id); - if (!title) - return res.redirect('/join/'+game_id); - let role = QUERY_ROLE_FROM_GAME_AND_USER.get(game_id, user_id); - if (!role) - return res.redirect('/'+title+'/play.html?game='+game_id+'&role=Observer'); - return res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role); - } catch (err) { - req.flash('message', err.toString()); + let title = SQL_SELECT_GAME_TITLE.get(game_id); + if (!title) return res.redirect('/join/'+game_id); - } + let role = SQL_SELECT_PLAYER_ROLE.get(game_id, user_id); + if (!role) + return res.redirect('/'+title+'/play.html?game='+game_id+'&role=Observer'); + return res.redirect('/'+title+'/play.html?game='+game_id+'&role='+role); }); /* @@ -1056,20 +1312,11 @@ app.get('/play/:game_id', must_be_logged_in, function (req, res) { const MAIL_FROM = process.env.MAIL_FROM || "Rally the Troops! <notifications@rally-the-troops.com>"; const MAIL_FOOTER = "You can unsubscribe from notifications on your profile page:\n\nhttps://rally-the-troops.com/profile\n"; -const sql_notify_too_soon = db.prepare("SELECT datetime('now') < datetime(time, ?) FROM notifications WHERE user_id = ? AND game_id = ?").pluck(); -const sql_notify_update = db.prepare("INSERT OR REPLACE INTO notifications VALUES ( ?, ?, datetime('now') )"); -const sql_notify_delete = db.prepare("DELETE FROM notifications WHERE user_id = ? AND game_id = ?"); -const sql_offline_user = db.prepare("SELECT * FROM users WHERE user_id = ? AND datetime('now') > datetime(atime, ?)"); - -const QUERY_LIST_YOUR_TURN = db.prepare(` - SELECT games.game_id, games.title_id, games.active, players.user_id, users.name, users.mail - FROM games - JOIN players ON games.game_id = players.game_id AND ( games.active = players.role OR games.active = 'Both' OR games.active = 'All' ) - JOIN users ON users.user_id = players.user_id AND users.notifications = 1 - WHERE games.status = 1 AND datetime('now') > datetime(games.mtime, '+1 hour') -`); +const SQL_SELECT_NOTIFIED = SQL("SELECT datetime('now') < datetime(time,?) FROM last_notified WHERE game_id=? AND user_id=?").pluck(); +const SQL_INSERT_NOTIFIED = SQL("INSERT OR REPLACE INTO last_notified (game_id,user_id,time) VALUES (?,?,datetime('now'))"); +const SQL_DELETE_NOTIFIED = SQL("DELETE FROM last_notified WHERE game_id=? AND user_id=?"); -const QUERY_LIST_UNSTARTED_GAMES = db.prepare("SELECT * FROM game_view WHERE status = 0"); +const QUERY_LIST_YOUR_TURN = SQL("SELECT * FROM your_turn_reminder"); function mail_callback(err, info) { if (err) @@ -1096,59 +1343,58 @@ function mail_password_reset_token(user, token) { "https://rally-the-troops.com/reset_password/" + user.mail + "/" + token + "\n\n" + "If you did not request a password reset you can ignore this mail.\n"; console.log("SENT MAIL:", mail_addr(user), subject); - mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); + if (mailer) + mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); } function mail_new_message(user, msg_id, msg_from, msg_subject, msg_body) { - if (!mailer) - return; let subject = "You have a new message from " + msg_from + "."; let body = "Subject: " + msg_subject + "\n\n" + msg_body + "\n\n" + "https://rally-the-troops.com/message/read/" + msg_id + "\n\n" + MAIL_FOOTER; console.log("SENT MAIL:", mail_addr(user), subject); - mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); + if (mailer) + mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); } function mail_your_turn_notification(user, game_id, interval) { - let too_soon = sql_notify_too_soon.get(interval, user.user_id, game_id); + let too_soon = SQL_SELECT_NOTIFIED.get(interval, game_id, user.user_id); if (!too_soon) { - sql_notify_update.run(user.user_id, game_id); - let game = QUERY_GAME.get(game_id); + SQL_INSERT_NOTIFIED.run(game_id, user.user_id); + let game = SQL_SELECT_GAME_FULL_VIEW.get(game_id); let subject = game.title_name + " - " + game_id + " - Your turn!"; let body = mail_describe(game) + "It's your turn.\n\n" + "https://rally-the-troops.com/play/" + game_id + "\n\n" + MAIL_FOOTER; console.log("SENT MAIL:", mail_addr(user), subject); - mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); + if (mailer) + mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); } } function reset_your_turn_notification(user, game_id) { - sql_notify_delete.run(user.user_id, game_id); + SQL_DELETE_NOTIFIED.run(game_id, user.user_id); } function mail_ready_to_start_notification(user, game_id, interval) { - let too_soon = sql_notify_too_soon.get(interval, user.user_id, game_id); + let too_soon = SQL_SELECT_NOTIFIED.get(interval, game_id, user.user_id); if (!too_soon) { - sql_notify_update.run(user.user_id, game_id); - let game = QUERY_GAME.get(game_id); + SQL_INSERT_NOTIFIED.run(game_id, user.user_id); + let game = SQL_SELECT_GAME_FULL_VIEW.get(game_id); let subject = game.title_name + " - " + game_id + " - Ready to start!"; let body = mail_describe(game) + "Your game is ready to start.\n\n" + "https://rally-the-troops.com/join/" + game_id + "\n\n" + MAIL_FOOTER; console.log("SENT MAIL:", mail_addr(user), subject); - mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); + if (mailer) + mailer.sendMail({ from: MAIL_FROM, to: mail_addr(user), subject: subject, text: body }, mail_callback); } } function mail_your_turn_notification_to_offline_users(game_id, old_active, active) { - if (!mailer) - return; - function is_online(game_id, user_id) { for (let other of clients[game_id]) if (other.user_id === user_id) @@ -1160,9 +1406,9 @@ function mail_your_turn_notification_to_offline_users(game_id, old_active, activ if (old_active === active && active !== 'Both' && active !== 'All') return; - let players = QUERY_PLAYERS_FULL.all(game_id); + let players = SQL_SELECT_PLAYERS.all(game_id); for (let p of players) { - if (p.notifications) { + if (p.notify) { if (active === p.role || active === 'Both' || active === 'All') { if (is_online(game_id, p.user_id)) { reset_your_turn_notification(p, game_id); @@ -1177,24 +1423,18 @@ function mail_your_turn_notification_to_offline_users(game_id, old_active, activ } function notify_your_turn_reminder() { - if (!mailer) - return; for (let item of QUERY_LIST_YOUR_TURN.all()) { - if (!QUERY_IS_SOLO.get(item.game_id)) { - mail_your_turn_notification(item, item.game_id, '+25 hours'); - } + mail_your_turn_notification(item, item.game_id, '+25 hours'); } } function notify_ready_to_start_reminder() { - if (!mailer) - return; - for (let game of QUERY_LIST_UNSTARTED_GAMES.all()) { - let players = QUERY_PLAYERS.all(game.game_id); + for (let game of SQL_SELECT_OPEN_GAMES.all()) { + let players = SQL_SELECT_PLAYERS.all(game.game_id); if (RULES[game.title_id].ready(game.scenario, game.options, players)) { - let owner = sql_offline_user.get(game.owner_id, '+3 minutes'); + let owner = SQL_OFFLINE_USER.get(game.owner_id, '+3 minutes'); if (owner) { - if (owner.notifications) + if (owner.notify) mail_ready_to_start_notification(owner, game.game_id, '+25 hours'); } } @@ -1208,16 +1448,9 @@ setInterval(notify_your_turn_reminder, 15 * 60 * 1000); setInterval(notify_ready_to_start_reminder, 5 * 60 * 1000); /* - * GAME PLAYING + * GAME SERVER */ -const QUERY_SELECT_CHAT = db.prepare("SELECT chat FROM chats WHERE game_id = ?").pluck(); -const QUERY_UPDATE_CHAT = db.prepare("INSERT OR REPLACE INTO chats ( game_id, time, chat ) VALUES ( ?, datetime('now'), ? )"); -const QUERY_SELECT_GAME_STATE = db.prepare("SELECT state FROM games WHERE game_id = ?"); -const QUERY_UPDATE_GAME_STATE = db.prepare("UPDATE games SET state = ?, active = ?, status = ?, result = ?, mtime = datetime('now') WHERE game_id = ?"); -const QUERY_CONNECT_GAME = db.prepare("SELECT title_id, state FROM games WHERE title_id = ? AND game_id = ?"); -const QUERY_RESTART_GAME = db.prepare("UPDATE games SET state = ?, mtime = datetime('now') WHERE game_id = ?"); - let clients = {}; function send_state(socket, state) { @@ -1237,32 +1470,27 @@ function send_state(socket, state) { } function get_game_state(game_id) { - let row = QUERY_SELECT_GAME_STATE.get(game_id); - if (!row) + let game_state = SQL_SELECT_GAME_STATE.get(game_id); + if (!game_state) throw new Error("No game with that ID"); - return JSON.parse(row.state); + return JSON.parse(game_state); } function put_game_state(game_id, state, old_active) { - let status = 1; - let result = null; if (state.state === 'game_over') { - status = 2; - result = state.result; + SQL_UPDATE_GAME_RESULT.run(2, state.result, game_id); } - QUERY_UPDATE_GAME_STATE.run(JSON.stringify(state), state.active, status, result, game_id); + SQL_UPDATE_GAME_STATE.run(game_id, JSON.stringify(state), state.active); for (let other of clients[game_id]) send_state(other, state); update_join_clients_game(game_id); mail_your_turn_notification_to_offline_users(game_id, old_active, state.active); } -const QUERY_INSERT_REPLAY = db.prepare("INSERT INTO replay ( game_id, time, role, action, arguments ) VALUES ( ?, datetime('now'), ?, ?, ? )"); - function put_replay(game_id, role, action, args) { if (args !== undefined && args !== null) args = JSON.stringify(args); - QUERY_INSERT_REPLAY.run(game_id, role, action, args); + SQL_INSERT_REPLAY.run(game_id, role, action, args); } function on_action(socket, action, arg) { @@ -1293,25 +1521,12 @@ function on_resign(socket) { } } -function send_chat(socket, chat) { - if (socket.role === "Observer") - return; - if (chat && socket.chat_length < chat.length) { - SLOG(socket, "<-- CHAT LOG", socket.chat_length, "..", chat.length); - socket.emit('chat', socket.chat_length, chat.slice(socket.chat_length)); - socket.chat_length = chat.length; - } -} - -function on_getchat(socket, old_len) { +function on_getchat(socket, seen) { try { - socket.chat_length = old_len; - let chat = QUERY_SELECT_CHAT.get(socket.game_id); - if (!chat) - chat = []; - else - chat = JSON.parse(chat); - send_chat(socket, chat); + let chat = SQL_SELECT_GAME_CHAT.all(socket.game_id, seen); + SLOG(socket, "<-- CHAT", seen, chat.length); + for (let i = 0; i < chat.length; ++i) + socket.emit('chat', chat[i]); } catch (err) { console.log(err); return socket.emit('error', err.toString()); @@ -1319,18 +1534,14 @@ function on_getchat(socket, old_len) { } function on_chat(socket, message) { - message = message.substring(0,4096); - SLOG(socket, "--> CHAT"); + message = message.substring(0,4000); try { - let chat = QUERY_SELECT_CHAT.get(socket.game_id); - if (!chat) - chat = []; - else - chat = JSON.parse(chat); - chat.push([new Date(), socket.user_name, message]); - QUERY_UPDATE_CHAT.run(socket.game_id, JSON.stringify(chat)); + let chat = SQL_INSERT_GAME_CHAT.get(socket.game_id, socket.user_id, message); + chat[2] = socket.user_name; + SLOG(socket, "--> CHAT", chat); for (let other of clients[socket.game_id]) - send_chat(other, chat); + if (other.role !== "Observer") + other.emit('chat', chat); } catch (err) { console.log(err); return socket.emit('error', err.toString()); @@ -1340,10 +1551,10 @@ function on_chat(socket, message) { function on_debug(socket) { SLOG(socket, "<-- DEBUG"); try { - let row = QUERY_SELECT_GAME_STATE.get(socket.game_id); - if (!row) + let game_state = SQL_SELECT_GAME_STATE.get(socket.game_id); + if (!game_state) return socket.emit('error', "No game with that ID."); - socket.emit('debug', row.state); + socket.emit('debug', game_state); } catch (err) { console.log(err); return socket.emit('error', err.toString()); @@ -1353,10 +1564,10 @@ function on_debug(socket) { function on_save(socket) { SLOG(socket, "<-- SAVE"); try { - let row = QUERY_SELECT_GAME_STATE.get(socket.game_id); - if (!row) + let game_state = SQL_SELECT_GAME_STATE.get(socket.game_id); + if (!game_state) return socket.emit('error', "No game with that ID."); - socket.emit('save', row.state); + socket.emit('save', game_state); } catch (err) { console.log(err); return socket.emit('error', err.toString()); @@ -1367,7 +1578,8 @@ function on_restore(socket, state_text) { SLOG(socket, '--> RESTORE', state_text); try { let state = JSON.parse(state_text); - QUERY_UPDATE_GAME_STATE.run(state_text, state.active, 1, null, socket.game_id); + SQL_UPDATE_GAME_RESULT.run(1, null, game_id); + SQL_UPDATE_GAME_STATE.run(game_id, state_text, state.active); for (let other of clients[socket.game_id]) send_state(other, state); } catch (err) { @@ -1385,23 +1597,22 @@ function broadcast_presence(game_id) { } io.on('connection', (socket) => { - socket.title_id = socket.handshake.query.title; + socket.title_id = socket.handshake.query.title || "unknown"; socket.game_id = socket.handshake.query.game | 0; socket.user_id = socket.request.user.user_id | 0; socket.user_name = socket.request.user.name; socket.role = socket.handshake.query.role; socket.log_length = 0; - socket.chat_length = 0; socket.rules = RULES[socket.title_id]; SLOG(socket, "CONNECT"); try { - let game = QUERY_CONNECT_GAME.get(socket.title_id, socket.game_id); - if (!game) - return socket.emit('error', "That game does not exist."); + let title_id = SQL_SELECT_GAME_TITLE.get(socket.game_id); + if (title_id !== socket.title_id) + return socket.emit('error', "Invalid game ID."); - let players = QUERY_PLAYERS.all(socket.game_id); + let players = SQL_SELECT_PLAYERS.all(socket.game_id); if (socket.role !== "Observer") { let me; @@ -1434,7 +1645,7 @@ io.on('connection', (socket) => { if (socket.role !== "Observer") { socket.on('action', (action, arg) => on_action(socket, action, arg)); socket.on('resign', () => on_resign(socket)); - socket.on('getchat', (old_len) => on_getchat(socket, old_len)); + socket.on('getchat', (seen) => on_getchat(socket, seen)); socket.on('chat', (message) => on_chat(socket, message)); socket.on('debug', () => on_debug(socket)); @@ -1450,7 +1661,8 @@ io.on('connection', (socket) => { send_state(other, state); } let state_text = JSON.stringify(state); - QUERY_RESTART_GAME.run(state_text, socket.game_id); + SQL_UPDATE_GAME_RESULT.run(1, null, socket.game_id); + SQL_UPDATE_GAME_STATE.run(socket.game_id, state_text, state.active); } catch (err) { console.log(err); return socket.emit('error', err.toString()); @@ -1460,7 +1672,7 @@ io.on('connection', (socket) => { broadcast_presence(socket.game_id); - send_state(socket, JSON.parse(game.state)); + send_state(socket, get_game_state(socket.game_id)); } catch (err) { console.log(err); @@ -1468,374 +1680,23 @@ io.on('connection', (socket) => { } }); -// EXTRAS +/* + * HIDDEN EXTRAS + */ -const QUERY_TITLES = db.prepare("SELECT * FROM titles ORDER BY title_name"); const QUERY_STATS = db.prepare(` SELECT title_id, scenario, result, count(*) AS count - FROM game_view - WHERE status=2 AND is_solo=0 + FROM game_full_view + WHERE status=2 AND private=0 GROUP BY title_name, scenario, result `); -app.get('/stats', function (req, res) { +app.get('/stats', may_be_logged_in, function (req, res) { LOG(req, "GET /stats"); let stats = QUERY_STATS.all(); - let titles = Object.fromEntries(QUERY_TITLES.all().map(t => [t.title_id, t.title_name])); res.render('stats.ejs', { user: req.user, - message: req.flash('message'), stats: stats, - title_role_map: ROLES, title_name_map: titles, title_rule_map: RULES - }); -}); - -app.get('/users', function (req, res) { - LOG(req, "GET /users"); - let rows = db.prepare("SELECT user_id, name, mail, ctime, atime FROM users ORDER BY atime DESC").all(); - rows.forEach(row => { - row.avatar = get_avatar(row.mail); - row.ctime = human_date(row.ctime); - row.atime = human_date(row.atime); - }); - res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows }); -}); - -const QUERY_LIST_GAMES = db.prepare(` - SELECT *, - EXISTS ( - SELECT 1 FROM players - WHERE players.game_id = game_view.game_id - AND user_id = $user_id - AND active_role IN ( 'All', 'Both', role ) - ) AS is_your_turn - FROM game_view - WHERE private = 0 AND status < 2 - AND EXISTS ( - SELECT 1 FROM players - WHERE players.game_id = game_view.game_id - AND user_id = game_view.owner_id - ) - ORDER BY status ASC, mtime DESC -`); - -app.get('/games', must_be_logged_in, function (req, res) { - LOG(req, "GET /join"); - let games = QUERY_LIST_GAMES.all({user_id: req.user.user_id}); - humanize(games); - let open_games = games.filter(game => game.status === 0); - let active_games = games.filter(game => game.status === 1); - res.set("Cache-Control", "no-store"); - res.render('games.ejs', { user: req.user, - open_games: open_games, - active_games: active_games, - message: req.flash('message') - }); -}); - -app.get('/user/:who_id', function (req, res) { - LOG(req, "GET /user/" + req.params.who_id); - let who = sql_fetch_user_by_name.get(req.params.who_id, req.params.who_id); - if (who) { - who.avatar = get_avatar(who.mail); - who.ctime = human_date(who.ctime); - who.atime = human_date(who.atime); - res.render('user.ejs', { user: req.user, who: who, message: req.flash('message') }); - } else { - req.flash('message', "Cannot find that user."); - return res.redirect('/'); - } -}); - -// FORUM - -const FORUM_PAGE_SIZE = 15; -const FORUM_COUNT_THREADS = db.prepare("SELECT COUNT(*) FROM threads").pluck(); -const FORUM_LIST_THREADS = db.prepare("SELECT * FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?"); -const FORUM_GET_THREAD = db.prepare("SELECT * FROM thread_view WHERE thread_id = ?"); -const FORUM_LIST_POSTS = db.prepare("SELECT * FROM post_view WHERE thread_id = ?"); -const FORUM_GET_POST = db.prepare("SELECT * FROM post_view WHERE post_id = ?"); -const FORUM_NEW_THREAD = db.prepare("INSERT INTO threads ( author_id, subject ) VALUES ( ?, ? )"); -const FORUM_NEW_POST = db.prepare("INSERT INTO posts ( thread_id, author_id, body ) VALUES ( ?, ?, ? )"); -const FORUM_EDIT_POST = db.prepare("UPDATE posts SET body = ?, mtime = datetime('now') WHERE post_id = ? AND author_id = ? RETURNING thread_id").pluck(); - -function show_forum_page(req, res, page) { - let thread_count = FORUM_COUNT_THREADS.get(); - let page_count = Math.ceil(thread_count / FORUM_PAGE_SIZE); - let threads = FORUM_LIST_THREADS.all(FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1)); - humanize(threads); - res.set("Cache-Control", "no-store"); - res.render('forum_view.ejs', { - user: req.user, - threads: threads, - current_page: page, - page_count: page_count, - message: req.flash('message'), - }); -} - -function linkify(text) { - text = text.replace(/&/g, "&").replace(/</g, "<").replace(/>/g, ">"); - text = text.replace(/https?:\/\/\S+/, (match) => { - if (match.endsWith(".jpg") || match.endsWith(".png") || match.endsWith(".svg")) - return `<a href="${match}"><img src="${match}"></a>`; - return `<a href="${match}">${match}</a>`; - }); - return text; -} - -app.get('/forum', function (req, res) { - LOG(req, "GET /forum"); - show_forum_page(req, res, 1); -}); - -app.get('/forum/page/:page', function (req, res) { - LOG(req, "GET /forum/page/" + req.params.page); - show_forum_page(req, res, req.params.page | 0); -}); - -app.get('/forum/thread/:thread_id', function (req, res) { - LOG(req, "GET /forum/thread/" + req.params.thread_id); - let thread_id = req.params.thread_id | 0; - let thread = FORUM_GET_THREAD.get(thread_id); - let posts = FORUM_LIST_POSTS.all(thread_id); - for (let i = 0; i < posts.length; ++i) { - posts[i].body = linkify(posts[i].body); - posts[i].edited = posts[i].mtime !== posts[i].ctime; - humanize_one(posts[i]); - } - res.set("Cache-Control", "no-store"); - res.render('forum_thread.ejs', { - user: req.user, - thread: thread, - posts: posts, - message: req.flash('message'), - }); -}); - -app.get('/forum/post', must_be_logged_in, function (req, res) { - LOG(req, "GET /forum/post"); - res.render('forum_post.ejs', { - user: req.user, - message: req.flash('message'), - }); -}); - -app.post('/forum/post', must_be_logged_in, function (req, res) { - LOG(req, "POST /forum/post"); - let user_id = req.user.user_id; - let subject = req.body.subject.trim(); - let body = req.body.body; - if (subject.length === 0) - subject = "Untitled"; - let thread_id = FORUM_NEW_THREAD.run(user_id, subject).lastInsertRowid; - FORUM_NEW_POST.run(thread_id, user_id, body); - res.redirect('/forum/thread/'+thread_id); -}); - -app.get('/forum/edit/:post_id', must_be_logged_in, function (req, res) { - // TODO: edit subject if editing first post - LOG(req, "GET /forum/edit/" + req.params.post_id); - let post_id = req.params.post_id | 0; - let post = FORUM_GET_POST.get(post_id); - humanize_one(post); - res.render('forum_edit.ejs', { - user: req.user, - post: post, - message: req.flash('message'), - }); -}); - -app.post('/forum/edit/:post_id', must_be_logged_in, function (req, res) { - LOG(req, "POST /forum/edit/" + req.params.post_id); - let user_id = req.user.user_id; - let post_id = req.params.post_id | 0; - let body = req.body.body; - let thread_id = FORUM_EDIT_POST.get(body, post_id, user_id); - res.redirect('/forum/thread/'+thread_id); -}); - -app.get('/forum/reply/:post_id', must_be_logged_in, function (req, res) { - LOG(req, "GET /forum/reply/" + req.params.post_id); - let post_id = req.params.post_id | 0; - let post = FORUM_GET_POST.get(post_id); - let thread = FORUM_GET_THREAD.get(post.thread_id); - post.body = linkify(post.body); - post.edited = post.mtime !== post.ctime; - humanize_one(post); - humanize_one(thread); - res.render('forum_reply.ejs', { - user: req.user, - thread: thread, - post: post, - message: req.flash('message'), - }); -}); - -app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) { - LOG(req, "POST /forum/reply/" + req.params.thread_id); - let thread_id = req.params.thread_id | 0; - let user_id = req.user.user_id; - let body = req.body.body; - FORUM_NEW_POST.run(thread_id, user_id, body); - res.redirect('/forum/thread/'+thread_id); -}); - -// MESSAGES - -const MESSAGE_GET_USER = db.prepare("SELECT user_id, name, mail, notifications FROM users WHERE name = ?"); -const MESSAGE_GET_USER_ID = db.prepare("SELECT user_id FROM users WHERE name = ?").pluck(); -const MESSAGE_GET_USER_NAME = db.prepare("SELECT name FROM users WHERE user_id = ?").pluck(); - -const MESSAGE_LIST_INBOX = db.prepare(` - SELECT message_id, from_name, subject, time, read - FROM message_view - WHERE to_id = ? AND deleted_from_inbox = 0 - ORDER BY time DESC`); - -const MESSAGE_LIST_OUTBOX = db.prepare(` - SELECT message_id, to_name, subject, time, 1 as read - FROM message_view - WHERE from_id = ? AND deleted_from_outbox = 0 - ORDER BY time DESC`); - -const MESSAGE_FETCH = db.prepare("SELECT * FROM message_view WHERE message_id = ? AND ( from_id = ? OR to_id = ? )"); -const MESSAGE_SEND = db.prepare("INSERT INTO messages ( from_id, to_id, subject, body ) VALUES ( ?, ?, ?, ? )"); -const MESSAGE_MARK_READ = db.prepare("UPDATE messages SET read = 1 WHERE message_id = ?"); -const MESSAGE_DELETE_INBOX = db.prepare("UPDATE messages SET deleted_from_inbox = 1 WHERE message_id = ? AND to_id = ?"); -const MESSAGE_DELETE_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox = 1 WHERE message_id = ? AND from_id = ?"); -const MESSAGE_DELETE_ALL_OUTBOX = db.prepare("UPDATE messages SET deleted_from_outbox = 1 WHERE from_id = ?"); - -app.get('/inbox', must_be_logged_in, function (req, res) { - LOG(req, "GET /inbox"); - let messages = MESSAGE_LIST_INBOX.all(req.user.user_id); - for (let i = 0; i < messages.length; ++i) { - messages[i].time = human_date(messages[i].time); - } - res.set("Cache-Control", "no-store"); - res.render('message_inbox.ejs', { - user: req.user, - messages: messages, - message: req.flash('message'), - }); -}); - -app.get('/outbox', must_be_logged_in, function (req, res) { - LOG(req, "GET /outbox"); - let messages = MESSAGE_LIST_OUTBOX.all(req.user.user_id); - for (let i = 0; i < messages.length; ++i) { - messages[i].time = human_date(messages[i].time); - } - res.set("Cache-Control", "no-store"); - res.render('message_outbox.ejs', { - user: req.user, - messages: messages, - message: req.flash('message'), - }); -}); - -app.get('/message/read/:message_id', must_be_logged_in, function (req, res) { - LOG(req, "GET /message/" + req.params.message_id); - let message_id = req.params.message_id | 0; - let mail = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id); - if (!mail) { - req.flash('message', "Cannot find that message."); - return res.redirect('/inbox'); - } - if (mail.to_id === req.user.user_id) - MESSAGE_MARK_READ.run(message_id); - mail.time = human_date(mail.time); - res.render('message_read.ejs', { - user: req.user, - mail: mail, - message: req.flash('message'), - }); -}); - -app.get('/message/send', must_be_logged_in, function (req, res) { - res.render('message_send.ejs', { - user: req.user, - to_id: 0, - to_name: "", - subject: "", - body: "", - message: req.flash('message'), - }); -}); - -app.get('/message/send/:to_id', must_be_logged_in, function (req, res) { - LOG(req, "GET /message/send/" + req.params.to_id); - let to_id = req.params.to_id | 0; - if (to_id === 0) - to_id = MESSAGE_GET_USER_ID.get(req.params.to_id); - let to_name = MESSAGE_GET_USER_NAME.get(to_id); - if (!to_name) - to_name = ""; - res.render('message_send.ejs', { - user: req.user, - to_id: to_id, - to_name: to_name, - subject: "", - body: "", - message: req.flash('message'), + title_role_map: ROLES, title_name_map: TITLES, title_rule_map: RULES }); }); - -app.post('/message/send', must_be_logged_in, function (req, res) { - LOG(req, "POST /message/send/" + req.params.to_id); - let to_name = req.body.to; - let subject = req.body.subject; - let body = req.body.body; - let to_user = MESSAGE_GET_USER.get(to_name); - if (!to_user) { - return res.render('message_send.ejs', { - user: req.user, - to_id: 0, - to_name: to_name, - subject: subject, - body: body, - message: "Cannot find that user." - }); - } - let info = MESSAGE_SEND.run(req.user.user_id, to_user.user_id, subject, body); - if (to_user.notifications) { - mail_new_message(to_user, info.lastInsertRowid, req.user.name, subject, body) - } - res.redirect('/inbox'); -}); - -function quote_body(text) { - return "> " + text.split("\n").join("\n> ") + "\n\n"; -} - -app.get('/message/reply/:message_id', must_be_logged_in, function (req, res) { - LOG(req, "POST /message/reply/" + req.params.message_id); - let message_id = req.params.message_id | 0; - let mail = MESSAGE_FETCH.get(message_id, req.user.user_id, req.user.user_id); - if (!mail) { - req.flash('message', "Cannot find that message."); - return res.redirect('/inbox'); - } - return res.render('message_send.ejs', { - user: req.user, - to_id: mail.from_id, - to_name: mail.from_name, - subject: mail.subject.startsWith("Re: ") ? mail.subject : "Re: " + mail.subject, - body: quote_body(mail.body), - message: req.flash('message') - }); -}); - -app.get('/message/delete/:message_id', must_be_logged_in, function (req, res) { - LOG(req, "POST /message/delete/" + req.params.message_id); - let message_id = req.params.message_id | 0; - MESSAGE_DELETE_INBOX.run(message_id, req.user.user_id); - MESSAGE_DELETE_OUTBOX.run(message_id, req.user.user_id); - res.redirect('/inbox'); -}); - -app.get('/outbox/delete', must_be_logged_in, function (req, res) { - LOG(req, "POST /outbox/delete"); - MESSAGE_DELETE_ALL_OUTBOX.run(req.user.user_id); - res.redirect('/outbox'); -}); |