diff options
| -rw-r--r-- | server.js | 156 | ||||
| -rw-r--r-- | tools/sql/schema.txt | 30 | ||||
| -rw-r--r-- | views/header.ejs | 6 | ||||
| -rw-r--r-- | views/message_inbox.ejs | 23 | ||||
| -rw-r--r-- | views/message_outbox.ejs | 23 | ||||
| -rw-r--r-- | views/message_read.ejs | 27 | ||||
| -rw-r--r-- | views/message_send.ejs | 30 | ||||
| -rw-r--r-- | views/users.ejs | 2 |
8 files changed, 295 insertions, 2 deletions
@@ -191,6 +191,8 @@ const sql_login_select = db.prepare("SELECT user_id, name, mail, password, salt 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(); + passport.serializeUser(function (user, done) { return done(null, user.user_id); }); @@ -200,6 +202,7 @@ passport.deserializeUser(function (user_id, done) { let row = sql_deserialize_user.get(user_id); if (!row) return done(null, false); + row.unread = () => sql_count_unread_messages.get(user_id); return done(null, row); } catch (err) { console.log(err); @@ -1442,7 +1445,7 @@ app.get('/stats', function (req, res) { app.get('/users', function (req, res) { LOG(req, "GET /users"); - let rows = db.prepare("SELECT name, mail, ctime, atime FROM users ORDER BY atime DESC").all(); + 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); @@ -1616,3 +1619,154 @@ app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) { 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 = ?"); + +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'), + }); +}); + +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." + }); + } + MESSAGE_SEND.run(req.user.user_id, to_user.user_id, subject, body); + if (to_user.notifications) { + console.log("MAIL USER NOTIFICATION"); + } + 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'); +}); diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt index 8c3e72f..cd1a7ee 100644 --- a/tools/sql/schema.txt +++ b/tools/sql/schema.txt @@ -103,6 +103,18 @@ CREATE TABLE IF NOT EXISTS posts ( body TEXT ); +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 TRIGGER IF EXISTS update_reply_count; CREATE TRIGGER update_reply_count AFTER INSERT ON posts BEGIN @@ -239,3 +251,21 @@ CREATE VIEW post_view AS 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/header.ejs b/views/header.ejs index 9c8ee0d..aed34d0 100644 --- a/views/header.ejs +++ b/views/header.ejs @@ -16,6 +16,12 @@ <span><a href="/forum">Forum</a></span> <% if (user) { + let unread = user.unread(); + if (unread > 0) { + %><span><a href="/inbox">Inbox (<%= unread %>)</a></span><% + } else { + %><span><a href="/inbox">Inbox</a></span><% + } %><span><a href="/profile">Profile (<%= user.name %>)</a></span><% } else { %><span><a href="/signup">Signup</a></span><% diff --git a/views/message_inbox.ejs b/views/message_inbox.ejs new file mode 100644 index 0000000..91490ac --- /dev/null +++ b/views/message_inbox.ejs @@ -0,0 +1,23 @@ +<%- include('header', { title: "Inbox" }) %> +<style> +table { width: 100%; max-width: 50em; } +tr.unread { background-color: lemonchiffon; } +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> +<br> +» <a href="/outbox">Outbox</a> +<table> +<tr><th>From<th>Subject<th>Date +<% if (messages.length > 0) { messages.forEach((row) => { %> +<tr class="<%- row.read ? "read" : "unread" %>"> +<td class="nowrap from"><%= row.from_name %> +<td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a> +<td class="nowrap time"><%= row.time %> +<% }); } else { %> +<tr><td colspan="3">No messages</td> +<% } %> +</table> diff --git a/views/message_outbox.ejs b/views/message_outbox.ejs new file mode 100644 index 0000000..c460bd3 --- /dev/null +++ b/views/message_outbox.ejs @@ -0,0 +1,23 @@ +<%- include('header', { title: "Outbox" }) %> +<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> +<p> +» <a href="/message/send">Send message</a> +<br> +» <a href="/inbox">Inbox</a> +<table> +<tr><th>To<th>Subject<th>Date +<% if (messages.length > 0) { messages.forEach((row) => { %> +<tr class="<%- row.read ? "read" : "unread" %>"> +<td class="nowrap to"><%= row.to_name %> +<td class="subject"><a href="/message/read/<%- row.message_id %>"><%= row.subject %></a> +<td class="nowrap time"><%= row.time %> +<% }); } else { %> +<tr><td colspan="3">No messages</td> +<% } %> +</table> diff --git a/views/message_read.ejs b/views/message_read.ejs new file mode 100644 index 0000000..f4a791e --- /dev/null +++ b/views/message_read.ejs @@ -0,0 +1,27 @@ +<%- 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; } +</style> +<script> +function delete_message(id) { + let warning = "Are you sure you want to DELETE this message?"; + if (window.confirm(warning)) + window.location.href = "/message/delete/" + id; +} +function reply_message(id) { + window.location.href = "/message/reply/" + id; +} +</script> +<table> +<tr><th>From:<td> <%= mail.from_name %> +<tr><th>To:<td> <%= mail.to_name %> +<tr><th>Date:<td> <%= mail.time %> +<tr><td colspan="2" class="body"><%= mail.body %></td> +</table> +<p> +<% if ( mail.from_id !== user.user_id ) { %> +<button onclick="reply_message(<%- mail.message_id %>)">Reply</button> +<% } %> +<button onclick="delete_message(<%- mail.message_id %>)">Delete</button> diff --git a/views/message_send.ejs b/views/message_send.ejs new file mode 100644 index 0000000..6f72d74 --- /dev/null +++ b/views/message_send.ejs @@ -0,0 +1,30 @@ +<%- include('header', { title: "Send Message" }) %> +<style> +input, textarea { width: 100%; max-width: 45em; } +</style> +<form action="/message/send" method="post"> +<p> +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" : "" %> + required> + +<p> +Subject: +<br> +<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" : "" %> + pattern=".*\S+.*" + required> + +<p> +Body: +<br> +<textarea id="body" name="body" rows="20" cols="80" maxlength="32000" required><%= body %></textarea> +<p> +<button type="submit">Send</button> +</form> diff --git a/views/users.ejs b/views/users.ejs index a8f5c7d..526b722 100644 --- a/views/users.ejs +++ b/views/users.ejs @@ -10,7 +10,7 @@ td.avatar img{display:block;width:80px;height:80px;} <% userList.forEach((row) => { %> <tr> <td class="avatar"><img src="<%= row.avatar %>"> -<td><%= row.name %> +<td><a href="/user/<%= row.user_id %>"><%= row.name %></a> <td><%= row.ctime %> <td><%= row.atime %> <% }); %> |