summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-30 15:20:00 +0100
committerYorhel <git@yorhel.nl>2019-12-30 15:20:00 +0100
commitd1bb5b82255c764edecc659c78d5b9f4e36555e8 (patch)
tree53058ac9a25d0e82968da77e28f46d1137204e6b /util/sql
parent13287329e70cbaf155c85e3054f2496411e21b21 (diff)
parentddb0d385eeb112de6e544adefbbac1cb0b8a957a (diff)
Merge branch 'ulist'
Diffstat (limited to 'util/sql')
-rw-r--r--util/sql/func.sql161
-rw-r--r--util/sql/perms.sql27
-rw-r--r--util/sql/schema.sql69
-rw-r--r--util/sql/tableattrs.sql25
4 files changed, 171 insertions, 111 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index dab2ddbb..b9fa5ade 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -102,24 +102,59 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
$$ LANGUAGE sql;
-
--- recalculate vn.c_popularity
-CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
- -- the following querie only update VNs which have valid votes, so make sure to reset all rows first.
- UPDATE vn SET c_popularity = NULL;
- WITH t2(vid, win) AS (
+-- Update vn.c_popularity, c_rating and c_votecount
+CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$
+ WITH votes(vid, uid, vote) AS ( -- List of all non-ignored VN votes
+ SELECT vid, uid, vote FROM ulist_vns WHERE vote IS NOT NULL AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
+ ), avgcount(avgcount) AS ( -- Average number of votes per VN
+ SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes
+ ), avgavg(avgavg) AS ( -- Average vote average
+ SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) x(a)
+ ), ratings(vid, count, rating) AS ( -- Ratings and vote counts
+ SELECT vid, COALESCE(COUNT(uid), 0),
+ COALESCE(
+ ((SELECT avgcount FROM avgcount) * (SELECT avgavg FROM avgavg) + SUM(vote)::real) /
+ ((SELECT avgcount FROM avgcount) + COUNT(uid)::real),
+ 0)
+ FROM votes
+ GROUP BY vid
+ ), popularities(vid, win) AS ( -- Popularity scores (before normalization)
SELECT vid, SUM(rank)
FROM (
- SELECT v.uid, v.vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788
- FROM votes v
- JOIN users u ON u.id = v.uid AND NOT ign_votes
- ) t1(uid, vid, rank)
- GROUP BY vid
+ SELECT uid, vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788 FROM votes
+ ) x(uid, vid, rank)
+ GROUP BY vid
+ ), stats(vid, rating, count, popularity) AS ( -- Combined stats
+ SELECT v.id, COALESCE(r.rating, 0), COALESCE(r.count, 0)
+ , p.win/(SELECT MAX(win) FROM popularities)
+ FROM vn v
+ LEFT JOIN ratings r ON r.vid = v.id
+ LEFT JOIN popularities p ON p.vid = v.id AND p.win > 0
)
- UPDATE vn SET c_popularity = s1.win/(SELECT MAX(win) FROM t2) FROM t2 s1 WHERE s1.vid = vn.id AND s1.win > 0;
+ UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity FROM stats WHERE id = vid;
$$ LANGUAGE SQL;
+
+-- Update users.c_vns, c_votes and c_wish for one user (when given an id) or all users (when given NULL)
+CREATE OR REPLACE FUNCTION update_users_ulist_stats(integer) RETURNS void AS $$
+BEGIN
+ WITH cnt(uid, votes, vns, wish) AS (
+ SELECT u.id
+ , COUNT(*) FILTER (WHERE ul.id = 7) -- Voted
+ , COUNT(DISTINCT uvl.vid) FILTER (WHERE ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist
+ , COUNT(DISTINCT uvl.vid) FILTER (WHERE ul.id = 5) -- Wishlist
+ FROM users u
+ LEFT JOIN ulist_vns_labels uvl ON uvl.uid = u.id
+ LEFT JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = u.id AND NOT ul.private
+ WHERE $1 IS NULL OR u.id = $1
+ GROUP BY u.id
+ ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish FROM cnt WHERE id = uid;
+END;
+$$ LANGUAGE plpgsql; -- Don't use "LANGUAGE SQL" here; Make sure to generate a new query plan at invocation time.
+
+
+
-- Recalculate tags_vn_inherit.
-- When a vid is given, only the tags for that vid will be updated. These
-- incremental updates do not affect tags.c_items, so that may still get
@@ -408,6 +443,16 @@ BEGIN
THEN
PERFORM notify_dbedit(xtype, xedit);
END IF;
+
+ -- Make sure all visual novels linked to a release have a corresponding entry
+ -- in ulist_vns for users who have the release in rlists. This is action (3) in
+ -- update_vnlist_rlist().
+ IF xtype = 'r' AND xoldchid IS NOT NULL
+ THEN
+ INSERT INTO ulist_vns (uid, vid)
+ SELECT rl.uid, rv.vid FROM rlists rl JOIN releases_vn rv ON rv.id = rl.rid WHERE rl.rid = xedit.itemid
+ ON CONFLICT (uid, vid) DO NOTHING;
+ END IF;
END;
$$ LANGUAGE plpgsql;
@@ -418,16 +463,10 @@ $$ LANGUAGE plpgsql;
----------------------------------------------------------
--- keep the c_* columns in the users table up to date
+-- keep the c_tags and c_changes columns in the users table up to date
CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
BEGIN
- IF TG_TABLE_NAME = 'votes' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
- ELSE
- UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
- END IF;
- ELSIF TG_TABLE_NAME = 'changes' THEN
+ IF TG_TABLE_NAME = 'changes' THEN
IF TG_OP = 'INSERT' THEN
UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
ELSE
@@ -519,48 +558,64 @@ $$ LANGUAGE plpgsql;
-- For each row in rlists, there should be at least one corresponding row in
--- vnlists for at least one of the VNs linked to that release.
--- 1. When a row is deleted from vnlists, also remove all rows from rlists that
--- would otherwise not have a corresponding row in vnlists
+-- ulist_vns for each VN linked to that release.
+-- 1. When a row is deleted from ulist_vns, also remove all rows from rlists
+-- with that VN linked.
-- 2. When a row is inserted to rlists and there is not yet a corresponding row
--- in vnlists, add a row in vnlists (with status=unknown) for each vn linked
--- to the release.
+-- in ulist_vns, add a row to ulist_vns for each vn linked to the release.
+-- 3. When a release is edited to add another VN, add those VNs to ulist_vns
+-- for everyone who has the release in rlists.
+-- This is done in edit_committed().
+-- #. When a release is edited to remove a VN, that VN kinda should also be
+-- removed from ulist_vns, but only if that ulist_vns entry was
+-- automatically added as part of the rlists entry and the user has not
+-- changed anything in the ulist_vns row. This isn't currently done.
CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$
BEGIN
-- 1.
- IF TG_TABLE_NAME = 'vnlists' THEN
- DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT rv.id
- -- fetch all related rows in rlists
- FROM releases_vn rv
- JOIN rlists rl ON rl.rid = rv.id
- WHERE rv.vid = OLD.vid AND rl.uid = OLD.uid
- -- and test for a corresponding row in vnlists
- AND NOT EXISTS(
- SELECT 1
- FROM releases_vn rvi
- JOIN vnlists vl ON vl.vid = rvi.vid AND uid = OLD.uid
- WHERE rvi.id = rv.id
- ));
-
+ IF TG_TABLE_NAME = 'ulist_vns' THEN
+ DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT id FROM releases_vn WHERE vid = OLD.vid);
-- 2.
ELSE
- INSERT INTO vnlists (uid, vid) SELECT NEW.uid, rv.vid
- -- all VNs linked to the release
- FROM releases_vn rv
- WHERE rv.id = NEW.rid
- -- but only if there are no corresponding rows in vnlists yet
- AND NOT EXISTS(
- SELECT 1
- FROM releases_vn rvi
- JOIN vnlists vl ON vl.vid = rvi.vid
- WHERE rvi.id = NEW.rid AND vl.uid = NEW.uid
- );
+ INSERT INTO ulist_vns (uid, vid)
+ SELECT NEW.uid, rv.vid FROM releases_vn rv WHERE rv.id = NEW.rid
+ ON CONFLICT (uid, vid) DO NOTHING;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
+-- Create ulist labels for new users.
+CREATE OR REPLACE FUNCTION ulist_labels_create(integer) RETURNS void AS $$
+ INSERT INTO ulist_labels (uid, id, label, private)
+ VALUES ($1, 1, 'Playing', false),
+ ($1, 2, 'Finished', false),
+ ($1, 3, 'Stalled', false),
+ ($1, 4, 'Dropped', false),
+ ($1, 5, 'Wishlist', false),
+ ($1, 6, 'Blacklist', false),
+ ($1, 7, 'Voted', false)
+ ON CONFLICT (uid, id) DO NOTHING;
+$$ LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS 'BEGIN PERFORM ulist_labels_create(NEW.id); RETURN NULL; END' LANGUAGE plpgsql;
+
+
+
+-- Set/unset the 'Voted' label when voting.
+CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$
+BEGIN
+ IF NEW.vote IS NULL THEN
+ DELETE FROM ulist_vns_labels WHERE uid = NEW.uid AND vid = NEW.vid AND lbl = 7;
+ ELSE
+ INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES (NEW.uid, NEW.vid, 7) ON CONFLICT (uid, vid, lbl) DO NOTHING;
+ END IF;
+ RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+
-- Send a notify whenever anime info should be fetched
CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
@@ -707,10 +762,8 @@ CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype
SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester
-- look for users who should get this notify
FROM (
- SELECT uid FROM votes WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM vnlists WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM wlists WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
+ SELECT uid FROM ulist_vns WHERE xtype = 'v' AND vid = xedit.itemid
+ UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
) u
-- fetch info about this edit
JOIN changes c ON c.id = xedit.chid
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index a038103e..e649526f 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -61,15 +61,15 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON threads_posts TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON traits TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON traits_chars TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_labels TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_vn_labels TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels 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, 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, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled),
- 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, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled),
- 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, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled) 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, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish),
+ 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, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish),
+ 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, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish) ON users TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site;
GRANT SELECT, INSERT, DELETE ON vn_anime TO vndb_site;
@@ -83,9 +83,6 @@ GRANT SELECT, INSERT, DELETE ON vn_seiyuu TO vndb_site;
GRANT SELECT, INSERT ON vn_seiyuu_hist TO vndb_site;
GRANT SELECT, INSERT, DELETE ON vn_staff TO vndb_site;
GRANT SELECT, INSERT ON vn_staff_hist TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_site;
GRANT SELECT, INSERT ON wikidata TO vndb_site;
@@ -147,9 +144,14 @@ GRANT SELECT ON threads_posts TO vndb_multi;
GRANT SELECT, UPDATE ON traits TO vndb_multi;
GRANT SELECT ON traits_chars TO vndb_multi; -- traits_chars_calc() is SECURITY DEFINER
GRANT SELECT ON traits_parents 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 SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
+GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
+
+GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, c_vns, c_wish, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce),
+ UPDATE ( c_votes, c_changes, c_tags, c_vns, c_wish) ON users TO vndb_multi;
GRANT DELETE ON users 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;
@@ -159,7 +161,4 @@ GRANT SELECT ON vn_screenshots_hist TO vndb_multi;
GRANT SELECT ON vn_seiyuu TO vndb_multi;
GRANT SELECT ON vn_staff TO vndb_multi;
GRANT SELECT ON vn_staff_hist TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON vnlists TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_multi;
GRANT SELECT, INSERT, UPDATE ON wikidata TO vndb_multi;
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)
-);
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index ed25bc98..c9b598b1 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -67,6 +67,13 @@ 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 ulist_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE;
+ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) 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;
@@ -93,12 +100,6 @@ ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey
ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
@@ -122,18 +123,17 @@ CREATE INDEX traits_chars_tid ON traits_chars (tid);
CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+?
CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+?
CREATE INDEX vn_staff_aid ON vn_staff (aid);
-CREATE INDEX votes_date ON votes (date desc); -- Mainly used on /v+ pages, other pages don't really need it
-CREATE INDEX votes_uid ON votes (uid);
CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0));
CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0));
+CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more
+CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;
-- Triggers
CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
@@ -189,7 +189,10 @@ CREATE TRIGGER insert_notify AFTER INSERT ON traits
CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
+CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+
CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify();
-CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
-CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();