From 8795f8a55df40603e3e589b584cc5d4c66e78f3a Mon Sep 17 00:00:00 2001 From: Yorhel 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/Multi/API.pm | 12 +++++----- lib/VNDB/Config.pm | 1 - lib/VNDB/DB/ULists.pm | 9 +++----- lib/VNDB/DB/Users.pm | 37 ++++-------------------------- lib/VNDB/Handler/Users.pm | 13 +++++------ lib/VNDB/Handler/VNPage.pm | 3 +-- lib/VNDB/Util/CommonHTML.pm | 2 +- lib/VNWeb/Auth.pm | 27 ++++++++++------------ lib/VNWeb/HTML.pm | 4 +--- lib/VNWeb/User/Edit.pm | 16 ++++++------- util/dbdump.pl | 13 +++++------ util/sql/data.sql | 6 ++--- util/sql/func.sql | 6 ++--- util/sql/perms.sql | 12 ++++------ util/sql/schema.sql | 49 +++++++++++++++++++++++----------------- util/sql/tableattrs.sql | 1 - util/updates/update_20191003.sql | 40 ++++++++++++++++++++++++++++++++ 17 files changed, 125 insertions(+), 126 deletions(-) create mode 100644 util/updates/update_20191003.sql 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 }; diff --git a/util/dbdump.pl b/util/dbdump.pl index f90cce8b..84e08126 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -59,7 +59,7 @@ my %tables = ( releases_platforms => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' }, releases_producers => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND pid IN(SELECT id FROM producers WHERE NOT hidden)' }, releases_vn => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, - rlists => { where => 'uid NOT IN(SELECT uid FROM users_prefs WHERE key = \'hide_list\') AND rid IN(SELECT id FROM releases WHERE NOT hidden)' }, + rlists => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list) AND rid IN(SELECT id FROM releases WHERE NOT hidden)' }, screenshots => { where => 'id IN(SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden)' }, staff => { where => 'NOT hidden' }, staff_alias => { where => 'id IN(SELECT id FROM staff WHERE NOT hidden)' }, @@ -73,7 +73,7 @@ my %tables = ( # (The 'DISTINCT' isn't necessary, but does make the query faster) # (Users with their votes ignored are still included. W/e) users => { where => q{ - ( id NOT IN(SELECT DISTINCT uid FROM users_prefs WHERE key = 'hide_list') + ( id NOT IN(SELECT DISTINCT id FROM users WHERE hide_list) AND id IN(SELECT DISTINCT uid FROM rlists UNION SELECT DISTINCT uid FROM wlists UNION SELECT DISTINCT uid FROM vnlists @@ -88,14 +88,13 @@ my %tables = ( .' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' .' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' }, vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' }, - vnlists => { where => 'uid NOT IN(SELECT uid FROM users_prefs WHERE key = \'hide_list\') AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, - votes => { where => 'uid NOT IN(SELECT uid FROM users_prefs WHERE key = \'hide_list\')' - .' AND uid NOT IN(SELECT id FROM users WHERE ign_votes)' + vnlists => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, + votes => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list OR ign_votes)' .' AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden UNION SELECT l_wikidata FROM staff WHERE NOT hidden UNION SELECT l_wikidata FROM vn WHERE NOT hidden)} }, - wlists => { where => 'uid NOT IN(SELECT uid FROM users_prefs WHERE key = \'hide_list\') AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, + wlists => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' }, ); my @tables = map +{ name => $_, %{$tables{$_}} }, sort keys %tables; @@ -274,7 +273,7 @@ sub export_votes { JOIN vn v ON v.id = vv.vid WHERE NOT v.hidden AND NOT u.ign_votes - AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE up.uid = u.id AND key = 'hide_list') + AND NOT u.hide_list ORDER BY vv.vid, vv.uid ) TO STDOUT }); diff --git a/util/sql/data.sql b/util/sql/data.sql index b921899b..3283c035 100644 --- a/util/sql/data.sql +++ b/util/sql/data.sql @@ -1,7 +1,5 @@ -INSERT INTO users (id, username, mail, perm) VALUES (0, 'deleted', 'del@vndb.org', 0); -INSERT INTO users (id, username, mail, perm) VALUES (1, 'multi', 'multi@vndb.org', 0); -INSERT INTO users_prefs (uid, key, value) VALUES (0, 'notify_nodbedit', '1'); -INSERT INTO users_prefs (uid, key, value) VALUES (1, 'notify_nodbedit', '1'); +INSERT INTO users (id, username, mail, perm, notify_dbedit) VALUES (0, 'deleted', 'del@vndb.org', 0, FALSE); +INSERT INTO users (id, username, mail, perm, notify_dbedit) VALUES (1, 'multi', 'multi@vndb.org', 0, FALSE); SELECT setval('users_id_seq', 2); INSERT INTO stats_cache (section, count) VALUES diff --git a/util/sql/func.sql b/util/sql/func.sql index 792753be..b06cf5e5 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -684,7 +684,7 @@ CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype) AND h.requester <> h2.requester -- exclude the user who edited the entry AND h2.requester <> 1 -- exclude edits by Multi -- exclude users who don't want this notify - AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit'); + AND EXISTS(SELECT 1 FROM users u WHERE u.id = h.requester AND notify_dbedit); $$ LANGUAGE sql; @@ -714,11 +714,11 @@ $$ LANGUAGE sql; CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ BEGIN INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'announce', 't', up.uid, t.id, 1, t.title, NEw.uid + SELECT 'announce', 't', u.id, t.id, 1, t.title, NEW.uid FROM threads t JOIN threads_boards tb ON tb.tid = t.id -- get the users who want this announcement - JOIN users_prefs up ON up.key = 'notify_announce' + JOIN users u ON u.notify_announce WHERE t.id = NEW.tid AND tb.type = 'an' -- announcement board AND NOT t.hidden; diff --git a/util/sql/perms.sql b/util/sql/perms.sql index 440c8354..f49686fc 100644 --- a/util/sql/perms.sql +++ b/util/sql/perms.sql @@ -64,12 +64,11 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents TO vndb_site; -- users table is special; The 'perm', 'passwd' and 'mail' columns are -- protected and can only be accessed through the user_* functions. -GRANT SELECT (id, username, registered, perm, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed), - INSERT (id, username, mail, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed), - UPDATE ( username, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed) ON users TO vndb_site; +GRANT SELECT (id, username, registered, perm, c_votes, c_changes, ip, c_tags, ign_votes, 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), + INSERT (id, username, mail, registered, c_votes, c_changes, ip, c_tags, ign_votes, 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), + UPDATE ( username, registered, c_votes, c_changes, ip, c_tags, ign_votes, 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) ON users TO vndb_site; GRANT DELETE ON users TO vndb_site; -GRANT SELECT, INSERT, UPDATE, DELETE ON users_prefs TO vndb_site; GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site; GRANT SELECT, INSERT, DELETE ON vn_anime TO vndb_site; GRANT SELECT, INSERT ON vn_anime_hist TO vndb_site; @@ -146,10 +145,9 @@ GRANT SELECT ON threads_posts TO vndb_multi; GRANT SELECT, UPDATE ON traits TO vndb_multi; GRANT SELECT, INSERT, TRUNCATE ON traits_chars TO vndb_multi; GRANT SELECT ON traits_parents TO vndb_multi; -GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, ign_votes, email_confirmed), - UPDATE ( c_votes, c_changes, c_tags ) ON users TO vndb_multi; +GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce), + UPDATE ( c_votes, c_changes, c_tags) ON users TO vndb_multi; GRANT DELETE ON users TO vndb_multi; -GRANT SELECT ON users_prefs TO vndb_multi; GRANT SELECT, UPDATE ON vn TO vndb_multi; GRANT SELECT ON vn_anime TO vndb_multi; GRANT SELECT ON vn_hist TO vndb_multi; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 6eb6d710..fa18937f 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -59,7 +59,6 @@ CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce'); CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't', 's', 'd'); CREATE TYPE platform AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'fmt', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pce', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'swi', 'wii', 'wiu', 'n3d', 'x68', 'xb1', 'xb3', 'xbo', 'web', 'oth'); -CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce', 'vn_list_own', 'vn_list_wish', 'tags_all', 'tags_cat', 'spoilers', 'traits_sexual'); CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng'); CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori'); CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); @@ -700,10 +699,10 @@ CREATE TABLE traits_parents ( -- users CREATE TABLE users ( - id SERIAL NOT NULL PRIMARY KEY, -- [pub] - username varchar(20) NOT NULL UNIQUE, -- [pub] - mail varchar(100) NOT NULL, - perm smallint NOT NULL DEFAULT 1+4+16, + id SERIAL NOT NULL PRIMARY KEY, -- [pub] + username varchar(20) NOT NULL UNIQUE, -- [pub] + mail varchar(100) NOT NULL, + perm smallint NOT NULL DEFAULT 1+4+16, -- Interpretation of the passwd column depends on its length: -- * 20 bytes: Password reset token (sha1(lower_hex(20 bytes of random data))) -- * 46 bytes: scrypt password @@ -713,22 +712,30 @@ CREATE TABLE users ( -- 8 bytes: salt -- 32 bytes: scrypt(passwd, global_salt + salt, N, r, p, 32) -- * Anything else: Invalid, account disabled. - passwd bytea NOT NULL DEFAULT '', - registered timestamptz NOT NULL DEFAULT NOW(), - c_votes integer NOT NULL DEFAULT 0, - c_changes integer NOT NULL DEFAULT 0, - ip inet NOT NULL DEFAULT '0.0.0.0', - c_tags integer NOT NULL DEFAULT 0, - ign_votes boolean NOT NULL DEFAULT FALSE, - email_confirmed boolean NOT NULL DEFAULT FALSE -); - --- users_prefs -CREATE TABLE users_prefs ( - uid integer NOT NULL, - key prefs_key NOT NULL, - value varchar NOT NULL, - PRIMARY KEY(uid, key) + passwd bytea NOT NULL DEFAULT '', + registered timestamptz NOT NULL DEFAULT NOW(), + c_votes integer NOT NULL DEFAULT 0, + c_changes integer NOT NULL DEFAULT 0, + ip inet NOT NULL DEFAULT '0.0.0.0', + c_tags integer NOT NULL DEFAULT 0, + ign_votes boolean NOT NULL DEFAULT FALSE, + email_confirmed boolean NOT NULL DEFAULT FALSE, + skin text NOT NULL DEFAULT '', + customcss text NOT NULL DEFAULT '', + filter_vn text NOT NULL DEFAULT '', + filter_release text NOT NULL DEFAULT '', + show_nsfw boolean NOT NULL DEFAULT FALSE, + hide_list boolean NOT NULL DEFAULT FALSE, + notify_dbedit boolean NOT NULL DEFAULT TRUE, + notify_announce boolean NOT NULL DEFAULT FALSE, + vn_list_own boolean NOT NULL DEFAULT FALSE, + vn_list_wish boolean NOT NULL DEFAULT FALSE, + tags_all boolean NOT NULL DEFAULT FALSE, + tags_cont boolean NOT NULL DEFAULT TRUE, + tags_ero boolean NOT NULL DEFAULT FALSE, + tags_tech boolean NOT NULL DEFAULT TRUE, + spoilers boolean NOT NULL DEFAULT FALSE, + traits_sexual boolean NOT NULL DEFAULT FALSE ); -- vn diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql index f7c60533..53b503ce 100644 --- a/util/sql/tableattrs.sql +++ b/util/sql/tableattrs.sql @@ -67,7 +67,6 @@ ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id); ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id); ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id); -ALTER TABLE users_prefs ADD CONSTRAINT users_prefs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id); ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; diff --git a/util/updates/update_20191003.sql b/util/updates/update_20191003.sql new file mode 100644 index 00000000..ee17363c --- /dev/null +++ b/util/updates/update_20191003.sql @@ -0,0 +1,40 @@ +ALTER TABLE users ADD COLUMN skin text NOT NULL DEFAULT ''; +ALTER TABLE users ADD COLUMN customcss text NOT NULL DEFAULT ''; +ALTER TABLE users ADD COLUMN filter_vn text NOT NULL DEFAULT ''; +ALTER TABLE users ADD COLUMN filter_release text NOT NULL DEFAULT ''; +ALTER TABLE users ADD COLUMN show_nsfw boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN hide_list boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN notify_dbedit boolean NOT NULL DEFAULT TRUE; +ALTER TABLE users ADD COLUMN notify_announce boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN vn_list_own boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN vn_list_wish boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN tags_all boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN tags_cont boolean NOT NULL DEFAULT TRUE; +ALTER TABLE users ADD COLUMN tags_ero boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN tags_tech boolean NOT NULL DEFAULT TRUE; +ALTER TABLE users ADD COLUMN spoilers boolean NOT NULL DEFAULT FALSE; +ALTER TABLE users ADD COLUMN traits_sexual boolean NOT NULL DEFAULT FALSE; + +UPDATE users SET + skin = COALESCE((SELECT value FROM users_prefs WHERE uid = id AND key = 'skin' ), ''), + customcss = COALESCE((SELECT value FROM users_prefs WHERE uid = id AND key = 'customcss' ), ''), + filter_vn = COALESCE((SELECT value FROM users_prefs WHERE uid = id AND key = 'filter_vn' ), ''), + filter_release = COALESCE((SELECT value FROM users_prefs WHERE uid = id AND key = 'filter_release' ), ''), + show_nsfw = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'show_nsfw' ), FALSE), + hide_list = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'hide_list' ), FALSE), + notify_dbedit = COALESCE((SELECT FALSE FROM users_prefs WHERE uid = id AND key = 'notify_nodbedit'), TRUE), -- NOTE: Inverted + notify_announce = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'notify_announce'), FALSE), + vn_list_own = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'vn_list_own' ), FALSE), + vn_list_wish = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'vn_list_wish' ), FALSE), + tags_all = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'tags_all' ), FALSE), + spoilers = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'spoilers' ), FALSE), + traits_sexual = COALESCE((SELECT TRUE FROM users_prefs WHERE uid = id AND key = 'traits_sexual' ), FALSE), + tags_cont = COALESCE((SELECT value LIKE '%cont%' FROM users_prefs WHERE uid = id AND key = 'tags_cat'), TRUE), + tags_ero = COALESCE((SELECT value LIKE '%ero%' FROM users_prefs WHERE uid = id AND key = 'tags_cat'), FALSE), + tags_tech = COALESCE((SELECT value LIKE '%tech%' FROM users_prefs WHERE uid = id AND key = 'tags_cat'), TRUE); + +\i util/sql/func.sql +\i util/sql/perms.sql + +DROP TABLE users_prefs; +DROP TYPE prefs_key; -- cgit v1.2.3