| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: InsertWorkflowJob :one |
| 4 | INSERT INTO workflow_jobs ( |
| 5 | run_id, job_index, job_key, job_name, |
| 6 | runs_on, needs_jobs, if_expr, timeout_minutes, |
| 7 | permissions, job_env |
| 8 | ) VALUES ( |
| 9 | $1, $2, $3, $4, $5, $6, $7, $8, $9, $10 |
| 10 | ) |
| 11 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 12 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 13 | job_env, status, conclusion, cancel_requested, |
| 14 | started_at, completed_at, version, created_at, updated_at; |
| 15 | |
| 16 | -- name: GetWorkflowJobByID :one |
| 17 | SELECT id, run_id, job_index, job_key, job_name, runs_on, |
| 18 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 19 | job_env, status, conclusion, cancel_requested, |
| 20 | started_at, completed_at, version, created_at, updated_at |
| 21 | FROM workflow_jobs |
| 22 | WHERE id = $1; |
| 23 | |
| 24 | -- name: UpdateWorkflowJobStatus :one |
| 25 | UPDATE workflow_jobs |
| 26 | SET status = $2, |
| 27 | conclusion = sqlc.narg(conclusion)::check_conclusion, |
| 28 | started_at = sqlc.narg(started_at)::timestamptz, |
| 29 | completed_at = sqlc.narg(completed_at)::timestamptz, |
| 30 | version = version + 1, |
| 31 | updated_at = now() |
| 32 | WHERE id = $1 |
| 33 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 34 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 35 | job_env, status, conclusion, cancel_requested, |
| 36 | started_at, completed_at, version, created_at, updated_at; |
| 37 | |
| 38 | -- name: RequestWorkflowJobCancel :one |
| 39 | UPDATE workflow_jobs |
| 40 | SET cancel_requested = true, |
| 41 | status = CASE |
| 42 | WHEN status = 'queued' THEN 'cancelled'::workflow_job_status |
| 43 | ELSE status |
| 44 | END, |
| 45 | conclusion = CASE |
| 46 | WHEN status = 'queued' THEN 'cancelled'::check_conclusion |
| 47 | ELSE conclusion |
| 48 | END, |
| 49 | started_at = CASE |
| 50 | WHEN status = 'queued' THEN COALESCE(started_at, now()) |
| 51 | ELSE started_at |
| 52 | END, |
| 53 | completed_at = CASE |
| 54 | WHEN status = 'queued' THEN COALESCE(completed_at, now()) |
| 55 | ELSE completed_at |
| 56 | END, |
| 57 | version = version + 1, |
| 58 | updated_at = now() |
| 59 | WHERE id = $1 |
| 60 | AND status IN ('queued', 'running') |
| 61 | AND (status = 'queued' OR cancel_requested = false) |
| 62 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 63 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 64 | job_env, status, conclusion, cancel_requested, |
| 65 | started_at, completed_at, version, created_at, updated_at; |
| 66 | |
| 67 | -- name: RequestWorkflowRunCancel :many |
| 68 | UPDATE workflow_jobs |
| 69 | SET cancel_requested = true, |
| 70 | status = CASE |
| 71 | WHEN status = 'queued' THEN 'cancelled'::workflow_job_status |
| 72 | ELSE status |
| 73 | END, |
| 74 | conclusion = CASE |
| 75 | WHEN status = 'queued' THEN 'cancelled'::check_conclusion |
| 76 | ELSE conclusion |
| 77 | END, |
| 78 | started_at = CASE |
| 79 | WHEN status = 'queued' THEN COALESCE(started_at, now()) |
| 80 | ELSE started_at |
| 81 | END, |
| 82 | completed_at = CASE |
| 83 | WHEN status = 'queued' THEN COALESCE(completed_at, now()) |
| 84 | ELSE completed_at |
| 85 | END, |
| 86 | version = version + 1, |
| 87 | updated_at = now() |
| 88 | WHERE run_id = $1 |
| 89 | AND status IN ('queued', 'running') |
| 90 | AND (status = 'queued' OR cancel_requested = false) |
| 91 | RETURNING id, run_id, job_index, job_key, job_name, runs_on, |
| 92 | runner_id, needs_jobs, if_expr, timeout_minutes, permissions, |
| 93 | job_env, status, conclusion, cancel_requested, |
| 94 | started_at, completed_at, version, created_at, updated_at; |
| 95 | |
| 96 | -- name: CountRunningJobsForRunner :one |
| 97 | SELECT COUNT(*)::integer |
| 98 | FROM workflow_jobs |
| 99 | WHERE runner_id = sqlc.arg(runner_id)::bigint AND status = 'running'; |
| 100 | |
| 101 | -- name: ClaimQueuedWorkflowJob :one |
| 102 | WITH candidate AS ( |
| 103 | SELECT j.id |
| 104 | FROM workflow_jobs j |
| 105 | JOIN workflow_runs r ON r.id = j.run_id |
| 106 | WHERE j.status = 'queued' |
| 107 | AND r.status IN ('queued', 'running') |
| 108 | AND j.cancel_requested = false |
| 109 | AND j.runner_id IS NULL |
| 110 | AND (j.runs_on = '' OR j.runs_on = ANY(sqlc.arg(labels)::text[])) |
| 111 | AND NOT EXISTS ( |
| 112 | SELECT 1 |
| 113 | FROM workflow_jobs dep |
| 114 | WHERE dep.run_id = j.run_id |
| 115 | AND dep.job_key = ANY(j.needs_jobs) |
| 116 | AND (dep.status <> 'completed' OR dep.conclusion <> 'success') |
| 117 | ) |
| 118 | AND NOT EXISTS ( |
| 119 | SELECT 1 |
| 120 | FROM workflow_runs blocker |
| 121 | WHERE r.concurrency_group <> '' |
| 122 | AND blocker.repo_id = r.repo_id |
| 123 | AND blocker.concurrency_group = r.concurrency_group |
| 124 | AND blocker.id <> r.id |
| 125 | AND blocker.status IN ('queued', 'running') |
| 126 | AND (blocker.created_at, blocker.id) < (r.created_at, r.id) |
| 127 | AND EXISTS ( |
| 128 | SELECT 1 |
| 129 | FROM workflow_jobs blocker_job |
| 130 | WHERE blocker_job.run_id = blocker.id |
| 131 | AND blocker_job.status IN ('queued', 'running') |
| 132 | AND blocker_job.cancel_requested = false |
| 133 | ) |
| 134 | ) |
| 135 | ORDER BY j.created_at ASC, j.id ASC |
| 136 | FOR UPDATE OF j SKIP LOCKED |
| 137 | LIMIT 1 |
| 138 | ), |
| 139 | claimed AS ( |
| 140 | UPDATE workflow_jobs j |
| 141 | SET runner_id = sqlc.arg(runner_id)::bigint, |
| 142 | status = 'running', |
| 143 | started_at = COALESCE(j.started_at, now()), |
| 144 | version = j.version + 1, |
| 145 | updated_at = now() |
| 146 | FROM candidate c |
| 147 | WHERE j.id = c.id |
| 148 | RETURNING j.id, j.run_id, j.job_index, j.job_key, j.job_name, j.runs_on, |
| 149 | j.runner_id, j.needs_jobs, j.if_expr, j.timeout_minutes, |
| 150 | j.permissions, j.job_env, j.status, j.conclusion, |
| 151 | j.cancel_requested, j.started_at, j.completed_at, j.version, |
| 152 | j.created_at, j.updated_at |
| 153 | ) |
| 154 | SELECT c.id, c.run_id, c.job_index, c.job_key, c.job_name, c.runs_on, |
| 155 | c.runner_id, c.needs_jobs, c.if_expr, c.timeout_minutes, |
| 156 | c.permissions, c.job_env, c.status, c.conclusion, |
| 157 | c.cancel_requested, c.started_at, c.completed_at, c.version, |
| 158 | c.created_at, c.updated_at, |
| 159 | r.repo_id, r.run_index, r.workflow_file, r.workflow_name, |
| 160 | r.head_sha, r.head_ref, r.event, r.event_payload, |
| 161 | COALESCE(owner_user.username, owner_org.slug)::text AS repo_owner, |
| 162 | repo.name AS repo_name |
| 163 | FROM claimed c |
| 164 | JOIN workflow_runs r ON r.id = c.run_id |
| 165 | JOIN repos repo ON repo.id = r.repo_id |
| 166 | LEFT JOIN users owner_user ON owner_user.id = repo.owner_user_id |
| 167 | LEFT JOIN orgs owner_org ON owner_org.id = repo.owner_org_id; |
| 168 | |
| 169 | -- name: ListJobsForRun :many |
| 170 | SELECT id, run_id, job_index, job_key, job_name, runs_on, status, |
| 171 | conclusion, cancel_requested, needs_jobs, started_at, completed_at, created_at, updated_at |
| 172 | FROM workflow_jobs |
| 173 | WHERE run_id = $1 |
| 174 | ORDER BY job_index ASC; |
| 175 |