summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r--lib/VNDB/DB/Releases.pm11
-rw-r--r--lib/VNDB/DB/Tags.pm2
-rw-r--r--lib/VNDB/DB/ULists.pm138
-rw-r--r--lib/VNDB/DB/Users.pm39
-rw-r--r--lib/VNDB/DB/VN.pm47
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);
}