ulist: Use lower numbers for custom labels + add VN listing

1 files changed, 17 insertions, 54 deletions

diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index 04e3b8ee..6c1b9c42 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -12,26 +12,12 @@ CREATE TABLE ulists (
     PRIMARY KEY(uid, vid)
 );
 
--- Automatically created for each user:
---
---   Wishlist (with -Low/-Medium/-High for converted wishlists, otherwise not created by default)
---   Blacklist
---   Playing
---   Finished
---   Stalled
---   Dropped
---
--- Should these be user-editable, apart from the 'private' flag?
--- I'd say no, because then it'd be impossible use the lists for stats and automated suggestions.
 CREATE TABLE ulists_labels (
     uid      integer NOT NULL, -- user.id
-    id       SERIAL NOT NULL,
+    id       integer NOT NULL, -- 0 < builtin < 10 <= custom, ids are reused
     label    text NOT NULL,
     private  boolean NOT NULL,
     PRIMARY KEY(uid, id)
-    -- Technically 'id' is already unique because of the SERIAL type, but we want labels to be local to users.
-    -- Assuming we don't need 'id' to be globally unique, we can reserve fixed numbers for automatically created labels
-    -- (this would allow e.g. an "exclude blacklisted VNs" filter to use the same label id for everyone).
 );
 
 CREATE TABLE ulists_vn_labels (
@@ -44,9 +30,6 @@ CREATE TABLE ulists_vn_labels (
     -- Do we want a 'when has this label been applied' timestamp?
 );
 
--- First 1000 numbers are reserved for built-in labels, first 10 non-built-in labels are for conversion.
-SELECT setval('ulists_labels_id_seq', 1010);
-
 -- 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)
@@ -66,37 +49,17 @@ SELECT setval('ulists_labels_id_seq', 1010);
 \timing
 
 INSERT INTO ulists_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, 1000,'Wishlist-High',   hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 0)
-    UNION ALL SELECT id, 1001,'Wishlist-Medium', hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 1)
-    UNION ALL SELECT id, 1002,'Wishlist-Low',    hide_list FROM users WHERE id IN(SELECT DISTINCT uid FROM wlists WHERE wstat = 2);
-
--- WAY TOO SLOW. No, really, this will likely bring down the server for a day.
---INSERT INTO ulists (uid, vid, added, lastmod, vote_date, vote, notes)
---    SELECT u.id, v.id, LEAST(wl.added, vl.added, vo.date), GREATEST(wl.added, vl.added, vo.date), vo.date, vo.vote, COALESCE(vl.notes, '')
---      FROM users u
---      JOIN vn v ON true
---      LEFT JOIN wlists  wl ON wl.uid = u.id AND wl.vid = v.id
---      LEFT JOIN vnlists vl ON vl.uid = u.id AND vl.vid = v.id
---      LEFT JOIN votes   vo ON vo.uid = u.id AND vo.vid = v.id
---     WHERE (wl.uid IS NOT NULL OR vl.uid IS NOT NULL OR vo.uid IS NOT NULL);
-
--- Same thing as above, but in 3 smaller steps.
---INSERT INTO ulists (uid, vid, added, lastmod, vote_date, vote) SELECT uid, vid, date, date, date, vote FROM votes;
---INSERT INTO ulists (uid, vid, added, lastmod, notes)
---    SELECT uid, vid, added, added, notes FROM vnlists ON CONFLICT (uid, vid) DO
---    UPDATE SET notes = excluded.notes, added = LEAST(ulists.added, excluded.added), lastmod = GREATEST(ulists.lastmod, excluded.added);
---INSERT INTO ulists (uid, vid, added, lastmod)
---    SELECT uid, vid, added, added FROM wlists ON CONFLICT (uid, vid) DO
---    UPDATE SET added = LEAST(ulists.added, excluded.added), lastmod = GREATEST(ulists.lastmod, excluded.added);
+              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);
 
--- Same thing again, I realized I just needed FULL OUTER JOINs.
 INSERT INTO ulists (uid, vid, added, lastmod, vote_date, vote, notes)
     SELECT COALESCE(wl.uid, vl.uid, vo.uid)
          , COALESCE(wl.vid, vl.vid, vo.vid)
@@ -109,13 +72,13 @@ INSERT INTO ulists (uid, vid, added, lastmod, vote_date, vote, notes)
       FULL JOIN votes   vo ON vo.uid = COALESCE(wl.uid, vl.uid) AND vo.vid = COALESCE(wl.vid, vl.vid);
 
 INSERT INTO ulists_vn_labels (uid, vid, lbl)
-              SELECT uid, vid,   5 FROM wlists WHERE wstat <> 3 -- All wishlisted items except the blacklist
-    UNION ALL SELECT uid, vid,1000 FROM wlists WHERE wstat = 0 -- Wishlist-High
-    UNION ALL SELECT uid, vid,1001 FROM wlists WHERE wstat = 1 -- Wishlist-Medium
-    UNION ALL SELECT uid, vid,1002 FROM wlists WHERE wstat = 2 -- Wishlist-Low
-    UNION ALL SELECT uid, vid,   6 FROM wlists WHERE wstat = 3 -- Blacklist
+              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;
+    UNION ALL SELECT uid, vid,  7 FROM votes;