OXYGENHUB
# 155cab142b63 oxygenhub / crates / hub / migrations / 0005_issues.sql
4.3 KiB · SQL · last changed Milestone 0.4: issues and notifications 1 month ago source blame history raw
18e381ea Andy McCall ↶ 1 
-- Issues, labels, milestones, notifications, watching and search
2 
-- (hub spec §8, §10, §11).
3
4 
CREATE TABLE issues (
5 
    id           INTEGER PRIMARY KEY,
6 
    repo_id      INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
7 
    number       INTEGER NOT NULL,
8 
    title        TEXT NOT NULL,
9 
    body         TEXT NOT NULL DEFAULT '',
10 
    author_id    INTEGER REFERENCES accounts(id) ON DELETE SET NULL,
11 
    author       TEXT NOT NULL DEFAULT '',
12 
    state        TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
13 
    reason       TEXT NOT NULL DEFAULT '' CHECK (reason IN ('', 'completed', 'not_planned')),
14 
    locked       INTEGER NOT NULL DEFAULT 0,
15 
    pinned       INTEGER NOT NULL DEFAULT 0,
16 
    milestone_id INTEGER REFERENCES milestones(id) ON DELETE SET NULL,
17 
    created_ms   INTEGER NOT NULL,
18 
    updated_ms   INTEGER NOT NULL,
19 
    closed_ms    INTEGER,
20 
    UNIQUE (repo_id, number)
21 
);
22
23 
CREATE TABLE issue_comments (
24 
    id         INTEGER PRIMARY KEY,
25 
    issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
26 
    author_id  INTEGER REFERENCES accounts(id) ON DELETE SET NULL,
27 
    author     TEXT NOT NULL DEFAULT '',
28 
    body       TEXT NOT NULL,
29 
    created_ms INTEGER NOT NULL,
30 
    edited_ms  INTEGER
31 
);
32 
CREATE INDEX issue_comments_by_issue ON issue_comments (issue_id, created_ms);
33
34 
CREATE TABLE issue_events (
35 
    id       INTEGER PRIMARY KEY,
36 
    issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
37 
    at_ms    INTEGER NOT NULL,
38 
    actor    TEXT NOT NULL DEFAULT '',
39 
    event    TEXT NOT NULL,
40 
    detail   TEXT NOT NULL DEFAULT ''
41 
);
42
43 
CREATE TABLE labels (
44 
    id          INTEGER PRIMARY KEY,
45 
    repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
46 
    name        TEXT NOT NULL COLLATE NOCASE,
47 
    colour      TEXT NOT NULL DEFAULT '#0072ff',
48 
    description TEXT NOT NULL DEFAULT '',
49 
    UNIQUE (repo_id, name)
50 
);
51
52 
CREATE TABLE issue_labels (
53 
    issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
54 
    label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
55 
    PRIMARY KEY (issue_id, label_id)
56 
);
57
58 
CREATE TABLE issue_assignees (
59 
    issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
60 
    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
61 
    PRIMARY KEY (issue_id, account_id)
62 
);
63
64 
CREATE TABLE milestones (
65 
    id          INTEGER PRIMARY KEY,
66 
    repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
67 
    title       TEXT NOT NULL,
68 
    description TEXT NOT NULL DEFAULT '',
69 
    due_ms      INTEGER,
70 
    state       TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed')),
71 
    UNIQUE (repo_id, title)
72 
);
73
74 
-- Proposals get labels too (hub spec §7.1).
75 
CREATE TABLE proposal_labels (
76 
    proposal_id INTEGER NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
77 
    label_id    INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
78 
    PRIMARY KEY (proposal_id, label_id)
79 
);
80
81 
-- The inbox (hub spec §10).
82 
CREATE TABLE notifications (
83 
    id         INTEGER PRIMARY KEY,
84 
    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
85 
    at_ms      INTEGER NOT NULL,
86 
    kind       TEXT NOT NULL,
87 
    title      TEXT NOT NULL,
88 
    url        TEXT NOT NULL,
89 
    actor      TEXT NOT NULL DEFAULT '',
90 
    read       INTEGER NOT NULL DEFAULT 0
91 
);
92 
CREATE INDEX notifications_by_account ON notifications (account_id, read, at_ms DESC);
93
94 
-- Watching a repository subscribes to all its events; participating in a
95 
-- thread subscribes to it.
96 
CREATE TABLE watches (
97 
    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
98 
    repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
99 
    PRIMARY KEY (account_id, repo_id)
100 
);
101
102 
CREATE TABLE subscriptions (
103 
    account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
104 
    kind       TEXT NOT NULL CHECK (kind IN ('issue', 'proposal')),
105 
    target_id  INTEGER NOT NULL,
106 
    PRIMARY KEY (account_id, kind, target_id)
107 
);
108
109 
-- Per-account notification preferences.
110 
ALTER TABLE accounts ADD COLUMN email_notifications INTEGER NOT NULL DEFAULT 0;
111 
ALTER TABLE accounts ADD COLUMN unsubscribe_key TEXT NOT NULL DEFAULT '';
112
113 
-- Full-text search over issues, proposals and their comments (§11).
114 
CREATE VIRTUAL TABLE search USING fts5(kind UNINDEXED, repo_id UNINDEXED, target_id UNINDEXED, number UNINDEXED, title, body, tokenize = 'porter unicode61');