From b7c525893bdd374d067e34d307bf0bc32df73f97 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 14 Oct 2019 18:24:06 +0200 Subject: v2rw: Convert user listing --- lib/VNDB/DB/Users.pm | 40 ++----------------- lib/VNDB/Func.pm | 13 +++++- lib/VNDB/Handler/Users.pm | 79 +------------------------------------ lib/VNDB/Util/CommonHTML.pm | 17 +------- lib/VNWeb/HTML.pm | 37 ++++++++++++++++- lib/VNWeb/Misc/History.pm | 11 +----- lib/VNWeb/Prelude.pm | 4 +- lib/VNWeb/User/List.pm | 96 +++++++++++++++++++++++++++++++++++++++++++++ lib/VNWeb/Validation.pm | 1 + 9 files changed, 153 insertions(+), 145 deletions(-) create mode 100644 lib/VNWeb/User/List.pm (limited to 'lib') diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index d80e6547..6981d291 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -10,8 +10,8 @@ our @EXPORT = qw| |; -# %options->{ username session uid ip registered search results page what sort reverse notperm } -# what: extended pubskin +# %options->{ uid results page what } +# what: pubskin # sort: username registered votes changes tags sub dbUserGet { my $s = shift; @@ -19,61 +19,29 @@ sub dbUserGet { page => 1, results => 10, what => '', - sort => '', @_ ); - my $token = unpack 'H*', $o{session}||''; - $o{search} =~ s/%// if $o{search}; my %where = ( - $o{username} ? ( - 'username = ?' => $o{username} ) : (), - $o{firstchar} ? ( - 'SUBSTRING(username from 1 for 1) = ?' => $o{firstchar} ) : (), - !$o{firstchar} && defined $o{firstchar} ? ( - 'ASCII(username) < 97 OR ASCII(username) > 122' => 1 ) : (), $o{uid} && !ref($o{uid}) ? ( 'id = ?' => $o{uid} ) : (), $o{uid} && ref($o{uid}) ? ( 'id IN(!l)' => [ $o{uid} ]) : (), - !$o{uid} && !$o{username} ? ( - 'id > 0' => 1 ) : (), - $o{ip} ? ( - 'ip !s ?' => [ $o{ip} =~ /\// ? '<<' : '=', $o{ip} ] ) : (), - $o{registered} ? ( - 'registered > to_timestamp(?)' => $o{registered} ) : (), - $o{search} ? ( - 'username ILIKE ?' => "%$o{search}%") : (), - $token ? ( - q|user_isloggedin(id, decode(?, 'hex')) IS NOT NULL| => $token ) : (), - $o{notperm} ? ( - 'perm & ~(?::smallint) > 0' => $o{notperm} ) : (), ); my @select = ( qw|id username c_votes c_changes c_tags hide_list|, VNWeb::DB::sql_user(), # XXX: This duplicates id and username, but updating all the code isn't going to be easy q|extract('epoch' from registered) as registered|, - $o{what} =~ /extended/ ? qw|perm ign_votes| : (), # mail $o{what} =~ /pubskin/ ? qw|pubskin_can pubskin_enabled customcss skin| : (), - $token ? qq|extract('epoch' from user_isloggedin(id, decode('$token', 'hex'))) as session_lastused| : (), ); - my $order = sprintf { - id => 'u.id %s', - username => 'u.username %s', - registered => 'u.registered %s', - votes => 'u.hide_list, u.c_votes %s', - changes => 'u.c_changes %s', - tags => 'u.c_tags %s', - }->{ $o{sort}||'username' }, $o{reverse} ? 'DESC' : 'ASC'; - my($r, $np) = $s->dbPage(\%o, q| SELECT !s FROM users u !W - ORDER BY !s|, - join(', ', @select), \%where, $order + ORDER BY id DESC|, + join(', ', @select), \%where ); return wantarray ? ($r, $np) : $r; diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index 3a1d9261..96a17106 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -3,7 +3,7 @@ package VNDB::Func; use strict; use warnings; -use TUWF ':html', 'kv_validate', 'xml_escape'; +use TUWF ':html', 'kv_validate', 'xml_escape', 'uri_escape'; use Exporter 'import'; use POSIX 'strftime', 'ceil', 'floor'; use JSON::XS; @@ -17,6 +17,7 @@ our @EXPORT = (@VNDBUtil::EXPORT, 'bb2html', 'bb2text', qw| lang_attr json_encode json_decode script_json form_compare + query_encode |); @@ -334,5 +335,15 @@ sub form_compare { return 0; } + +# Encode query parameters. Takes a hash or hashref with key/values, supports array values. +sub query_encode { + my $o = @_ == 1 ? $_[0] : {@_}; + return join '&', map { + my($k, $v) = ($_, $o->{$_}); + !defined $v ? () : ref $v ? map "$k=".uri_escape($_), sort @$v : "$k=".uri_escape($v) + } sort keys %$o; +} + 1; diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index 56a00d2a..a9c53206 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -3,17 +3,12 @@ package VNDB::Handler::Users; use strict; use warnings; -use TUWF ':html', 'xml_escape'; +use TUWF ':html'; use VNDB::Func; -use VNDB::Types; -use VNWeb::Auth; -use POSIX 'floor'; -use PWLookup; TUWF::register( qr{u([1-9]\d*)/posts} => \&posts, - qr{u/(all|[0a-z])} => \&list, ); @@ -70,77 +65,5 @@ sub posts { } -sub list { - my($self, $char) = @_; - - my $f = $self->formValidate( - { get => 's', required => 0, default => 'username', enum => [ qw|username registered votes changes tags| ] }, - { get => 'o', required => 0, default => 'a', enum => [ 'a','d' ] }, - { get => 'p', required => 0, default => 1, template => 'page' }, - { get => 'q', required => 0, default => '', maxlength => 50 }, - ); - return $self->resNotFound if $f->{_err}; - - $self->htmlHeader(noindex => 1, title => 'Browse users'); - - div class => 'mainbox'; - h1 'Browse users'; - form action => '/u/all', 'accept-charset' => 'UTF-8', method => 'get'; - $self->htmlSearchBox('u', $f->{q}); - end; - p class => 'browseopts'; - for ('all', 'a'..'z', 0) { - a href => "/u/$_", $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? 'ALL' : $_ ? uc $_ : '#'; - } - end; - end; - - my($list, $np) = $self->dbUserGet( - sort => $f->{s}, reverse => $f->{o} eq 'd', - what => 'hide_list', - $char ne 'all' ? ( - firstchar => $char ) : (), - results => 50, - page => $f->{p}, - search => $f->{q}, - ); - - $self->htmlBrowse( - items => $list, - options => $f, - nextpage => $np, - pageurl => "/u/$char?o=$f->{o};s=$f->{s};q=$f->{q}", - sorturl => "/u/$char?q=$f->{q}", - header => [ - [ 'Username', 'username' ], - [ 'Registered', 'registered' ], - [ 'Votes', 'votes' ], - [ 'Edits', 'changes' ], - [ 'Tags', 'tags' ], - ], - row => sub { - my($s, $n, $l) = @_; - Tr; - td class => 'tc1'; - VNWeb::HTML::user_($l); - end; - td class => 'tc2', fmtdate $l->{registered}; - td class => 'tc3'.($l->{hide_list} && $self->authCan('usermod') ? ' linethrough' : ''); - lit $l->{hide_list} && !$self->authCan('usermod') ? '-' : !$l->{c_votes} ? 0 : - qq|$l->{c_votes}|; - end; - td class => 'tc4'; - lit !$l->{c_changes} ? 0 : qq|$l->{c_changes}|; - end; - td class => 'tc5'; - lit !$l->{c_tags} ? 0 : qq|$l->{c_tags}|; - end; - end 'tr'; - }, - ); - $self->htmlFooter; -} - - 1; diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 3d18bdf5..81309325 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -418,22 +418,7 @@ sub htmlVoteStats { sub htmlSearchBox { - my($self, $sel, $v) = @_; - - fieldset class => 'search'; - p id => 'searchtabs'; - a href => '/v/all', $sel eq 'v' ? (class => 'sel') : (), 'Visual novels'; - a href => '/r', $sel eq 'r' ? (class => 'sel') : (), 'Releases'; - a href => '/p/all', $sel eq 'p' ? (class => 'sel') : (), 'Producers'; - a href => '/s/all', $sel eq 's' ? (class => 'sel') : (), 'Staff'; - a href => '/c/all', $sel eq 'c' ? (class => 'sel') : (), 'Characters'; - a href => '/g', $sel eq 'g' ? (class => 'sel') : (), 'Tags'; - a href => '/i', $sel eq 'i' ? (class => 'sel') : (), 'Traits'; - a href => '/u/all', $sel eq 'u' ? (class => 'sel') : (), 'Users'; - end; - input type => 'text', name => 'q', id => 'q', class => 'text', value => $v; - input type => 'submit', class => 'submit', value => 'Search!'; - end 'fieldset'; + shift; VNWeb::HTML::searchbox_(@_); } diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm index 08601252..8e7ffa3c 100644 --- a/lib/VNWeb/HTML.pm +++ b/lib/VNWeb/HTML.pm @@ -26,6 +26,8 @@ our @EXPORT = qw/ framework_ revision_ paginate_ + sortable_ + searchbox_ /; @@ -598,7 +600,7 @@ sub revision_ { # Creates next/previous buttons (tabs), if needed. # Arguments: -# url generator (code reference that takes $_ and returns a url for that page). +# url generator (code reference that takes ('p', $pagenumber) as arguments with $_=$pagenumber and returns a url for that page). # current page number (1..n), # nextpage (0/1 or, if the full count is known: [$total, $perpage]), # alignment (t/b) @@ -611,7 +613,7 @@ sub paginate_ { my($left, $page, $label) = @_; li_ mkclass(left => $left), sub { local $_ = $page; - my $u = $url->(); + my $u = $url->(p => $page); a_ href => $u, $label; } } @@ -635,4 +637,35 @@ sub paginate_ { } } + +# Generate sort buttons for a table header. This function assumes that sorting +# options are given as query parameters: 's' for the $column_name to sort on +# and 'o' for order ('a'sc/'d'esc). +# Options: $column_title, $column_name, $opt, $url +# Where $url is a function that is given ('p', undef, 's', $column_name, 'o', $order) and returns a URL. +sub sortable_ { + my($name, $opt, $url) = @_; + $opt->{s} eq $name && $opt->{o} eq 'a' ? txt_ ' ▴' : a_ href => $url->(p => undef, s => $name, o => 'a'), ' ▴'; + $opt->{s} eq $name && $opt->{o} eq 'd' ? txt_ '▾' : a_ href => $url->(p => undef, s => $name, o => 'd'), '▾'; +} + + +sub searchbox_ { + my($sel, $value) = @_; + fieldset_ class => 'search', sub { + p_ id => 'searchtabs', sub { + a_ href => '/v/all', $sel eq 'v' ? (class => 'sel') : (), 'Visual novels'; + a_ href => '/r', $sel eq 'r' ? (class => 'sel') : (), 'Releases'; + a_ href => '/p/all', $sel eq 'p' ? (class => 'sel') : (), 'Producers'; + a_ href => '/s/all', $sel eq 's' ? (class => 'sel') : (), 'Staff'; + a_ href => '/c/all', $sel eq 'c' ? (class => 'sel') : (), 'Characters'; + a_ href => '/g', $sel eq 'g' ? (class => 'sel') : (), 'Tags'; + a_ href => '/i', $sel eq 'i' ? (class => 'sel') : (), 'Traits'; + a_ href => '/u/all', $sel eq 'u' ? (class => 'sel') : (), 'Users'; + }; + input_ type => 'text', name => 'q', id => 'q', class => 'text', value => $value; + input_ type => 'submit', class => 'submit', value => 'Search!'; + }; +} + 1; diff --git a/lib/VNWeb/Misc/History.pm b/lib/VNWeb/Misc/History.pm index 5251d4ea..c96d07cc 100644 --- a/lib/VNWeb/Misc/History.pm +++ b/lib/VNWeb/Misc/History.pm @@ -51,15 +51,6 @@ sub fetch { } -sub _filturl { - my($filt) = @_; - return '?'.join '&', map { - my $k = $_; - ref $filt->{$k} ? map "$k=$_", sort $filt->{$k}->@* : "$k=$filt->{$k}" - } sort keys %$filt; -} - - # Also used by User::Page. # %opt: nopage => 1/0, results => $num sub tablebox_ { @@ -67,7 +58,7 @@ sub tablebox_ { my($lst, $np) = fetch $type, $id, $filt, \%opt; - my sub url { _filturl {%$filt, p => $_} } + my sub url { '?'.query_encode %$filt, p => $_ } paginate_ \&url, $filt->{p}, $np, 't' unless $opt{nopage}; div_ class => 'mainbox browse history', sub { diff --git a/lib/VNWeb/Prelude.pm b/lib/VNWeb/Prelude.pm index 66f78425..c00e9afb 100644 --- a/lib/VNWeb/Prelude.pm +++ b/lib/VNWeb/Prelude.pm @@ -13,7 +13,7 @@ # use VNDB::BBCode; # use VNDB::Types; # use VNDB::Config; -# use VNDB::Func 'fmtdate', 'fmtage', 'fmtvote'; +# use VNDB::Func 'fmtdate', 'fmtage', 'fmtvote', 'query_encode'; # use VNWeb::Auth; # use VNWeb::HTML; # use VNWeb::DB; @@ -55,7 +55,7 @@ sub import { use VNDB::BBCode; use VNDB::Types; use VNDB::Config; - use VNDB::Func 'fmtdate', 'fmtage', 'fmtvote'; + use VNDB::Func 'fmtdate', 'fmtage', 'fmtvote', 'query_encode'; use VNWeb::Auth; use VNWeb::HTML; use VNWeb::DB; diff --git a/lib/VNWeb/User/List.pm b/lib/VNWeb/User/List.pm new file mode 100644 index 00000000..dc34fe38 --- /dev/null +++ b/lib/VNWeb/User/List.pm @@ -0,0 +1,96 @@ +package VNWeb::User::List; + +use VNWeb::Prelude; + + +sub listing_ { + my($opt, $list, $count) = @_; + + my sub url { '?'.query_encode %$opt, @_ } + + paginate_ \&url, $opt->{p}, [$count, 50], 't'; + div_ class => 'mainbox browse', sub { + table_ class => 'stripe', sub { + thead_ sub { tr_ sub { + td_ class => 'tc1', sub { txt_ 'Username'; sortable_ 'username', $opt, \&url }; + td_ class => 'tc2', sub { txt_ 'Registered'; sortable_ 'registered', $opt, \&url }; + td_ class => 'tc3', sub { txt_ 'Votes'; sortable_ 'votes', $opt, \&url }; + td_ class => 'tc4', sub { txt_ 'Edits'; sortable_ 'changes', $opt, \&url }; + td_ class => 'tc5', sub { txt_ 'Tags'; sortable_ 'tags', $opt, \&url }; + } }; + tr_ sub { + my $l = $_; + td_ class => 'tc1', sub { user_ $l }; + td_ class => 'tc2', fmtdate $l->{registered}; + td_ mkclass(tc3 => 1, linethrough => $l->{hide_list} && auth->permUsermod), sub { + if($l->{hide_list} && !auth->permUsermod) { + txt_ '-'; + } elsif(!$l->{c_votes}) { + txt_ '0'; + } else { + a_ href => "/u$l->{user_id}/votes", $l->{c_votes}; + } + }; + td_ class => 'tc4', sub { + txt_ '-' if !$l->{c_changes}; + a_ href => "/u$l->{user_id}/hist", $l->{c_changes} if $l->{c_changes}; + }; + td_ class => 'tc5', sub { + txt_ '-' if !$l->{c_tags}; + a_ href => "/g/links?u=$l->{user_id}", $l->{c_tags} if $l->{c_tags}; + }; + } for @$list; + }; + }; + paginate_ \&url, $opt->{p}, [$count, 50], 'b'; +} + + +TUWF::get qr{/u/(?[0a-z]|all)}, sub { + my $char = tuwf->capture('char'); + + my $opt = eval { tuwf->validate(get => + p => { upage => 1 }, + s => { required => 0, default => 'registered', enum => [qw[username registered votes changes tags]] }, + o => { required => 0, default => 'd', enum => [qw[a d]] }, + q => { required => 0, default => '' }, + )->data } || return tuwf->resNotFound; + + my @where = ( + $char eq 'all' ? () : $char eq '0' ? "ascii(username) not between ascii('a') and ascii('z')" : "username like '$char%'", + $opt->{q} ? sql_or( + $opt->{q} =~ /^u?([0-9]+)$/ ? sql 'id =', \"$1" : (), + sql 'position(', \$opt->{q}, 'in username) > 0' + ) : () + ); + + my($list) = tuwf->dbPagei({ results => 50, page => $opt->{p} }, + 'SELECT', sql_user(), ',', sql_totime('registered'), 'as registered, c_votes, c_changes, c_tags, hide_list + FROM users u + WHERE', sql_and('id > 0', @where), + 'ORDER BY', { + username => 'username', + registered => 'id', + votes => auth->permUsermod ? 'c_votes' : 'hide_list, c_votes', + changes => 'c_changes', + tags => 'c_tags' + }->{$opt->{s}}, $opt->{o} eq 'd' ? 'DESC' : 'ASC' + ); + my $count = @where ? tuwf->dbVali('SELECT count(*) FROM users WHERE', sql_and @where) : tuwf->{stats}{users}; + + framework_ title => 'Browse users', index => 0, sub { + div_ class => 'mainbox', sub { + h1_ 'Browse users'; + form_ action => '/u/all', method => 'get', sub { + searchbox_ u => $opt->{q}; + }; + p_ class => 'browseopts', sub { + a_ href => "/u/$_", $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? 'ALL' : $_ ? uc $_ : '#' + for ('all', 'a'..'z', 0); + }; + }; + listing_ $opt, $list, $count if $count; + }; +}; + +1; diff --git a/lib/VNWeb/Validation.pm b/lib/VNWeb/Validation.pm index 5ddc1ac3..eb29a52c 100644 --- a/lib/VNWeb/Validation.pm +++ b/lib/VNWeb/Validation.pm @@ -19,6 +19,7 @@ TUWF::set custom_validations => { id => { uint => 1, max => 1<<40 }, editsum => { required => 1, length => [ 2, 5000 ] }, page => { uint => 1, min => 1, max => 1000, required => 0, default => 1 }, + upage => { uint => 1, min => 1, required => 0, default => 1 }, # pagination without a maximum username => { regex => qr/^(?!-*[a-z][0-9]+-*$)[a-z0-9-]*$/, minlength => 2, maxlength => 15 }, password => { length => [ 4, 500 ] }, }; -- cgit v1.2.3