lemmy/migrations/2020-12-04-183345_create_community_aggregates/up.sql

139 lines
3.9 KiB
MySQL
Raw Permalink Normal View History

2020-12-04 21:35:46 +00:00
-- Add community aggregates
create table community_aggregates (
id serial primary key,
community_id int references community on update cascade on delete cascade not null,
2020-12-09 16:52:10 +00:00
subscribers bigint not null default 0,
posts bigint not null default 0,
comments bigint not null default 0,
2021-01-06 04:42:48 +00:00
published timestamp not null default now(),
2020-12-04 21:35:46 +00:00
unique (community_id)
);
2021-01-06 04:42:48 +00:00
insert into community_aggregates (community_id, subscribers, posts, comments, published)
2020-12-04 21:35:46 +00:00
select
c.id,
2020-12-09 16:52:10 +00:00
coalesce(cf.subs, 0) as subscribers,
coalesce(cd.posts, 0) as posts,
2021-01-06 04:42:48 +00:00
coalesce(cd.comments, 0) as comments,
c.published
2020-12-04 21:35:46 +00:00
from community c
left join (
select
p.community_id,
count(distinct p.id) as posts,
count(distinct ct.id) as comments
from post p
left join comment ct on p.id = ct.post_id
group by p.community_id
) cd on cd.community_id = c.id
left join (
select
community_follower.community_id,
count(*) as subs
from community_follower
group by community_follower.community_id
) cf on cf.community_id = c.id;
-- Add community aggregate triggers
2020-12-09 16:52:10 +00:00
-- initial community add
create function community_aggregates_community()
returns trigger language plpgsql
as $$
begin
IF (TG_OP = 'INSERT') THEN
insert into community_aggregates (community_id) values (NEW.id);
ELSIF (TG_OP = 'DELETE') THEN
delete from community_aggregates where community_id = OLD.id;
END IF;
return null;
end $$;
create trigger community_aggregates_community
after insert or delete on community
for each row
execute procedure community_aggregates_community();
2020-12-04 21:35:46 +00:00
-- post count
create function community_aggregates_post_count()
returns trigger language plpgsql
as $$
begin
IF (TG_OP = 'INSERT') THEN
update community_aggregates
set posts = posts + 1 where community_id = NEW.community_id;
ELSIF (TG_OP = 'DELETE') THEN
update community_aggregates
set posts = posts - 1 where community_id = OLD.community_id;
2020-12-09 16:52:10 +00:00
-- Update the counts if the post got deleted
update community_aggregates ca
set posts = coalesce(cd.posts, 0),
comments = coalesce(cd.comments, 0)
from (
select
c.id,
count(distinct p.id) as posts,
count(distinct ct.id) as comments
from community c
left join post p on c.id = p.community_id
left join comment ct on p.id = ct.post_id
group by c.id
) cd
where ca.community_id = OLD.community_id;
2020-12-04 21:35:46 +00:00
END IF;
return null;
end $$;
create trigger community_aggregates_post_count
after insert or delete on post
2020-12-07 03:17:52 +00:00
for each row
2020-12-04 21:35:46 +00:00
execute procedure community_aggregates_post_count();
-- comment count
create function community_aggregates_comment_count()
returns trigger language plpgsql
as $$
begin
IF (TG_OP = 'INSERT') THEN
2020-12-09 16:52:10 +00:00
update community_aggregates ca
set comments = comments + 1 from comment c, post p
where p.id = c.post_id
and p.id = NEW.post_id
and ca.community_id = p.community_id;
2020-12-04 21:35:46 +00:00
ELSIF (TG_OP = 'DELETE') THEN
2020-12-09 16:52:10 +00:00
update community_aggregates ca
set comments = comments - 1 from comment c, post p
where p.id = c.post_id
and p.id = OLD.post_id
and ca.community_id = p.community_id;
2020-12-04 21:35:46 +00:00
END IF;
return null;
end $$;
create trigger community_aggregates_comment_count
after insert or delete on comment
2020-12-07 03:17:52 +00:00
for each row
2020-12-04 21:35:46 +00:00
execute procedure community_aggregates_comment_count();
-- subscriber count
create function community_aggregates_subscriber_count()
returns trigger language plpgsql
as $$
begin
IF (TG_OP = 'INSERT') THEN
update community_aggregates
set subscribers = subscribers + 1 where community_id = NEW.community_id;
ELSIF (TG_OP = 'DELETE') THEN
update community_aggregates
set subscribers = subscribers - 1 where community_id = OLD.community_id;
END IF;
return null;
end $$;
create trigger community_aggregates_subscriber_count
after insert or delete on community_follower
2020-12-07 03:17:52 +00:00
for each row
2020-12-04 21:35:46 +00:00
execute procedure community_aggregates_subscriber_count();