From 0096b8af096ce08ef7ebb6bb5e8f91a4a6042d8a Mon Sep 17 00:00:00 2001 From: Yorhel Date: Thu, 27 Jun 2019 13:39:23 +0200 Subject: Add support for "private" threads Currently a mod-only feature. Each thread now has a 'private' flag which, when set, will make the thread visible only to users mentioned in the boards. --- lib/Multi/Feed.pm | 6 +++--- lib/Multi/IRC.pm | 6 +++--- lib/VNDB/DB/Discussions.pm | 25 ++++++++++++++----------- lib/VNDB/Handler/Discussions.pm | 17 ++++++++++++++++- util/sql/schema.sql | 3 ++- util/updates/update_20190627.sql | 1 + 6 files changed, 39 insertions(+), 19 deletions(-) create mode 100644 util/updates/update_20190627.sql diff --git a/lib/Multi/Feed.pm b/lib/Multi/Feed.pm index b4cddfac..76a7e459 100644 --- a/lib/Multi/Feed.pm +++ b/lib/Multi/Feed.pm @@ -36,7 +36,7 @@ sub generate { JOIN threads_posts tp ON tp.tid = t.id AND tp.num = 1 JOIN threads_boards tb ON tb.tid = t.id AND tb.type = 'an' JOIN users u ON u.id = tp.uid - WHERE NOT t.hidden + WHERE NOT t.hidden AND NOT t.private ORDER BY t.id DESC LIMIT $1}, [$VNDB::S{atom_feeds}{announcements}[0]], @@ -62,14 +62,14 @@ sub generate { [$VNDB::S{atom_feeds}{changes}[0]], sub { write_atom(changes => @_); }; - # posts (this query isn't all that fast) + # posts pg_cmd q{ SELECT '/t'||t.id||'.'||tp.num AS id, t.title||' (#'||tp.num||')' AS title, extract('epoch' from tp.date) AS published, extract('epoch' from tp.edited) AS updated, u.username, u.id AS uid, tp.msg AS summary FROM threads_posts tp JOIN threads t ON t.id = tp.tid JOIN users u ON u.id = tp.uid - WHERE NOT tp.hidden AND NOT t.hidden + WHERE NOT tp.hidden AND NOT t.hidden AND NOT t.private ORDER BY tp.date DESC LIMIT $1}, [$VNDB::S{atom_feeds}{posts}[0]], diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index b1be1a24..b9b8a035 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -297,7 +297,7 @@ sub handleid { $t eq 'p' ? 'p.name AS title FROM producers p WHERE p.id = $2' : $t eq 'c' ? 'c.name AS title FROM chars c WHERE c.id = $2' : $t eq 's' ? 'sa.name AS title FROM staff s JOIN staff_alias sa ON sa.aid = s.aid AND sa.id = s.id WHERE s.id = $2' : - $t eq 't' ? 'title, '.$GETBOARDS.' FROM threads t WHERE id = $2' : + $t eq 't' ? 'title, '.$GETBOARDS.' FROM threads t WHERE NOT t.hidden AND NOT t.private AND t.id = $2' : $t eq 'g' ? 'name AS title FROM tags WHERE id = $2' : $t eq 'i' ? 'name AS title FROM traits WHERE id = $2' : $t eq 'd' ? 'title FROM docs WHERE id = $2' : @@ -312,7 +312,7 @@ sub handleid { $t eq 'c' ? 'ch.name AS title, u.username, c.comments FROM changes c JOIN chars_hist ch ON c.id = ch.chid JOIN users u ON u.id = c.requester WHERE c.type = \'c\' AND c.itemid = $2 AND c.rev = $3' : $t eq 's' ? 'sah.name AS title, u.username, c.comments FROM changes c JOIN staff_hist sh ON c.id = sh.chid JOIN users u ON u.id = c.requester JOIN staff_alias_hist sah ON sah.chid = c.id AND sah.aid = sh.aid WHERE c.type = \'s\' AND c.itemid = $2 AND c.rev = $3' : $t eq 'd' ? 'dh.title, u.username, c.comments FROM changes c JOIN docs_hist dh ON c.id = dh.chid JOIN users u ON u.id = c.requester WHERE c.type = \'d\' AND c.itemid = $2 AND c.rev = $3' : - 't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE t.id = $2 AND tp.num = $3'), + 't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE NOT t.hidden AND NOT t.private AND t.id = $2 AND tp.num = $3'), [ $t, $id, $rev], $c if $rev && $t =~ /[dvprtcs]/; } @@ -359,7 +359,7 @@ sub notify { FROM threads_posts tp JOIN threads t ON t.id = tp.tid JOIN users u ON u.id = tp.uid - WHERE tp.date > $1 AND tp.num = 1 + WHERE tp.date > $1 AND tp.num = 1 AND NOT t.hidden AND NOT t.private ORDER BY tp.date}, trait => q{ SELECT 'i' AS type, t.id, t.name AS title, u.username, t.id AS lastid diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm index b4771adc..0e7f0623 100644 --- a/lib/VNDB/DB/Discussions.pm +++ b/lib/VNDB/DB/Discussions.pm @@ -8,7 +8,7 @@ use Exporter 'import'; our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount dbPollStats dbPollVote|; -# Options: id, type, iid, results, page, what, notusers, search, sort, reverse +# Options: id, type, iid, results, page, what, asuser, notusers, search, sort, reverse # What: boards, boardtitles, firstpost, lastpost, poll # Sort: id lastpost sub dbThreadGet { @@ -19,9 +19,11 @@ sub dbThreadGet { my @where = ( $o{id} ? ( - 't.id = ?' => $o{id} ) : (), - !$o{id} ? ( - 't.hidden = FALSE' => 0 ) : (), + 't.id = ?' => $o{id} + ) : ( + 'NOT t.hidden' => 0, + q{(NOT t.private OR EXISTS(SELECT 1 FROM threads_boards WHERE tid = t.id AND type = 'u' AND iid = ?))} => $o{asuser} + ), $o{type} && !$o{iid} ? ( 'EXISTS(SELECT 1 FROM threads_boards WHERE tid = t.id AND type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (), $o{type} && $o{iid} ? ( @@ -38,7 +40,7 @@ sub dbThreadGet { } my @select = ( - qw|t.id t.title t.count t.locked t.hidden|, 't.poll_question IS NOT NULL AS haspoll', + qw|t.id t.title t.count t.locked t.hidden t.private|, 't.poll_question IS NOT NULL AS haspoll', $o{what} =~ /lastpost/ ? ('tpl.uid AS luid', q|EXTRACT('epoch' from tpl.date) AS ldate|, 'ul.username AS lusername') : (), $o{what} =~ /poll/ ? (qw|t.poll_question t.poll_max_options t.poll_preview t.poll_recast|) : (), ); @@ -118,7 +120,7 @@ sub dbThreadGet { } -# id, %options->( title locked hidden boards poll_question poll_max_options poll_preview poll_recast poll_options } +# id, %options->( title locked hidden private boards poll_question poll_max_options poll_preview poll_recast poll_options } # The poll_{question,options,max_options} fields should not be set when there # are no changes to the poll info. Either all or none of these fields should be # set. @@ -129,6 +131,7 @@ sub dbThreadEdit { 'title = ?' => $o{title}, 'locked = ?' => $o{locked}?1:0, 'hidden = ?' => $o{hidden}?1:0, + 'private = ?' => $o{private}?1:0, 'poll_preview = ?' => $o{poll_preview}?1:0, 'poll_recast = ?' => $o{poll_recast}?1:0, exists $o{poll_question} ? ( @@ -163,15 +166,15 @@ sub dbThreadEdit { } -# %options->{ title hidden locked boards poll_stuff } +# %options->{ title hidden locked private boards poll_stuff } sub dbThreadAdd { my($self, %o) = @_; my $id = $self->dbRow(q| - INSERT INTO threads (title, hidden, locked, poll_question, poll_max_options, poll_preview, poll_recast) - VALUES (?, ?, ?, ?, ?, ?, ?) + INSERT INTO threads (title, hidden, locked, private, poll_question, poll_max_options, poll_preview, poll_recast) + VALUES (?, ?, ?, ?, ?, ?, ?, ?) RETURNING id|, - $o{title}, $o{hidden}?1:0, $o{locked}?1:0, $o{poll_question}||undef, $o{poll_max_options}||1, $o{poll_preview}?1:0, $o{poll_recast}?1:0 + $o{title}, $o{hidden}?1:0, $o{locked}?1:0, $o{private}?1:0, $o{poll_question}||undef, $o{poll_max_options}||1, $o{poll_preview}?1:0, $o{poll_recast}?1:0 )->{id}; $self->dbExec(q| @@ -224,7 +227,7 @@ sub dbPostGet { $o{hide} ? ( 'tp.hidden = FALSE' => 1 ) : (), $o{hide} && $o{what} =~ /thread/ ? ( - 't.hidden = FALSE' => 1 ) : (), + 't.hidden = FALSE AND t.private = FALSE' => 1 ) : (), $o{search} ? ( 'bb_tsvector(msg) @@ to_tsquery(?)' => $o{search}) : (), $o{type} ? ( diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm index cab4fdb9..48676bfc 100644 --- a/lib/VNDB/Handler/Discussions.pm +++ b/lib/VNDB/Handler/Discussions.pm @@ -36,12 +36,17 @@ sub thread { my $t = $self->dbThreadGet(id => $tid, what => 'boardtitles poll')->[0]; return $self->resNotFound if !$t->{id} || $t->{hidden} && !$self->authCan('boardmod'); + my $onuserboard = grep $_->{type} eq 'u' && $_->{iid} == ($self->authInfo->{id}||-1), @{$t->{boards}}; + return $self->resNotFound if $t->{private} && !($self->authCan('boardmod') || $onuserboard); + my $p = $self->dbPostGet(tid => $tid, results => 25, page => $page, what => 'user'); return $self->resNotFound if !$p->[0]; $self->htmlHeader(title => $t->{title}, noindex => 1); div class => 'mainbox'; h1 $t->{title}; + h2 'Hidden' if $t->{hidden}; + h2 'Private' if $t->{private}; h2 'Posted in'; ul; for (sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } @{$t->{boards}}) { @@ -186,6 +191,9 @@ sub edit { { post => 'hidden', required => 0 }, { post => 'nolastmod', required => 0 }, ) : (), + $self->authCan('boardmod') || $self->authCan('dbmod') || $self->authCan('tagmod') ? ( + { post => 'private', required => 0 }, + ) : (), { post => 'msg', maxlength => 32768 }, { post => 'fullreply', required => 0 }, ); @@ -196,7 +204,7 @@ sub edit { push @{$frm->{_err}}, 'Please wait 30 seconds before making another post' if !$num && !$frm->{_err} && $self->dbPostGet( uid => $self->authInfo->{id}, tid => $tid, mindate => time - 30, results => 1, $tid ? () : (num => 1))->[0]{num}; - # Don't allow regular users to create more than 10 threads a day + # Don't allow regular users to create more than 5 threads a day push @{$frm->{_err}}, 'You can only create 5 threads every 24 hours' if !$tid && !$self->authCan('boardmod') && @{$self->dbPostGet(uid => $self->authInfo->{id}, mindate => time - 24*3600, num => 1)} >= 5; @@ -242,6 +250,7 @@ sub edit { boards => \@boards, hidden => $frm->{hidden}, locked => $frm->{locked}, + private => $frm->{private}, poll_preview => $frm->{poll_preview}||0, poll_recast => $frm->{poll_recast}||0, !$haspoll ? ( @@ -278,6 +287,7 @@ sub edit { $frm->{title} ||= $t->{title}; $frm->{locked} //= $t->{locked}; $frm->{hidden} //= $t->{hidden}; + $frm->{private} //= $t->{private}; if($t->{haspoll}) { $frm->{poll} //= 1; $frm->{poll_question} ||= $t->{poll_question}; @@ -308,6 +318,9 @@ sub edit { $self->authCan('boardmod') ? ( [ check => name => 'Locked', short => 'locked' ], ) : (), + $self->authCan('boardmod') || $self->authCan('dbmod') || $self->authCan('tagmod') ? ( + [ check => name => 'Private (only visible to users mentioned in the boards)', short => 'private' ], + ) : (), ) : ( [ static => label => 'Topic', content => qq||.xml_escape($t->{title}).'' ], ), @@ -389,6 +402,7 @@ sub board { page => $f->{p}, what => 'firstpost lastpost boardtitles', sort => $type eq 'an' ? 'id' : 'lastpost', reverse => 1, + asuser => $self->authInfo()->{id}, ); $self->htmlHeader(title => $title, noindex => 1, feeds => [ $type eq 'an' ? 'announcements' : 'posts' ]); @@ -459,6 +473,7 @@ sub index { page => 1, what => 'firstpost lastpost boardtitles', sort => 'lastpost', reverse => 1, + asuser => $self->authInfo()->{id}, ); h1 class => 'boxtitle'; a href => "/t/$_", $self->{discussion_boards}{$_}; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 40eb6a2b..9730268d 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -510,7 +510,8 @@ CREATE TABLE threads ( poll_question varchar(100), poll_max_options smallint NOT NULL DEFAULT 1, poll_preview boolean NOT NULL DEFAULT FALSE, - poll_recast boolean NOT NULL DEFAULT FALSE + poll_recast boolean NOT NULL DEFAULT FALSE, + private boolean NOT NULL DEFAULT FALSE ); -- threads_poll_options diff --git a/util/updates/update_20190627.sql b/util/updates/update_20190627.sql new file mode 100644 index 00000000..28ed0f65 --- /dev/null +++ b/util/updates/update_20190627.sql @@ -0,0 +1 @@ +ALTER TABLE threads ADD COLUMN private boolean NOT NULL DEFAULT FALSE; -- cgit v1.2.3