diff options
author | Yorhel <git@yorhel.nl> | 2010-02-01 17:45:26 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-02-01 17:45:26 +0100 |
commit | 0c5c9bab2c022e251024cc2bd222bd7585ca6837 (patch) | |
tree | 841a3aebf4c9933e8cfdb37b02ef1b6fe272cecf | |
parent | 1dd46f31b7bc117d4a02515175b925bf1be2de92 (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.pm | 11 | ||||
-rw-r--r-- | lib/VNDB/Handler/Users.pm | 2 | ||||
-rw-r--r-- | util/sql/func.sql | 4 | ||||
-rw-r--r-- | util/sql/schema.sql | 5 | ||||
-rw-r--r-- | util/updates/update_2.11.sql | 8 |
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) |