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.

1 files changed, 40 insertions, 0 deletions

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;