From b43423e9ab6543c30660368e128d88a78b1aed39 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Wed, 7 Oct 2020 12:47:41 +0200 Subject: notifications: Add backend for subscriptions and new notification types + (un)subscription for new posts/edits on individual threads, reviews and DB entries. + Subscribing to VNs to get notifications for new reviews. + The users.notify_posts setting now also includes comments on reviews the user has commented on. While this is all very nice and such, it's just the data model and backend. The hard part is the user interface. --- lib/VNWeb/User/Notifications.pm | 26 +++++++++++++------ sql/func.sql | 52 ++++++++++++++++++++++++++++++++------ sql/perms.sql | 1 + sql/schema.sql | 16 +++++++++++- sql/tableattrs.sql | 1 + sql/triggers.sql | 14 ++++++++++ util/updates/wip-notifications.sql | 15 +++++++++++ 7 files changed, 108 insertions(+), 17 deletions(-) diff --git a/lib/VNWeb/User/Notifications.pm b/lib/VNWeb/User/Notifications.pm index aeaaa7f1..e4bc0b06 100644 --- a/lib/VNWeb/User/Notifications.pm +++ b/lib/VNWeb/User/Notifications.pm @@ -3,13 +3,16 @@ package VNWeb::User::Notifications; use VNWeb::Prelude; my %ntypes = ( - pm => 'Private Message', - dbdel => 'Entry you contributed to has been deleted', - listdel => 'VN in your list has been deleted', - dbedit => 'Entry you contributed to has been edited', - announce => 'Site announcement', - post => 'Reply to a thread you\'ve posted in', - comment => 'Comment on your review', + pm => 'Private Message', + dbdel => 'Entry you contributed to has been deleted', + listdel => 'VN in your list has been deleted', + dbedit => 'Entry you contributed to has been edited', + announce => 'Site announcement', + post => 'Reply to a thread you posted in', + comment => 'Comment on your review', + subpost => 'Reply to a thread you subscribed to', + subedit => 'Entry you subscribed to has been edited', + subreview => 'New review for a VN you subscribed to', ); @@ -73,7 +76,14 @@ sub listing_ { my $lid = $l->{iid}.($l->{num}?'.'.$l->{num}:''); my $url = "/u$id/notify/$l->{id}/$lid"; td_ class => 'tc1', sub { input_ type => 'checkbox', name => 'notifysel', value => $l->{id}; }; - td_ class => 'tc2', sub { join_ \&br_, sub { txt_ $ntypes{$_} }, $l->{ntype}->@* }; + td_ class => 'tc2', sub { + # Hide some not very interesting overlapping notification types + my %t = map +($_,1), $l->{ntype}->@*; + delete $t{subpost} if $t{post} || $t{comment}; + delete $t{subedit} if $t{dbedit}; + delete $t{dbedit} if $t{dbdel}; + join_ \&br_, sub { txt_ $ntypes{$_} }, sort keys %t; + }; td_ class => 'tc3', fmtage $l->{date}; td_ class => 'tc4', sub { a_ href => $url, $lid }; td_ class => 'tc5', sub { diff --git a/sql/func.sql b/sql/func.sql index 94d442fa..ec309237 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -554,6 +554,7 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid integer) RETURNS SELECT 'pm'::notification_ntype, tb.iid FROM threads_boards tb WHERE vndbid_type($1) = 't' AND tb.tid = $1 AND tb.type = 'u' + AND NOT EXISTS(SELECT 1 FROM notification_subs ns WHERE ns.iid = $1 AND ns.uid = tb.iid AND ns.subnum = false) -- dbdel UNION @@ -580,14 +581,22 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid integer) RETURNS -- 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) + SELECT 'dbedit', c.requester + FROM changes c + JOIN users u ON u.id = c.requester + WHERE c.type = vndbid_type($1)::dbentry_type AND c.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 $3 <> 1 -- Exclude edits by Multi AND u.notify_dbedit + AND NOT EXISTS(SELECT 1 FROM notification_subs ns WHERE ns.iid = $1 AND ns.uid = c.requester AND ns.subnum = false) + + -- subedit + UNION + SELECT 'subedit', ns.uid + FROM notification_subs ns + WHERE $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd') + AND $3 <> 1 -- Exclude edits by Multi + AND ns.iid = $1 AND ns.subnum -- announce UNION @@ -597,13 +606,33 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid integer) RETURNS 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? + -- post (threads_posts) 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 + AND NOT EXISTS(SELECT 1 FROM notification_subs ns WHERE ns.iid = $1 AND ns.uid = tp.uid AND ns.subnum = false) + + -- post (reviews_posts) + UNION + SELECT 'post', u.id + FROM reviews_posts wp + JOIN users u ON wp.uid = u.id + WHERE wp.id = $1 AND vndbid_type($1) = 'w' AND $2 IS NOT NULL AND u.notify_post + AND NOT EXISTS(SELECT 1 FROM notification_subs ns WHERE ns.iid = $1 AND ns.uid = wp.uid AND ns.subnum = false) + + -- subpost (threads_posts) + UNION + SELECT 'subpost', ns.uid + FROM threads t, notification_subs ns + WHERE t.id = $1 AND ns.iid = $1 AND vndbid_type($1) = 't' AND $2 > 1 AND NOT t.private AND NOT t.hidden AND ns.subnum + + -- subpost (reviews_posts) + UNION + SELECT 'subpost', ns.uid + FROM notification_subs ns + WHERE ns.iid = $1 AND vndbid_type($1) = 'w' AND $2 IS NOT NULL AND ns.subnum -- comment UNION @@ -611,6 +640,13 @@ CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid integer) RETURNS 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 + AND NOT EXISTS(SELECT 1 FROM notification_subs ns WHERE ns.iid = $1 AND ns.uid = w.uid AND NOT ns.subnum) + + -- subreview + UNION + SELECT 'subreview', ns.uid + FROM reviews w, notification_subs ns + WHERE w.id = $1 AND vndbid_type($1) = 'w' AND $2 IS NULL AND ns.iid = vndbid('v', w.vid) AND ns.subreview ) AS noti(ntype, uid) WHERE uid <> $3 diff --git a/sql/perms.sql b/sql/perms.sql index 6ce6393d..13b8fbd6 100644 --- a/sql/perms.sql +++ b/sql/perms.sql @@ -19,6 +19,7 @@ GRANT SELECT, INSERT ON docs_hist TO vndb_site; GRANT SELECT, INSERT, UPDATE ON images TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON image_votes TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_site; +GRANT SELECT, INSERT, UPDATE, DELETE ON notification_subs TO vndb_site; GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_site; GRANT SELECT, INSERT, UPDATE ON producers TO vndb_site; GRANT SELECT, INSERT ON producers_hist TO vndb_site; diff --git a/sql/schema.sql b/sql/schema.sql index 5dec6abe..2b213986 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -57,7 +57,7 @@ CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer); CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b'); CREATE TYPE language AS ENUM ('ar', 'bg', 'ca', 'cs', 'da', 'de', 'el', 'en', 'eo', 'es', 'fi', 'fr', 'gd', 'he', 'hr', 'hu', 'id', 'it', 'ja', 'ko', 'mk', 'ms', 'lt', 'lv', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sl', 'sv', 'ta', 'th', 'tr', 'uk', 'vi', 'zh'); CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); -CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce', 'post', 'comment'); +CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce', 'post', 'comment', 'subpost', 'subedit', 'subreview'); CREATE TYPE platform AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'fmt', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pce', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'swi', 'wii', 'wiu', 'n3d', 'x68', 'xb1', 'xb3', 'xbo', 'web', 'oth'); CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng'); CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori'); @@ -245,6 +245,20 @@ CREATE TABLE login_throttle ( timeout timestamptz NOT NULL ); +-- notification_subs +CREATE TABLE notification_subs ( + uid integer NOT NULL, + iid vndbid NOT NULL, + -- Indicates a subscription on the creation of a new 'num' for the item, i.e. new post, new comment, new edit. + -- Affects the following ntypes: dbedit, subedit, pm, post, comment, subpost. Does not affect: dbdel, listdel. + -- NULL = Default behavior as if this entry did not have a row; i.e. use users.notify_post / users.notify_comment / users.notify_dbedit settings. + -- true = Default behavior + get subedit/subpost notifications for this entry. + -- false = Disable all affected ntypes for this entry. + subnum boolean, + subreview boolean NOT NULL DEFAULT false, + PRIMARY KEY(iid,uid) +); + -- notifications CREATE TABLE notifications ( id serial PRIMARY KEY, diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 15d81b95..23dd362a 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -18,6 +18,7 @@ ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id); ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id) ON DELETE CASCADE; ALTER TABLE image_votes ADD CONSTRAINT image_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE notification_subs ADD CONSTRAINT notification_subs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE producers ADD CONSTRAINT producers_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id); ALTER TABLE producers_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE; diff --git a/sql/triggers.sql b/sql/triggers.sql index d05f18ba..a1acfd90 100644 --- a/sql/triggers.sql +++ b/sql/triggers.sql @@ -256,6 +256,20 @@ CREATE TRIGGER notify_comment AFTER INSERT ON reviews_posts FOR EACH ROW EXECUTE +-- Create notifications for new reviews. + +CREATE OR REPLACE FUNCTION notify_review() RETURNS trigger AS $$ +BEGIN + INSERT INTO notifications (uid, ntype, iid, num) SELECT uid, ntype, iid, num FROM notify(NEW.id, NULL, NEW.uid) n; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER notify_review AFTER INSERT ON reviews FOR EACH ROW EXECUTE PROCEDURE notify_review(); + + + + -- Update threads.c_count and c_lastnum CREATE OR REPLACE FUNCTION update_threads_cache() RETURNS trigger AS $$ diff --git a/util/updates/wip-notifications.sql b/util/updates/wip-notifications.sql index 31dababa..1a817b33 100644 --- a/util/updates/wip-notifications.sql +++ b/util/updates/wip-notifications.sql @@ -13,5 +13,20 @@ ALTER TABLE notifications ALTER COLUMN ntype TYPE notification_ntype[] USING ARR ALTER TABLE notifications DROP COLUMN c_title; ALTER TABLE notifications DROP COLUMN c_byuser; +-- Subscriptions +ALTER TYPE notification_ntype ADD VALUE 'subpost' AFTER 'comment'; +ALTER TYPE notification_ntype ADD VALUE 'subedit' AFTER 'subpost'; +ALTER TYPE notification_ntype ADD VALUE 'subreview' AFTER 'subedit'; + +CREATE TABLE notification_subs ( + uid integer NOT NULL, + iid vndbid NOT NULL, + subnum boolean, + subreview boolean NOT NULL DEFAULT false, + PRIMARY KEY(iid,uid) +); +ALTER TABLE notification_subs ADD CONSTRAINT notification_subs_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + \i sql/func.sql \i sql/triggers.sql +\i sql/perms.sql -- cgit v1.2.3