summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
Diffstat (limited to 'lib')
-rw-r--r--lib/Multi/API.pm16
-rw-r--r--lib/Multi/Anime.pm17
-rw-r--r--lib/Multi/Maintenance.pm37
-rw-r--r--lib/Multi/RG.pm11
-rw-r--r--lib/POE/Filter/VNDBAPI.pm135
-rw-r--r--lib/VNDB/DB/Discussions.pm18
-rw-r--r--lib/VNDB/DB/Misc.pm69
-rw-r--r--lib/VNDB/DB/Producers.pm49
-rw-r--r--lib/VNDB/DB/Releases.pm108
-rw-r--r--lib/VNDB/DB/Tags.pm89
-rw-r--r--lib/VNDB/DB/ULists.pm30
-rw-r--r--lib/VNDB/DB/Users.pm14
-rw-r--r--lib/VNDB/DB/VN.pm113
-rw-r--r--lib/VNDB/Handler/Discussions.pm4
-rw-r--r--lib/VNDB/Handler/Misc.pm21
-rw-r--r--lib/VNDB/Handler/Producers.pm30
-rw-r--r--lib/VNDB/Handler/Releases.pm24
-rw-r--r--lib/VNDB/Handler/Tags.pm162
-rw-r--r--lib/VNDB/Handler/ULists.pm4
-rw-r--r--lib/VNDB/Handler/Users.pm4
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm57
-rw-r--r--lib/VNDB/Handler/VNEdit.pm35
-rw-r--r--lib/VNDB/Handler/VNPage.pm46
-rw-r--r--lib/VNDB/L10N.pm6
-rw-r--r--lib/VNDB/Plugin/TransAdmin.pm2
-rw-r--r--lib/VNDB/Util/BrowseHTML.pm204
-rw-r--r--lib/VNDB/Util/CommonHTML.pm153
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 => [ \&gtintype, '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>&#9656;</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) = @_;
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-16 23:01:49 +0000