diff options
author | Yorhel <git@yorhel.nl> | 2019-10-03 10:45:51 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-10-03 10:50:20 +0200 |
commit | 8795f8a55df40603e3e589b584cc5d4c66e78f3a (patch) | |
tree | 7407dd4b1d40a0485a42e0a0ea14fa41a7e74981 /lib/VNWeb | |
parent | 2e9f6f1844131529f553de37eba0bca421a75f8b (diff) |
SQL: Get rid of the users_prefs table, store preferences in users table
This bloats the users table a little bit, but that's fine. The main
advantage of this change is that we now have a proper schema for user
preferences, rather than the schemaless key-value mess we had before.
This commit also splits the 'tags_cat' preference up into tags_cont,
tags_ero and tags_tech bools, as that's more compact to store and easier
to work with.
This commit also changes the 'notify_nodbedit' preference to
'notify_dbedit' with inverted meaning. The reason the value was negated
in the first place was because the old schemaless approach did not
support positive defaults.
Diffstat (limited to 'lib/VNWeb')
-rw-r--r-- | lib/VNWeb/Auth.pm | 27 | ||||
-rw-r--r-- | lib/VNWeb/HTML.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/User/Edit.pm | 16 |
3 files changed, 20 insertions, 27 deletions
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 }; |