002_features.sql · rin/polar

002_features.sql · 29 строк · 1.2 KB

1	ALTER TABLE users ADD COLUMN full_name TEXT NOT NULL DEFAULT '';
2	ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT '';
3	ALTER TABLE users ADD COLUMN location TEXT NOT NULL DEFAULT '';
4	ALTER TABLE users ADD COLUMN website TEXT NOT NULL DEFAULT '';
5
6	CREATE TABLE IF NOT EXISTS issues (
7	id INTEGER PRIMARY KEY AUTOINCREMENT,
8	repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
9	number INTEGER NOT NULL,
10	author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
11	title TEXT NOT NULL,
12	body TEXT NOT NULL DEFAULT '',
13	is_closed INTEGER NOT NULL DEFAULT 0,
14	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
15	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
16	UNIQUE(repo_id, number)
17	);
18
19	CREATE INDEX IF NOT EXISTS idx_issues_repo_id ON issues(repo_id, is_closed, number DESC);
20
21	CREATE TABLE IF NOT EXISTS issue_comments (
22	id INTEGER PRIMARY KEY AUTOINCREMENT,
23	issue_id INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
24	author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
25	body TEXT NOT NULL,
26	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
27	);
28
29	CREATE INDEX IF NOT EXISTS idx_issue_comments_issue_id ON issue_comments(issue_id, created_at ASC);
30