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
  31     cr.*,
  32     c.post_id,
  33     c.content AS current_comment_text,
  34     p.community_id,
  35     -- report creator details
  36     f.actor_id AS creator_actor_id,
  37     f.name AS creator_name,
  38     f.preferred_username AS creator_preferred_username,
  39     f.avatar AS creator_avatar,
  40     f.local AS creator_local,
  41     -- comment creator details
  42     u.id AS comment_creator_id,
  43     u.actor_id AS comment_creator_actor_id,
  44     u.name AS comment_creator_name,
  45     u.preferred_username AS comment_creator_preferred_username,
  46     u.avatar AS comment_creator_avatar,
  47     u.local AS comment_creator_local,
  48     -- resolver details
  49     r.actor_id AS resolver_actor_id,
  50     r.name AS resolver_name,
  51     r.preferred_username AS resolver_preferred_username,
  52     r.avatar AS resolver_avatar,
  53     r.local AS resolver_local
  54 FROM
  55     comment_report cr
  56     LEFT JOIN comment c ON c.id = cr.comment_id
  57     LEFT JOIN post p ON p.id = c.post_id
  58     LEFT JOIN user_ u ON u.id = c.creator_id
  59     LEFT JOIN user_ f ON f.id = cr.creator_id
  60     LEFT JOIN user_ r ON r.id = cr.resolver_id;
  61
  62 CREATE OR REPLACE VIEW post_report_view AS
  63 SELECT
  64     pr.*,
  65     p.name AS current_post_name,
  66     p.url AS current_post_url,
  67     p.body AS current_post_body,
  68     p.community_id,
  69     -- report creator details
  70     f.actor_id AS creator_actor_id,
  71     f.name AS creator_name,
  72     f.preferred_username AS creator_preferred_username,
  73     f.avatar AS creator_avatar,
  74     f.local AS creator_local,
  75     -- post creator details
  76     u.id AS post_creator_id,
  77     u.actor_id AS post_creator_actor_id,
  78     u.name AS post_creator_name,
  79     u.preferred_username AS post_creator_preferred_username,
  80     u.avatar AS post_creator_avatar,
  81     u.local AS post_creator_local,
  82     -- resolver details
  83     r.actor_id AS resolver_actor_id,
  84     r.name AS resolver_name,
  85     r.preferred_username AS resolver_preferred_username,
  86     r.avatar AS resolver_avatar,
  87     r.local AS resolver_local
  88 FROM
  89     post_report pr
  90     LEFT JOIN post p ON p.id = pr.post_id
  91     LEFT JOIN user_ u ON u.id = p.creator_id
  92     LEFT JOIN user_ f ON f.id = pr.creator_id
  93     LEFT JOIN user_ r ON r.id = pr.resolver_id;
  94