From eb12dd7700dfceff88d8d68acc720d86cdf90e05 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Tue, 9 Nov 2021 16:35:49 +0100
Subject: Add forum.
---
public/style.css | 4 +-
server.js | 139 +++++++++++++++++++++++++++++++++++++++++++++++++
tools/sql/schema.txt | 69 ++++++++++++++++++++++++
views/forum_edit.ejs | 10 ++++
views/forum_post.ejs | 20 +++++++
views/forum_reply.ejs | 25 +++++++++
views/forum_thread.ejs | 32 ++++++++++++
views/forum_view.ejs | 42 +++++++++++++++
views/header.ejs | 1 +
9 files changed, 340 insertions(+), 2 deletions(-)
create mode 100644 views/forum_edit.ejs
create mode 100644 views/forum_post.ejs
create mode 100644 views/forum_reply.ejs
create mode 100644 views/forum_thread.ejs
create mode 100644 views/forum_view.ejs
diff --git a/public/style.css b/public/style.css
index 44f6185..20865a4 100644
--- a/public/style.css
+++ b/public/style.css
@@ -2,7 +2,7 @@ button, select {
font-family: "Source Sans", "Verdana", "Dingbats", "Noto Emoji", sans-serif;
font-size: 16px;
}
-html, input {
+html, input, textarea {
font-family: "Source Serif", "Georgia", "Dingbats", "Noto Emoji", serif;
font-size: 16px;
}
@@ -35,7 +35,7 @@ th, td { border: 1px solid black; }
th, td { padding: 3px 1ex; }
label { user-select: none; }
button, input, select { font-size: 1rem; margin: 5px 0; }
-input[type="text"], input[type="password"] { padding: 5px; }
+input[type="text"], input[type="password"], textarea { padding: 5px; }
select { padding-right: 20px; }
form { display: inline; }
.nowrap { white-space: nowrap; }
diff --git a/server.js b/server.js
index d9c8db0..7f89252 100644
--- a/server.js
+++ b/server.js
@@ -129,6 +129,11 @@ function humanize(rows) {
}
}
+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-]+)*$/);
}
@@ -1477,3 +1482,137 @@ app.get('/games', must_be_logged_in, function (req, res) {
message: req.flash('message')
});
});
+
+// 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);
+});
diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt
index da77956..8c3e72f 100644
--- a/tools/sql/schema.txt
+++ b/tools/sql/schema.txt
@@ -77,6 +77,49 @@ CREATE TABLE IF NOT EXISTS players (
UNIQUE ( game_id, role )
);
+CREATE TABLE IF NOT EXISTS forums (
+ forum_id INTEGER PRIMARY KEY,
+ title TEXT
+);
+
+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 ?
+);
+
+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 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
+ ;
+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 TRIGGER IF EXISTS no_part_on_active_game;
CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players
BEGIN
@@ -170,3 +213,29 @@ CREATE VIEW game_view AS
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
+ ;
diff --git a/views/forum_edit.ejs b/views/forum_edit.ejs
new file mode 100644
index 0000000..2f3c3ff
--- /dev/null
+++ b/views/forum_edit.ejs
@@ -0,0 +1,10 @@
+<%- include('header', { title: "Edit Post" }) %>
+<style>
+input, textarea { width: 100%; max-width: 45em; }
+</style>
+<form action="/forum/edit/<%- post.post_id %>" method="post">
+<p>
+<textarea name="body" rows="20" cols="80" maxlength="32000" required autofocus><%= post.body %></textarea>
+<p>
+<button type="submit">Submit</button>
+</form>
diff --git a/views/forum_post.ejs b/views/forum_post.ejs
new file mode 100644
index 0000000..aa24455
--- /dev/null
+++ b/views/forum_post.ejs
@@ -0,0 +1,20 @@
+<%- include('header', { title: "New Thread" }) %>
+<style>
+input, textarea { width: 100%; max-width: 45em; }
+</style>
+<form action="/forum/post" method="post">
+<p>
+Subject:
+<br>
+<input type="text" name="subject" size="80" maxlength="80"
+ onkeypress="if(event.keyCode===13){document.querySelector('textarea').focus();return false;}"
+ autofocus
+ pattern=".*\S+.*"
+ required>
+<p>
+Body:
+<br>
+<textarea name="body" rows="20" cols="80" maxlength="32000" required></textarea>
+<p>
+<button type="submit">Submit</button>
+</form>
diff --git a/views/forum_reply.ejs b/views/forum_reply.ejs
new file mode 100644
index 0000000..bf27492
--- /dev/null
+++ b/views/forum_reply.ejs
@@ -0,0 +1,25 @@
+<%- 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; }
+</style>
+<table>
+<tr>
+<th class="nowrap author"><%= post.author_name %>
+<th class="nowrap time"><%= post.ctime %>
+<%= post.edited ? "(edited " + post.mtime + ")" : "" %>
+<tr>
+<td class="body" colspan="2"><%- post.body %></td>
+</table>
+<form action="/forum/reply/<%- thread.thread_id %>" method="post">
+<p>
+Reply:
+<br>
+<textarea name="body" rows="15" cols="80" maxlength="32000" required autofocus
+ placeholder="Say something nice."></textarea>
+<p>
+<button type="submit">Submit</button>
+</form>
diff --git a/views/forum_thread.ejs b/views/forum_thread.ejs
new file mode 100644
index 0000000..fad8829
--- /dev/null
+++ b/views/forum_thread.ejs
@@ -0,0 +1,32 @@
+<%- include('header', { title: thread.subject }) %>
+<style>
+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; }
+td.edit { text-align: right; border: none; }
+</style>
+<% posts.forEach((row) => { %>
+<p>
+<table>
+<tr>
+<th class="nowrap author"><%= row.author_name %>
+<th class="nowrap time"><%= row.ctime %>
+<%= row.edited ? "(edited " + row.mtime + ")" : "" %>
+<tr>
+<td class="body" colspan="2"><%- row.body %></td>
+<% if (user) { %>
+<tr>
+<td class="edit" colspan=2>
+<% if (row.author_id === user.user_id) { %>
+<a href="/forum/edit/<%- row.post_id %>">Edit</a>
+<% } %>
+<a href="/forum/reply/<%- row.post_id %>">Reply</a>
+<% } %>
+</table>
+<% }); %>
+<%
+if (user) {
+ %><p><a href="/forum/reply/<%- posts[0].post_id %>">Reply</a><%
+}
+%>
diff --git a/views/forum_view.ejs b/views/forum_view.ejs
new file mode 100644
index 0000000..7c3de75
--- /dev/null
+++ b/views/forum_view.ejs
@@ -0,0 +1,42 @@
+<%- 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; }
+</style>
+<table>
+<thead>
+<tr><th>Subject<th>Author
+<th>Replies<th>Time
+</thead>
+<% threads.forEach((row) => { %>
+<tr>
+<td class="ellipsis"><a href="/forum/thread/<%- row.thread_id %>"><%= row.subject %></a>
+<td class="nowrap"><%= row.author_name %>
+<td><%= row.reply_count %>
+<td class="nowrap"><%= row.mtime %>
+<% }); %>
+<tfoot>
+<tr>
+<td colspan="4">
+<%
+if (current_page > 1) {
+ %><a href="/forum/page/<%= current_page-1 %>">←</a> <%
+}
+for (let p = 1; p <= page_count && p <= 30; ++p) {
+ if (p === current_page) {
+ %>(<%= p %>) <%
+ } else {
+ %><a href="/forum/page/<%= p %>"><%= p %></a> <%
+ }
+}
+if (current_page < page_count) {
+ %><a href="/forum/page/<%= current_page+1 %>">→</a> <%
+}
+%>
+</table>
+<%
+if (user) {
+ %><p><a href="/forum/post">New thread</a><%
+}
+%>
diff --git a/views/header.ejs b/views/header.ejs
index b430c25..9c8ee0d 100644
--- a/views/header.ejs
+++ b/views/header.ejs
@@ -13,6 +13,7 @@
<div><a href="/"><img src="/images/rally-the-troops.svg" width="48" height="48"></a></div>
<div>
<span><a href="/about">About</a></span>
+<span><a href="/forum">Forum</a></span>
<%
if (user) {
%><span><a href="/profile">Profile (<%= user.name %>)</a></span><%
--
cgit v1.2.3