Se8G8pB8

1CREATE EXTENSION IF NOT EXISTS uuid-ossp;
2
3CREATE TABLE IF NOT EXISTS users (
4  user_id       uuid PRIMARY KEY DEFAULT uuid_generate_v1mc(),
5  email         text UNIQUE,
6  password_hash text,
7  username      text,
8  avatar_url    text
9);
10
11CREATE TABLE IF NOT EXISTS gists (
12  gist_id       uuid      PRIMARY KEY DEFAULT uuid_generate_v1mc(),
13  user_id       text      REFERENCES users (user_id)
14  -- head          text      REFERENCES revisions (revision_id),
15  name          text,
16  description   text,
17  private       boolean   DEFAULT FALSE
18);
19
20CREATE TABLE IF NOT EXISTS revisions (
21  id            text PRIMARY KEY DEFAULT uuid_generate_v1mc(),
22  parent_id     text REFERENCES revisions (id),
23  gist_id       text REFERENCES gists (gist_id),
24  -- I don't think I need parent_id here since we have the created_at
25  -- and can order by the timestamp to know how to display
26  -- the revision's files in the correct order
27  -- parent_id     text REFERENCES revisions (revision_id),
28  created_at    timestamp NOT NULL
29);
30CREATE UNIQUE INDEX first_revision ON revisions USING btree (id) WHERE (parent_id IS NULL);
31CREATE UNIQUE INDEX subsequent_revision ON revisions USING btree (gist_id, parent_id);
32
33
34CREATE TABLE IF NOT EXISTS files (
35  file_id       text PRIMARY KEY DEFAULT uuid_generate_v1mc(),
36  gist_id       text REFERENCES gists (gist_id),
37  -- Do we still need parent_id here if we know which revision this
38  -- file is a part of, and then can diff the content between this
39  -- file and the file from the previously created revision ?
40  -- parent_id     text,
41  filename      text NOT NULL,
42  content       text NOT NULL,
43  diff          text
44);
45
46CREATE TABLE IF NOT EXISTS revision_files (
47  revision_id   text REFERENCES revisions (revision_id),
48  file_id       text REFERENCES files (file_id),
49  PRIMARY KEY (revision_id, file_id),
50);
51
52CREATE TABLE IF NOT EXISTS comments (
53  comment_id    text PRIMARY KEY DEFAULT uuid_generate_v1mc(),
54  gist_id       text REFERENCES gists (gist_id),
55  user_id       text REFERENCES users (user_id),
56  content       text NOT NULL,
57  created_at    timestamp NOT NULL,
58  updated_at    timestamp NOT NULL
59);
60
61CREATE TABLE IF NOT EXISTS subscriptions (
62  gist_id       text REFERENCES gists (uuid),
63  user_id       text REFERENCES users (uuid),
64  PRIMARY KEY (gist_id, user_id)
65);
66
67CREATE TABLE IF NOT EXISTS stars (
68  gist_id       text REFERENCES gists (uuid),
69  user_id       text REFERENCES users (uuid),
70  PRIMARY KEY (gist_id, user_id)
71);