diff options
| -rw-r--r-- | public/style.css | 3 | ||||
| -rw-r--r-- | schema.sql | 47 | ||||
| -rw-r--r-- | server.js | 28 | ||||
| -rw-r--r-- | views/forum_search.pug | 28 | ||||
| -rw-r--r-- | views/forum_view.pug | 7 | ||||
| -rw-r--r-- | views/head.pug | 2 |
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; @@ -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; @@ -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 |