diff options
author | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-07-29 11:15:07 +0000 |
---|---|---|
committer | yorhel <yorhel@1fe2e327-d9db-4752-bcf7-ef0cb4a1748b> | 2008-07-29 11:15:07 +0000 |
commit | 7aea19c2f8e71a7d630b613a57325c4dce1b1363 (patch) | |
tree | b4c2e35d40afc7b3a0a22eb8f509c49b37f5f7db /lib | |
parent | a0f440de277653b3c79fd117d026855ec6b78373 (diff) |
- Added release list feature
- Added noindex tag to iid-ttag browser
- Replaced last poster with age of last post on home page
- Merged user vote lists into new vnlist
- Removed 'hide votes' feature
- Fixed users+1 stats bug
- Fixed FF3 JS hack
- Old VNList code is still present, need to decide what to do with that
git-svn-id: svn://vndb.org/vndb@74 1fe2e327-d9db-4752-bcf7-ef0cb4a1748b
Diffstat (limited to 'lib')
-rw-r--r-- | lib/ChangeLog | 6 | ||||
-rw-r--r-- | lib/VNDB.pm | 10 | ||||
-rw-r--r-- | lib/VNDB/Releases.pm | 3 | ||||
-rw-r--r-- | lib/VNDB/Users.pm | 9 | ||||
-rw-r--r-- | lib/VNDB/Util/DB.pm | 128 | ||||
-rw-r--r-- | lib/VNDB/VN.pm | 20 | ||||
-rw-r--r-- | lib/VNDB/VNLists.pm | 101 | ||||
-rw-r--r-- | lib/VNDB/Votes.pm | 62 | ||||
-rw-r--r-- | lib/global.pl | 18 |
9 files changed, 264 insertions, 93 deletions
diff --git a/lib/ChangeLog b/lib/ChangeLog index dc4d5b9f..f14c0edd 100644 --- a/lib/ChangeLog +++ b/lib/ChangeLog @@ -15,6 +15,12 @@ TODO: - Place/time category order on VN pages is now consistent - Admins can delete users from the DB - Added small NSFW indication for users who have disabled the warning + - Added noindex tag to iid-ttag browser + - Replaced last poster with age of last post on home page + - Added release list feature and removed the old VNList + - Merged user vote list into the new release list (and removed 'hide my + votes' option) + - Merged Votes.pm into VNLists.pm 1.19 - 2008-07-08 (r62) - Integrated discussion board diff --git a/lib/VNDB.pm b/lib/VNDB.pm index dff63bc7..be5bc542 100644 --- a/lib/VNDB.pm +++ b/lib/VNDB.pm @@ -20,7 +20,6 @@ use VNDB::Producers; use VNDB::Releases; use VNDB::VNLists; use VNDB::Users; -use VNDB::Votes; use VNDB::VN; @@ -43,10 +42,11 @@ my %VNDBuris = ( # wildcards: * -> (.+), + -> ([0-9]+) }, 'u+' => { '/' => sub { shift->UsrPage(shift) }, - votes => sub { shift->VNVotes(shift) }, edit => sub { shift->UsrEdit(shift) }, del => sub { shift->UsrDel(shift) }, - list => sub { shift->VNMyList(shift) }, + list => sub { shift->RList(shift) }, + #vlist => sub { shift->VNMyList(shift) }, + #votes => sub { shift->VNVotes(shift) }, hist => {'*'=> sub { shift->History('u', shift, $_[1]) } }, }, # visual novels @@ -61,7 +61,7 @@ my %VNDBuris = ( # wildcards: * -> (.+), + -> ([0-9]+) rg => sub { shift->VNPage(shift, shift) }, edit => sub { shift->VNEdit(shift) }, vote => sub { shift->VNVote(shift) }, - list => sub { shift->VNListMod(shift) }, + #list => sub { shift->VNListMod(shift) }, add => sub { shift->REdit('v', shift) }, lock => sub { shift->VNLock(shift) }, hide => sub { shift->VNHide(shift) }, @@ -74,6 +74,7 @@ my %VNDBuris = ( # wildcards: * -> (.+), + -> ([0-9]+) edit => sub { shift->REdit('r', shift) }, lock => sub { shift->RLock(shift) }, hide => sub { shift->RHide(shift) }, + list => sub { shift->RListMod(shift) }, hist => {'*'=> sub { shift->History('r', shift, $_[1]) } }, }, 'r+.+' => sub { shift->RPage($_[0][0], $_[0][1]) }, @@ -160,6 +161,7 @@ my %OLDuris = ( } }, 'u+' => { + votes => sub { shift->ResRedirect('/u'.(shift).'/list', 'perm') }, hist=>{rss => sub { shift->ResRedirect('/u'.(shift).'/hist/rss.xml', 'perm') } }, }, 'p+' => { diff --git a/lib/VNDB/Releases.pm b/lib/VNDB/Releases.pm index 3dcf4153..de598276 100644 --- a/lib/VNDB/Releases.pm +++ b/lib/VNDB/Releases.pm @@ -31,6 +31,9 @@ sub RPage { $self->ResRedirect('/v'.$v->{vn}[0]{vid}) if ($self->ReqHeader('Referer')||'') =~ m{^http://[^/]*(yahoo|google)} && @{$v->{vn}} == 1; + $v->{rlist} = $self->DBGetRList(rids => [ $id ], uid => $self->AuthInfo->{id})->[0] + if $self->AuthInfo->{id}; + return $self->ResAddTpl(rpage => { rel => $v, prev => $c, diff --git a/lib/VNDB/Users.pm b/lib/VNDB/Users.pm index 4b953d17..e2021627 100644 --- a/lib/VNDB/Users.pm +++ b/lib/VNDB/Users.pm @@ -141,7 +141,6 @@ sub UsrEdit { { name => 'username',required => 1, template => 'pname', minlength => 2, maxlength => 15 }, { name => 'rank', required => 1, enum => [ '1'..($#{$self->{ranks}}-1) ] }, ) : (), - { name => 'pvotes',required => 0 }, { name => 'plist', required => 0 }, { name => 'pign_nsfw', required => 0 }, ); @@ -157,8 +156,7 @@ sub UsrEdit { mail => $frm->{mail}, ); $opts{passwd} = $pass if $pass; - $opts{flags} = $frm->{pvotes} ? $VNDB::UFLAGS->{votes} : 0; - $opts{flags} += $VNDB::UFLAGS->{list} if $frm->{plist}; + $opts{flags} = $frm->{plist} ? $VNDB::UFLAGS->{list} : 0; $opts{flags} += $VNDB::UFLAGS->{nsfw} if $frm->{pign_nsfw}; $self->DBUpdateUser($u->{id}, %opts); return $adm ? $self->ResRedirect('/u'.$user.'/edit?d=1', 'post') : @@ -169,7 +167,6 @@ sub UsrEdit { $frm->{$_} ||= $u->{$_} for (qw| username mail rank |); - $frm->{pvotes} ||= $u->{flags} & $VNDB::UFLAGS->{votes}; $frm->{plist} ||= $u->{flags} & $VNDB::UFLAGS->{list}; $frm->{pign_nsfw} ||= $u->{flags} & $VNDB::UFLAGS->{nsfw}; $self->ResAddTpl(useredit => { @@ -221,10 +218,6 @@ sub UsrPage { $self->ResAddTpl(userpage => { user => $u, - lists => { - latest => scalar $self->DBGetVNList(uid => $id, results => 7), - graph => $self->DBVNListStats(uid => $id), - }, votes => { latest => scalar $self->DBGetVotes(uid => $id, results => 10), graph => $self->DBVoteStats(uid => $id), diff --git a/lib/VNDB/Util/DB.pm b/lib/VNDB/Util/DB.pm index 46a17fb1..fdefa6e6 100644 --- a/lib/VNDB/Util/DB.pm +++ b/lib/VNDB/Util/DB.pm @@ -16,6 +16,7 @@ $VERSION = $VNDB::VERSION; DBGetUser DBAddUser DBUpdateUser DBDelUser DBGetVotes DBVoteStats DBAddVote DBDelVote DBGetVNList DBVNListStats DBAddVNList DBEditVNList DBDelVNList + DBGetRList DBGetRLists DBEditRList DBDelRList DBGetVN DBAddVN DBEditVN DBHideVN DBUndefRG DBVNCache DBGetRelease DBAddRelease DBEditRelease DBHideRelease DBGetProducer DBGetProducerVN DBAddProducer DBEditProducer DBHideProducer @@ -141,7 +142,7 @@ sub DBTableCount { # table (users, producers, vn, releases, votes) %s|, $_[1], $_[1] =~ /producers|vn|releases/ ? 'WHERE hidden = 0' : '', - )->{cnt}; + )->{cnt} - ($_[1] eq 'users' ? 1 : 0); } @@ -304,14 +305,6 @@ sub DBGetUser { # %options->{ username mail passwd order firstchar uid results p [ keys %r ] )}); - $r->[$r{$_->{uid}}]{vnlist} = $_->{cnt} for (@{$s->DBAll(q| - SELECT uid, COUNT(vid) AS cnt - FROM vnlists - WHERE uid IN(!l) - GROUP BY uid|, - [ keys %r ] - )}); - $r->[$r{$_->{requester}}]{changes} = $_->{cnt} for (@{$s->DBAll(q| SELECT requester, COUNT(id) AS cnt FROM changes @@ -390,7 +383,7 @@ sub DBGetVotes { # %options->{ uid vid hide order results page } my %where = ( $o{uid} ? ( 'n.uid = %d' => $o{uid} ) : (), $o{vid} ? ( 'n.vid = %d' => $o{vid} ) : (), - $o{hide} ? ( 'u.flags & %d = %1$d' => $VNDB::UFLAGS->{votes} ) : (), + $o{hide} ? ( 'u.flags & %d = %1$d' => $VNDB::UFLAGS->{list} ) : (), ); my $where = scalar keys %where ? 'WHERE !W' : ''; @@ -553,6 +546,119 @@ sub DBDelVNList { # uid, @vid # uid = 0 to delete all #-----------------------------------------------------------------------------# +# U S E R R E L E A S E L I S T S # +#-----------------------------------------------------------------------------# + + +sub DBGetRList { # %options->{ uid rids } + my($s, %o) = @_; + + my %where = ( + 'uid = %d' => $o{uid}, + $o{rids} ? ( + 'rid IN(!l)' => $o{rids} ) : (), + ); + + return $s->DBAll(q| + SELECT uid, rid, rstat, vstat + FROM rlists + WHERE !W|, + \%where); +} + + +# separate function, which also fetches VN info and votes +sub DBGetRLists { # %options->{ uid order char rstat vstat voted page results } + my($s, %o) = @_; + + $o{results} ||= 50; + $o{page} ||= 1; + + # bit ugly... + my $where = !$o{rstat} && !$o{vstat} ? 'vo.vote IS NOT NULL' : ''; + $where .= ($where?' OR ':'').q|v.id IN( + SELECT irv.vid + FROM rlists irl + JOIN releases ir ON ir.id = irl.rid + JOIN releases_vn irv ON irv.rid = ir.latest + WHERE !W + )| if !$o{voted}; + $where = '('.$where.') AND LOWER(SUBSTR(vr.title, 1, 1)) = \''.$o{char}.'\'' if $o{char}; + $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 clause for the rlists subquery + my %where = ( + 'uid = %d' => $o{uid}, + defined $o{rstat} ? ( 'rstat = %d' => $o{rstat} ) : (), + defined $o{vstat} ? ( 'vstat = %d' => $o{vstat} ) : (), + ); + + my $r = $s->DBAll(qq| + SELECT vr.vid, vr.title, v.c_released, v.c_languages, v.c_platforms, COALESCE(vo.vote, 0) AS vote + FROM vn v + JOIN vn_rev vr ON vr.id = v.latest + %s JOIN votes vo ON vo.vid = v.id AND vo.uid = %d + WHERE $where + ORDER BY %s + LIMIT %d OFFSET %d|, + $o{voted} ? '' : 'LEFT', $o{uid}, # JOIN if we only want votes, LEFT JOIN if we also want rlist items + $o{voted} ? () : \%where, + $o{order}, + $o{results}+(wantarray?1:0), $o{results}*($o{page}-1) + ); + + # now fetch the releases and link them to VNs + if(@$r) { + my %vns = map { $_->{rels}=[]; $_->{vid}, $_->{rels} } @$r; + push @{$vns{$_->{vid}}}, $_ for (@{$s->DBAll(q| + SELECT rv.vid, rr.rid, rr.title, rr.original, rr.released, rr.type, rr.language, rr.minage, rl.rstat, rl.vstat + FROM rlists rl + JOIN releases r ON rl.rid = r.id + JOIN releases_rev rr ON rr.id = r.latest + JOIN releases_vn rv ON rv.rid = r.latest + WHERE rl.uid = %d + AND rv.vid IN(!l) + ORDER BY rr.released ASC|, + $o{uid}, [ keys %vns ] + )}); + } + + return $r if !wantarray; + return ($r, 0) if $#$r < $o{results}; + pop @$r; + return ($r, 1); +} + + +sub DBEditRList { # %options->{ uid rid rstat vstat } + # rid can only be a arrayref with UPDATE + my($s, %o) = @_; + my %s = ( + defined $o{rstat} ? ( 'rstat = %d', $o{rstat} ) : (), + defined $o{vstat} ? ( 'vstat = %d', $o{vstat} ) : (), + ); + $o{rstat}||=0; + $o{vstat}||=0; + + $s->DBExec(q|UPDATE rlists SET !H WHERE uid = %d AND rid IN(!l)|, + \%s, $o{uid}, ref($o{rid}) eq 'ARRAY' ? $o{rid} : [ $o{rid} ]) + || + $s->DBExec(q|INSERT INTO rlists (uid, rid, rstat, vstat) + VALUES(%d, %d, %d, %d)|, + @o{qw| uid rid rstat vstat |}); +} + + +sub DBDelRList { # uid, \@rids + my($s, $uid, $rid) = @_; + $s->DBExec(q|DELETE FROM rlists WHERE uid = %d AND rid IN(!l)|, $uid, ref($rid) eq 'ARRAY' ? $rid : [ $rid ]); +} + + + + + +#-----------------------------------------------------------------------------# # V I S U A L N O V E L S # #-----------------------------------------------------------------------------# @@ -1374,7 +1480,7 @@ sub sqlhelper { # type, query, @list $sqlq =~ s/\r?\n/ /g; $sqlq =~ s/ +/ /g; $sqlq = sqlprint($sqlq, @_) if exists $_[0]; -# warn "$sqlq\n"; + #warn "$sqlq\n"; my $q = $s->prepare($sqlq); $q->execute(); diff --git a/lib/VNDB/VN.pm b/lib/VNDB/VN.pm index fadba900..c94705cc 100644 --- a/lib/VNDB/VN.pm +++ b/lib/VNDB/VN.pm @@ -30,7 +30,19 @@ sub VNPage { my $c = $rev && $rev > 1 && $self->DBGetVN(id => $id, rev => $rev-1, what => 'extended changes relations categories anime')->[0]; $v->{next} = $rev && $v->{latest} > $v->{cid} ? $rev+1 : 0; - if($page eq 'rg' && $v->{rgraph}) { + my $rel = $self->DBGetRelease(vid => $id, what => 'producers platforms'); + + if(!$page && @$rel && $self->AuthInfo->{id}) { + my $rl = $self->DBGetRList( + rids => [ map $_->{id}, @$rel ], + uid => $self->AuthInfo->{id} + ); + for my $i (@$rl) { + my $r = (grep $i->{rid} == $_->{id}, @$rel)[0]; + $r->{rlist} = $i; + } + + } elsif($page eq 'rg' && $v->{rgraph}) { open(my $F, '<:utf8', sprintf '%s/%02d/%d.cmap', $self->{mappath}, $v->{rgraph}%100, $v->{rgraph}) || die $!; $v->{rmap} = join('', (<$F>)); close($F); @@ -39,16 +51,12 @@ sub VNPage { $self->ResAddTpl(vnpage => { vote => $self->AuthInfo->{id} ? $self->DBGetVotes(uid => $self->AuthInfo->{id}, vid => $id)->[0] : {}, list => $self->AuthInfo->{id} ? $self->DBGetVNList(uid => $self->AuthInfo->{id}, vid => $id)->[0] : {}, - rel => scalar $self->DBGetRelease(vid => $id, what => 'producers platforms'), vn => $v, + rel => $rel, prev => $c, page => $page, change => $rev, $page eq 'stats' ? ( - lists => { - latest => scalar $self->DBGetVNList(vid => $id, results => 7, hide => 1), - graph => $self->DBVNListStats(vid => $id), - }, votes => { latest => scalar $self->DBGetVotes(vid => $id, results => 10, hide => 1), graph => $self->DBVoteStats(vid => $id), diff --git a/lib/VNDB/VNLists.pm b/lib/VNDB/VNLists.pm index 2a8be0a8..f2ad5d50 100644 --- a/lib/VNDB/VNLists.pm +++ b/lib/VNDB/VNLists.pm @@ -7,7 +7,7 @@ use Exporter 'import'; use vars ('$VERSION', '@EXPORT'); $VERSION = $VNDB::VERSION; -@EXPORT = qw| VNListMod VNMyList |; +@EXPORT = qw| VNListMod VNMyList VNVote RListMod RList |; sub VNListMod { @@ -93,5 +93,104 @@ sub VNMyList { } +sub VNVote { + my $self = shift; + my $id = shift; + + my $uid = $self->AuthInfo()->{id}; + return $self->ResDenied() if !$uid; + + my $f = $self->FormCheck( + { name => 'v', required => 0, default => 0, enum => [ '-1','1'..'10'] } + ); + return $self->ResNotFound() if !$f->{v}; + + + $self->DBDelVote($uid, $id) if $f->{v} == -1 || $self->DBGetVotes(uid => $uid, vid => $id)->[0]{vid}; + $self->DBAddVote($id, $uid, $f->{v}) if $f->{v} > 0; + + $self->ResRedirect('/v'.$id, 'temp'); +} + + +sub RListMod { + my $self = shift; + my $rid = shift; + + my $f = $self->FormCheck( + { name => 'd', required => 0 }, + { name => 'r', required => 0, enum => [ 0..$#$VNDB::RSTAT ] }, + { name => 'v', required => 0, enum => [ 0..$#$VNDB::VSTAT ] }, + ); + + return $self->ResNotFound if $f->{_err}; + return $self->ResDenied if !$self->AuthInfo->{id}; + + if($f->{d}) { + $self->DBDelRList($self->AuthInfo->{id}, $rid); + } else { + $self->DBEditRList( + uid => $self->AuthInfo->{id}, + rid => $rid, + rstat => $f->{r}, + vstat => $f->{v}, + ); + } + + my $r = $self->ReqHeader('Referer'); + $r = $r && $r =~ /([vr][0-9]+)$/ ? $1 : 'r'.$rid; + return $self->ResRedirect('/'.$r, 'temp'); +} + + +sub RList { + my $self = shift; + my $uid = shift; + + my $u = $self->DBGetUser(uid => $uid)->[0]; + return $self->ResNotFound if !$uid || !$u || (($self->AuthInfo->{id}||0) != $uid && !($u->{flags} & $VNDB::UFLAGS->{list})); + + my $f = $self->FormCheck( + { name => 's', required => 0, default => 'title', enum => [ qw|title vote| ] }, + { name => 'o', required => 0, default => 'a', enum => [ 'a','d' ] }, + { name => 'p', required => 0, template => 'int', default => 1 }, + { name => 'c', required => 0, default => 'all', enum => [ 'a'..'z', '0', 'all' ] }, + ); + return $self->ResNotFound if $f->{_err}; + + if($self->ReqMethod eq 'POST') { + my $frm = $self->FormCheck( + { name => 'vnlistchange', required => 1, enum => [ 'd', 'r0'..('r'.$#$VNDB::RSTAT), 'v0'..('v'.$#$VNDB::VSTAT) ] }, + { name => 'rsel', required => 1, multi => 1, template => 'int' }, + ); + if(!$frm->{_err} && @{$frm->{rsel}}) { + $self->DBDelRList($uid, $frm->{rsel}) if $frm->{vnlistchange} eq 'd'; + $self->DBEditRList( + uid => $uid, + rid => $frm->{rsel}, + substr($frm->{vnlistchange},0,1).'stat', substr($frm->{vnlistchange},1) + ) if $frm->{vnlistchange} ne 'd'; + } + } + + my $order = $f->{s} . ($f->{o} eq 'a' ? ' ASC' : ' DESC'); + my($list, $np) = $self->DBGetRLists( + uid => $uid, + results => 50, + page => $f->{p}, + order => $order, + char => $f->{c} eq 'all' ? undef : $f->{c}, + ); + + $self->ResAddTpl(rlist => { + user => $u, + list => $list, + char => $f->{c}, + order => [ $f->{s}, $f->{o} ], + page => $f->{p}, + npage => $np, + }); +} + 1; diff --git a/lib/VNDB/Votes.pm b/lib/VNDB/Votes.pm deleted file mode 100644 index 99b28465..00000000 --- a/lib/VNDB/Votes.pm +++ /dev/null @@ -1,62 +0,0 @@ - -package VNDB::Votes; - -use strict; -use warnings; -use Exporter 'import'; - -use vars ('$VERSION', '@EXPORT'); -$VERSION = $VNDB::VERSION; -@EXPORT = qw| VNVote VNVotes |; - - -sub VNVote { - my $self = shift; - my $id = shift; - - my $uid = $self->AuthInfo()->{id}; - return $self->ResDenied() if !$uid; - - my $f = $self->FormCheck( - { name => 'v', required => 1, default => 0, enum => [ '-1','1'..'10'] } - ); - return $self->ResNotFound() if $f->{_err}; - - - $self->DBDelVote($uid, $id) if $f->{v} == -1 || $self->DBGetVotes(uid => $uid, vid => $id)->[0]{vid}; - $self->DBAddVote($id, $uid, $f->{v}) if $f->{v} > 0; - - $self->ResRedirect('/v'.$id, 'temp'); -} - - -sub VNVotes { - my $self = shift; - my $user = shift; - - my $u = $self->DBGetUser(uid => $user)->[0]; - return $self->ResNotFound if !$user || !$u || (($self->AuthInfo->{id}||0) != $user && !($u->{flags} & $VNDB::UFLAGS->{votes})); - - my $f = $self->FormCheck( - { name => 's', required => 0, default => 'date', enum => [ qw|date title vote| ] }, - { name => 'o', required => 0, default => 'd', enum => [ 'a','d' ] }, - { name => 'p', required => 0, default => 1, template => 'int' }, - ); - return $self->ResNotFound if $f->{_err}; - - my $order = $f->{s} . ($f->{o} eq 'a' ? ' ASC' : ' DESC'); - my ($votes, $np) = $self->DBGetVotes( - uid => $u->{id}, - order => $order, - results => 50, - page => $f->{p} - ); - - $self->ResAddTpl(myvotes => { - user => $u, - votes => $votes, - page => $f->{p}, - npage => $np, - order => [ $f->{s}, $f->{o} ], - }); -} diff --git a/lib/global.pl b/lib/global.pl index 353ffb6d..c1ee12ab 100644 --- a/lib/global.pl +++ b/lib/global.pl @@ -157,6 +157,23 @@ our $CAT = { }; +our $RSTAT = [ + '[blank]', + 'Pending', + 'Obtained', # hardcoded + 'On loan', + 'Deleted', +]; +our $VSTAT = [ + '[blank]', + 'Playing', + 'Finished', # hardcoded + 'Stalled', + 'Dropped', +]; + + +# OLD our $LSTAT = [ 'Wishlist', 'Blacklist', @@ -187,7 +204,6 @@ our $VRELW = {map{$_=>1}qw| 1 7 9 |}; # users.flags our $UFLAGS = { - votes => 1, list => 4, nsfw => 8, }; |