diff options
Diffstat (limited to 'server/migrations')
4 files changed, 120 insertions, 0 deletions
diff --git a/server/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql b/server/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql new file mode 100644 index 00000000..06ec5971 --- /dev/null +++ b/server/migrations/2020-01-29-011901_create_reply_materialized_view/down.sql @@ -0,0 +1,25 @@ +-- Drop the materialized / built views +drop view reply_view; +create view reply_view as +with closereply as ( + select + c2.id, + c2.creator_id as sender_id, + c.creator_id as recipient_id + from comment c + inner join comment c2 on c.id = c2.parent_id + where c2.creator_id != c.creator_id + -- Do union where post is null + union + select + c.id, + c.creator_id as sender_id, + p.creator_id as recipient_id + from comment c, post p + where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id +) +select cv.*, +closereply.recipient_id +from comment_view cv, closereply +where closereply.id = cv.id +; diff --git a/server/migrations/2020-01-29-011901_create_reply_materialized_view/up.sql b/server/migrations/2020-01-29-011901_create_reply_materialized_view/up.sql new file mode 100644 index 00000000..ebbb1dff --- /dev/null +++ b/server/migrations/2020-01-29-011901_create_reply_materialized_view/up.sql @@ -0,0 +1,27 @@ +-- https://github.com/dessalines/lemmy/issues/197 +drop view reply_view; + +-- Do the reply_view referencing the comment_mview +create view reply_view as +with closereply as ( + select + c2.id, + c2.creator_id as sender_id, + c.creator_id as recipient_id + from comment c + inner join comment c2 on c.id = c2.parent_id + where c2.creator_id != c.creator_id + -- Do union where post is null + union + select + c.id, + c.creator_id as sender_id, + p.creator_id as recipient_id + from comment c, post p + where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id +) +select cv.*, +closereply.recipient_id +from comment_mview cv, closereply +where closereply.id = cv.id +; diff --git a/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/down.sql b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/down.sql new file mode 100644 index 00000000..d93ebc2e --- /dev/null +++ b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/down.sql @@ -0,0 +1 @@ +drop view user_mention_mview; diff --git a/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql new file mode 100644 index 00000000..b0ae4e9d --- /dev/null +++ b/server/migrations/2020-01-29-030825_create_user_mention_materialized_view/up.sql @@ -0,0 +1,67 @@ +create view user_mention_mview as +with all_comment as +( + select + ca.* + from comment_aggregates_mview ca +) + +select + ac.id, + um.id as user_mention_id, + ac.creator_id, + ac.post_id, + ac.parent_id, + ac.content, + ac.removed, + um.read, + ac.published, + ac.updated, + ac.deleted, + ac.community_id, + ac.banned, + ac.banned_from_community, + ac.creator_name, + ac.creator_avatar, + ac.score, + ac.upvotes, + ac.downvotes, + u.id as user_id, + coalesce(cl.score, 0) as my_vote, + (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved, + um.recipient_id +from user_ u +cross join all_comment ac +left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id +left join user_mention um on um.comment_id = ac.id + +union all + +select + ac.id, + um.id as user_mention_id, + ac.creator_id, + ac.post_id, + ac.parent_id, + ac.content, + ac.removed, + um.read, + ac.published, + ac.updated, + ac.deleted, + ac.community_id, + ac.banned, + ac.banned_from_community, + ac.creator_name, + ac.creator_avatar, + ac.score, + ac.upvotes, + ac.downvotes, + null as user_id, + null as my_vote, + null as saved, + um.recipient_id +from all_comment ac +left join user_mention um on um.comment_id = ac.id +; + |