From 70f0408500a37c1e3ce9fc68a5b894babf62b7c8 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 9 Nov 2009 16:40:40 +0100 Subject: Plugin::TransAdmin: CSS/doc fixes related to the API + Changelog updates, considering the API and other changes are now in the beta branch. --- lib/VNDB/Plugin/TransAdmin.pm | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm index ed5ebb36..f9cf8aed 100644 --- a/lib/VNDB/Plugin/TransAdmin.pm +++ b/lib/VNDB/Plugin/TransAdmin.pm @@ -172,7 +172,7 @@ sub _intro { $f->close; div class => 'mainbox'; h1 'Introduction to the language file'; - pre $intro; + pre style => 'padding: 0; margin: 0; background: none; border: none', $intro; end; } @@ -217,7 +217,7 @@ sub _section { for my $l (@$page) { if($l->[0] eq 'comment') { - pre; + pre style => 'padding: 0; margin: 0; background: none; border: none'; b class => 'grayedout', $l->[1]."\n"; end; next; @@ -281,7 +281,7 @@ sub _savedoc { sub _docs { my($lang, $doc) = @_; - my @d = map /\.[a-z]{2}$/ || /\/8$/ ? () : s{^.+\/([^/]+)$}{$1} && $_, glob "$VNDB::ROOT/data/docs/*"; + my @d = map /\.[a-z]{2}$/ || /\/(?:8|11)$/ ? () : s{^.+\/([^/]+)$}{$1} && $_, glob "$VNDB::ROOT/data/docs/*"; h2 class => 'alttitle', '...or a doc page'; div style => 'margin: 0 40px'; -- cgit v1.2.3 From e4b912bc68cebff9c42ef17e7356e9cf5a218272 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Mon, 9 Nov 2009 17:06:45 +0100 Subject: bb2html: Added [code] tag and fixed a minor bug The previous version also had a problem with closing tags when a $maxlength was defined. With $maxlength, not all tags actually open a tag in HTML, after all. --- lib/VNDB/Func.pm | 23 ++++++++++++++++++----- 1 file changed, 18 insertions(+), 5 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index ad38215e..49df0121 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -22,6 +22,7 @@ sub shorten { # [raw] .. [/raw] # [spoiler] .. [/spoiler] # [quote] .. [/quote] +# [code] .. [/code] # v+, v+.+ # http://../ sub bb2html { @@ -51,7 +52,7 @@ sub bb2html { $rmnewline-- && $_ eq "\n" && next if $rmnewline; my $lit = $_; - if($open[$#open] ne 'raw') { + if($open[$#open] ne 'raw' && $open[$#open] ne 'code') { if (lc$_ eq '[raw]') { push @open, 'raw'; next } elsif (lc$_ eq '[spoiler]') { push @open, 'spoiler'; $result .= ''; next } elsif (lc$_ eq '[quote]') { @@ -59,6 +60,11 @@ sub bb2html { $result .= '
' if !$maxlength; $rmnewline = 1; next + } elsif (lc$_ eq '[code]') { + push @open, 'code'; + $result .= '
' if !$maxlength;
+        $rmnewline = 1;
+        next
       } elsif (lc$_ eq '[/spoiler]') {
         if($open[$#open] eq 'spoiler') {
           $result .= '';
@@ -97,8 +103,12 @@ sub bb2html {
         $result .= $_;
         next;
       }
-    } elsif(lc$_ eq '[/raw]') {
-      pop @open if $open[$#open] eq 'raw';
+    } elsif($open[$#open] eq 'raw' && lc$_ eq '[/raw]') {
+      pop @open;
+      next;
+    } elsif($open[$#open] eq 'code' && lc$_ eq '[/code]') {
+      $result .= '
' if !$maxlength; + pop @open; next; } @@ -108,8 +118,11 @@ sub bb2html { $result .= $e->($_); } - $result .= $_ eq 'url' ? '' : $_ eq 'quote' ? '
' : '' - while((local $_ = pop @open) ne 'first'); + # close open tags + while((local $_ = pop @open) ne 'first') { + $result .= $_ eq 'url' ? '' : $_ eq 'spoiler' ? '' : ''; + $result .= $_ eq 'quote' ? '' : $_ eq 'code' ? '' : '' if !$maxlength; + } $result .= '...' if $maxlength && $length > $maxlength; return $result; -- cgit v1.2.3 From 997d1752ff1b1d2b1cf425b2c05dc180abc2243a Mon Sep 17 00:00:00 2001 From: Yorhel Date: Fri, 13 Nov 2009 14:08:20 +0100 Subject: docs: Updated d11 and added :SUBSUB: macro --- lib/VNDB/Handler/Misc.pm | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 9625372b..d2f5852b 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -281,12 +281,17 @@ sub docpage { (my $title = shift @c) =~ s/^:TITLE://; chomp $title; - my $sec = 0; + my($sec, $subsec) = (0,0); for (@c) { s{^:SUB:(.+)\r?\n$}{ $sec++; + $subsec = 0; qq|

$sec. $1

\n| }eg; + s{^:SUBSUB:(.+)\r?\n$}{ + $subsec++; + qq|

$sec.$subsec. $1

\n| + }eg; s{^:INC:(.+)\r?\n$}{ $f = sprintf('%s/data/docs/%s', $VNDB::ROOT, $1); open($F, '<:utf8', $f.$l) or open($F, '<:utf8', $f) or die $!; -- cgit v1.2.3 From 0a4f97f0186d6941a4cab2e3bd05201f1fed1441 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 10:50:22 +0100 Subject: SQL/L10N: Allow NULL for releases_rev.minage and make the values translatable --- lib/VNDB/DB/Releases.pm | 2 +- lib/VNDB/DB/ULists.pm | 2 +- lib/VNDB/Func.pm | 17 ++++++++++++++++- lib/VNDB/Handler/Releases.pm | 25 +++++++++++++------------ lib/VNDB/Handler/VNPage.pm | 2 +- 5 files changed, 32 insertions(+), 16 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index 9260b787..d7bddaab 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -40,7 +40,7 @@ sub dbReleaseGet { defined $o{type} ? ( 'rr.type = ?' => $o{type} ) : (), $o{minage} ? ( - '(rr.minage !s ? AND rr.minage <> -1)' => [ $o{minage}[0] ? '<=' : '>=', $o{minage}[1] ] ) : (), + 'rr.minage !s ?' => [ $o{minage}[0] ? '<=' : '>=', $o{minage}[1] ] ) : (), $o{media} ? ( 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' => [ $o{media} ] ) : (), $o{resolutions} ? ( diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index e9b38e57..0f54686c 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -78,7 +78,7 @@ sub dbVNListList { } @$r; my $rel = $self->dbAll(q| - SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rr.minage, rl.rstat, rl.vstat + SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.rstat, rl.vstat FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_rev rr ON rr.id = r.latest diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index 49df0121..1801e3b8 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -6,7 +6,7 @@ use warnings; use YAWF ':html'; use Exporter 'import'; use POSIX 'strftime', 'ceil', 'floor'; -our @EXPORT = qw| shorten bb2html gtintype liststat clearfloat cssicon tagscore mt |; +our @EXPORT = qw| shorten bb2html gtintype liststat clearfloat cssicon tagscore mt minage |; # I would've done this as a #define if this was C... @@ -221,5 +221,20 @@ sub mt { } +sub minage { + my($a, $ex) = @_; + my $str = !defined($a) ? mt '_minage_null' : !$a ? mt '_minage_all' : mt '_minage_age', $a; + $ex = !defined($a) ? '' : { + 0 => 'CERO A', + 12 => 'CERO B', + 15 => 'CERO C', + 17 => 'CERO D', + 18 => 'CERO Z', + }->{$a} if $ex; + return $str if !$ex; + return $str.' '.mt('_minage_example', $ex); +} + + 1; diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index 7365f235..daeffb9e 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -50,7 +50,7 @@ sub page { [ languages => join => ', ', split => sub { map mt("_lang_$_"), @{$_[0]} } ], [ 'website' ], [ released => htmlize => sub { $self->{l10n}->datestr($_[0]) } ], - [ minage => serialize => sub { $self->{age_ratings}{$_[0]}[0] } ], + [ minage => serialize => \&minage ], [ notes => diff => 1 ], [ platforms => join => ', ', split => sub { map mt("_plat_$_"), @{$_[0]} } ], [ media => join => ', ', split => sub { @@ -189,10 +189,10 @@ sub _infotable { end; end; - if($r->{minage} >= 0) { + if(defined $r->{minage}) { Tr ++$i % 2 ? (class => 'odd') : (); td mt '_relinfo_minage'; - td $self->{age_ratings}{$r->{minage}}[0]; + td minage $r->{minage}; end; } @@ -287,7 +287,8 @@ sub edit { my $vn = $rid ? $r->{vn} : [{ vid => $vid, title => $v->{title} }]; my %b4 = !$rid ? () : ( (map { $_ => $r->{$_} } qw|type title original gtin catalog languages website released - notes minage platforms patch resolution voiced freeware doujin ani_story ani_ero|), + notes platforms patch resolution voiced freeware doujin ani_story ani_ero|), + minage => defined($r->{minage}) ? $r->{minage} : -1, media => join(',', sort map "$_->{medium} $_->{qty}", @{$r->{media}}), producers => join('|||', map sprintf('%d,%d,%s', $_->{id}, ($_->{developer}?1:0)+($_->{publisher}?2:0), $_->{name}), @@ -311,7 +312,7 @@ sub edit { { name => 'languages', multi => 1, enum => $self->{languages} }, { name => 'website', required => 0, default => '', template => 'url' }, { name => 'released', required => 0, default => 0, template => 'int' }, - { name => 'minage' , required => 0, default => -1, enum => [ keys %{$self->{age_ratings}} ] }, + { name => 'minage' , required => 0, default => -1, enum => [map !defined($_)?-1:$_, @{$self->{age_ratings}}] }, { name => 'notes', required => 0, default => '', maxlength => 10240 }, { name => 'platforms', required => 0, default => '', multi => 1, enum => $self->{platforms} }, { name => 'media', required => 0, default => '' }, @@ -349,7 +350,8 @@ sub edit { if(!$frm->{_err}) { my %opts = ( (map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released - notes minage platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|), + notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|), + minage => $frm->{minage} < 0 ? undef : $frm->{minage}, vn => $new_vn, producers => $producers, media => $media, @@ -403,8 +405,7 @@ sub _form { [ date => short => 'released', name => mt('_redit_form_released') ], [ static => content => mt('_redit_form_released_note') ], [ select => short => 'minage', name => mt('_redit_form_minage'), - options => [ map [ $_, $self->{age_ratings}{$_}[0].($self->{age_ratings}{$_}[1]?" (e.g. $self->{age_ratings}{$_}[1])":'') ], - sort { $a <=> $b } keys %{$self->{age_ratings}} ] ], + options => [ map [ !defined($_)?-1:$_, minage $_, 1 ], @{$self->{age_ratings}} ] ], [ textarea => short => 'notes', name => mt('_redit_form_notes').'
'.mt('_inenglish').'' ], [ static => content => mt('_redit_form_notes_note') ], ], @@ -495,7 +496,7 @@ sub browse { { name => 'fw', required => 0, default => 0, enum => [ 0..2 ] }, { name => 'do', required => 0, default => 0, enum => [ 0..2 ] }, { name => 'ma_m', required => 0, default => 0, enum => [ 0, 1 ] }, - { name => 'ma_a', required => 0, default => 0, enum => [ keys %{$self->{age_ratings}} ] }, + { name => 'ma_a', required => 0, default => 0, enum => [ grep defined($_), @{$self->{age_ratings}} ] }, { name => 'mi', required => 0, default => 0, template => 'int' }, { name => 'ma', required => 0, default => 99999999, template => 'int' }, { name => 're', required => 0, multi => 1, default => 0, enum => [ 1..$#{$self->{resolutions}} ] }, @@ -550,7 +551,7 @@ sub browse { td class => 'tc1'; lit $self->{l10n}->datestr($l->{released}); end; - td class => 'tc2', $l->{minage} > -1 ? $self->{age_ratings}{$l->{minage}}[0] : ''; + td class => 'tc2', !defined($l->{minage}) ? '' : minage $l->{minage}; td class => 'tc3'; $_ ne 'oth' && cssicon $_, mt "_plat_$_" for (@{$l->{platforms}}); cssicon "lang $_", mt "_lang_$_" for (@{$l->{languages}}); @@ -599,8 +600,8 @@ sub _filters { option value => 1, $f->{ma_m} == 1 ? ('selected' => 'selected') : (), mt '_rbrowse_le'; end; Select id => 'ma_a', name => 'ma_a', style => 'width: 80px; text-align: center'; - $_>=0 && option value => $_, $f->{ma_a} == $_ ? ('selected' => 'selected') : (), $self->{age_ratings}{$_}[0] - for (sort { $a <=> $b } keys %{$self->{age_ratings}}); + defined($_) && option value => $_, $f->{ma_a} == $_ ? ('selected' => 'selected') : (), minage $_ + for (@{$self->{age_ratings}}); end; end; td rowspan => 5, style => 'padding-left: 40px'; diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index ec5f4afc..7a258d5a 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -398,7 +398,7 @@ sub _releases { for my $rel (grep grep($_ eq $l, @{$_->{languages}}), @$r) { Tr; td class => 'tc1'; lit $self->{l10n}->datestr($rel->{released}); end; - td class => 'tc2', $rel->{minage} < 0 ? '' : $self->{age_ratings}{$rel->{minage}}[0]; + td class => 'tc2', !defined($rel->{minage}) ? '' : minage $rel->{minage}; td class => 'tc3'; for (sort @{$rel->{platforms}}) { next if $_ eq 'oth'; -- cgit v1.2.3 From aef1e3f073df3494711e949eb2aa31c60cad8460 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 11:51:31 +0100 Subject: Added wikipedia links for producers --- lib/VNDB/DB/Producers.pm | 6 +++--- lib/VNDB/Handler/Producers.pm | 19 ++++++++++++++++--- 2 files changed, 19 insertions(+), 6 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index 7c7da3ba..0539634c 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -43,7 +43,7 @@ sub dbProducerGet { push @join, 'JOIN relgraphs pg ON pg.id = p.rgraph' if $o{what} =~ /relgraph/; my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph'; - $select .= ', pr.desc, pr.alias, pr.website, p.hidden, p.locked' if $o{what} =~ /extended/; + $select .= ', pr.desc, pr.alias, pr.website, pr.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/; $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev| if $o{what} =~ /changes/; $select .= ', pg.svg' if $o{what} =~ /relgraph/; @@ -125,9 +125,9 @@ sub dbProducerAdd { sub insert_rev { my($self, $cid, $pid, $o) = @_; $self->dbExec(q| - INSERT INTO producers_rev (id, pid, name, original, website, type, lang, "desc", alias) + INSERT INTO producers_rev (id, pid, name, original, website, l_wp, type, lang, "desc", alias) VALUES (!l)|, - [ $cid, $pid, @$o{qw| name original website type lang desc alias|} ] + [ $cid, $pid, @$o{qw| name original website l_wp type lang desc alias|} ] ); $self->dbExec(q| diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index 27534e67..e3473c3c 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -62,6 +62,9 @@ sub page { [ alias => diff => 1 ], [ lang => serialize => sub { "$_[0] (".mt("_lang_$_[0]").')' } ], [ website => diff => 1 ], + [ l_wp => htmlize => sub { + $_[0] ? sprintf '%1$s', xml_escape $_[0] : mt '_vndiff_nolink' # _vn? hmm... + }], [ desc => diff => 1 ], [ relations => join => '
', split => sub { my @r = map sprintf('%s: %s', @@ -79,9 +82,15 @@ sub page { p class => 'center'; txt mt '_prodpage_langtype', mt("_lang_$p->{lang}"), mt "_ptype_$p->{type}"; txt "\n".mt '_prodpage_aliases', $p->{alias} if $p->{alias}; - if($p->{website}) { - txt "\n"; - a href => $p->{website}, $p->{website}; + + my @links = ( + $p->{website} ? [ 'homepage', $p->{website} ] : (), + $p->{l_wp} ? [ 'wikipedia', "http://en.wikipedia.org/wiki/$p->{l_wp}" ] : (), + ); + txt "\n" if @links; + for(@links) { + a href => $_->[1], mt "_prodpage_$_->[0]"; + txt ' - ' if $_ ne $links[$#links]; } end; @@ -146,6 +155,7 @@ sub edit { my %b4 = !$pid ? () : ( (map { $_ => $p->{$_} } qw|type name original lang website desc alias|), + l_wp => $p->{l_wp} || '', prodrelations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{name}, sort { $a->{id} <=> $b->{id} } @{$p->{relations}}), ); my $frm; @@ -158,6 +168,7 @@ sub edit { { name => 'alias', required => 0, maxlength => 500, default => '' }, { name => 'lang', enum => $self->{languages} }, { name => 'website', required => 0, template => 'url', default => '' }, + { name => 'l_wp', required => 0, maxlength => 150, default => '' }, { name => 'desc', required => 0, maxlength => 5000, default => '' }, { name => 'prodrelations', required => 0, maxlength => 5000, default => '' }, { name => 'editsum', maxlength => 5000 }, @@ -173,6 +184,7 @@ sub edit { if $pid && !grep $frm->{$_} ne $b4{$_}, keys %b4; $frm->{relations} = $relations; + $frm->{l_wp} = undef if !$frm->{l_wp}; $rev = 1; my $npid = $pid; my $cid; @@ -210,6 +222,7 @@ sub edit { [ select => name => mt('_pedit_form_lang'), short => 'lang', options => [ map [ $_, "$_ (".mt("_lang_$_").')' ], sort @{$self->{languages}} ] ], [ input => name => mt('_pedit_form_website'), short => 'website' ], + [ input => name => mt('_pedit_form_wikipedia'), short => 'l_wp', pre => 'http://en.wikipedia.org/wiki/' ], [ text => name => mt('_pedit_form_desc').'
'.mt('_inenglish').'', short => 'desc', rows => 6 ], ], 'pedit_rel' => [ mt('_pedit_form_rel'), [ hidden => short => 'prodrelations' ], -- cgit v1.2.3 From 537bba22c0ed0c0fe52ba067f136b30e7f95d968 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 11:51:48 +0100 Subject: Made external links on VN pages translatable --- lib/VNDB/Handler/VNPage.pm | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 7a258d5a..b27fa58b 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -111,16 +111,16 @@ sub page { end; } my @links = ( - $v->{l_wp} ? [ 'Wikipedia', 'http://en.wikipedia.org/wiki/%s', $v->{l_wp} ] : (), - $v->{l_encubed} ? [ 'Encubed', 'http://novelnews.net/tag/%s/', $v->{l_encubed} ] : (), - $v->{l_renai} ? [ 'Renai.us', 'http://renai.us/game/%s.shtml', $v->{l_renai} ] : (), + $v->{l_wp} ? [ 'wp', 'http://en.wikipedia.org/wiki/%s', $v->{l_wp} ] : (), + $v->{l_encubed} ? [ 'encubed', 'http://novelnews.net/tag/%s/', $v->{l_encubed} ] : (), + $v->{l_renai} ? [ 'renai', 'http://renai.us/game/%s.shtml', $v->{l_renai} ] : (), ); if(@links) { Tr ++$i % 2 ? (class => 'odd') : (); td mt '_vnpage_links'; td; for(@links) { - a href => sprintf($_->[1], $_->[2]), $_->[0]; + a href => sprintf($_->[1], $_->[2]), mt "_vnpage_l_$_->[0]"; txt ', ' if $_ ne $links[$#links]; } end; -- cgit v1.2.3 From ed15643975e6e05ce5e8b9509901f769c86e06b4 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 16:37:25 +0100 Subject: Added bayesian rating and vote count to the VN list It's even realtime! To my surprise this calculation isn't very heavy, or PostgreSQL is just extremely fast. The GetVN query on /v/all takes 100ms in the worst case (instead of the usual 30-60ms). Can always cache this later on. --- lib/VNDB/DB/VN.pm | 5 ++++- lib/VNDB/Handler/VNBrowse.pm | 8 +++++++- 2 files changed, 11 insertions(+), 2 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index bb2c1275..b1fb103b 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -78,6 +78,8 @@ sub dbVNGet { 'JOIN users u ON u.id = c.requester' : (), $o{what} =~ /relgraph/ ? 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), + $o{what} =~ /rating/ ? + 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (), ); my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]}; @@ -89,6 +91,7 @@ sub dbVNGet { qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), $o{what} =~ /ranking/ ? '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (), + $o{what} =~ /rating/ ? 'r.rating, r.votecount' : (), $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| : (), ); @@ -98,7 +101,7 @@ sub dbVNGet { FROM vn_rev vr !s !W - ORDER BY !s|, + ORDER BY !s NULLS LAST|, join(', ', @select), join(' ', @join), \%where, $o{order}, ); diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index bca21151..87f3371a 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -16,7 +16,7 @@ sub list { my($self, $char) = @_; my $f = $self->formValidate( - { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore| ] }, + { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore rating| ] }, { name => 'o', required => 0, enum => [ 'a','d' ] }, { name => 'p', required => 0, default => 1, template => 'int' }, { name => 'q', required => 0, default => '' }, @@ -56,6 +56,7 @@ sub list { $f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o}; my($list, $np) = $self->dbVNGet( + what => 'rating', $char ne 'all' ? ( char => $char ) : (), $f->{q} ? ( search => $f->{q} ) : (), results => 50, @@ -90,6 +91,7 @@ sub list { [ '', 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) = @_; @@ -114,6 +116,10 @@ sub list { lit $self->{l10n}->datestr($l->{c_released}); end; td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100; + td class => 'tc6'; + txt sprintf '%.2f', $l->{rating}||0; + b class => 'grayedout', sprintf ' (%d)', $l->{votecount}||0; + end; end; }, ); -- cgit v1.2.3 From 2394a77e8d503ebcf78109ced76680e46770b617 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 17:08:19 +0100 Subject: Added bayesian rating info to VN pages This adds about 100ms (sometimes more) to the page generation time of VN pages... maybe I should cache the ratings after all. --- lib/VNDB/DB/VN.pm | 7 +++++-- lib/VNDB/Handler/VNPage.pm | 2 +- lib/VNDB/Util/CommonHTML.pm | 5 +++-- 3 files changed, 9 insertions(+), 5 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index b1fb103b..4201181a 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -78,7 +78,7 @@ sub dbVNGet { 'JOIN users u ON u.id = c.requester' : (), $o{what} =~ /relgraph/ ? 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), - $o{what} =~ /rating/ ? + $o{what} =~ /(rating|ranking)/ ? 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (), ); @@ -90,7 +90,10 @@ sub dbVNGet { $o{what} =~ /changes/ ? ( qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), - $o{what} =~ /ranking/ ? '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (), + $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_ratings ir WHERE ir.rating > r.rating) AS r_ranking', + ) : (), $o{what} =~ /rating/ ? 'r.rating, r.votecount' : (), $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| : (), diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index b27fa58b..2a333337 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -46,7 +46,7 @@ sub page { my $v = $self->dbVNGet( id => $vid, - what => 'extended anime relations screenshots ranking'.($rev ? ' changes' : ''), + what => 'extended anime relations screenshots rating ranking'.($rev ? ' changes' : ''), $rev ? (rev => $rev) : (), )->[0]; return 404 if !$v->{id}; diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index ad99d32d..857cdcef 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -481,8 +481,9 @@ sub htmlVoteStats { clearfloat; if($type eq 'v') { div; - h3 mt '_votestats_pop_title'; - p mt '_votestats_pop_sum', $obj->{ranking}, $self->{stats}{vn}, sprintf('%0.2f',$obj->{c_popularity}*100); + h3 mt '_votestats_rank_title'; + p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100; + p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{rating}; end; } end; -- cgit v1.2.3 From 151a8338c931389c6d67389c56c2d8862e882de6 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 18:46:59 +0100 Subject: SQL: Cached bayesian VN rating and vote counts Was a good idea after all... --- lib/VNDB/DB/VN.pm | 10 ++++------ lib/VNDB/Handler/VNBrowse.pm | 14 +++++++++++--- lib/VNDB/Util/CommonHTML.pm | 2 +- 3 files changed, 16 insertions(+), 10 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 4201181a..250f5267 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -11,7 +11,7 @@ our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd # Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, order, what -# What: extended anime relations screenshots relgraph ranking changes +# What: extended anime relations screenshots relgraph rating ranking changes sub dbVNGet { my($self, %o) = @_; $o{results} ||= 10; @@ -78,23 +78,21 @@ sub dbVNGet { 'JOIN users u ON u.id = c.requester' : (), $o{what} =~ /relgraph/ ? 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), - $o{what} =~ /(rating|ranking)/ ? - 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (), ); my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]}; my @select = ( - qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph v.c_popularity|, 'vr.id AS cid', + qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid', $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|) : (), $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_ratings ir WHERE ir.rating > r.rating) AS r_ranking', + '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking', ) : (), - $o{what} =~ /rating/ ? 'r.rating, r.votecount' : (), $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| : (), ); diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index 87f3371a..3aaa1e25 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -55,13 +55,21 @@ 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 => ($f->{s} eq 'rel' ? 'c_released' : $f->{s} eq 'pop' ? 'c_popularity' : $f->{s}).($f->{o} eq 'a' ? ' ASC' : ' DESC'), + order => $sortcol.($f->{o} eq 'a' ? ' ASC' : ' DESC'), $f->{pl}[0] ? ( platform => $f->{pl} ) : (), $f->{ln}[0] ? ( lang => $f->{ln} ) : (), @ti ? (tags_include => [ $f->{sp}, \@ti ]) : (), @@ -117,8 +125,8 @@ sub list { end; td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100; td class => 'tc6'; - txt sprintf '%.2f', $l->{rating}||0; - b class => 'grayedout', sprintf ' (%d)', $l->{votecount}||0; + txt sprintf '%.2f', $l->{c_rating}; + b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount}; end; end; }, diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 857cdcef..bb731a26 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -483,7 +483,7 @@ sub htmlVoteStats { div; h3 mt '_votestats_rank_title'; p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100; - p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{rating}; + p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{c_rating}; end; } end; -- cgit v1.2.3 From 7791d0236a9bcd85ea3542360b0c68a8492a909e Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 14 Nov 2009 19:04:44 +0100 Subject: SQL: Allow NULL for vn.c_popularity Sorting from least to most popular VN make sense now, you won't have to wade through those entries without any vote at all. --- lib/VNDB/Handler/VNBrowse.pm | 4 ++-- lib/VNDB/Util/CommonHTML.pm | 2 +- 2 files changed, 3 insertions(+), 3 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index 3aaa1e25..dbe80ac1 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -123,9 +123,9 @@ sub list { td class => 'tc4'; lit $self->{l10n}->datestr($l->{c_released}); end; - td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100; + td class => 'tc5', sprintf '%.2f', ($l->{c_popularity}||0)*100; td class => 'tc6'; - txt sprintf '%.2f', $l->{c_rating}; + txt sprintf '%.2f', $l->{c_rating}||0; b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount}; end; end; diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index bb731a26..dc87d737 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -479,7 +479,7 @@ sub htmlVoteStats { } clearfloat; - if($type eq 'v') { + if($type eq 'v' && $obj->{c_votecount}) { div; h3 mt '_votestats_rank_title'; p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100; -- cgit v1.2.3 From 7f1b892cb46c8f713a3bb7c5ea94de2f7aa42508 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 15 Nov 2009 10:46:54 +0100 Subject: Auth: Combined dbSessionCheck into dbUserGet This one query is a bit faster than the two queries executed seperately, and with a query that is executed on each pageview it does matter. Ideally, the dbUserMessageCount() is cached and fetched with the same query, this should save another 1-2ms. But this is probably not worth the extra code it would require. --- lib/VNDB/DB/Users.pm | 25 +++++++++++-------------- lib/VNDB/Util/Auth.pm | 4 ++-- 2 files changed, 13 insertions(+), 16 deletions(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index 593c6415..39429a02 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -5,10 +5,10 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel dbSessionCheck|; +our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|; -# %options->{ username passwd mail order uid ip registered search results page what } +# %options->{ username passwd mail session order uid ip registered search results page what } # what: stats extended sub dbUserGet { my $s = shift; @@ -40,6 +40,8 @@ sub dbUserGet { 'registered > to_timestamp(?)' => $o{registered} ) : (), $o{search} ? ( 'username ILIKE ?' => "%$o{search}%") : (), + $o{session} ? ( + q|s.token = decode(?, 'hex')| => $o{session} ) : (), ); my @select = ( @@ -59,12 +61,17 @@ sub dbUserGet { ) : (), ); + my @join = ( + $o{session} ? 'JOIN sessions s ON s.uid = u.id' : (), + ); + my($r, $np) = $s->dbPage(\%o, q| SELECT !s FROM users u + !s !W ORDER BY !s|, - join(', ', @select), \%where, $o{order} + join(', ', @select), join(' ', @join), \%where, $o{order} ); return wantarray ? ($r, $np) : $r; } @@ -147,15 +154,5 @@ sub dbSessionDel { } -# Queries the database for the validity of a session -# Returns 1 if corresponding session found, 0 if not -# uid, token -sub dbSessionCheck { - my($s, @o) = @_; - return $s->dbRow( - q|SELECT count(uid) AS count FROM sessions WHERE uid = ? AND token = decode(?, 'hex') LIMIT 1|, @o - )->{count}||0; -} - - 1; + diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index a3bf7c29..cef9b905 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -25,8 +25,8 @@ sub authInit { return _rmcookie($self) if length($cookie) < 41; my $token = substr($cookie, 0, 40); my $uid = substr($cookie, 40); - return _rmcookie($self) if $uid !~ /^\d+$/ || !$self->dbSessionCheck($uid, $token); - $self->{_auth} = $self->dbUserGet(uid => $uid, what => 'extended')->[0]; + $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended')->[0]; + return _rmcookie($self) if !$self->{_auth}; } -- cgit v1.2.3 From 2879734d7f5dd805181319dc191a9ee8013497ae Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 15 Nov 2009 11:23:45 +0100 Subject: TransAdmin: CSS fix for doc page editor --- lib/VNDB/Plugin/TransAdmin.pm | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'lib/VNDB') diff --git a/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm index f9cf8aed..0d510aa9 100644 --- a/lib/VNDB/Plugin/TransAdmin.pm +++ b/lib/VNDB/Plugin/TransAdmin.pm @@ -327,7 +327,7 @@ sub _doc { } div style => 'width: 48%; margin-right: 10px; overflow-y: auto; float: left'; - pre style => 'font: 12px Tahoma', $en; + pre style => 'font: 12px Tahoma; border: none; background: none; margin: 0', $en; end; textarea name => 'tl', id => 'tl', rows => ($en =~ y/\n//), style => 'border: none; float: left; width: 49%; white-space: nowrap', wrap => 'off'; -- cgit v1.2.3