g1t/services/work/migrations/0027_labels_milestones_bases.sql
| 1 | -- Labels and milestones of a repository, and pull requests into branches |
| 2 | -- other than the default one. |
| 3 | |
| 4 | -- A repository's labels. Issues and pull requests carry them by name, in |
| 5 | -- their `labels` JSON arrays; names are lowercase. |
| 6 | CREATE TABLE labels ( |
| 7 | repo_id TEXT NOT NULL, |
| 8 | name TEXT NOT NULL, |
| 9 | -- Six hex digits, without '#'. |
| 10 | color TEXT NOT NULL, |
| 11 | description TEXT NOT NULL DEFAULT '', |
| 12 | created_at TEXT NOT NULL, |
| 13 | PRIMARY KEY (repo_id, name) |
| 14 | ); |
| 15 | |
| 16 | -- Pull requests carry labels too. |
| 17 | ALTER TABLE pulls ADD COLUMN labels TEXT NOT NULL DEFAULT '[]'; |
| 18 | |
| 19 | -- Every label already on an issue becomes one of its repository's labels, |
| 20 | -- with a color chosen as the site chose it before, so nothing in use |
| 21 | -- disappears from the labels page. |
| 22 | INSERT OR IGNORE INTO labels (repo_id, name, color, description, created_at) |
| 23 | SELECT DISTINCT issues.repo_id, json_each.value, |
| 24 | CASE json_each.value |
| 25 | WHEN 'bug' THEN 'd73a4a' |
| 26 | WHEN 'feature' THEN 'a2eeef' |
| 27 | WHEN 'docs' THEN '0075ca' |
| 28 | WHEN 'chore' THEN 'fbca04' |
| 29 | WHEN 'question' THEN 'd876e3' |
| 30 | ELSE 'bfdadc' |
| 31 | END, |
| 32 | '', strftime('%Y-%m-%dT%H:%M:%fZ', 'now') |
| 33 | FROM issues, json_each(issues.labels) |
| 34 | WHERE trim(json_each.value) != ''; |
| 35 | |
| 36 | -- Milestones: numbered from 1 in each repository, apart from issues. |
| 37 | CREATE TABLE milestones ( |
| 38 | repo_id TEXT NOT NULL, |
| 39 | number INTEGER NOT NULL, |
| 40 | title TEXT NOT NULL, |
| 41 | description TEXT NOT NULL DEFAULT '', |
| 42 | -- YYYY-MM-DD, or NULL. |
| 43 | due_on TEXT, |
| 44 | -- open or closed. |
| 45 | state TEXT NOT NULL DEFAULT 'open', |
| 46 | created_at TEXT NOT NULL, |
| 47 | updated_at TEXT NOT NULL, |
| 48 | closed_at TEXT, |
| 49 | PRIMARY KEY (repo_id, number) |
| 50 | ); |
| 51 | CREATE INDEX milestones_by_state ON milestones (repo_id, state, due_on); |
| 52 | |
| 53 | -- The milestone an issue or a pull request is in, by number. |
| 54 | ALTER TABLE issues ADD COLUMN milestone INTEGER; |
| 55 | ALTER TABLE pulls ADD COLUMN milestone INTEGER; |
| 56 | CREATE INDEX issues_by_milestone ON issues (repo_id, milestone); |
| 57 | CREATE INDEX pulls_by_milestone ON pulls (repo_id, milestone); |
| 58 | |
| 59 | -- The branch a pull request merges into. NULL is the repository's default |
| 60 | -- branch, whichever that is at the time, as every pull request until now. |
| 61 | ALTER TABLE pulls ADD COLUMN base_branch TEXT; |
| 62 | CREATE INDEX pulls_by_base ON pulls (repo_id, base_branch, status); |