From 38ea8ede77186e0399c8f039b0f99397d55d0808 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 1 Jan 2020 08:21:50 +0100 Subject: ulist: Time to put this live Decided to change the naming scheme of files in util/updates/ --- util/updates/2020-01-01-ulists.sql | 135 +++++++++++++++++++++++++++++++++++++ util/updates/update_wip_lists.sql | 135 ------------------------------------- 2 files changed, 135 insertions(+), 135 deletions(-) create mode 100644 util/updates/2020-01-01-ulists.sql delete mode 100644 util/updates/update_wip_lists.sql (limited to 'util') diff --git a/util/updates/2020-01-01-ulists.sql b/util/updates/2020-01-01-ulists.sql new file mode 100644 index 00000000..7d8c4b82 --- /dev/null +++ b/util/updates/2020-01-01-ulists.sql @@ -0,0 +1,135 @@ +-- This script may be run multiple times while in beta, so clean up after ourselves. +-- (Or, uh, before ourselves, in this case...) +DROP TABLE IF EXISTS ulist_vns, ulist_labels, ulist_vns_labels CASCADE; +DROP TRIGGER IF EXISTS ulist_labels_create ON users; +DROP FUNCTION IF EXISTS ulist_labels_create(); +DROP FUNCTION IF EXISTS ulist_voted_label(); + + + + +-- Replaces the current vnlists, votes and wlists tables +CREATE TABLE ulist_vns ( + uid integer NOT NULL, -- users.id + vid integer NOT NULL, -- vn.id + added timestamptz NOT NULL DEFAULT NOW(), + lastmod timestamptz NOT NULL DEFAULT NOW(), -- updated when anything in this row has changed? + vote_date timestamptz, -- Used for "recent votes" - also updated when vote has changed? + vote smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), + started date, + finished date, + notes text NOT NULL DEFAULT '', + PRIMARY KEY(uid, vid) +); + +CREATE TABLE ulist_labels ( + uid integer NOT NULL, -- user.id + id integer NOT NULL, -- 0 < builtin < 10 <= custom, ids are reused + label text NOT NULL, + private boolean NOT NULL, + PRIMARY KEY(uid, id) +); + +CREATE TABLE ulist_vns_labels ( + uid integer NOT NULL, -- user.id + lbl integer NOT NULL, + vid integer NOT NULL, -- vn.id + PRIMARY KEY(uid, lbl, vid) + -- (uid, lbl) REFERENCES ulist_labels (uid, id) ON DELETE CASCADE + -- (uid, vid) REFERENCES ulist (uid, vid) ON DELETE CASCADE + -- Do we want a 'when has this label been applied' timestamp? +); + +-- When is a row in ulist 'public'? i.e. When it is visible in a VNs recent votes and in the user's VN list? +-- +-- EXISTS(SELECT 1 FROM ulist_vn_label uvl JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE uid = ulist.uid AND vid = ulist.vid AND NOT ul.private) +-- +-- That is: It is public when it has been assigned at least one non-private label. +-- +-- This means that, during the conversion of old lists to this new format, all +-- vns with an 'unknown' status (= old 'unknown' status or voted but not in +-- vnlist/wlist) from users who have not hidden their list should be assigned +-- to a new non-private label. +-- +-- The "Don't allow others to see my [..] list" profile option becomes obsolete +-- with this label-based private flag. + + + +\timing + +-- The following queries need a consistent view of the database. +BEGIN; +SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; + +INSERT INTO ulist_labels (uid, id, label, private) + SELECT id, 1, 'Playing', hide_list FROM users + UNION ALL SELECT id, 2, 'Finished', hide_list FROM users + UNION ALL SELECT id, 3, 'Stalled', hide_list FROM users + UNION ALL SELECT id, 4, 'Dropped', hide_list FROM users + UNION ALL SELECT id, 5, 'Wishlist', hide_list FROM users + UNION ALL SELECT id, 6, 'Blacklist', hide_list FROM users + UNION ALL SELECT id, 7, 'Voted', hide_list FROM users + UNION ALL SELECT id, 10, 'Wishlist-High', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 0) + UNION ALL SELECT id, 11, 'Wishlist-Medium', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 1) + UNION ALL SELECT id, 12, 'Wishlist-Low', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 2); + +INSERT INTO ulist_vns (uid, vid, added, lastmod, vote_date, vote, notes) + SELECT COALESCE(wl.uid, vl.uid, vo.uid, ro.uid) + , COALESCE(wl.vid, vl.vid, vo.vid, ro.vid) + , LEAST(wl.added, vl.added, vo.date, ro.added) + , GREATEST(wl.added, vl.added, vo.date, ro.added) + , vo.date, vo.vote + , COALESCE(vl.notes, '') + FROM wlists wl + FULL JOIN vnlists vl ON vl.uid = wl.uid AND vl.vid = wl.vid + FULL JOIN votes vo ON vo.uid = COALESCE(wl.uid, vl.uid) AND vo.vid = COALESCE(wl.vid, vl.vid) + FULL JOIN ( -- It used to be possible to have items in rlists without a corresponding entry in vnlists, so also merge rows from there. + SELECT rl.uid, rv.vid, MAX(rl.added) + FROM rlists rl + JOIN releases_vn rv ON rv.id = rl.rid + GROUP BY rl.uid, rv.vid + ) ro (uid, vid, added) ON ro.uid = COALESCE(wl.uid, vl.uid, vo.uid) AND ro.vid = COALESCE(wl.vid, vl.vid, vo.vid); + +INSERT INTO ulist_vns_labels (uid, vid, lbl) + SELECT uid, vid, 5 FROM wlists WHERE wstat <> 3 -- All wishlisted items except the blacklist + UNION ALL SELECT uid, vid, 10 FROM wlists WHERE wstat = 0 -- Wishlist-High + UNION ALL SELECT uid, vid, 11 FROM wlists WHERE wstat = 1 -- Wishlist-Medium + UNION ALL SELECT uid, vid, 12 FROM wlists WHERE wstat = 2 -- Wishlist-Low + UNION ALL SELECT uid, vid, 6 FROM wlists WHERE wstat = 3 -- Blacklist + UNION ALL SELECT uid, vid, status FROM vnlists WHERE status <> 0 -- Playing/Finished/Stalled/Dropped + UNION ALL SELECT uid, vid, 7 FROM votes; + + +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_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +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; + +COMMIT; + +\timing + +DROP FUNCTION update_vnpopularity(); + +ALTER TABLE users ADD COLUMN c_vns integer NOT NULL DEFAULT 0; +ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0; + +DROP TRIGGER users_votes_update ON votes; +DROP TRIGGER update_vnlist_rlist ON rlists; + +\i util/sql/func.sql +\i util/sql/triggers.sql +\i util/sql/perms.sql + +\timing +SELECT update_users_ulist_stats(NULL); +CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; +CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes; + + +-- Can be done later: +-- DROP TABLE wlists, vnlists, votes; diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql deleted file mode 100644 index 7d8c4b82..00000000 --- a/util/updates/update_wip_lists.sql +++ /dev/null @@ -1,135 +0,0 @@ --- This script may be run multiple times while in beta, so clean up after ourselves. --- (Or, uh, before ourselves, in this case...) -DROP TABLE IF EXISTS ulist_vns, ulist_labels, ulist_vns_labels CASCADE; -DROP TRIGGER IF EXISTS ulist_labels_create ON users; -DROP FUNCTION IF EXISTS ulist_labels_create(); -DROP FUNCTION IF EXISTS ulist_voted_label(); - - - - --- Replaces the current vnlists, votes and wlists tables -CREATE TABLE ulist_vns ( - uid integer NOT NULL, -- users.id - vid integer NOT NULL, -- vn.id - added timestamptz NOT NULL DEFAULT NOW(), - lastmod timestamptz NOT NULL DEFAULT NOW(), -- updated when anything in this row has changed? - vote_date timestamptz, -- Used for "recent votes" - also updated when vote has changed? - vote smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), - started date, - finished date, - notes text NOT NULL DEFAULT '', - PRIMARY KEY(uid, vid) -); - -CREATE TABLE ulist_labels ( - uid integer NOT NULL, -- user.id - id integer NOT NULL, -- 0 < builtin < 10 <= custom, ids are reused - label text NOT NULL, - private boolean NOT NULL, - PRIMARY KEY(uid, id) -); - -CREATE TABLE ulist_vns_labels ( - uid integer NOT NULL, -- user.id - lbl integer NOT NULL, - vid integer NOT NULL, -- vn.id - PRIMARY KEY(uid, lbl, vid) - -- (uid, lbl) REFERENCES ulist_labels (uid, id) ON DELETE CASCADE - -- (uid, vid) REFERENCES ulist (uid, vid) ON DELETE CASCADE - -- Do we want a 'when has this label been applied' timestamp? -); - --- When is a row in ulist 'public'? i.e. When it is visible in a VNs recent votes and in the user's VN list? --- --- EXISTS(SELECT 1 FROM ulist_vn_label uvl JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE uid = ulist.uid AND vid = ulist.vid AND NOT ul.private) --- --- That is: It is public when it has been assigned at least one non-private label. --- --- This means that, during the conversion of old lists to this new format, all --- vns with an 'unknown' status (= old 'unknown' status or voted but not in --- vnlist/wlist) from users who have not hidden their list should be assigned --- to a new non-private label. --- --- The "Don't allow others to see my [..] list" profile option becomes obsolete --- with this label-based private flag. - - - -\timing - --- The following queries need a consistent view of the database. -BEGIN; -SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; - -INSERT INTO ulist_labels (uid, id, label, private) - SELECT id, 1, 'Playing', hide_list FROM users - UNION ALL SELECT id, 2, 'Finished', hide_list FROM users - UNION ALL SELECT id, 3, 'Stalled', hide_list FROM users - UNION ALL SELECT id, 4, 'Dropped', hide_list FROM users - UNION ALL SELECT id, 5, 'Wishlist', hide_list FROM users - UNION ALL SELECT id, 6, 'Blacklist', hide_list FROM users - UNION ALL SELECT id, 7, 'Voted', hide_list FROM users - UNION ALL SELECT id, 10, 'Wishlist-High', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 0) - UNION ALL SELECT id, 11, 'Wishlist-Medium', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 1) - UNION ALL SELECT id, 12, 'Wishlist-Low', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 2); - -INSERT INTO ulist_vns (uid, vid, added, lastmod, vote_date, vote, notes) - SELECT COALESCE(wl.uid, vl.uid, vo.uid, ro.uid) - , COALESCE(wl.vid, vl.vid, vo.vid, ro.vid) - , LEAST(wl.added, vl.added, vo.date, ro.added) - , GREATEST(wl.added, vl.added, vo.date, ro.added) - , vo.date, vo.vote - , COALESCE(vl.notes, '') - FROM wlists wl - FULL JOIN vnlists vl ON vl.uid = wl.uid AND vl.vid = wl.vid - FULL JOIN votes vo ON vo.uid = COALESCE(wl.uid, vl.uid) AND vo.vid = COALESCE(wl.vid, vl.vid) - FULL JOIN ( -- It used to be possible to have items in rlists without a corresponding entry in vnlists, so also merge rows from there. - SELECT rl.uid, rv.vid, MAX(rl.added) - FROM rlists rl - JOIN releases_vn rv ON rv.id = rl.rid - GROUP BY rl.uid, rv.vid - ) ro (uid, vid, added) ON ro.uid = COALESCE(wl.uid, vl.uid, vo.uid) AND ro.vid = COALESCE(wl.vid, vl.vid, vo.vid); - -INSERT INTO ulist_vns_labels (uid, vid, lbl) - SELECT uid, vid, 5 FROM wlists WHERE wstat <> 3 -- All wishlisted items except the blacklist - UNION ALL SELECT uid, vid, 10 FROM wlists WHERE wstat = 0 -- Wishlist-High - UNION ALL SELECT uid, vid, 11 FROM wlists WHERE wstat = 1 -- Wishlist-Medium - UNION ALL SELECT uid, vid, 12 FROM wlists WHERE wstat = 2 -- Wishlist-Low - UNION ALL SELECT uid, vid, 6 FROM wlists WHERE wstat = 3 -- Blacklist - UNION ALL SELECT uid, vid, status FROM vnlists WHERE status <> 0 -- Playing/Finished/Stalled/Dropped - UNION ALL SELECT uid, vid, 7 FROM votes; - - -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_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; -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; - -COMMIT; - -\timing - -DROP FUNCTION update_vnpopularity(); - -ALTER TABLE users ADD COLUMN c_vns integer NOT NULL DEFAULT 0; -ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0; - -DROP TRIGGER users_votes_update ON votes; -DROP TRIGGER update_vnlist_rlist ON rlists; - -\i util/sql/func.sql -\i util/sql/triggers.sql -\i util/sql/perms.sql - -\timing -SELECT update_users_ulist_stats(NULL); -CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes; - - --- Can be done later: --- DROP TABLE wlists, vnlists, votes; -- cgit v1.2.3