summaryrefslogtreecommitdiff
path: root/lib/Multi
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-30 15:20:00 +0100
committerYorhel <git@yorhel.nl>2019-12-30 15:20:00 +0100
commitd1bb5b82255c764edecc659c78d5b9f4e36555e8 (patch)
tree53058ac9a25d0e82968da77e28f46d1137204e6b /lib/Multi
parent13287329e70cbaf155c85e3054f2496411e21b21 (diff)
parentddb0d385eeb112de6e544adefbbac1cb0b8a957a (diff)
Merge branch 'ulist'
Diffstat (limited to 'lib/Multi')
-rw-r--r--lib/Multi/API.pm119
-rw-r--r--lib/Multi/Maintenance.pm41
2 files changed, 85 insertions, 75 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 5cb68c1b..120c3341 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -1036,20 +1036,22 @@ my %GET_USER = (
# the uid filter for votelist/vnlist/wishlist
-my $UID_FILTER = [ 'int' => 'uid :op: :value:', {qw|= =|}, range => [0,1e6], process => \&subst_user_id ];
+my $UID_FILTER = [ 'int' => 'uv.uid :op: :value:', {qw|= =|}, range => [0,1e6], process => \&subst_user_id ];
# Similarly, a filter for 'vid'
my $VN_FILTER = [
- [ 'int' => 'vid :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, range => [1,1e6] ],
- [ inta => 'vid :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, range => [1,1e6], join => ',' ],
+ [ 'int' => 'uv.vid :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, range => [1,1e6] ],
+ [ inta => 'uv.vid :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, range => [1,1e6], join => ',' ],
];
+my $UV_PUBLIC = 'EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND NOT ul.private)';
+
my %GET_VOTELIST = (
islist => 1,
- sql => "SELECT %s FROM votes v WHERE (%s) AND NOT EXISTS(SELECT 1 FROM users WHERE id = v.uid AND hide_list) %s",
- sqluser => q{SELECT %1$s FROM votes v WHERE (%2$s) AND (uid = %4$d OR NOT EXISTS(SELECT 1 FROM users WHERE id = v.uid AND hide_list)) %3$s},
- select => "uid, vid as vn, vote, extract('epoch' from date) AS added",
+ sql => "SELECT %s FROM ulist_vns uv WHERE uv.vote IS NOT NULL AND (%s) AND $UV_PUBLIC %s",
+ sqluser => "SELECT %1\$s FROM ulist_vns uv WHERE uv.vote IS NOT NULL AND (%2\$s) AND (uid = %4\$d OR $UV_PUBLIC) %3\$s",
+ select => "uid, vid as vn, vote, extract('epoch' from vote_date) AS added",
proc => sub {
$_[0]{uid}*=1;
$_[0]{vn}*=1;
@@ -1062,37 +1064,44 @@ my %GET_VOTELIST = (
filters => { uid => [ $UID_FILTER ], vn => $VN_FILTER }
);
+my $SQL_VNLIST = 'FROM ulist_vns uv LEFT JOIN ulist_vns_labels uvl ON uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl IN(1,2,3,4)'
+ .' WHERE (EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid AND uvl.lbl IN(1,2,3,4))'
+ .' OR NOT EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.uid = uv.uid AND uvl.vid = uv.vid))';
+
my %GET_VNLIST = (
islist => 1,
- sql => "SELECT %s FROM vnlists v WHERE (%s) AND NOT EXISTS(SELECT 1 FROM users WHERE id = v.uid AND hide_list) %s",
- sqluser => q{SELECT %1$s FROM vnlists v WHERE (%2$s) AND (uid = %4$d OR NOT EXISTS(SELECT 1 FROM users WHERE id = v.uid AND hide_list)) %3$s},
- select => "uid, vid as vn, status, extract('epoch' from added) AS added, notes",
+ sql => "SELECT %s $SQL_VNLIST AND (%s) AND $UV_PUBLIC GROUP BY uv.uid, uv.vid, uv.added, uv.notes %s",
+ sqluser => "SELECT %1\$s $SQL_VNLIST AND (%2\$s) AND (uv.uid = %4\$d OR $UV_PUBLIC) GROUP BY uv.uid, uv.vid, uv.added, uv.notes %3\$s",
+ select => "uv.uid, uv.vid as vn, MAX(uvl.lbl) AS status, extract('epoch' from uv.added) AS added, uv.notes",
proc => sub {
$_[0]{uid}*=1;
$_[0]{vn}*=1;
- $_[0]{status}*=1;
+ $_[0]{status} = defined $_[0]{status} ? $_[0]{status}*1 : undef;
$_[0]{added} = int $_[0]{added};
$_[0]{notes} ||= undef;
},
sortdef => 'vn',
- sorts => { vn => 'vid %s' },
+ sorts => { vn => 'uv.vid %s' },
flags => { basic => {} },
filters => { uid => [ $UID_FILTER ], vn => $VN_FILTER }
);
+my $SQL_WISHLIST = "FROM ulist_vns uv JOIN ulist_vns_labels uvl ON uvl.uid = uv.uid AND uvl.vid = uv.vid JOIN ulist_labels ul ON ul.uid = uv.uid AND ul.id = uvl.lbl"
+ ." WHERE (uvl.lbl IN(5,6) OR ul.label IN('Wishlist-Low','Wishlist-Medium','Wishlist-High'))";
+
my %GET_WISHLIST = (
islist => 1,
- sql => "SELECT %s FROM wlists w WHERE (%s) AND NOT EXISTS(SELECT 1 FROM users WHERE id = w.uid AND hide_list) %s",
- sqluser => q{SELECT %1$s FROM wlists w WHERE (%2$s) AND (uid = %4$d OR NOT EXISTS(SELECT 1 FROM users WHERE id = w.uid AND hide_list)) %3$s},
- select => "uid, vid AS vn, wstat AS priority, extract('epoch' from added) AS added",
+ sql => "SELECT %s $SQL_WISHLIST AND (%s) AND NOT ul.private GROUP BY uv.uid, uv.vid, uv.added %s",
+ sqluser => "SELECT %1\$s $SQL_WISHLIST AND (%2\$s) AND (uv.uid = %4\$d OR NOT ul.private) GROUP BY uv.uid, uv.vid, uv.added %3\$s",
+ select => "uv.uid, uv.vid AS vn, MAX(ul.label) AS priority, extract('epoch' from uv.added) AS added",
proc => sub {
$_[0]{uid}*=1;
$_[0]{vn}*=1;
- $_[0]{priority}*=1;
+ $_[0]{priority} = {'Wishlist-High' => 0, 'Wishlist-Medium' => 1, 'Wishlist-Low' => 2, 'Blacklist' => 3}->{$_[0]{priority}}//1;
$_[0]{added} = int $_[0]{added};
},
sortdef => 'vn',
- sorts => { vn => 'vid %s' },
+ sorts => { vn => 'uv.vid %s' },
flags => { basic => {} },
filters => { uid => [ $UID_FILTER ], vn => $VN_FILTER }
);
@@ -1427,28 +1436,35 @@ sub setpg {
};
}
+sub set_ulist_ret {
+ my($c, $obj) = @_;
+ setpg $obj, 'SELECT update_users_ulist_stats($1)', [ $c->{uid} ]; # XXX: This can be deferred, to speed up batch updates over the same connection
+}
+
sub set_votelist {
my($c, $obj) = @_;
- return setpg $obj, 'DELETE FROM votes WHERE uid = $1 AND vid = $2',
- [ $c->{uid}, $obj->{id} ] if !$obj->{opt};
+ return cpg $c, 'UPDATE ulist_vns SET vote = NULL, vote_date = NULL WHERE uid = $1 AND vid = $2', [ $c->{uid}, $obj->{id} ], sub {
+ set_ulist_ret $c, $obj
+ } if !$obj->{opt};
my($ev, $vv) = (exists($obj->{opt}{vote}), $obj->{opt}{vote});
return cerr $c, missing => 'No vote given', field => 'vote' if !$ev;
return cerr $c, badarg => 'Invalid vote', field => 'vote' if ref($vv) || !defined($vv) || $vv !~ /^\d+$/ || $vv < 10 || $vv > 100;
- setpg $obj, 'WITH upsert AS (UPDATE votes SET vote = $1 WHERE uid = $2 AND vid = $3 RETURNING vid)
- INSERT INTO votes (vote, uid, vid) SELECT $1, $2, $3 WHERE EXISTS(SELECT 1 FROM vn v WHERE v.id = $3) AND NOT EXISTS(SELECT 1 FROM upsert)',
- [ $vv, $c->{uid}, $obj->{id} ];
+ cpg $c, 'INSERT INTO ulist_vns (uid, vid, vote, vote_date) VALUES ($1, $2, $3, NOW()) ON CONFLICT (uid, vid) DO UPDATE SET vote = $3, vote_date = NOW(), lastmod = NOW()',
+ [ $c->{uid}, $obj->{id}, $vv ], sub { set_ulist_ret $c, $obj; }
}
sub set_vnlist {
my($c, $obj) = @_;
- return setpg $obj, 'DELETE FROM vnlists WHERE uid = $1 AND vid = $2',
- [ $c->{uid}, $obj->{id} ] if !$obj->{opt};
+ # Bug: Also removes from wishlist and votelist.
+ return cpg $c, 'DELETE FROM ulist_vns WHERE uid = $1 AND vid = $2', [ $c->{uid}, $obj->{id} ], sub {
+ set_ulist_ret $c, $obj;
+ } if !$obj->{opt};
my($es, $en, $vs, $vn) = (exists($obj->{opt}{status}), exists($obj->{opt}{notes}), $obj->{opt}{status}, $obj->{opt}{notes});
return cerr $c, missing => 'No status or notes given', field => 'status,notes' if !$es && !$en;
@@ -1458,34 +1474,63 @@ sub set_vnlist {
$vs ||= 0;
$vn ||= '';
- my $set = join ', ', $es ? 'status = $3' : (), $en ? 'notes = $4' : ();
- setpg $obj, 'WITH upsert AS (UPDATE vnlists SET '.$set.' WHERE uid = $1 AND vid = $2 RETURNING vid)
- INSERT INTO vnlists (uid, vid, status, notes) SELECT $1, $2, $3, $4 WHERE EXISTS(SELECT 1 FROM vn v WHERE v.id = $2) AND NOT EXISTS(SELECT 1 FROM upsert)',
- [ $c->{uid}, $obj->{id}, $vs, $vn ];
+ cpg $c, 'INSERT INTO ulist_vns (uid, vid, notes) VALUES ($1, $2, $3) ON CONFLICT (uid, vid) DO UPDATE SET lastmod = NOW()'.($en ? ', notes = $3' : ''),
+ [ $c->{uid}, $obj->{id}, $vn ], sub {
+ if($es) {
+ cpg $c, 'DELETE FROM ulist_vns_labels WHERE uid = $1 AND vid = $2 AND lbl IN(1,2,3,4)', [ $c->{uid}, $obj->{id} ], sub {
+ if($vs) {
+ cpg $c, 'INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES($1, $2, $3)', [ $c->{uid}, $obj->{id}, $vs ], sub {
+ set_ulist_ret $c, $obj;
+ }
+ } else {
+ set_ulist_ret $c, $obj;
+ }
+ }
+ } else {
+ set_ulist_ret $c, $obj;
+ }
+ }
}
sub set_wishlist {
my($c, $obj) = @_;
- return setpg $obj, 'DELETE FROM wlists WHERE uid = $1 AND vid = $2',
- [ $c->{uid}, $obj->{id} ] if !$obj->{opt};
+ my $sql_label = "(lbl IN(5,6) OR lbl IN(SELECT id FROM ulist_labels WHERE uid = \$1 AND label IN('Wishlist-Low','Wishlist-High','Wishlist-Medium')))";
+
+ # Bug: This will make it appear in the vnlist
+ return cpg $c, "DELETE FROM ulist_vns_labels WHERE uid = \$1 AND vid = \$2 AND $sql_label",
+ [ $c->{uid}, $obj->{id} ], sub {
+ set_ulist_ret $c, $obj;
+ } if !$obj->{opt};
my($ep, $vp) = (exists($obj->{opt}{priority}), $obj->{opt}{priority});
return cerr $c, missing => 'No priority given', field => 'priority' if !$ep;
return cerr $c, badarg => 'Invalid priority', field => 'priority' if ref($vp) || !defined($vp) || $vp !~ /^[0-3]$/;
- setpg $obj, 'WITH upsert AS (UPDATE wlists SET wstat = $1 WHERE uid = $2 AND vid = $3 RETURNING vid)
- INSERT INTO wlists (wstat, uid, vid) SELECT $1, $2, $3 WHERE EXISTS(SELECT 1 FROM vn v WHERE v.id = $3) AND NOT EXISTS(SELECT 1 FROM upsert)',
- [ $vp, $c->{uid}, $obj->{id} ];
+ # Bug: High/Med/Low statuses are only set if a Wishlist-(High|Medium|Low) label exists; These should probably be created if they don't.
+ cpg $c, 'INSERT INTO ulist_vns (uid, vid) VALUES ($1, $2) ON CONFLICT DO NOTHING', [ $c->{uid}, $obj->{id} ], sub {
+ cpg $c, "DELETE FROM ulist_vns_labels WHERE uid = \$1 AND vid = \$2 AND $sql_label", [ $c->{uid}, $obj->{id} ], sub {
+ cpg $c, 'INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES($1, $2, $3)', [ $c->{uid}, $obj->{id}, $vp == 3 ? 6 : 5 ], sub {
+ if($vp != 3) {
+ cpg $c, 'INSERT INTO ulist_vns_labels (uid, vid, lbl) SELECT $1, $2, id FROM ulist_labels WHERE uid = $1 AND label = $3',
+ [ $c->{uid}, $obj->{id}, ['Wishlist-High', 'Wishlist-Medium', 'Wishlist-Low']->[$vp] ], sub {
+ set_ulist_ret $c, $obj;
+ }
+ } else {
+ set_ulist_ret $c, $obj;
+ }
+ }
+ }
+ }
}
-
sub set_ulist {
my($c, $obj) = @_;
- return setpg $obj, 'DELETE FROM ulist_vns WHERE uid = $1 AND vid = $2',
- [ $c->{uid}, $obj->{id} ] if !$obj->{opt};
+ return cpg $c, 'DELETE FROM ulist_vns WHERE uid = $1 AND vid = $2', [ $c->{uid}, $obj->{id} ], sub {
+ set_ulist_ret $c, $obj;
+ } if !$obj->{opt};
my $opt = $obj->{opt};
my @set;
@@ -1533,7 +1578,9 @@ sub set_ulist {
return cerr $c, missing => 'No fields to change' if !@set;
cpg $c, 'INSERT INTO ulist_vns (uid, vid) VALUES ($1, $2) ON CONFLICT (uid, vid) DO NOTHING', [ $c->{uid}, $obj->{id} ], sub {
- setpg $obj, 'UPDATE ulist_vns SET '.join(',', @set).' WHERE uid = $1 AND vid = $2', \@bind;
+ cpg $c, 'UPDATE ulist_vns SET '.join(',', @set).' WHERE uid = $1 AND vid = $2', \@bind, sub {
+ set_ulist_ret $c, $obj;
+ }
};
}
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index e19c1890..abed87a6 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -71,18 +71,8 @@ my %dailies = (
# takes about 25 seconds, OK
traitcache => 'SELECT traits_chars_calc(NULL)',
- # takes about 140 seconds, not really OK
- vnpopularity => 'SELECT update_vnpopularity()',
-
- # takes about 3 seconds, can be performed in ranges as well when necessary
- vnrating => q|
- UPDATE vn SET
- c_rating = (SELECT (
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) /
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
- ) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
- ),
- c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)), 0)|,
+ # takes about 4 seconds, OK
+ vnstats => 'SELECT update_vnvotestats()',
# should be pretty fast
cleangraphs => q|
@@ -214,30 +204,3 @@ sub vnsearch_update { # id, res, time
1;
-
-__END__
-
-# Shouldn't really be necessary, except c_changes could be slightly off when
-# hiding/unhiding DB items.
-# This query takes almost two hours to complete and tends to bring the entire
-# site down with it, so it's been disabled for now. Can be performed in
-# ranges though.
-UPDATE users SET
- c_votes = COALESCE(
- (SELECT COUNT(vid)
- FROM votes
- WHERE uid = users.id
- GROUP BY uid
- ), 0),
- c_changes = COALESCE(
- (SELECT COUNT(id)
- FROM changes
- WHERE requester = users.id
- GROUP BY requester
- ), 0),
- c_tags = COALESCE(
- (SELECT COUNT(tag)
- FROM tags_vn
- WHERE uid = users.id
- GROUP BY uid
- ), 0)