diff options
-rw-r--r-- | data/style.css | 4 | ||||
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 19 | ||||
-rw-r--r-- | lib/VNDB/Handler/Discussions.pm | 112 | ||||
-rw-r--r-- | lib/VNWeb/Discussions/Search.pm | 210 |
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; |