forge
ExploreLog in

Tahinli/forge

public
0 watching
★ 0
3.4 KiB
1-- Issues, comments, labels and milestones (milestone 3). Applied by store/migrate.rs.
2-- Numbers come from repos.next_issue_number, allocated in the same transaction as the insert.
3
4ALTER TABLE repos ADD COLUMN next_issue_number INTEGER NOT NULL DEFAULT 1;
5
6CREATE TABLE milestones (
7 id TEXT PRIMARY KEY, repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
8 title TEXT NOT NULL, title_folded TEXT NOT NULL, description TEXT NOT NULL DEFAULT '',
9 due_on TEXT, -- 'YYYY-MM-DD' or NULL
10 state TEXT NOT NULL DEFAULT 'open',-- 'open' | 'closed'
11 created_at TEXT NOT NULL, closed_at TEXT,
12 UNIQUE (repo_id, title_folded));
13
14CREATE TABLE issues (
15 id TEXT PRIMARY KEY, repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
16 number INTEGER NOT NULL,
17 kind TEXT NOT NULL DEFAULT 'issue', -- 'issue' now; 'pull' in M4 (shared numbering)
18 title TEXT NOT NULL, body TEXT NOT NULL DEFAULT '',
19 state TEXT NOT NULL DEFAULT 'open', -- 'open' | 'closed'
20 state_reason TEXT, -- NULL | 'completed' | 'not_planned'
21 author_id TEXT NOT NULL REFERENCES users(id),
22 milestone_id TEXT REFERENCES milestones(id) ON DELETE SET NULL,
23 comment_count INTEGER NOT NULL DEFAULT 0,
24 created_at TEXT NOT NULL, updated_at TEXT NOT NULL, closed_at TEXT,
25 UNIQUE (repo_id, number));
26CREATE INDEX issues_repo_state ON issues(repo_id, kind, state, number);
27
28CREATE TABLE comments (
29 id TEXT PRIMARY KEY, issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
30 author_id TEXT NOT NULL REFERENCES users(id),
31 body TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL,
32 edited INTEGER NOT NULL DEFAULT 0);
33CREATE INDEX comments_issue ON comments(issue_id, created_at);
34
35-- Edit history: the previous version is saved on every edit.
36CREATE TABLE edit_history (
37 id TEXT PRIMARY KEY, target_kind TEXT NOT NULL, -- 'issue' | 'comment'
38 target_id TEXT NOT NULL, old_title TEXT, old_body TEXT NOT NULL,
39 editor_id TEXT NOT NULL REFERENCES users(id), edited_at TEXT NOT NULL);
40CREATE INDEX edit_history_target ON edit_history(target_kind, target_id, edited_at);
41
42-- Timeline events (everything except comments).
43CREATE TABLE issue_events (
44 id TEXT PRIMARY KEY, issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
45 actor_id TEXT REFERENCES users(id), -- NULL = system
46 kind TEXT NOT NULL, -- closed reopened labeled unlabeled assigned unassigned milestoned demilestoned renamed referenced
47 data TEXT NOT NULL DEFAULT '{}', -- JSON, e.g. {"label":"bug"} {"from":"a","to":"b"} {"commit":"<oid>"} {"reason":"completed"}
48 created_at TEXT NOT NULL);
49CREATE INDEX issue_events_issue ON issue_events(issue_id, created_at);
50
51CREATE TABLE labels (
52 id TEXT PRIMARY KEY, repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
53 name TEXT NOT NULL, name_folded TEXT NOT NULL,
54 color TEXT NOT NULL, -- '#rrggbb' lowercase
55 description TEXT NOT NULL DEFAULT '',
56 UNIQUE (repo_id, name_folded));
57
58CREATE TABLE issue_labels (issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
59 label_id TEXT NOT NULL REFERENCES labels(id) ON DELETE CASCADE, PRIMARY KEY (issue_id, label_id));
60CREATE TABLE issue_assignees (issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
61 user_id TEXT NOT NULL REFERENCES users(id), PRIMARY KEY (issue_id, user_id));