server/migrations_testing/2020-01-13-025151_create_materialized_views/down.sql

   1 -- functions and triggers
   2 drop trigger refresh_user on user_;
   3 drop function refresh_user();
   4 drop trigger refresh_post on post;
   5 drop function refresh_post();
   6 drop trigger refresh_post_like on post_like;
   7 drop function refresh_post_like();
   8 drop trigger refresh_community on community;
   9 drop function refresh_community();
  10 drop trigger refresh_community_follower on community_follower;
  11 drop function refresh_community_follower();
  12 drop trigger refresh_comment on comment;
  13 drop function refresh_comment();
  14 drop trigger refresh_comment_like on comment_like;
  15 drop function refresh_comment_like();
  16
  17 -- post
  18 -- Recreate the view
  19 drop materialized view post_view;
  20 create view post_view as
  21 with all_post as
  22 (
  23   select
  24   p.*,
  25   (select u.banned from user_ u where p.creator_id = u.id) as banned,
  26   (select cb.id::bool from community_user_ban cb where p.creator_id = cb.user_id and p.community_id = cb.community_id) as banned_from_community,
  27   (select name from user_ where p.creator_id = user_.id) as creator_name,
  28   (select avatar from user_ where p.creator_id = user_.id) as creator_avatar,
  29   (select name from community where p.community_id = community.id) as community_name,
  30   (select removed from community c where p.community_id = c.id) as community_removed,
  31   (select deleted from community c where p.community_id = c.id) as community_deleted,
  32   (select nsfw from community c where p.community_id = c.id) as community_nsfw,
  33   (select count(*) from comment where comment.post_id = p.id) as number_of_comments,
  34   coalesce(sum(pl.score), 0) as score,
  35   count (case when pl.score = 1 then 1 else null end) as upvotes,
  36   count (case when pl.score = -1 then 1 else null end) as downvotes,
  37   hot_rank(coalesce(sum(pl.score) , 0), p.published) as hot_rank
  38   from post p
  39   left join post_like pl on p.id = pl.post_id
  40   group by p.id
  41 )
  42
  43 select
  44 ap.*,
  45 u.id as user_id,
  46 coalesce(pl.score, 0) as my_vote,
  47 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
  48 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
  49 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
  50 from user_ u
  51 cross join all_post ap
  52 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
  53
  54 union all
  55
  56 select
  57 ap.*,
  58 null as user_id,
  59 null as my_vote,
  60 null as subscribed,
  61 null as read,
  62 null as saved
  63 from all_post ap
  64 ;
  65
  66
  67 drop materialized view user_view;
  68 create view user_view as
  69 select id,
  70 name,
  71 avatar,
  72 email,
  73 fedi_name,
  74 admin,
  75 banned,
  76 show_avatars,
  77 send_notifications_to_email,
  78 published,
  79 (select count(*) from post p where p.creator_id = u.id) as number_of_posts,
  80 (select coalesce(sum(score), 0) from post p, post_like pl where u.id = p.creator_id and p.id = pl.post_id) as post_score,
  81 (select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
  82 (select coalesce(sum(score), 0) from comment c, comment_like cl where u.id = c.creator_id and c.id = cl.comment_id) as comment_score
  83 from user_ u;
  84
  85
  86 -- community
  87 drop materialized view community_view;
  88 create view community_view as
  89 with all_community as
  90 (
  91   select *,
  92   (select name from user_ u where c.creator_id = u.id) as creator_name,
  93   (select avatar from user_ u where c.creator_id = u.id) as creator_avatar,
  94   (select name from category ct where c.category_id = ct.id) as category_name,
  95   (select count(*) from community_follower cf where cf.community_id = c.id) as number_of_subscribers,
  96   (select count(*) from post p where p.community_id = c.id) as number_of_posts,
  97   (select count(*) from comment co, post p where c.id = p.community_id and p.id = co.post_id) as number_of_comments,
  98   hot_rank((select count(*) from community_follower cf where cf.community_id = c.id), c.published) as hot_rank
  99   from community c
 100 )
 101
 102 select
 103 ac.*,
 104 u.id as user_id,
 105 (select cf.id::boolean from community_follower cf where u.id = cf.user_id and ac.id = cf.community_id) as subscribed
 106 from user_ u
 107 cross join all_community ac
 108
 109 union all
 110
 111 select
 112 ac.*,
 113 null as user_id,
 114 null as subscribed
 115 from all_community ac
 116 ;
 117
 118 -- reply and comment view
 119 drop view reply_view;
 120 drop view user_mention_view;
 121 drop materialized view comment_view;
 122 create view comment_view as
 123 with all_comment as
 124 (
 125   select
 126   c.*,
 127   (select community_id from post p where p.id = c.post_id),
 128   (select u.banned from user_ u where c.creator_id = u.id) as banned,
 129   (select cb.id::bool from community_user_ban cb, post p where c.creator_id = cb.user_id and p.id = c.post_id and p.community_id = cb.community_id) as banned_from_community,
 130   (select name from user_ where c.creator_id = user_.id) as creator_name,
 131   (select avatar from user_ where c.creator_id = user_.id) as creator_avatar,
 132   coalesce(sum(cl.score), 0) as score,
 133   count (case when cl.score = 1 then 1 else null end) as upvotes,
 134   count (case when cl.score = -1 then 1 else null end) as downvotes
 135   from comment c
 136   left join comment_like cl on c.id = cl.comment_id
 137   group by c.id
 138 )
 139
 140 select
 141 ac.*,
 142 u.id as user_id,
 143 coalesce(cl.score, 0) as my_vote,
 144 (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved
 145 from user_ u
 146 cross join all_comment ac
 147 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 148
 149 union all
 150
 151 select
 152     ac.*,
 153     null as user_id,
 154     null as my_vote,
 155     null as saved
 156 from all_comment ac
 157 ;
 158
 159 create view reply_view as
 160 with closereply as (
 161     select
 162     c2.id,
 163     c2.creator_id as sender_id,
 164     c.creator_id as recipient_id
 165     from comment c
 166     inner join comment c2 on c.id = c2.parent_id
 167     where c2.creator_id != c.creator_id
 168     -- Do union where post is null
 169     union
 170     select
 171     c.id,
 172     c.creator_id as sender_id,
 173     p.creator_id as recipient_id
 174     from comment c, post p
 175     where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
 176 )
 177 select cv.*,
 178 closereply.recipient_id
 179 from comment_view cv, closereply
 180 where closereply.id = cv.id
 181 ;
 182
 183 -- user mention
 184 create view user_mention_view as
 185 select
 186     c.id,
 187     um.id as user_mention_id,
 188     c.creator_id,
 189     c.post_id,
 190     c.parent_id,
 191     c.content,
 192     c.removed,
 193     um.read,
 194     c.published,
 195     c.updated,
 196     c.deleted,
 197     c.community_id,
 198     c.banned,
 199     c.banned_from_community,
 200     c.creator_name,
 201     c.creator_avatar,
 202     c.score,
 203     c.upvotes,
 204     c.downvotes,
 205     c.user_id,
 206     c.my_vote,
 207     c.saved,
 208     um.recipient_id
 209 from user_mention um, comment_view c
 210 where um.comment_id = c.id;
 211