migrations/2020-10-13-212240_create_report_tables/up.sql

   1 create table comment_report (
   2   id            serial    primary key,
   3   creator_id    int       references user_ on update cascade on delete cascade not null,   -- user reporting comment
   4   comment_id    int       references comment on update cascade on delete cascade not null, -- comment being reported
   5   original_comment_text  text      not null,
   6   reason        text      not null,
   7   resolved      bool      not null default false,
   8   resolver_id   int       references user_ on update cascade on delete cascade,   -- user resolving report
   9   published     timestamp not null default now(),
  10   updated       timestamp null,
  11   unique(comment_id, creator_id) -- users should only be able to report a comment once
  12 );
  13
  14 create table post_report (
  15   id            serial    primary key,
  16   creator_id    int       references user_ on update cascade on delete cascade not null, -- user reporting post
  17   post_id       int       references post on update cascade on delete cascade not null,  -- post being reported
  18   original_post_name      varchar(100) not null,
  19   original_post_url       text,
  20   original_post_body      text,
  21   reason        text      not null,
  22   resolved      bool      not null default false,
  23   resolver_id   int       references user_ on update cascade on delete cascade,   -- user resolving report
  24   published     timestamp not null default now(),
  25   updated       timestamp null,
  26   unique(post_id, creator_id) -- users should only be able to report a post once
  27 );
  28
  29 create or replace view comment_report_view as
  30 select cr.*,
  31 c.post_id,
  32 c.content as current_comment_text,
  33 p.community_id,
  34 -- report creator details
  35 f.actor_id as creator_actor_id,
  36 f.name as creator_name,
  37 f.preferred_username as creator_preferred_username,
  38 f.avatar as creator_avatar,
  39 f.local as creator_local,
  40 -- comment creator details
  41 u.id as comment_creator_id,
  42 u.actor_id as comment_creator_actor_id,
  43 u.name as comment_creator_name,
  44 u.preferred_username as comment_creator_preferred_username,
  45 u.avatar as comment_creator_avatar,
  46 u.local as comment_creator_local,
  47 -- resolver details
  48 r.actor_id as resolver_actor_id,
  49 r.name as resolver_name,
  50 r.preferred_username as resolver_preferred_username,
  51 r.avatar as resolver_avatar,
  52 r.local as resolver_local
  53 from comment_report cr
  54 left join comment c on c.id = cr.comment_id
  55 left join post p on p.id = c.post_id
  56 left join user_ u on u.id = c.creator_id
  57 left join user_ f on f.id = cr.creator_id
  58 left join user_ r on r.id = cr.resolver_id;
  59
  60 create or replace view post_report_view as
  61 select pr.*,
  62 p.name as current_post_name,
  63 p.url as current_post_url,
  64 p.body as current_post_body,
  65 p.community_id,
  66 -- report creator details
  67 f.actor_id as creator_actor_id,
  68 f.name as creator_name,
  69 f.preferred_username as creator_preferred_username,
  70 f.avatar as creator_avatar,
  71 f.local as creator_local,
  72 -- post creator details
  73 u.id as post_creator_id,
  74 u.actor_id as post_creator_actor_id,
  75 u.name as post_creator_name,
  76 u.preferred_username as post_creator_preferred_username,
  77 u.avatar as post_creator_avatar,
  78 u.local as post_creator_local,
  79 -- resolver details
  80 r.actor_id as resolver_actor_id,
  81 r.name as resolver_name,
  82 r.preferred_username as resolver_preferred_username,
  83 r.avatar as resolver_avatar,
  84 r.local as resolver_local
  85 from post_report pr
  86 left join post p on p.id = pr.post_id
  87 left join user_ u on u.id = p.creator_id
  88 left join user_ f on f.id = pr.creator_id
  89 left join user_ r on r.id = pr.resolver_id;