Added users_prefs table and removed users.(skin|customcss)

Will convert the other preferences later.

4 files changed, 31 insertions, 3 deletions

diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index b785f582..f7cb9923 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -23,7 +23,7 @@ my %subgraphs = (
   'Producers'        => [qw| FFFFCC producers producers_rev producers_relations |],
   'Releases'         => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |],
   'Visual Novels'    => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots |],
-  'Users'            => [qw| CCFFFF users votes rlists wlists vnlists sessions notifications |],
+  'Users'            => [qw| CCFFFF users votes rlists wlists vnlists sessions notifications users_prefs |],
   'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |],
   'Tags'             => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |],
   'Misc'             => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |],
diff --git a/util/sql/all.sql b/util/sql/all.sql
index 8f55ed88..6ecb96f1 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -10,6 +10,7 @@ CREATE TYPE language          AS ENUM('cs', 'da', 'de', 'en', 'es', 'fi', 'fr',
 CREATE TYPE medium            AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
 CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
 CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't');
+CREATE TYPE prefs_key         AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', 'hide_list', 'notify_dbedit', 'notify_announce');
 CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
 CREATE TYPE release_type      AS ENUM ('complete', 'partial', 'trial');
 CREATE TYPE vn_relation       AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index eff5edde..02fb7d60 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -270,8 +270,6 @@ CREATE TABLE users (
   show_list boolean NOT NULL DEFAULT TRUE,
   c_votes integer NOT NULL DEFAULT 0,
   c_changes integer NOT NULL DEFAULT 0,
-  skin varchar(128) NOT NULL DEFAULT '',
-  customcss text NOT NULL DEFAULT '',
   ip inet NOT NULL DEFAULT '0.0.0.0',
   c_tags integer NOT NULL DEFAULT 0,
   salt character(9) NOT NULL DEFAULT '',
@@ -280,6 +278,14 @@ CREATE TABLE users (
   notify_announce 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)
+);
+
 -- vn
 CREATE TABLE vn (
   id SERIAL NOT NULL PRIMARY KEY,
@@ -404,6 +410,7 @@ ALTER TABLE threads             ADD FOREIGN KEY (id, count) REFERENCES threads_p
 ALTER TABLE threads_posts       ADD FOREIGN KEY (tid)       REFERENCES threads       (id);
 ALTER TABLE threads_posts       ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE threads_boards      ADD FOREIGN KEY (tid)       REFERENCES threads       (id);
+ALTER TABLE users_prefs         ADD FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
 ALTER TABLE vn                  ADD FOREIGN KEY (latest)    REFERENCES vn_rev        (id) DEFERRABLE INITIALLY DEFERRED;
 ALTER TABLE vn                  ADD FOREIGN KEY (rgraph)    REFERENCES relgraphs     (id);
 ALTER TABLE vn_anime            ADD FOREIGN KEY (aid)       REFERENCES anime         (id);
diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql
index 9ff96b32..86654c72 100644
--- a/util/updates/update_2.16.sql
+++ b/util/updates/update_2.16.sql
@@ -47,3 +47,23 @@ CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists  DEFERRABLE
 ALTER TABLE rlists DROP COLUMN vstat;
 ALTER TABLE rlists RENAME COLUMN rstat TO status;
 
+
+
+-- add users_prefs table
+CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', 'hide_list', 'notify_dbedit', 'notify_announce');
+CREATE TABLE users_prefs (
+  uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
+  key prefs_key NOT NULL,
+  value varchar NOT NULL,
+  PRIMARY KEY(uid, key)
+);
+
+-- convert from users.* to users_prefs
+INSERT INTO users_prefs (uid, key, value)
+    SELECT id, 'skin'::prefs_key, skin FROM users WHERE skin <> ''
+  UNION
+    SELECT id, 'customcss', customcss FROM users WHERE customcss <> '';
+
+ALTER TABLE users DROP COLUMN skin;
+ALTER TABLE users DROP COLUMN customcss;
+