raPwSTZv

1CREATE TABLE IF NOT EXISTS users
2(
3  id       BIGSERIAL    NOT NULL
4    CONSTRAINT users_pk PRIMARY KEY,
5  nickname VARCHAR(32)  NOT NULL,
6  email    VARCHAR(255) NOT NULL,
7  fullname TEXT         NOT NULL,
8  about    TEXT
9);
10
11CREATE UNIQUE INDEX IF NOT EXISTS users_nickname_uindex
12  ON users (LOWER(nickname));
13
14CREATE UNIQUE INDEX IF NOT EXISTS users_email_uindex
15  ON users (LOWER(email));
16
17CREATE INDEX IF NOT EXISTS users_nickname_index
18  ON users (LOWER(nickname));
19
20CREATE INDEX IF NOT EXISTS users_nickname_email_index
21  ON users (LOWER(nickname), LOWER(email));
22
23CREATE TABLE IF NOT EXISTS forums
24(
25  id      BIGSERIAL    NOT NULL
26    CONSTRAINT forums_pk PRIMARY KEY,
27  slug    VARCHAR(128) NOT NULL,
28  title   VARCHAR(128) NOT NULL,
29  user_id BIGINT       NOT NULL,
30  posts   BIGINT       NOT NULL DEFAULT 0,
31  threads INT          NOT NULL DEFAULT 0
32);
33
34CREATE UNIQUE INDEX IF NOT EXISTS forums_slug_uindex
35  ON forums (LOWER(slug));
36
37CREATE INDEX IF NOT EXISTS forums_slug_index
38  ON forums (LOWER(slug));
39
40CREATE TABLE IF NOT EXISTS threads
41(
42  id       BIGSERIAL    NOT NULL
43    CONSTRAINT threads_pk PRIMARY KEY,
44  forum_id BIGINT       NOT NULL,
45  user_id  BIGINT       NOT NULL,
46  created  TIMESTAMPTZ  NOT NULL,
47  slug     VARCHAR(128) NOT NULL,
48  title    VARCHAR(128) NOT NULL,
49  message  TEXT         NOT NULL,
50  votes    INT          NOT NULL DEFAULT 0
51);
52
53CREATE INDEX IF NOT EXISTS threads_slug_index
54  ON threads (forum_id);
55
56CREATE TABLE IF NOT EXISTS posts
57(
58  id        BIGSERIAL   NOT NULL
59    CONSTRAINT posts_pk PRIMARY KEY,
60  thread_id BIGINT      NOT NULL,
61  user_id   BIGINT      NOT NULL,
62  created   TIMESTAMPTZ NOT NULL,
63  parent_id BIGINT      NOT NULL DEFAULT 0,
64  message   TEXT        NOT NULL,
65  is_edited BOOLEAN     NOT NULL DEFAULT FALSE
66);
67
68CREATE INDEX IF NOT EXISTS posts_slug_index
69  ON posts (thread_id);
70
71CREATE TABLE IF NOT EXISTS votes
72(
73  id        BIGSERIAL   NOT NULL
74    CONSTRAINT votes_pk PRIMARY KEY,
75  thread_id BIGINT      NOT NULL,
76  user_id   BIGINT      NOT NULL,
77  vote      BIGINT      NOT NULL
78);
79
80CREATE INDEX IF NOT EXISTS votes_thread_user_index
81  ON votes (thread_id, user_id);
82
83CREATE UNIQUE INDEX IF NOT EXISTS votes_thread_user_uindex
84  ON votes (thread_id, user_id);