shithub Public

Watch 1 Fork 0 Star 0
MySQL · 7547 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2
3 -- name: InsertWorkflowRun :one
4 INSERT INTO workflow_runs (
5 repo_id, run_index, workflow_file, workflow_name,
6 head_sha, head_ref, event, event_payload,
7 actor_user_id, parent_run_id, concurrency_group, need_approval
8 ) VALUES (
9 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12
10 )
11 RETURNING id, repo_id, run_index, workflow_file, workflow_name,
12 head_sha, head_ref, event, event_payload,
13 actor_user_id, parent_run_id, concurrency_group,
14 status, conclusion, pinned, need_approval, approved_by_user_id,
15 started_at, completed_at, version, created_at, updated_at, trigger_event_id;
16
17 -- name: EnqueueWorkflowRun :one
18 -- Idempotent insert: if a row with the same (repo_id, workflow_file,
19 -- trigger_event_id) already exists, returns no rows (pgx.ErrNoRows in
20 -- Go). The handler treats that as a successful no-op so worker
21 -- retries and admin replays of the same triggering event don't
22 -- duplicate runs.
23 --
24 -- The ON CONFLICT predicate matches the partial unique index defined
25 -- in migration 0051; both must agree for postgres to infer the
26 -- target.
27 INSERT INTO workflow_runs (
28 repo_id, run_index, workflow_file, workflow_name,
29 head_sha, head_ref, event, event_payload,
30 actor_user_id, parent_run_id, concurrency_group, need_approval,
31 trigger_event_id
32 ) VALUES (
33 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13
34 )
35 ON CONFLICT (repo_id, workflow_file, trigger_event_id) WHERE trigger_event_id <> ''
36 DO NOTHING
37 RETURNING id, repo_id, run_index, workflow_file, workflow_name,
38 head_sha, head_ref, event, event_payload,
39 actor_user_id, parent_run_id, concurrency_group,
40 status, conclusion, pinned, need_approval, approved_by_user_id,
41 started_at, completed_at, version, created_at, updated_at, trigger_event_id;
42
43 -- name: LookupWorkflowRunByTriggerEvent :one
44 -- Companion to EnqueueWorkflowRun for the conflict path: when an
45 -- INSERT ... ON CONFLICT DO NOTHING returns no rows, the trigger
46 -- handler uses this to find the existing row so it can surface a
47 -- stable RunID. Matches the partial-unique index from migration 0051.
48 SELECT id, repo_id, run_index, workflow_file, workflow_name,
49 head_sha, head_ref, event, event_payload,
50 actor_user_id, parent_run_id, concurrency_group,
51 status, conclusion, pinned, need_approval, approved_by_user_id,
52 started_at, completed_at, version, created_at, updated_at, trigger_event_id
53 FROM workflow_runs
54 WHERE repo_id = $1 AND workflow_file = $2 AND trigger_event_id = $3;
55
56 -- name: GetWorkflowRunByID :one
57 SELECT id, repo_id, run_index, workflow_file, workflow_name,
58 head_sha, head_ref, event, event_payload,
59 actor_user_id, parent_run_id, concurrency_group,
60 status, conclusion, pinned, need_approval, approved_by_user_id,
61 started_at, completed_at, version, created_at, updated_at, trigger_event_id
62 FROM workflow_runs
63 WHERE id = $1;
64
65 -- name: GetWorkflowRunForRepoByIndex :one
66 SELECT r.id, r.repo_id, r.run_index, r.workflow_file, r.workflow_name,
67 r.head_sha, r.head_ref, r.event, r.event_payload,
68 r.actor_user_id, COALESCE(u.username::text, '')::text AS actor_username,
69 r.parent_run_id, r.concurrency_group,
70 r.status, r.conclusion, r.pinned, r.need_approval, r.approved_by_user_id,
71 r.started_at, r.completed_at, r.version, r.created_at, r.updated_at, r.trigger_event_id
72 FROM workflow_runs r
73 LEFT JOIN users u ON u.id = r.actor_user_id
74 WHERE r.repo_id = $1 AND r.run_index = $2;
75
76 -- name: MarkWorkflowRunRunning :exec
77 UPDATE workflow_runs
78 SET status = 'running',
79 started_at = COALESCE(started_at, now()),
80 version = version + 1,
81 updated_at = now()
82 WHERE id = $1 AND status = 'queued';
83
84 -- name: CompleteWorkflowRun :one
85 UPDATE workflow_runs
86 SET status = 'completed',
87 conclusion = sqlc.arg(conclusion)::check_conclusion,
88 started_at = COALESCE(started_at, now()),
89 completed_at = COALESCE(completed_at, now()),
90 version = version + 1,
91 updated_at = now()
92 WHERE id = $1
93 RETURNING id, repo_id, run_index, workflow_file, workflow_name,
94 head_sha, head_ref, event, event_payload,
95 actor_user_id, parent_run_id, concurrency_group,
96 status, conclusion, pinned, need_approval, approved_by_user_id,
97 started_at, completed_at, version, created_at, updated_at, trigger_event_id;
98
99 -- name: NextRunIndexForRepo :one
100 -- Atomic next-index emitter: take the max + 1 for this repo. Pairs
101 -- with the (repo_id, run_index) UNIQUE so concurrent inserts that
102 -- race here will catch a unique-violation and the caller retries.
103 -- Cast to bigint so sqlc generates int64 (the column type) rather
104 -- than int32 (the type the +1 literal would default to).
105 SELECT (COALESCE(MAX(run_index), 0) + 1)::bigint AS next_index
106 FROM workflow_runs
107 WHERE repo_id = $1;
108
109 -- name: ListWorkflowRunsForRepo :many
110 SELECT r.id, r.repo_id, r.run_index, r.workflow_file, r.workflow_name,
111 r.head_sha, r.head_ref, r.event, r.status, r.conclusion,
112 r.actor_user_id, COALESCE(u.username::text, '')::text AS actor_username,
113 r.started_at, r.completed_at, r.created_at, r.updated_at
114 FROM workflow_runs r
115 LEFT JOIN users u ON u.id = r.actor_user_id
116 WHERE r.repo_id = sqlc.arg(repo_id)::bigint
117 AND (sqlc.narg(workflow_file)::text IS NULL OR r.workflow_file = sqlc.narg(workflow_file)::text)
118 AND (sqlc.narg(head_ref)::text IS NULL OR r.head_ref = sqlc.narg(head_ref)::text)
119 AND (sqlc.narg(event)::workflow_run_event IS NULL OR r.event = sqlc.narg(event)::workflow_run_event)
120 AND (sqlc.narg(status)::workflow_run_status IS NULL OR r.status = sqlc.narg(status)::workflow_run_status)
121 AND (sqlc.narg(conclusion)::check_conclusion IS NULL OR r.conclusion = sqlc.narg(conclusion)::check_conclusion)
122 AND (sqlc.narg(actor_username)::text IS NULL OR u.username = sqlc.narg(actor_username)::citext)
123 ORDER BY r.created_at DESC, r.id DESC
124 LIMIT sqlc.arg(page_limit) OFFSET sqlc.arg(page_offset);
125
126 -- name: CountWorkflowRunsForRepo :one
127 SELECT COUNT(*)::bigint
128 FROM workflow_runs r
129 LEFT JOIN users u ON u.id = r.actor_user_id
130 WHERE r.repo_id = sqlc.arg(repo_id)::bigint
131 AND (sqlc.narg(workflow_file)::text IS NULL OR r.workflow_file = sqlc.narg(workflow_file)::text)
132 AND (sqlc.narg(head_ref)::text IS NULL OR r.head_ref = sqlc.narg(head_ref)::text)
133 AND (sqlc.narg(event)::workflow_run_event IS NULL OR r.event = sqlc.narg(event)::workflow_run_event)
134 AND (sqlc.narg(status)::workflow_run_status IS NULL OR r.status = sqlc.narg(status)::workflow_run_status)
135 AND (sqlc.narg(conclusion)::check_conclusion IS NULL OR r.conclusion = sqlc.narg(conclusion)::check_conclusion)
136 AND (sqlc.narg(actor_username)::text IS NULL OR u.username = sqlc.narg(actor_username)::citext);
137
138 -- name: ListWorkflowRunWorkflowsForRepo :many
139 WITH ranked AS (
140 SELECT workflow_file,
141 workflow_name,
142 (COUNT(*) OVER (PARTITION BY workflow_file))::bigint AS run_count,
143 ROW_NUMBER() OVER (PARTITION BY workflow_file ORDER BY created_at DESC, id DESC) AS rn
144 FROM workflow_runs
145 WHERE repo_id = $1
146 )
147 SELECT workflow_file,
148 COALESCE(NULLIF(workflow_name, ''), workflow_file) AS workflow_name,
149 run_count
150 FROM ranked
151 WHERE rn = 1
152 ORDER BY lower(COALESCE(NULLIF(workflow_name, ''), workflow_file)), workflow_file;
153
154 -- name: DeleteOldWorkflowRunsForCleanup :execrows
155 DELETE FROM workflow_runs
156 WHERE pinned = false
157 AND status IN ('completed', 'cancelled')
158 AND completed_at IS NOT NULL
159 AND completed_at < $1;
160