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