summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--ChangeLog1
-rw-r--r--data/lang.txt90
-rw-r--r--data/script.js14
-rw-r--r--data/style.css10
-rw-r--r--lib/VNDB/DB/Discussions.pm12
-rw-r--r--lib/VNDB/DB/Users.pm82
-rw-r--r--lib/VNDB/Handler/Discussions.pm6
-rw-r--r--lib/VNDB/Handler/Users.pm107
-rw-r--r--lib/VNDB/Util/Auth.pm4
-rw-r--r--lib/VNDB/Util/LayoutHTML.pm4
-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
15 files changed, 383 insertions, 51 deletions
diff --git a/ChangeLog b/ChangeLog
index bafe7e14..d23e431d 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -8,6 +8,7 @@ git - ?
- Abstracted parsing skin config files into a SkinFile module
- Automatically generate the skin credits on d7, by reading the skin files
- Only tagmods can create top-level tags
+ - Notification system (still only used for PMs)
2.10 - 2010-01-10
- VN score on tag pages use plain averages instead of bayesian rating
diff --git a/data/lang.txt b/data/lang.txt
index 624cb916..a986a3b7 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -1430,11 +1430,11 @@ cs : Můj wishlist
hu : Kivánságlistám
# [_1] = number of messages
-:_menu_mymessages
-en : My Messages ([_1])
-ru : Мои сообщения ([_1])
-cs : Mé zprávy ([_1])
-hu : Üzenenteim ([_1])
+:_menu_mynotifications
+en : My Notifications ([_1])
+ru*: Мои сообщения ([_1])
+cs*: Mé zprávy ([_1])
+hu*: Üzenenteim ([_1])
:_menu_mychanges
en : My Recent Changes
@@ -4623,6 +4623,86 @@ cs : Tagy
hu : Címkék
+# Notifications (/u+/notifies)
+
+:_usern_title
+en : My notifications
+ru*:
+cs*:
+hu*:
+
+:_usern_o_unread
+en : Unread notifications
+ru*:
+cs*:
+hu*:
+
+:_usern_o_alsoread
+en : All notifications
+ru*:
+cs*:
+hu*:
+
+:_usern_nonotifies
+en : No notifications!
+ru*:
+cs*:
+hu*:
+
+:_usern_col_type
+en : Type
+ru*:
+cs*:
+hu*:
+
+:_usern_col_age
+en : Age
+ru*:
+cs*:
+hu*:
+
+:_usern_col_id
+en : ID
+ru*:
+cs*:
+hu*:
+
+:_usern_col_desc
+en : Description
+ru*:
+cs*:
+hu*:
+
+:_usern_type_pm
+en : PM
+ru*:
+cs*:
+hu*:
+
+:_usern_n_t_new
+en : New thread [_1] by [_2]
+ru*:
+cs*:
+hu*:
+
+:_usern_n_t_reply
+en : Reply to [_1] by [_2]
+ru*:
+cs*:
+hu*:
+
+:_usern_but_markread
+en : mark selected read
+ru*:
+cs*:
+hu*:
+
+:_usern_but_remove
+en : remove selected
+ru*:
+cs*:
+hu*:
+
#############################################################################
diff --git a/data/script.js b/data/script.js
index 251f0921..bd4dd144 100644
--- a/data/script.js
+++ b/data/script.js
@@ -1959,6 +1959,20 @@ if(byId('lang_select')) {
d.onclick = function() {return false};
}
+// "check all" checkbox
+{
+ var f = function() {
+ var l = byName('input');
+ for(var i=0; i<l.length; i++)
+ if(l[i].type == this.type && l[i].name == this.name)
+ l[i].checked = this.checked;
+ };
+ var l = byClass('input', 'checkall');
+ for(var i=0; i<l.length; i++)
+ if(l[i].type == 'checkbox')
+ l[i].onclick = f;
+}
+
// spam protection on all forms
setTimeout(function() {
for(i=1; i<document.forms.length; i++)
diff --git a/data/style.css b/data/style.css
index 654701a0..14672ccf 100644
--- a/data/style.css
+++ b/data/style.css
@@ -943,6 +943,16 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height: 1px
.browse.rlist .relhid .tc4 { text-align: right }
+/***** User notifications *****/
+
+.browse.notifies td.tc1 { width: 14px }
+.browse.notifies td.tc3 { width: 90px }
+.browse.notifies td.tc4 { width: 60px }
+.browse.notifies tbody td.tc5 { color: $grayedout$ }
+.browse.notifies td.tc5 i { font-style: normal; color: $maintext$ }
+.browse.notifies .unread td { font-weight: bold }
+.browse.notifies tfoot td { padding: 0 0 0 25px }
+
/***** Userpage *****/
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 1c27c22e..9da25384 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -5,7 +5,7 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount dbPostRead|;
+our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount|;
# Options: id, type, iid, results, page, what, notusers, sort, reverse
@@ -252,15 +252,5 @@ sub dbPostAdd {
}
-sub dbPostRead { # thread id, user id, last post number
- my($s, $tid, $uid, $num) = @_;
- $s->dbExec(q|
- UPDATE threads_boards
- SET lastread = ?
- WHERE tid = ? AND type = 'u' AND iid = ?|,
- $num, $tid, $uid);
-}
-
-
1;
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 36f589ac..10c90cf6 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -5,11 +5,14 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|;
+our @EXPORT = qw|
+ dbUserGet dbUserEdit dbUserAdd dbUserDel dbSessionAdd dbSessionDel
+ dbNotifyGet dbNotifyMarkRead dbNotifyRemove
+|;
# %options->{ username passwd mail session uid ip registered search results page what sort reverse }
-# what: stats extended
+# what: notifycount stats extended
# sort: username registered votes changes tags
sub dbUserGet {
my $s = shift;
@@ -53,6 +56,8 @@ sub dbUserGet {
qw|mail rank salt skin customcss show_nsfw ign_votes|,
q|encode(passwd, 'hex') AS passwd|
) : (),
+ $o{what} =~ /notifycount/ ?
+ '(SELECT COUNT(*) FROM notifications WHERE uid = u.id AND read IS NULL) AS notifycount' : (),
$o{what} =~ /stats/ ? (
'(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount',
'(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount',
@@ -129,20 +134,6 @@ sub dbUserDel {
}
-# Returns number of unread messages
-sub dbUserMessageCount { # uid
- my($s, $uid) = @_;
- return $s->dbRow(q{
- SELECT SUM(tbi.count) AS cnt FROM (
- SELECT t.count-COALESCE(tb.lastread,0)
- FROM threads_boards tb
- JOIN threads t ON t.id = tb.tid AND NOT t.hidden
- WHERE tb.type = 'u' AND tb.iid = ?
- ) AS tbi (count)
- }, $uid)->{cnt}||0;
-}
-
-
# Adds a session to the database
# If no expiration is supplied the database default is used
# uid, 40 character session token, expiration time (timestamp)
@@ -164,5 +155,64 @@ sub dbSessionDel {
}
+# %options->{ uid id what results page }
+# what: titles
+sub dbNotifyGet {
+ my($s, %o) = @_;
+ $o{what} ||= '';
+ $o{results} ||= 10;
+ $o{page} ||= 1;
+
+ my %where = (
+ 'n.uid = ?' => $o{uid},
+ $o{id} ? (
+ 'n.id = ?' => $o{id} ) : (),
+ defined($o{read}) ? (
+ 'n.read !s' => $o{read} ? 'IS NOT NULL' : 'IS NULL' ) : (),
+ );
+
+ 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|
+ ) : ()
+ );
+
+ 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|,
+ ) : (),
+ );
+
+ my($r, $np) = $s->dbPage(\%o, q|
+ SELECT !s
+ FROM notifications n
+ !s
+ !W
+ ORDER BY n.id
+ |, join(', ', @select), join(' ', @join), \%where);
+ return wantarray ? ($r, $np) : $r;
+}
+
+
+# ids
+sub dbNotifyMarkRead {
+ my $s = shift;
+ $s->dbExec('UPDATE notifications SET read = NOW() WHERE id IN(!l)', \@_);
+}
+
+
+# ids
+sub dbNotifyRemove {
+ my $s = shift;
+ $s->dbExec('DELETE FROM notifications WHERE id IN(!l)', \@_);
+}
+
+
1;
diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm
index fc477de2..c8431c2c 100644
--- a/lib/VNDB/Handler/Discussions.pm
+++ b/lib/VNDB/Handler/Discussions.pm
@@ -29,13 +29,7 @@ sub thread {
my $p = $self->dbPostGet(tid => $tid, results => 25, page => $page, what => 'user');
return 404 if !$p->[0];
- # mark as read when this thread is posted in the board of the currently logged in user
- my $uid = $self->authInfo->{id};
- $self->dbPostRead($t->{id}, $uid, $p->[$#$p]{num})
- if $uid && grep $_->{type} eq 'u' && $_->{iid} == $uid, @{$t->{boards}};
-
$self->htmlHeader(title => $t->{title});
-
div class => 'mainbox';
h1 $t->{title};
h2 mt '_thread_postedin';
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index 75ce411e..d31d176b 100644
--- a/lib/VNDB/Handler/Users.pm
+++ b/lib/VNDB/Handler/Users.pm
@@ -3,7 +3,7 @@ package VNDB::Handler::Users;
use strict;
use warnings;
-use YAWF ':html';
+use YAWF ':html', 'xml_escape';
use VNDB::Func;
@@ -18,6 +18,8 @@ YAWF::register(
qr{u([1-9]\d*)/posts} => \&posts,
qr{u([1-9]\d*)/del(/[od])?} => \&delete,
qr{u/(all|[0a-z])} => \&list,
+ qr{u([1-9]\d*)/notifies} => \&notifies,
+ qr{u([1-9]\d*)/notify/([1-9]\d*)} => \&readnotify,
);
@@ -519,5 +521,108 @@ sub list {
}
+sub notifies {
+ my($self, $uid) = @_;
+ return $self->htmlDenied if !$self->authInfo->{id} || $uid != $self->authInfo->{id};
+
+ my $u = $self->dbUserGet(uid => $uid)->[0];
+
+ my $f = $self->formValidate(
+ { name => 'p', required => 0, default => 1, template => 'int' },
+ { name => 'r', required => 0, default => 0, enum => [0,1] },
+ );
+ return 404 if $f->{_err};
+
+ if($self->reqMethod() eq 'POST') {
+ my $frm = $self->formValidate(
+ { name => 'notifysel', multi => 1, required => 0, template => 'int' },
+ { name => 'markread', required => 0 },
+ { name => 'remove', required => 0 }
+ );
+ return 404 if $frm->{_err};
+ my @ids = grep $_, @{$frm->{notifysel}};
+ $self->dbNotifyMarkRead(@ids) if @ids && $frm->{markread};
+ $self->dbNotifyRemove(@ids) if @ids && $frm->{remove};
+ }
+
+ my($list, $np) = $self->dbNotifyGet(
+ uid => $uid,
+ page => $f->{p},
+ results => 25,
+ what => 'titles',
+ read => $f->{r} == 1 ? undef : 0,
+ );
+
+ $self->htmlHeader(title => mt('_usern_title'), noindex => 1);
+ $self->htmlMainTabs(u => $u);
+ div class => 'mainbox';
+ h1 mt '_usern_title';
+ p class => 'browseopts';
+ a !$f->{r} ? (class => 'optselected') : (), href => "/u$uid/notifies?r=0", mt '_usern_o_unread';
+ a $f->{r} ? (class => 'optselected') : (), href => "/u$uid/notifies?r=1", mt '_usern_o_alsoread';
+ end;
+ p mt '_usern_nonotifies' if !@$list;
+ end;
+
+ if(@$list) {
+ form action => "/u$uid/notifies?r=$f->{r}", method => 'post';
+ $self->htmlBrowse(
+ items => $list,
+ options => $f,
+ nextpage => $np,
+ class => 'notifies',
+ pageurl => "/u$uid/notifies?r=$f->{r}",
+ header => [
+ [ '<input type="checkbox" class="checkall" name="notifysel" value="0" />' ],
+ [ mt '_usern_col_type' ],
+ [ mt '_usern_col_age' ],
+ [ mt '_usern_col_id' ],
+ [ mt '_usern_col_desc' ],
+ ],
+ row => sub {
+ my($s, $n, $l) = @_;
+ Tr class => join ' ', $n%2?'odd':'', $l->{read}?'':'unread';
+ td class => 'tc1';
+ input type => 'checkbox', name => 'notifysel', value => "$l->{id}";
+ end;
+ td class => 'tc2', mt "_usern_type_$l->{ntype}";
+ td class => 'tc3', $self->{l10n}->age($l->{date});
+ td class => 'tc4';
+ a href => "/u$uid/notify/$l->{id}", "$l->{ltype}$l->{iid}".($l->{subid}?".$l->{subid}":'');
+ end;
+ td class => 'tc5';
+ 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});
+ end;
+ end;
+ },
+ footer => sub {
+ Tr;
+ td colspan => 5;
+ input type => 'submit', name => 'markread', value => mt '_usern_but_markread';
+ input type => 'submit', name => 'remove', value => mt '_usern_but_remove';
+ end;
+ end;
+ }
+ );
+ end;
+ }
+ $self->htmlFooter;
+}
+
+
+sub readnotify {
+ my($self, $uid, $nid) = @_;
+ return $self->htmlDenied if !$self->authInfo->{id} || $uid != $self->authInfo->{id};
+ my $n = $self->dbNotifyGet(uid => $uid, id => $nid)->[0];
+ return 404 if !$n->{iid};
+ $self->dbNotifyMarkRead($n->{id}) if !$n->{read};
+ # NOTE: for t+.+ IDs, this will create a double redirect, which is rather awkward...
+ $self->resRedirect("/$n->{ltype}$n->{iid}".($n->{subid}?".$n->{subid}":''), 'perm');
+}
+
+
1;
diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm
index 19a58a0f..99434c3f 100644
--- a/lib/VNDB/Util/Auth.pm
+++ b/lib/VNDB/Util/Auth.pm
@@ -25,7 +25,7 @@ sub authInit {
return _rmcookie($self) if length($cookie) < 41;
my $token = substr($cookie, 0, 40);
my $uid = substr($cookie, 40);
- $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended')->[0];
+ $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended notifycount')->[0];
return _rmcookie($self) if !$self->{_auth};
}
@@ -95,7 +95,7 @@ sub _authCheck {
return 0 if !$user || length($user) > 15 || length($user) < 2 || !$pass;
- my $d = $self->dbUserGet(username => $user, what => 'extended')->[0];
+ my $d = $self->dbUserGet(username => $user, what => 'extended notifycount')->[0];
return 0 if !defined $d->{id} || !$d->{rank};
if(_authEncryptPass($self, $pass, $d->{salt}) eq $d->{passwd}) {
diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm
index 084b9a4e..0783a9e4 100644
--- a/lib/VNDB/Util/LayoutHTML.pm
+++ b/lib/VNDB/Util/LayoutHTML.pm
@@ -79,8 +79,8 @@ sub _menu {
div class => 'menubox';
if($self->authInfo->{id}) {
- my $msg = $self->dbUserMessageCount($self->authInfo->{id});
my $uid = sprintf '/u%d', $self->authInfo->{id};
+ my $nc = $self->authInfo->{notifycount};
h2;
a href => $uid, ucfirst $self->authInfo->{username};
# note: user ranks aren't TL'ed (but might be in the future, hmm)
@@ -90,7 +90,7 @@ sub _menu {
a href => "$uid/edit", mt '_menu_myprofile'; br;
a href => "$uid/list", mt '_menu_myvnlist'; br;
a href => "$uid/wish", mt '_menu_mywishlist'; br;
- a href => "/t$uid", $msg ? (class => 'standout') : (), mt '_menu_mymessages', $msg; br;
+ a href => "$uid/notifies", $nc ? (class => 'standout') : (), mt '_menu_mynotifications', $nc; br;
a href => "$uid/hist", mt '_menu_mychanges'; br;
a href => "$uid/tags", mt '_menu_mytags'; br;
br;
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);
-
-
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-20 07:49:15 +0000