From a1838158708df84ed115de5e70539734a1332c4c Mon Sep 17 00:00:00 2001 From: Dessalines Date: Mon, 15 Feb 2021 14:34:10 -0500 Subject: [PATCH] Adding a few more tables. --- .rgignore | 2 +- .../down.sql | 187 +++++++++++++++++- .../2021-02-14-041356_split_user_table/up.sql | 186 ++++++++++++++++- 3 files changed, 369 insertions(+), 6 deletions(-) diff --git a/.rgignore b/.rgignore index d1b811b7..eab207b7 100644 --- a/.rgignore +++ b/.rgignore @@ -1 +1 @@ -*.sql +*.sqldump diff --git a/migrations/2021-02-14-041356_split_user_table/down.sql b/migrations/2021-02-14-041356_split_user_table/down.sql index 218aaec2..c39a8ac8 100644 --- a/migrations/2021-02-14-041356_split_user_table/down.sql +++ b/migrations/2021-02-14-041356_split_user_table/down.sql @@ -1,3 +1,42 @@ +-- user_ban +alter table person_ban rename to user_ban; +alter sequence person_ban_id_seq rename to user_ban_id_seq; +alter index person_ban_pkey rename to user_ban_pkey; +alter index person_ban_person_id_key rename to user_ban_user_id_key; +alter table user_ban rename constraint person_ban_person_id_fkey to user_ban_user_id_fkey; + +-- user_mention +alter table person_mention rename to user_mention; +alter sequence person_mention_id_seq rename to user_mention_id_seq; +alter index person_mention_pkey rename to user_mention_pkey; +alter index person_mention_recipient_id_comment_id_key rename to user_mention_recipient_id_comment_id_key; +alter table user_mention rename constraint person_mention_comment_id_fkey to user_mention_comment_id_fkey; +alter table user_mention rename constraint person_mention_recipient_id_fkey to user_mention_recipient_id_fkey; + +-- User aggregates table +alter table person_aggregates rename to user_aggregates; +alter sequence person_aggregates_id_seq rename to user_aggregates_id_seq; +alter table user_aggregates rename column person_id to user_id; + +-- Indexes +alter index person_aggregates_pkey rename to user_aggregates_pkey; +alter index idx_person_aggregates_comment_score rename to idx_user_aggregates_comment_score; +alter index person_aggregates_person_id_key rename to user_aggregates_user_id_key; +alter table user_aggregates rename constraint person_aggregates_person_id_fkey to user_aggregates_user_id_fkey; + +-- Redo the user_aggregates table +drop trigger person_aggregates_person on person; +drop trigger person_aggregates_post_count on post; +drop trigger person_aggregates_post_score on post_like; +drop trigger person_aggregates_comment_count on comment; +drop trigger person_aggregates_comment_score on comment_like; +drop function + person_aggregates_person, + person_aggregates_post_count, + person_aggregates_post_score, + person_aggregates_comment_count, + person_aggregates_comment_score; + -- user_ table -- Drop views drop view person_alias_1, person_alias_2; @@ -12,15 +51,14 @@ alter index idx_person_published rename to idx_user_published; -- Rename triggers alter trigger site_aggregates_person_delete on person rename to site_aggregates_user_delete; alter trigger site_aggregates_person_insert on person rename to site_aggregates_user_insert; -alter trigger person_aggregates_person on person rename to user_aggregates_user; -- Rename the trigger functions alter function site_aggregates_person_delete() rename to site_aggregates_user_delete; alter function site_aggregates_person_insert() rename to site_aggregates_user_insert; -alter function person_aggregates_person() rename to user_aggregates_user; -- Rename the table back to user_ alter table person rename to user_; +alter sequence person_id_seq rename to user__id_seq; -- Add the columns back in alter table user_ @@ -57,3 +95,148 @@ create view user_alias_2 as select * from user_; drop table local_user; +-- Add the user_aggregates table triggers + +-- 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(); + +-- post count +create function user_aggregates_post_count() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + update user_aggregates + set post_count = post_count + 1 where user_id = NEW.creator_id; + + ELSIF (TG_OP = 'DELETE') THEN + update user_aggregates + set post_count = post_count - 1 where user_id = OLD.creator_id; + + -- 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 + where ua.user_id = OLD.creator_id; + + END IF; + return null; +end $$; + +create trigger user_aggregates_post_count +after insert or delete on post +for each row +execute procedure user_aggregates_post_count(); + +-- post score +create function user_aggregates_post_score() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + -- Need to get the post creator, not the voter + update user_aggregates ua + set post_score = post_score + NEW.score + from post p + where ua.user_id = p.creator_id and p.id = NEW.post_id; + + ELSIF (TG_OP = 'DELETE') THEN + update user_aggregates ua + set post_score = post_score - OLD.score + from post p + where ua.user_id = p.creator_id and p.id = OLD.post_id; + END IF; + return null; +end $$; + +create trigger user_aggregates_post_score +after insert or delete on post_like +for each row +execute procedure user_aggregates_post_score(); + +-- comment count +create function user_aggregates_comment_count() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + update user_aggregates + set comment_count = comment_count + 1 where user_id = NEW.creator_id; + ELSIF (TG_OP = 'DELETE') THEN + update user_aggregates + set comment_count = comment_count - 1 where user_id = OLD.creator_id; + + -- 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 + where ua.user_id = OLD.creator_id; + END IF; + return null; +end $$; + +create trigger user_aggregates_comment_count +after insert or delete on comment +for each row +execute procedure user_aggregates_comment_count(); + +-- comment score +create function user_aggregates_comment_score() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + -- Need to get the post creator, not the voter + update user_aggregates ua + set comment_score = comment_score + NEW.score + from comment c + where ua.user_id = c.creator_id and c.id = NEW.comment_id; + ELSIF (TG_OP = 'DELETE') THEN + update user_aggregates ua + set comment_score = comment_score - OLD.score + from comment c + where ua.user_id = c.creator_id and c.id = OLD.comment_id; + END IF; + return null; +end $$; + +create trigger user_aggregates_comment_score +after insert or delete on comment_like +for each row +execute procedure user_aggregates_comment_score(); + + + diff --git a/migrations/2021-02-14-041356_split_user_table/up.sql b/migrations/2021-02-14-041356_split_user_table/up.sql index 7d673c43..107ee4fc 100644 --- a/migrations/2021-02-14-041356_split_user_table/up.sql +++ b/migrations/2021-02-14-041356_split_user_table/up.sql @@ -1,8 +1,10 @@ +-- Person -- Drop the 2 views user_alias_1, user_alias_2 drop view user_alias_1, user_alias_2; -- rename the user_ table to person alter table user_ rename to person; +alter sequence user__id_seq rename to person_id_seq; -- create a new table local_user create table local_user ( @@ -78,16 +80,194 @@ alter index idx_user_published rename to idx_person_published; -- Rename triggers alter trigger site_aggregates_user_delete on person rename to site_aggregates_person_delete; alter trigger site_aggregates_user_insert on person rename to site_aggregates_person_insert; -alter trigger user_aggregates_user on person rename to person_aggregates_person; -- Rename the trigger functions alter function site_aggregates_user_delete() rename to site_aggregates_person_delete; alter function site_aggregates_user_insert() rename to site_aggregates_person_insert; -alter function user_aggregates_user() rename to person_aggregates_person; -- Create views create view person_alias_1 as select * from person; create view person_alias_2 as select * from person; --- Rename every user_id column to person_id +-- Redo user aggregates into person_aggregates +alter table user_aggregates rename to person_aggregates; +alter sequence user_aggregates_id_seq rename to person_aggregates_id_seq; +alter table person_aggregates rename column user_id to person_id; + +-- index +alter index user_aggregates_pkey rename to person_aggregates_pkey; +alter index idx_user_aggregates_comment_score rename to idx_person_aggregates_comment_score; +alter index user_aggregates_user_id_key rename to person_aggregates_person_id_key; +alter table person_aggregates rename constraint user_aggregates_user_id_fkey to person_aggregates_person_id_fkey; + + +-- Drop all the old triggers and functions +drop trigger user_aggregates_user on person; +drop trigger user_aggregates_post_count on post; +drop trigger user_aggregates_post_score on post_like; +drop trigger user_aggregates_comment_count on comment; +drop trigger user_aggregates_comment_score on comment_like; +drop function + user_aggregates_user, + user_aggregates_post_count, + user_aggregates_post_score, + user_aggregates_comment_count, + user_aggregates_comment_score; + +-- initial user add +create function person_aggregates_person() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + insert into person_aggregates (person_id) values (NEW.id); + ELSIF (TG_OP = 'DELETE') THEN + delete from person_aggregates where person_id = OLD.id; + END IF; + return null; +end $$; + +create trigger person_aggregates_person +after insert or delete on person +for each row +execute procedure person_aggregates_person(); + +-- post count +create function person_aggregates_post_count() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + update person_aggregates + set post_count = post_count + 1 where person_id = NEW.creator_id; + + ELSIF (TG_OP = 'DELETE') THEN + update person_aggregates + set post_count = post_count - 1 where person_id = OLD.creator_id; + + -- If the post gets deleted, the score calculation trigger won't fire, + -- so you need to re-calculate + update person_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 person 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 + where ua.person_id = OLD.creator_id; + + END IF; + return null; +end $$; + +create trigger person_aggregates_post_count +after insert or delete on post +for each row +execute procedure person_aggregates_post_count(); + +-- post score +create function person_aggregates_post_score() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + -- Need to get the post creator, not the voter + update person_aggregates ua + set post_score = post_score + NEW.score + from post p + where ua.person_id = p.creator_id and p.id = NEW.post_id; + + ELSIF (TG_OP = 'DELETE') THEN + update person_aggregates ua + set post_score = post_score - OLD.score + from post p + where ua.person_id = p.creator_id and p.id = OLD.post_id; + END IF; + return null; +end $$; + +create trigger person_aggregates_post_score +after insert or delete on post_like +for each row +execute procedure person_aggregates_post_score(); + +-- comment count +create function person_aggregates_comment_count() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + update person_aggregates + set comment_count = comment_count + 1 where person_id = NEW.creator_id; + ELSIF (TG_OP = 'DELETE') THEN + update person_aggregates + set comment_count = comment_count - 1 where person_id = OLD.creator_id; + + -- If the comment gets deleted, the score calculation trigger won't fire, + -- so you need to re-calculate + update person_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 person 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 + where ua.person_id = OLD.creator_id; + END IF; + return null; +end $$; + +create trigger person_aggregates_comment_count +after insert or delete on comment +for each row +execute procedure person_aggregates_comment_count(); + +-- comment score +create function person_aggregates_comment_score() +returns trigger language plpgsql +as $$ +begin + IF (TG_OP = 'INSERT') THEN + -- Need to get the post creator, not the voter + update person_aggregates ua + set comment_score = comment_score + NEW.score + from comment c + where ua.person_id = c.creator_id and c.id = NEW.comment_id; + ELSIF (TG_OP = 'DELETE') THEN + update person_aggregates ua + set comment_score = comment_score - OLD.score + from comment c + where ua.person_id = c.creator_id and c.id = OLD.comment_id; + END IF; + return null; +end $$; + +create trigger person_aggregates_comment_score +after insert or delete on comment_like +for each row +execute procedure person_aggregates_comment_score(); + +-- person_mention +alter table user_mention rename to person_mention; +alter sequence user_mention_id_seq rename to person_mention_id_seq; +alter index user_mention_pkey rename to person_mention_pkey; +alter index user_mention_recipient_id_comment_id_key rename to person_mention_recipient_id_comment_id_key; +alter table person_mention rename constraint user_mention_comment_id_fkey to person_mention_comment_id_fkey; +alter table person_mention rename constraint user_mention_recipient_id_fkey to person_mention_recipient_id_fkey; + +-- user_ban +alter table user_ban rename to person_ban; +alter sequence user_ban_id_seq rename to person_ban_id_seq; +alter index user_ban_pkey rename to person_ban_pkey; +alter index user_ban_user_id_key rename to person_ban_person_id_key; +alter table person_ban rename constraint user_ban_user_id_fkey to person_ban_person_id_fkey; + -- 2.44.1