Forum search using FTS5.

6 files changed, 112 insertions, 3 deletions

diff --git a/public/style.css b/public/style.css
index e530ef3..aeb9584 100644
--- a/public/style.css
+++ b/public/style.css
@@ -51,6 +51,7 @@ input[type="text"], input[type="password"], input[type="email"], textarea {
 	padding: 5px;
 	margin: 5px 0;
 	border: 1px solid black;
+	vertical-align: middle;
 }
 input:focus, textarea:focus {
 	outline: 2px solid lightsteelblue;
@@ -59,7 +60,7 @@ button, select {
 	margin: 5px 10px 5px 0;
 	padding: 1px 10px;
 	background-color: gainsboro;
-	vertical-align: top;
+	vertical-align: middle;
 	border: 2px solid;
 	border-color: white darkgray darkgray white;
 	outline: 1px solid black;
diff --git a/schema.sql b/schema.sql
index b0f5570..b0d7b39 100644
--- a/schema.sql
+++ b/schema.sql
@@ -259,6 +259,13 @@ create view post_view as
 
 create index if not exists posts_thread_idx on posts(thread_id);
 
+-- Forum Search (FTS5) --
+
+drop table if exists forum_search;
+create virtual table forum_search using fts5(thread_id, post_id, text, tokenize='porter unicode61');
+insert into forum_search(thread_id,post_id,text) select thread_id, 0, subject from threads;
+insert into forum_search(thread_id,post_id,text) select thread_id, post_id, body from posts;
+
 -- Games --
 
 create table if not exists games (
@@ -489,3 +496,43 @@ drop trigger if exists trigger_log_deleted_users;
 create trigger trigger_log_deleted_users before delete on users begin
 	insert into deleted_users (user_id, name, mail) values (old.user_id, old.name, old.mail);
 end;
+
+-- Triggers to keep FTS search index up to date
+
+drop trigger if exists trigger_search_insert_thread;
+create trigger trigger_search_insert_thread after insert on threads
+begin
+	insert into forum_search(thread_id, post_id, text) values(new.thread_id, 0, new.subject);
+end;
+
+drop trigger if exists trigger_search_update_thread;
+create trigger trigger_search_update_thread after update on threads
+begin
+	delete from forum_search where thread_id=old.thread_id and post_id=0;
+	insert into forum_search(thread_id, post_id, text) values(new.thread_id, 0, new.subject);
+end;
+
+drop trigger if exists trigger_search_delete_thread;
+create trigger trigger_search_delete_thread after delete on threads
+begin
+	delete from forum_search where thread_id=old.thread_id and post_id=0;
+end;
+
+drop trigger if exists trigger_search_insert_post;
+create trigger trigger_search_insert_post after insert on posts
+begin
+	insert into forum_search(thread_id, post_id, text) values(new.thread_id, new.post_id, new.body);
+end;
+
+drop trigger if exists trigger_search_update_post;
+create trigger trigger_search_update_post after update on posts
+begin
+	delete from forum_search where post_id=old.post_id;
+	insert into forum_search(thread_id, post_id, text) values(new.thread_id, new.post_id, new.body);
+end;
+
+drop trigger if exists trigger_search_delete_post;
+create trigger trigger_search_delete_post after delete on posts
+begin
+	delete from forum_search where post_id=old.post_id;
+end;
diff --git a/server.js b/server.js
index 1c3e422..962d277 100644
--- a/server.js
+++ b/server.js
@@ -883,6 +883,26 @@ const FORUM_DELETE_THREAD_POSTS = SQL("delete from posts where thread_id=?")
 const FORUM_DELETE_THREAD = SQL("delete from threads where thread_id=?")
 const FORUM_DELETE_POST = SQL("delete from posts where post_id=?")
 
+const FORUM_SEARCH = SQL(`
+	select
+		forum_search.thread_id,
+		forum_search.post_id,
+		threads.subject,
+		coalesce(pusers.name, tusers.name) as author,
+		snippet(forum_search, -1, '', '', '...', 18) as snippet
+	from
+		forum_search
+		join threads on threads.thread_id = forum_search.thread_id
+		left join posts on posts.post_id = forum_search.post_id
+		left join users as pusers on pusers.user_id = posts.author_id
+		left join users as tusers on tusers.user_id = threads.author_id
+	where
+		forum_search match ?
+	order by
+		forum_search.thread_id desc,
+		forum_search.post_id desc
+`)
+
 function show_forum_page(req, res, page) {
 	let thread_count = FORUM_COUNT_THREADS.get()
 	let page_count = Math.ceil(thread_count / FORUM_PAGE_SIZE)
@@ -1019,6 +1039,14 @@ app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) {
 	res.redirect('/forum/thread/'+thread_id)
 })
 
+app.get('/forum/search', must_be_logged_in, function (req, res) {
+	let search = req.query.q
+	let results = []
+	if (search)
+		results = FORUM_SEARCH.all(search)
+	res.render('forum_search.pug', { user: req.user, search, results })
+})
+
 /*
  * GAME LOBBY
  */
diff --git a/views/forum_search.pug b/views/forum_search.pug
new file mode 100644
index 0000000..1301b76
--- /dev/null
+++ b/views/forum_search.pug
@@ -0,0 +1,28 @@
+//- vim:ts=4:sw=4:
+doctype html
+html
+	head
+		include head
+		title Forum Search Results
+	body
+		include header
+		article
+			h1 Forum Search Results
+
+			table
+				thead
+					tr
+						th Author
+						th Thread
+						th Snippet
+				tobdy
+					each row in results
+						tr
+							td: a(href="/user/"+row.author)= row.author
+							td: a(href="/forum/thread/"+row.thread_id+"#"+row.post_id)= row.subject
+							td= row.snippet
+
+			if user
+				p
+					form(method="get" action="/forum/search")
+						input(type="text" name="q" size=40 maxlength=80 placeholder="Search..." required)
diff --git a/views/forum_view.pug b/views/forum_view.pug
index 46c1b1e..33be0ad 100644
--- a/views/forum_view.pug
+++ b/views/forum_view.pug
@@ -43,4 +43,9 @@ html
 								| #[a(href="/forum/page/"+(current_page+1)) →]
 
 			if user
-				p: a(href="/forum/post") New thread
+				p
+					form(method="get" action="/forum/search")
+						input(type="text" name="q" size=40 maxlength=80 placeholder="Search..." required)
+			if user
+				p
+					a(href="/forum/post") New thread
diff --git a/views/head.pug b/views/head.pug
index 3d2285a..e90ce6b 100644
--- a/views/head.pug
+++ b/views/head.pug
@@ -19,7 +19,7 @@ mixin gamecover(title_id)
 		img(src=`/${title_id}/cover.1x.jpg` srcset=`/${title_id}/cover.2x.jpg 2x`)
 
 mixin forumpost(row,show_buttons)
-	.post
+	.post(id=row.post_id)
 		.head
 			.from: a(href="/user/"+row.author_name)= row.author_name
 			.time= row.ctime