summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-11-15 10:29:06 +0100
committerYorhel <git@yorhel.nl>2019-11-15 10:30:17 +0100
commit73f04d94a62d7fc37b524011fda5d65ea466cdc9 (patch)
treeddcad92e3f24293384a9959155de1d9793fd00d4
parent389fe32c24adce3277548892b5d636c40ac35bc4 (diff)
v2rw: Convert discussion board search
With minor styling improvements and full pagination for thread title search. I did combine a few queries which I initially separated for performance reasons. A few quick benchmarks show that it's fast enough, but I'll have to see how it performs in production.
-rw-r--r--data/style.css4
-rw-r--r--lib/VNDB/DB/Discussions.pm19
-rw-r--r--lib/VNDB/Handler/Discussions.pm112
-rw-r--r--lib/VNWeb/Discussions/Search.pm210
4 files changed, 212 insertions, 133 deletions
diff --git a/data/style.css b/data/style.css
index a0d744e2..a4526426 100644
--- a/data/style.css
+++ b/data/style.css
@@ -366,9 +366,9 @@ div.mainbox.discussions td.tc4 { text-align: right; }
div.mainbox.discussions a.locked { text-decoration: line-through; }
div.mainbox.discussions b.boards { padding-left: 10px; font-weight: normal; }
div.mainbox.discussions b.boards a { color: $grayedout$; }
-div.discussions td.tc2 { width: 60px; }
+div.discussions td.tc2 { width: 60px; text-align: right }
div.discussions td.tc3 { width: 110px; }
-div.discussions td.tc4 { width: 230px; }
+div.discussions td.tc4 { width: 250px; }
div.discussions .pollflag { color: $grayedout$; padding-right: 6px; }
div.postsearch td.tc1_1 { width: 60px; padding-left: 0; padding-right: 0; text-align: right }
div.postsearch td.tc1_2 { width: 25px; padding-left: 0 }
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 75fb93e8..77af72fb 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -228,8 +228,6 @@ sub dbPostGet {
'tp.hidden = FALSE' => 1 ) : (),
$o{hide} && $o{what} =~ /thread/ ? (
't.hidden = FALSE AND t.private = FALSE' => 1 ) : (),
- $o{search} ? (
- 'bb_tsvector(msg) @@ to_tsquery(?)' => $o{search}) : (),
$o{type} ? (
'tp.tid IN(SELECT tid FROM threads_boards WHERE type IN(!l))' => [ ref $o{type} ? $o{type} : [ $o{type} ] ] ) : (),
);
@@ -259,23 +257,6 @@ sub dbPostGet {
join(', ', @select), join(' ', @join), \%where, $order
);
- # Get headlines in a separate query
- if($o{search} && @$r) {
- my %r = map {
- ($r->[$_]{tid}.'.'.$r->[$_]{num}, $_)
- } 0..$#$r;
- my $where = join ' or ', ('(tid = ? and num = ?)')x@$r;
- my @where = map +($_->{tid},$_->{num}), @$r;
- my $h = join ',', map "$_=$o{headline}{$_}", $o{headline} ? keys %{$o{headline}} : ();
-
- $r->[$r{$_->{tid}.'.'.$_->{num}}]{headline} = $_->{headline} for (@{$self->dbAll(qq|
- SELECT tid, num, ts_headline('english', strip_bb_tags(strip_spoilers(msg)), to_tsquery(?), ?) as headline
- FROM threads_posts
- WHERE $where|,
- $o{search}, $h, @where
- )});
- }
-
return wantarray ? ($r, $np) : $r;
}
diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm
index f88bce17..82dcdfa1 100644
--- a/lib/VNDB/Handler/Discussions.pm
+++ b/lib/VNDB/Handler/Discussions.pm
@@ -18,7 +18,6 @@ TUWF::register(
qr{t([1-9]\d*)/reply} => \&edit,
qr{t([1-9]\d*)\.([1-9]\d*)/edit} => \&edit,
qr{t/(db|an|ge|[vpu])([1-9]\d*)?/new} => \&edit,
- qr{t/search} => \&search,
qr{t} => \&index,
);
@@ -487,117 +486,6 @@ sub index {
}
-sub search {
- my $self = shift;
-
- my $frm = $self->formValidate(
- { get => 'bq', required => 0, maxlength => 100 },
- { get => 'b', required => 0, multi => 1, enum => [ keys %BOARD_TYPE ] },
- { get => 't', required => 0 },
- { get => 'p', required => 0, default => 1, template => 'page' },
- );
- return $self->resNotFound if $frm->{_err};
-
- $self->htmlHeader(title => 'Search the discussion board', noindex => 1);
- $self->htmlForm({ frm => $frm, action => '/t/search', method => 'get', nosubmit => 1, noformcode => 1 }, 'boardsearch' => ['Search the discussion board',
- [ input => short => 'bq', name => 'Query' ],
- [ check => short => 't', name => 'Only search thread titles' ],
- [ select => short => 'b', name => 'Boards', multi => 1, size => scalar keys %BOARD_TYPE,
- options => [ map [$_,$BOARD_TYPE{$_}{txt}], keys %BOARD_TYPE ] ],
- [ static => content => sub {
- input type => 'submit', class => 'submit', tabindex => 10, value => 'Search!';
- } ],
- ]);
- return $self->htmlFooter if !$frm->{bq};
-
- my %boards = map +($_,1), @{$frm->{b}};
- %boards = () if keys %boards == keys %BOARD_TYPE;
-
- my($l, $np);
- if($frm->{t}) {
- ($l, $np) = $self->dbThreadGet(
- keys %boards ? ( type => [keys %boards] ) : (),
- search => $frm->{bq},
- results => 50,
- page => $frm->{p},
- what => 'firstpost lastpost boardtitles',
- sort => 'lastpost', reverse => 1,
- );
- } else {
- # TODO: Allow or-matching too. But what syntax?
- (my $ts = $frm->{bq}) =~ y{+|&:*()="';!?$%^\\[]{}<>~` }{ }s;
- $ts =~ s/ +/ /;
- $ts =~ s/^ //;
- $ts =~ s/ $//;
- $ts =~ s/ / & /g;
- $ts =~ s/(?:^| )-([^ ]+)/ !$1 /;
- ($l, $np) = $self->dbPostGet(
- keys %boards ? ( type => [keys %boards] ) : (),
- search => $ts,
- results => 20,
- page => $frm->{p},
- hide => 1,
- what => 'thread user',
- sort => 'date', reverse => 1,
- headline => {
- # HACK: The bbcodes are stripped from the original messages when
- # creating the headline, so they are guaranteed not to show up in the
- # message. This means we can re-use them for highlighting without
- # worrying that they conflict with the message contents.
- MaxFragments => 2, MinWords => 15, MaxWords => 40, StartSel => '[raw]', StopSel => '[/raw]', FragmentDelimiter => '[code]',
- },
- );
- }
-
- my $url = '/t/search?'.join ';', 'bq='.uri_escape($frm->{bq}), $frm->{t} ? 't=1' : (), map "b=$_", keys %boards;
- if(!@$l) {
- div class => 'mainbox';
- h1 'No results';
- p 'No threads or messages found matching your criteria.';
- end;
- } elsif($frm->{t}) {
- _threadlist($self, $l, $frm, $np, $url, 'all');
- } else {
- $self->htmlBrowse(
- items => $l,
- options => $frm,
- nextpage => $np,
- pageurl => $url,
- class => 'postsearch',
- header => [
- sub { td class => 'tc1_1', ''; td class => 'tc1_2', ''; },
- [ 'Date' ],
- [ 'User' ],
- [ 'Message' ],
- ],
- row => sub {
- my($s, $n, $l) = @_;
- my $link = "/t$l->{tid}.$l->{num}";
- Tr;
- td class => 'tc1_1'; a href => $link, 't'.$l->{tid}; end;
- td class => 'tc1_2'; a href => $link, '.'.$l->{num}; end;
- td class => 'tc2', fmtdate $l->{date};
- td class => 'tc3'; VNWeb::HTML::user_($l); end;
- td class => 'tc4';
- div class => 'title';
- a href => $link, $l->{title};
- end;
- my $h = xml_escape $l->{headline};
- $h =~ s/\[raw\]/<b class="standout">/g;
- $h =~ s/\[\/raw\]/<\/b>/g;
- $h =~ s/\[code\]/<b class="grayedout">...<\/b><br \/>/g;
- div class => 'thread';
- lit $h;
- end;
- end;
- end;
- }
- );
- }
- $self->htmlFooter;
-}
-
-
sub _threadlist {
my($self, $list, $f, $np, $url, $board) = @_;
$self->htmlBrowse(
diff --git a/lib/VNWeb/Discussions/Search.pm b/lib/VNWeb/Discussions/Search.pm
new file mode 100644
index 00000000..9c947337
--- /dev/null
+++ b/lib/VNWeb/Discussions/Search.pm
@@ -0,0 +1,210 @@
+package VNWeb::Discussions::Search;
+
+use VNWeb::Prelude;
+
+
+sub filters_ {
+ state $schema = tuwf->compile({ type => 'hash', keys => {
+ bq => { required => 0, default => '' },
+ b => { type => 'array', scalar => 1, required => 0, default => [keys %BOARD_TYPE], values => { enum => \%BOARD_TYPE } },
+ t => { anybool => 1 },
+ p => { page => 1 },
+ }});
+ my $filt = eval { tuwf->validate(get => $schema)->data } || tuwf->pass;
+ my %boards = map +($_,1), $filt->{b}->@*;
+
+ form_ method => 'get', action => tuwf->reqPath(), sub {
+ table_ style => 'margin: 0 auto', sub { tr_ sub {
+ td_ style => 'padding: 10px', sub {
+ p_ class => 'linkradio', sub {
+ join_ \&br_, sub {
+ input_ type => 'checkbox', name => 'b', id => "b_$_", value => $_, $boards{$_} ? (checked => 'checked') : ();
+ label_ for => "b_$_", $BOARD_TYPE{$_}{txt};
+ }, keys %BOARD_TYPE;
+ }
+ };
+ td_ style => 'padding: 10px', sub {
+ input_ type => 'text', class => 'text', name => 'bq', style => 'width: 400px', placeholder => 'Search', value => $filt->{bq};
+
+ p_ class => 'linkradio', sub {
+ input_ type => 'checkbox', name => 't', id => 't', value => 1, $filt->{t} ? (checked => 'checked') : ();
+ label_ for => 't', 'Only search thread titles';
+ };
+
+ input_ type => 'submit', class => 'submit', value => 'Search';
+ debug_ $filt;
+ };
+ };
+ }
+ };
+ $filt
+}
+
+
+sub noresults_ {
+ div_ class => 'mainbox', sub {
+ h1_ 'No results';
+ p_ 'No threads or messages found matching your criteria.';
+ };
+}
+
+
+sub posts_ {
+ my($filt) = @_;
+
+ # Turn query into something suitable for to_tsquery()
+ # TODO: Use Postgres 11 websearch_to_tsquery() instead.
+ (my $ts = $filt->{bq}) =~ y{+|&:*()="';!?$%^\\[]{}<>~` }{ }s;
+ $ts =~ s/ +/ /;
+ $ts =~ s/^ //;
+ $ts =~ s/ $//;
+ $ts =~ s/ / & /g;
+ $ts =~ s/(?:^| )-([^ ]+)/ !$1 /;
+
+ # HACK: The bbcodes are stripped from the original messages when creating
+ # the headline, so they are guaranteed not to show up in the message. This
+ # means we can re-use them for highlighting without worrying that they
+ # conflict with the message contents.
+
+ my($posts, $np) = tuwf->dbPagei({ results => 20, page => $filt->{p} }, q{
+ SELECT tp.tid, tp.num, t.title
+ , }, sql_user(), q{
+ , }, sql_totime('tp.date'), q{as date
+ , ts_headline('english', strip_bb_tags(strip_spoilers(tp.msg)), to_tsquery(}, \$ts, '),',
+ \'MaxFragments=2,MinWords=15,MaxWords=40,StartSel=[raw],StopSel=[/raw],FragmentDelimiter=[code]',
+ q{) as headline
+ FROM threads_posts tp
+ JOIN threads t ON t.id = tp.tid
+ JOIN users u ON u.id = tp.uid
+ WHERE NOT t.hidden AND NOT t.private AND NOT tp.hidden
+ AND bb_tsvector(tp.msg) @@ to_tsquery(}, \$ts, ')',
+ $filt->{b}->@* < keys %BOARD_TYPE ? ('AND t.id IN(SELECT tid FROM threads_boards WHERE type IN', $filt->{b}, ')') : (), q{
+ ORDER BY tp.date DESC
+ });
+
+ return noresults_ if !@$posts;
+
+ my sub url { '?'.query_encode %$filt, @_ }
+ paginate_ \&url, $filt->{p}, $np, 't';
+ div_ class => 'mainbox browse postsearch', sub {
+ table_ class => 'stripe', sub {
+ thead_ sub { tr_ sub {
+ td_ class => 'tc1_1', 'Id';
+ td_ class => 'tc1_2', '';
+ td_ class => 'tc2', 'Date';
+ td_ class => 'tc3', 'User';
+ td_ class => 'tc4', sub { txt_ 'Message'; debug_ $posts; };
+ }};
+ tr_ sub {
+ my $l = $_;
+ my $link = "/t$l->{tid}.$l->{num}";
+ td_ class => 'tc1_1', sub { a_ href => $link, 't'.$l->{tid} };
+ td_ class => 'tc1_2', sub { a_ href => $link, '.'.$l->{num} };
+ td_ class => 'tc2', fmtdate $l->{date};
+ td_ class => 'tc3', sub { user_ $l };
+ td_ class => 'tc4', sub {
+ div_ class => 'title', sub { a_ href => $link, $l->{title} };
+ div_ class => 'thread', sub { lit_(
+ TUWF::XML::xml_escape($l->{headline})
+ =~ s/\[raw\]/<b class="standout">/gr
+ =~ s/\[\/raw\]/<\/b>/gr
+ =~ s/\[code\]/<b class="grayedout">...<\/b><br \/>/gr
+ )};
+ };
+ } for @$posts;
+ }
+ };
+ paginate_ \&url, $filt->{p}, $np, 'b';
+}
+
+
+sub threads_ {
+ my($filt) = @_;
+
+ my $where = sql_and
+ 'NOT t.hidden',
+ 'NOT t.private',
+ $filt->{b}->@* < keys %BOARD_TYPE ? sql('t.id IN(SELECT tid FROM threads_boards WHERE type IN', $filt->{b}, ')') : (),
+ map sql('t.title ilike', \('%'.($_ =~ s/%//gr).'%')), grep length($_) > 0, split /[ -,._]/, $filt->{bq};
+
+ my $count = tuwf->dbVali('SELECT count(*) FROM threads t WHERE', $where);
+ return noresults_ if !$count;
+
+ my $lst = tuwf->dbPagei({ results => 50, page => $filt->{p} }, q{
+ SELECT t.id, t.title, t.count, t.locked, t.poll_question IS NOT NULL AS haspoll
+ , }, sql_user('tfu', 'firstpost_'), ',', sql_totime('tf.date'), q{ as firstpost_date
+ , }, sql_user('tlu', 'lastpost_'), ',', sql_totime('tl.date'), q{ as lastpost_date
+ FROM threads t
+ JOIN threads_posts tf ON tf.tid = t.id AND tf.num = 1
+ JOIN threads_posts tl ON tl.tid = t.id AND tl.num = t.count
+ JOIN users tfu ON tfu.id = tf.uid
+ JOIN users tlu ON tlu.id = tl.uid
+ WHERE }, $where, q{
+ ORDER BY tl.date DESC
+ });
+
+ enrich boards => id => tid => sub { sql q{
+ SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, v.title, p.name) AS title, COALESCE(u.username, v.original, p.original) AS original
+ FROM threads_boards tb
+ LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid
+ LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid
+ LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid
+ WHERE tb.tid IN}, $_[0], q{
+ ORDER BY tb.type, tb.iid
+ }}, $lst;
+
+
+ my sub url { '?'.query_encode %$filt, @_ }
+ paginate_ \&url, $filt->{p}, [ $count, 50 ], 't';
+ div_ class => 'mainbox browse discussions', sub {
+ table_ class => 'stripe', sub {
+ thead_ sub { tr_ sub {
+ td_ class => 'tc1', sub { txt_ 'Topic'; debug_ $lst };
+ td_ class => 'tc2', 'Replies';
+ td_ class => 'tc3', 'Starter';
+ td_ class => 'tc4', 'Last post';
+ }};
+ tr_ sub {
+ my $l = $_;
+ td_ class => 'tc1', sub {
+ a_ mkclass(locked => $l->{locked}), href => "/t$l->{id}", sub {
+ span_ class => 'pollflag', '[poll]' if $l->{haspoll};
+ txt_ shorten $l->{title}, 50;
+ };
+ b_ class => 'boards', sub {
+ join_ ', ', sub {
+ a_ href => "/t/$_->{type}".($_->{iid}||''),
+ title => $_->{original}||$BOARD_TYPE{$_->{type}}{txt},
+ shorten $_->{title}||$BOARD_TYPE{$_->{type}}{txt}, 30;
+ }, $l->{boards}->@[0 .. min 4, $#{$l->{boards}}];
+ txt_ ', ...' if $l->{boards}->@* > 4;
+ };
+ };
+ td_ class => 'tc2', $l->{count}-1;
+ td_ class => 'tc3', sub { user_ $l, 'firstpost_' };
+ td_ class => 'tc4', sub {
+ user_ $l, 'lastpost_';
+ txt_ ' @ ';
+ a_ href => "/t$l->{id}.$l->{count}", fmtdate $l->{lastpost_date}, 'full';
+ };
+ } for @$lst;
+ }
+ };
+ paginate_ \&url, $filt->{p}, [ $count, 50 ], 'b';
+}
+
+
+TUWF::get qr{/t/search}, sub {
+ framework_ title => 'Search the discussion board',
+ sub {
+ my $filt;
+ div_ class => 'mainbox', sub {
+ h1_ 'Search the discussion board';
+ $filt = filters_;
+ };
+ posts_ $filt if $filt->{bq} && !$filt->{t};
+ threads_ $filt if $filt->{bq} && $filt->{t};
+ };
+};
+
+1;