diff options
Diffstat (limited to 'sql/func.sql')
| -rw-r--r-- | sql/func.sql | 191 |
1 files changed, 111 insertions, 80 deletions
diff --git a/sql/func.sql b/sql/func.sql index 6b77d30a..94d442fa 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -353,6 +353,38 @@ CREATE OR REPLACE FUNCTION ulist_labels_create(integer) RETURNS void AS $$ $$ LANGUAGE SQL; +-- Returns the title and (where applicable) uid of the user who created the thing for almost every supported vndbid + num. +-- While a function like this would be super useful in many places, it's too slow to be used in large or popular listings. +-- A VIEW that can be joined would offer much better optimization possibilities, but I've not managed to write that in a performant way yet. +-- A MATERIALIZED VIEW would likely be the fastest approach, but keeping that up-to-date seems like a pain. +-- +-- Not currently supported: i#, g#, u#, ch#, cv#, sf# +CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title text, uid int) AS $$ + -- x#.# + SELECT v.title, h.requester FROM changes h JOIN vn_hist v ON h.id = v.chid WHERE h.type = 'v' AND vndbid_type($1) = 'v' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2 + UNION ALL SELECT r.title, h.requester FROM changes h JOIN releases_hist r ON h.id = r.chid WHERE h.type = 'r' AND vndbid_type($1) = 'r' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2 + UNION ALL SELECT p.name, h.requester FROM changes h JOIN producers_hist p ON h.id = p.chid WHERE h.type = 'p' AND vndbid_type($1) = 'p' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2 + UNION ALL SELECT c.name, h.requester FROM changes h JOIN chars_hist c ON h.id = c.chid WHERE h.type = 'c' AND vndbid_type($1) = 'c' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2 + UNION ALL SELECT d.title, h.requester FROM changes h JOIN docs_hist d ON h.id = d.chid WHERE h.type = 'd' AND vndbid_type($1) = 'd' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2 + UNION ALL SELECT sa.name, h.requester FROM changes h JOIN staff_hist s ON h.id = s.chid JOIN staff_alias_hist sa ON sa.chid = s.chid AND sa.aid = s.aid WHERE h.type = 's' AND vndbid_type($1) = 's' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2 + -- x# + UNION ALL SELECT title, NULL FROM vn WHERE vndbid_type($1) = 'v' AND id = vndbid_num($1) AND $2 IS NULL + UNION ALL SELECT title, NULL FROM releases WHERE vndbid_type($1) = 'r' AND id = vndbid_num($1) AND $2 IS NULL + UNION ALL SELECT name, NULL FROM producers WHERE vndbid_type($1) = 'p' AND id = vndbid_num($1) AND $2 IS NULL + UNION ALL SELECT name, NULL FROM chars WHERE vndbid_type($1) = 'c' AND id = vndbid_num($1) AND $2 IS NULL + UNION ALL SELECT title, NULL FROM docs WHERE vndbid_type($1) = 'd' AND id = vndbid_num($1) AND $2 IS NULL + UNION ALL SELECT sa.name, NULL FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE vndbid_type($1) = 's' AND s.id = vndbid_num($1) AND $2 IS NOT NULL AND $2 IS NULL + -- t# + UNION ALL SELECT title, NULL FROM threads WHERE vndbid_type($1) = 't' AND id = $1 AND $2 IS NULL + -- t#.# + UNION ALL SELECT t.title, tp.uid FROM threads t JOIN threads_posts tp ON tp.tid = t.id WHERE vndbid_type($1) = 't' AND t.id = $1 AND $2 IS NOT NULL AND tp.num = $2 + -- w# + UNION ALL SELECT v.title, w.uid FROM reviews w JOIN vn v ON v.id = w.vid WHERE vndbid_type($1) = 'w' AND w.id = $1 AND $2 IS NULL + -- w#.# + UNION ALL SELECT v.title, wp.uid FROM reviews w JOIN vn v ON v.id = w.vid JOIN reviews_posts wp ON wp.id = w.id WHERE vndbid_type($1) = 'w' AND w.id = $1 AND $2 IS NOT NULL AND wp.num = $2 +$$ LANGUAGE SQL ROWS 1; + + ---------------------------------------------------------- @@ -473,23 +505,9 @@ BEGIN PERFORM traits_chars_calc(xedit.itemid); END IF; - -- Call notify_dbdel() if an entry has been deleted - -- Call notify_listdel() if a vn/release entry has been deleted - IF xoldchid IS NOT NULL - AND EXISTS(SELECT 1 FROM changes WHERE id = xoldchid AND NOT ihid) - AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND ihid) - THEN - PERFORM notify_dbdel(xtype, xedit); - IF xtype = 'v' OR xtype = 'r' THEN - PERFORM notify_listdel(xtype, xedit); - END IF; - END IF; - - -- Call notify_dbedit() if a non-hidden entry has been edited - IF xoldchid IS NOT NULL AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND NOT ihid) - THEN - PERFORM notify_dbedit(xtype, xedit); - END IF; + -- Create edit notifications + INSERT INTO notifications (uid, ntype, iid, num) + SELECT n.uid, n.ntype, n.iid, n.num FROM changes c, notify(vndbid(c.type::text, c.itemid), c.rev, c.requester) n WHERE c.id = xedit.chid; -- Make sure all visual novels linked to a release have a corresponding entry -- in ulist_vns for users who have the release in rlists. This is action (3) in @@ -522,70 +540,83 @@ $$ LANGUAGE plpgsql; ---------------------------------------------------------- --- called when an entry has been deleted -CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ - INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser) - SELECT DISTINCT 'dbdel'::notification_ntype, h.requester, vndbid(xtype::text, xedit.itemid), xedit.rev, x.title, h2.requester - FROM changes h - -- join info about the deletion itself - JOIN changes h2 ON h2.id = xedit.chid - -- Fetch the latest name/title of the entry - -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs - JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid - UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid - UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid - UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid - UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid - UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid - ) x(title) ON true - WHERE h.type = xtype AND h.itemid = xedit.itemid - AND h.requester <> 1 -- exclude Multi - AND h.requester <> h2.requester; -- exclude the user who deleted the entry -$$ LANGUAGE sql; - - - --- Called when a non-deleted item has been edited. -CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ - INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser) - SELECT DISTINCT 'dbedit'::notification_ntype, h.requester, vndbid(xtype::text, xedit.itemid), xedit.rev, x.title, h2.requester - FROM changes h - -- join info about the edit itself - JOIN changes h2 ON h2.id = xedit.chid - -- Fetch the latest name/title of the entry - JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid - UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid - UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid - UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid - UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid - UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid - ) x(title) ON true - WHERE h.type = xtype AND h.itemid = xedit.itemid - AND h.requester <> h2.requester -- exclude the user who edited the entry - AND h2.requester <> 1 -- exclude edits by Multi - -- exclude users who don't want this notify - AND EXISTS(SELECT 1 FROM users u WHERE u.id = h.requester AND notify_dbedit); -$$ LANGUAGE sql; - - +-- Called after a certain event has occurred (new edit, post, etc). +-- 'iid' and 'num' identify the item that has been created. +-- 'uid' indicates who created the item, providing an easy method of not creating a notification for that user. +-- (can technically be fetched with a DB lookup, too) +-- +-- TODO: Don't create a notification if the user still has an unread notification on the same item, but lower 'num'? +CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid integer) RETURNS TABLE (uid integer, ntype notification_ntype[], iid vndbid, num int) AS $$ + SELECT uid, array_agg(ntype), $1, $2 + FROM ( --- called when a VN/release entry has been deleted -CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$ - INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser) - SELECT DISTINCT 'listdel'::notification_ntype, u.uid, vndbid(xtype::text, xedit.itemid), xedit.rev, x.title, c.requester - -- look for users who should get this notify - FROM ( - 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 - JOIN ( - SELECT title FROM vn WHERE xtype = 'v' AND id = xedit.itemid - UNION SELECT title FROM releases WHERE xtype = 'r' AND id = xedit.itemid - ) x ON true - WHERE c.requester <> u.uid; -$$ LANGUAGE sql; + -- pm + SELECT 'pm'::notification_ntype, tb.iid + FROM threads_boards tb + WHERE vndbid_type($1) = 't' AND tb.tid = $1 AND tb.type = 'u' + + -- dbdel + UNION + SELECT 'dbdel', c_all.requester + FROM changes c_cur, changes c_all, changes c_pre + WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2 -- Current edit + AND c_pre.type = vndbid_type($1)::dbentry_type AND c_pre.itemid = vndbid_num($1) AND c_pre.rev = $2-1 -- Previous edit, to check if .ihid changed + AND c_all.type = vndbid_type($1)::dbentry_type AND c_all.itemid = vndbid_num($1) -- All edits on this entry, to see whom to notify + AND c_cur.ihid AND NOT c_pre.ihid + AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd') + + -- listdel + UNION + SELECT 'listdel', u.uid + FROM changes c_cur, changes c_pre, + ( SELECT uid FROM ulist_vns WHERE vndbid_type($1) = 'v' AND vid = vndbid_num($1) -- TODO: Could use an index on ulist_vns.vid + UNION ALL + SELECT uid FROM rlists WHERE vndbid_type($1) = 'r' AND rid = vndbid_num($1) -- TODO: Could also use an index, but the rlists table isn't that large so it's still okay + ) u(uid) + WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2 -- Current edit + AND c_pre.type = vndbid_type($1)::dbentry_type AND c_pre.itemid = vndbid_num($1) AND c_pre.rev = $2-1 -- Previous edit, to check if .ihid changed + AND c_cur.ihid AND NOT c_pre.ihid + AND $2 > 1 AND vndbid_type($1) IN('v','r') + + -- dbedit + UNION + SELECT 'dbedit', c_all.requester + FROM changes c_cur, changes c_all + JOIN users u ON u.id = c_all.requester + WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2 + AND c_all.type = vndbid_type($1)::dbentry_type AND c_all.itemid = vndbid_num($1) + AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd') + AND c_cur.requester <> 1 -- exclude edits by Multi + AND u.notify_dbedit + + -- announce + UNION + SELECT 'announce', u.id + FROM threads t + JOIN threads_boards tb ON tb.tid = t.id + JOIN users u ON u.notify_announce + WHERE vndbid_type($1) = 't' AND $2 = 1 AND t.id = $1 AND tb.type = 'an' + + -- post + -- TODO: Should this also include comments on reviews the user has commented on? + UNION + SELECT 'post', u.id + FROM threads t, threads_posts tp + JOIN users u ON tp.uid = u.id + WHERE t.id = $1 AND tp.tid = $1 AND vndbid_type($1) = 't' AND $2 > 1 AND NOT t.private AND NOT t.hidden AND u.notify_post + + -- comment + UNION + SELECT 'comment', u.id + FROM reviews w + JOIN users u ON w.uid = u.id + WHERE w.id = $1 AND vndbid_type($1) = 'w' AND $2 IS NOT NULL AND u.notify_comment + + ) AS noti(ntype, uid) + WHERE uid <> $3 + AND uid <> 1 -- No announcements for Multi + GROUP BY uid; +$$ LANGUAGE SQL; |