diff options
| -rw-r--r-- | migrate.sql | 111 | ||||
| -rw-r--r-- | public/common/client.js | 75 | ||||
| -rw-r--r-- | public/join.js | 20 | ||||
| -rw-r--r-- | public/style.css | 37 | ||||
| -rw-r--r-- | server.js | 1749 | ||||
| -rw-r--r-- | tools/sql/schema.txt | 462 | ||||
| -rw-r--r-- | views/change_about.ejs | 2 | ||||
| -rw-r--r-- | views/forum_reply.ejs | 13 | ||||
| -rw-r--r-- | views/forum_thread.ejs | 15 | ||||
| -rw-r--r-- | views/forum_view.ejs | 25 | ||||
| -rw-r--r-- | views/games.ejs | 37 | ||||
| -rw-r--r-- | views/header.ejs | 6 | ||||
| -rw-r--r-- | views/info.ejs | 47 | ||||
| -rw-r--r-- | views/join.ejs | 3 | ||||
| -rw-r--r-- | views/message_inbox.ejs | 13 | ||||
| -rw-r--r-- | views/message_outbox.ejs | 13 | ||||
| -rw-r--r-- | views/message_read.ejs | 24 | ||||
| -rw-r--r-- | views/message_send.ejs | 11 | ||||
| -rw-r--r-- | views/profile.ejs | 85 | ||||
| -rw-r--r-- | views/stats.ejs | 2 | ||||
| -rw-r--r-- | views/user.ejs | 7 | ||||
| -rw-r--r-- | views/users.ejs | 6 |
22 files changed, 1365 insertions, 1398 deletions
diff --git a/migrate.sql b/migrate.sql new file mode 100644 index 0000000..1d218b3 --- /dev/null +++ b/migrate.sql @@ -0,0 +1,111 @@ +attach database 'old.db' as old; + +pragma foreign_keys = on; + +.read tools/sql/schema.txt + +-- Drop triggers while migrating data: +drop trigger no_join_on_active_game; + +BEGIN; + +.read tools/sql/data-300-ew.txt +.read tools/sql/data-caesar.txt +.read tools/sql/data-crusader.txt +.read tools/sql/data-hammer.txt +.read tools/sql/data-richard.txt +.read tools/sql/data-tripoli.txt + +insert into users + (user_id,name,mail,notify,password,salt,ctime,about) +values + (0, 'Deleted', 'deleted@rally-the-troops.com', 0, '', '', '1970-01-01 00:00:00', 'Deleted user.'); + +-- Users + +insert into users ( + user_id,name,mail,notify,password,salt,ctime,about +) select + user_id,name,mail,notifications,password,salt,ctime,about +from old.users; + +insert into user_last_seen ( + user_id,atime,aip +) select + user_id,atime,aip +from old.users; + +insert into tokens ( + user_id,token,time +) select + user_id,token,time +from old.tokens; + +-- Messages and Forum + +insert into messages ( + message_id,from_id,to_id,time,subject,body,read,deleted_from_inbox,deleted_from_outbox +) select + message_id,from_id,to_id,time,subject,body,read,deleted_from_inbox,deleted_from_outbox +from old.messages; + +insert into threads ( + thread_id,author_id,subject,locked +) select + thread_id,author_id,subject,locked +from old.threads; + +insert into posts ( + post_id,thread_id,author_id,ctime,mtime,body +) select + post_id,thread_id,author_id,ctime,mtime,body +from old.posts; + +-- Games + +insert into games ( + game_id,title_id,scenario,options,owner_id,ctime,private,random,description,status,result +) select + game_id,title_id,scenario,options,owner_id,ctime,private,random,description,status,result +from old.games; + +insert into game_state ( + game_id,mtime,active,state +) select + game_id,mtime,active,state +from old.games; + +insert into game_chat ( + game_id,time,user_id,message +) select + game_id + , datetime(json_extract(value,'$[0]')) AS time + , (select user_id from old.users where name=json_extract(value,'$[1]')) + , json_extract(value,'$[2]') +from old.chats, json_each(chat,'$') +order by time; + +insert into game_replay ( + game_id,time,role,action,arguments +) select + game_id,time,role,action,arguments +from old.replay; + +insert into players ( + user_id,game_id,role +) select + user_id,game_id,role +from old.players; + +-- Foo + +insert into last_notified ( + game_id,user_id,time +) select + game_id,user_id,time +from old.notifications; + +COMMIT; + +-- re-enable triggers +.read tools/sql/schema.txt diff --git a/public/common/client.js b/public/common/client.js index 9ef89e5..c516880 100644 --- a/public/common/client.js +++ b/public/common/client.js @@ -11,7 +11,8 @@ let chat_is_visible = false; let chat_text = null; let chat_key = null; let chat_last_day = null; -let chat_log = null; +let chat_log = 0; +let chat_seen = 0; function scroll_with_middle_mouse(panel_sel, multiplier) { let panel = document.querySelector(panel_sel); @@ -104,7 +105,19 @@ function stop_blinker() { window.addEventListener("focus", stop_blinker); -function add_chat_lines(log) { +function load_chat(game_id) { + chat_key = "chat/" + game_id; + chat_text = document.querySelector(".chat_text"); + chat_last_day = null; + chat_log = 0; + chat_seen = window.localStorage.getItem(chat_key) | 0; +} + +function save_chat() { + window.localStorage.setItem(chat_key, chat_log); +} + +function update_chat(chat_id, utc_date, user, message) { function format_time(date) { let mm = date.getMinutes(); let hh = date.getHours(); @@ -124,10 +137,9 @@ function add_chat_lines(log) { chat_text.appendChild(line); chat_text.scrollTop = chat_text.scrollHeight; } - for (let entry of log) { - chat_log.push(entry); - let [date, user, message] = entry; - date = new Date(date); + if (chat_id > chat_log) { + chat_log = chat_id; + let date = new Date(utc_date + "Z"); let day = date.toDateString(); if (day !== chat_last_day) { add_date_line(day); @@ -135,37 +147,14 @@ function add_chat_lines(log) { } add_chat_line(format_time(date), user, message); } -} - -function load_chat(game_id) { - chat_key = "chat/" + game_id; - chat_text = document.querySelector(".chat_text"); - chat_last_day = null; - chat_log = []; - let save = JSON.parse(window.localStorage.getItem(chat_key)); - if (save) { - if (Date.now() < save.expires) - add_chat_lines(save.chat); + if (chat_id > chat_seen) { + let button = document.querySelector(".chat_button"); + start_blinker("NEW MESSAGE"); + if (!chat_is_visible) + button.classList.add("new"); else - window.localStorage.removeItem(chat_key); - } - return chat_log.length; -} - -function save_chat() { - const DAY = 86400000; - let save = { expires: Date.now() + 7 * DAY, chat: chat_log }; - window.localStorage.setItem(chat_key, JSON.stringify(save)); -} - -function update_chat(log_start, log) { - if (log_start === 0) { - chat_last_day = null; - chat_log = []; - while (chat_text.firstChild) - chat_text.removeChild(chat_text.firstChild); + save_chat(); } - add_chat_lines(log); } function init_client(roles) { @@ -209,7 +198,7 @@ function init_client(roles) { socket.on('connect', () => { console.log("CONNECTED"); document.querySelector(".grid_top").classList.remove('disconnected'); - socket.emit('getchat', chat_log.length); // only send new messages when we reconnect! + socket.emit('getchat', chat_log); // only send new messages when we reconnect! }); socket.on('disconnect', () => { @@ -226,7 +215,7 @@ function init_client(roles) { document.querySelector("body").classList.add(player); for (let i = 0; i < roles.length; ++i) { let pr = players.find(p => p.role === roles[i]); - document.querySelector(USER_SEL[i]).textContent = pr ? pr.user_name : "NONE"; + document.querySelector(USER_SEL[i]).textContent = pr ? pr.name : "NONE"; } }); @@ -261,15 +250,9 @@ function init_client(roles) { document.getElementById("prompt").textContent = msg; }); - socket.on('chat', function (log_start, log) { - console.log("CHAT UPDATE", log_start, log.length); - update_chat(log_start, log); - let button = document.querySelector(".chat_button"); - start_blinker("NEW MESSAGE"); - if (!chat_is_visible) - button.classList.add("new"); - else - save_chat(); + socket.on('chat', function (item) { + console.log("CHAT MESSAGE", JSON.stringify(item)); + update_chat(item[0], item[1], item[2], item[3]); }); document.querySelector(".chat_form").addEventListener("submit", e => { diff --git a/public/join.js b/public/join.js index a5a2e3a..ce3d0d0 100644 --- a/public/join.js +++ b/public/join.js @@ -76,8 +76,12 @@ function start_event_source() { } } -function is_your_turn(player, role) { - return (game.active_role === role || game.active_role === "Both" || game.active_role === "All"); +function is_active(player, role) { + return (game.active === role || game.active === "Both" || game.active === "All"); +} + +function is_solo() { + return players.every(p => p.user_id === players[0].user_id); } function update() { @@ -94,19 +98,19 @@ function update() { let element = document.getElementById(role_id); if (player) { if (game.status > 0) { - if (is_your_turn(player, role)) - element.className = "is_your_turn"; + if (is_active(player, role)) + element.className = "is_active"; else element.className = ""; if (player.user_id === user_id) element.innerHTML = `<a href="/play/${game.game_id}/${role}">Play</a>`; else - element.innerHTML = player.user_name; + element.innerHTML = player.name; } else { if ((player.user_id === user_id) || (game.owner_id === user_id)) - element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.user_name}`; + element.innerHTML = `<a class="red" href="javascript:send('/part/${game.game_id}/${role}')">\u274c</a> ${player.name}`; else - element.innerHTML = player.user_name; + element.innerHTML = player.name; } } else { if (game.status === 0) @@ -131,7 +135,7 @@ function update() { if (game.owner_id === user_id) { window.start_button.disabled = !ready; window.start_button.classList = (game.status === 0) ? "" : "hide"; - window.delete_button.classList = (game.status === 0 || game.is_solo) ? "" : "hide"; + window.delete_button.classList = (game.status === 0 || is_solo()) ? "" : "hide"; if (game.status === 0 && ready) start_blinker("READY TO START"); else diff --git a/public/style.css b/public/style.css index 20865a4..f72cdb7 100644 --- a/public/style.css +++ b/public/style.css @@ -24,22 +24,51 @@ h2 { margin-left: -1px; } .main hr { max-width: 50rem; margin-right: auto; margin-left: 0; } .main hr { border: none; border-top: 2px dotted brown; } .main hr + p { font-style: italic; } -table.wide { min-width: 50rem; } +.is_active { background-color: lemonchiffon; } .error { color: brown; font-style: italic; white-space: pre-wrap; } .warning { color: brown; } .warning::before { content: "\26a0"; } -.logo { float: left; margin: 0 20px 5px 0; box-shadow: 2px 2px 4px 0px rgba(0,0,0,0.5); height: 200px; } +img.logo { + float: left; + margin: 0 20px 5px 0; + box-shadow: 2px 2px 4px 0px rgba(0,0,0,0.5); + height: 200px; +} +img.avatar { + float: left; + margin: 0 20px 5px 0; + box-shadow: 2px 2px 4px 0px rgba(0,0,0,.5); + width: 80px; height: 80px; +} + table { border-collapse: collapse; } +tfoot td { background-color: gainsboro; } th { text-align: left; background-color: gainsboro; } th, td { border: 1px solid black; } th, td { padding: 3px 1ex; } + +table.game { min-width: min(50rem,100%); } +table.game .title { white-space: nowrap; } +table.game .scenario { white-space: nowrap; } +table.game .role { white-space: nowrap; } +table.game .time { white-space: nowrap; } +table.game td a { text-decoration: none; color: black; } +table.game td.command a { text-decoration: underline; color: blue; } + +table.post { min-width: min(50rem,100%); } +table.post .author { white-space: nowrap; width: 10rem; } +table.post .time { white-space: nowrap; text-align: right; width: 5rem; } +table.post .replies { width: 0; } +table.post .unread { background-color: lightyellow; } +table.post .body { white-space: pre-wrap; padding: 10px 10px; } +table.post th a { text-decoration: none; color: black; } +table.post td:not(.body):not(.edit) a { text-decoration: none; color: black; } + label { user-select: none; } button, input, select { font-size: 1rem; margin: 5px 0; } input[type="text"], input[type="password"], textarea { padding: 5px; } select { padding-right: 20px; } form { display: inline; } -.nowrap { white-space: nowrap; } -.is_your_turn { background-color: lemonchiffon; } button, select { margin: 5px 10px 5px 0; padding: 1px 10px; @@ -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'); -}); diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt index 6344470..5e80ec6 100644 --- a/tools/sql/schema.txt +++ b/tools/sql/schema.txt @@ -1,272 +1,292 @@ +CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID; +CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT NOT NULL PRIMARY KEY ) WITHOUT ROWID; + +CREATE TABLE IF NOT EXISTS titles ( + title_id TEXT NOT NULL PRIMARY KEY, + title_name TEXT NOT NULL, + bgg INTEGER +) WITHOUT ROWID; + +CREATE TABLE IF NOT EXISTS roles ( + title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE, + role TEXT NOT NULL, + UNIQUE ( title_id, role ) +); + +-- Users -- + CREATE TABLE IF NOT EXISTS users ( - user_id INTEGER PRIMARY KEY, - name TEXT UNIQUE COLLATE NOCASE, - mail TEXT UNIQUE COLLATE NOCASE, - about TEXT, - password TEXT, - salt TEXT, - ctime TIMESTAMP, - cip TEXT, - atime TIMESTAMP, - aip TEXT, - notifications INTEGER + user_id INTEGER NOT NULL PRIMARY KEY, + name TEXT NOT NULL UNIQUE COLLATE NOCASE, + mail TEXT NOT NULL UNIQUE COLLATE NOCASE, + notify BOOLEAN NOT NULL DEFAULT 0, + banned BOOLEAN NOT NULL DEFAULT 0, + ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + password TEXT NOT NULL, + salt TEXT NOT NULL, + about TEXT ); -CREATE TABLE IF NOT EXISTS notifications ( - user_id INTEGER, - game_id INTEGER, - time TIMESTAMP, - UNIQUE ( user_id, game_id ) +CREATE TABLE IF NOT EXISTS user_last_seen ( + user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE, + atime TIMESTAMP NOT NULL, + aip TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS tokens ( - user_id INTEGER PRIMARY KEY, - token TEXT, - time TIMESTAMP + user_id INTEGER NOT NULL PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE, + token TEXT NOT NULL, + time TIMESTAMP NOT NULL ); -CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT PRIMARY KEY ); -CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT PRIMARY KEY ); +CREATE TABLE IF NOT EXISTS last_notified ( + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE, + time TIMESTAMP NOT NULL, + PRIMARY KEY ( game_id, user_id ) +) WITHOUT ROWID; -CREATE TABLE IF NOT EXISTS titles ( - title_id TEXT UNIQUE, - title_name TEXT, - bgg INTEGER +DROP VIEW IF EXISTS user_view; +CREATE VIEW user_view AS + SELECT + user_id, name, mail, notify + FROM users + ; + +DROP VIEW IF EXISTS user_login_view; +CREATE VIEW user_login_view AS + SELECT + user_id, name, mail, notify, password, salt + FROM users + ; + +DROP VIEW IF EXISTS user_profile_view; +CREATE VIEW user_profile_view AS + SELECT + user_id, name, mail, notify, ctime, atime, about + FROM users + NATURAL LEFT JOIN user_last_seen + ; + +-- Messages -- + +CREATE TABLE IF NOT EXISTS messages ( + message_id INTEGER NOT NULL PRIMARY KEY, + deleted_from_inbox BOOLEAN NOT NULL DEFAULT 0, + deleted_from_outbox BOOLEAN NOT NULL DEFAULT 0, + from_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + to_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + read BOOLEAN NOT NULL DEFAULT 0, + subject TEXT NOT NULL, + body TEXT NOT NULL ); -CREATE TABLE IF NOT EXISTS roles ( - title_id TEXT, - role TEXT, - UNIQUE ( title_id, role ) +DROP VIEW IF EXISTS message_view; +CREATE VIEW message_view AS + SELECT * + , users_from.name AS from_name + , users_to.name AS to_name + FROM messages + JOIN users AS users_from ON messages.from_id = users_from.user_id + JOIN users AS users_to ON messages.to_id = users_to.user_id + ; + +DROP INDEX IF EXISTS messages_inbox_idx; +CREATE INDEX messages_inbox_idx ON messages(to_id) WHERE deleted_from_inbox=0; + +DROP INDEX IF EXISTS messages_inbox_unread_idx; +CREATE INDEX messages_inbox_unread_idx ON messages(to_id) WHERE read=0 AND deleted_from_inbox=0; + +-- Forum -- + +CREATE TABLE IF NOT EXISTS threads ( + thread_id INTEGER NOT NULL PRIMARY KEY, + author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + subject TEXT NOT NULL, + locked BOOLEAN NOT NULL DEFAULT 0 ); +CREATE TABLE IF NOT EXISTS posts ( + post_id INTEGER NOT NULL PRIMARY KEY, + thread_id INTEGER NOT NULL REFERENCES threads(thread_id) ON DELETE CASCADE, + author_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + mtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + body TEXT NOT NULL +); + +DROP VIEW IF EXISTS thread_view; +CREATE VIEW thread_view AS + SELECT * + , author.name AS author_name + , ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = threads.thread_id ) - 1 AS replies + , ( SELECT MAX(posts.mtime) FROM posts WHERE posts.thread_id = threads.thread_id ) AS mtime + FROM threads + JOIN users AS author ON threads.author_id = author.user_id + ; + +DROP VIEW IF EXISTS post_view; +CREATE VIEW post_view AS + SELECT * + , author.name AS author_name + FROM posts + JOIN users AS author ON posts.author_id = author.user_id + ; + +DROP INDEX IF EXISTS posts_thread_idx; +CREATE INDEX posts_thread_idx ON posts(thread_id); + +-- Games -- + CREATE TABLE IF NOT EXISTS games ( - game_id INTEGER PRIMARY KEY, - title_id TEXT, + game_id INTEGER NOT NULL PRIMARY KEY, + title_id TEXT NOT NULL REFERENCES titles(title_id) ON DELETE CASCADE, scenario TEXT, options TEXT, - owner_id INTEGER, - private BOOLEAN, - random BOOLEAN, - description TEXT, - ctime TIMESTAMP, - mtime TIMESTAMP, - status INTEGER, - result TEXT, + owner_id INTEGER DEFAULT 0 REFERENCES users(user_id) ON DELETE SET NULL, + ctime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + private BOOLEAN NOT NULL DEFAULT 0, + random BOOLEAN NOT NULL DEFAULT 0, + description TEXT NOT NULL DEFAULT '', + status INTEGER NOT NULL DEFAULT 0, + result TEXT +); + +DROP INDEX IF EXISTS games_title_idx; +CREATE INDEX games_title_idx ON games(title_id); + +DROP INDEX IF EXISTS games_status_idx; +CREATE INDEX games_status_idx ON games(status); + +CREATE TABLE IF NOT EXISTS game_state ( + game_id INTEGER NOT NULL PRIMARY KEY REFERENCES games(game_id) ON DELETE CASCADE, + mtime TIMESTAMP NOT NULL, active TEXT, state TEXT ); -CREATE TABLE IF NOT EXISTS replay ( - game_id INTEGER, - time TIMESTAMP, - role TEXT, - action TEXT, - arguments TEXT +CREATE TABLE IF NOT EXISTS game_chat ( + chat_id INTEGER NOT NULL PRIMARY KEY, + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + message TEXT ); -CREATE TABLE IF NOT EXISTS chats ( - game_id INTEGER PRIMARY KEY, - time TIMESTAMP, - chat TEXT -); +DROP VIEW IF EXISTS game_chat_view; +CREATE VIEW game_chat_view AS + SELECT chat_id, game_id, time, name, message + FROM game_chat + NATURAL JOIN users + ; -CREATE TABLE IF NOT EXISTS players ( - user_id INTEGER, - game_id INTEGER, +DROP INDEX IF EXISTS game_chat_idx; +CREATE INDEX game_chat_idx ON game_chat(game_id); + +CREATE TABLE IF NOT EXISTS game_replay ( + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, role TEXT, - UNIQUE ( game_id, role ) + action TEXT, + arguments TEXT ); -CREATE TABLE IF NOT EXISTS forums ( - forum_id INTEGER PRIMARY KEY, - title TEXT -); +CREATE TABLE IF NOT EXISTS players ( + game_id INTEGER NOT NULL REFERENCES games(game_id) ON DELETE CASCADE, + role TEXT NOT NULL, + user_id INTEGER NOT NULL DEFAULT 0 REFERENCES users(user_id) ON DELETE SET DEFAULT, + PRIMARY KEY ( game_id, role ) +) WITHOUT ROWID; -CREATE TABLE IF NOT EXISTS threads ( - thread_id INTEGER PRIMARY KEY, - forum_id INTEGER DEFAULT 1, - author_id INTEGER, - subject TEXT, - ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - post_count INTEGER DEFAULT 0, - locked BOOLEAN DEFAULT 0 - -- or first_post_id and last_post_id ? -); +DROP INDEX IF EXISTS player_user_idx; +CREATE INDEX player_user_idx ON players(user_id); -CREATE TABLE IF NOT EXISTS posts ( - post_id INTEGER PRIMARY KEY, - thread_id INTEGER, - author_id INTEGER, - ctime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - mtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - body TEXT -); +DROP INDEX IF EXISTS player_game_user_idx; +CREATE INDEX player_game_user_idx ON players(game_id,user_id); -CREATE TABLE IF NOT EXISTS messages ( - message_id INTEGER PRIMARY KEY, - from_id INTEGER, - to_id INTEGER, - time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - subject TEXT, - body TEXT, - read BOOLEAN DEFAULT 0, - deleted_from_inbox BOOLEAN DEFAULT 0, - deleted_from_outbox BOOLEAN DEFAULT 0 -); +DROP VIEW IF EXISTS game_view; +CREATE VIEW game_view AS + SELECT games.* + , titles.title_name + , owner.name AS owner_name + , game_state.mtime + , game_state.active + FROM games + NATURAL LEFT JOIN game_state + NATURAL JOIN titles + JOIN users AS owner ON owner.user_id=games.owner_id + ; -DROP TRIGGER IF EXISTS update_reply_count; -CREATE TRIGGER update_reply_count AFTER INSERT ON posts -BEGIN - UPDATE threads - SET - post_count = ( SELECT COUNT(*) FROM posts WHERE posts.thread_id = new.thread_id ), - mtime = datetime('now') - WHERE threads.thread_id = new.thread_id +DROP VIEW IF EXISTS game_full_view; +CREATE VIEW game_full_view AS + SELECT *, + ( SELECT group_concat(name, ', ') + FROM players + NATURAL JOIN users + WHERE players.game_id=game_view.game_id + ) AS player_names, + ( SELECT COUNT(DISTINCT user_id) = 1 + FROM players + WHERE players.game_id=game_view.game_id + ) AS is_solo + FROM game_view ; -END; -DROP TRIGGER IF EXISTS update_reply_time; -CREATE TRIGGER update_reply_time AFTER UPDATE ON posts -BEGIN - UPDATE threads SET mtime = datetime('now') WHERE threads.thread_id = new.thread_id ; -END; +DROP VIEW IF EXISTS your_turn_reminder; +CREATE VIEW your_turn_reminder AS + SELECT + game_id, role, user_id, name, mail, notify + FROM game_full_view + JOIN players USING(game_id) + JOIN users USING(user_id) + WHERE status = 1 AND is_solo = 0 AND notify = 1 + AND active IN ( 'All', 'Both', role ) + AND datetime('now') > datetime(mtime, '+1 hour') + ; DROP TRIGGER IF EXISTS no_part_on_active_game; CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players BEGIN - SELECT CASE - WHEN ( SELECT status FROM games WHERE game_id = old.game_id ) > 0 - THEN RAISE(ABORT, "Cannot remove players from started games.") - END; + SELECT RAISE(ABORT, "Cannot remove players from started games.") + WHERE ( SELECT status FROM games WHERE games.game_id = old.game_id ) > 0 ; END; DROP TRIGGER IF EXISTS no_join_on_active_game; CREATE TRIGGER no_join_on_active_game BEFORE INSERT ON players BEGIN - SELECT CASE - WHEN ( SELECT status FROM games WHERE game_id = new.game_id ) > 0 - THEN RAISE(ABORT, "Cannot add players to started games.") - END; + SELECT RAISE(ABORT, "Cannot add players to started games.") + WHERE ( SELECT status FROM games WHERE games.game_id = new.game_id ) > 0 ; END; DROP TRIGGER IF EXISTS must_be_valid_role; CREATE TRIGGER must_be_valid_role BEFORE INSERT ON players BEGIN - SELECT CASE - WHEN ( SELECT COUNT(*) FROM roles, games WHERE - roles.title_id = games.title_id AND - games.game_id = new.game_id AND - roles.role = new.role ) <> 1 - AND new.role <> 'Random 1' - AND new.role <> 'Random 2' - AND new.role <> 'Random 3' - AND new.role <> 'Random 4' - AND new.role <> 'Random 5' - AND new.role <> 'Random 6' - THEN RAISE(ABORT, "Invalid role for that title.") - END; + SELECT + RAISE(ABORT, "Invalid role.") + WHERE + NOT EXISTS ( + SELECT 1 + FROM roles + WHERE roles.title_id = ( SELECT title_id FROM games WHERE games.game_id = new.game_id ) + AND roles.role = new.role + ) + AND new.role <> 'Random 1' + AND new.role <> 'Random 2' + AND new.role <> 'Random 3' + AND new.role <> 'Random 4' + AND new.role <> 'Random 5' + AND new.role <> 'Random 6' + ; END; -DROP TRIGGER IF EXISTS purge_players; -CREATE TRIGGER purge_players AFTER DELETE ON games +-- Manual key management if pragma foreign_keys = OFF +DROP TRIGGER IF EXISTS trigger_delete_on_games; +CREATE TRIGGER trigger_delete_on_games AFTER DELETE ON games BEGIN + DELETE FROM game_state WHERE game_id = old.game_id; + DELETE FROM game_chat WHERE game_id = old.game_id; + DELETE FROM game_replay WHERE game_id = old.game_id; + DELETE FROM last_notified WHERE game_id = old.game_id; DELETE FROM players WHERE game_id = old.game_id; - DELETE FROM notifications WHERE game_id = old.game_id; - DELETE FROM chats WHERE game_id = old.game_id; - DELETE FROM replay WHERE game_id = old.game_id; END; - -DROP VIEW IF EXISTS player_view; -DROP VIEW IF EXISTS player_list_view; -DROP VIEW IF EXISTS game_view; - -CREATE VIEW player_view AS - SELECT players.game_id - , players.role AS role - , players.user_id AS user_id - , users.name AS user_name - FROM players - JOIN users ON players.user_id = users.user_id - ; - -CREATE VIEW player_list_view AS - SELECT game_id - , group_concat(name, ', ') AS player_names - , COUNT(DISTINCT user_id) AS user_count - , COUNT(user_id) AS role_count - FROM players - JOIN users USING ( user_id ) - GROUP BY game_id - ; - -CREATE VIEW game_view AS - SELECT games.game_id - , games.title_id - , titles.title_name - , games.scenario - , games.options - , games.owner_id - , owner.name AS owner_name - , players.player_names - , players.user_count = 1 AS is_solo - , players.user_count <> players.role_count AS is_shared - , games.private - , games.random - , games.description - , games.ctime - , games.mtime - , games.status - , games.result - , games.active AS active_role - -- , games.state - FROM games - JOIN titles USING ( title_id ) - JOIN users AS owner ON games.owner_id = owner.user_id - LEFT JOIN player_list_view AS players USING ( game_id ) - ; - -DROP VIEW IF EXISTS thread_view; -CREATE VIEW thread_view AS - SELECT threads.thread_id - , threads.author_id - , author.name AS author_name - , threads.post_count - 1 AS reply_count - , threads.ctime - , threads.mtime - , threads.subject - FROM threads - JOIN users AS author ON threads.author_id = author.user_id - ; - -DROP VIEW IF EXISTS post_view; -CREATE VIEW post_view AS - SELECT posts.post_id - , posts.thread_id - , posts.author_id - , author.name AS author_name - , posts.ctime - , posts.mtime - , posts.body - FROM posts - JOIN users AS author ON posts.author_id = author.user_id - ; - -DROP VIEW IF EXISTS message_view; -CREATE VIEW message_view AS - SELECT messages.message_id - , messages.from_id - , users_from.name AS from_name - , messages.to_id - , users_to.name AS to_name - , messages.time - , messages.subject - , messages.body - , messages.read - , messages.deleted_from_inbox - , messages.deleted_from_outbox - FROM messages - JOIN users AS users_from ON messages.from_id = users_from.user_id - JOIN users AS users_to ON messages.to_id = users_to.user_id - ; diff --git a/views/change_about.ejs b/views/change_about.ejs index 249f9be..c87fc55 100644 --- a/views/change_about.ejs +++ b/views/change_about.ejs @@ -1,4 +1,4 @@ -<%- include('header', { title: "Change Profile" }) %> +<%- include('header', { title: "Change profile text" }) %> <style> textarea { width: 100%; max-width: 45em; } </style> diff --git a/views/forum_reply.ejs b/views/forum_reply.ejs index bf27492..85bafad 100644 --- a/views/forum_reply.ejs +++ b/views/forum_reply.ejs @@ -1,16 +1,13 @@ <%- include('header', { title: thread.subject }) %> <style> input, textarea { width: 100%; max-width: 45em; } -table { width: 100%; max-width: 50em; } -td.body { white-space: pre-wrap; padding: 10px 10px; } -th.author { border-right: none; } -th.time { text-align: right; border-left: none; font-weight: normal; } +table .author { border-right: none; } +table .time { border-left: none; font-weight: normal; } </style> -<table> +<table class="post"> <tr> -<th class="nowrap author"><%= post.author_name %> -<th class="nowrap time"><%= post.ctime %> -<%= post.edited ? "(edited " + post.mtime + ")" : "" %> +<th class="author"><%= post.author_name %> +<th class="time"><%= post.ctime %> <%= post.edited ? "(edited " + post.mtime + ")" : "" %> <tr> <td class="body" colspan="2"><%- post.body %></td> </table> diff --git a/views/forum_thread.ejs b/views/forum_thread.ejs index 0041f20..7232bce 100644 --- a/views/forum_thread.ejs +++ b/views/forum_thread.ejs @@ -1,18 +1,15 @@ <%- include('header', { title: thread.subject }) %> <style> -table { width: 100%; max-width: 50em; } -td.body { white-space: pre-wrap; padding: 10px 10px; } -th a { color: black; text-decoration: none; } -th.author { border-right: none; } -th.time { text-align: right; border-left: none; font-weight: normal; } -td.edit { text-align: right; border: none; } +table .author { border-right: none; } +table .time { border-left: none; font-weight: normal; } +table .edit { text-align: right; border: none; } </style> <% posts.forEach((row) => { %> <p> -<table> +<table class="post"> <tr> -<th class="nowrap author"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a> -<th class="nowrap time"><%= row.ctime %> +<th class="author"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a> +<th class="time"><%= row.ctime %> <%= row.edited ? "(edited " + row.mtime + ")" : "" %> <tr> <td class="body" colspan="2"><%- row.body %></td> diff --git a/views/forum_view.ejs b/views/forum_view.ejs index 5f04f3f..6838803 100644 --- a/views/forum_view.ejs +++ b/views/forum_view.ejs @@ -1,23 +1,18 @@ <%- include('header', { title: "Forum", refresh: 900 }) %> -<style> -table { width: 100%; max-width: 60em; } -td a { color: black; text-decoration: none; } -tfoot td { background-color: gainsboro; } -th.replies { width: 3em; } -th.time { width: 5em; } -th.author { width: 10em; } -</style> -<table> +<table class="post"> <thead> -<tr><th class="subject">Subject<th class="author">Author -<th class="replies">Replies<th class="time">Time +<tr> +<th class="subject">Subject +<th class="author">Author +<th class="replies">Replies +<th class="time">Time </thead> <% threads.forEach((row) => { %> <tr> -<td class="ellipsis"><a href="/forum/thread/<%- row.thread_id %>"><%= row.subject %></a> -<td class="nowrap"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a> -<td><%= row.reply_count %> -<td class="nowrap"><%= row.mtime %> +<td class="subject"><a href="/forum/thread/<%- row.thread_id %>"><%= row.subject %></a> +<td class="author"><a href="/user/<%- row.author_name %>"><%= row.author_name %></a> +<td class="replies"><%= row.replies %> +<td class="time"><%= row.mtime %> <% }); %> <tfoot> <tr> diff --git a/views/games.ejs b/views/games.ejs index b74dbba..acb94b6 100644 --- a/views/games.ejs +++ b/views/games.ejs @@ -1,40 +1,37 @@ <%- include('header', { title: "All Public Games", refresh: (user ? 300 : 0) }) %> -<style> -td.nowrap a { color: black; text-decoration: none; } -</style> <h2>Open</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Created<th> <% if (open_games.length > 0) { %> <% open_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> -<td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> -<td><%= row.scenario %> -<td><%- row.player_names || row.owner_name %> -<td><%= row.description %> -<td class="nowrap"><%= row.ctime %> -<td><a href="/join/<%= row.game_id %>">Join</a> +<td class="id"><%= row.game_id %> +<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> +<td class="scenario"><%= row.scenario %> +<td class="players"><%- row.player_names || row.owner_name %> +<td class="description"><%= row.description %> +<td class="time"><%= row.ctime %> +<td class="command"><a href="/join/<%= row.game_id %>">Join</a> <% }); } else { %> <tr><td colspan="7">No open games. <% } %> </table> <h2>Active</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Title<th>Scenario<th>Players<th>Description<th>Changed<th>Active<th> <% if (active_games.length > 0) { %> <% active_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> -<td class="nowrap"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> -<td><%= row.scenario %> -<td><%- row.player_names %> -<td><%= row.description %> -<td class="nowrap"><%= row.mtime %> -<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %> -<td><a href="/join/<%= row.game_id %>">Enter</a> +<td class="id"><%= row.game_id %> +<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> +<td class="scenario"><%= row.scenario %> +<td class="players"><%- row.player_names %> +<td class="description"><%= row.description %> +<td class="time"><%= row.mtime %> +<td class="role <%= row.is_active ? "is_active" : "" %>"><%= row.active %> +<td class="command"><a href="/join/<%= row.game_id %>">Enter</a> <% }); } else { %> <tr><td colspan="8">No active games. <% } %> diff --git a/views/header.ejs b/views/header.ejs index aed34d0..337290a 100644 --- a/views/header.ejs +++ b/views/header.ejs @@ -16,7 +16,7 @@ <span><a href="/forum">Forum</a></span> <% if (user) { - let unread = user.unread(); + let unread = user.unread | 0; if (unread > 0) { %><span><a href="/inbox">Inbox (<%= unread %>)</a></span><% } else { @@ -33,7 +33,7 @@ <div class="main"> <h1><%= title %></h1> <% - if (typeof message !== 'undefined' && message.length > 0) { - %><p class="error"><%= Array.isArray(message) ? message.join("\n") : message %></p><% + if (typeof flash !== 'undefined' && flash.length > 0) { + %><p class="error"><%= Array.isArray(flash) ? flash.join("\n") : flash %></p><% } %> diff --git a/views/info.ejs b/views/info.ejs index 159e732..f97b04e 100644 --- a/views/info.ejs +++ b/views/info.ejs @@ -1,7 +1,4 @@ <%- include('header', { title: title.title_name, refresh: (user ? 300 : 0) }) %> -<style> -td.names a { color: black; text-decoration: none; } -</style> <img class="logo" src="/<%= title.title_id %>/cover.jpg"> <%- include('../public/' + title.title_id + '/about.html') %> <p> @@ -11,17 +8,17 @@ Read more about the game on <% if (user) { %> <h2>Open Games</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Scenario<th>Players<th>Description<th>Created<th> <% if (open_games.length > 0) { %> <% open_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> +<td class="id"><%= row.game_id %> <td><%= row.scenario %> -<td class="names"><%- row.player_names || `<a href="/user/${row.owner_name}">${row.owner_name}</a>` %> -<td><%= row.description %> -<td class="nowrap"><%= row.ctime %> -<td><a href="/join/<%= row.game_id %>">Join</a> +<td class="players"><%- row.player_names || `<a href="/user/${row.owner_name}">${row.owner_name}</a>` %> +<td class="description"><%= row.description %> +<td class="time"><%= row.ctime %> +<td class="command"><a href="/join/<%= row.game_id %>">Join</a> <% }); } else { %> <tr><td colspan="6">No open games. <% } %> @@ -32,34 +29,34 @@ Read more about the game on <% if (active_games.length > 0) { %> <h2>Active Games</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Scenario<th>Players<th>Description<th>Changed<th>Turn<th> <% active_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> -<td><%= row.scenario %> -<td class="names"><%- row.player_names %> -<td><%= row.description %> -<td class="nowrap"><%= row.mtime %> -<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %> -<td><a href="/join/<%= row.game_id %>">Enter</a> +<td class="id"><%= row.game_id %> +<td class="scenario"><%= row.scenario %> +<td class="players"><%- row.player_names %> +<td class="description"><%= row.description %> +<td class="time"><%= row.mtime %> +<td class="<%= row.is_active ? "role is_active" : "role" %>"><%= row.active %> +<td class="command"><a href="/join/<%= row.game_id %>">Enter</a> <% }); %> </table> <% } %> <% if (finished_games.length > 0) { %> <h2>Finished Games</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Scenario<th>Players<th>Description<th>Finished<th>Result<th> <% finished_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> -<td><%= row.scenario %> -<td class="names"><%- row.player_names %> -<td><%= row.description %> -<td class="nowrap"><%= row.mtime %> -<td><%= row.result %> -<td><a href="/join/<%= row.game_id %>">View</a> +<td class="id"><%= row.game_id %> +<td class="scenario"><%= row.scenario %> +<td class="players"><%- row.player_names %> +<td class="description"><%= row.description %> +<td class="time"><%= row.mtime %> +<td class="result"><%= row.result %> +<td class="command"><a href="/join/<%= row.game_id %>">View</a> <% }); %> </table> <% } %> diff --git a/views/join.ejs b/views/join.ejs index b9cf824..fa33d5c 100644 --- a/views/join.ejs +++ b/views/join.ejs @@ -12,6 +12,7 @@ function format_options(options) { <style> th, td { min-width: 10em; font-size: 16px; } a.red { text-decoration: none; color: brown; font-size: 15px; } +td a { text-decoration: underline; color: blue; } .hide { display: none; } </style> <script> @@ -44,7 +45,7 @@ Result: <span id="game_result"></span> <br clear=left> -<table> +<table class="small"> <tr> <% roles.forEach((role) => { %><th id="role_<%= role.replace(/ /g, '_') %>_name"><%= role %></th><% }); %> <tr> diff --git a/views/message_inbox.ejs b/views/message_inbox.ejs index 16b0c46..e70e156 100644 --- a/views/message_inbox.ejs +++ b/views/message_inbox.ejs @@ -1,19 +1,12 @@ <%- include('header', { title: "Inbox" }) %> -<style> -table { width: 100%; max-width: 50em; } -tr.unread { background-color: lightyellow; } -td.from { width: 5em; } -td.time { text-align: right; width: 5em; } -td a { color:black; text-decoration: none; } -</style> <p><a href="/message/send">Send message</a> -<table> +<table class="post"> <tr><th>From<th>Subject<th>Date <% if (messages.length > 0) { messages.forEach((row) => { %> <tr class="<%- row.read ? "read" : "unread" %>"> -<td class="nowrap from"><a href="/user/<%- row.from_name %>"><%= row.from_name %></a> +<td class="author"><a href="/user/<%- row.from_name %>"><%= row.from_name %></a> <td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a> -<td class="nowrap time"><%= row.time %> +<td class="time"><%= row.time %> <% }); } else { %> <tr><td colspan="3">No messages</td> <% } %> diff --git a/views/message_outbox.ejs b/views/message_outbox.ejs index e5c53e3..c7ff379 100644 --- a/views/message_outbox.ejs +++ b/views/message_outbox.ejs @@ -6,20 +6,13 @@ function delete_all() { window.location.href = "/outbox/delete"; } </script> -<style> -table { width: 100%; max-width: 50em; } -tr.unread { background-color: lemonchiffon; } -td.to { width: 5em; } -td.time { text-align: right; width: 5em; } -td a { color:black; text-decoration: none; } -</style> -<table> +<table class="post"> <tr><th>To<th>Subject<th>Date <% if (messages.length > 0) { messages.forEach((row) => { %> <tr class="<%- row.read ? "read" : "unread" %>"> -<td class="nowrap to"><a href="/user/<%- row.to_name %>"><%= row.to_name %></a> +<td class="author"><a href="/user/<%- row.to_name %>"><%= row.to_name %></a> <td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a> -<td class="nowrap time"><%= row.time %> +<td class="time"><%= row.time %> <% }); } else { %> <tr><td colspan="3">No messages</td> <% } %> diff --git a/views/message_read.ejs b/views/message_read.ejs index 28762cf..7b32eda 100644 --- a/views/message_read.ejs +++ b/views/message_read.ejs @@ -1,10 +1,4 @@ -<%- include('header', { title: mail.subject }) %> -<style> -table { width: 100%; max-width: 50em; } -th { width: 5em; font-weight: normal; } -td.body { white-space: pre-wrap; padding: 10px 10px; } -td a { color: black; text-decoration: none; } -</style> +<%- include('header', { title: message.subject }) %> <script> function delete_message(id) { let warning = "Are you sure you want to DELETE this message?"; @@ -15,14 +9,14 @@ function reply_message(id) { window.location.href = "/message/reply/" + id; } </script> -<table> -<tr><th>From:<td> <a href="/user/<%- mail.from_name %>"><%= mail.from_name %></a> -<tr><th>To:<td> <a href="/user/<%- mail.to_name %>"><%= mail.to_name %></a> -<tr><th>Date:<td> <%= mail.time %> -<tr><td colspan="2" class="body"><%= mail.body %></td> +<table class="post"> +<tr><th>From:<td> <a href="/user/<%- message.from_name %>"><%= message.from_name %></a> +<tr><th>To:<td> <a href="/user/<%- message.to_name %>"><%= message.to_name %></a> +<tr><th>Date:<td> <%= message.time %> +<tr><td colspan="2" class="body"><%- message.body %></td> </table> <p> -<% if ( mail.from_id !== user.user_id ) { %> -<button onclick="reply_message(<%- mail.message_id %>)">Reply</button> +<% if ( 1 || message.from_id !== user.user_id ) { %> +<button onclick="reply_message(<%- message.message_id %>)">Reply</button> <% } %> -<button onclick="delete_message(<%- mail.message_id %>)">Delete</button> +<button onclick="delete_message(<%- message.message_id %>)">Delete</button> diff --git a/views/message_send.ejs b/views/message_send.ejs index 6f72d74..4dafa3a 100644 --- a/views/message_send.ejs +++ b/views/message_send.ejs @@ -1,6 +1,6 @@ <%- include('header', { title: "Send Message" }) %> <style> -input, textarea { width: 100%; max-width: 45em; } +input, textarea { width: 100%; max-width: 45rem; } </style> <form action="/message/send" method="post"> <p> @@ -8,7 +8,7 @@ To:<br> <input id="to" type="text" name="to" size="80" maxlength="80" onkeypress="if(event.keyCode===13){document.querySelector('#subject').focus();return false;}" value="<%= to_name %>" - <%= to_id === 0 ? "autofocus" : "" %> + <%= (to_name === "") ? "autofocus" : "" %> required> <p> @@ -17,14 +17,17 @@ Subject: <input id="subject" type="text" name="subject" size="80" maxlength="80" onkeypress="if(event.keyCode===13){document.querySelector('#body').focus();return false;}" value="<%= subject %>" - <%= to_id > 0 ? "autofocus" : "" %> + <%= (to_name !== "" && subject === "") ? "autofocus" : "" %> pattern=".*\S+.*" required> <p> Body: <br> -<textarea id="body" name="body" rows="20" cols="80" maxlength="32000" required><%= body %></textarea> +<textarea id="body" name="body" rows="20" cols="80" maxlength="32000" + <%= (to_name !== "" && subject !== "") ? "autofocus" : "" %> + required> +<%= body %></textarea> <p> <button type="submit">Send</button> </form> diff --git a/views/profile.ejs b/views/profile.ejs index 3c49014..1dbc1f5 100644 --- a/views/profile.ejs +++ b/views/profile.ejs @@ -1,10 +1,5 @@ <%- include('header', { title: "Rally the Troops!", refresh: (active_games.length > 0 ? 300 : 0) }) %> -<style> -td.game a, td.names a { color: black; text-decoration: none; } -.logo { width: 80px; height: 80px; } -</style> - -<img class="logo" src="<%= avatar %>" width="80" height="80"> +<img class="logo avatar" src="<%= avatar %>" width="80" height="80"> <p> Welcome, <%= user.name %>! <p> @@ -12,60 +7,62 @@ Your mail address is <%= user.mail %>. <br clear=left> -<ul style="list-style:'\bb '"> -<li><% - if (user.notifications) { +<p>» +<% + if (user.notify) { %><a href="/unsubscribe">Disable mail notifications</a><% } else { %><a href="/subscribe">Enable mail notifications</a><% } %> -<li><a href="/change_password">Change password</a> -<li><a href="/change_mail">Change mail address</a> -<li><a href="/change_name">Change name</a> -<li><a href="/change_about">Change profile text</a> -<li><a href="/logout">Logout</a> -</ul> +<br>» +Change +<a href="/change_password">password</a>, +<a href="/change_mail">mail address</a>, +<a href="/change_name">name</a>, +or <a href="/change_about">profile text</a>. +<br>» +<a href="/logout">Logout</a> <% if (open_games.length > 0) { %> <h2>Open Games</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Created<th> <% open_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> -<td class="nowrap game"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> -<td><%= row.scenario %> -<td class="names"><%- row.player_names %> -<td><%= row.description %> -<td class="nowrap"><%= row.ctime %> -<td><a href="/join/<%= row.game_id %>">Join</a> +<td class="id"><%= row.game_id %> +<td class="name"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> +<td class="scenario"><%= row.scenario %> +<td class="players"><%- row.player_names %> +<td class="description"><%= row.description %> +<td class="time"><%= row.ctime %> +<td class="command"><a href="/join/<%= row.game_id %>">Join</a> <% }); %> </table> <% } %> <% if (active_games.length > 0) { %> <h2>Active Games</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Changed<th>Turn<th> <% active_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> -<td class="nowrap game"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> -<td><%= row.scenario %> -<td class="names"><%- row.player_names %> -<td><%= row.description %> -<td class="nowrap"><%= row.mtime %> +<td class="id"><%= row.game_id %> +<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> +<td class="scenario"><%= row.scenario %> +<td class="players"><%- row.player_names %> +<td class="description"><%= row.description %> +<td class="time"><%= row.mtime %> <% - if (row.is_your_turn) { - %><td class="is_your_turn"><%= row.active_role %><% + if (row.is_active) { + %><td class="role is_active"><%= row.active %><% } else { - %><td><%= row.active_role %><% + %><td class="role"><%= row.active %><% } if (row.is_shared) { - %><td><a href="/join/<%= row.game_id %>">Enter</a><% + %><td class="command"><a href="/join/<%= row.game_id %>">Enter</a><% } else { - %><td><a href="/play/<%= row.game_id %>">Play</a><% + %><td class="command"><a href="/play/<%= row.game_id %>">Play</a><% } %> <% }); %> @@ -74,18 +71,18 @@ Your mail address is <%= user.mail %>. <% if (finished_games.length > 0) { %> <h2>Finished Games</h2> -<table class="wide"> +<table class="game"> <tr><th>ID<th>Game<th>Scenario<th>Players<th>Description<th>Finished<th>Result<th> <% finished_games.forEach((row) => { %> <tr> -<td><%= row.game_id %> -<td class="nowrap game"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> -<td><%= row.scenario %> -<td class="names"><%- row.player_names %> -<td><%= row.description %> -<td class="nowrap"><%= row.mtime %> -<td><%= row.result %> -<td><a href="/join/<%= row.game_id %>">View</a> +<td class="id"><%= row.game_id %> +<td class="title"><a href="/info/<%= row.title_id %>"><%= row.title_name %></a> +<td class="scenario"><%= row.scenario %> +<td class="players"><%- row.player_names %> +<td class="description"><%= row.description %> +<td class="time"><%= row.mtime %> +<td class="result"><%= row.result %> +<td class="command"><a href="/join/<%= row.game_id %>">View</a> <% }); %> </table> <% } %> diff --git a/views/stats.ejs b/views/stats.ejs index 64a6303..9e07aaf 100644 --- a/views/stats.ejs +++ b/views/stats.ejs @@ -17,7 +17,7 @@ for (let title_id in title_name_map) { let scenarios = title_rule_map[title_id].scenarios; let roles = title_role_map[title_id].concat(['Draw']); - %><tr><th><%= title_name_map[title_id] %><% + %><tr><th><%= title_name_map[title_id].title_name %><% roles.forEach(role => { %><th><%= role %><% }); diff --git a/views/user.ejs b/views/user.ejs index 9da706c..8fd1d08 100644 --- a/views/user.ejs +++ b/views/user.ejs @@ -1,8 +1,5 @@ <%- include('header', { title: who.name }) %> -<style> -.logo { width: 80px; height: 80px; } -</style> -<img class="logo" src="<%= who.avatar %>" width="80" height="80"> +<img class="avatar" src="<%= who.avatar %>" width="80" height="80"> <% if (who.about) { %> <p style="white-space:pre-wrap;font-style:italic"><%= who.about %></p> <% } else { %> @@ -11,7 +8,7 @@ <p> Member since <%= who.ctime %>. <p> -Was last seen <%= who.atime %>. +Last seen <%= who.atime %>. <% if (user) { %> <p> <a href="/message/send/<%- who.name %>">Send message</a> diff --git a/views/users.ejs b/views/users.ejs index d6e4d4a..9255d04 100644 --- a/views/users.ejs +++ b/views/users.ejs @@ -3,14 +3,12 @@ td.avatar{padding:0;width:80px;} td.avatar img{display:block;width:80px;height:80px;} </style> - -<table class="wide"> +<table class="post"> <tr><th>Avatar<th>Name<th>Member since<th>Last seen - <% userList.forEach((row) => { %> <tr> <td class="avatar"><img src="<%= row.avatar %>"> -<td><a href="/user/<%= row.name %>"><%= row.name %></a> +<td class="name"><a href="/user/<%= row.name %>"><%= row.name %></a> <td><%= row.ctime %> <td><%= row.atime %> <% }); %> |