g1t/services/work/migrations/0022_required_checks.sql
| 1 | -- Checks are workflows, and the default branch's protection chooses which |
| 2 | -- of them must pass: for every pull request, a person's or an agent's. |
| 3 | -- Commands written on an issue are no longer run. |
| 4 | |
| 5 | -- The checks that must pass before a pull request merges, by name (a |
| 6 | -- workflow's name, such as "CI", or another status's context): JSON array. |
| 7 | ALTER TABLE repo_settings ADD COLUMN required_checks TEXT NOT NULL DEFAULT '[]'; |
| 8 | |
| 9 | -- Until now every workflow reported on a pull request's head held its merge. |
| 10 | -- Repositories whose pull requests had workflows keep exactly that, made |
| 11 | -- visible: the workflows reported for pull_request events in the last 30 |
| 12 | -- days become the default branch's required checks. Only where none are |
| 13 | -- set yet, so running this again changes nothing. |
| 14 | INSERT INTO repo_settings (repo_id, updated_by, updated_at, required_checks) |
| 15 | SELECT s.repo_id, 'g1t', strftime('%Y-%m-%dT%H:%M:%fZ', 'now'), |
| 16 | json_group_array(DISTINCT substr(s.context, 1, length(s.context) - length(' / pull_request'))) |
| 17 | FROM commit_statuses s |
| 18 | JOIN pulls p ON p.repo_id = s.repo_id AND p.head_commit = s.sha |
| 19 | WHERE s.context LIKE '% / pull_request' |
| 20 | AND s.updated_at >= strftime('%Y-%m-%dT%H:%M:%fZ', 'now', '-30 days') |
| 21 | GROUP BY s.repo_id |
| 22 | ON CONFLICT (repo_id) DO UPDATE SET required_checks = excluded.required_checks |
| 23 | WHERE repo_settings.required_checks = '[]'; |
| 24 | |
| 25 | -- An issue's commands become words its agent and reviewers read: added to |
| 26 | -- its body under "Definition of done", once. The column is kept, as it |
| 27 | -- was, for the record; nothing reads it any more. |
| 28 | UPDATE issues |
| 29 | SET body = CASE WHEN trim(body) = '' THEN '' ELSE rtrim(body, ' ' || char(10) || char(13)) || char(10) || char(10) END |
| 30 | || '## Definition of done' || char(10) || char(10) |
| 31 | || (SELECT group_concat('- `' || replace(value, '`', '''') || '` passes.', char(10)) |
| 32 | FROM json_each(issues.checks) WHERE trim(value) != '') |
| 33 | WHERE checks != '[]' |
| 34 | AND instr(body, '## Definition of done') = 0 |
| 35 | AND EXISTS (SELECT 1 FROM json_each(issues.checks) WHERE trim(value) != ''); |
| 36 | |
| 37 | -- What earlier runs of those commands said about open pull requests no |
| 38 | -- longer decides anything: their workflows do. |
| 39 | UPDATE pulls SET check_status = NULL, check_run_id = NULL |
| 40 | WHERE status IN ('draft', 'open') AND check_status IS NOT NULL; |