From 8795f8a55df40603e3e589b584cc5d4c66e78f3a Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Thu, 3 Oct 2019 10:45:51 +0200
Subject: 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.
---
 lib/VNWeb/Auth.pm | 27 ++++++++++++---------------
 1 file changed, 12 insertions(+), 15 deletions(-)

(limited to 'lib/VNWeb/Auth.pm')

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);
 }
 
 
-- 
cgit v1.2.3