-
Notifications
You must be signed in to change notification settings - Fork 128
Notifications Overview
The following query is used to get the notification count seen in the user info at the top of the site:
SELECT COUNT( * )
FROM (
SELECT "me"."id"
FROM "event_notification_group" "me"
JOIN "user_notification_group" "user_notification_group"
ON "user_notification_group"."id" = "me"."user_notification_group_id"
LEFT JOIN "event_notification" "event_notifications"
ON "event_notifications"."event_notification_group_id" = "me"."id"
LEFT JOIN "user_notification" "user_notification"
ON "user_notification"."id" = "event_notifications"."user_notification_id"
WHERE "user_notification"."users_id" = '1'
GROUP BY "me"."id"
) "me"
This is, in effect:
SELECT count(distinct "me"."id")
FROM "event_notification_group" "me"
JOIN "user_notification_group" "user_notification_group"
ON "user_notification_group"."id" = "me"."user_notification_group_id"
LEFT JOIN "event_notification" "event_notifications"
ON "event_notifications"."event_notification_group_id" = "me"."id"
LEFT JOIN "user_notification" "user_notification"
ON "user_notification"."id" = "event_notifications"."user_notification_id"
WHERE "user_notification"."users_id" = '1'
We can remove the informational table user_notification_group from this query:
SELECT count(distinct "me"."id")
FROM "event_notification_group" "me"
LEFT JOIN "event_notification" "event_notifications"
ON "event_notifications"."event_notification_group_id" = "me"."id"
LEFT JOIN "user_notification" "user_notification"
ON "user_notification"."id" = "event_notifications"."user_notification_id"
WHERE "user_notification"."users_id" = '3';
This is the minimal query which will return a correct notification count.
So at the top level a notification (what we're counting) consists of unique event_notification_group
entries with a set of chained joins down to user_notification
where we can finally filter on user_notification.users_id
.
.-User::Notification::Matrix------------------------------------------.
.------. | .--------------------. .---------------------------. |
| User |---has many-->| User::Notification |<--has many--| User::Notification::Group | |
'------' | '--------------------' '---------------------------' |
'-------------|-------------------------------------|-----------------'
| |
has many has many
| |
v v
.-------. .---------------------. .----------------------------.
| Event |--has many-->| Event::Notification |<-has many-| Event::Notification::Group |
'-------' '---------------------' '----------------------------'
|
has many
|
v
.---------------.
| Event::Relate |----------.
'---------------' |
DDGC::DB::Role::HasContext
->get_context_obj
Role included by
DDGC::DB::Base::Result
->add_context_relations_role
Added to classes by
__PACKAGE__->add_context_relations
|
v
.----------------------------------------------------------.
| DDGC Contribution Type Object Instance |
| (e.g. DDGC::DB::Result::Comment, DDGC::DB::Result::Idea) |
| See: select distinct context from event_relate; |
'----------------------------------------------------------'
Every user action which creates or updates a comment, translation, vote etc. will have an entry in this table.
The existence / completion of a notification depends on its presence in this table. New posts, votes, ideas and other contributions will create an entry in this table, marking notification done deletes them.
While the Event Notification table stores notifiable events, to receive the notification, a subscription (follow) to the event or event type has to exist in this table.
This is an Event Notification type, e.g. if you have a Forum post entitled "Hello", a direct response to this will create a new 'Event Notification Group' entry and all new events of that type will link to this entry (new Event Notifications will be created which link to this Event Notification Group entry).
This is a table which lists the relationships between contexts:
# Context ContextID SubContext Target
#-------------------------------------------------
# Y X Z Changed/New Z on Y #X
# Y X Changes on Y #X
# Y Changes on any Y or Changed/New Y
# Y Z New Z on any Y
ddgc=# select context, group_context, sub_context from public.user_notification_group;
context | group_context | sub_context
------------------------------------------------+------------------------------------------------+------------------------------------------------------
DDGC::DB::Result::Comment | DDGC::DB::Result::Comment | DDGC::DB::Result::Comment
DDGC::DB::Result::User::Blog | DDGC::DB::Result::User::Blog | DDGC::DB::Result::Comment
DDGC::DB::Result::User::Report | |
DDGC::DB::Result::Idea | DDGC::DB::Result::Idea |
DDGC::DB::Result::Idea | DDGC::DB::Result::Idea |
DDGC::DB::Result::Idea | DDGC::DB::Result::Idea | DDGC::DB::Result::Idea::Vote
DDGC::DB::Result::Idea | DDGC::DB::Result::Idea | DDGC::DB::Result::Idea::Vote
DDGC::DB::Result::User::Blog | DDGC::DB::Result::User::Blog |
DDGC::DB::Result::Comment | |
DDGC::DB::Result::Idea | |
DDGC::DB::Result::Thread | |
DDGC::DB::Result::Token | DDGC::DB::Result::Token::Domain |
DDGC::DB::Result::User::Blog | DDGC::DB::Result::User::Blog | DDGC::DB::Result::Comment
DDGC::DB::Result::Thread | DDGC::DB::Result::Thread |
DDGC::DB::Result::User::Blog | DDGC::DB::Result::User::Blog |
DDGC::DB::Result::Token::Language | DDGC::DB::Result::Token::Language | DDGC::DB::Result::Comment
DDGC::DB::Result::Token::Domain::Language | DDGC::DB::Result::Token::Domain::Language | DDGC::DB::Result::Comment
DDGC::DB::Result::Token::Language::Translation | DDGC::DB::Result::Token::Domain::Language |
DDGC::DB::Result::Thread | DDGC::DB::Result::Thread | DDGC::DB::Result::Comment
DDGC::DB::Result::Thread | DDGC::DB::Result::Thread | DDGC::DB::Result::Comment
DDGC::DB::Result::Idea | DDGC::DB::Result::Idea | DDGC::DB::Result::Comment
DDGC::DB::Result::Idea | DDGC::DB::Result::Idea | DDGC::DB::Result::Comment
DDGC::DB::Result::Token::Language::Translation | DDGC::DB::Result::Token::Language::Translation | DDGC::DB::Result::Token::Language::Translation::Vote
DDGC::DB::Result::Idea | DDGC::DB::Result::Idea |
(24 rows)
This is a DBIx::Class view which joins user_notification
and user_notification_group
.
This appears to relate an event to its full context, e.g. linking a Comment to a Blog post:
ddgc=# select * from event where id = 1795;
id | users_id | action | context | context_id | notified | data | created | updated | nid | pid
------+----------+--------+---------------------------+------------+----------+------+------------------------+------------------------+-----+------
1795 | 3 | create | DDGC::DB::Result::Comment | 1060 | 1 | | 2014-03-21 10:27:08+00 | 2014-03-21 10:28:18+00 | 1 | 3437
ddgc=# select * from event_relate where event_id = 1795;
id | event_id | context | context_id | created
------+----------+------------------------------+------------+------------------------
4483 | 1795 | DDGC::DB::Result::Comment | 1059 | 2014-03-21 10:27:08+00
4484 | 1795 | DDGC::DB::Result::User::Blog | 28 | 2014-03-21 10:27:08+00
(2 rows)
Or a translation vote to a translation, token, language etc.:
ddgc=# select * from event where id = 985;
id | users_id | action | context | context_id | notified | data | created | updated | nid | pid
-----+----------+--------+------------------------------------------------------+------------+----------+------+------------------------+------------------------+-----+------
985 | 2 | create | DDGC::DB::Result::Token::Language::Translation::Vote | 1 | 1 | | 2014-03-21 10:26:33+00 | 2014-03-21 10:28:00+00 | 1 | 3437
(1 row)
ddgc=# select * from event_relate where event_id = 985;
id | event_id | context | context_id | created
------+----------+------------------------------------------------+------------+------------------------
3138 | 985 | DDGC::DB::Result::Token::Domain | 2 | 2014-03-21 10:26:33+00
3139 | 985 | DDGC::DB::Result::Language | 15 | 2014-03-21 10:26:33+00
3140 | 985 | DDGC::DB::Result::Token | 241 | 2014-03-21 10:26:33+00
3141 | 985 | DDGC::DB::Result::Token::Language | 1117 | 2014-03-21 10:26:33+00
3142 | 985 | DDGC::DB::Result::Token::Language::Translation | 1 | 2014-03-21 10:26:33+00
In DDGC::Web::Controller::My::Notifications
we can see the queries which retrieve undone notifications or mark notifications done.
We can infer quite a bit from generating new events and taking a look at the changes in certain tables (though we will take a look at the code behind this to confirm the findings). While entries in the Events table would appear to be core to the Event Notification system, they can in fact be safely ignored for the purposes of this section. We can also ignore User Notification Group as those context types are fairly static.
We have two users who will interact here for the purposes of generating events, TestOne (id 1) and TestTwo (id 3).
Our counts are generated by the following simple SQL:
select count (*) as event_notification from event_notification;
select count (*) as event_notification_group from event_notification_group;
select count (*) as user_notification from user_notification;
Our starting counts are:
event_notification: 2432
event_notification_group: 544
user_notification: 1827
TestTwo creates a new post in General Ramblings, which creates two new entries in the User Notification (follow) table:
event_notification: 2432
event_notification_group: 544
user_notification: 1829
ddgc=# select * from user_notification order by id desc limit 2;
id | users_id | user_notification_group_id | context_id | cycle | xmpp | cycle_time | last_check | created
------+----------+----------------------------+------------+-------+------+------------+------------------------+------------------------
1837 | 3 | 1 | 1258 | 1 | 0 | | 2014-03-27 16:22:36+00 | 2014-03-27 16:22:36+00
1836 | 3 | 19 | 66 | 1 | 0 | | 2014-03-27 16:22:35+00 | 2014-03-27 16:22:35+00
ddgc=# select * from user_notification_group where id = 1 or id = 19;
id | type | context | with_context_id | group_context | sub_context | action | priority | filter_by_language | email_has_content | data | created
----+----------------+---------------------------+-----------------+---------------------------+---------------------------+--------+----------+--------------------+-------------------+------+------------------------
1 | replies | DDGC::DB::Result::Comment | 1 | DDGC::DB::Result::Comment | DDGC::DB::Result::Comment | live | 100 | 0 | 1 | {} | 2014-03-21 10:25:24+00
19 | forum_comments | DDGC::DB::Result::Thread | 1 | DDGC::DB::Result::Thread | DDGC::DB::Result::Comment | live | 0 | 0 | 1 | {} | 2014-03-21 10:25:24+00
(2 rows)
We are subscribed to two new notifications, the thread and the comment in it (Comments in General ramblings are anchored to a Thread like Comments elsewhere are anchored to the Blog Post, Idea, Translation etc.) A Thread is just a forum topic, the thread starter post's text is a comment anchored to the thread... So a thread with title "Hello" and content "World has two table entries:
ddgc=# select * from thread order by id desc limit 1;
id | users_id | forum | comment_id | key | title | data | sticky | readonly | done | deleted | created | updated | ghosted | checked | seen_live | old_url
----+----------+-------+------------+-----+-------+------+--------+----------+------+---------+------------------------+------------------------+---------+---------+-----------+---------
66 | 3 | 1 | 1258 | hello | Hello | {} | 0 | 0 | 0 | 0 | 2014-03-27 16:22:35+00 | 2014-03-27 16:34:11+00 | 0 | | 1 |
(1 row)
ddgc=# select * from comment order by id desc limit 1;
id | users_id | content | context | context_id | deleted | readonly | is_html | data | created | updated | ghosted | checked | seen_live | parent_id
------+----------+---------+--------------------------+------------+---------+----------+---------+------+------------------------+------------------------+---------+---------+-----------+-----------
1258 | 3 | World | DDGC::DB::Result::Thread | 66 | 0 | 0 | 0 | {} | 2014-03-27 16:22:35+00 | 2014-03-27 16:34:11+00 | 0 | | 1 |
(1 row)
And you can see in the context_id field of the user_notification query above that it links to DDGC::DB::Result::Comment id 1258 and DDGC::DB::Result::Thread id 66.
And as we would expect from this, the thread and comment show up in our "Following" page for user TestTwo:
So now that we are subscribed to this page, let's create a notification. Along comes user TestOne and responds "Hey there!", how do our event table counts look now?
event_notification: 2433
event_notification_group: 545
user_notification: 1830
Well we know right off that the User Notification table will have a new entry as user TestOne is now subscribed to replies to the comment "Hey there!":
ddgc=# select * from user_notification order by id desc limit 1;
id | users_id | user_notification_group_id | context_id | cycle | xmpp | cycle_time | last_check | created
------+----------+----------------------------+------------+-------+------+------------+------------------------+------------------------
1838 | 1 | 1 | 1259 | 2 | 0 | | 2014-03-27 16:48:23+00 | 2014-03-27 16:48:23+00
(1 row)
We also have a new event_notification:
ddgc=# select * from event_notification order by id desc limit 1;
id | event_id | sent | event_notification_group_id | user_notification_id | created
------+----------+------+-----------------------------+----------------------+------------------------
2443 | 2526 | 0 | 545 | 1837 | 2014-03-27 16:48:23+00
(1 row)
As you can see, this has a user_notification_id which matches the Comment subscription (1837) in the User Notification table which was created when TestTwo created the thread. An entry in the Event Notification table means TestTwo will have a notification:
There's also a Event Notification Group entry which links the event back to its full Context (the User Notification Group id tells us it's a DDGC::DB::Result::Comment
, Group Context id tells us the entry in the comment table we replied to, the followed comment.
ddgc=# select * from event_notification_group where id = 545;
id | user_notification_group_id | group_context_id | data | created
-----+----------------------------+------------------+------+------------------------
545 | 1 | 1258 | {} | 2014-03-27 16:48:23+00
(1 row)
There are other routes to this information. The actual role of the Event Notification Group table is to deduplicate or group certain classes of events into a single followed event. In our example, the comment is a fairly discrete, standalone event and results in one new entry in the Event Notification table.
If you have submitted translations for a given language for a given token domain, you may be subscribed to receive info on new tokens created for that domain. Each individual token is an Event and an Event Notification, but to generate a notification for each of these would be too much - potentially dozens of tokens will be uploaded at a time.
Linking these Event Notifications to a single Event Notification Group entry (which in turn will link to a User Notification Group denoting the type/context of the notification) will turn each of these sets of events into a single notification:
So we can see here that there are 240 new tokens for translation in one of our domains in TestTwo's (user id 3) notifications. This means we have 240 Event Notifications in one Event Notification Group (id 1 in this case):
ddgc=# select count(distinct event_notification.id)
ddgc-# from event_notification
ddgc-# join user_notification
ddgc-# on user_notification.id = event_notification.user_notification_id
ddgc-# where user_notification.users_id = 3
ddgc-# and event_notification.event_notification_group_id = 1;
count
-------
240
(1 row)
The unique types of notification groupings are the entries in User Notification Group, so to add a new type of notification there, we need a new Context (the storage and triggering mechanisms for the event data), and a new User Notification Group to describe this Context and how it is grouped (if at all).
A complete context describes the notifiable event, e.g. replies to a specific Comment which belongs to a specific Idea or a vote on a specific translation which belongs to a specific language and token domain.
In terms of Events and Notifications, the Contexts are described the the User Notification Groups table, so in our example above where there were grouped by User Notification Group 12:
ddgc=# select * from user_notification_group where id = 12;
id | type | context | with_context_id | group_context | sub_context | action | priority | filter_by_language | email_has_content | data | created
----+--------+-------------------------+-----------------+---------------------------------+-------------+--------+----------+--------------------+-------------------+------+------------------------
12 | tokens | DDGC::DB::Result::Token | 0 | DDGC::DB::Result::Token::Domain | | create | 1 | 0 | 1 | {} | 2014-03-21 10:25:24+00
(1 row)
Tokens, grouped by Domain. The actual text of the entries are the DBIx::Class names of the tables containing associated data.
While the plain SQL above helps demonstrate the core of notification generation, their retrieval is performed by a DBIx::Class prefetch query which pulls in the related data for each notification - so with the queries above we know there might be 240 new tokens in a domain, but we need to know which domain before reporting it.
There is also a DBIx::Class role which handles events in a generic manner for each notification type, hence the idea of a Context - the role uses the classes which extend it, ones which require the notification role, to denote which Context an event occurred in.
You don't want to read this yet...
DB Says:
ddgc=# \d user_notification
Table "public.user_notification"
Column | Type | Modifiers
----------------------------+--------------------------+----------------------------------------------------------------
id | integer | not null default nextval('user_notification_id_seq'::regclass)
users_id | bigint | not null
user_notification_group_id | bigint | not null
context_id | bigint |
cycle | integer | not null
xmpp | integer | not null default 0
cycle_time | timestamp with time zone |
last_check | timestamp with time zone | not null
created | timestamp with time zone | not null
ddgc=# \d user_notification_group
Table "public.user_notification_group"
Column | Type | Modifiers
--------------------+--------------------------+----------------------------------------------------------------------
id | integer | not null default nextval('user_notification_group_id_seq'::regclass)
type | text | not null
context | text | not null
with_context_id | integer | not null
group_context | text |
sub_context | text | not null
action | text | not null
priority | integer | not null
filter_by_language | integer | not null
email_has_content | integer | not null default 1
data | text | not null default '{}'::text
created | timestamp with time zone | not null
ddgc=# \d event
Table "public.event"
Column | Type | Modifiers
------------+--------------------------+----------------------------------------------------
id | integer | not null default nextval('event_id_seq'::regclass)
users_id | bigint |
action | text | not null
context | text | not null
context_id | bigint | not null
notified | integer | not null default 0
data | text |
created | timestamp with time zone | not null
updated | timestamp with time zone | not null
nid | integer | not null
pid | integer | not null
ddgc=# \d event_notification
Table "public.event_notification"
Column | Type | Modifiers
-----------------------------+--------------------------+-----------------------------------------------------------------
id | integer | not null default nextval('event_notification_id_seq'::regclass)
event_id | bigint | not null
sent | integer | not null default 0
event_notification_group_id | bigint | not null
user_notification_id | bigint | not null
created | timestamp with time zone | not null
ddgc=# \d event_notification_group
Table "public.event_notification_group"
Column | Type | Modifiers
----------------------------+--------------------------+-----------------------------------------------------------------------
id | integer | not null default nextval('event_notification_group_id_seq'::regclass)
user_notification_group_id | bigint | not null
group_context_id | bigint | not null
data | text | not null default '{}'::text
created | timestamp with time zone | not null
ddgc=# \d event_relate
Table "public.event_relate"
Column | Type | Modifiers
------------+--------------------------+-----------------------------------------------------------
id | integer | not null default nextval('event_relate_id_seq'::regclass)
event_id | bigint | not null
context | text | not null
context_id | bigint | not null
created | timestamp with time zone | not null
ddgc=# \d user_notification
Table "public.user_notification"
Column | Type | Modifiers
----------------------------+--------------------------+----------------------------------------------------------------
id | integer | not null default nextval('user_notification_id_seq'::regclass)
users_id | bigint | not null
user_notification_group_id | bigint | not null
context_id | bigint |
cycle | integer | not null
xmpp | integer | not null default 0
cycle_time | timestamp with time zone |
last_check | timestamp with time zone | not null
created | timestamp with time zone | not null
ddgc=# \d user_notification_group
Table "public.user_notification_group"
Column | Type | Modifiers
--------------------+--------------------------+----------------------------------------------------------------------
id | integer | not null default nextval('user_notification_group_id_seq'::regclass)
type | text | not null
context | text | not null
with_context_id | integer | not null
group_context | text |
sub_context | text | not null
action | text | not null
priority | integer | not null
filter_by_language | integer | not null
email_has_content | integer | not null default 1
data | text | not null default '{}'::text
created | timestamp with time zone | not null
I say:
?
\o/
|
/ \
ERD say:
DDGC::DB::Result::User::undone_notifications
:
sub undone_notifications {
my ( $self, $limit ) = @_;
$self->schema->resultset('Event::Notification::Group')->prefetch_all->search_rs({
'user_notification.users_id' => $self->id,
},{
order_by => { -desc => 'event_notifications.created' },
cache_for => 45,
$limit ? ( rows => $limit ) : (),
});
}
DDGC::DB::ResultSet::Event::Notification::Group::prefetch_all
:
sub prefetch_all {
my ( $self ) = @_;
$self->search_rs({},{
prefetch => [qw( user_notification_group ),{
event_notifications => [qw( user_notification ),{
event => [qw( user ),{
%{$self->prefetch_context_config('DDGC::DB::Result::Event')},
event_relates => $self->prefetch_context_config('DDGC::DB::Result::Event::Relate'),
}],
}],
}],
});
}
DDGC::DB::Base::ResultSet::prefetch_context_config
:
sub prefetch_context_config {
my ( $self, $result_class, %opts ) = @_;
$result_class = $self->result_class unless defined $result_class;
my %prefetch = map {
defined $result_class->context_config(%opts)->{$_}->{prefetch} && $_ ne $result_class
? (
$result_class->context_config(%opts)->{$_}->{relation} => $result_class->context_config(%opts)->{$_}->{prefetch}
) : ()
} keys %{$self->result_class->context_config};
return \%prefetch;
}
DDGC::DB::Base::Result::context_config:
sub context_config {
my ( $class, %opts ) = @_;
{
'DDGC::DB::Result::Comment' => {
relation => 'comment',
prefetch => [qw( user parent ),(
$opts{comment_prefetch}
? ($opts{comment_prefetch})
: ()
)],
},
'DDGC::DB::Result::Event' => {
relation => 'event',
},
'DDGC::DB::Result::Thread' => {
relation => 'thread',
prefetch => [qw( user comment )],
},
'DDGC::DB::Result::Token' => {
relation => 'token',
prefetch => [qw( token_domain )],
},
'DDGC::DB::Result::Token::Language' => {
relation => 'token_language',
prefetch => [{
token => [qw( token_domain )],
token_domain_language => [qw( token_domain language )],
token_language_translations => [qw( user )]
}],
},
'DDGC::DB::Result::Token::Domain::Language' => {
relation => 'token_domain_language',
prefetch => [qw( token_domain language )],
},
'DDGC::DB::Result::User::Blog' => {
relation => 'user_blog',
prefetch => [qw( user )],
},
'DDGC::DB::Result::Token::Language::Translation::Vote' => {
relation => 'token_language_translation_vote',
prefetch => [qw( user ),{
token_language_translation => [qw( user ),{
token_language => {
token => [qw( token_domain )],
token_domain_language => [qw( token_domain language )],
}
}]
}],
},
}
}
Dumped expanded prefetch config:
prefetch => [
'user_notification_group',
{ event_notifications => [
'user_notification',
{ event => [
'user',
{
comment => [
'user',
'parent'
],
event_relates => {
comment => [
'user',
'parent'
],
thread => [
'user',
'comment'
],
token => [ 'token_domain' ],
token_domain_language
=> [
'token_domain',
'language'
],
token_language
=> [ {
token => [ 'token_domain' ],
token_domain_language
=> [
'token_domain',
'language'
],
=> [
'token_domain',
'language'
],
token_language_translations
=> [ 'user' ]
} ],
token_language_translation_vote
=> [
'user',
{ token_language_translation => [
'user',
{ token_language => {
token => [ 'token_domain' ],
token_domain_language
=> [
'token_domain',
'language'
]
} }
] }
],
user_blog => [ 'user' ]
},
thread => [
'user',
'comment'
],
token => [ 'token_domain' ],
token_domain_language
=> [
'token_domain',
'language'
],
token_language
=> [ {
token => [ 'token_domain' ],
token_domain_language
=> [
'token_domain',
'language'
],
token_language_translations
=> [ 'user' ]
} ],
token_language_translation_vote
=> [
'user',
{ token_language_translation => [
'user',
{ token_language => {
token => [ 'token_domain' ],
token_domain_language
=> [
'token_domain',
'language'
]
} }
] }
],
user_blog => [ 'user' ]
}
] }
] }
],