summaryrefslogtreecommitdiff
path: root/util/sql/schema.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/sql/schema.sql')
-rw-r--r--util/sql/schema.sql69
1 files changed, 37 insertions, 32 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index aa380aa4..3bc27c77 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -704,6 +704,37 @@ CREATE TABLE traits_parents (
PRIMARY KEY(trait, parent)
);
+-- ulist_labels
+CREATE TABLE ulist_labels (
+ uid integer NOT NULL, -- [pub] user.id
+ id integer NOT NULL, -- [pub] 0 < builtin < 10 <= custom, ids are reused
+ label text NOT NULL, -- [pub]
+ private boolean NOT NULL,
+ PRIMARY KEY(uid, id)
+);
+
+-- ulist_vns
+CREATE TABLE ulist_vns (
+ uid integer NOT NULL, -- [pub] users.id
+ vid integer NOT NULL, -- [pub] vn.id
+ added timestamptz NOT NULL DEFAULT NOW(), -- [pub]
+ lastmod timestamptz NOT NULL DEFAULT NOW(), -- [pub] updated when anything in this row has changed?
+ vote_date timestamptz, -- [pub] Used for "recent votes" - also updated when vote has changed?
+ vote smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), -- [pub]
+ started date, -- [pub]
+ finished date, -- [pub]
+ notes text NOT NULL DEFAULT '', -- [pub]
+ PRIMARY KEY(uid, vid)
+);
+
+-- ulist_vns_labels
+CREATE TABLE ulist_vns_labels (
+ uid integer NOT NULL, -- [pub] user.id
+ lbl integer NOT NULL, -- [pub]
+ vid integer NOT NULL, -- [pub] vn.id
+ PRIMARY KEY(uid, lbl, vid)
+);
+
-- users
CREATE TABLE users (
id SERIAL NOT NULL PRIMARY KEY, -- [pub]
@@ -723,18 +754,18 @@ CREATE TABLE users (
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,
+ ign_votes boolean NOT NULL DEFAULT FALSE, -- [pub]
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,
+ hide_list boolean NOT NULL DEFAULT FALSE, -- deprecated, replaced with ulist_labels.private
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,
+ vn_list_wish boolean NOT NULL DEFAULT FALSE, -- Not used anymore, wishlist column in the VN list view has been removed
tags_all boolean NOT NULL DEFAULT FALSE,
tags_cont boolean NOT NULL DEFAULT TRUE,
tags_ero boolean NOT NULL DEFAULT FALSE,
@@ -749,7 +780,9 @@ CREATE TABLE users (
uniname_can boolean NOT NULL DEFAULT FALSE,
uniname text NOT NULL DEFAULT '',
pubskin_can boolean NOT NULL DEFAULT FALSE,
- pubskin_enabled boolean NOT NULL DEFAULT FALSE
+ pubskin_enabled boolean NOT NULL DEFAULT FALSE,
+ c_vns integer NOT NULL DEFAULT 0,
+ c_wish integer NOT NULL DEFAULT 0
);
-- vn
@@ -881,25 +914,6 @@ CREATE TABLE vn_staff_hist (
PRIMARY KEY (chid, aid, role)
);
--- vnlists
-CREATE TABLE vnlists (
- uid integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub]
- status smallint NOT NULL DEFAULT 0, -- [pub]
- added TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- [pub]
- notes varchar NOT NULL DEFAULT '', -- [pub]
- PRIMARY KEY(uid, vid)
-);
-
--- votes
-CREATE TABLE votes (
- vid integer NOT NULL DEFAULT 0, -- [pub]
- uid integer NOT NULL DEFAULT 0, -- [pub]
- vote integer NOT NULL DEFAULT 0, -- [pub]
- date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- PRIMARY KEY(vid, uid)
-);
-
-- wikidata
CREATE TABLE wikidata (
id integer NOT NULL PRIMARY KEY, -- [pub]
@@ -934,12 +948,3 @@ CREATE TABLE wikidata (
pixiv_user integer[], -- [pub] P5435
doujinshi_author integer[] -- [pub] P7511
);
-
--- wlists
-CREATE TABLE wlists (
- uid integer NOT NULL DEFAULT 0, -- [pub]
- vid integer NOT NULL DEFAULT 0, -- [pub]
- wstat smallint NOT NULL DEFAULT 0, -- [pub]
- added timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- PRIMARY KEY(uid, vid)
-);