summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
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);
-
-