summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-10-14 18:24:06 +0200
committerYorhel <git@yorhel.nl>2019-10-14 18:24:06 +0200
commitb7c525893bdd374d067e34d307bf0bc32df73f97 (patch)
tree1a3609d44bc3746fd8dfd66e73e0bd360255613d
parentb539ea56c2406a110ca6666e9f42c3b4af8e1a10 (diff)
v2rw: Convert user listingHEADmaster
-rw-r--r--lib/VNDB/DB/Users.pm40
-rw-r--r--lib/VNDB/Func.pm13
-rw-r--r--lib/VNDB/Handler/Users.pm79
-rw-r--r--lib/VNDB/Util/CommonHTML.pm17
-rw-r--r--lib/VNWeb/HTML.pm37
-rw-r--r--lib/VNWeb/Misc/History.pm11
-rw-r--r--lib/VNWeb/Prelude.pm4
-rw-r--r--lib/VNWeb/User/List.pm96
-rw-r--r--lib/VNWeb/Validation.pm1
9 files changed, 153 insertions, 145 deletions
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|<a href="/u$l->{id}/votes">$l->{c_votes}</a>|;
- end;
- td class => 'tc4';
- lit !$l->{c_changes} ? 0 : qq|<a href="/u$l->{id}/hist">$l->{c_changes}</a>|;
- end;
- td class => 'tc5';
- lit !$l->{c_tags} ? 0 : qq|<a href="/g/links?u=$l->{id}">$l->{c_tags}</a>|;
- 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/(?<char>[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 ] },
};