diff options
author | Yorhel <git@yorhel.nl> | 2019-12-30 15:20:00 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-12-30 15:20:00 +0100 |
commit | d1bb5b82255c764edecc659c78d5b9f4e36555e8 (patch) | |
tree | 53058ac9a25d0e82968da77e28f46d1137204e6b /util/sql | |
parent | 13287329e70cbaf155c85e3054f2496411e21b21 (diff) | |
parent | ddb0d385eeb112de6e544adefbbac1cb0b8a957a (diff) |
Merge branch 'ulist'
Diffstat (limited to 'util/sql')
-rw-r--r-- | util/sql/func.sql | 161 | ||||
-rw-r--r-- | util/sql/perms.sql | 27 | ||||
-rw-r--r-- | util/sql/schema.sql | 69 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 25 |
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(); |