| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | |
| 3 | -- name: InsertRunner :one |
| 4 | INSERT INTO workflow_runners (name, labels, capacity, registered_by_user_id) |
| 5 | VALUES ($1, $2, $3, $4) |
| 6 | RETURNING id, name, labels, capacity, status, last_heartbeat_at, |
| 7 | host_name, version, draining_at, drain_reason, revoked_at, |
| 8 | revoked_reason, registered_by_user_id, created_at, updated_at; |
| 9 | |
| 10 | -- name: GetRunnerByID :one |
| 11 | SELECT id, name, labels, capacity, status, last_heartbeat_at, |
| 12 | host_name, version, draining_at, drain_reason, revoked_at, |
| 13 | revoked_reason, registered_by_user_id, created_at, updated_at |
| 14 | FROM workflow_runners |
| 15 | WHERE id = $1; |
| 16 | |
| 17 | -- name: GetRunnerByName :one |
| 18 | SELECT id, name, labels, capacity, status, last_heartbeat_at, |
| 19 | host_name, version, draining_at, drain_reason, revoked_at, |
| 20 | revoked_reason, registered_by_user_id, created_at, updated_at |
| 21 | FROM workflow_runners |
| 22 | WHERE name = $1; |
| 23 | |
| 24 | -- name: ListRunners :many |
| 25 | SELECT r.id, r.name, r.labels, r.capacity, r.status, r.last_heartbeat_at, |
| 26 | r.host_name, r.version, r.draining_at, r.drain_reason, r.revoked_at, |
| 27 | r.revoked_reason, r.created_at, COUNT(j.id)::integer AS active_job_count |
| 28 | FROM workflow_runners r |
| 29 | LEFT JOIN workflow_jobs j |
| 30 | ON j.runner_id = r.id |
| 31 | AND j.status = 'running' |
| 32 | GROUP BY r.id, r.name, r.labels, r.capacity, r.status, r.last_heartbeat_at, |
| 33 | r.host_name, r.version, r.draining_at, r.drain_reason, r.revoked_at, |
| 34 | r.revoked_reason, r.created_at |
| 35 | ORDER BY r.name ASC; |
| 36 | |
| 37 | -- name: LockRunnerByID :one |
| 38 | SELECT id, name, labels, capacity, status, last_heartbeat_at, |
| 39 | host_name, version, draining_at, drain_reason, revoked_at, |
| 40 | revoked_reason, registered_by_user_id, created_at, updated_at |
| 41 | FROM workflow_runners |
| 42 | WHERE id = $1 |
| 43 | FOR UPDATE; |
| 44 | |
| 45 | -- name: HeartbeatRunner :one |
| 46 | UPDATE workflow_runners |
| 47 | SET labels = $2, |
| 48 | capacity = $3, |
| 49 | last_heartbeat_at = now(), |
| 50 | status = $4, |
| 51 | host_name = $5, |
| 52 | version = $6, |
| 53 | updated_at = now() |
| 54 | WHERE id = $1 |
| 55 | RETURNING id, name, labels, capacity, status, last_heartbeat_at, |
| 56 | host_name, version, draining_at, drain_reason, revoked_at, |
| 57 | revoked_reason, registered_by_user_id, created_at, updated_at; |
| 58 | |
| 59 | -- name: TouchRunnerHeartbeat :exec |
| 60 | UPDATE workflow_runners |
| 61 | SET last_heartbeat_at = now(), |
| 62 | status = $2, |
| 63 | updated_at = now() |
| 64 | WHERE id = $1; |
| 65 | |
| 66 | -- name: InsertRunnerToken :one |
| 67 | INSERT INTO runner_tokens (runner_id, token_hash, expires_at) |
| 68 | VALUES ($1, $2, $3) |
| 69 | RETURNING id, runner_id, token_hash, expires_at, revoked_at, created_at; |
| 70 | |
| 71 | -- name: GetRunnerByTokenHash :one |
| 72 | SELECT r.id, r.name, r.labels, r.capacity, r.status, |
| 73 | r.last_heartbeat_at, r.host_name, r.version, r.draining_at, |
| 74 | r.drain_reason, r.revoked_at, r.revoked_reason, r.created_at |
| 75 | FROM workflow_runners r |
| 76 | JOIN runner_tokens t ON t.runner_id = r.id |
| 77 | WHERE t.token_hash = $1 |
| 78 | AND t.revoked_at IS NULL |
| 79 | AND r.revoked_at IS NULL |
| 80 | AND (t.expires_at IS NULL OR t.expires_at > now()); |
| 81 | |
| 82 | -- name: SetRunnerDraining :one |
| 83 | UPDATE workflow_runners |
| 84 | SET draining_at = COALESCE(draining_at, now()), |
| 85 | drain_reason = $2, |
| 86 | updated_at = now() |
| 87 | WHERE id = $1 |
| 88 | AND revoked_at IS NULL |
| 89 | RETURNING id, name, labels, capacity, status, last_heartbeat_at, |
| 90 | host_name, version, draining_at, drain_reason, revoked_at, |
| 91 | revoked_reason, registered_by_user_id, created_at, updated_at; |
| 92 | |
| 93 | -- name: ClearRunnerDraining :one |
| 94 | UPDATE workflow_runners |
| 95 | SET draining_at = NULL, |
| 96 | drain_reason = '', |
| 97 | updated_at = now() |
| 98 | WHERE id = $1 |
| 99 | AND revoked_at IS NULL |
| 100 | RETURNING id, name, labels, capacity, status, last_heartbeat_at, |
| 101 | host_name, version, draining_at, drain_reason, revoked_at, |
| 102 | revoked_reason, registered_by_user_id, created_at, updated_at; |
| 103 | |
| 104 | -- name: RevokeRunner :one |
| 105 | UPDATE workflow_runners |
| 106 | SET revoked_at = COALESCE(revoked_at, now()), |
| 107 | revoked_reason = CASE WHEN revoked_at IS NULL THEN $2 ELSE revoked_reason END, |
| 108 | draining_at = COALESCE(draining_at, now()), |
| 109 | drain_reason = CASE |
| 110 | WHEN draining_at IS NULL THEN $2 |
| 111 | ELSE drain_reason |
| 112 | END, |
| 113 | status = 'offline', |
| 114 | updated_at = now() |
| 115 | WHERE id = $1 |
| 116 | RETURNING id, name, labels, capacity, status, last_heartbeat_at, |
| 117 | host_name, version, draining_at, drain_reason, revoked_at, |
| 118 | revoked_reason, registered_by_user_id, created_at, updated_at; |
| 119 | |
| 120 | -- name: RevokeAllTokensForRunner :exec |
| 121 | UPDATE runner_tokens |
| 122 | SET revoked_at = now() |
| 123 | WHERE runner_id = $1 AND revoked_at IS NULL; |
| 124 | |
| 125 | -- name: MarkStaleRunnersOffline :many |
| 126 | UPDATE workflow_runners |
| 127 | SET status = 'offline', |
| 128 | updated_at = now() |
| 129 | WHERE revoked_at IS NULL |
| 130 | AND status <> 'offline' |
| 131 | AND last_heartbeat_at IS NOT NULL |
| 132 | AND last_heartbeat_at < $1 |
| 133 | RETURNING id, name, labels, capacity, status, last_heartbeat_at, |
| 134 | host_name, version, draining_at, drain_reason, revoked_at, |
| 135 | revoked_reason, registered_by_user_id, created_at, updated_at; |
| 136 |