SHARE
TWEET

Untitled

a guest Jun 26th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE 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.  
  11. CREATE UNIQUE INDEX IF NOT EXISTS users_nickname_uindex
  12.   ON users (LOWER(nickname));
  13.  
  14. CREATE UNIQUE INDEX IF NOT EXISTS users_email_uindex
  15.   ON users (LOWER(email));
  16.  
  17. CREATE INDEX IF NOT EXISTS users_nickname_index
  18.   ON users (LOWER(nickname));
  19.  
  20. CREATE INDEX IF NOT EXISTS users_nickname_email_index
  21.   ON users (LOWER(nickname), LOWER(email));
  22.  
  23. CREATE 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.  
  34. CREATE UNIQUE INDEX IF NOT EXISTS forums_slug_uindex
  35.   ON forums (LOWER(slug));
  36.  
  37. CREATE INDEX IF NOT EXISTS forums_slug_index
  38.   ON forums (LOWER(slug));
  39.  
  40. CREATE 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.  
  53. CREATE INDEX IF NOT EXISTS threads_slug_index
  54.   ON threads (forum_id);
  55.  
  56. CREATE 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.  
  68. CREATE INDEX IF NOT EXISTS posts_slug_index
  69.   ON posts (thread_id);
  70.  
  71. CREATE 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.  
  80. CREATE INDEX IF NOT EXISTS votes_thread_user_index
  81.   ON votes (thread_id, user_id);
  82.  
  83. CREATE UNIQUE INDEX IF NOT EXISTS votes_thread_user_uindex
  84.   ON votes (thread_id, user_id);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top