migrations/2020-02-07-210055_add_comment_subscribed/down.sql

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