2 ALTER TABLE user_ RENAME COLUMN icon TO avatar;
5 ALTER COLUMN avatar TYPE text;
7 -- Rebuild nearly all the views, to include the creator avatars
11 CREATE VIEW user_view AS
26 p.creator_id = u.id) AS number_of_posts,
29 coalesce(sum(score), 0)
35 AND p.id = pl.post_id) AS post_score,
42 c.creator_id = u.id) AS number_of_comments,
45 coalesce(sum(score), 0)
51 AND c.id = cl.comment_id) AS comment_score
59 CREATE VIEW post_view AS
69 p.creator_id = u.id) AS banned,
76 p.creator_id = cb.user_id
77 AND p.community_id = cb.community_id) AS banned_from_community,
84 p.creator_id = user_.id) AS creator_name,
91 p.creator_id = user_.id) AS creator_avatar,
98 p.community_id = community.id) AS community_name,
105 p.community_id = c.id) AS community_removed,
112 p.community_id = c.id) AS community_deleted,
119 p.community_id = c.id) AS community_nsfw,
126 comment.post_id = p.id) AS number_of_comments,
127 coalesce(sum(pl.score), 0) AS score,
129 CASE WHEN pl.score = 1 THEN
135 CASE WHEN pl.score = - 1 THEN
140 hot_rank (coalesce(sum(pl.score), 0), p.published) AS hot_rank
143 LEFT JOIN post_like pl ON p.id = pl.post_id
150 coalesce(pl.score, 0) AS my_vote,
155 community_follower cf
158 AND cf.community_id = ap.community_id) AS subscribed,
166 AND pr.post_id = ap.id) AS read,
174 AND ps.post_id = ap.id) AS saved
177 CROSS JOIN all_post ap
178 LEFT JOIN post_like pl ON u.id = pl.user_id
179 AND ap.id = pl.post_id
192 DROP VIEW community_view;
194 CREATE VIEW community_view AS
195 with all_community AS (
204 c.creator_id = u.id) AS creator_name,
211 c.creator_id = u.id) AS creator_avatar,
218 c.category_id = ct.id) AS category_name,
223 community_follower cf
225 cf.community_id = c.id) AS number_of_subscribers,
232 p.community_id = c.id) AS number_of_posts,
240 c.id = p.community_id
241 AND p.id = co.post_id) AS number_of_comments,
245 FROM community_follower cf
247 cf.community_id = c.id), c.published) AS hot_rank
258 community_follower cf
261 AND ac.id = cf.community_id) AS subscribed
264 CROSS JOIN all_community ac
273 -- reply and comment view
274 DROP VIEW reply_view;
276 DROP VIEW user_mention_view;
278 DROP VIEW comment_view;
280 CREATE VIEW comment_view AS
281 with all_comment AS (
297 c.creator_id = u.id) AS banned,
302 community_user_ban cb,
305 c.creator_id = cb.user_id
307 AND p.community_id = cb.community_id) AS banned_from_community,
314 c.creator_id = user_.id) AS creator_name,
321 c.creator_id = user_.id) AS creator_avatar,
322 coalesce(sum(cl.score), 0) AS score,
324 CASE WHEN cl.score = 1 THEN
330 CASE WHEN cl.score = - 1 THEN
337 LEFT JOIN comment_like cl ON c.id = cl.comment_id
344 coalesce(cl.score, 0) AS my_vote,
352 AND cs.comment_id = ac.id) AS saved
355 CROSS JOIN all_comment ac
356 LEFT JOIN comment_like cl ON u.id = cl.user_id
357 AND ac.id = cl.comment_id
367 CREATE VIEW reply_view AS
371 c2.creator_id AS sender_id,
372 c.creator_id AS recipient_id
375 INNER JOIN comment c2 ON c.id = c2.parent_id
377 c2.creator_id != c.creator_id
378 -- Do union where post is null
382 c.creator_id AS sender_id,
383 p.creator_id AS recipient_id
389 AND c.parent_id IS NULL
390 AND c.creator_id != p.creator_id
394 closereply.recipient_id
399 closereply.id = cv.id;
402 CREATE VIEW user_mention_view AS
405 um.id AS user_mention_id,
417 c.banned_from_community,
431 um.comment_id = c.id;
434 DROP VIEW community_moderator_view;
436 DROP VIEW community_follower_view;
438 DROP VIEW community_user_ban_view;
442 CREATE VIEW community_moderator_view AS
451 cm.user_id = u.id) AS user_name,
458 cm.user_id = u.id), (
464 cm.community_id = c.id) AS community_name
466 community_moderator cm;
468 CREATE VIEW community_follower_view AS
477 cf.user_id = u.id) AS user_name,
484 cf.user_id = u.id), (
490 cf.community_id = c.id) AS community_name
492 community_follower cf;
494 CREATE VIEW community_user_ban_view AS
503 cm.user_id = u.id) AS user_name,
510 cm.user_id = u.id), (
516 cm.community_id = c.id) AS community_name
518 community_user_ban cm;
520 CREATE VIEW site_view AS
529 s.creator_id = u.id) AS creator_name,
536 s.creator_id = u.id) AS creator_avatar,
541 user_) AS number_of_users,
546 post) AS number_of_posts,
551 comment) AS number_of_comments,
556 community) AS number_of_communities