lemmy/migrations/2019-03-03-163336_create_post/up.sql
Dessalines be1389420b
Adding SQL format checking via pg_format / pgFormatter (#3740)
* SQL format checking, 1.

* SQL format checking, 2.

* SQL format checking, 3.

* SQL format checking, 4.

* SQL format checking, 5.

* Running pg_format

* Getting rid of comment.

* Upping pg_format version.

* Using git ls-files for sql format check.

* Fixing sql lints.

* Addressing PR comments.
2023-08-02 12:44:51 -04:00

39 lines
1.4 KiB
SQL

CREATE TABLE post (
id serial PRIMARY KEY,
name varchar(100) NOT NULL,
url text, -- These are both optional, a post can just have a title
body text,
creator_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
community_id int REFERENCES community ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
removed boolean DEFAULT FALSE NOT NULL,
locked boolean DEFAULT FALSE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
updated timestamp
);
CREATE TABLE post_like (
id serial PRIMARY KEY,
post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
score smallint NOT NULL, -- -1, or 1 for dislike, like, no row for no opinion
published timestamp NOT NULL DEFAULT now(),
UNIQUE (post_id, user_id)
);
CREATE TABLE post_saved (
id serial PRIMARY KEY,
post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (post_id, user_id)
);
CREATE TABLE post_read (
id serial PRIMARY KEY,
post_id int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
published timestamp NOT NULL DEFAULT now(),
UNIQUE (post_id, user_id)
);