g1t/services/work/migrations/0027_labels_milestones_bases.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Teams and CODEOWNERS, labels and milestones, dependency updates, the security suite, and a clearer top bar | 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); |