summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNWeb/Auth.pm3
-rw-r--r--lib/VNWeb/Reviews/Page.pm5
-rw-r--r--lib/VNWeb/User/Notifications.pm19
-rw-r--r--sql/perms.sql8
-rw-r--r--sql/schema.sql6
-rw-r--r--sql/triggers.sql59
-rw-r--r--util/updates/2020-09-05-notifications.sql6
7 files changed, 97 insertions, 9 deletions
diff --git a/lib/VNWeb/Auth.pm b/lib/VNWeb/Auth.pm
index cd2bf8eb..907fb2f4 100644
--- a/lib/VNWeb/Auth.pm
+++ b/lib/VNWeb/Auth.pm
@@ -261,7 +261,8 @@ sub csrfcheck {
# TODO: Measure global usage of the pref() and prefSet() calls to see if this cache is actually necessary.
my @pref_columns = qw/
- email_confirmed skin customcss filter_vn filter_release notify_dbedit notify_announce
+ email_confirmed skin customcss filter_vn filter_release
+ notify_dbedit notify_announce notify_post notify_comment
vn_list_own vn_list_wish tags_all tags_cont tags_ero tags_tech spoilers traits_sexual
max_sexual max_violence nodistract_can nodistract_noads nodistract_nofancy
/;
diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm
index a030b477..72bad7b1 100644
--- a/lib/VNWeb/Reviews/Page.pm
+++ b/lib/VNWeb/Reviews/Page.pm
@@ -114,6 +114,11 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
);
return tuwf->resNotFound if $num && !grep $_->{num} == $num, @$posts;
+ # Mark a notification for this thread as read, if there is one.
+ tuwf->dbExeci(
+ 'UPDATE notifications SET read = NOW() WHERE uid =', \auth->uid, 'AND iid =', \$id, 'AND read IS NULL'
+ ) if auth && $w->{count} <= $page*25;
+
my $title = "Review of $w->{title}";
framework_ title => $title, index => 1, type => 'w', dbobj => $w,
$num||$page>1 ? (pagevars => {sethash=>$num?$num:'threadstart'}) : (),
diff --git a/lib/VNWeb/User/Notifications.pm b/lib/VNWeb/User/Notifications.pm
index 3ae1077e..40417c63 100644
--- a/lib/VNWeb/User/Notifications.pm
+++ b/lib/VNWeb/User/Notifications.pm
@@ -8,6 +8,8 @@ my %ntypes = (
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',
);
@@ -24,6 +26,16 @@ sub settings_ {
};
br_;
label_ sub {
+ input_ type => 'checkbox', name => 'post', auth->pref('notify_post') ? (checked => 'checked') : ();
+ txt_ ' Notify me about replies to threads I posted in.';
+ };
+ br_;
+ label_ sub {
+ input_ type => 'checkbox', name => 'comment', auth->pref('notify_comment') ? (checked => 'checked') : ();
+ txt_ ' Notify me about comments to my reviews.';
+ };
+ br_;
+ label_ sub {
input_ type => 'checkbox', name => 'announce', auth->pref('notify_announce') ? (checked => 'checked') : ();
txt_ ' Notify me about site announcements.';
};
@@ -66,7 +78,8 @@ sub listing_ {
td_ class => 'tc4', sub { a_ href => $url, $lid };
td_ class => 'tc5', sub {
a_ href => $url, sub {
- txt_ $l->{iid} !~ /^t/ ? 'Edit of ' : $l->{num} == 1 ? 'New thread ' : 'Reply to ';
+ txt_ $l->{iid} =~ /^w/ ? ($l->{num} ? 'Comment on ' : 'Review of ') :
+ $l->{iid} =~ /^t/ ? ($l->{num} == 1 ? 'New thread ' : 'Reply to ') : 'Edit of ';
i_ $l->{c_title};
txt_ ' by ';
i_ user_displayname $l;
@@ -135,11 +148,15 @@ TUWF::post qr{/$RE{uid}/notify_options}, sub {
csrf => {},
dbedit => { anybool => 1 },
announce => { anybool => 1 },
+ post => { anybool => 1 },
+ comment => { anybool => 1 },
)->data;
return tuwf->resNotFound if !auth->csrfcheck($frm->{csrf});
auth->prefSet(notify_dbedit => $frm->{dbedit});
auth->prefSet(notify_announce => $frm->{announce});
+ auth->prefSet(notify_post => $frm->{post});
+ auth->prefSet(notify_comment => $frm->{comment});
tuwf->resRedirect("/u$id/notifies", 'post');
};
diff --git a/sql/perms.sql b/sql/perms.sql
index f8189895..6ce6393d 100644
--- a/sql/perms.sql
+++ b/sql/perms.sql
@@ -75,7 +75,7 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;
-- protected and can only be accessed through the user_* functions.
GRANT SELECT ( id, username, registered, ip, ign_votes, email_confirmed, last_reports
, perm_board, perm_boardmod, perm_dbmod, perm_edit, perm_imgvote, perm_tag, perm_tagmod, perm_usermod, perm_imgmod, perm_review
- , skin, customcss, show_nsfw, notify_dbedit, notify_announce
+ , skin, customcss, show_nsfw, notify_dbedit, notify_announce, notify_post, notify_comment
, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, max_sexual, max_violence
, filter_vn, filter_release, vn_list_own, vn_list_wish
, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled
@@ -84,7 +84,7 @@ GRANT SELECT ( id, username, registered, ip, ign_votes, email_confirmed, last_re
INSERT ( username, mail, ip),
UPDATE ( username, ign_votes, email_confirmed, last_reports
, perm_board, perm_boardmod, perm_dbmod, perm_edit, perm_imgvote, perm_tag, perm_tagmod, perm_imgmod, perm_review
- , skin, customcss, show_nsfw, notify_dbedit, notify_announce
+ , skin, customcss, show_nsfw, notify_dbedit, notify_announce, notify_post, notify_comment
, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, max_sexual, max_violence
, filter_vn, filter_release, vn_list_own, vn_list_wish
, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled
@@ -171,8 +171,8 @@ GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
-GRANT SELECT (id, username, registered, ign_votes, email_confirmed, notify_dbedit, notify_announce, c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags, perm_imgvote),
- UPDATE ( c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags ) ON users TO vndb_multi;
+GRANT SELECT (id, username, registered, ign_votes, email_confirmed, notify_dbedit, notify_announce, notify_post, notify_comment, c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags, perm_imgvote),
+ UPDATE ( c_vns, c_wish, c_votes, c_changes, c_imgvotes, c_tags ) ON users TO vndb_multi;
GRANT DELETE ON users TO vndb_multi;
GRANT SELECT, UPDATE ON vn TO vndb_multi;
diff --git a/sql/schema.sql b/sql/schema.sql
index bff3abe8..2ee7d24d 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');
+CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce', 'post', 'comment');
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');
@@ -904,7 +904,9 @@ CREATE TABLE users (
max_sexual smallint NOT NULL DEFAULT 0,
max_violence smallint NOT NULL DEFAULT 0,
last_reports timestamptz, -- For mods: Most recent activity seen on the reports listing
- perm_review boolean NOT NULL DEFAULT false -- TODO: DEFAULT true when out of beta.
+ perm_review boolean NOT NULL DEFAULT false, -- TODO: DEFAULT true when out of beta.
+ notify_post boolean NOT NULL DEFAULT true,
+ notify_comment boolean NOT NULL DEFAULT true
);
-- vn
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 12579f9d..2fd34f1c 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -243,7 +243,6 @@ BEGIN
SELECT 1
FROM notifications n
WHERE n.uid = tb.iid
- AND n.ntype = 'pm'
AND n.iid = t.id
AND n.read IS NULL
);
@@ -278,6 +277,64 @@ CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (
+-- Add a notification on new posts
+
+CREATE OR REPLACE FUNCTION notify_post() RETURNS trigger AS $$
+BEGIN
+ INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
+ SELECT DISTINCT 'post'::notification_ntype, u.id, t.id, NEW.num, t.title, NEW.uid
+ FROM threads t
+ JOIN threads_posts tp ON tp.tid = t.id
+ JOIN users u ON tp.uid = u.id
+ WHERE t.id = NEW.tid
+ AND u.notify_post
+ AND u.id <> NEW.uid
+ AND NOT t.hidden
+ AND NOT t.private -- don't leak posts in private threads, these are handled by notify_pm anyway
+ AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet (also avoids double notification with notify_pm)
+ SELECT 1
+ FROM notifications n
+ WHERE n.uid = u.id
+ AND n.iid = t.id
+ AND n.read IS NULL
+ );
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER notify_post AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_post();
+
+
+
+
+-- Add a notification on new comment to review
+
+CREATE OR REPLACE FUNCTION notify_comment() RETURNS trigger AS $$
+BEGIN
+ INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
+ SELECT 'comment', u.id, w.id, NEW.num, v.title, NEW.uid
+ FROM reviews w
+ JOIN vn v ON v.id = w.vid
+ JOIN users u ON w.uid = u.id
+ WHERE w.id = NEW.id
+ AND u.notify_comment
+ AND u.id <> NEW.uid
+ AND NOT EXISTS( -- don't notify when you haven't read earlier comments yet
+ SELECT 1
+ FROM notifications n
+ WHERE n.uid = u.id
+ AND n.iid = w.id
+ AND n.read IS NULL
+ );
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER notify_comment AFTER INSERT ON reviews_posts FOR EACH ROW EXECUTE PROCEDURE notify_comment();
+
+
+
+
-- Update threads.c_count and c_lastnum
CREATE OR REPLACE FUNCTION update_threads_cache() RETURNS trigger AS $$
diff --git a/util/updates/2020-09-05-notifications.sql b/util/updates/2020-09-05-notifications.sql
index 864824e3..1c3e6bd6 100644
--- a/util/updates/2020-09-05-notifications.sql
+++ b/util/updates/2020-09-05-notifications.sql
@@ -6,5 +6,11 @@ ALTER TABLE notifications ALTER COLUMN c_byuser DROP NOT NULL;
DROP TYPE notification_ltype;
UPDATE notifications SET c_byuser = NULL WHERE c_byuser = 0;
+ALTER TABLE users ADD COLUMN notify_post boolean NOT NULL DEFAULT true;
+ALTER TABLE users ADD COLUMN notify_comment boolean NOT NULL DEFAULT true;
+ALTER TYPE notification_ntype ADD VALUE 'post' AFTER 'announce';
+ALTER TYPE notification_ntype ADD VALUE 'comment' AFTER 'post';
+
\i sql/func.sql
\i sql/triggers.sql
+\i sql/perms.sql