summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--lib/VNWeb/User/Notifications.pm17
-rw-r--r--sql/func.sql191
-rw-r--r--sql/schema.sql6
-rw-r--r--sql/tableattrs.sql1
-rw-r--r--sql/triggers.sql87
-rw-r--r--util/updates/wip-notifications.sql17
6 files changed, 142 insertions, 177 deletions
diff --git a/lib/VNWeb/User/Notifications.pm b/lib/VNWeb/User/Notifications.pm
index 40417c63..aeaaa7f1 100644
--- a/lib/VNWeb/User/Notifications.pm
+++ b/lib/VNWeb/User/Notifications.pm
@@ -73,14 +73,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', $ntypes{$l->{ntype}};
+ td_ class => 'tc2', sub { join_ \&br_, sub { txt_ $ntypes{$_} }, $l->{ntype}->@* };
td_ class => 'tc3', fmtage $l->{date};
td_ class => 'tc4', sub { a_ href => $url, $lid };
td_ class => 'tc5', sub {
a_ href => $url, sub {
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};
+ i_ $l->{title};
txt_ ' by ';
i_ user_displayname $l;
};
@@ -109,17 +109,16 @@ TUWF::get qr{/$RE{uid}/notifies}, sub {
)->data;
my $where = sql_and(
- sql('uid =', \$id),
- $opt->{r} ? () : 'read IS NULL'
+ sql('n.uid =', \$id),
+ $opt->{r} ? () : 'n.read IS NULL'
);
- my $count = tuwf->dbVali('SELECT count(*) FROM notifications WHERE', $where);
+ my $count = tuwf->dbVali('SELECT count(*) FROM notifications n WHERE', $where);
my $list = tuwf->dbPagei({ results => 25, page => $opt->{p} },
- 'SELECT n.id, n.ntype, n.iid, n.num, n.c_title
+ 'SELECT n.id, n.ntype::text[] AS ntype, n.iid, n.num, t.title, ', sql_user(), '
, ', sql_totime('n.date'), ' as date
, ', sql_totime('n.read'), ' as read
- , ', sql_user(),
- 'FROM notifications n
- LEFT JOIN users u ON u.id = n.c_byuser
+ FROM notifications n, item_info(n.iid, n.num) t
+ LEFT JOIN users u ON u.id = t.uid
WHERE ', $where,
'ORDER BY n.id', $opt->{r} ? 'DESC' : 'ASC'
);
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;
diff --git a/sql/schema.sql b/sql/schema.sql
index 3605e9cc..5dec6abe 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -251,11 +251,9 @@ CREATE TABLE notifications (
uid integer NOT NULL,
date timestamptz NOT NULL DEFAULT NOW(),
read timestamptz,
- ntype notification_ntype NOT NULL,
+ ntype notification_ntype[] NOT NULL,
iid vndbid NOT NULL,
- num integer,
- c_title text NOT NULL,
- c_byuser integer
+ num integer
);
-- producers
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index f00db33d..15d81b95 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -19,7 +19,6 @@ ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey
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 notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
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;
ALTER TABLE producers_hist ADD CONSTRAINT producers_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 2fd34f1c..d05f18ba 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -228,76 +228,11 @@ CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_se
--- Add a notification when someone posts in someone's board.
-
-CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$
-BEGIN
- INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
- SELECT 'pm', tb.iid, t.id, NEW.num, t.title, NEW.uid
- FROM threads t
- JOIN threads_boards tb ON tb.tid = t.id
- WHERE t.id = NEW.tid
- AND tb.type = 'u'
- AND tb.iid <> NEW.uid -- don't notify when posting in your own board
- AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet
- SELECT 1
- FROM notifications n
- WHERE n.uid = tb.iid
- AND n.iid = t.id
- AND n.read IS NULL
- );
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-
-
-
-
--- Add a notification when a thread is created in /t/an
-
-CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
-BEGIN
- INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
- SELECT 'announce', u.id, t.id, 1, t.title, NEW.uid
- FROM threads t
- JOIN threads_boards tb ON tb.tid = t.id
- -- get the users who want this announcement
- JOIN users u ON u.notify_announce
- WHERE t.id = NEW.tid
- AND tb.type = 'an' -- announcement board
- AND NOT t.hidden;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
-
-
-
-
--- Add a notification on new posts
+-- Create notifications for 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
- );
+ INSERT INTO notifications (uid, ntype, iid, num) SELECT uid, ntype, iid, num FROM notify(NEW.tid, NEW.num, NEW.uid) n;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
@@ -307,25 +242,11 @@ CREATE TRIGGER notify_post AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PR
--- Add a notification on new comment to review
+-- Create notifications for new review comments.
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
- );
+ INSERT INTO notifications (uid, ntype, iid, num) SELECT uid, ntype, iid, num FROM notify(NEW.id, NEW.num, NEW.uid) n;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
diff --git a/util/updates/wip-notifications.sql b/util/updates/wip-notifications.sql
new file mode 100644
index 00000000..31dababa
--- /dev/null
+++ b/util/updates/wip-notifications.sql
@@ -0,0 +1,17 @@
+-- Simplified triggers, all the logic is consolidated in notify().
+DROP TRIGGER notify_pm ON threads_posts;
+DROP TRIGGER notify_announce ON threads_posts;
+DROP FUNCTION notify_pm();
+DROP FUNCTION notify_announce();
+
+DROP FUNCTION notify_dbdel(dbentry_type, edit_rettype);
+DROP FUNCTION notify_dbedit(dbentry_type, edit_rettype);
+DROP FUNCTION notify_listdel(dbentry_type, edit_rettype);
+
+-- Table changes
+ALTER TABLE notifications ALTER COLUMN ntype TYPE notification_ntype[] USING ARRAY[ntype];
+ALTER TABLE notifications DROP COLUMN c_title;
+ALTER TABLE notifications DROP COLUMN c_byuser;
+
+\i sql/func.sql
+\i sql/triggers.sql
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 12:44:05 +0000