From 5f5306216268e2bc97f7dccee24a9a8152553890 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Tue, 26 Jan 2010 19:02:19 +0100 Subject: Made a start on the notification system The current setup should be able to handle all kinds of notifications, though only PMs are implemented at this point. More to come. --- util/dbgraph.pl | 2 +- util/sql/all.sql | 4 ++++ util/sql/func.sql | 47 ++++++++++++++++++++++++++++++++++++++++++-- util/sql/schema.sql | 18 ++++++++++++++--- util/updates/update_2.11.sql | 33 +++++++++++++++++++++++++++++-- 5 files changed, 96 insertions(+), 8 deletions(-) (limited to 'util') diff --git a/util/dbgraph.pl b/util/dbgraph.pl index 10297b66..a8f1cbe2 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -23,7 +23,7 @@ my %subgraphs = ( 'Producers' => [qw| FFFFCC producers producers_rev producers_relations |], 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |], 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots |], - 'Users' => [qw| CCFFFF users votes rlists wlists sessions |], + 'Users' => [qw| CCFFFF users votes rlists wlists sessions notifications |], 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |], 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |], 'Misc' => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |], diff --git a/util/sql/all.sql b/util/sql/all.sql index b2e82bfb..dee71614 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -10,6 +10,8 @@ CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p'); CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer); CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); +CREATE TYPE notification_ntype AS ENUM ('pm'); +CREATE TYPE notification_ltype AS ENUM ('t'); CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori'); CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); @@ -60,6 +62,8 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update(); +CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); + -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; diff --git a/util/sql/func.sql b/util/sql/func.sql index f32bd2ee..b1edd59b 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -1,4 +1,16 @@ +-- A small note on the function naming scheme: +-- edit_* -> revision insertion abstraction functions +-- *_notify -> functions issuing a PgSQL NOTIFY statement +-- notify_* -> functions creating entries in the notifications table +-- update_* -> functions to update a cache +-- *_update ^ (I should probably rename these to +-- *_calc ^ the update_* scheme for consistency) +-- I like to keep the nouns in functions singular, in contrast to the table +-- naming scheme where nouns are always plural. But I'm not very consistent +-- with that, either. + + -- update_vncache(id) - updates the c_* columns in the vn table CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ @@ -38,8 +50,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer AND r3.hidden = FALSE GROUP BY rp3.platform - ORDER BY rp3.platform - ), '/'), '') + ORDER BY rp3.platform), '/'), '') WHERE id = $1; $$ LANGUAGE sql; @@ -575,3 +586,35 @@ BEGIN END; $$ LANGUAGE plpgsql; + + + + +---------------------------------------------------------- +-- notification functions -- +-- (these are, in fact, also triggers) -- +---------------------------------------------------------- + + +-- 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 + FROM threads t + JOIN threads_boards tb ON tb.tid = t.id + WHERE t.id = NEW.tid + AND tb.type = 'u' + AND tb.iid <> NEW.uid -- don't notify when posting in your own board + AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet + SELECT 1 + FROM notifications n + WHERE n.uid = tb.iid + AND n.ntype = 'pm' + AND n.iid = t.id + AND n.read IS NULL + ); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + diff --git a/util/sql/schema.sql b/util/sql/schema.sql index cbd061c4..f345cffd 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -25,6 +25,18 @@ CREATE TABLE changes ( ilock boolean NOT NULL DEFAULT FALSE ); +-- notifications +CREATE TABLE notifications ( + id serial PRIMARY KEY NOT NULL, + 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 +); + -- producers CREATE TABLE producers ( id SERIAL NOT NULL PRIMARY KEY, @@ -240,7 +252,6 @@ CREATE TABLE threads_boards ( tid integer NOT NULL DEFAULT 0, type character(2) NOT NULL DEFAULT 0, iid integer NOT NULL DEFAULT 0, - lastread smallint NOT NULL, PRIMARY KEY(tid, type, iid) ); @@ -341,10 +352,11 @@ 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 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); -ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id); ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id); ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id); diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql index d311165c..bf999cdc 100644 --- a/util/updates/update_2.11.sql +++ b/util/updates/update_2.11.sql @@ -1,4 +1,33 @@ + +CREATE TYPE notification_ntype AS ENUM ('pm'); +CREATE TYPE notification_ltype AS ENUM ('t'); + +CREATE TABLE notifications ( + id serial PRIMARY KEY NOT NULL, + uid integer NOT NULL REFERENCES users (id), + date timestamptz NOT NULL DEFAULT NOW(), + read timestamptz, + ntype notification_ntype NOT NULL, + ltype notification_ltype NOT NULL, + iid integer NOT NULL, + subid integer +); + +-- 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 + 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) + WHERE tb.type = 'u' AND NOT t.hidden AND (tb.lastread IS NULL OR t.count <> tb.lastread); + +-- ...and drop the now unused lastread column +ALTER TABLE threads_boards DROP COLUMN lastread; + + + + ALTER TABLE changes ADD COLUMN ihid boolean NOT NULL DEFAULT FALSE; ALTER TABLE changes ADD COLUMN ilock boolean NOT NULL DEFAULT FALSE; @@ -8,6 +37,8 @@ CREATE TRIGGER vn_hidlock_update BEFORE UPDATE ON vn CREATE TRIGGER producers_hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); CREATE TRIGGER releases_hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); + CREATE OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$ BEGIN @@ -50,5 +81,3 @@ UNION SELECT 'r', COUNT(*) FROM (SELECT tmp_edit_hidlock('r', id) FROM releases UNION SELECT 'p', COUNT(*) FROM (SELECT tmp_edit_hidlock('p', id) FROM producers WHERE hidden OR locked) x; DROP FUNCTION tmp_edit_hidlock(text, integer); - - -- cgit v1.2.3