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 /util | |
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.
Diffstat (limited to 'util')
-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 |
3 files changed, 11 insertions, 6 deletions
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) |