summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-01-26 19:02:19 +0100
committerYorhel <git@yorhel.nl>2010-01-26 19:29:00 +0100
commit5f5306216268e2bc97f7dccee24a9a8152553890 (patch)
tree8d273ee2ec55fd89f83a1dc5d47c1926284d9596 /util
parent363d4750f04806775513371b027f9460609eab1a (diff)
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.
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbgraph.pl2
-rw-r--r--util/sql/all.sql4
-rw-r--r--util/sql/func.sql47
-rw-r--r--util/sql/schema.sql18
-rw-r--r--util/updates/update_2.11.sql33
5 files changed, 96 insertions, 8 deletions
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);
-
-