diff options
| -rw-r--r-- | schema.sql | 22 | ||||
| -rw-r--r-- | server.js | 12 | ||||
| -rw-r--r-- | views/forum_view.pug | 4 | ||||
| -rw-r--r-- | views/message_inbox.pug | 10 |
4 files changed, 40 insertions, 8 deletions
@@ -164,6 +164,12 @@ create table if not exists posts ( body text ); +create table if not exists read_threads ( + user_id integer, + thread_id integer, + primary key (user_id, thread_id) +) without rowid; + drop view if exists thread_view; create view thread_view as select @@ -383,6 +389,7 @@ begin delete from tokens where user_id = old.user_id; delete from user_last_seen where user_id = old.user_id; delete from last_notified where user_id = old.user_id; + delete from read_threads where user_id = old.user_id; delete from contacts where me = old.user_id or you = old.user_id; delete from messages where from_id = old.user_id or to_id = old.user_id; delete from posts where author_id = old.user_id; @@ -396,4 +403,17 @@ drop trigger if exists trigger_delete_on_threads; create trigger trigger_delete_on_threads after delete on threads begin delete from posts where thread_id = old.thread_id; -end + delete from read_threads where thread_id = old.thread_id; +end; + +drop trigger if exists trigger_mark_threads_as_unread1; +create trigger trigger_mark_threads_as_unread1 after insert on posts +begin + delete from read_threads where user_id != new.author_id and thread_id = new.thread_id; +end; + +drop trigger if exists trigger_mark_threads_as_unread2; +create trigger trigger_mark_threads_as_unread2 after update on posts +begin + delete from read_threads where user_id != new.author_id and thread_id = new.thread_id; +end; @@ -886,13 +886,15 @@ app.get('/outbox/delete', must_be_logged_in, function (req, res) { const FORUM_PAGE_SIZE = 15 const FORUM_COUNT_THREADS = SQL("SELECT COUNT(*) FROM threads").pluck() -const FORUM_LIST_THREADS = SQL("SELECT * FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?") +const FORUM_LIST_THREADS_USER = SQL("SELECT *, (exists (select 1 from read_threads where user_id=? and read_threads.thread_id=thread_view.thread_id)) as is_read FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?") +const FORUM_LIST_THREADS = SQL("SELECT *, 1 as is_read FROM thread_view ORDER BY mtime DESC LIMIT ? OFFSET ?") const FORUM_GET_THREAD = SQL("SELECT * FROM thread_view WHERE thread_id=?") const FORUM_LIST_POSTS = SQL("SELECT * FROM post_view WHERE thread_id=?") const FORUM_GET_POST = SQL("SELECT * FROM post_view WHERE post_id=?") const FORUM_NEW_THREAD = SQL("INSERT INTO threads (author_id,subject) VALUES (?,?)") const FORUM_NEW_POST = SQL("INSERT INTO posts (thread_id,author_id,body) VALUES (?,?,?)") const FORUM_EDIT_POST = SQL("UPDATE posts SET body=?, mtime=julianday() WHERE post_id=? AND author_id=? RETURNING thread_id").pluck() +const FORUM_MARK_READ = SQL("insert or ignore into read_threads (user_id,thread_id) values (?,?)") const FORUM_DELETE_THREAD_POSTS = SQL("delete from posts where thread_id=?") const FORUM_DELETE_THREAD = SQL("delete from threads where thread_id=?") @@ -901,7 +903,11 @@ const FORUM_DELETE_POST = SQL("delete from posts where post_id=?") 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)) + let threads + if (req.user) + threads = FORUM_LIST_THREADS_USER.all(req.user.user_id, FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1)) + else + threads = FORUM_LIST_THREADS.all(FORUM_PAGE_SIZE, FORUM_PAGE_SIZE * (page - 1)) for (let thread of threads) thread.mtime = human_date(thread.mtime) res.render('forum_view.pug', { @@ -942,6 +948,8 @@ app.get('/forum/thread/:thread_id', function (req, res) { posts[i].ctime = human_date(posts[i].ctime) posts[i].mtime = human_date(posts[i].mtime) } + if (req.user) + FORUM_MARK_READ.run(req.user.user_id, thread_id) res.render('forum_thread.pug', { user: req.user, thread: thread, diff --git a/views/forum_view.pug b/views/forum_view.pug index 990d3d3..46c1b1e 100644 --- a/views/forum_view.pug +++ b/views/forum_view.pug @@ -6,6 +6,7 @@ html title Forum meta(http-equiv="refresh" content=3600) style. + .unread { font-weight: bold } tbody a { display: block } body include header @@ -21,7 +22,8 @@ html tbody each row in threads tr - td: a(href="/forum/thread/"+row.thread_id)= row.subject + td(class=row.is_read?"read":"unread") + a(href="/forum/thread/"+row.thread_id)= row.subject td.r= row.count td.w= row.mtime tfoot diff --git a/views/message_inbox.pug b/views/message_inbox.pug index 29bd4d3..10c4626 100644 --- a/views/message_inbox.pug +++ b/views/message_inbox.pug @@ -5,7 +5,7 @@ html include head title Inbox style. - .unread { background-color: lightyellow } + .unread { font-weight: bold } td a { display: block } body include header @@ -23,9 +23,11 @@ html th Date tbody each row in messages - tr(class=row.is_read?"read":"unread") - td: a(href="/user/"+row.from_name)= row.from_name - td: a(href="/message/read/"+row.message_id)= row.subject + tr + td + a(href="/user/"+row.from_name)= row.from_name + td(class=row.is_read?"read":"unread") + a(href="/message/read/"+row.message_id)= row.subject td= row.time else tr |