summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
Diffstat (limited to 'lib')
-rw-r--r--lib/Multi/API.pm12
-rw-r--r--lib/VNDB/Config.pm1
-rw-r--r--lib/VNDB/DB/ULists.pm9
-rw-r--r--lib/VNDB/DB/Users.pm37
-rw-r--r--lib/VNDB/Handler/Users.pm13
-rw-r--r--lib/VNDB/Handler/VNPage.pm3
-rw-r--r--lib/VNDB/Util/CommonHTML.pm2
-rw-r--r--lib/VNWeb/Auth.pm27
-rw-r--r--lib/VNWeb/HTML.pm4
-rw-r--r--lib/VNWeb/User/Edit.pm16
10 files changed, 41 insertions, 83 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 3f05b6c8..f8490df3 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -1040,8 +1040,8 @@ my $VN_FILTER = [
my %GET_VOTELIST = (
islist => 1,
- sql => "SELECT %s FROM votes v WHERE (%s) AND NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = v.uid AND key = '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_prefs WHERE uid = v.uid AND key = 'hide_list')) %3$s},
+ 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",
proc => sub {
$_[0]{uid}*=1;
@@ -1057,8 +1057,8 @@ my %GET_VOTELIST = (
my %GET_VNLIST = (
islist => 1,
- sql => "SELECT %s FROM vnlists v WHERE (%s) AND NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = v.uid AND key = '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_prefs WHERE uid = v.uid AND key = 'hide_list')) %3$s},
+ 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",
proc => sub {
$_[0]{uid}*=1;
@@ -1075,8 +1075,8 @@ my %GET_VNLIST = (
my %GET_WISHLIST = (
islist => 1,
- sql => "SELECT %s FROM wlists w WHERE (%s) AND NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = w.uid AND key = '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_prefs WHERE uid = w.uid AND key = 'hide_list')) %3$s},
+ 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",
proc => sub {
$_[0]{uid}*=1;
diff --git a/lib/VNDB/Config.pm b/lib/VNDB/Config.pm
index 9c9a3a63..43303b52 100644
--- a/lib/VNDB/Config.pm
+++ b/lib/VNDB/Config.pm
@@ -24,7 +24,6 @@ my $config = {
source_url => 'http://git.blicky.net/vndb.git/?h=master',
admin_email => 'contact@vndb.org',
login_throttle => [ 24*3600/10, 24*3600 ], # interval between attempts, max burst (10 a day)
- default_tags_cat => 'cont,tech', # Default visible tag categories
board_edit_time => 7*24*3600, # Time after which posts become immutable
poll_options => 20, # max number of options in discussion board polls
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 9c892cf3..6de6b25b 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -195,25 +195,22 @@ sub dbVoteGet {
qw|n.vid n.vote n.uid|, q|extract('epoch' from n.date) as date|,
$o{what} =~ /user/ ? ('u.username') : (),
$o{what} =~ /vn/ ? (qw|v.title v.original|) : (),
- $o{what} =~ /hide_list/ ? ('up.uid as hide_list') : (),
+ $o{what} =~ /hide_list/ ? ('u.hide_list') : (),
);
my @join = (
$o{what} =~ /vn/ ? (
'JOIN vn v ON v.id = n.vid',
) : (),
- $o{what} =~ /user/ || $o{hide} ? (
+ $o{what} =~ /user/ || $o{hide} || $o{what} =~ /hide_list/ ? (
'JOIN users u ON u.id = n.uid'
) : (),
- $o{what} =~ /hide_list/ ? (
- 'LEFT JOIN users_prefs up ON up.uid = n.uid AND key = \'hide_list\''
- ) : (),
);
my $order = sprintf {
date => 'n.date %s',
# Hidden users should not be sorted among the rest. as that would still give them away
- username => $o{what} =~ /hide_list/ ? '(CASE WHEN up.uid IS NULL THEN u.username ELSE NULL END) %s, n.date' : 'u.username %s',
+ username => $o{what} =~ /hide_list/ ? '(CASE WHEN u.hide_list THEN NULL ELSE u.username END) %s, n.date' : 'u.username %s',
title => 'v.title %s',
vote => 'n.vote %s'.($o{what} =~ /vn/ ? ', v.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''),
}->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 70864fca..40f3404e 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -6,7 +6,7 @@ use warnings;
use Exporter 'import';
our @EXPORT = qw|
- dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserPrefSet dbUserLogout
+ dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserLogout
dbUserEmailExists dbUserGetMail dbUserSetMail dbUserSetPerm
dbNotifyGet dbNotifyMarkRead dbNotifyRemove
dbThrottleGet dbThrottleSet
@@ -14,7 +14,7 @@ our @EXPORT = qw|
# %options->{ username session uid ip registered search results page what sort reverse notperm }
-# what: notifycount stats scryptargs extended prefs hide_list
+# what: notifycount stats scryptargs extended
# sort: username registered votes changes tags
sub dbUserGet {
my $s = shift;
@@ -54,10 +54,9 @@ sub dbUserGet {
);
my @select = (
- qw|id username c_votes c_changes c_tags|,
+ qw|id username c_votes c_changes c_tags hide_list|,
q|extract('epoch' from registered) as registered|,
$o{what} =~ /extended/ ? qw|perm ign_votes| : (), # mail
- $o{what} =~ /hide_list/ ? 'up.value AS hide_list' : (),
$o{what} =~ /scryptargs/ ? 'user_getscryptargs(id) AS scryptargs' : (),
$o{what} =~ /notifycount/ ?
'(SELECT COUNT(*) FROM notifications WHERE uid = u.id AND read IS NULL) AS notifycount' : (),
@@ -72,11 +71,6 @@ sub dbUserGet {
$token ? qq|extract('epoch' from user_isloggedin(id, decode('$token', 'hex'))) as session_lastused| : (),
);
- my @join = (
- $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 {
id => 'u.id %s',
username => 'u.username %s',
@@ -89,25 +83,11 @@ sub dbUserGet {
my($r, $np) = $s->dbPage(\%o, q|
SELECT !s
FROM users u
- !s
!W
ORDER BY !s|,
- join(', ', @select), join(' ', @join), \%where, $order
+ join(', ', @select), \%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;
}
@@ -141,15 +121,6 @@ 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);
-}
-
-
# uid, token
sub dbUserLogout {
$_[0]->dbExec(q|SELECT user_logout(?, decode(?, 'hex'))|, $_[1], unpack 'H*', $_[2]);
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index 2557b9d1..ebc037f2 100644
--- a/lib/VNDB/Handler/Users.pm
+++ b/lib/VNDB/Handler/Users.pm
@@ -322,11 +322,11 @@ sub notifies {
if($self->reqMethod() eq 'POST' && $self->reqPost('set')) {
return if !$self->authCheckCode;
my $frm = $self->formValidate(
- { post => 'notify_nodbedit', required => 0, default => 1, enum => [0,1] },
+ { post => 'notify_dbedit', required => 0, default => 0, enum => [0,1] },
{ post => 'notify_announce', required => 0, default => 0, enum => [0,1] }
);
return $self->resNotFound if $frm->{_err};
- $self->authPref($_, $frm->{$_}) for ('notify_nodbedit', 'notify_announce');
+ $self->authPref($_, $frm->{$_}) for ('notify_dbedit', 'notify_announce');
$saved = 1;
# updating notifications
@@ -428,11 +428,10 @@ sub notifies {
h1 'Settings';
div class => 'notice', 'Settings successfully saved.' if $saved;
p;
- for('nodbedit', 'announce') {
- my $def = $_ eq 'nodbedit' ? 0 : 1;
- input type => 'checkbox', name => "notify_$_", id => "notify_$_", value => $def,
- ($self->authPref("notify_$_")||0) == $def ? (checked => 'checked') : ();
- label for => "notify_$_", $_ eq 'nodbedit'
+ for('dbedit', 'announce') {
+ input type => 'checkbox', name => "notify_$_", id => "notify_$_", value => 1,
+ $self->authPref("notify_$_") ? (checked => 'checked') : ();
+ label for => "notify_$_", $_ eq 'dbedit'
? ' Notify me about edits of database entries I contributed to.'
: ' Notify me about site announcements.';
br;
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index 90597101..d716c74e 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -456,9 +456,8 @@ sub page {
my $t = $self->dbTagStats(vid => $v->{id}, sort => 'rating', reverse => 1, minrating => 0, results => 999, state => 2);
if(@$t) {
div id => 'tagops';
- my $tags_cat = $self->authPref('tags_cat') || $self->{default_tags_cat};
for (keys %TAG_CATEGORY) {
- input id => "cat_$_", type => 'checkbox', class => 'visuallyhidden', $tags_cat =~ /\Q$_/ ? (checked => 'checked') : ();
+ input id => "cat_$_", type => 'checkbox', class => 'visuallyhidden', $self->authPref("tags_$_") ? (checked => 'checked') : ();
label for => "cat_$_", lc $TAG_CATEGORY{$_};
}
my $spoiler = $self->authPref('spoilers') || 0;
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 7a6e8620..d8e584ab 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -46,7 +46,7 @@ sub htmlMainTabs {
end;
}
- if($type eq 'u' && (!($obj->{hide_list} || $obj->{prefs}{hide_list}) || ($self->authInfo->{id} && $self->authInfo->{id} == $obj->{id}) || $self->authCan('usermod'))) {
+ if($type eq 'u' && (!$obj->{hide_list} || ($self->authInfo->{id} && $self->authInfo->{id} == $obj->{id}) || $self->authCan('usermod'))) {
li $sel eq 'wish' ? (class => 'tabselected') : ();
a href => "/$id/wish", 'wishlist';
end;
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index 35840680..306bb64c 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -150,7 +150,6 @@ sub _load_session {
my($self, $uid, $token_db) = @_;
my $user = {};
- my %pref = ();
if($uid) {
my $loggedin = sql_func(user_isloggedin => 'id', sql_fromhex($token_db));
$user = tuwf->dbRowi(
@@ -280,32 +279,30 @@ sub csrfcheck {
}
-# Returns a value from 'users_prefs' for the current user. Lazily loads all
+# TODO: Measure global usage of the pref() and prefSet() calls to see if this cache is actually necessary.
+
+my @pref_columns = qw/
+ email_confirmed skin customcss filter_vn filter_release show_nsfw hide_list notify_dbedit notify_announce
+ vn_list_own vn_list_wish tags_all tags_cont tags_ero tags_tech spoilers traits_sexual
+/;
+
+# Returns a user preference column for the current user. Lazily loads all
# preferences to speed of subsequent calls.
sub pref {
my($self, $key) = @_;
return undef if !$self->uid;
- $self->{pref} ||= { map +($_->{key}, $_->{value}), @{ tuwf->dbAlli(
- 'SELECT key, value FROM users_prefs WHERE uid =', \$self->uid
- ) } };
+ $self->{pref} ||= tuwf->dbRowi('SELECT', sql_comma(map "\"$_\"", @pref_columns), 'FROM users WHERE id =', \$self->uid);
$self->{pref}{$key};
}
sub prefSet {
my($self, $key, $value, $uid) = @_;
+ die "Unknown pref key: $_" if !grep $key eq $_, @pref_columns;
$uid //= $self->uid;
- if($value) {
- $self->{pref}{$key} = $value;
- tuwf->dbExeci(
- 'INSERT INTO users_prefs', { uid => $uid, key => $key, value => $value },
- 'ON CONFLICT (uid,key) DO UPDATE SET', { value => $value }
- );
- } else {
- delete $self->{pref}{$key};
- tuwf->dbExeci('DELETE FROM users_prefs WHERE', { uid => $uid, key => $key });
- }
+ $self->{pref}{$key} = $value;
+ tuwf->dbExeci(qq{UPDATE users SET "$key" =}, \$value, 'WHERE id =', \$self->uid);
}
diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index e84f009c..28002dcb 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -285,9 +285,7 @@ sub _maintabs_ {
t list => "/$id/list", 'list';
} if $t eq 'u' && (
auth->permUsermod || (auth && auth->uid == $o->{id})
- || !(exists $o->{hide_list}
- ? $o->{hide_list}
- : tuwf->dbVali('SELECT value FROM users_prefs WHERE', { uid => $o->{id}, key => 'hide_list' }))
+ || !($o->{hide_list} // tuwf->dbVali('SELECT hide_list FROM users WHERE id =', \$o->{id}))
);
t tagmod => "/$id/tagmod", 'modify tags' if $t eq 'v' && auth->permTag && !$o->{entry_hidden};
diff --git a/lib/VNWeb/User/Edit.pm b/lib/VNWeb/User/Edit.pm
index 1af1c6c8..8b1f1ea2 100644
--- a/lib/VNWeb/User/Edit.pm
+++ b/lib/VNWeb/User/Edit.pm
@@ -35,13 +35,18 @@ elm_form UserEdit => undef, $FORM;
TUWF::get qr{/$RE{uid}/edit}, sub {
- my $u = tuwf->dbRowi('SELECT id, username, perm, ign_votes FROM users WHERE id =', \tuwf->capture('id'));
+ my $u = tuwf->dbRowi(q{
+ SELECT id, username, perm, ign_votes, hide_list, show_nsfw, traits_sexual,
+ tags_all, tags_cont, tags_ero, tags_tech, spoilers, skin, customcss
+ FROM users WHERE id =}, \tuwf->capture('id')
+ );
return tuwf->resNotFound if !can_edit u => $u;
$u->{email} = tuwf->dbVali(select => sql_func user_getmail => \$u->{id}, \auth->uid, sql_fromhex auth->token);
$u->{authmod} = auth->permUsermod;
$u->{password} = undef;
+ $u->{skin} ||= config->{skin_default};
# Let's not disclose this (though it's not hard to find out through other means)
if(!auth->permUsermod) {
@@ -49,12 +54,6 @@ TUWF::get qr{/$RE{uid}/edit}, sub {
$u->{perm} = auth->defaultPerms;
}
- my $prefs = { map +($_->{key}, $_->{value}), @{ tuwf->dbAlli('SELECT key, value FROM users_prefs WHERE uid =', \$u->{id}) }};
- $u->{$_} = $prefs->{$_}||'' for qw/hide_list show_nsfw traits_sexual tags_all spoilers skin customcss/;
- $u->{spoilers} ||= 0;
- $u->{skin} ||= config->{skin_default};
- $u->{"tags_$_"} = (($prefs->{tags_cat}||'cont,tech') =~ /$_/) for qw/cont ero tech/;
-
my $title = $u->{id} == auth->uid ? 'My Account' : "Edit $u->{username}";
framework_ title => $title, index => 0, type => 'u', dbobj => $u, tab => 'edit',
sub {
@@ -92,8 +91,7 @@ json_api qr{/u/edit}, $FORM, sub {
tuwf->dbExeci(select => sql_func user_setmail => \$data->{id}, \auth->uid, sql_fromhex(auth->token), \$data->{email});
$data->{skin} = '' if $data->{skin} eq config->{skin_default};
- auth->prefSet($_, $data->{$_}, $data->{id}) for qw/hide_list show_nsfw traits_sexual tags_all spoilers skin customcss/;
- auth->prefSet(tags_cat => join(',', map $data->{"tags_$_"} ? $_ : (), qw/cont ero tech/), $data->{id});
+ auth->prefSet($_, $data->{$_}, $data->{id}) for qw/hide_list show_nsfw traits_sexual tags_all tags_cont tags_ero tags_tech spoilers skin customcss/;
elm_Success
};
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 04:03:45 +0000