diff options
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/Releases.pm | 11 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 138 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 39 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 47 |
5 files changed, 158 insertions, 79 deletions
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index b0fb9a89..ffffb2a6 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -32,6 +32,7 @@ sub dbReleaseGet { defined $o{type} ? ( 'rr.type = ?' => $o{type} ) : (), defined $o{date_before} ? ( 'rr.released <= ?' => $o{date_before} ) : (), defined $o{date_after} ? ( 'rr.released >= ?' => $o{date_after} ) : (), + defined $o{minage} ? ( 'rr.minage IN(!l)' => [ ref $o{minage} ? $o{minage} : [$o{minage}] ] ) : (), defined $o{resolution} ? ( 'rr.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (), defined $o{voiced} ? ( 'rr.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (), defined $o{ani_story} ? ( 'rr.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (), @@ -47,16 +48,6 @@ sub dbReleaseGet { 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' => [ ref $o{med} ? $o{med} : [ $o{med} ] ] ) : (), ); - # TODO: don't allow NULL for rr.minage after all, since this could be a lot easier... - if(exists $o{minage}) { - my @m = ref $o{minage} ? @{$o{minage}} : ($o{minage}); - my @w = ( - grep(!defined $_ || $_ == -1, @m) ? 'rr.minage IS NULL' : (), - grep(defined $_ && $_ != -1, @m) ? 'rr.minage IN(!s)' : () - ); - push @where, '('.join(' OR ', @w).')', [ grep defined $_ && $_ != -1, @m ]; - } - if($o{search}) { for (split /[ -,._]/, $o{search}) { s/%//g; diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 4a87713b..b3e16960 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -24,7 +24,7 @@ sub dbTagGet { my %where = ( $o{id} ? ( - 't.id = ?' => $o{id} ) : (), + 't.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (), $o{noid} ? ( 't.id <> ?' => $o{noid} ) : (), $o{name} ? ( diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index e52cbf37..e6b6419a 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -7,56 +7,62 @@ use Exporter 'import'; our @EXPORT = qw| - dbVNListGet dbVNListList dbVNListAdd dbVNListDel + dbRListGet dbVNListGet dbVNListList dbVNListAdd dbVNListDel dbRListAdd dbRListDel dbVoteGet dbVoteStats dbVoteAdd dbVoteDel dbWishListGet dbWishListAdd dbWishListDel |; -# Simpler and more efficient version of dbVNListList below -# %options->{ uid rid } -sub dbVNListGet { +# Options: uid rid +sub dbRListGet { my($self, %o) = @_; my %where = ( 'uid = ?' => $o{uid}, - $o{rid} && !ref $o{rid} ? ( - 'rid = ?' => $o{rid} ) : (), - $o{rid} && ref $o{rid} ? ( - 'rid IN(!l)' => [$o{rid}] ) : (), + $o{rid} ? ('rid IN(!l)' => [ ref $o{rid} ? $o{rid} : [$o{rid}] ]) : (), ); return $self->dbAll(q| - SELECT uid, rid, rstat, vstat + SELECT uid, rid, status FROM rlists !W|, \%where ); } +# Options: uid vid +sub dbVNListGet { + my($self, %o) = @_; -# %options->{ uid char voted page results sort reverse } + my %where = ( + 'uid = ?' => $o{uid}, + $o{vid} ? ('vid IN(!l)' => [ ref $o{vid} ? $o{vid} : [$o{vid}] ]) : (), + ); + + return $self->dbAll(q| + SELECT uid, vid, status + FROM vnlists + !W|, + \%where + ); +} + + +# 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{voted} ||= 0; # -1: only non-voted, 0: all, 1: only voted - - # construct the global WHERE clause - my $where = $o{voted} != -1 ? 'vo.vote IS NOT NULL' : ''; - $where .= ($where?' OR ':'').q|v.id = ANY(ARRAY( - SELECT irv.vid - FROM rlists irl - JOIN releases ir ON ir.id = irl.rid - JOIN releases_vn irv ON irv.rid = ir.latest - WHERE uid = ? - ))| if $o{voted} != 1; - $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 = '('.$where.') AND vo.vote IS NULL' if $o{voted} == -1; + + my %where = ( + 'vl.uid = ?' => $o{uid}, + defined($o{voted}) ? ('vo.vote !s NULL' => $o{voted} ? 'IS NOT' : 'IS') : (), + defined($o{status})? ('vl.status = ?' => $o{status}) : (), + $o{char} ? ('LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (), + defined $o{char} && !$o{char} ? ( + '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + ); my $order = sprintf { title => 'vr.title %s', @@ -65,14 +71,14 @@ sub dbVNListList { # execute query my($r, $np) = $self->dbPage(\%o, qq| - SELECT vr.vid, vr.title, vr.original, COALESCE(vo.vote, 0) AS vote - FROM vn v + SELECT vr.vid, vr.title, vr.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote + FROM vnlists vl + JOIN vn v ON v.id = vl.vid JOIN vn_rev vr ON vr.id = v.latest - !s JOIN votes vo ON vo.vid = v.id AND vo.uid = ? - WHERE $where + LEFT JOIN votes vo ON vo.vid = vl.vid AND vo.uid = vl.uid + !W 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} : (), $order + \%where, $order ); # fetch releases and link to VNs @@ -83,7 +89,7 @@ sub dbVNListList { } @$r; my $rel = $self->dbAll(q| - SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.rstat, rl.vstat + SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.status FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_rev rr ON rr.id = r.latest @@ -114,35 +120,57 @@ sub dbVNListList { } -# %options->{ uid rid rstat vstat } +# Arguments: uid vid status notes +# vid can be an arrayref only when the rows are already present, in which case an update is done +# status and notes can be undef when an update is done, in which case these fields aren't updated sub dbVNListAdd { - my($self, %o) = @_; + my($self, $uid, $vid, $stat, $notes) = @_; + $self->dbExec( + 'UPDATE vnlists !H WHERE uid = ? AND vid IN(!l)', + {defined($stat) ? ('status = ?' => $stat ):(), + defined($notes)? ('notes = ?' => $notes):()}, + $uid, ref($vid) ? $vid : [ $vid ] + ) + || + $self->dbExec( + 'INSERT INTO vnlists (uid, vid, status, notes) VALUES(?, ?, ?, ?)', + $uid, $vid, $stat||0, $notes||'' + ); +} - my %s = ( - defined $o{rstat} ? ( 'rstat = ?', $o{rstat} ) : (), - defined $o{vstat} ? ( 'vstat = ?', $o{vstat} ) : (), + +# Arguments: uid, vid +sub dbVNListDel { + my($self, $uid, $vid) = @_; + $self->dbExec( + 'DELETE FROM vnlists WHERE uid = ? AND vid IN(!l)', + $uid, ref($vid) ? $vid : [ $vid ] ); - $o{rstat}||=0; - $o{vstat}||=0; +} + +# Arguments: uid rid status +# rid can be an arrayref only when the rows are already present, in which case an update is done +sub dbRListAdd { + my($self, $uid, $rid, $stat) = @_; $self->dbExec( - 'UPDATE rlists !H WHERE uid = ? AND rid IN(!l)', - \%s, $o{uid}, ref($o{rid}) eq 'ARRAY' ? $o{rid} : [ $o{rid} ] + 'UPDATE rlists SET status = ? WHERE uid = ? AND rid IN(!l)', + $stat, $uid, ref($rid) ? $rid : [ $rid ] ) || $self->dbExec( - 'INSERT INTO rlists (uid, rid, rstat, vstat) VALUES(!l)', - [@o{qw| uid rid rstat vstat |}] + 'INSERT INTO rlists (uid, rid, status) VALUES(?, ?, ?)', + $uid, $rid, $stat ); } # Arguments: uid, rid -sub dbVNListDel { +sub dbRListDel { my($self, $uid, $rid) = @_; $self->dbExec( 'DELETE FROM rlists WHERE uid = ? AND rid IN(!l)', - $uid, ref($rid) eq 'ARRAY' ? $rid : [ $rid ] + $uid, ref($rid) ? $rid : [ $rid ] ); } @@ -160,8 +188,14 @@ sub dbVoteGet { my %where = ( $o{uid} ? ( 'n.uid = ?' => $o{uid} ) : (), $o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (), - $o{hide} ? ( 'u.show_list = TRUE' => 1 ) : (), + $o{hide} ? ( 'NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = n.uid AND key = \'hide_list\')' => 1 ) : (), $o{hide_ign} ? ( '(NOT u.ign_votes OR u.id = ?)' => $self->authInfo->{id}||0 ) : (), + $o{vn_char} ? ( 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{vn_char} ) : (), + defined $o{vn_char} && !$o{vn_char} ? ( + '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + $o{user_char} ? ( 'LOWER(SUBSTR(u.username, 1, 1)) = ?' => $o{user_char} ) : (), + defined $o{user_char} && !$o{user_char} ? ( + '(ASCII(u.username) < 97 OR ASCII(u.username) > 122) AND (ASCII(u.username) < 65 OR ASCII(u.username) > 90)' => 1 ) : (), ); my @select = ( @@ -221,14 +255,15 @@ sub dbVoteStats { # Adds a new vote or updates an existing one # Arguments: vid, uid, vote +# vid can be an arrayref only when the rows are already present, in which case an update is done sub dbVoteAdd { my($self, $vid, $uid, $vote) = @_; $self->dbExec(q| UPDATE votes - SET vote = ? - WHERE vid = ? + SET vote = ?, date = NOW() + WHERE vid IN(!l) AND uid = ?|, - $vote, $vid, $uid + $vote, ref($vid) ? $vid : [$vid], $uid ) || $self->dbExec(q| INSERT INTO votes (vid, uid, vote) @@ -239,10 +274,11 @@ sub dbVoteAdd { # Arguments: uid, vid +# vid can be an arrayref sub dbVoteDel { my($self, $uid, $vid) = @_; $self->dbExec('DELETE FROM votes !W', - { 'vid = ?' => $vid, 'uid = ?' => $uid } + { 'vid IN(!l)' => [ref($vid)?$vid:[$vid]], 'uid = ?' => $uid } ); } diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index 7440f495..bd7db201 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -6,14 +6,14 @@ use warnings; use Exporter 'import'; our @EXPORT = qw| - dbUserGet dbUserEdit dbUserAdd dbUserDel + dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserPrefSet dbSessionAdd dbSessionDel dbSessionUpdateLastUsed dbNotifyGet dbNotifyMarkRead dbNotifyRemove |; # %options->{ username passwd mail session uid ip registered search results page what sort reverse } -# what: notifycount stats extended +# what: notifycount stats extended prefs hide_list # sort: username registered votes changes tags sub dbUserGet { my $s = shift; @@ -21,6 +21,7 @@ sub dbUserGet { page => 1, results => 10, what => '', + sort => '', @_ ); @@ -51,12 +52,13 @@ sub dbUserGet { ); my @select = ( - qw|id username c_votes c_changes show_list c_tags|, + qw|id username c_votes c_changes c_tags|, q|extract('epoch' from registered) as registered|, $o{what} =~ /extended/ ? ( - qw|mail rank salt skin customcss show_nsfw ign_votes notify_dbedit notify_announce|, + qw|mail rank salt ign_votes|, q|encode(passwd, 'hex') AS passwd| ) : (), + $o{what} =~ /hide_list/ ? 'up.value AS hide_list' : (), $o{what} =~ /notifycount/ ? '(SELECT COUNT(*) FROM notifications WHERE uid = u.id AND read IS NULL) AS notifycount' : (), $o{what} =~ /stats/ ? ( @@ -72,12 +74,14 @@ sub dbUserGet { my @join = ( $o{session} ? 'JOIN sessions s ON s.uid = u.id' : (), + $o{what} =~ /hide_list/ || $o{sort} eq 'votes' ? + "LEFT JOIN users_prefs up ON up.uid = u.id AND up.key = 'hide_list'" : (), ); my $order = sprintf { username => 'u.username %s', registered => 'u.registered %s', - votes => 'NOT u.show_list, u.c_votes %s', + votes => 'up.value NULLS FIRST, u.c_votes %s', changes => 'u.c_changes %s', tags => 'u.c_tags %s', }->{ $o{sort}||'username' }, $o{reverse} ? 'DESC' : 'ASC'; @@ -90,6 +94,20 @@ sub dbUserGet { ORDER BY !s|, join(', ', @select), join(' ', @join), \%where, $order ); + + if(@$r && $o{what} =~ /prefs/) { + my %r = map { + $r->[$_]{prefs} = {}; + ($r->[$_]{id}, $r->[$_]) + } 0..$#$r; + + $r{$_->{uid}}{prefs}{$_->{key}} = $_->{value} for (@{$s->dbAll(q| + SELECT uid, key, value + FROM users_prefs + WHERE uid IN(!l)|, + [ keys %r ] + )}); + } return wantarray ? ($r, $np) : $r; } @@ -100,7 +118,7 @@ sub dbUserEdit { my %h; defined $o{$_} && ($h{$_.' = ?'} = $o{$_}) - for (qw| username mail rank show_nsfw show_list skin customcss salt ign_votes notify_dbedit notify_announce |); + for (qw| username mail rank salt ign_votes |); $h{'passwd = decode(?, \'hex\')'} = $o{passwd} if defined $o{passwd}; @@ -127,6 +145,15 @@ sub dbUserDel { } +# uid, key, val +sub dbUserPrefSet { + my($s, $uid, $key, $val) = @_; + !$val ? $s->dbExec('DELETE FROM users_prefs WHERE uid = ? AND key = ?', $uid, $key) + : $s->dbExec('UPDATE users_prefs SET value = ? WHERE uid = ? AND key = ?', $val, $uid, $key) + || $s->dbExec('INSERT INTO users_prefs (uid, key, value) VALUES (?, ?, ?)', $uid, $key, $val); +} + + # Adds a session to the database # uid, 40 character session token sub dbSessionAdd { diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index d25a5796..2a7f2477 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -10,7 +10,8 @@ use Encode 'decode_utf8'; our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|; -# Options: id, rev, char, search, length, lang, olang, plat, tags_include, tags_exclude, hasani, results, page, what, sort, reverse +# Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, +# hasani, hasshot, results, page, what, sort, reverse # What: extended anime relations screenshots relgraph rating ranking changes # Sort: id rel pop rating title tagscore rand sub dbVNGet { @@ -19,6 +20,12 @@ sub dbVNGet { $o{page} ||= 1; $o{what} ||= ''; $o{sort} ||= 'title'; + $o{tagspoil} //= 2; + + # user input that is literally added to the query should be checked... + die "Invalid input for tagspoil or tag_inc at dbVNGet()\n" if + grep !defined($_) || $_!~/^\d+$/, $o{tagspoil}, + !$o{tag_inc} ? () : (ref($o{tag_inc}) ? @{$o{tag_inc}} : $o{tag_inc}); my @where = ( $o{id} ? ( @@ -39,19 +46,20 @@ sub dbVNGet { '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", ref $o{plat} ? @{$o{plat}} : $o{plat}).')' => 1 ) : (), defined $o{hasani} ? ( '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.vid = vr.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (), - $o{tags_include} && @{$o{tags_include}} ? ( + defined $o{hasshot} ? ( + '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.vid = vr.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (), + $o{tag_inc} ? ( '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_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (), + [ ref $o{tag_inc} ? $o{tag_inc} : [$o{tag_inc}], $o{tagspoil}, ref $o{tag_inc} ? $#{$o{tag_inc}}+1 : 1 ]) : (), + $o{tag_exc} ? ( + 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ ref $o{tag_exc} ? $o{tag_exc} : [$o{tag_exc}] ] ) : (), $o{search} ? ( map +('v.c_search like ?', "%$_%"), normalize_query($o{search})) : (), # don't fetch hidden items unless we ask for an ID !$o{id} && !$o{rev} ? ( 'v.hidden = FALSE' => 0 ) : (), # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well) - $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort)$/, keys %o) ? ( + $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort|tagspoil)$/, keys %o) ? ( sprintf 'v.id IN(SELECT floor(random() * last_value)::integer FROM generate_series(1,20), (SELECT last_value FROM vn_id_seq) s1 LIMIT 20)' ) : (), @@ -69,7 +77,7 @@ sub dbVNGet { 'JOIN relgraphs vg ON vg.id = v.rgraph' : (), ); - my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]}; + my $tag_ids = $o{tag_inc} && join ',', ref $o{tag_inc} ? @{$o{tag_inc}} : $o{tag_inc}; my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid', $o{what} =~ /extended/ ? ( @@ -84,7 +92,7 @@ sub dbVNGet { ) : (), # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000) $tag_ids ? - 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| : (), + qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tagspoil} GROUP BY tvh.vid) AS tagscore| : (), ); my $order = sprintf { @@ -216,8 +224,10 @@ sub dbScreenshotGet { # Fetch random VN + screenshots +# if any arguments are given, it will return one random screenshot for each VN sub dbScreenshotRandom { - return shift->dbAll(q| + my($self, @vids) = @_; + return $self->dbAll(q| SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title FROM screenshots s JOIN vn_screenshots vs ON vs.scr = s.id @@ -230,7 +240,22 @@ sub dbScreenshotRandom { LIMIT 20 ) LIMIT 4| - ); + ) if !@vids; + # this query is faster than it looks + return $self->dbAll(join(' UNION ALL ', map + q|SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title, RANDOM() AS position + FROM vn v + JOIN vn_rev vr ON vr.id = v.latest + JOIN vn_screenshots vs ON vs.vid = v.latest + JOIN screenshots s ON s.id = vs.scr + WHERE v.id = ? AND s.id = ( + SELECT vs2.scr + FROM vn_screenshots vs2 + JOIN vn v2 ON v2.latest = vs2.vid + WHERE v2.id = v.id + ORDER BY RANDOM() + LIMIT 1 + )|, @vids).' ORDER BY position', @vids); } |