summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNDB/DB/Users.pm11
-rw-r--r--lib/VNDB/Handler/Users.pm2
-rw-r--r--util/sql/func.sql4
-rw-r--r--util/sql/schema.sql5
-rw-r--r--util/updates/update_2.11.sql8
5 files changed, 14 insertions, 16 deletions
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 63ac2270..bade6ca1 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -177,21 +177,14 @@ sub dbNotifyGet {
);
my @join = (
- $o{what} =~ /titles/ ? (
- q|LEFT JOIN threads t ON n.ltype = 't' AND t.id = n.iid|,
- q|LEFT JOIN threads_posts tp ON n.ltype = 't' AND tp.tid = t.id AND n.subid = tp.num|,
- q|LEFT JOIN users tu ON tp.uid = tu.id|
- ) : ()
+ $o{what} =~ /titles/ ? 'LEFT JOIN users u ON n.c_byuser = u.id' : (),
);
my @select = (
qw|n.id n.ntype n.ltype n.iid n.subid|,
q|extract('epoch' from n.date) as date|,
q|extract('epoch' from n.read) as read|,
- $o{what} =~ /titles/ ? (
- q|COALESCE(t.title,'') AS title|,
- q|COALESCE(tu.username,'') AS subtitle|,
- ) : (),
+ $o{what} =~ /titles/ ? qw|u.username n.c_title| : (),
);
my($r, $np) = $s->dbPage(\%o, q|
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index 16111add..7ab21336 100644
--- a/lib/VNDB/Handler/Users.pm
+++ b/lib/VNDB/Handler/Users.pm
@@ -594,7 +594,7 @@ sub notifies {
lit mt '_usern_n_'.(
$l->{ltype} eq 't' ? ($l->{subid} == 1 ? 't_new' : 't_reply')
: die("unknown notification type")),
- sprintf('<i>%s</i>', xml_escape $l->{title}), sprintf('<i>%s</i>', xml_escape $l->{subtitle});
+ sprintf('<i>%s</i>', xml_escape $l->{c_title}), sprintf('<i>%s</i>', xml_escape $l->{username});
end;
end;
},
diff --git a/util/sql/func.sql b/util/sql/func.sql
index b1edd59b..9675097e 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -599,8 +599,8 @@ $$ LANGUAGE plpgsql;
-- called on INSERT INTO threads_posts
CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$
BEGIN
- INSERT INTO notifications (ntype, ltype, uid, iid, subid)
- SELECT 'pm', 't', tb.iid, t.id, NEW.num
+ INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
+ SELECT 'pm', 't', 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
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index 557b75a1..c496e01d 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -34,7 +34,9 @@ CREATE TABLE notifications (
ntype notification_ntype NOT NULL,
ltype notification_ltype NOT NULL,
iid integer NOT NULL,
- subid integer
+ subid integer,
+ c_title text NOT NULL,
+ c_byuser integer
);
-- producers
@@ -354,6 +356,7 @@ CREATE TABLE wlists (
ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id);
ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id);
ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id);
diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql
index c59b2815..396ea540 100644
--- a/util/updates/update_2.11.sql
+++ b/util/updates/update_2.11.sql
@@ -11,12 +11,14 @@ CREATE TABLE notifications (
ntype notification_ntype NOT NULL,
ltype notification_ltype NOT NULL,
iid integer NOT NULL,
- subid integer
+ subid integer,
+ c_title text NOT NULL,
+ c_byuser integer REFERENCES users (id)
);
-- convert the "unread messages" count into notifications
-INSERT INTO notifications (uid, date, ntype, ltype, iid, subid)
- SELECT tb.iid, tp.date, 'pm', 't', t.id, tp.num
+INSERT INTO notifications (uid, date, ntype, ltype, iid, subid, c_title, c_byuser)
+ SELECT tb.iid, tp.date, 'pm', 't', t.id, tp.num, t.title, tp.uid
FROM threads_boards tb
JOIN threads t ON t.id = tb.tid
JOIN threads_posts tp ON tp.tid = t.id AND tp.num = COALESCE(tb.lastread, 1)