| 1 | -- Settles inbox items that later events left with nothing to do, once, for |
| 2 | -- the items written before the inbox settled them as events arrive |
| 3 | -- (src/inbox.rs, "Settling"). Each is marked done, and read, as the event |
| 4 | -- would have marked it; it stays in Done with its history. |
| 5 | |
| 6 | -- A workflow that failed on a branch and passed there since. |
| 7 | UPDATE inbox_items |
| 8 | SET done_at = COALESCE(done_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), |
| 9 | read_at = COALESCE(read_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) |
| 10 | WHERE done_at IS NULL |
| 11 | AND thread LIKE '%/run/%' |
| 12 | AND EXISTS ( |
| 13 | SELECT 1 FROM events e |
| 14 | WHERE e.repo_id = inbox_items.repo_id |
| 15 | AND e.type = 'workflow.completed' |
| 16 | AND e.id > inbox_items.event_id |
| 17 | AND json_extract(e.data, '$.conclusion') = 'success' |
| 18 | AND json_extract(e.data, '$.pull') IS NULL |
| 19 | AND inbox_items.thread = e.repo_id || '/run/' |
| 20 | || COALESCE(NULLIF(json_extract(e.data, '$.path'), ''), json_extract(e.data, '$.workflow')) |
| 21 | || '@' || replace(json_extract(e.data, '$.ref'), 'refs/heads/', '') |
| 22 | ); |
| 23 | |
| 24 | -- A deployment that failed and has succeeded since. |
| 25 | UPDATE inbox_items |
| 26 | SET done_at = COALESCE(done_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), |
| 27 | read_at = COALESCE(read_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) |
| 28 | WHERE done_at IS NULL |
| 29 | AND event = 'deployment.failed' |
| 30 | AND EXISTS ( |
| 31 | SELECT 1 FROM events e |
| 32 | WHERE e.repo_id = inbox_items.repo_id |
| 33 | AND e.type = 'deployment.succeeded' |
| 34 | AND e.id > inbox_items.event_id |
| 35 | AND inbox_items.thread = e.repo_id || '/deploy/' || json_extract(e.data, '$.projectId') || '/' |
| 36 | || COALESCE(CAST(json_extract(e.data, '$.number') AS TEXT), 'production') |
| 37 | ); |
| 38 | |
| 39 | -- Anything about an issue or pull request that was merged or closed since |
| 40 | -- (or by the event the item last told of), and a pull request's previews. |
| 41 | UPDATE inbox_items |
| 42 | SET done_at = COALESCE(done_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), |
| 43 | read_at = COALESCE(read_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) |
| 44 | WHERE done_at IS NULL |
| 45 | AND number IS NOT NULL |
| 46 | AND EXISTS ( |
| 47 | SELECT 1 FROM events e |
| 48 | WHERE e.repo_id = inbox_items.repo_id |
| 49 | AND e.type IN ('pull.merged', 'pull.closed', 'issue.closed') |
| 50 | AND e.id >= inbox_items.event_id |
| 51 | AND json_extract(e.data, '$.number') = inbox_items.number |
| 52 | AND (inbox_items.thread = e.repo_id || '#' || inbox_items.number |
| 53 | OR (e.type <> 'issue.closed' AND inbox_items.thread GLOB e.repo_id || '/deploy/*/' || inbox_items.number)) |
| 54 | ); |
| 55 | |
| 56 | -- Auto-merge g1t turned off because the merge queue took the pull request. |
| 57 | UPDATE inbox_items |
| 58 | SET done_at = COALESCE(done_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')), |
| 59 | read_at = COALESCE(read_at, strftime('%Y-%m-%dT%H:%M:%fZ', 'now')) |
| 60 | WHERE done_at IS NULL |
| 61 | AND event = 'pull.auto_merge_disabled' |
| 62 | AND body = 'It joined the merge queue.'; |