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