1 CREATE TABLE comment_report (
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,
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
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,
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
29 CREATE OR REPLACE VIEW comment_report_view AS
33 c.content AS current_comment_text,
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,
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
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;
62 CREATE OR REPLACE VIEW post_report_view AS
65 p.name AS current_post_name,
66 p.url AS current_post_url,
67 p.body AS current_post_body,
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,
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
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;