From eb12dd7700dfceff88d8d68acc720d86cdf90e05 Mon Sep 17 00:00:00 2001 From: Tor Andersson 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, ">"); + text = text.replace(/https?:\/\/\S+/, (match) => { + if (match.endsWith(".jpg") || match.endsWith(".png") || match.endsWith(".svg")) + return ``; + return `${match}`; + }); + 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" }) %> + +
+

+ +

+ +

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" }) %> + +
+

+Subject: +
+ +

+Body: +
+ +

+ +

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 }) %> + + + + + +
<%= post.author_name %> +<%= post.ctime %> +<%= post.edited ? "(edited " + post.mtime + ")" : "" %> +
<%- post.body %>
+
+

+Reply: +
+ +

+ +

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 }) %> + +<% posts.forEach((row) => { %> +

+ + + + +<% if (user) { %> + +
<%= row.author_name %> +<%= row.ctime %> +<%= row.edited ? "(edited " + row.mtime + ")" : "" %> +
<%- row.body %>
+<% if (row.author_id === user.user_id) { %> +Edit +<% } %> +Reply +<% } %> +
+<% }); %> +<% +if (user) { + %>

Reply<% +} +%> 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 }) %> + + + + +<% threads.forEach((row) => { %> + + + +
SubjectAuthor +RepliesTime +
<%= row.subject %> +<%= row.author_name %> +<%= row.reply_count %> +<%= row.mtime %> +<% }); %> +
+<% +if (current_page > 1) { + %>← <% +} +for (let p = 1; p <= page_count && p <= 30; ++p) { + if (p === current_page) { + %>(<%= p %>) <% + } else { + %><%= p %> <% + } +} +if (current_page < page_count) { + %>→ <% +} +%> +
+<% +if (user) { + %>

New thread<% +} +%> 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 @@

About +Forum <% if (user) { %>Profile (<%= user.name %>)<% -- cgit v1.2.3