server/migrations/2020-03-06-202329_add_post_iframely_data/down.sql

   1 -- Adds a newest_activity_time for the post_views, in order to sort by newest comment
   2 drop view post_view;
   3 drop view post_mview;
   4 drop materialized view post_aggregates_mview;
   5 drop view post_aggregates_view;
   6
   7 -- Drop the columns
   8 alter table post drop column embed_title;
   9 alter table post drop column embed_description;
  10 alter table post drop column embed_html;
  11 alter table post drop column thumbnail_url;
  12
  13 -- regen post view
  14 create view post_aggregates_view as
  15 select
  16 p.*,
  17 (select u.banned from user_ u where p.creator_id = u.id) as banned,
  18 (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,
  19 (select name from user_ where p.creator_id = user_.id) as creator_name,
  20 (select avatar from user_ where p.creator_id = user_.id) as creator_avatar,
  21 (select name from community where p.community_id = community.id) as community_name,
  22 (select removed from community c where p.community_id = c.id) as community_removed,
  23 (select deleted from community c where p.community_id = c.id) as community_deleted,
  24 (select nsfw from community c where p.community_id = c.id) as community_nsfw,
  25 (select count(*) from comment where comment.post_id = p.id) as number_of_comments,
  26 coalesce(sum(pl.score), 0) as score,
  27 count (case when pl.score = 1 then 1 else null end) as upvotes,
  28 count (case when pl.score = -1 then 1 else null end) as downvotes,
  29 hot_rank(coalesce(sum(pl.score) , 0),
  30   (
  31     case when (p.published < ('now'::timestamp - '1 month'::interval)) then p.published -- Prevents necro-bumps
  32     else greatest(c.recent_comment_time, p.published)
  33     end
  34   )
  35 ) as hot_rank,
  36 (
  37   case when (p.published < ('now'::timestamp - '1 month'::interval)) then p.published -- Prevents necro-bumps
  38   else greatest(c.recent_comment_time, p.published)
  39   end
  40 ) as newest_activity_time
  41 from post p
  42 left join post_like pl on p.id = pl.post_id
  43 left join (
  44   select post_id,
  45   max(published) as recent_comment_time
  46   from comment
  47   group by 1
  48 ) c on p.id = c.post_id
  49 group by p.id, c.recent_comment_time;
  50
  51 create materialized view post_aggregates_mview as select * from post_aggregates_view;
  52
  53 create unique index idx_post_aggregates_mview_id on post_aggregates_mview (id);
  54
  55 create view post_view as
  56 with all_post as (
  57   select
  58   pa.*
  59   from post_aggregates_view pa
  60 )
  61 select
  62 ap.*,
  63 u.id as user_id,
  64 coalesce(pl.score, 0) as my_vote,
  65 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
  66 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
  67 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
  68 from user_ u
  69 cross join all_post ap
  70 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
  71
  72 union all
  73
  74 select
  75 ap.*,
  76 null as user_id,
  77 null as my_vote,
  78 null as subscribed,
  79 null as read,
  80 null as saved
  81 from all_post ap
  82 ;
  83
  84 create view post_mview as
  85 with all_post as (
  86   select
  87   pa.*
  88   from post_aggregates_mview pa
  89 )
  90 select
  91 ap.*,
  92 u.id as user_id,
  93 coalesce(pl.score, 0) as my_vote,
  94 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
  95 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
  96 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
  97 from user_ u
  98 cross join all_post ap
  99 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
 100
 101 union all
 102
 103 select
 104 ap.*,
 105 null as user_id,
 106 null as my_vote,
 107 null as subscribed,
 108 null as read,
 109 null as saved
 110 from all_post ap
 111 ;
 112