summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-09-05 10:09:50 +0200
committerYorhel <git@yorhel.nl>2020-09-05 10:09:52 +0200
commit112f64f1c75b19b65cce701754f2f43cea0edcf4 (patch)
tree7012fea846ec32f4bbf83ee5baf74595fe6225fd
parent818f2aa2cf67526d5d1bd2b34ebf77ce342dc8c4 (diff)
Notifications: Modernize SQL schema
By removing the ltype, using the vndbid type, renaming 'subid' to 'num' (as used elsewhere) and removing the reference to uid=0.
-rw-r--r--lib/VNWeb/Discussions/Edit.pm2
-rw-r--r--lib/VNWeb/Discussions/PostEdit.pm2
-rw-r--r--lib/VNWeb/Discussions/Thread.pm2
-rw-r--r--lib/VNWeb/User/Notifications.pm8
-rw-r--r--sql/func.sql12
-rw-r--r--sql/schema.sql20
-rw-r--r--sql/triggers.sql10
-rw-r--r--util/updates/2020-09-05-notifications.sql10
8 files changed, 37 insertions, 29 deletions
diff --git a/lib/VNWeb/Discussions/Edit.pm b/lib/VNWeb/Discussions/Edit.pm
index f0463102..dddc1ac8 100644
--- a/lib/VNWeb/Discussions/Edit.pm
+++ b/lib/VNWeb/Discussions/Edit.pm
@@ -50,7 +50,7 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub {
if($tid && $data->{delete} && auth->permBoardmod) {
auth->audit($t->{user_id}, 'post delete', "deleted $tid.1");
tuwf->dbExeci('DELETE FROM threads WHERE id =', \$tid);
- tuwf->dbExeci(q{DELETE FROM notifications WHERE ltype = 't' AND iid = vndbid_num(}, \$tid, ')');
+ tuwf->dbExeci(q{DELETE FROM notifications WHERE iid =}, \$tid);
return elm_Redirect '/t';
}
auth->audit($t->{user_id}, 'post edit', "edited $tid.1") if $tid && $t->{user_id} != auth->uid;
diff --git a/lib/VNWeb/Discussions/PostEdit.pm b/lib/VNWeb/Discussions/PostEdit.pm
index 05df61f4..a645fb6f 100644
--- a/lib/VNWeb/Discussions/PostEdit.pm
+++ b/lib/VNWeb/Discussions/PostEdit.pm
@@ -48,7 +48,7 @@ elm_api DiscussionsPostEdit => $FORM_OUT, $FORM_IN, sub {
auth->audit($t->{user_id}, 'post delete', "deleted $id.$num");
tuwf->dbExeci('DELETE FROM threads_posts WHERE tid =', \$id, 'AND num =', \$num);
tuwf->dbExeci('DELETE FROM reviews_posts WHERE id =', \$id, 'AND num =', \$num);
- tuwf->dbExeci(q{DELETE FROM notifications WHERE ltype = 't' AND iid = vndbid_num(}, \$id, ') AND subid =', \$num);
+ tuwf->dbExeci(q{DELETE FROM notifications WHERE iid =}, \$id, 'AND num =', \$num);
return elm_Redirect "/$id";
}
auth->audit($t->{user_id}, 'post edit', "edited $id.$num") if $t->{user_id} != auth->uid;
diff --git a/lib/VNWeb/Discussions/Thread.pm b/lib/VNWeb/Discussions/Thread.pm
index 80b7e544..3fd67dbe 100644
--- a/lib/VNWeb/Discussions/Thread.pm
+++ b/lib/VNWeb/Discussions/Thread.pm
@@ -194,7 +194,7 @@ TUWF::get qr{/$RE{tid}(?:(?<sep>[\./])$RE{num})?}, sub {
# Mark a notification for this thread as read, if there is one.
tuwf->dbExeci(
- 'UPDATE notifications SET read = NOW() WHERE uid =', \auth->uid, 'AND ltype = \'t\' AND iid = vndbid_num(', \$id, ') AND read IS NULL'
+ 'UPDATE notifications SET read = NOW() WHERE uid =', \auth->uid, 'AND iid =', \$id, 'AND read IS NULL'
) if auth && $t->{count} <= $page*25;
framework_ title => $t->{title}, $num ? (js => 1, pagevars => {sethash=>$num}) : (), sub {
diff --git a/lib/VNWeb/User/Notifications.pm b/lib/VNWeb/User/Notifications.pm
index 59512f05..3ae1077e 100644
--- a/lib/VNWeb/User/Notifications.pm
+++ b/lib/VNWeb/User/Notifications.pm
@@ -5,7 +5,7 @@ use VNWeb::Prelude;
my %ntypes = (
pm => 'Private Message',
dbdel => 'Entry you contributed to has been deleted',
- listdel => 'VN in your (wish)list has been deleted',
+ listdel => 'VN in your list has been deleted',
dbedit => 'Entry you contributed to has been edited',
announce => 'Site announcement',
);
@@ -58,7 +58,7 @@ sub listing_ {
}};
tr_ $_->{read} ? () : (class => 'unread'), sub {
my $l = $_;
- my $lid = $l->{ltype}.$l->{iid}.($l->{subid}?'.'.$l->{subid}:'');
+ 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}};
@@ -66,7 +66,7 @@ sub listing_ {
td_ class => 'tc4', sub { a_ href => $url, $lid };
td_ class => 'tc5', sub {
a_ href => $url, sub {
- txt_ $l->{ltype} ne 't' ? 'Edit of ' : $l->{subid} == 1 ? 'New thread ' : 'Reply to ';
+ txt_ $l->{iid} !~ /^t/ ? 'Edit of ' : $l->{num} == 1 ? 'New thread ' : 'Reply to ';
i_ $l->{c_title};
txt_ ' by ';
i_ user_displayname $l;
@@ -101,7 +101,7 @@ TUWF::get qr{/$RE{uid}/notifies}, sub {
);
my $count = tuwf->dbVali('SELECT count(*) FROM notifications WHERE', $where);
my $list = tuwf->dbPagei({ results => 25, page => $opt->{p} },
- 'SELECT n.id, n.ntype, n.ltype, n.iid, n.subid, n.c_title
+ 'SELECT n.id, n.ntype, n.iid, n.num, n.c_title
, ', sql_totime('n.date'), ' as date
, ', sql_totime('n.read'), ' as read
, ', sql_user(),
diff --git a/sql/func.sql b/sql/func.sql
index f3bb9add..6cb3735d 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -518,8 +518,8 @@ $$ 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, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'dbdel'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
+ 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
@@ -541,8 +541,8 @@ $$ 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, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'dbedit'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
+ 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
@@ -565,8 +565,8 @@ $$ LANGUAGE sql;
-- 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, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester
+ 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
diff --git a/sql/schema.sql b/sql/schema.sql
index 92f85edc..bff3abe8 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -58,7 +58,6 @@ 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_ltype AS ENUM ('v', 'r', 'p', 'c', 't', 's', 'd');
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');
@@ -248,16 +247,15 @@ CREATE TABLE login_throttle (
-- notifications
CREATE TABLE notifications (
- id serial PRIMARY KEY,
- uid integer NOT NULL,
- date timestamptz NOT NULL DEFAULT NOW(),
- read timestamptz,
- ntype notification_ntype NOT NULL,
- ltype notification_ltype NOT NULL,
- iid integer NOT NULL,
- subid integer,
- c_title text NOT NULL,
- c_byuser integer NOT NULL DEFAULT 0
+ id serial PRIMARY KEY,
+ uid integer NOT NULL,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ read timestamptz,
+ ntype notification_ntype NOT NULL,
+ iid vndbid NOT NULL,
+ num integer,
+ c_title text NOT NULL,
+ c_byuser integer
);
-- producers
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 0cda662f..12579f9d 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -232,8 +232,8 @@ CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_se
CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$
BEGIN
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT 'pm', 't', tb.iid, vndbid_num(t.id), NEW.num, t.title, NEW.uid
+ 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
@@ -244,7 +244,7 @@ BEGIN
FROM notifications n
WHERE n.uid = tb.iid
AND n.ntype = 'pm'
- AND n.iid = vndbid_num(t.id)
+ AND n.iid = t.id
AND n.read IS NULL
);
RETURN NULL;
@@ -260,8 +260,8 @@ CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROC
CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
BEGIN
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT 'announce', 't', u.id, vndbid_num(t.id), 1, t.title, NEW.uid
+ 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
diff --git a/util/updates/2020-09-05-notifications.sql b/util/updates/2020-09-05-notifications.sql
new file mode 100644
index 00000000..864824e3
--- /dev/null
+++ b/util/updates/2020-09-05-notifications.sql
@@ -0,0 +1,10 @@
+ALTER TABLE notifications ALTER COLUMN iid TYPE vndbid USING vndbid(ltype::text, iid);
+ALTER TABLE notifications RENAME COLUMN subid TO num;
+ALTER TABLE notifications DROP COLUMN ltype;
+ALTER TABLE notifications ALTER COLUMN c_byuser DROP DEFAULT;
+ALTER TABLE notifications ALTER COLUMN c_byuser DROP NOT NULL;
+DROP TYPE notification_ltype;
+UPDATE notifications SET c_byuser = NULL WHERE c_byuser = 0;
+
+\i sql/func.sql
+\i sql/triggers.sql