summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-11-10 15:31:36 +0100
committerYorhel <git@yorhel.nl>2019-11-10 15:32:11 +0100
commit6978b670e715d32f39e15c1aa2191d5dcd86685c (patch)
tree8e3f35a0b9f2622d32a55d7b85ba65c5a645e3e0 /util
parentf5ede9e020c47c49567ada2cfa52ca1b926b4c12 (diff)
ulist: SQL: Create labels for new users + auto-assign Voted label + import rlisted VNs
Diffstat (limited to 'util')
-rw-r--r--util/updates/update_wip_lists.sql62
1 files changed, 57 insertions, 5 deletions
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index 10d8e477..655cdb67 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -1,3 +1,13 @@
+-- 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;
+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
@@ -61,15 +71,21 @@ INSERT INTO ulist_labels (uid, id, label, private)
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)
- , COALESCE(wl.vid, vl.vid, vo.vid)
- , LEAST(wl.added, vl.added, vo.date)
- , GREATEST(wl.added, vl.added, vo.date)
+ 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 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
@@ -90,6 +106,42 @@ ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey
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;
+\timing
+
+
+CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS $$
+BEGIN
+ INSERT INTO ulist_labels (uid, id, label, private)
+ VALUES (NEW.id, 1, 'Playing', false),
+ (NEW.id, 2, 'Finished', false),
+ (NEW.id, 3, 'Stalled', false),
+ (NEW.id, 4, 'Dropped', false),
+ (NEW.id, 5, 'Wishlist', false),
+ (NEW.id, 6, 'Blacklist', false),
+ (NEW.id, 7, 'Voted', false);
+ RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
+
+
+
+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;
+
+CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+
+
+
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;