summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/VNWeb/User/Notifications.pm26
-rw-r--r--sql/func.sql52
-rw-r--r--sql/perms.sql1
-rw-r--r--sql/schema.sql16
-rw-r--r--sql/tableattrs.sql1
-rw-r--r--sql/triggers.sql14
-rw-r--r--util/updates/wip-notifications.sql15
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
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-19 22:38:08 +0000