ikurotime / gitgud

 1CREATE TABLE IF NOT EXISTS users (
 2  id            INTEGER PRIMARY KEY AUTOINCREMENT,
 3  username      TEXT NOT NULL UNIQUE,
 4  email         TEXT NOT NULL,
 5  password_hash TEXT NOT NULL,
 6  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
 7);
 8
 9CREATE TABLE IF NOT EXISTS repositories (
10  id             INTEGER PRIMARY KEY AUTOINCREMENT,
11  owner_id       INTEGER NOT NULL REFERENCES users(id),
12  name           TEXT NOT NULL,
13  description    TEXT NOT NULL DEFAULT '',
14  is_private     INTEGER NOT NULL DEFAULT 0,
15  default_branch TEXT NOT NULL DEFAULT 'main',
16  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
17  UNIQUE(owner_id, name)
18);
19
20CREATE TABLE IF NOT EXISTS issues (
21  id         INTEGER PRIMARY KEY AUTOINCREMENT,
22  repo_id    INTEGER NOT NULL REFERENCES repositories(id),
23  number     INTEGER NOT NULL,
24  author_id  INTEGER NOT NULL REFERENCES users(id),
25  title      TEXT NOT NULL,
26  body       TEXT NOT NULL DEFAULT '',
27  state      TEXT NOT NULL DEFAULT 'open',
28  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
29  UNIQUE(repo_id, number)
30);
31
32CREATE TABLE IF NOT EXISTS issue_comments (
33  id         INTEGER PRIMARY KEY AUTOINCREMENT,
34  issue_id   INTEGER NOT NULL REFERENCES issues(id),
35  author_id  INTEGER NOT NULL REFERENCES users(id),
36  body       TEXT NOT NULL,
37  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
38);
39
40CREATE TABLE IF NOT EXISTS pull_requests (
41  id          INTEGER PRIMARY KEY AUTOINCREMENT,
42  repo_id     INTEGER NOT NULL REFERENCES repositories(id),
43  number      INTEGER NOT NULL,
44  author_id   INTEGER NOT NULL REFERENCES users(id),
45  title       TEXT NOT NULL,
46  body        TEXT NOT NULL DEFAULT '',
47  base_branch TEXT NOT NULL,
48  head_branch TEXT NOT NULL,
49  state       TEXT NOT NULL DEFAULT 'open',
50  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
51  UNIQUE(repo_id, number)
52);
53
54CREATE TABLE IF NOT EXISTS pr_comments (
55  id         INTEGER PRIMARY KEY AUTOINCREMENT,
56  pr_id      INTEGER NOT NULL REFERENCES pull_requests(id),
57  author_id  INTEGER NOT NULL REFERENCES users(id),
58  body       TEXT NOT NULL,
59  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
60);