lemmy/migrations/2020-12-03-035643_create_us.../up.sql

179 lines
4.9 KiB
MySQL
Raw Permalink Normal View History

2020-12-03 14:27:22 +00:00
-- Add user aggregates
create table user_aggregates (
id serial primary key,
user_id int references user_ on update cascade on delete cascade not null,
2020-12-07 20:58:53 +00:00
post_count bigint not null default 0,
post_score bigint not null default 0,
comment_count bigint not null default 0,
comment_score bigint not null default 0,
2020-12-03 14:27:22 +00:00
unique (user_id)
);
insert into user_aggregates (user_id, post_count, post_score, comment_count, comment_score)
select u.id,
coalesce(pd.posts, 0),
coalesce(pd.score, 0),
coalesce(cd.comments, 0),
coalesce(cd.score, 0)
from user_ u
left join (
select p.creator_id,
count(distinct p.id) as posts,
sum(pl.score) as score
from post p
left join post_like pl on p.id = pl.post_id
group by p.creator_id
) pd on u.id = pd.creator_id
left join (
select c.creator_id,
count(distinct c.id) as comments,
sum(cl.score) as score
from comment c
left join comment_like cl on c.id = cl.comment_id
group by c.creator_id
) cd on u.id = cd.creator_id;
2020-12-03 15:18:17 +00:00
-- Add user aggregate triggers
2020-12-07 20:58:53 +00:00
-- initial user add
create function user_aggregates_user()
returns trigger language plpgsql
as $$
begin
IF (TG_OP = 'INSERT') THEN
insert into user_aggregates (user_id) values (NEW.id);
ELSIF (TG_OP = 'DELETE') THEN
delete from user_aggregates where user_id = OLD.id;
END IF;
return null;
end $$;
create trigger user_aggregates_user
after insert or delete on user_
for each row
execute procedure user_aggregates_user();
2020-12-03 15:18:17 +00:00
-- post count
create function user_aggregates_post_count()
2020-12-03 14:27:22 +00:00
returns trigger language plpgsql
as $$
begin
2020-12-03 15:18:17 +00:00
IF (TG_OP = 'INSERT') THEN
update user_aggregates
set post_count = post_count + 1 where user_id = NEW.creator_id;
2020-12-07 20:58:53 +00:00
2020-12-03 15:18:17 +00:00
ELSIF (TG_OP = 'DELETE') THEN
update user_aggregates
set post_count = post_count - 1 where user_id = OLD.creator_id;
2020-12-07 20:58:53 +00:00
-- If the post gets deleted, the score calculation trigger won't fire,
-- so you need to re-calculate
update user_aggregates ua
set post_score = pd.score
from (
select u.id,
coalesce(0, sum(pl.score)) as score
-- User join because posts could be empty
from user_ u
left join post p on u.id = p.creator_id
left join post_like pl on p.id = pl.post_id
group by u.id
) pd
2020-12-09 16:52:10 +00:00
where ua.user_id = OLD.creator_id;
2020-12-07 20:58:53 +00:00
2020-12-03 15:18:17 +00:00
END IF;
2020-12-03 14:27:22 +00:00
return null;
end $$;
2020-12-03 15:18:17 +00:00
create trigger user_aggregates_post_count
after insert or delete on post
2020-12-07 03:17:52 +00:00
for each row
2020-12-03 15:18:17 +00:00
execute procedure user_aggregates_post_count();
2020-12-03 14:27:22 +00:00
2020-12-03 15:18:17 +00:00
-- post score
create function user_aggregates_post_score()
2020-12-03 14:27:22 +00:00
returns trigger language plpgsql
as $$
begin
2020-12-03 15:18:17 +00:00
IF (TG_OP = 'INSERT') THEN
2020-12-03 18:39:56 +00:00
-- Need to get the post creator, not the voter
2020-12-07 20:58:53 +00:00
update user_aggregates ua
2020-12-03 18:39:56 +00:00
set post_score = post_score + NEW.score
2020-12-07 20:58:53 +00:00
from post p
where ua.user_id = p.creator_id and p.id = NEW.post_id;
2020-12-03 15:18:17 +00:00
ELSIF (TG_OP = 'DELETE') THEN
2020-12-07 20:58:53 +00:00
update user_aggregates ua
2020-12-03 18:39:56 +00:00
set post_score = post_score - OLD.score
2020-12-07 20:58:53 +00:00
from post p
where ua.user_id = p.creator_id and p.id = OLD.post_id;
2020-12-03 15:18:17 +00:00
END IF;
2020-12-03 14:27:22 +00:00
return null;
end $$;
2020-12-03 15:18:17 +00:00
create trigger user_aggregates_post_score
after insert or delete on post_like
2020-12-07 03:17:52 +00:00
for each row
2020-12-03 15:18:17 +00:00
execute procedure user_aggregates_post_score();
2020-12-03 14:27:22 +00:00
2020-12-03 15:18:17 +00:00
-- comment count
create function user_aggregates_comment_count()
2020-12-03 14:27:22 +00:00
returns trigger language plpgsql
as $$
begin
2020-12-03 15:18:17 +00:00
IF (TG_OP = 'INSERT') THEN
update user_aggregates
set comment_count = comment_count + 1 where user_id = NEW.creator_id;
2020-12-03 15:18:17 +00:00
ELSIF (TG_OP = 'DELETE') THEN
update user_aggregates
set comment_count = comment_count - 1 where user_id = OLD.creator_id;
2020-12-07 20:58:53 +00:00
-- If the comment gets deleted, the score calculation trigger won't fire,
-- so you need to re-calculate
update user_aggregates ua
set comment_score = cd.score
from (
select u.id,
coalesce(0, sum(cl.score)) as score
-- User join because comments could be empty
from user_ u
left join comment c on u.id = c.creator_id
left join comment_like cl on c.id = cl.comment_id
group by u.id
) cd
2020-12-09 16:52:10 +00:00
where ua.user_id = OLD.creator_id;
2020-12-03 15:18:17 +00:00
END IF;
2020-12-03 14:27:22 +00:00
return null;
end $$;
2020-12-03 15:18:17 +00:00
create trigger user_aggregates_comment_count
after insert or delete on comment
2020-12-07 03:17:52 +00:00
for each row
2020-12-03 15:18:17 +00:00
execute procedure user_aggregates_comment_count();
2020-12-03 14:27:22 +00:00
2020-12-03 15:18:17 +00:00
-- comment score
create function user_aggregates_comment_score()
2020-12-03 14:27:22 +00:00
returns trigger language plpgsql
as $$
begin
2020-12-03 15:18:17 +00:00
IF (TG_OP = 'INSERT') THEN
2020-12-03 18:39:56 +00:00
-- Need to get the post creator, not the voter
2020-12-07 20:58:53 +00:00
update user_aggregates ua
2020-12-03 18:39:56 +00:00
set comment_score = comment_score + NEW.score
2020-12-07 20:58:53 +00:00
from comment c
where ua.user_id = c.creator_id and c.id = NEW.comment_id;
2020-12-03 15:18:17 +00:00
ELSIF (TG_OP = 'DELETE') THEN
2020-12-07 20:58:53 +00:00
update user_aggregates ua
2020-12-03 18:39:56 +00:00
set comment_score = comment_score - OLD.score
2020-12-07 20:58:53 +00:00
from comment c
where ua.user_id = c.creator_id and c.id = OLD.comment_id;
2020-12-03 15:18:17 +00:00
END IF;
2020-12-03 14:27:22 +00:00
return null;
end $$;
2020-12-03 15:18:17 +00:00
create trigger user_aggregates_comment_score
after insert or delete on comment_like
2020-12-07 20:58:53 +00:00
for each row
2020-12-03 15:18:17 +00:00
execute procedure user_aggregates_comment_score();