| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- ─── check_suites ──────────────────────────────────────────────────── |
| 4 | |
| 5 | -- name: GetOrCreateCheckSuite :one |
| 6 | -- Idempotent suite-by-(repo, head_sha, app_slug) lookup. Used by every |
| 7 | -- check-run create so consumers don't need to manage suite ids. ON |
| 8 | -- CONFLICT … DO UPDATE returns the existing row when the unique |
| 9 | -- (repo_id, head_sha, app_slug) already exists; otherwise returns the |
| 10 | -- freshly inserted one. |
| 11 | INSERT INTO check_suites (repo_id, head_sha, app_slug) |
| 12 | VALUES ($1, $2, $3) |
| 13 | ON CONFLICT (repo_id, head_sha, app_slug) DO UPDATE |
| 14 | SET app_slug = EXCLUDED.app_slug |
| 15 | RETURNING *; |
| 16 | |
| 17 | -- name: GetCheckSuite :one |
| 18 | SELECT * FROM check_suites WHERE id = $1; |
| 19 | |
| 20 | -- name: GetCheckSuiteForRepo :one |
| 21 | SELECT |
| 22 | cs.*, |
| 23 | COALESCE(pr_meta.number, 0)::bigint AS pull_number, |
| 24 | COALESCE(pr_meta.title, '')::text AS pull_title, |
| 25 | COALESCE(pr_meta.author_username, '')::text AS pull_author_username, |
| 26 | COALESCE(pr_meta.head_ref, '')::text AS head_ref, |
| 27 | COALESCE(pr_meta.base_ref, '')::text AS base_ref |
| 28 | FROM check_suites cs |
| 29 | LEFT JOIN LATERAL ( |
| 30 | SELECT |
| 31 | i.number, |
| 32 | i.title, |
| 33 | COALESCE(u.username, '') AS author_username, |
| 34 | pr.head_ref, |
| 35 | pr.base_ref |
| 36 | FROM pull_requests pr |
| 37 | JOIN issues i ON i.id = pr.issue_id AND i.kind = 'pr' |
| 38 | LEFT JOIN users u ON u.id = i.author_user_id |
| 39 | WHERE i.repo_id = cs.repo_id |
| 40 | AND pr.head_oid = cs.head_sha |
| 41 | ORDER BY i.updated_at DESC, i.number DESC |
| 42 | LIMIT 1 |
| 43 | ) pr_meta ON true |
| 44 | WHERE cs.repo_id = $1 AND cs.id = $2; |
| 45 | |
| 46 | -- name: ListCheckSuitesForRepo :many |
| 47 | SELECT |
| 48 | cs.*, |
| 49 | COALESCE(pr_meta.number, 0)::bigint AS pull_number, |
| 50 | COALESCE(pr_meta.title, '')::text AS pull_title, |
| 51 | COALESCE(pr_meta.author_username, '')::text AS pull_author_username, |
| 52 | COALESCE(pr_meta.head_ref, '')::text AS head_ref, |
| 53 | COALESCE(pr_meta.base_ref, '')::text AS base_ref |
| 54 | FROM check_suites cs |
| 55 | LEFT JOIN LATERAL ( |
| 56 | SELECT |
| 57 | i.number, |
| 58 | i.title, |
| 59 | COALESCE(u.username, '') AS author_username, |
| 60 | pr.head_ref, |
| 61 | pr.base_ref |
| 62 | FROM pull_requests pr |
| 63 | JOIN issues i ON i.id = pr.issue_id AND i.kind = 'pr' |
| 64 | LEFT JOIN users u ON u.id = i.author_user_id |
| 65 | WHERE i.repo_id = cs.repo_id |
| 66 | AND pr.head_oid = cs.head_sha |
| 67 | ORDER BY i.updated_at DESC, i.number DESC |
| 68 | LIMIT 1 |
| 69 | ) pr_meta ON true |
| 70 | WHERE cs.repo_id = $1 |
| 71 | ORDER BY cs.updated_at DESC, cs.id DESC |
| 72 | LIMIT $2 OFFSET $3; |
| 73 | |
| 74 | -- name: ListCheckSuitesForCommit :many |
| 75 | SELECT * FROM check_suites |
| 76 | WHERE repo_id = $1 AND head_sha = $2 |
| 77 | ORDER BY app_slug; |
| 78 | |
| 79 | -- name: UpdateCheckSuiteRollup :exec |
| 80 | -- Persists the rollup result computed in Go (suite_rollup.go). |
| 81 | UPDATE check_suites |
| 82 | SET status = $2, |
| 83 | conclusion = sqlc.narg(conclusion)::check_conclusion |
| 84 | WHERE id = $1; |
| 85 | |
| 86 | -- name: ListCheckSuiteIDsForHead :many |
| 87 | -- Used by the stale-on-push hook to flip queued/in_progress suites on |
| 88 | -- the previous head to conclusion='stale'. |
| 89 | SELECT id FROM check_suites |
| 90 | WHERE repo_id = $1 AND head_sha = $2 AND status <> 'completed'; |
| 91 | |
| 92 | -- name: MarkCheckSuiteStale :exec |
| 93 | UPDATE check_suites |
| 94 | SET status = 'completed', conclusion = 'stale' |
| 95 | WHERE id = $1; |
| 96 | |
| 97 | |
| 98 | -- ─── check_runs ────────────────────────────────────────────────────── |
| 99 | |
| 100 | -- name: GetCheckRunByExternalID :one |
| 101 | -- External-system create dedupe: lookup by (repo, head_sha, name, |
| 102 | -- external_id). NULL external_id never matches via this query. |
| 103 | SELECT * FROM check_runs |
| 104 | WHERE repo_id = $1 AND head_sha = $2 AND name = $3 AND external_id = $4; |
| 105 | |
| 106 | -- name: CreateCheckRun :one |
| 107 | INSERT INTO check_runs ( |
| 108 | suite_id, repo_id, head_sha, name, |
| 109 | status, conclusion, |
| 110 | started_at, completed_at, |
| 111 | details_url, output, external_id |
| 112 | ) VALUES ( |
| 113 | $1, $2, $3, $4, |
| 114 | $5, sqlc.narg(conclusion)::check_conclusion, |
| 115 | sqlc.narg(started_at)::timestamptz, sqlc.narg(completed_at)::timestamptz, |
| 116 | $6, $7, sqlc.narg(external_id)::text |
| 117 | ) |
| 118 | RETURNING *; |
| 119 | |
| 120 | -- name: GetCheckRun :one |
| 121 | SELECT * FROM check_runs WHERE id = $1; |
| 122 | |
| 123 | -- name: UpdateCheckRun :exec |
| 124 | UPDATE check_runs |
| 125 | SET status = $2, |
| 126 | conclusion = sqlc.narg(conclusion)::check_conclusion, |
| 127 | started_at = sqlc.narg(started_at)::timestamptz, |
| 128 | completed_at = sqlc.narg(completed_at)::timestamptz, |
| 129 | details_url = $3, |
| 130 | output = $4 |
| 131 | WHERE id = $1; |
| 132 | |
| 133 | -- name: ListCheckRunsForCommit :many |
| 134 | SELECT * FROM check_runs |
| 135 | WHERE repo_id = $1 AND head_sha = $2 |
| 136 | ORDER BY name; |
| 137 | |
| 138 | -- name: ListCheckRunsBySuite :many |
| 139 | SELECT * FROM check_runs |
| 140 | WHERE suite_id = $1 |
| 141 | ORDER BY name; |
| 142 | |
| 143 | -- name: GetLatestCheckRunByName :one |
| 144 | -- Required-check evaluator: most recent run with the given name on the |
| 145 | -- specified head_sha. |
| 146 | SELECT * FROM check_runs |
| 147 | WHERE repo_id = $1 AND head_sha = $2 AND name = $3 |
| 148 | ORDER BY created_at DESC |
| 149 | LIMIT 1; |
| 150 |