3 name varchar(100) NOT NULL,
4 url text, -- These are both optional, a post can just have a title
6 creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
7 community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
8 removed boolean DEFAULT FALSE NOT NULL,
9 locked boolean DEFAULT FALSE NOT NULL,
10 published timestamp NOT NULL DEFAULT now(),
14 CREATE TABLE post_like (
15 id serial PRIMARY KEY,
16 post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
17 user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
18 score smallint NOT NULL, -- -1, or 1 for dislike, like, no row for no opinion
19 published timestamp NOT NULL DEFAULT now(),
20 UNIQUE (post_id, user_id)
23 CREATE TABLE post_saved (
24 id serial PRIMARY KEY,
25 post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
26 user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
27 published timestamp NOT NULL DEFAULT now(),
28 UNIQUE (post_id, user_id)
31 CREATE TABLE post_read (
32 id serial PRIMARY KEY,
33 post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
34 user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
35 published timestamp NOT NULL DEFAULT now(),
36 UNIQUE (post_id, user_id)