migrations/2020-07-12-100442_add_post_title_to_comments_view/down.sql

   1 drop view user_mention_view;
   2 drop view reply_fast_view;
   3 drop view comment_fast_view;
   4 drop view comment_view;
   5
   6 drop view user_mention_fast_view;
   7 drop table comment_aggregates_fast;
   8 drop view comment_aggregates_view;
   9
  10 create view comment_aggregates_view as
  11 select
  12         ct.*,
  13         -- community details
  14         p.community_id,
  15         c.actor_id as community_actor_id,
  16         c."local" as community_local,
  17         c."name" as community_name,
  18         -- creator details
  19         u.banned as banned,
  20   coalesce(cb.id, 0)::bool as banned_from_community,
  21         u.actor_id as creator_actor_id,
  22         u.local as creator_local,
  23         u.name as creator_name,
  24   u.published as creator_published,
  25         u.avatar as creator_avatar,
  26         -- score details
  27         coalesce(cl.total, 0) as score,
  28         coalesce(cl.up, 0) as upvotes,
  29         coalesce(cl.down, 0) as downvotes,
  30         hot_rank(coalesce(cl.total, 0), ct.published) as hot_rank
  31 from comment ct
  32 left join post p on ct.post_id = p.id
  33 left join community c on p.community_id = c.id
  34 left join user_ u on ct.creator_id = u.id
  35 left join community_user_ban cb on ct.creator_id = cb.user_id and p.id = ct.post_id and p.community_id = cb.community_id
  36 left join (
  37         select
  38                 l.comment_id as id,
  39                 sum(l.score) as total,
  40                 count(case when l.score = 1 then 1 else null end) as up,
  41                 count(case when l.score = -1 then 1 else null end) as down
  42         from comment_like l
  43         group by comment_id
  44 ) as cl on cl.id = ct.id;
  45
  46 create or replace view comment_view as (
  47 select
  48         cav.*,
  49   us.user_id as user_id,
  50   us.my_vote as my_vote,
  51   us.is_subbed::bool as subscribed,
  52   us.is_saved::bool as saved
  53 from comment_aggregates_view cav
  54 cross join lateral (
  55         select
  56                 u.id as user_id,
  57                 coalesce(cl.score, 0) as my_vote,
  58     coalesce(cf.id, 0) as is_subbed,
  59     coalesce(cs.id, 0) as is_saved
  60         from user_ u
  61         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
  62         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
  63         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
  64 ) as us
  65
  66 union all
  67
  68 select
  69     cav.*,
  70     null as user_id,
  71     null as my_vote,
  72     null as subscribed,
  73     null as saved
  74 from comment_aggregates_view cav
  75 );
  76
  77 create table comment_aggregates_fast as select * from comment_aggregates_view;
  78 alter table comment_aggregates_fast add primary key (id);
  79
  80 create view comment_fast_view as
  81 select
  82         cav.*,
  83   us.user_id as user_id,
  84   us.my_vote as my_vote,
  85   us.is_subbed::bool as subscribed,
  86   us.is_saved::bool as saved
  87 from comment_aggregates_fast cav
  88 cross join lateral (
  89         select
  90                 u.id as user_id,
  91                 coalesce(cl.score, 0) as my_vote,
  92     coalesce(cf.id, 0) as is_subbed,
  93     coalesce(cs.id, 0) as is_saved
  94         from user_ u
  95         left join comment_like cl on u.id = cl.user_id and cav.id = cl.comment_id
  96         left join comment_saved cs on u.id = cs.user_id and cs.comment_id = cav.id
  97         left join community_follower cf on u.id = cf.user_id and cav.community_id = cf.community_id
  98 ) as us
  99
 100 union all
 101
 102 select
 103     cav.*,
 104     null as user_id,
 105     null as my_vote,
 106     null as subscribed,
 107     null as saved
 108 from comment_aggregates_fast cav;
 109
 110 create view user_mention_view as
 111 select
 112     c.id,
 113     um.id as user_mention_id,
 114     c.creator_id,
 115     c.creator_actor_id,
 116     c.creator_local,
 117     c.post_id,
 118     c.parent_id,
 119     c.content,
 120     c.removed,
 121     um.read,
 122     c.published,
 123     c.updated,
 124     c.deleted,
 125     c.community_id,
 126     c.community_actor_id,
 127     c.community_local,
 128     c.community_name,
 129     c.banned,
 130     c.banned_from_community,
 131     c.creator_name,
 132     c.creator_avatar,
 133     c.score,
 134     c.upvotes,
 135     c.downvotes,
 136     c.hot_rank,
 137     c.user_id,
 138     c.my_vote,
 139     c.saved,
 140     um.recipient_id,
 141     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 142     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 143 from user_mention um, comment_view c
 144 where um.comment_id = c.id;
 145
 146 create view user_mention_fast_view as
 147 select
 148     ac.id,
 149     um.id as user_mention_id,
 150     ac.creator_id,
 151     ac.creator_actor_id,
 152     ac.creator_local,
 153     ac.post_id,
 154     ac.parent_id,
 155     ac.content,
 156     ac.removed,
 157     um.read,
 158     ac.published,
 159     ac.updated,
 160     ac.deleted,
 161     ac.community_id,
 162     ac.community_actor_id,
 163     ac.community_local,
 164     ac.community_name,
 165     ac.banned,
 166     ac.banned_from_community,
 167     ac.creator_name,
 168     ac.creator_avatar,
 169     ac.score,
 170     ac.upvotes,
 171     ac.downvotes,
 172     ac.hot_rank,
 173     u.id as user_id,
 174     coalesce(cl.score, 0) as my_vote,
 175     (select cs.id::bool from comment_saved cs where u.id = cs.user_id and cs.comment_id = ac.id) as saved,
 176     um.recipient_id,
 177     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 178     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 179 from user_ u
 180 cross join (
 181   select
 182   ca.*
 183   from comment_aggregates_fast ca
 184 ) ac
 185 left join comment_like cl on u.id = cl.user_id and ac.id = cl.comment_id
 186 left join user_mention um on um.comment_id = ac.id
 187
 188 union all
 189
 190 select
 191     ac.id,
 192     um.id as user_mention_id,
 193     ac.creator_id,
 194     ac.creator_actor_id,
 195     ac.creator_local,
 196     ac.post_id,
 197     ac.parent_id,
 198     ac.content,
 199     ac.removed,
 200     um.read,
 201     ac.published,
 202     ac.updated,
 203     ac.deleted,
 204     ac.community_id,
 205     ac.community_actor_id,
 206     ac.community_local,
 207     ac.community_name,
 208     ac.banned,
 209     ac.banned_from_community,
 210     ac.creator_name,
 211     ac.creator_avatar,
 212     ac.score,
 213     ac.upvotes,
 214     ac.downvotes,
 215     ac.hot_rank,
 216     null as user_id,
 217     null as my_vote,
 218     null as saved,
 219     um.recipient_id,
 220     (select actor_id from user_ u where u.id = um.recipient_id) as recipient_actor_id,
 221     (select local from user_ u where u.id = um.recipient_id) as recipient_local
 222 from comment_aggregates_fast ac
 223 left join user_mention um on um.comment_id = ac.id
 224 ;
 225
 226 -- Do the reply_view referencing the comment_fast_view
 227 create view reply_fast_view as
 228 with closereply as (
 229     select
 230     c2.id,
 231     c2.creator_id as sender_id,
 232     c.creator_id as recipient_id
 233     from comment c
 234     inner join comment c2 on c.id = c2.parent_id
 235     where c2.creator_id != c.creator_id
 236     -- Do union where post is null
 237     union
 238     select
 239     c.id,
 240     c.creator_id as sender_id,
 241     p.creator_id as recipient_id
 242     from comment c, post p
 243     where c.post_id = p.id and c.parent_id is null and c.creator_id != p.creator_id
 244 )
 245 select cv.*,
 246 closereply.recipient_id
 247 from comment_fast_view cv, closereply
 248 where closereply.id = cv.id
 249 ;