summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-10-03 10:45:51 +0200
committerYorhel <git@yorhel.nl>2019-10-03 10:50:20 +0200
commit8795f8a55df40603e3e589b584cc5d4c66e78f3a (patch)
tree7407dd4b1d40a0485a42e0a0ea14fa41a7e74981 /util
parent2e9f6f1844131529f553de37eba0bca421a75f8b (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 'util')
-rwxr-xr-xutil/dbdump.pl13
-rw-r--r--util/sql/data.sql6
-rw-r--r--util/sql/func.sql6
-rw-r--r--util/sql/perms.sql12
-rw-r--r--util/sql/schema.sql49
-rw-r--r--util/sql/tableattrs.sql1
-rw-r--r--util/updates/update_20191003.sql40
7 files changed, 84 insertions, 43 deletions
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;