migrations/2020-02-06-165953_change_post_title_length/down.sql

   1 -- Drop the dependent views
   2 drop view post_view;
   3 drop view post_mview;
   4 drop materialized view post_aggregates_mview;
   5 drop view post_aggregates_view;
   6 drop view mod_remove_post_view;
   7 drop view mod_sticky_post_view;
   8 drop view mod_lock_post_view;
   9 drop view mod_remove_comment_view;
  10
  11 alter table post alter column name type varchar(100);
  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), p.published) as hot_rank
  30 from post p
  31 left join post_like pl on p.id = pl.post_id
  32 group by p.id;
  33
  34 create materialized view post_aggregates_mview as select * from post_aggregates_view;
  35
  36 create unique index idx_post_aggregates_mview_id on post_aggregates_mview (id);
  37
  38 create view post_view as
  39 with all_post as (
  40   select
  41   pa.*
  42   from post_aggregates_view pa
  43 )
  44 select
  45 ap.*,
  46 u.id as user_id,
  47 coalesce(pl.score, 0) as my_vote,
  48 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
  49 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
  50 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
  51 from user_ u
  52 cross join all_post ap
  53 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
  54
  55 union all
  56
  57 select
  58 ap.*,
  59 null as user_id,
  60 null as my_vote,
  61 null as subscribed,
  62 null as read,
  63 null as saved
  64 from all_post ap
  65 ;
  66
  67 create view post_mview as
  68 with all_post as (
  69   select
  70   pa.*
  71   from post_aggregates_mview pa
  72 )
  73 select
  74 ap.*,
  75 u.id as user_id,
  76 coalesce(pl.score, 0) as my_vote,
  77 (select cf.id::bool from community_follower cf where u.id = cf.user_id and cf.community_id = ap.community_id) as subscribed,
  78 (select pr.id::bool from post_read pr where u.id = pr.user_id and pr.post_id = ap.id) as read,
  79 (select ps.id::bool from post_saved ps where u.id = ps.user_id and ps.post_id = ap.id) as saved
  80 from user_ u
  81 cross join all_post ap
  82 left join post_like pl on u.id = pl.user_id and ap.id = pl.post_id
  83
  84 union all
  85
  86 select
  87 ap.*,
  88 null as user_id,
  89 null as my_vote,
  90 null as subscribed,
  91 null as read,
  92 null as saved
  93 from all_post ap
  94 ;
  95
  96 -- The mod views
  97
  98 create view mod_remove_post_view as
  99 select mrp.*,
 100 (select name from user_ u where mrp.mod_user_id = u.id) as mod_user_name,
 101 (select name from post p where mrp.post_id = p.id) as post_name,
 102 (select c.id from post p, community c where mrp.post_id = p.id and p.community_id = c.id) as community_id,
 103 (select c.name from post p, community c where mrp.post_id = p.id and p.community_id = c.id) as community_name
 104 from mod_remove_post mrp;
 105
 106 create view mod_lock_post_view as
 107 select mlp.*,
 108 (select name from user_ u where mlp.mod_user_id = u.id) as mod_user_name,
 109 (select name from post p where mlp.post_id = p.id) as post_name,
 110 (select c.id from post p, community c where mlp.post_id = p.id and p.community_id = c.id) as community_id,
 111 (select c.name from post p, community c where mlp.post_id = p.id and p.community_id = c.id) as community_name
 112 from mod_lock_post mlp;
 113
 114 create view mod_remove_comment_view as
 115 select mrc.*,
 116 (select name from user_ u where mrc.mod_user_id = u.id) as mod_user_name,
 117 (select c.id from comment c where mrc.comment_id = c.id) as comment_user_id,
 118 (select name from user_ u, comment c where mrc.comment_id = c.id and u.id = c.creator_id) as comment_user_name,
 119 (select content from comment c where mrc.comment_id = c.id) as comment_content,
 120 (select p.id from post p, comment c where mrc.comment_id = c.id and c.post_id = p.id) as post_id,
 121 (select p.name from post p, comment c where mrc.comment_id = c.id and c.post_id = p.id) as post_name,
 122 (select co.id from comment c, post p, community co where mrc.comment_id = c.id and c.post_id = p.id and p.community_id = co.id) as community_id,
 123 (select co.name from comment c, post p, community co where mrc.comment_id = c.id and c.post_id = p.id and p.community_id = co.id) as community_name
 124 from mod_remove_comment mrc;
 125
 126 create view mod_sticky_post_view as
 127 select msp.*,
 128 (select name from user_ u where msp.mod_user_id = u.id) as mod_user_name,
 129 (select name from post p where msp.post_id = p.id) as post_name,
 130 (select c.id from post p, community c where msp.post_id = p.id and p.community_id = c.id) as community_id,
 131 (select c.name from post p, community c where msp.post_id = p.id and p.community_id = c.id) as community_name
 132 from mod_sticky_post msp;