diff options
Diffstat (limited to 'lib')
-rw-r--r-- | lib/Multi/API.pm | 16 | ||||
-rw-r--r-- | lib/Multi/Anime.pm | 17 | ||||
-rw-r--r-- | lib/Multi/Maintenance.pm | 37 | ||||
-rw-r--r-- | lib/Multi/RG.pm | 11 | ||||
-rw-r--r-- | lib/POE/Filter/VNDBAPI.pm | 135 | ||||
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 18 | ||||
-rw-r--r-- | lib/VNDB/DB/Misc.pm | 69 | ||||
-rw-r--r-- | lib/VNDB/DB/Producers.pm | 49 | ||||
-rw-r--r-- | lib/VNDB/DB/Releases.pm | 108 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 89 | ||||
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 30 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 14 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 113 | ||||
-rw-r--r-- | lib/VNDB/Handler/Discussions.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Misc.pm | 21 | ||||
-rw-r--r-- | lib/VNDB/Handler/Producers.pm | 30 | ||||
-rw-r--r-- | lib/VNDB/Handler/Releases.pm | 24 | ||||
-rw-r--r-- | lib/VNDB/Handler/Tags.pm | 162 | ||||
-rw-r--r-- | lib/VNDB/Handler/ULists.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Users.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNBrowse.pm | 57 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNEdit.pm | 35 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 46 | ||||
-rw-r--r-- | lib/VNDB/L10N.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/Plugin/TransAdmin.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/Util/BrowseHTML.pm | 204 | ||||
-rw-r--r-- | lib/VNDB/Util/CommonHTML.pm | 153 |
27 files changed, 651 insertions, 807 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index 8014f9be..b6100dfa 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -264,7 +264,7 @@ sub client_connect { # the wheel my $w = POE::Wheel::ReadWrite->new( Handle => $sock, - Filter => POE::Filter::VNDBAPI->new(type => 'server'), + Filter => POE::Filter::VNDBAPI->new(), ErrorEvent => 'client_error', InputEvent => 'client_input', ); @@ -303,11 +303,8 @@ sub client_input { # parse error? return cerr $c, $arg->[0]{id}, $arg->[0]{msg} if !defined $cmd; - # when we're here, we can assume that $cmd contains a valid command - # and the arguments are syntactically valid - # handle login command - return $_[KERNEL]->yield(login => $c, @$arg) if $cmd eq 'login'; + return $_[KERNEL]->yield(login => $c, $arg) if $cmd eq 'login'; return cerr $c, needlogin => 'Not logged in.' if !$c->{username}; # update throttle array of the current user @@ -329,7 +326,10 @@ sub client_input { # handle get command if($cmd eq 'get') { - my $opt = $arg->[3]; + return cerr $c, parse => 'Invalid arguments to get command' if @$arg < 3 || @$arg > 4 + || ref($arg->[0]) || ref($arg->[1]) || ref($arg->[2]) ne 'POE::Filter::VNDBAPI::filter' + || exists($arg->[3]) && ref($arg->[3]) ne 'HASH'; + my $opt = $arg->[3] || {}; return cerr $c, badarg => 'Invalid argument for the "page" option', field => 'page' if defined($opt->{page}) && (ref($opt->{page}) || $opt->{page} !~ /^\d+$/ || $opt->{page} < 1); return cerr $c, badarg => '"reverse" option must be boolean', field => 'reverse' @@ -340,7 +340,7 @@ sub client_input { $opt->{reverse} = defined($opt->{reverse}) && $opt->{reverse}; my %obj = ( c => $c, - info => $arg->[1], + info => [ split /,/, $arg->[1] ], filters => $arg->[2], opt => $opt, ); @@ -357,6 +357,8 @@ sub login { my($c, $arg) = @_[ARG0,ARG1]; # validation (bah) + return cerr $c, parse => 'Argument to login must be a single JSON object' if @$arg != 1 || ref($arg->[0]) ne 'HASH'; + $arg = $arg->[0]; return cerr $c, loggedin => 'Already logged in, please reconnect to start a new session' if $c->{username}; for (qw|protocol client clientver username password|) { !exists $arg->{$_} && return cerr $c, missing => "Required field '$_' is missing", field => $_; diff --git a/lib/Multi/Anime.pm b/lib/Multi/Anime.pm index df2f6424..793cbe07 100644 --- a/lib/Multi/Anime.pm +++ b/lib/Multi/Anime.pm @@ -147,7 +147,6 @@ sub nextcmd { clientver => $_[HEAP]{clientver}, enc => 'UTF-8', ); - $_[KERNEL]->call(core => log => 'Authenticating with AniDB...'); } # logged in, get anime else { @@ -156,7 +155,6 @@ sub nextcmd { aid => $_[HEAP]{aid}, acode => 3973121, # aid, ANN id, NFO id, year, type, romaji, kanji ); - $_[KERNEL]->call(core => log => 'Fetching info for a%d', $_[HEAP]{aid}); } # send command @@ -179,17 +177,14 @@ sub receivepacket { # input, wheelid # parse message my @r = split /\n/, $_[ARG0]{payload}[0]; my($tag, $code, $msg) = ($1, $2, $3) if $r[0] =~ /^([0-9]+) ([0-9]+) (.+)$/; - - # log - $_[KERNEL]->call(core => log => 'Received from AniDB after %.2fs: %d %s', - time-$_[HEAP]{lm}, $code, $msg); + my $time = time-$_[HEAP]{lm}; # tag incorrect, ignore message - return $_[KERNEL]->call(core => log => 'Ignoring incorrect tag') + return $_[KERNEL]->call(core => log => 'Ignoring incorrect tag of message: %d %s', $code, $msg) if $tag != $_[HEAP]{tag}; # unhandled code, ignore as well - return $_[KERNEL]->call(core => log => 'Ignoring unhandled code') + return $_[KERNEL]->call(core => log => 'Ignoring unhandled code %d (%s)', $code, $msg) if !grep $_ == $code, @handled_codes; # at this point, we have a message we can handle, so disable the timeout @@ -201,7 +196,7 @@ sub receivepacket { # input, wheelid $_[HEAP]{tm}++; my $delay = $_[HEAP]{msgdelay}**(1 + $_[HEAP]{tm}*$_[HEAP]{timeoutdelay}); $delay = $_[HEAP]{maxtimeoutdelay} if $delay > $_[HEAP]{maxtimeoutdelay}; - $_[KERNEL]->call(core => log => 'Delaying %.0fs.', $delay); + $_[KERNEL]->call(core => log => 'Reply timed out, delaying %.0fs.', $delay); return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay}); } @@ -211,12 +206,14 @@ sub receivepacket { # input, wheelid # our session isn't valid, discard it and call nextcmd to get a new one if($code == NOT_LOGGED_IN || $code == LOGIN_FIRST || $code == INVALID_SESSION) { $_[HEAP]{s} = ''; + $_[KERNEL]->call(core => log => 'Our session was invalid, logging in again...'); return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay}); } # we received a session ID, call nextcmd again to fetch anime info if($code == LOGIN_ACCEPTED || $code == LOGIN_ACCEPTED_NEW_VER) { $_[HEAP]{s} = $1 if $msg =~ /^\s*([a-zA-Z0-9]{4,8}) /; + $_[KERNEL]->call(core => log => 'Successfully logged in to AniDB in %.2fs.', $time); return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay}); } @@ -244,7 +241,7 @@ sub receivepacket { # input, wheelid WHERE id = ?', [ @col, $_[HEAP]{aid} ] ); - $_[KERNEL]->call(core => log => 'Updated anime info for a%d', $_[HEAP]{aid}); + $_[KERNEL]->call(core => log => 'Fetched anime info for a%d in %.2fs', $_[HEAP]{aid}, $time); $_[KERNEL]->call(core => log => 'ERROR: a%d doesn\'t have a title or year!', $_[HEAP]{aid}) if !$col[3] || !$col[5]; } diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 0ea7ef29..864239dd 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -17,13 +17,13 @@ sub spawn { package_states => [ $p => [qw| _start shutdown set_daily daily set_monthly monthly log_stats - vncache tagcache vnpopularity vnrating cleangraphs - usercache statscache logrotate + vncache_inc tagcache vnpopularity vnrating cleangraphs + vncache_full usercache statscache logrotate |], ], heap => { - daily => [qw|vncache tagcache vnpopularity vnrating cleangraphs|], - monthly => [qw|usercache statscache logrotate|], + daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs|], + monthly => [qw|vncache_full usercache statscache logrotate|], @_, }, ); @@ -98,21 +98,31 @@ sub log_stats { # num, res, action, time # -sub vncache { - # this takes about 40s to complete. We really need to search for an alternative - # method of keeping the c_* columns in the vn table up-to-date. - $_[KERNEL]->post(pg => do => 'SELECT update_vncache(0)', undef, 'log_stats', 'vncache'); +sub vncache_inc { + # takes about 50ms to 1s to complete, depending on how many + # releases have been released within the past 5 days + $_[KERNEL]->post(pg => do => q| + SELECT update_vncache(id) + FROM ( + SELECT DISTINCT rv.vid + FROM releases r + JOIN releases_rev rr ON rr.id = r.latest + JOIN releases_vn rv ON rv.rid = r.latest + WHERE rr.released > TO_CHAR(NOW() - '5 days'::interval, 'YYYYMMDD')::integer + AND rr.released <= TO_CHAR(NOW(), 'YYYYMMDD')::integer + ) AS r(id) + |, undef, 'log_stats', 'vncache_inc'); } sub tagcache { - # takes about 18 seconds max. ouch, but still kind-of acceptable + # takes about 2 seconds max, still OK $_[KERNEL]->post(pg => do => 'SELECT tag_vn_calc()', undef, 'log_stats', 'tagcache'); } sub vnpopularity { - # still takes at most 2 seconds. let's hope that doesn't increase... + # still takes at most 3 seconds. let's hope that doesn't increase... $_[KERNEL]->post(pg => do => 'SELECT update_vnpopularity()', undef, 'log_stats', 'vnpopularity'); } @@ -146,6 +156,13 @@ sub cleangraphs { # +sub vncache_full { + # this takes more than a minute to complete, and should only be necessary in the + # event that the daily vncache_inc cron hasn't been running for 5 subsequent days. + $_[KERNEL]->post(pg => do => 'SELECT update_vncache(id) FROM vn', undef, 'log_stats', 'vncache_full'); +} + + sub usercache { # Shouldn't really be necessary, except c_changes could be slightly off when hiding/unhiding DB items # Currently takes about 25 seconds to complete. diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm index b3f9bb46..76408d92 100644 --- a/lib/Multi/RG.pm +++ b/lib/Multi/RG.pm @@ -158,7 +158,8 @@ sub savegraph { $attr{class} = 'border' if $attr{stroke} && $attr{stroke} eq '#111111'; $attr{class} = 'nodebg' if $attr{fill} && $attr{fill} eq '#222222'; - delete @attr{qw|stroke fill id xmlns xmlns:xlink|}; + delete @attr{qw|stroke fill xmlns xmlns:xlink|}; + delete $attr{id} if $attr{id} && $attr{id} !~ /^node_[vp]\d+$/; $el eq 'path' || $el eq 'polygon' ? $w->emptyTag("svg:$el", %attr) : $w->startTag("svg:$el", %attr); @@ -246,12 +247,12 @@ sub _vnnode { $tooltip =~ s/"/\\"/g; return sprintf - qq|\tv%d [ URL = "/v%d", tooltip = "%s", label=<|. + qq|\tv%d [ id = "node_v%1\$d", URL = "/v%1\$d", tooltip = "%s", label=<|. q|<TABLE CELLSPACING="0" CELLPADDING="1" BORDER="0" CELLBORDER="1" BGCOLOR="#222222">|. q|<TR><TD COLSPAN="2" ALIGN="CENTER" CELLPADDING="2"><FONT POINT-SIZE="%d"> %s </FONT></TD></TR>|. q|<TR><TD> %s </TD><TD> %s </TD></TR>|. qq|</TABLE>> ]\n|, - $_->{id}, $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($title), $date, $n->{lang}||'N/A'; + $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($title), $date, $n->{lang}||'N/A'; } @@ -298,12 +299,12 @@ sub _prodnode { $tooltip =~ s/"/\\"/g; return sprintf - qq|\tp%d [ URL = "/p%d", tooltip = "%s", label=<|. + qq|\tp%d [ id = "node_p%1\$d", URL = "/p%1\$d", tooltip = "%s", label=<|. q|<TABLE CELLSPACING="0" CELLPADDING="1" BORDER="0" CELLBORDER="1" BGCOLOR="#222222">|. q|<TR><TD COLSPAN="2" ALIGN="CENTER" CELLPADDING="2"><FONT POINT-SIZE="%d"> %s </FONT></TD></TR>|. q|<TR><TD ALIGN="CENTER"> $_lang_%s_$ </TD><TD ALIGN="CENTER"> $_ptype_%s_$ </TD></TR>|. qq|</TABLE>> ]\n|, - $_->{id}, $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($name), $n->{lang}, $n->{type}; + $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($name), $n->{lang}, $n->{type}; } diff --git a/lib/POE/Filter/VNDBAPI.pm b/lib/POE/Filter/VNDBAPI.pm index 24188a2f..816643d9 100644 --- a/lib/POE/Filter/VNDBAPI.pm +++ b/lib/POE/Filter/VNDBAPI.pm @@ -1,35 +1,24 @@ # Implements a POE::Filter for the VNDB API, and includes basic error checking # -# Currently recognised commands and their mapping between Perl and strings -# (this is just a simple overview, actual implementation is more advanced) +# Mapping between the request/response data and perl data structure: # -# C: login <json-object> -# [ 'login', {object} ] +# <command> -> [ '<command>' ] # -# C: get <type> <info> <filters> <options> -# [ 'get', <type>, <info>[ split ',', $2 ], [ filters ], { options } ] -# <type> must match /[a-z\/_]+/ -# <info> as string: /[a-z_]+(,[a-z_]+)*/, in perl: [ /[a-z_]+/, .. ] -# <options> is optional, must be JSON-object otherwise +# <command> <arg1> <arg2> .. -> [ 'command', <arg1>, <arg2>, .. ] # -# S: ok -# [ 'ok' ] +# <arg>: <JSON-text> | <filter> | <unescaped-string> # -# S: results <json-object> -# [ 'results', {object} ] +# <JSON-text>: JSON object or array -> perl object or array # -# S: error <json-object> -# [ 'error', {object} ] -# -# <filters>: +# <filter>: # string: ((<field> <op> <json-value>) <bool-op> (<field> <op> <json-value> )) -# perl: [ [ 'field', 'op', value ], 'bool-op', [ 'field', 'op', value ] ] +# perl: bless [ [ 'field', 'op', value ], 'bool-op', [ 'field', 'op', value ] ], 'POE::Filter::VNDBAPI::filter' # <field> must match /[a-z_]+/ # <op> must be one of =, !=, <, >, >=, <= or ~ # whitespace around fields/ops/json-values/bool-ops are ignored. # -# When type='server', put() will accept the objects marked by 'S' and get() will accept the strings marked by 'C' -# When type='client', put() will accept the objects marked by 'C' and get() will accept the strings marked by 'S' +# <unescaped-string>: Any string not starting with (, [ or { and not containing +# whitespace. In perl represented as a normal string. # # When invalid data is given to put(), ...don't do that, seriously. # When invalid data is given to get(), it will return the following arrayref: @@ -56,8 +45,6 @@ our @EXPORT_OK = qw|decode_filters encode_filters|; my $EOT = "\x04"; # End Of Transmission, this string is searched in the binary data using index() my $WS = qr/[\x20\x09\x0a\x0d]/; # witespace as defined by RFC4627 -my $GET_TYPE = qr/(?:[a-z\/_]+)/; # get <type> -my $GET_INFO = qr/(?:[a-z_]+(?:,[a-z_]+)*)/; # get <info> my $FILTER_FIELD = qr/(?:[a-z_]+)/; # <field> in the filters my $FILTER_OP = qr/(?:=|!=|<|>|>=|<=|~)/; # <op> in the filters my $FILTER_BOOL = qr/(?:and|or)/; # <boolean-op> in the filters @@ -65,19 +52,15 @@ my $FILTER_BOOL = qr/(?:and|or)/; # <boolean-op> in the filters sub new { my($class, %o) = @_; - my $type = ($o{type}||'') eq 'server' ? 'server' : 'client'; - return bless { - type => $type, - buffer => '' - }, $class; + my $b = ''; + return bless \$b, $class; } sub clone { my $self = shift; - return bless { - type => $self->{type}, - }, ref $self; + my $b = ''; + return bless \$b, ref $self; } @@ -98,13 +81,13 @@ sub get { sub get_one_start { my($self, $data) = @_; - $self->{buffer} .= join '', @$data; + $$self .= join '', @$data; } sub get_pending { my $self = shift; - return $self->{buffer} ne '' ? [ $self->{buffer} ] : undef; + return $$self ne '' ? [ $$self ] : undef; } @@ -113,53 +96,52 @@ sub _err($) { [ [ undef, { id => 'parse', msg => $_[0] } ] ] }; sub get_one { my $self = shift; # look for EOT - my $end = index $self->{buffer}, $EOT; + my $end = index $$self, $EOT; return [] if $end < 0; - my $str = substr $self->{buffer}, 0, $end; - $self->{buffer} = substr $self->{buffer}, $end+1; + my $str = substr $$self, 0, $end; + $$self = substr $$self, $end+1; # $str now contains our request/response encoded in UTF8, time to decode $str = eval { decode_utf8($str, Encode::FB_CROAK); }; return _err "Encoding error: $@" if !defined $str; - # C: login - # S: error, results - if($str =~ /^$WS*(login|error|results)$WS+(.+)$/s && ($self->{type} eq 'server' && $1 eq 'login' || $self->{type} eq 'client' && $1 ne 'login')) { - my($cmd, $json) = ($1, $2); - $json = eval { JSON::XS->new->decode($json) }; - if(!defined $json) { - my $err = $@; - $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//; - return _err "JSON-decode: $err"; - } - return _err qq|"$cmd" command requires a JSON object| if ref($json) ne 'HASH'; - return [[ $cmd, $json ]]; - } + # get command + return _err "Invalid command" if !($str =~ s/^$WS*([a-z]+)$WS*//); + my @ret = ($1); + + # parse arguments + while($str) { + $str =~ s/^$WS*//; - # C: get - if($self->{type} eq 'server' && $str =~ /^$WS*get$WS+($GET_TYPE)$WS+($GET_INFO)$WS+(.+)$/s) { - my($type, $info, $options) = ($1, $2, {}); - my($filters, $rest) = decode_filters($3); - return _err $filters if !ref $filters; - if($rest !~ /^$WS*$/) { - $options = eval { JSON::XS->new->decode($rest) }; - if(!defined $options) { + # JSON text, starts with { or [ + if($str =~ /^[\[{]/) { + my($value, $chars) = eval { JSON::XS->new->decode_prefix($str) }; + if(!defined $chars) { my $err = $@; $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//; - return _err "JSON-decode: $err"; + return _err "Invalid JSON value in filter expression: $err"; } - return _err 'options argument must be a JSON object' if ref($options) ne 'HASH'; + $str = substr $str, $chars; + push @ret, $value; + } + + # filter expression, starts with ( + elsif($str =~ /^\(/) { + my($value, $rest) = decode_filters($str); + return _err $value if !ref $value; + $str = $rest; + push @ret, bless $value, 'POE::Filter::VNDBAPI::filter'; } - return [[ 'get', $type, [ split /,/, $info ], $filters, $options ]]; - } - # S: ok - if($self->{type} eq 'client' && $str =~ /^$WS*ok$WS*$/) { - return [[ 'ok' ]]; + # otherwise it's an unescaped string + else { + my ($value, $rest) = split /$WS+/, $str, 2; + $str = $rest; + push @ret, $value; + } } - # if we're here, we've received something strange - return _err 'Invalid command or argument'; + return [ \@ret ]; } @@ -170,22 +152,13 @@ sub put { my @r; for my $p (@$cmds) { my $cmd = shift @$p; - - # C: login - push @r, 'login '.JSON::XS->new->encode($p->[0]) - if $self->{type} eq 'client' && $cmd eq 'login'; - - # C: get - push @r, sprintf 'get %s %s %s', $p->[0], join(',',@{$p->[1]}), encode_filters($p->[2]) - if $self->{type} eq 'client' && $cmd eq 'get'; - - # S: ok - push @r, 'ok' - if $self->{type} eq 'server' && $cmd eq 'ok'; - - # S: error, results - push @r, "$cmd ".JSON::XS->new->encode($p->[0]) - if $self->{type} eq 'server' && ($cmd eq 'error' || $cmd eq 'results'); + for (@$p) { + $cmd .= ' '.( + ref($_) eq 'POE::Filter::VNDBAPI::filter' ? encode_filters $_ : + ref($_) eq 'ARRAY' || ref($_) eq 'HASH' ? JSON::XS->new->encode($_) : $_ + ); + } + push @r, $cmd; } # the $EOT can also be passed through encode_utf8(), the result is the same. return [ map encode_utf8($_).$EOT, @r ]; diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm index 60487098..deeb7480 100644 --- a/lib/VNDB/DB/Discussions.pm +++ b/lib/VNDB/DB/Discussions.pm @@ -8,14 +8,14 @@ use Exporter 'import'; our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount dbPostRead|; -# Options: id, type, iid, results, page, what, notusers +# Options: id, type, iid, results, page, what, notusers, sort, reverse # What: boards, boardtitles, firstpost, lastpost +# Sort: id lastpost sub dbThreadGet { my($self, %o) = @_; $o{results} ||= 50; $o{page} ||= 1; $o{what} ||= ''; - $o{order} ||= 't.id DESC'; my %where = ( $o{id} ? ( @@ -49,13 +49,18 @@ sub dbThreadGet { 'JOIN threads_boards tb ON tb.tid = t.id' : (), ); + my $order = sprintf { + id => 't.id %s', + lastpost => 'tpl.date %s', + }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + my($r, $np) = $self->dbPage(\%o, q| SELECT !s FROM threads t !s !W ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $o{order} + join(', ', @select), join(' ', @join), \%where, $order ); if($o{what} =~ /(boards|boardtitles)/ && $#$r >= 0) { @@ -153,14 +158,13 @@ sub dbThreadCount { } -# Options: tid, num, what, order, uid, mindate, hide, page, results +# Options: tid, num, what, uid, mindate, hide, page, results # what: user thread sub dbPostGet { my($self, %o) = @_; $o{results} ||= 50; $o{page} ||= 1; $o{what} ||= ''; - $o{order} ||= 'tp.num ASC'; my %where = ( $o{tid} ? ( @@ -192,8 +196,8 @@ sub dbPostGet { FROM threads_posts tp !s !W - ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $o{order} + ORDER BY tp.num ASC|, + join(', ', @select), join(' ', @join), \%where ); return wantarray ? ($r, $np) : $r; diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index e819a1a3..02df0a2b 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -6,7 +6,7 @@ use warnings; use Exporter 'import'; our @EXPORT = qw| - dbStats dbRevisionInsert dbItemInsert dbRevisionGet dbItemMod dbRandomQuote + dbStats dbItemEdit dbRevisionGet dbItemMod dbRandomQuote |; @@ -20,60 +20,23 @@ sub dbStats { } -# Inserts a new revision and updates the item to point to this revision -# This function leaves the DB in an inconsistent state, the actual revision -# will have to be inserted directly after calling this function, otherwise -# the commit will fail. -# Arguments: type [vrp], item ID, edit summary -# Returns: local revision, global revision -sub dbRevisionInsert { - my($self, $type, $iid, $editsum, $uid) = @_; - - my $table = {qw|v vn r releases p producers|}->{$type}; - - my $c = $self->dbRow(q| - INSERT INTO changes (type, requester, ip, comments, rev) - VALUES (?, ?, ?, ?, ( - SELECT c.rev+1 - FROM changes c - JOIN !s_rev ir ON ir.id = c.id - WHERE ir.!sid = ? - ORDER BY c.id DESC - LIMIT 1 - )) - RETURNING id, rev|, - $type, $uid||$self->authInfo->{id}, $self->reqIP, $editsum, - $table, $type, $iid - ); - - $self->dbExec(q|UPDATE !s SET latest = ? WHERE id = ?|, $table, $c->{id}, $iid); +# Inserts a new revision into the database +# Arguments: type [vrp], revision id, %options->{ editsum uid + db[item]RevisionInsert } +# revision id = changes.id of the revision this edit is based on, undef to create a new DB item +# Returns: { iid, cid, rev } +sub dbItemEdit { + my($self, $type, $oid, %o) = @_; - return ($c->{rev}, $c->{id}); -} + my $fun = {qw|v vn r release p producer|}->{$type}; + $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid); + $self->dbExec('UPDATE edit_revision SET requester = ?, ip = ?, comments = ?', + $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum}); + $self->dbVNRevisionInsert( \%o) if $type eq 'v'; + $self->dbProducerRevisionInsert(\%o) if $type eq 'p'; + $self->dbReleaseRevisionInsert( \%o) if $type eq 'r'; -# Comparable to RevisionInsert, but creates a new item with a corresponding -# change. Same things about inconsistent state, etc. -# Argumments: type [vrp], edit summary, [uid] -# Returns: item id, global revision -sub dbItemInsert { - my($self, $type, $editsum, $uid) = @_; - - my $cid = $self->dbRow(q| - INSERT INTO changes (type, requester, ip, comments) - VALUES (?, ?, ?, ?) - RETURNING id|, - $type, $uid||$self->authInfo->{id}, $self->reqIP, $editsum - )->{id}; - - my $iid = $self->dbRow(q| - INSERT INTO !s (latest) - VALUES (?) - RETURNING id|, - {qw|v vn r releases p producers|}->{$type}, $cid - )->{id}; - - return ($iid, $cid); + return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun); } @@ -127,7 +90,7 @@ sub dbRevisionGet { ); my @select = ( - qw|c.id c.type c.requester c.comments c.rev c.causedby|, + qw|c.id c.type c.requester c.comments c.rev|, q|extract('epoch' from c.added) as added|, $o{what} =~ /user/ ? 'u.username' : (), $o{what} =~ /item/ ? ( diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index 0539634c..f32f70db 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -5,7 +5,7 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbProducerGet dbProducerEdit dbProducerAdd|; +our @EXPORT = qw|dbProducerGet dbProducerRevisionInsert|; # options: results, page, id, search, char, rev @@ -100,42 +100,23 @@ sub dbProducerGet { } -# arguments: id, %options ->( editsum uid + insert_rev ) -# returns: ( local revision, global revision ) -sub dbProducerEdit { - my($self, $pid, %o) = @_; - my($rev, $cid) = $self->dbRevisionInsert('p', $pid, $o{editsum}, $o{uid}); - insert_rev($self, $cid, $pid, \%o); - return ($rev, $cid); -} - - -# arguments: %options ->( editsum uid + insert_rev ) -# returns: ( item id, global revision ) -sub dbProducerAdd { - my($self, %o) = @_; - my($pid, $cid) = $self->dbItemInsert('p', $o{editsum}, $o{uid}); - insert_rev($self, $cid, $pid, \%o); - return ($pid, $cid); -} +# Updates the edit_* tables, used from dbItemEdit() +# Arguments: { columns in producers_rev + relations }, +sub dbProducerRevisionInsert { + my($self, $o) = @_; + my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), + qw|name original website l_wp type lang desc alias|; + $self->dbExec('UPDATE edit_producer !H', \%set) if keys %set; -# helper function, inserts a producer revision -# Arguments: global revision, item id, { columns in producers_rev }, relations -sub insert_rev { - my($self, $cid, $pid, $o) = @_; - $self->dbExec(q| - INSERT INTO producers_rev (id, pid, name, original, website, l_wp, type, lang, "desc", alias) - VALUES (!l)|, - [ $cid, $pid, @$o{qw| name original website l_wp type lang desc alias|} ] - ); - - $self->dbExec(q| - INSERT INTO producers_relations (pid1, pid2, relation) - VALUES (?, ?, ?)|, - $cid, $_->[1], $_->[0] - ) for (@{$o->{relations}}); + if($o->{relations}) { + $self->dbExec('DELETE FROM edit_producer_relations'); + my $q = join ',', map '(?,?)', @{$o->{relations}}; + my @q = map +($_->[1], $_->[0]), @{$o->{relations}}; + $self->dbExec("INSERT INTO edit_producer_relations (pid, relation) VALUES $q", @q) if @q; + } } 1; + diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index d7bddaab..a2f62a63 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -7,18 +7,18 @@ use POSIX 'strftime'; use Exporter 'import'; use VNDB::Func 'gtintype'; -our @EXPORT = qw|dbReleaseGet dbReleaseAdd dbReleaseEdit|; +our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|; -# Options: id vid rev order unreleased page results what date media +# Options: id vid rev unreleased page results what date media sort reverse # platforms languages type minage search resolutions freeware doujin # What: extended changes vn producers platforms media +# Sort: title released minage sub dbReleaseGet { my($self, %o) = @_; $o{results} ||= 50; $o{page} ||= 1; $o{what} ||= ''; - $o{order} ||= 'rr.released ASC'; my @where = ( !$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (), @@ -77,13 +77,19 @@ sub dbReleaseGet { (qw|c.requester c.comments r.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (), ); + my $order = sprintf { + title => 'rr.title %s', + minage => 'rr.minage %s', + released => 'rr.released %s', + }->{ $o{sort}||'released' }, $o{reverse} ? 'DESC' : 'ASC'; + my($r, $np) = $self->dbPage(\%o, q| SELECT !s FROM releases_rev rr !s !W ORDER BY !s|, - join(', ', @select), join(' ', @join), \@where, $o{order} + join(', ', @select), join(' ', @join), \@where, $order ); if(@$r) { @@ -150,67 +156,47 @@ sub dbReleaseGet { } -# arguments: id, %options ->( editsum uid + insert_rev ) -# returns: ( local revision, global revision ) -sub dbReleaseEdit { - my($self, $rid, %o) = @_; - my($rev, $cid) = $self->dbRevisionInsert('r', $rid, $o{editsum}, $o{uid}); - insert_rev($self, $cid, $rid, \%o); - return ($rev, $cid); -} +# Updates the edit_* tables, used from dbItemEdit() +# Arguments: { columns in releases_rev + languages + vn + producers + media + platforms } +sub dbReleaseRevisionInsert { + my($self, $o) = @_; + my %set = map exists($o->{$_}) ? ("$_ = ?", $o->{$_}) : (), + qw|title original gtin catalog website released notes minage type + patch resolution voiced freeware doujin ani_story ani_ero|; + $self->dbExec('UPDATE edit_release !H', \%set) if keys %set; -# arguments: %options ->( editsum uid + insert_rev ) -# returns: ( item id, global revision ) -sub dbReleaseAdd { - my($self, %o) = @_; - my($rid, $cid) = $self->dbItemInsert('r', $o{editsum}, $o{uid}); - insert_rev($self, $cid, $rid, \%o); - return ($rid, $cid); -} + if($o->{languages}) { + $self->dbExec('DELETE FROM edit_release_lang'); + my $q = join ',', map '(?)', @{$o->{languages}}; + $self->dbExec("INSERT INTO edit_release_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}}; + } + if($o->{producers}) { + $self->dbExec('DELETE FROM edit_release_producers'); + my $q = join ',', map '(?,?,?)', @{$o->{producers}}; + my @q = map +($_->[0], $_->[1]?1:0, $_->[2]?1:0), @{$o->{producers}}; + $self->dbExec("INSERT INTO edit_release_producers (pid, developer, publisher) VALUES $q", @q) if @q; + } -# helper function, inserts a producer revision -# Arguments: global revision, item id, { columns in releases_rev + languages + vn + producers + media + platforms } -sub insert_rev { - my($self, $cid, $rid, $o) = @_; - - $self->dbExec(q| - INSERT INTO releases_rev (id, rid, title, original, gtin, catalog, website, released, - notes, minage, type, patch, resolution, voiced, freeware, doujin, ani_story, ani_ero) - VALUES (!l)|, - [ $cid, $rid, @$o{qw| title original gtin catalog website released - notes minage type patch resolution voiced freeware doujin ani_story ani_ero|} ]); - - $self->dbExec(q| - INSERT INTO releases_lang (rid, lang) - VALUES (?, ?)|, - $cid, $_ - ) for (@{$o->{languages}}); - - $self->dbExec(q| - INSERT INTO releases_producers (rid, pid, developer, publisher) - VALUES (?, ?, ?, ?)|, - $cid, $_->[0], $_->[1]?1:0, $_->[2]?1:0 - ) for (@{$o->{producers}}); - - $self->dbExec(q| - INSERT INTO releases_platforms (rid, platform) - VALUES (?, ?)|, - $cid, $_ - ) for (@{$o->{platforms}}); - - $self->dbExec(q| - INSERT INTO releases_vn (rid, vid) - VALUES (?, ?)|, - $cid, $_ - ) for (@{$o->{vn}}); - - $self->dbExec(q| - INSERT INTO releases_media (rid, medium, qty) - VALUES (?, ?, ?)|, - $cid, $_->[0], $_->[1] - ) for (@{$o->{media}}); + if($o->{platforms}) { + $self->dbExec('DELETE FROM edit_release_platforms'); + my $q = join ',', map '(?)', @{$o->{platforms}}; + $self->dbExec("INSERT INTO edit_release_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}}; + } + + if($o->{vn}) { + $self->dbExec('DELETE FROM edit_release_vn'); + my $q = join ',', map '(?)', @{$o->{vn}}; + $self->dbExec("INSERT INTO edit_release_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}}; + } + + if($o->{media}) { + $self->dbExec('DELETE FROM edit_release_media'); + my $q = join ',', map '(?,?)', @{$o->{media}}; + my @q = map +($_->[0], $_->[1]), @{$o->{media}}; + $self->dbExec("INSERT INTO edit_release_media (medium, qty) VALUES $q", @q) if @q; + } } diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 8cf4ee29..478cd91c 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -5,15 +5,15 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats dbTagVNs|; +our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats|; -# %options->{ id noid name search state meta page results order what } +# %options->{ id noid name search state meta page results what sort reverse } # what: parents childs(n) aliases addedby +# sort: id name added vns sub dbTagGet { my $self = shift; my %o = ( - order => 't.id ASC', page => 1, results => 10, what => '', @@ -45,13 +45,20 @@ sub dbTagGet { ); my @join = $o{what} =~ /addedby/ ? 'JOIN users u ON u.id = t.addedby' : (); + my $order = sprintf { + id => 't.id %s', + name => 't.name %s', + added => 't.added %s', + vns => 't.c_vns %s', + }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC'; + my($r, $np) = $self->dbPage(\%o, q| SELECT !s FROM tags t !s !W ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $o{order} + join(', ', @select), join(' ', @join), \%where, $order ); if(@$r && $o{what} =~ /aliases/) { @@ -66,21 +73,46 @@ sub dbTagGet { } if($o{what} =~ /parents\((\d+)\)/) { - $_->{parents} = $self->dbTagTree($_->{id}, $1, 0) for(@$r); + $_->{parents} = $self->dbTagTree($_->{id}, $1, 1) for(@$r); } if($o{what} =~ /childs\((\d+)\)/) { - $_->{childs} = $self->dbTagTree($_->{id}, $1, 1) for(@$r); + $_->{childs} = $self->dbTagTree($_->{id}, $1) for(@$r); } return wantarray ? ($r, $np) : $r; } -# plain interface to the tag_tree() stored procedure in pgsql +# Walks the tag tree +# id = tag to start with, or 0 to start with top-level tags +# lvl = max. recursion level +# back = false for parent->child, true for child->parent +# Returns: [ { id, name, c_vns, sub => [ { id, name, c_vns, sub => [..] }, .. ] }, .. ] sub dbTagTree { - my($self, $id, $lvl, $dir) = @_; - return $self->dbAll('SELECT * FROM tag_tree(?, ?, ?)', $id, $lvl||0, $dir?1:0); + my($self, $id, $lvl, $back) = @_; + $lvl ||= 15; + my $r = $self->dbAll(q| + WITH RECURSIVE tagtree(lvl, id, parent, name, c_vns) AS ( + SELECT ?::integer, id, 0, name, c_vns + FROM tags + !W + UNION ALL + SELECT tt.lvl-1, t.id, tt.id, t.name, t.c_vns + FROM tagtree tt + JOIN tags_parents tp ON !s + JOIN tags t ON !s + WHERE tt.lvl > 0 + AND t.state = 2 + ) SELECT id, parent, name, c_vns FROM tagtree ORDER BY name|, $lvl, + $id ? {'id = ?' => $id} : {'NOT EXISTS(SELECT 1 FROM tags_parents WHERE tag = id)' => 1, 'state = 2' => 1}, + !$back ? ('tp.parent = tt.id', 't.id = tp.tag') : ('tp.tag = tt.id', 't.id = tp.parent') + ); + for my $i (@$r) { + $i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ]; + } + my @r = grep !delete($_->{parent}), @$r; + return $id ? $r[0]{'sub'} : \@r; } @@ -147,13 +179,13 @@ sub dbTagLinkEdit { # Fetch all tags related to a VN or User -# Argument: %options->{ uid vid minrating results what page order } +# Argument: %options->{ uid vid minrating results what page sort reverse } # what: vns +# sort: name, count, rating sub dbTagStats { my($self, %o) = @_; $o{results} ||= 10; $o{page} ||= 1; - $o{order} ||= 't.name ASC'; $o{what} ||= ''; my %where = ( @@ -162,6 +194,13 @@ sub dbTagStats { $o{vid} ? ( 'tv.vid = ?' => $o{vid} ) : (), ); + + my $order = sprintf { + name => 't.name %s', + count => 'count(*) %s', + rating => 'avg(tv.vote) %s', + }->{ $o{sort}||'name' }, $o{reverse} ? 'DESC' : 'ASC'; + my($r, $np) = $self->dbPage(\%o, q| SELECT t.id, t.name, count(*) as cnt, avg(tv.vote) as rating, COALESCE(avg(tv.spoiler), 0) as spoiler FROM tags t @@ -171,7 +210,7 @@ sub dbTagStats { !s ORDER BY !s|, \%where, defined $o{minrating} ? "HAVING avg(tv.vote) > $o{minrating}" : '', - $o{order} + $order ); if(@$r && $o{what} =~ /vns/ && $o{uid}) { @@ -196,31 +235,5 @@ sub dbTagStats { } -# Fetch all VNs from a tag, including VNs from child tags, and provide ratings for them. -# Argument: %options->{ tag order page results maxspoil } -sub dbTagVNs { - my($self, %o) = @_; - $o{order} ||= 'tb.rating DESC'; - $o{page} ||= 1; - $o{results} ||= 10; - - my %where = ( - 'tag = ?' => $o{tag}, - defined $o{maxspoil} ? ( - 'tb.spoiler <= ?' => $o{maxspoil} ) : (), - 'v.hidden = FALSE' => 1, - ); - - my($r, $np) = $self->dbPage(\%o, q| - SELECT tb.tag, tb.vid, tb.users, tb.rating, tb.spoiler, vr.title, vr.original, v.c_languages, v.c_released, v.c_platforms, v.c_popularity - FROM tags_vn_bayesian tb - JOIN vn v ON v.id = tb.vid - JOIN vn_rev vr ON vr.id = v.latest - !W - ORDER BY !s|, - \%where, $o{order}); - return wantarray ? ($r, $np) : $r; -} - 1; diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index 0f54686c..28c4d572 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -35,14 +35,14 @@ sub dbVNListGet { } -# %options->{ uid order char voted page results } +# %options->{ uid char voted page results sort reverse } +# sort: title vote # NOTE: this function is mostly copied from 1.x, may need some rewriting... sub dbVNListList { my($self, %o) = @_; $o{results} ||= 50; $o{page} ||= 1; - $o{order} ||= 'vr.title ASC'; $o{voted} ||= 0; # -1: only non-voted, 0: all, 1: only voted # construct the global WHERE clause @@ -58,6 +58,11 @@ sub dbVNListList { $where = '('.$where.') AND (ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' if defined $o{char} && !$o{char}; $where = '('.$where.') AND vo.vote IS NULL' if $o{voted} == -1; + my $order = sprintf { + title => 'vr.title %s', + vote => 'vo.vote %s NULLS LAST, vr.title ASC', + }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC'; + # execute query my($r, $np) = $self->dbPage(\%o, qq| SELECT vr.vid, vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms, COALESCE(vo.vote, 0) AS vote @@ -67,7 +72,7 @@ sub dbVNListList { WHERE $where ORDER BY !s|, $o{voted} == 1 ? '' : 'LEFT', $o{uid}, # JOIN if we only want votes, LEFT JOIN if we also want rlist items - $o{voted} != 1 ? $o{uid} : (), $o{order}, + $o{voted} != 1 ? $o{uid} : (), $order ); # fetch releases and link to VNs @@ -142,11 +147,10 @@ sub dbVNListDel { } -# %options->{ uid vid hide order results page what } +# %options->{ uid vid hide results page what } # what: user, vn sub dbVoteGet { my($self, %o) = @_; - $o{order} ||= 'n.date DESC'; $o{results} ||= 50; $o{page} ||= 1; $o{what} ||= ''; @@ -179,8 +183,8 @@ sub dbVoteGet { FROM votes n !s !W - ORDER BY !s|, - join(',', @select), join(' ', @join), \%where, $o{order} + ORDER BY n.date DESC|, + join(',', @select), join(' ', @join), \%where ); return wantarray ? ($r, $np) : $r; @@ -234,12 +238,12 @@ sub dbVoteDel { } -# %options->{ uid vid wstat what order page results } +# %options->{ uid vid wstat what page results sort reverse } # what: vn +# sort: title added wstat sub dbWishListGet { my($self, %o) = @_; - $o{order} ||= 'wl.wstat ASC'; $o{page} ||= 1; $o{results} ||= 50; $o{what} ||= ''; @@ -258,13 +262,19 @@ sub dbWishListGet { 'JOIN vn_rev vr ON vr.id = v.latest'; } + my $order = sprintf { + title => 'vr.title %s', + added => 'wl.added %s', + wstat => 'wl.wstat %2$s, vr.title ASC', + }->{ $o{sort}||'added' }, $o{reverse} ? 'DESC' : 'ASC', $o{reverse} ? 'ASC' : 'DESC'; + my($r, $np) = $self->dbPage(\%o, q| SELECT !s FROM wlists wl !s !W ORDER BY !s|, - $select, join(' ', @join), \%where, $o{order}, + $select, join(' ', @join), \%where, $order, ); return wantarray ? ($r, $np) : $r; diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index 39429a02..f8fdfe3f 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -8,12 +8,12 @@ use Exporter 'import'; our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|; -# %options->{ username passwd mail session order uid ip registered search results page what } +# %options->{ username passwd mail session uid ip registered search results page what sort reverse } # what: stats extended +# sort: username registered votes changes tags sub dbUserGet { my $s = shift; my %o = ( - order => 'username ASC', page => 1, results => 10, what => '', @@ -65,13 +65,21 @@ sub dbUserGet { $o{session} ? 'JOIN sessions s ON s.uid = u.id' : (), ); + my $order = sprintf { + username => 'u.username %s', + registered => 'u.registered %s', + votes => 'NOT u.show_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 !s !W ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $o{order} + join(', ', @select), join(' ', @join), \%where, $order ); return wantarray ? ($r, $np) : $r; } diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 250f5267..b8cc0c42 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -7,16 +7,16 @@ use Exporter 'import'; use VNDB::Func 'gtintype'; use Encode 'decode_utf8'; -our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|; +our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|; -# Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, order, what +# Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, what, sort, reverse # What: extended anime relations screenshots relgraph rating ranking changes +# Sort: id rel pop rating title tagscore rand sub dbVNGet { my($self, %o) = @_; $o{results} ||= 10; $o{page} ||= 1; - $o{order} ||= 'vr.title ASC'; $o{what} ||= ''; my %where = ( @@ -33,11 +33,11 @@ sub dbVNGet { $o{platform} && @{$o{platform}} ? ( '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (), $o{tags_include} && @{$o{tags_include}} ? ( - 'v.id IN(SELECT vid FROM tags_vn_bayesian WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)', + 'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)', [ $o{tags_include}[1], $o{tags_include}[0], $#{$o{tags_include}[1]}+1 ] ) : (), $o{tags_exclude} && @{$o{tags_exclude}} ? ( - 'v.id NOT IN(SELECT vid FROM tags_vn_bayesian WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (), + 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (), # don't fetch hidden items unless we ask for an ID !$o{id} && !$o{rev} ? ( 'v.hidden = FALSE' => 0 ) : (), @@ -86,24 +86,35 @@ sub dbVNGet { $o{what} =~ /extended/ ? ( qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (), $o{what} =~ /changes/ ? ( - qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (), + qw|c.requester c.comments v.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (), $o{what} =~ /ranking/ ? ( '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking', '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking', ) : (), + # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000) $tag_ids ? - qq|(SELECT AVG(tvb.rating) FROM tags_vn_bayesian tvb WHERE tvb.tag IN($tag_ids) AND tvb.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvb.vid) AS tagscore| : (), + qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvh.vid) AS tagscore| : (), ); + my $order = sprintf { + id => 'id %s', + rel => 'c_released %s', + pop => 'c_popularity %s NULLS LAST', + rating => 'c_rating %s NULLS LAST', + title => 'title %s', + tagscore => 'tagscore %s', + rand => 'RANDOM()', + }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC'; + my($r, $np) = $self->dbPage(\%o, q| SELECT !s FROM vn_rev vr !s !W - ORDER BY !s NULLS LAST|, - join(', ', @select), join(' ', @join), \%where, $o{order}, + ORDER BY !s|, + join(', ', @select), join(' ', @join), \%where, $order, ); if($o{what} =~ /relgraph/) { @@ -160,57 +171,38 @@ sub dbVNGet { } -# arguments: id, %options ->( editsum uid + insert_rev ) -# returns: ( local revision, global revision ) -sub dbVNEdit { - my($self, $id, %o) = @_; - my($rev, $cid) = $self->dbRevisionInsert('v', $id, $o{editsum}, $o{uid}); - insert_rev($self, $cid, $id, \%o); - return ($rev, $cid); -} - - -# arguments: %options ->( editsum uid + insert_rev ) -# returns: ( item id, global revision ) -sub dbVNAdd { - my($self, %o) = @_; - my($id, $cid) = $self->dbItemInsert('v', $o{editsum}, $o{uid}); - insert_rev($self, $cid, $id, \%o); - return ($id, $cid); -} - - -# helper function, inserts a producer revision -# Arguments: global revision, item id, { columns in producers_rev + anime + relations + screenshots } +# Updates the edit_* tables, used from dbItemEdit() +# Arguments: { columns in producers_rev + anime + relations + screenshots } # screenshots = [ [ scrid, nsfw, rid ], .. ] # relations = [ [ rel, vid ], .. ] # anime = [ aid, .. ] -sub insert_rev { - my($self, $cid, $vid, $o) = @_; - - $o->{img_nsfw} = $o->{img_nsfw}?1:0; - $self->dbExec(q| - INSERT INTO vn_rev (id, vid, title, original, "desc", alias, image, img_nsfw, length, l_wp, l_encubed, l_renai, l_vnn) - VALUES (!l)|, - [ $cid, $vid, @$o{qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|} ]); - - $self->dbExec(q| - INSERT INTO vn_screenshots (vid, scr, nsfw, rid) - VALUES (?, ?, ?, ?)|, - $cid, $_->[0], $_->[1]?1:0, $_->[2] - ) for (@{$o->{screenshots}}); - - $self->dbExec(q| - INSERT INTO vn_relations (vid1, vid2, relation) - VALUES (?, ?, ?)|, - $cid, $_->[1], $_->[0] - ) for (@{$o->{relations}}); - - $self->dbExec(q| - INSERT INTO vn_anime (vid, aid) - VALUES (?, ?)|, - $cid, $_ - ) for (@{$o->{anime}}); +sub dbVNRevisionInsert { + my($self, $o) = @_; + + $o->{img_nsfw} = $o->{img_nsfw}?1:0 if exists $o->{img_nsfw}; + my %set = map exists($o->{$_}) ? (qq|"$_" = ?| => $o->{$_}) : (), + qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|; + $self->dbExec('UPDATE edit_vn !H', \%set) if keys %set; + + if($o->{screenshots}) { + $self->dbExec('DELETE FROM edit_vn_screenshots'); + my $q = join ',', map '(?, ?, ?)', @{$o->{screenshots}}; + my @val = map +($_->[0], $_->[1]?1:0, $_->[2]), @{$o->{screenshots}}; + $self->dbExec("INSERT INTO edit_vn_screenshots (scr, nsfw, rid) VALUES $q", @val) if @val; + } + + if($o->{relations}) { + $self->dbExec('DELETE FROM edit_vn_relations'); + my $q = join ',', map '(?, ?)', @{$o->{relations}}; + my @val = map +($_->[1], $_->[0]), @{$o->{relations}}; + $self->dbExec("INSERT INTO edit_vn_relations (vid, relation) VALUES $q", @val) if @val; + } + + if($o->{anime}) { + $self->dbExec('DELETE FROM edit_vn_anime'); + my $q = join ',', map '(?)', @{$o->{anime}}; + $self->dbExec("INSERT INTO edit_vn_anime (aid) VALUES $q", @{$o->{anime}}) if @{$o->{anime}}; + } } @@ -220,13 +212,6 @@ sub dbVNImageId { } -# Updates the vn.c_ columns -sub dbVNCache { - my($self, @vn) = @_; - $self->dbExec('SELECT update_vncache(?)', $_) for (@vn); -} - - # insert a new screenshot and return it's ID # (no arguments required, as Multi is responsible for filling the entry with information) sub dbScreenshotAdd { diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm index 12b55029..fc477de2 100644 --- a/lib/VNDB/Handler/Discussions.pm +++ b/lib/VNDB/Handler/Discussions.pm @@ -295,7 +295,7 @@ sub board { results => 50, page => $f->{p}, what => 'firstpost lastpost boardtitles', - order => $type eq 'an' ? 't.id DESC' : 'tpl.date DESC', + sort => $type eq 'an' ? 'id' : 'lastpost', reverse => 1, ); $self->htmlHeader(title => $title, noindex => !@$list || $type eq 'u'); @@ -349,7 +349,7 @@ sub index { results => 5, page => 1, what => 'firstpost lastpost boardtitles', - order => 'tpl.date DESC', + sort => 'lastpost', reverse => 1, ); h1 class => 'boxtitle'; a href => "/t/$_", mt "_dboard_$_"; diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index d2f5852b..625b0463 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -78,7 +78,7 @@ sub homepage { # Announcements td; - my $an = $self->dbThreadGet(type => 'an', order => 't.id DESC', results => 2); + my $an = $self->dbThreadGet(type => 'an', sort => 'id', reverse => 1, results => 2); h1; a href => '/t/an', mt '_home_announcements'; end; @@ -98,7 +98,7 @@ sub homepage { h1; a href => '/t', mt '_home_recentposts'; end; - my $posts = $self->dbThreadGet(what => 'lastpost boardtitles', results => 10, order => 'tpl.date DESC', notusers => 1); + my $posts = $self->dbThreadGet(what => 'lastpost boardtitles', results => 10, sort => 'lastpost', reverse => 1, notusers => 1); ul; for (@$posts) { my $boards = join ', ', map mt("_dboard_$_->{type}").($_->{iid}?' > '.$_->{title}:''), @{$_->{boards}}; @@ -120,7 +120,7 @@ sub homepage { h1; a href => '/v/rand', mt '_home_randomvn'; end; - my $random = $self->dbVNGet(results => 10, order => 'RANDOM()'); + my $random = $self->dbVNGet(results => 10, sort => 'rand'); ul; for (@$random) { li; @@ -142,6 +142,7 @@ sub homepage { lit $self->{l10n}->datestr($_->{released}); txt ' '; cssicon $_, mt "_plat_$_" for (@{$_->{platforms}}); + cssicon "lang $_", mt "_lang_$_" for (@{$_->{languages}}); txt ' '; a href => "/r$_->{id}", title => $_->{original}||$_->{title}, shorten $_->{title}, 30; end; @@ -154,13 +155,14 @@ sub homepage { h1; a href => strftime('/r?ma=%Y%m%d;o=d;s=released', gmtime), mt '_home_justreleased'; end; - my $justrel = $self->dbReleaseGet(results => 10, order => 'rr.released DESC', unreleased => 0, what => 'platforms'); + my $justrel = $self->dbReleaseGet(results => 10, sort => 'released', reverse => 1, unreleased => 0, what => 'platforms'); ul; for (@$justrel) { li; lit $self->{l10n}->datestr($_->{released}); txt ' '; cssicon $_, mt "_plat_$_" for (@{$_->{platforms}}); + cssicon "lang $_", mt "_lang_$_" for (@{$_->{languages}}); txt ' '; a href => "/r$_->{id}", title => $_->{original}||$_->{title}, shorten $_->{title}, 30; end; @@ -263,7 +265,7 @@ sub history { } end; - $self->htmlHistory($list, $f, $np, $u->()); + $self->htmlBrowseHist($list, $f, $np, $u->()); $self->htmlFooter; } @@ -300,7 +302,7 @@ sub docpage { $ii; }eg; s{^:TOP5CONTRIB:$}{ - my $l = $self->dbUserGet(results => 6, order => 'c_changes DESC'); + my $l = $self->dbUserGet(results => 6, sort => 'changes', reverse => 1); '<dl>'.join('', map $_->{id} == 1 ? () : sprintf('<dt><a href="/u%d">%s</a></dt><dd>%d</dd>', $_->{id}, $_->{username}, $_->{c_changes}), @$l).'</dl>'; @@ -340,16 +342,11 @@ sub itemmod { return $self->htmlDenied if !$self->authCan($act eq 'hide' ? 'del' : 'lock'); my $obj = $type eq 'v' ? $self->dbVNGet(id => $iid)->[0] : - $type eq 'r' ? $self->dbReleaseGet(id => $iid, what => 'vn extended')->[0] : + $type eq 'r' ? $self->dbReleaseGet(id => $iid, what => 'extended')->[0] : $self->dbProducerGet(id => $iid, what => 'extended')->[0]; return 404 if !$obj->{id}; $self->dbItemMod($type, $iid, $act eq 'hide' ? (hidden => !$obj->{hidden}) : (locked => !$obj->{locked})); - - # update cached vn info when hiding an r+ page - $self->dbVNCache(map $_->{vid}, @{$obj->{vn}}) - if $type eq 'r' && $act eq 'hide'; - $self->resRedirect("/$type$iid", 'temp'); } diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index e3473c3c..064a06bc 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -26,6 +26,7 @@ sub rg { my $title = mt '_prodrg_title', $p->{name}; return if $self->htmlRGHeader($title, 'p', $p); + $p->{svg} =~ s/id="node_p$pid"/id="graph_current"/; $p->{svg} =~ s/\$___(_prodrel_[a-z]+)____\$/mt $1/eg; $p->{svg} =~ s/\$(_lang_[a-z]+)_\$/mt $1/eg; $p->{svg} =~ s/\$(_ptype_[a-z]+)_\$/mt $1/eg; @@ -63,14 +64,14 @@ sub page { [ lang => serialize => sub { "$_[0] (".mt("_lang_$_[0]").')' } ], [ website => diff => 1 ], [ l_wp => htmlize => sub { - $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink' # _vn? hmm... + $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink' }], [ desc => diff => 1 ], [ relations => join => '<br />', split => sub { my @r = map sprintf('%s: <a href="/p%d" title="%s">%s</a>', mt("_prodrel_$_->{relation}"), $_->{id}, xml_escape($_->{original}||$_->{name}), xml_escape shorten $_->{name}, 40 ), sort { $a->{id} <=> $b->{id} } @{$_[0]}; - return @r ? @r : (mt '_proddiff_none'); + return @r ? @r : (mt '_revision_empty'); }], ); } @@ -167,7 +168,7 @@ sub edit { { name => 'original', required => 0, maxlength => 200, default => '' }, { name => 'alias', required => 0, maxlength => 500, default => '' }, { name => 'lang', enum => $self->{languages} }, - { name => 'website', required => 0, template => 'url', default => '' }, + { name => 'website', required => 0, maxlength => 250, default => '', template => 'url' }, { name => 'l_wp', required => 0, maxlength => 150, default => '' }, { name => 'desc', required => 0, maxlength => 5000, default => '' }, { name => 'prodrelations', required => 0, maxlength => 5000, default => '' }, @@ -185,20 +186,16 @@ sub edit { $frm->{relations} = $relations; $frm->{l_wp} = undef if !$frm->{l_wp}; - $rev = 1; - my $npid = $pid; - my $cid; - ($rev, $cid) = $self->dbProducerEdit($pid, %$frm) if $pid; - ($npid, $cid) = $self->dbProducerAdd(%$frm) if !$pid; + my $nrev = $self->dbItemEdit(p => $pid ? $p->{cid} : undef, %$frm); # update reverse relations if(!$pid && $#$relations >= 0 || $pid && $frm->{prodrelations} ne $b4{prodrelations}) { my %old = $pid ? (map { $_->{id} => $_->{relation} } @{$p->{relations}}) : (); my %new = map { $_->[1] => $_->[0] } @$relations; - _updreverse($self, \%old, \%new, $npid, $cid, $rev); + _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev}); } - return $self->resRedirect("/p$npid.$rev", 'post'); + return $self->resRedirect("/p$nrev->{iid}.$nrev->{rev}", 'post'); } } @@ -256,10 +253,8 @@ sub edit { $self->htmlFooter; } -# !IMPORTANT!: Don't forget to update this function when -# adding/removing fields to/from producer entries! sub _updreverse { - my($self, $old, $new, $pid, $cid, $rev) = @_; + my($self, $old, $new, $pid, $rev) = @_; my %upd; # compare %old and %new @@ -270,20 +265,17 @@ sub _updreverse { $upd{$_} = $self->{prod_relations}{$$new{$_}}[1]; } } - return if !keys %upd; # edit all related producers for my $i (keys %upd) { - my $r = $self->dbProducerGet(id => $i, what => 'extended relations')->[0]; + my $r = $self->dbProducerGet(id => $i, what => 'relations')->[0]; my @newrel = map $_->{id} != $pid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}}; push @newrel, [ $upd{$i}, $pid ] if $upd{$i}; - $self->dbProducerEdit($i, + $self->dbItemEdit(p => $r->{cid}, relations => \@newrel, editsum => "Reverse relation update caused by revision p$pid.$rev", - causedby => $cid, - uid => 1, # Multi - hardcoded - ( map { $_ => $r->{$_} } qw|type name original lang website desc alias| ) + uid => 1, ); } } diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index daeffb9e..498b9fa9 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -40,13 +40,13 @@ sub page { map sprintf('<a href="/v%d" title="%s">%s</a>', $_->{vid}, $_->{original}||$_->{title}, shorten $_->{title}, 50), @{$_[0]}; } ], [ type => serialize => sub { mt "_rtype_$_[0]" } ], - [ patch => serialize => sub { $_[0] ? 'Patch' : 'Not a patch' } ], - [ freeware => serialize => sub { $_[0] ? 'yes' : 'nope' } ], - [ doujin => serialize => sub { $_[0] ? 'yups' : 'nope' } ], + [ patch => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ], + [ freeware => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ], + [ doujin => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ], [ title => diff => 1 ], [ original => diff => 1 ], - [ gtin => serialize => sub { $_[0]||'[none]' } ], - [ catalog => serialize => sub { $_[0]||'[none]' } ], + [ gtin => serialize => sub { $_[0]||mt '_revision_empty' } ], + [ catalog => serialize => sub { $_[0]||mt '_revision_empty' } ], [ languages => join => ', ', split => sub { map mt("_lang_$_"), @{$_[0]} } ], [ 'website' ], [ released => htmlize => sub { $self->{l10n}->datestr($_[0]) } ], @@ -310,7 +310,7 @@ sub edit { func => [ \>intype, 'Not a valid JAN/UPC/EAN code' ] }, { name => 'catalog', required => 0, default => '', maxlength => 50 }, { name => 'languages', multi => 1, enum => $self->{languages} }, - { name => 'website', required => 0, default => '', template => 'url' }, + { name => 'website', required => 0, default => '', maxlength => 250, template => 'url' }, { name => 'released', required => 0, default => 0, template => 'int' }, { name => 'minage' , required => 0, default => -1, enum => [map !defined($_)?-1:$_, @{$self->{age_ratings}}] }, { name => 'notes', required => 0, default => '', maxlength => 10240 }, @@ -348,7 +348,7 @@ sub edit { } if(!$frm->{_err}) { - my %opts = ( + my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef, (map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|), minage => $frm->{minage} < 0 ? undef : $frm->{minage}, @@ -357,13 +357,7 @@ sub edit { media => $media, ); - $rev = 1; - ($rev) = $self->dbReleaseEdit($rid, %opts) if !$copy && $rid; - ($rid) = $self->dbReleaseAdd(%opts) if $copy || !$rid; - - $self->dbVNCache(@$new_vn, map $_->{vid}, @$vn); - - return $self->resRedirect("/r$rid.$rev", 'post'); + return $self->resRedirect("/r$nrev->{iid}.$nrev->{rev}", 'post'); } } @@ -517,7 +511,7 @@ sub browse { $f->{do} ? (doujin => $f->{do}) : (), ); my($list, $np) = !@filters ? ([], 0) : $self->dbReleaseGet( - order => $f->{s}.($f->{o}eq'd'?' DESC':' ASC'), + sort => $f->{s}, reverse => $f->{o} eq 'd', page => $f->{p}, results => 50, what => 'platforms', diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index b28ba97a..df91a1c3 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -18,7 +18,6 @@ YAWF::register( qr{u([1-9]\d*)/tags}, \&usertags, qr{g}, \&tagindex, qr{xml/tags\.xml}, \&tagxml, - qr{g/debug}, \&tagtree, ); @@ -29,7 +28,7 @@ sub tagpage { return 404 if !$t; my $f = $self->formValidate( - { name => 's', required => 0, default => 'score', enum => [ qw|score title rel pop| ] }, + { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore rating| ] }, { name => 'o', required => 0, default => 'd', enum => [ 'a','d' ] }, { name => 'p', required => 0, default => 1, template => 'int' }, { name => 'm', required => 0, default => -1, enum => [qw|0 1 2|] }, @@ -38,12 +37,12 @@ sub tagpage { my $tagspoil = $self->reqCookie('tagspoil'); $f->{m} = $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0 if $f->{m} == -1; - my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbTagVNs( - tag => $tag, - order => {score=>'tb.rating',title=>'vr.title',rel=>'v.c_released',pop=>'v.c_popularity'}->{$f->{s}}.($f->{o}eq'a'?' ASC':' DESC'), - page => $f->{p}, + my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbVNGet( + what => 'rating', results => 50, - maxspoil => $f->{m}, + page => $f->{p}, + sort => $f->{s}, reverse => $f->{o} eq 'd', + tags_include => [ $f->{m}, [$tag ]], ); my $title = mt '_tagp_title', $t->{meta}?0:1, $t->{name}; @@ -86,7 +85,7 @@ sub tagpage { a href => '/g', mt '_tagp_indexlink'; for ($p[$_], reverse @r) { txt ' > '; - a href => "/g$_->{tag}", $_->{name}; + a href => "/g$_->{id}", $_->{name}; } txt " > $t->{name}\n"; } @@ -111,7 +110,20 @@ sub tagpage { end; _childtags($self, $t) if @{$t->{childs}}; - _vnlist($self, $t, $f, $list, $np) if !$t->{meta} && $t->{state} == 2; + + if(!$t->{meta} && $t->{state} == 2) { + div class => 'mainbox'; + h1 mt '_tagp_vnlist'; + p class => 'browseopts'; + a href => "/g$t->{id}?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0'; + a href => "/g$t->{id}?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1'; + a href => "/g$t->{id}?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2'; + end; + p "\n\n".mt '_tagp_novn' if !@$list; + p "\n".mt '_tagp_cached'; + end; + $self->htmlBrowseVN($list, $f, $np, "/g$t->{id}?m=$f->{m}", 1) if @$list; + } $self->htmlFooter; } @@ -120,38 +132,27 @@ sub tagpage { sub _childtags { my($self, $t, $index) = @_; - my @l = @{$t->{childs}}; - my @tags; - for (0..$#l) { - if($l[$_]{lvl} == $l[0]{lvl}) { - $l[$_]{childs} = []; - push @tags, $l[$_]; - } else { - push @{$tags[$#tags]{childs}}, $l[$_]; - } - } - div class => 'mainbox'; h1 mt $index ? '_tagp_tree' : '_tagp_childs'; ul class => 'tagtree'; - for my $p (sort { @{$b->{childs}} <=> @{$a->{childs}} } @tags) { + for my $p (sort { @{$b->{'sub'}} <=> @{$a->{'sub'}} } @{$t->{childs}}) { li; - a href => "/g$p->{tag}", $p->{name}; + a href => "/g$p->{id}", $p->{name}; b class => 'grayedout', " ($p->{c_vns})" if $p->{c_vns}; - end, next if !@{$p->{childs}}; + end, next if !@{$p->{'sub'}}; ul; - for (0..$#{$p->{childs}}) { - last if $_ >= 5 && @{$p->{childs}} > 6; + for (0..$#{$p->{'sub'}}) { + last if $_ >= 5 && @{$p->{'sub'}} > 6; li; txt '> '; - a href => "/g$p->{childs}[$_]{tag}", $p->{childs}[$_]{name}; - b class => 'grayedout', " ($p->{childs}[$_]{c_vns})" if $p->{childs}[$_]{c_vns}; + a href => "/g$p->{sub}[$_]{id}", $p->{'sub'}[$_]{name}; + b class => 'grayedout', " ($p->{sub}[$_]{c_vns})" if $p->{'sub'}[$_]{c_vns}; end; } - if(@{$p->{childs}} > 6) { + if(@{$p->{'sub'}} > 6) { li; txt '> '; - a href => "/g$p->{tag}", style => 'font-style: italic', mt '_tagp_moretags', @{$p->{childs}}-5; + a href => "/g$p->{id}", style => 'font-style: italic', mt '_tagp_moretags', @{$p->{'sub'}}-5; end; } end; @@ -163,63 +164,6 @@ sub _childtags { end; } -sub _vnlist { - my($self, $t, $f, $list, $np) = @_; - div class => 'mainbox'; - h1 mt '_tagp_vnlist'; - p class => 'browseopts'; - a href => "/g$t->{id}?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0'; - a href => "/g$t->{id}?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1'; - a href => "/g$t->{id}?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2'; - end; - if(!@$list) { - p "\n\n".mt '_tagp_novn'; - } - p "\n".mt '_tagp_cached'; - end; - return if !@$list; - $self->htmlBrowse( - class => 'tagvnlist', - items => $list, - options => $f, - nextpage => $np, - pageurl => "/g$t->{id}?m=$f->{m};o=$f->{o};s=$f->{s}", - sorturl => "/g$t->{id}?m=$f->{m}", - header => [ - [ mt('_tagp_vncol_score'), 'score' ], - [ mt('_tagp_vncol_title'), 'title' ], - [ '', 0 ], - [ '', 0 ], - [ mt('_tagp_vncol_rel'), 'rel' ], - [ mt('_tagp_vncol_pop'), 'pop' ], - ], - row => sub { - my($s, $n, $l) = @_; - Tr $n % 2 ? (class => 'odd') : (); - td class => 'tc1'; - tagscore $l->{rating}; - i sprintf '(%d)', $l->{users}; - end; - td class => 'tc2'; - a href => '/v'.$l->{vid}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100; - end; - td class => 'tc3'; - $_ ne 'oth' && cssicon $_, mt "_plat_$_" - for (sort split /\//, $l->{c_platforms}); - end; - td class => 'tc4'; - cssicon "lang $_", mt "_lang_$_" - for (reverse sort split /\//, $l->{c_languages}); - end; - td class => 'tc5'; - lit $self->{l10n}->datestr($l->{c_released}); - end; - td class => 'tc6', sprintf '%.2f', ($l->{c_popularity}||0)*100; - end; - } - ); -} - sub tagedit { my($self, $tag, $act) = @_; @@ -345,7 +289,7 @@ sub taglist { return 404 if $f->{_err}; my($t, $np) = $self->dbTagGet( - order => $f->{s}.($f->{o}eq'd'?' DESC':' ASC'), + sort => $f->{s}, reverse => $f->{o} eq 'd', page => $f->{p}, results => 50, state => $f->{t}, @@ -493,7 +437,7 @@ sub usertags { return 404 if !$u; my $f = $self->formValidate( - { name => 's', required => 0, default => 'cnt', enum => [ qw|cnt name| ] }, + { name => 's', required => 0, default => 'count', enum => [ qw|count name| ] }, { name => 'o', required => 0, default => 'd', enum => [ 'a','d' ] }, { name => 'p', required => 0, default => 1, template => 'int' }, ); @@ -503,7 +447,7 @@ sub usertags { my($list, $np) = $self->dbTagStats( uid => $uid, page => $f->{p}, - order => ($f->{s}eq'cnt'?'COUNT(*)':'name').($f->{o}eq'a'?' ASC':' DESC'), + sort => $f->{s}, reverse => $f->{o} eq 'd', what => 'vns', ); @@ -533,8 +477,8 @@ sub usertags { b id => 'expandall'; lit '<i>▸</i> '.mt('_tagu_col_num').' '; end; - lit $f->{s} eq 'cnt' && $f->{o} eq 'a' ? "\x{25B4}" : qq|<a href="/u$u->{id}/tags?o=a;s=cnt">\x{25B4}</a>|; - lit $f->{s} eq 'cnt' && $f->{o} eq 'd' ? "\x{25BE}" : qq|<a href="/u$u->{id}/tags?o=d;s=cnt">\x{25BE}</a>|; + lit $f->{s} eq 'count' && $f->{o} eq 'a' ? "\x{25B4}" : qq|<a href="/u$u->{id}/tags?o=a;s=count">\x{25B4}</a>|; + lit $f->{s} eq 'count' && $f->{o} eq 'd' ? "\x{25BE}" : qq|<a href="/u$u->{id}/tags?o=d;s=count">\x{25BE}</a>|; end; }, [ mt('_tagu_col_name'), 'name' ], @@ -580,7 +524,7 @@ sub tagindex { end; end; - my $t = $self->dbTagTree(0, 2, 1); + my $t = $self->dbTagTree(0, 2); _childtags($self, {childs => $t}, 1); table class => 'mainbox threelayout'; @@ -589,7 +533,7 @@ sub tagindex { # Recently added td; a class => 'right', href => '/g/list', mt '_tagidx_browseall'; - my $r = $self->dbTagGet(order => 'added DESC', results => 10, state => 2); + my $r = $self->dbTagGet(sort => 'added', reverse => 1, results => 10, state => 2); h1 mt '_tagidx_recent'; ul; for (@$r) { @@ -604,7 +548,7 @@ sub tagindex { # Popular td; - $r = $self->dbTagGet(order => 'c_vns DESC', meta => 0, results => 10); + $r = $self->dbTagGet(sort => 'vns', reverse => 1, meta => 0, results => 10); h1 mt '_tagidx_popular'; ul; for (@$r) { @@ -619,7 +563,7 @@ sub tagindex { # Moderation queue td; h1 mt '_tagidx_queue'; - $r = $self->dbTagGet(state => 0, order => 'added DESC', results => 10); + $r = $self->dbTagGet(state => 0, sort => 'added', reverse => 1, results => 10); ul; li mt '_tagidx_queue_empty' if !@$r; for (@$r) { @@ -667,32 +611,4 @@ sub tagxml { } -sub tagtree { - my $self = shift; - - return 404 if !$self->authCan('tagmod'); - - $self->htmlHeader(title => '[DEBUG] The complete tag tree'); - div class => 'mainbox'; - h1 '[DEBUG] The complete tag tree'; - - div style => 'margin-left: 10px'; - my $t = $self->dbTagTree(0, -1, 1); - my $lvl = $t->[0]{lvl} + 1; - for (@$t) { - map ul(style => 'margin-left: 15px; list-style-type: none'), 1..($lvl-$_->{lvl}) if $lvl > $_->{lvl}; - map end, 1..($_->{lvl}-$lvl) if $lvl < $_->{lvl}; - $lvl = $_->{lvl}; - li; - txt '> '; - a href => "/g$_->{tag}", $_->{name}; - end; - } - map end, 0..($t->[0]{lvl}-$lvl); - end; - end; - $self->htmlFooter; -} - - 1; diff --git a/lib/VNDB/Handler/ULists.pm b/lib/VNDB/Handler/ULists.pm index 6a0d6c9e..9d6885ad 100644 --- a/lib/VNDB/Handler/ULists.pm +++ b/lib/VNDB/Handler/ULists.pm @@ -122,7 +122,7 @@ sub wishlist { my($list, $np) = $self->dbWishListGet( uid => $uid, - order => $f->{s}.' '.($f->{o} eq 'a' ? ($f->{s} eq 'wstat' ? 'DESC' : 'ASC' ) : ($f->{s} eq 'wstat' ? 'ASC' : 'DESC')).($f->{s} eq 'wstat' ? ', title ASC' : ''), + sort => $f->{s}, reverse => $f->{o} eq 'd', $f->{f} != -1 ? (wstat => $f->{f}) : (), what => 'vn', results => 50, @@ -229,7 +229,7 @@ sub vnlist { uid => $uid, results => 50, page => $f->{p}, - order => $f->{s}.' '.($f->{o} eq 'd' ? 'DESC' : 'ASC').($f->{s} eq 'vote' ? ', title ASC' : ''), + sort => $f->{s}, reverse => $f->{o} eq 'd', voted => $f->{v}, $f->{c} ne 'all' ? (char => $f->{c}) : (), ); diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index bf89b32d..e9c97198 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -117,7 +117,7 @@ sub userpage { h1 class => 'boxtitle'; a href => "/u$uid/hist", mt '_userpage_changes'; end; - $self->htmlHistory($list, { p => 1 }, 0, "/u$uid/hist"); + $self->htmlBrowseHist($list, { p => 1 }, 0, "/u$uid/hist"); } $self->htmlFooter; } @@ -474,7 +474,7 @@ sub list { end; my($list, $np) = $self->dbUserGet( - order => ($f->{s} eq 'changes' || $f->{s} eq 'tags' ? 'c_' : $f->{s} eq 'votes' ? 'NOT show_list, c_' : '').$f->{s}.($f->{o} eq 'a' ? ' ASC' : ' DESC'), + sort => $f->{s}, reverse => $f->{o} eq 'd', $char ne 'all' ? ( firstchar => $char ) : (), results => 50, diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index dbe80ac1..43e8b3cb 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -55,21 +55,13 @@ sub list { $f->{s} = 'title' if !@ti && $f->{s} eq 'tagscore'; $f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o}; - my $sortcol = {qw| - rel c_released - pop c_popularity - rating c_rating - title title - tagscore tagscore - |}->{$f->{s}}; - my($list, $np) = $self->dbVNGet( what => 'rating', $char ne 'all' ? ( char => $char ) : (), $f->{q} ? ( search => $f->{q} ) : (), results => 50, page => $f->{p}, - order => $sortcol.($f->{o} eq 'a' ? ' ASC' : ' DESC'), + sort => $f->{s}, reverse => $f->{o} eq 'd', $f->{pl}[0] ? ( platform => $f->{pl} ) : (), $f->{ln}[0] ? ( lang => $f->{ln} ) : (), @ti ? (tags_include => [ $f->{sp}, \@ti ]) : (), @@ -85,52 +77,7 @@ sub list { my $url = "/v/$char?q=$f->{q};ti=$f->{ti};te=$f->{te}"; $_ and $url .= ";pl=$_" for @{$f->{pl}}; $_ and $url .= ";ln=$_" for @{$f->{ln}}; - $self->htmlBrowse( - class => 'vnbrowse', - items => $list, - options => $f, - nextpage => $np, - pageurl => "$url;o=$f->{o};s=$f->{s}", - sorturl => $url, - header => [ - @ti ? [ mt('_vnbrowse_col_score'), 'tagscore', undef, 'tc_s' ] : (), - [ mt('_vnbrowse_col_title'), 'title', undef, @ti ? 'tc_t' : 'tc1' ], - [ '', 0, undef, 'tc2' ], - [ '', 0, undef, 'tc3' ], - [ mt('_vnbrowse_col_released'), 'rel', undef, 'tc4' ], - [ mt('_vnbrowse_col_popularity'), 'pop', undef, 'tc5' ], - [ mt('_vnbrowse_col_rating'), 'rating', undef, 'tc6' ], - ], - row => sub { - my($s, $n, $l) = @_; - Tr $n % 2 ? (class => 'odd') : (); - if(@ti) { - td class => 'tc_s'; - tagscore $l->{tagscore}, 0; - end; - } - td class => @ti ? 'tc_t' : 'tc1'; - a href => '/v'.$l->{id}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100; - end; - td class => 'tc2'; - $_ ne 'oth' && cssicon $_, mt "_plat_$_" - for (sort split /\//, $l->{c_platforms}); - end; - td class => 'tc3'; - cssicon "lang $_", mt "_lang_$_" - for (reverse sort split /\//, $l->{c_languages}); - end; - td class => 'tc4'; - lit $self->{l10n}->datestr($l->{c_released}); - end; - td class => 'tc5', sprintf '%.2f', ($l->{c_popularity}||0)*100; - td class => 'tc6'; - txt sprintf '%.2f', $l->{c_rating}||0; - b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount}; - end; - end; - }, - ); + $self->htmlBrowseVN($list, $f, $np, $url, scalar @ti); $self->htmlFooter; } diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index ed7068dc..d5dfd878 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -69,8 +69,8 @@ sub edit { return $self->resRedirect("/v$vid", 'post') if $vid && !$self->reqUploadFileName('img') && !grep $frm->{$_} ne $b4{$_}, keys %b4; - # execute the edit/add - my %args = ( + # perform the edit/add + my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef, (map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw|), anime => [ keys %$anime ], relations => $relations, @@ -78,18 +78,14 @@ sub edit { screenshots => $screenshots, ); - my($nvid, $nrev, $cid) = ($vid, 1); - ($nrev, $cid) = $self->dbVNEdit($vid, %args) if $vid; - ($nvid, $cid) = $self->dbVNAdd(%args) if !$vid; - # update reverse relations & relation graph if(!$vid && $#$relations >= 0 || $vid && $frm->{vnrelations} ne $b4{vnrelations}) { my %old = $vid ? (map { $_->{id} => $_->{relation} } @{$v->{relations}}) : (); my %new = map { $_->[1] => $_->[0] } @$relations; - _updreverse($self, \%old, \%new, $nvid, $cid, $nrev); + _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev}); } - return $self->resRedirect("/v$nvid.$nrev", 'post'); + return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post'); } } @@ -236,14 +232,12 @@ sub _form { # Update reverse relations and regenerate relation graph -# Arguments: %old. %new, vid, cid, rev +# Arguments: %old. %new, vid, rev # %old,%new -> { vid2 => relation, .. } # from the perspective of vid -# cid, rev are of the related edit -# !IMPORTANT!: Don't forget to update this function when -# adding/removing fields to/from VN entries! +# rev is of the related edit sub _updreverse { - my($self, $old, $new, $vid, $cid, $rev) = @_; + my($self, $old, $new, $vid, $rev) = @_; my %upd; # compare %old and %new @@ -254,22 +248,17 @@ sub _updreverse { $upd{$_} = $self->{vn_relations}{$$new{$_}}[1]; } } - return if !keys %upd; # edit all related VNs for my $i (keys %upd) { - my $r = $self->dbVNGet(id => $i, what => 'extended relations anime screenshots')->[0]; + my $r = $self->dbVNGet(id => $i, what => 'relations')->[0]; my @newrel = map $_->{id} != $vid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}}; push @newrel, [ $upd{$i}, $vid ] if $upd{$i}; - $self->dbVNEdit($i, + $self->dbItemEdit(v => $r->{cid}, relations => \@newrel, editsum => "Reverse relation update caused by revision v$vid.$rev", - causedby => $cid, - uid => 1, # Multi - hardcoded - anime => [ map $_->{id}, @{$r->{anime}} ], - screenshots => [ map [ $_->{id}, $_->{nsfw}, $_->{rid} ], @{$r->{screenshots}} ], - ( map { $_ => $r->{$_} } qw| title original desc alias img_nsfw length l_wp l_encubed l_renai l_vnn image | ) + uid => 1, # Multi ); } } @@ -321,8 +310,10 @@ sub scrxml { } # upload new screenshot + my $num = $self->formValidate({name => 'upload', template => 'int'}); + return 404 if $num->{_err}; my $tmp = sprintf '%s/static/sf/00/tmp.%d.jpg', $VNDB::ROOT, $$*int(rand(1000)+1); - $self->reqSaveUpload('scr_upload', $tmp); + $self->reqSaveUpload("scr_upl_file_$num->{upload}", $tmp); my $id = 0; $id = -2 if !-s $tmp; diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 2a333337..6c2e9b79 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -16,7 +16,7 @@ YAWF::register( sub rand { my $self = shift; - $self->resRedirect('/v'.$self->dbVNGet(results => 1, order => 'RANDOM()')->[0]{id}, 'temp'); + $self->resRedirect('/v'.$self->dbVNGet(results => 1, sort => 'rand')->[0]{id}, 'temp'); } @@ -29,6 +29,7 @@ sub rg { my $title = mt '_vnrg_title', $v->{title}; return if $self->htmlRGHeader($title, 'v', $v); + $v->{svg} =~ s/id="node_v$vid"/id="graph_current"/; $v->{svg} =~ s/\$___(_vnrel_[a-z]+)____\$/mt $1/eg; div class => 'mainbox'; @@ -72,16 +73,18 @@ sub page { p mt '_vnpage_noimg'; } elsif($v->{image} < 0) { p mt '_vnpage_imgproc'; - } elsif($v->{img_nsfw} && !$self->authInfo->{show_nsfw}) { - img id => 'nsfw_hid', src => sprintf("%s/cv/%02d/%d.jpg", $self->{url_static}, $v->{image}%100, $v->{image}), alt => $v->{title}; - p id => 'nsfw_show'; - txt mt('_vnpage_imgnsfw_msg')."\n\n"; - a href => '#', mt '_vnpage_imgnsfw_show'; - txt "\n\n".mt '_vnpage_imgnsfw_note'; - end; } else { - img src => sprintf("%s/cv/%02d/%d.jpg", $self->{url_static}, $v->{image}%100, $v->{image}), alt => $v->{title}; - i mt '_vnpage_imgnsfw_foot' if $v->{img_nsfw} && $self->authInfo->{show_nsfw}; + p $v->{img_nsfw} ? (id => 'nsfw_hid', style => $self->authInfo->{show_nsfw} ? 'display: block' : '') : (); + img src => sprintf("%s/cv/%02d/%d.jpg", $self->{url_static}, $v->{image}%100, $v->{image}), alt => $v->{title}; + i mt '_vnpage_imgnsfw_foot' if $v->{img_nsfw}; + end; + if($v->{img_nsfw}) { + p id => 'nsfw_show', $self->authInfo->{show_nsfw} ? (style => 'display: none') : (); + txt mt('_vnpage_imgnsfw_msg')."\n\n"; + a href => '#', mt '_vnpage_imgnsfw_show'; + txt "\n\n".mt '_vnpage_imgnsfw_note'; + end; + } } end; @@ -146,7 +149,7 @@ sub page { clearfloat; # tags - my $t = $self->dbTagStats(vid => $v->{id}, order => 'avg(tv.vote) DESC', minrating => 0, results => 999); + my $t = $self->dbTagStats(vid => $v->{id}, sort => 'rating', reverse => 1, minrating => 0, results => 999); if(@$t) { div id => 'tagops'; # NOTE: order of these links is hardcoded in JS @@ -191,40 +194,41 @@ sub _revision { [ desc => diff => 1 ], [ length => serialize => sub { mt '_vnlength_'.$_[0] } ], [ l_wp => htmlize => sub { - $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink' + $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink' }], [ l_encubed => htmlize => sub { - $_[0] ? sprintf '<a href="http://novelnews.net/tag/%s/">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink' + $_[0] ? sprintf '<a href="http://novelnews.net/tag/%s/">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink' }], [ l_renai => htmlize => sub { - $_[0] ? sprintf '<a href="http://renai.us/game/%s.shtml">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink' + $_[0] ? sprintf '<a href="http://renai.us/game/%s.shtml">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink' }], [ relations => join => '<br />', split => sub { my @r = map sprintf('%s: <a href="/v%d" title="%s">%s</a>', mt("_vnrel_$_->{relation}"), $_->{id}, xml_escape($_->{original}||$_->{title}), xml_escape shorten $_->{title}, 40 ), sort { $a->{id} <=> $b->{id} } @{$_[0]}; - return @r ? @r : (mt '_vndiff_none'); + return @r ? @r : (mt '_revision_empty'); }], [ anime => join => ', ', split => sub { my @r = map sprintf('<a href="http://anidb.net/a%d">a%1$d</a>', $_->{id}), sort { $a->{id} <=> $b->{id} } @{$_[0]}; - return @r ? @r : (mt '_vndiff_none'); + return @r ? @r : (mt '_revision_empty'); }], [ screenshots => join => '<br />', split => sub { my @r = map sprintf('[%s] <a href="%s/sf/%02d/%d.jpg" rel="iv:%dx%d">%4$d</a> (%s)', $_->{rid} ? qq|<a href="/r$_->{rid}">r$_->{rid}</a>| : 'no release', - $self->{url_static}, $_->{id}%100, $_->{id}, $_->{width}, $_->{height}, $_->{nsfw} ? 'NSFW' : 'Safe' + $self->{url_static}, $_->{id}%100, $_->{id}, $_->{width}, $_->{height}, + mt($_->{nsfw} ? '_vndiff_nsfw_notsafe' : '_vndiff_nsfw_safe') ), @{$_[0]}; - return @r ? @r : (mt '_vndiff_none'); + return @r ? @r : (mt '_revision_empty'); }], [ image => htmlize => sub { my $url = sprintf "%s/cv/%02d/%d.jpg", $self->{url_static}, $_[0]%100, $_[0]; if($_[0] > 0) { - return $_[1]->{img_nsfw} && !$self->authInfo->{show_nsfw} ? "<a href=\"$url\">(NSFW)</a>" : "<img src=\"$url\" />"; + return $_[1]->{img_nsfw} && !$self->authInfo->{show_nsfw} ? "<a href=\"$url\">".mt('_vndiff_image_nsfw').'</a>' : "<img src=\"$url\" />"; } else { - return $_[0] < 0 ? '[processing]' : 'No image'; + return mt $_[0] < 0 ? '_vndiff_image_proc' : '_vndiff_image_none'; } }], - [ img_nsfw => serialize => sub { $_[0] ? 'Not safe' : 'Safe' } ], + [ img_nsfw => serialize => sub { mt $_[0] ? '_vndiff_nsfw_notsafe' : '_vndiff_nsfw_safe' } ], ); } diff --git a/lib/VNDB/L10N.pm b/lib/VNDB/L10N.pm index 8698cb74..9b9d2a82 100644 --- a/lib/VNDB/L10N.pm +++ b/lib/VNDB/L10N.pm @@ -73,12 +73,10 @@ use warnings; } # argument: unix timestamp and optional format (compact/full) - # return value: yyyy-mm-dd - # (maybe an idea to use cgit-style ages for recent timestamps) sub date { my($s, $t, $f) = @_; - return strftime '%Y-%m-%d', gmtime $t if !$f || $f eq 'compact'; - return strftime '%Y-%m-%d at %R', gmtime $t; + return strftime $s->maketext('_datetime_compact'), gmtime $t if !$f || $f eq 'compact'; + return strftime $s->maketext('_datetime_full'), gmtime $t; } # argument: database release date format (yyyymmdd) diff --git a/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm index 0d510aa9..4859ea67 100644 --- a/lib/VNDB/Plugin/TransAdmin.pm +++ b/lib/VNDB/Plugin/TransAdmin.pm @@ -137,7 +137,7 @@ sub _readlang { my $t = shift @$l; if($t eq 'space') { - if(join("\n", @$l) =~ /((#{30,90}\n)## +(.+) +##\n\2.+)^/ms) { + if(join("\n", @$l) =~ /((#{30,90}\n)## +(.+) +##\n\2.+)/ms) { my $header = $1; (my $title = $3) =~ s/\s+$//; $title =~ s/\s+\([^)]+\)$//; diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm new file mode 100644 index 00000000..e9812f36 --- /dev/null +++ b/lib/VNDB/Util/BrowseHTML.pm @@ -0,0 +1,204 @@ + +package VNDB::Util::BrowseHTML; + +use strict; +use warnings; +use YAWF ':html', 'xml_escape'; +use Exporter 'import'; +use VNDB::Func; + + +our @EXPORT = qw| htmlBrowse htmlBrowseNavigate htmlBrowseHist htmlBrowseVN |; + + +# generates a browse box, arguments: +# items => arrayref with the list items +# options => hashref containing at least the keys s (sort key), o (order) and p (page) +# nextpage => whether there's a next page or not +# sorturl => base URL to append the sort options to (if there are any sortable columns) +# pageurl => base URL to append the page option to +# class => classname of the mainbox +# header => +# can be either an arrayref or subroutine reference, +# in the case of a subroutine, it will be called when the header should be written, +# in the case of an arrayref, the array should contain the header items. Each item +# can again be either an arrayref or subroutine ref. The arrayref would consist of +# two elements: the name of the header, and the name of the sorting column if it can +# be sorted +# row => subroutine ref, which is called for each item in $list, arguments will be +# $self, $item_number (starting from 0), $item_value +# footer => subroutine ref, called after all rows have been processed +sub htmlBrowse { + my($self, %opt) = @_; + + $opt{sorturl} .= $opt{sorturl} =~ /\?/ ? ';' : '?' if $opt{sorturl}; + + # top navigation + $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 't'); + + div class => 'mainbox browse'.($opt{class} ? ' '.$opt{class} : ''); + table; + + # header + thead; + Tr; + if(ref $opt{header} eq 'CODE') { + $opt{header}->($self); + } else { + for(0..$#{$opt{header}}) { + if(ref $opt{header}[$_] eq 'CODE') { + $opt{header}[$_]->($self, $_+1); + } else { + td class => $opt{header}[$_][3]||'tc'.($_+1), $opt{header}[$_][2] ? (colspan => $opt{header}[$_][2]) : (); + lit $opt{header}[$_][0]; + if($opt{header}[$_][1]) { + lit ' '; + lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'a' ? "\x{25B4}" : qq|<a href="$opt{sorturl}o=a;s=$opt{header}[$_][1]">\x{25B4}</a>|; + lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'd' ? "\x{25BE}" : qq|<a href="$opt{sorturl}o=d;s=$opt{header}[$_][1]">\x{25BE}</a>|; + } + end; + } + } + } + end; + end; + + # footer + if($opt{footer}) { + tfoot; + $opt{footer}->($self); + end; + } + + # rows + $opt{row}->($self, $_+1, $opt{items}[$_]) + for 0..$#{$opt{items}}; + + end; + end; + + # bottom navigation + $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 'b'); +} + + +# creates next/previous buttons (tabs), if needed +# Arguments: page url, current page (1..n), nextpage (0/1), alignment (t/b), noappend (0/1) +sub htmlBrowseNavigate { + my($self, $url, $p, $np, $al, $na) = @_; + return if $p == 1 && !$np; + + $url .= $url =~ /\?/ ? ';p=' : '?p=' unless $na; + ul class => 'maintabs ' . ($al eq 't' ? 'notfirst' : 'bottom'); + if($p > 1) { + li class => 'left'; + a href => $url.($p-1), '<- '.mt '_browse_previous'; + end; + } + if($np) { + li; + a href => $url.($p+1), mt('_browse_next').' ->'; + end; + } + end; +} + + +sub htmlBrowseHist { + my($self, $list, $f, $np, $url) = @_; + $self->htmlBrowse( + items => $list, + options => $f, + nextpage => $np, + pageurl => $url, + class => 'history', + header => [ + sub { td colspan => 2, class => 'tc1', mt '_hist_col_rev' }, + [ mt '_hist_col_date' ], + [ mt '_hist_col_user' ], + sub { td; a href => '#', id => 'expandlist', mt '_js_expand'; txt mt '_hist_col_page'; end; } + ], + row => sub { + my($s, $n, $i) = @_; + my $revurl = "/$i->{type}$i->{iid}.$i->{rev}"; + + Tr $n % 2 ? ( class => 'odd' ) : (); + td class => 'tc1_1'; + a href => $revurl, "$i->{type}$i->{iid}"; + end; + td class => 'tc1_2'; + a href => $revurl, ".$i->{rev}"; + end; + td class => 'tc2', $self->{l10n}->date($i->{added}); + td class => 'tc3'; + lit $self->{l10n}->userstr($i); + end; + td; + a href => $revurl, title => $i->{ioriginal}, shorten $i->{ititle}, 80; + end; + end; + if($i->{comments}) { + Tr class => $n % 2 ? 'collapse msgsum odd hidden' : 'collapse msgsum hidden'; + td colspan => 5; + lit bb2html $i->{comments}, 150; + end; + end; + } + }, + ); +} + + +sub htmlBrowseVN { + my($self, $list, $f, $np, $url, $tagscore) = @_; + $self->htmlBrowse( + class => 'vnbrowse', + items => $list, + options => $f, + nextpage => $np, + pageurl => "$url;o=$f->{o};s=$f->{s}", + sorturl => $url, + header => [ + $tagscore ? [ mt('_vnbrowse_col_score'), 'tagscore', undef, 'tc_s' ] : (), + [ mt('_vnbrowse_col_title'), 'title', undef, $tagscore ? 'tc_t' : 'tc1' ], + [ '', 0, undef, 'tc2' ], + [ '', 0, undef, 'tc3' ], + [ mt('_vnbrowse_col_released'), 'rel', undef, 'tc4' ], + [ mt('_vnbrowse_col_popularity'), 'pop', undef, 'tc5' ], + [ mt('_vnbrowse_col_rating'), 'rating', undef, 'tc6' ], + ], + row => sub { + my($s, $n, $l) = @_; + Tr $n % 2 ? (class => 'odd') : (); + if($tagscore) { + td class => 'tc_s'; + tagscore $l->{tagscore}, 0; + end; + } + td class => $tagscore ? 'tc_t' : 'tc1'; + a href => '/v'.$l->{id}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100; + end; + td class => 'tc2'; + $_ ne 'oth' && cssicon $_, mt "_plat_$_" + for (sort split /\//, $l->{c_platforms}); + end; + td class => 'tc3'; + cssicon "lang $_", mt "_lang_$_" + for (reverse sort split /\//, $l->{c_languages}); + end; + td class => 'tc4'; + lit $self->{l10n}->datestr($l->{c_released}); + end; + td class => 'tc5', sprintf '%.2f', ($l->{c_popularity}||0)*100; + td class => 'tc6'; + txt sprintf '%.2f', $l->{c_rating}||0; + b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount}; + end; + end; + }, + ); +} + + +1; + diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index f39ffaaa..79330272 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -11,8 +11,8 @@ use Encode 'encode_utf8', 'decode_utf8'; use POSIX 'ceil'; our @EXPORT = qw| - htmlMainTabs htmlDenied htmlHiddenMessage htmlBrowse htmlBrowseNavigate - htmlRevision htmlEditMessage htmlItemMessage htmlVoteStats htmlHistory htmlSearchBox htmlRGHeader + htmlMainTabs htmlDenied htmlHiddenMessage htmlRevision + htmlEditMessage htmlItemMessage htmlVoteStats htmlSearchBox htmlRGHeader |; @@ -155,99 +155,6 @@ sub htmlHiddenMessage { } -# generates a browse box, arguments: -# items => arrayref with the list items -# options => hashref containing at least the keys s (sort key), o (order) and p (page) -# nextpage => whether there's a next page or not -# sorturl => base URL to append the sort options to (if there are any sortable columns) -# pageurl => base URL to append the page option to -# class => classname of the mainbox -# header => -# can be either an arrayref or subroutine reference, -# in the case of a subroutine, it will be called when the header should be written, -# in the case of an arrayref, the array should contain the header items. Each item -# can again be either an arrayref or subroutine ref. The arrayref would consist of -# two elements: the name of the header, and the name of the sorting column if it can -# be sorted -# row => subroutine ref, which is called for each item in $list, arguments will be -# $self, $item_number (starting from 0), $item_value -# footer => subroutine ref, called after all rows have been processed -sub htmlBrowse { - my($self, %opt) = @_; - - $opt{sorturl} .= $opt{sorturl} =~ /\?/ ? ';' : '?' if $opt{sorturl}; - - # top navigation - $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 't'); - - div class => 'mainbox browse'.($opt{class} ? ' '.$opt{class} : ''); - table; - - # header - thead; - Tr; - if(ref $opt{header} eq 'CODE') { - $opt{header}->($self); - } else { - for(0..$#{$opt{header}}) { - if(ref $opt{header}[$_] eq 'CODE') { - $opt{header}[$_]->($self, $_+1); - } else { - td class => $opt{header}[$_][3]||'tc'.($_+1), $opt{header}[$_][2] ? (colspan => $opt{header}[$_][2]) : (); - lit $opt{header}[$_][0]; - if($opt{header}[$_][1]) { - lit ' '; - lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'a' ? "\x{25B4}" : qq|<a href="$opt{sorturl}o=a;s=$opt{header}[$_][1]">\x{25B4}</a>|; - lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'd' ? "\x{25BE}" : qq|<a href="$opt{sorturl}o=d;s=$opt{header}[$_][1]">\x{25BE}</a>|; - } - end; - } - } - } - end; - end; - - # footer - if($opt{footer}) { - tfoot; - $opt{footer}->($self); - end; - } - - # rows - $opt{row}->($self, $_+1, $opt{items}[$_]) - for 0..$#{$opt{items}}; - - end; - end; - - # bottom navigation - $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 'b'); -} - - -# creates next/previous buttons (tabs), if needed -# Arguments: page url, current page (1..n), nextpage (0/1), alignment (t/b), noappend (0/1) -sub htmlBrowseNavigate { - my($self, $url, $p, $np, $al, $na) = @_; - return if $p == 1 && !$np; - - $url .= $url =~ /\?/ ? ';p=' : '?p=' unless $na; - ul class => 'maintabs ' . ($al eq 't' ? 'notfirst' : 'bottom'); - if($p > 1) { - li class => 'left'; - a href => $url.($p-1), '<- '.mt '_browse_previous'; - end; - } - if($np) { - li; - a href => $url.($p+1), mt('_browse_next').' ->'; - end; - } - end; -} - - # Shows a revision, including diff if there is a previous revision. # Arguments: v|p|r, old revision, new revision, @fields # Where @fields is a list of fields as arrayrefs with: @@ -341,8 +248,8 @@ sub revdiff { # $i % 2 == 0 -> equal, otherwise it's different my $a = join($o{join}, @ser1[ $d[$i*2] .. $d[$i*2+2]-1 ]); my $b = join($o{join}, @ser2[ $d[$i*2+1] .. $d[$i*2+3]-1 ]); - $ser1 .= ($ser1?$o{join}:'').($i % 2 ? qq|<b class="diff_del">$a</b>| : $a) if $a; - $ser2 .= ($ser2?$o{join}:'').($i % 2 ? qq|<b class="diff_add">$b</b>| : $b) if $b; + $ser1 .= ($ser1?$o{join}:'').($i % 2 ? qq|<b class="diff_del">$a</b>| : $a) if $a ne ''; + $ser2 .= ($ser2?$o{join}:'').($i % 2 ? qq|<b class="diff_add">$b</b>| : $b) if $b ne ''; } $ser1 = decode_utf8($ser1); $ser2 = decode_utf8($ser2); @@ -351,8 +258,8 @@ sub revdiff { $ser2 = xml_escape $ser2; } - $ser1 = mt '_revision_emptyfield' if !$ser1 && $ser1 ne '0'; - $ser2 = mt '_revision_emptyfield' if !$ser2 && $ser2 ne '0'; + $ser1 = mt '_revision_empty' if !$ser1 && $ser1 ne '0'; + $ser2 = mt '_revision_empty' if !$ser2 && $ser2 ne '0'; Tr $$i++ % 2 ? (class => 'odd') : (); td mt "_revfield_${type}_$short"; @@ -375,7 +282,7 @@ sub htmlEditMessage { div class => 'warning'; h2 mt '_editmsg_copy_title'; p; - lit mt '_editmsg_copy_msg', sprintf '<a href="/%s%d">%s</a>', $type, $obj->{id}, xml_escape $obj->{title}, + lit mt '_editmsg_copy_msg', sprintf '<a href="/%s%d">%s</a>', $type, $obj->{id}, xml_escape $obj->{title}; end; end; } @@ -452,7 +359,6 @@ sub htmlVoteStats { my $recent = $self->dbVoteGet( $type.'id' => $obj->{id}, results => 8, - order => 'date DESC', what => $type eq 'v' ? 'user' : 'vn', hide => $type eq 'v', hide_ign => $type eq 'v', @@ -490,51 +396,6 @@ sub htmlVoteStats { } -sub htmlHistory { - my($self, $list, $f, $np, $url) = @_; - $self->htmlBrowse( - items => $list, - options => $f, - nextpage => $np, - pageurl => $url, - class => 'history', - header => [ - sub { td colspan => 2, class => 'tc1', mt '_hist_col_rev' }, - [ mt '_hist_col_date' ], - [ mt '_hist_col_user' ], - sub { td; a href => '#', id => 'expandlist', mt '_js_expand'; txt mt '_hist_col_page'; end; } - ], - row => sub { - my($s, $n, $i) = @_; - my $revurl = "/$i->{type}$i->{iid}.$i->{rev}"; - - Tr $n % 2 ? ( class => 'odd' ) : (); - td class => 'tc1_1'; - a href => $revurl, "$i->{type}$i->{iid}"; - end; - td class => 'tc1_2'; - a href => $revurl, ".$i->{rev}"; - end; - td class => 'tc2', $self->{l10n}->date($i->{added}); - td class => 'tc3'; - lit $self->{l10n}->userstr($i); - end; - td; - a href => $revurl, title => $i->{ioriginal}, shorten $i->{ititle}, 80; - end; - end; - if($i->{comments}) { - Tr class => $n % 2 ? 'collapse msgsum odd hidden' : 'collapse msgsum hidden'; - td colspan => 5; - lit bb2html $i->{comments}, 150; - end; - end; - } - }, - ); -} - - sub htmlSearchBox { my($self, $sel, $v) = @_; |