summaryrefslogtreecommitdiff
path: root/util/updates/2020-01-01-ulists.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-01-01 08:21:50 +0100
committerYorhel <git@yorhel.nl>2020-01-01 08:21:52 +0100
commit38ea8ede77186e0399c8f039b0f99397d55d0808 (patch)
treede651c7533fe62076129ebd74104ce717f00a665 /util/updates/2020-01-01-ulists.sql
parent5429252b438249092f615497829177cd32b14599 (diff)
ulist: Time to put this live2.27
Decided to change the naming scheme of files in util/updates/
Diffstat (limited to 'util/updates/2020-01-01-ulists.sql')
-rw-r--r--util/updates/2020-01-01-ulists.sql135
1 files changed, 135 insertions, 0 deletions
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;