1 files changed, 37 insertions, 97 deletions

diff --git a/lib/VNWeb/Images/Vote.pm b/lib/VNWeb/Images/Vote.pm
index a1aa4570..48c1fffb 100644
--- a/lib/VNWeb/Images/Vote.pm
+++ b/lib/VNWeb/Images/Vote.pm
@@ -1,66 +1,7 @@
 package VNWeb::Images::Vote;
 
 use VNWeb::Prelude;
-
-
-# Add signed tokens to the image ist - indicating that the current user is
-# permitted to vote on these images. These tokens ensure that non-moderators
-# can only vote on images that they have been randomly assigned, thus
-# preventing possible abuse when a single person uses multiple accounts to
-# influence the rating of a single image.
-sub enrich_token {
-    my($canvote, $l) = @_;
-    $_->{token} = $canvote || ($_->{votecount} == 0 && auth->permImgvote) ? auth->csrftoken(0, "imgvote-$_->{id}") : undef for @$l;
-}
-
-
-# Does the reverse of enrich_token. Returns true if all tokens validated.
-sub validate_token {
-    my($l) = @_;
-    my $ok = 1;
-    $ok &&= $_->{token} && auth->csrfcheck($_->{token}, "imgvote-$_->{id}") for @$l;
-    $ok;
-}
-
-
-sub enrich_image {
-    my($l) = @_;
-    enrich_merge id => sub { sql q{
-      SELECT i.id, i.width, i.height, i.c_votecount AS votecount
-           , i.c_sexual_avg AS sexual_avg, i.c_sexual_stddev AS sexual_stddev
-           , i.c_violence_avg AS violence_avg, i.c_violence_stddev AS violence_stddev
-           , iv.sexual AS my_sexual, iv.violence AS my_violence
-           , COALESCE(EXISTS(SELECT 1 FROM image_votes iv0 WHERE iv0.id = i.id AND iv0.ignore) AND NOT iv.ignore, FALSE) AS my_overrule
-           , COALESCE('v'||v.id, 'c'||c.id, 'v'||vsv.id) AS entry_id
-           , COALESCE(v.title, c.name, vsv.title) AS entry_title
-        FROM images i
-        LEFT JOIN image_votes iv ON iv.id = i.id AND iv.uid =}, \auth->uid, q{
-        LEFT JOIN vn v ON i.id BETWEEN 'cv1' AND vndbid_max('cv') AND v.image = i.id
-        LEFT JOIN chars c ON i.id BETWEEN 'ch1' AND vndbid_max('ch') AND c.image = i.id
-        LEFT JOIN vn_screenshots vs ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vs.scr = i.id
-        LEFT JOIN vn vsv ON i.id BETWEEN 'sf1' AND vndbid_max('sf') AND vsv.id = vs.id
-       WHERE i.id IN}, $_
-    }, $l;
-
-    enrich votes => id => id => sub { sql '
-        SELECT iv.id, iv.uid, iv.sexual, iv.violence, iv.ignore OR (u.id IS NOT NULL AND NOT u.perm_imgvote) AS ignore, ', sql_user(), '
-          FROM image_votes iv
-          LEFT JOIN users u ON u.id = iv.uid
-         WHERE iv.id IN', $_,
-               auth ? ('AND (iv.uid IS NULL OR iv.uid <> ', \auth->uid, ')') : (), '
-         ORDER BY u.username'
-    }, $l;
-
-    for(@$l) {
-        $_->{entry} = $_->{entry_id} ? { id => $_->{entry_id}, title => $_->{entry_title} } : undef;
-        delete $_->{entry_id};
-        delete $_->{entry_title};
-        for my $v ($_->{votes}->@*) {
-            $v->{user} = xml_string sub { user_ $v }; # Easier than duplicating user_() in Elm
-            delete $v->{$_} for grep /^user_/, keys %$v;
-        }
-    }
-}
+use VNWeb::Images::Lib;
 
 
 my $SEND = form_compile any => {
@@ -74,24 +15,16 @@ my $SEND = form_compile any => {
     nsfw_token => {},
 };
 
+
+sub can_vote { auth->permDbmod || (auth->permImgvote && !global_settings->{lockdown_edit}) }
+
+
 # Fetch a list of images for the user to vote on.
 elm_api Images => $SEND, { excl_voted => { anybool => 1 } }, sub {
     my($data) = @_;
-    return elm_Unauth if !auth->permImgvote;
-
-    state $stats = tuwf->dbRowi('SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE c_weight > 0) AS referenced FROM images');
-
-    # Return an empty set when the user has voted on >90% of the (referenced) images.
-    # Limiting the number of images a user can vote on has two effects:
-    # - When the user has voted on everything, they'd be able to immediately
-    #   vote on newly added images, meaning they can be used to influence votes
-    #   from multiple accounts.
-    # - When a user has voted on a lot of images, the algorithm to select new
-    #   images to vote on will become too slow (need to sample everything to
-    #   find an unvoted image) or may randomly not return images (depending on
-    #   the initial table sample).
-    # (Note: c_imgvotes also counts votes on unreferenced images, so this limit may be a little too strict)
-    return elm_ImageResult [] if $data->{excl_voted} && my_votes() > $stats->{referenced}*0.90;
+    return elm_Unauth if !can_vote;
+
+    state $stats = tuwf->dbRowi('SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE c_weight > 1) AS referenced FROM images');
 
     # Performing a proper weighted sampling on the entire images table is way
     # too slow, so we do a TABLESAMPLE to first randomly select a number of
@@ -100,27 +33,34 @@ elm_api Images => $SEND, { excl_voted => { anybool => 1 } }, sub {
     # hopefully enough to get a good (weighted) sample and should have a good
     # chance at selecting images even when the user has voted on 90%.
     #
-    # Performance can be further improved by adding a 'images.c_uids integer[]'
-    # cache to filter out already voted images faster.
-    my $tablesample = 100 * min 1, (5000 / $stats->{referenced}) * ($stats->{total} / $stats->{referenced});
+    # TABLESAMPLE is not used if there are only few images to select from, i.e.
+    # when the user has already voted on 99% of all images. Finding all
+    # applicable images in that case is slow, but at least there aren't many
+    # rows for the final ORDER BY.
+    my $tablesample =
+        !$data->{excl_voted} || tuwf->dbVali('SELECT c_imgvotes FROM users WHERE id =', \auth->uid) < $stats->{referenced}*0.99
+        ? 100 * min 1, (5000 / $stats->{referenced}) * ($stats->{total} / $stats->{referenced})
+        : 100;
+
+    # NOTE: Elm assumes that, if it receives less than 30 images, we've reached
+    # the end of the list and will not attempt to load more.
     my $l = tuwf->dbAlli('
         SELECT id
-          FROM images i TABLESAMPLE SYSTEM (', \$tablesample, ')
-         WHERE c_weight > 0',
-            $data->{excl_voted} ? ('AND NOT EXISTS(SELECT 1 FROM image_votes iv WHERE iv.id = i.id AND iv.uid =', \auth->uid, ')') : (), '
+          FROM images TABLESAMPLE SYSTEM (', \$tablesample, ')
+         WHERE c_weight > 1',
+            $data->{excl_voted} ? ('AND NOT (c_uids && ARRAY[', \auth->uid, '::vndbid])') : (), '
          ORDER BY random() ^ (1.0/c_weight) DESC
          LIMIT', \30
     );
-    warn sprintf 'Weighted random image sampling query returned %d < 30 rows for u%d with a sample fraction of %f', scalar @$l, auth->uid(), $tablesample if @$l < 30;
-    enrich_image $l;
-    enrich_token 1, $l;
+    warn sprintf 'Weighted random image sampling query returned %d < 30 rows for %s with a sample fraction of %f', scalar @$l, auth->uid(), $tablesample if @$l < 30;
+    enrich_image 1, $l;
     elm_ImageResult $l;
 };
 
 
 elm_api ImageVote => undef, {
     votes => { sort_keys => 'id', aoh => {
-        id       => { regex => qr/^(?:ch|cv|sf)[1-9][0-9]*$/ },
+        id       => { vndbid => [qw/ch cv sf/] },
         token    => {},
         sexual   => { uint => 1, range => [0,2] },
         violence => { uint => 1, range => [0,2] },
@@ -128,16 +68,19 @@ elm_api ImageVote => undef, {
     } },
 }, sub {
     my($data) = @_;
-    return elm_Unauth if !auth->permImgvote;
-    return elm_CSRF if !validate_token $data->{votes};
+    return elm_Unauth if !can_vote;
+    return elm_Unauth if !validate_token $data->{votes};
+
+    # Lock the users table early to prevent deadlock with a concurrent DB edit that attempts to update c_changes.
+    tuwf->dbExeci('SELECT c_imgvotes FROM users WHERE id =', \auth->uid, 'FOR UPDATE');
 
     # Find out if any of these images are being overruled
     enrich_merge id => sub { sql 'SELECT id, bool_or(ignore) AS overruled FROM image_votes WHERE id IN', $_, 'GROUP BY id' }, $data->{votes};
     enrich_merge id => sql('SELECT id, NOT ignore AS my_overrule FROM image_votes WHERE uid =', \auth->uid, 'AND id IN'),
-        grep $_->{overruled}, $data->{votes}->@* if auth->permImgmod;
+        grep $_->{overruled}, $data->{votes}->@* if auth->permDbmod;
 
     for($data->{votes}->@*) {
-        $_->{overrule} = 0 if !auth->permImgmod;
+        $_->{overrule} = 0 if !auth->permDbmod;
         my $d = {
             id       => $_->{id},
             uid      => auth->uid(),
@@ -162,18 +105,17 @@ sub imgflag_ {
     elm_ 'ImageFlagging', $SEND, {
         my_votes   => my_votes(),
         nsfw_token => viewset(show_nsfw => 1),
-        mod        => auth->permImgmod()||0,
+        mod        => auth->permDbmod()||0,
         @_
     };
 }
 
 
 TUWF::get qr{/img/vote}, sub {
-    return tuwf->resDenied if !auth->permImgvote;
+    return tuwf->resDenied if !can_vote;
 
     my $recent = tuwf->dbAlli('SELECT id FROM image_votes WHERE uid =', \auth->uid, 'ORDER BY date DESC LIMIT', \30);
-    enrich_image $recent;
-    enrich_token 1, $recent;
+    enrich_image 1, $recent;
 
     framework_ title => 'Image flagging', sub {
         imgflag_ images => [ reverse @$recent ], single => 0, warn => 1;
@@ -181,15 +123,13 @@ TUWF::get qr{/img/vote}, sub {
 };
 
 
-TUWF::get qr{/img/$RE{imgid}}, sub {
+TUWF::get qr{/$RE{imgid}}, sub {
     my $id = tuwf->capture('id');
 
     my $l = [{ id => $id }];
-    enrich_image $l;
+    enrich_image auth->permDbmod() || sub { defined $_[0]{my_sexual} }, $l;
     return tuwf->resNotFound if !defined $l->[0]{width};
 
-    enrich_token defined($l->[0]{my_sexual}) || auth->permImgmod(), $l;
-
     framework_ title => "Image flagging for $id", sub {
         imgflag_ images => $l, single => 1, warn => !viewget->{show_nsfw};
     };