summaryrefslogtreecommitdiff
path: root/util/sql/func.sql
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-16 13:06:49 +0100
committerYorhel <git@yorhel.nl>2019-12-16 13:06:49 +0100
commit153d6578e4c1973f08694fe78259417912e3d5f3 (patch)
tree16682188c87e84bbe310fab3b8fd3e4d4d6fa008 /util/sql/func.sql
parent829eb1f2472936d70a70839e55b16858dbcfb6af (diff)
ulist: Integrate update script into SQL + update notify_listdel()
Diffstat (limited to 'util/sql/func.sql')
-rw-r--r--util/sql/func.sql37
1 files changed, 33 insertions, 4 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index dab2ddbb..f326d9be 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -562,6 +562,37 @@ $$ LANGUAGE plpgsql;
+-- Create ulist labels for new users.
+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;
+
+
+
+-- 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 $$
BEGIN NOTIFY anime; RETURN NULL; END;
@@ -707,10 +738,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
+ UNION 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