migrations/005_create_comments_tables.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

CREATE TYPE comment_state AS ENUM ('hidden', 'visible');

CREATE TABLE comments(
    id SERIAL NOT NULL PRIMARY KEY,
    comment TEXT NOT NULL,
    user_id INT NOT NULL REFERENCES users(id),
    post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    reply_id INT REFERENCES comments(id) ON DELETE CASCADE,
    state comment_state DEFAULT 'visible',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TRIGGER set_timestamp
BEFORE UPDATE ON comments
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();

---- create above / drop below ----

DROP TABLE comments;
DROP TYPE comment_state;