summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-02-01 17:45:26 +0100
committerYorhel <git@yorhel.nl>2010-02-01 17:45:26 +0100
commit0c5c9bab2c022e251024cc2bd222bd7585ca6837 (patch)
tree841a3aebf4c9933e8cfdb37b02ef1b6fe272cecf
parent1dd46f31b7bc117d4a02515175b925bf1be2de92 (diff)
Cache the title and userid of the notifications
These aren't likely to change anyway, and things will become less easy to display when other types of notifications are added.
-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)