shithub Public

Watch 1 Fork 0 Star 0
MySQL · 9265 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2
3 -- ─── per-repo numbering ───────────────────────────────────────────────
4
5 -- name: EnsureRepoIssueCounter :exec
6 -- Lazy-initialize the counter row. Idempotent — invoked from repo
7 -- create AND from the first issue insert (defensive in case someone
8 -- migrates an old repo that predates S21).
9 INSERT INTO repo_issue_counter (repo_id, next_number)
10 VALUES ($1, 1)
11 ON CONFLICT (repo_id) DO NOTHING;
12
13 -- name: AllocateIssueNumber :one
14 -- UPDATE … RETURNING is concurrency-safe: each row update is
15 -- serialized by the row lock; concurrent transactions see different
16 -- values. The caller wraps this in the same tx as the issue insert.
17 UPDATE repo_issue_counter
18 SET next_number = next_number + 1
19 WHERE repo_id = $1
20 RETURNING (next_number - 1)::bigint AS allocated;
21
22
23 -- ─── issues ──────────────────────────────────────────────────────────
24
25 -- name: CreateIssue :one
26 INSERT INTO issues (
27 repo_id, number, kind, title, body, author_user_id
28 ) VALUES (
29 $1, $2, $3, $4, $5, sqlc.narg(author_user_id)::bigint
30 )
31 RETURNING *;
32
33 -- name: GetIssueByNumber :one
34 SELECT * FROM issues
35 WHERE repo_id = $1 AND number = $2;
36
37 -- name: GetIssueByID :one
38 SELECT * FROM issues WHERE id = $1;
39
40 -- name: ListIssues :many
41 -- Filterable list. Caller passes a state filter (open/closed/all
42 -- where 'all' is encoded as NULL); label/assignee/author/milestone
43 -- filtering happens after this query in Go for v1 — see the
44 -- internal/issues/list.go composer. Per-page hardcoded at 25 in the
45 -- handler; offset is the (page-1)*25.
46 SELECT * FROM issues
47 WHERE repo_id = $1
48 AND (sqlc.narg(state_filter)::text IS NULL OR state::text = sqlc.narg(state_filter)::text)
49 AND kind = COALESCE(sqlc.narg(kind)::issue_kind, 'issue')
50 ORDER BY updated_at DESC
51 LIMIT $2 OFFSET $3;
52
53 -- name: CountIssues :one
54 SELECT count(*)::bigint FROM issues
55 WHERE repo_id = $1
56 AND (sqlc.narg(state_filter)::text IS NULL OR state::text = sqlc.narg(state_filter)::text)
57 AND kind = COALESCE(sqlc.narg(kind)::issue_kind, 'issue');
58
59 -- name: UpdateIssueTitleBody :exec
60 UPDATE issues
61 SET title = $2, body = $3, body_html_cached = $4, edited_at = now(), updated_at = now()
62 WHERE id = $1;
63
64 -- name: SetIssueState :exec
65 UPDATE issues
66 SET state = $2,
67 state_reason = sqlc.narg(state_reason)::issue_state_reason,
68 closed_at = CASE WHEN $2::issue_state = 'closed' THEN now() ELSE NULL END,
69 closed_by_user_id = sqlc.narg(closed_by_user_id)::bigint,
70 updated_at = now()
71 WHERE id = $1;
72
73 -- name: SetIssueLock :exec
74 UPDATE issues
75 SET locked = $2, lock_reason = sqlc.narg(lock_reason)::text, updated_at = now()
76 WHERE id = $1;
77
78 -- name: SetIssueMilestone :exec
79 UPDATE issues
80 SET milestone_id = sqlc.narg(milestone_id)::bigint, updated_at = now()
81 WHERE id = $1;
82
83
84 -- ─── comments ────────────────────────────────────────────────────────
85
86 -- name: CreateIssueComment :one
87 INSERT INTO issue_comments (issue_id, author_user_id, body, body_html_cached)
88 VALUES ($1, sqlc.narg(author_user_id)::bigint, $2, $3)
89 RETURNING *;
90
91 -- name: ListIssueComments :many
92 SELECT * FROM issue_comments
93 WHERE issue_id = $1
94 ORDER BY created_at ASC;
95
96 -- name: GetIssueComment :one
97 SELECT * FROM issue_comments WHERE id = $1;
98
99 -- name: UpdateIssueCommentBody :exec
100 UPDATE issue_comments
101 SET body = $2, body_html_cached = $3, edited_at = now(), updated_at = now()
102 WHERE id = $1;
103
104 -- name: DeleteIssueComment :exec
105 DELETE FROM issue_comments WHERE id = $1;
106
107
108 -- ─── assignees ───────────────────────────────────────────────────────
109
110 -- name: AssignUserToIssue :exec
111 INSERT INTO issue_assignees (issue_id, user_id, assigned_by_user_id)
112 VALUES ($1, $2, sqlc.narg(assigned_by_user_id)::bigint)
113 ON CONFLICT (issue_id, user_id) DO NOTHING;
114
115 -- name: UnassignUserFromIssue :exec
116 DELETE FROM issue_assignees WHERE issue_id = $1 AND user_id = $2;
117
118 -- name: ListIssueAssignees :many
119 SELECT a.issue_id, a.user_id, a.assigned_at, u.username, u.display_name
120 FROM issue_assignees a
121 JOIN users u ON u.id = a.user_id
122 WHERE a.issue_id = $1
123 ORDER BY a.assigned_at;
124
125
126 -- ─── labels ──────────────────────────────────────────────────────────
127
128 -- name: CreateLabel :one
129 INSERT INTO labels (repo_id, name, color, description)
130 VALUES ($1, $2, $3, $4)
131 RETURNING *;
132
133 -- name: ListLabels :many
134 SELECT * FROM labels WHERE repo_id = $1 ORDER BY name;
135
136 -- name: GetLabelByName :one
137 SELECT * FROM labels WHERE repo_id = $1 AND name = $2;
138
139 -- name: UpdateLabel :exec
140 UPDATE labels
141 SET name = $2, color = $3, description = $4
142 WHERE id = $1;
143
144 -- name: DeleteLabel :exec
145 DELETE FROM labels WHERE id = $1;
146
147
148 -- ─── issue ↔ label ───────────────────────────────────────────────────
149
150 -- name: AddIssueLabel :exec
151 INSERT INTO issue_labels (issue_id, label_id, applied_by_user_id)
152 VALUES ($1, $2, sqlc.narg(applied_by_user_id)::bigint)
153 ON CONFLICT (issue_id, label_id) DO NOTHING;
154
155 -- name: RemoveIssueLabel :exec
156 DELETE FROM issue_labels WHERE issue_id = $1 AND label_id = $2;
157
158 -- name: ListLabelsOnIssue :many
159 SELECT l.id, l.repo_id, l.name, l.color, l.description, l.created_at
160 FROM issue_labels il
161 JOIN labels l ON l.id = il.label_id
162 WHERE il.issue_id = $1
163 ORDER BY l.name;
164
165
166 -- ─── milestones ──────────────────────────────────────────────────────
167
168 -- name: CreateMilestone :one
169 INSERT INTO milestones (repo_id, title, description, due_on)
170 VALUES ($1, $2, $3, sqlc.narg(due_on)::timestamptz)
171 RETURNING *;
172
173 -- name: ListMilestones :many
174 SELECT * FROM milestones WHERE repo_id = $1 ORDER BY state, due_on NULLS LAST, title;
175
176 -- name: GetMilestone :one
177 SELECT * FROM milestones WHERE id = $1;
178
179 -- name: UpdateMilestone :exec
180 UPDATE milestones SET title = $2, description = $3, due_on = sqlc.narg(due_on)::timestamptz WHERE id = $1;
181
182 -- name: SetMilestoneState :exec
183 UPDATE milestones SET state = $2, closed_at = CASE WHEN $2::milestone_state = 'closed' THEN now() ELSE NULL END WHERE id = $1;
184
185 -- name: DeleteMilestone :exec
186 DELETE FROM milestones WHERE id = $1;
187
188 -- name: MilestoneIssueCounts :one
189 -- Open + closed counts for the milestone progress bar.
190 SELECT
191 count(*) FILTER (WHERE state = 'open')::int AS open_count,
192 count(*) FILTER (WHERE state = 'closed')::int AS closed_count
193 FROM issues
194 WHERE milestone_id = $1;
195
196
197 -- ─── events + references ─────────────────────────────────────────────
198
199 -- name: InsertIssueEvent :one
200 INSERT INTO issue_events (issue_id, actor_user_id, kind, meta, ref_target_id)
201 VALUES ($1, sqlc.narg(actor_user_id)::bigint, $2, $3, sqlc.narg(ref_target_id)::bigint)
202 RETURNING *;
203
204 -- name: ListIssueEvents :many
205 SELECT * FROM issue_events
206 WHERE issue_id = $1
207 ORDER BY created_at ASC;
208
209 -- name: ListIssueEventsWithActor :many
210 -- Paginated timeline shape for the REST `/issues/{n}/events` endpoint:
211 -- the same event rows ListIssueEvents returns, but LEFT-joined to users
212 -- so the response can carry `actor_username` without a second round-trip.
213 -- Suspended/deleted actor rows still appear (the timeline is historical
214 -- truth), with NULL username when the user row is unrecoverable.
215 SELECT e.id, e.issue_id, e.actor_user_id, e.kind, e.meta, e.ref_target_id,
216 e.created_at, u.username AS actor_username
217 FROM issue_events e
218 LEFT JOIN users u ON u.id = e.actor_user_id
219 WHERE e.issue_id = $1
220 ORDER BY e.created_at ASC, e.id ASC
221 LIMIT $2 OFFSET $3;
222
223 -- name: CountIssueEvents :one
224 SELECT COUNT(*) FROM issue_events WHERE issue_id = $1;
225
226 -- name: ListProfileAuthoredIssuesForUser :many
227 -- Cross-repository profile contribution activity. The handler performs the
228 -- final repo visibility gate with policy.IsVisibleTo so private issues and
229 -- PRs never leak through the public profile timeline.
230 SELECT
231 i.id, i.repo_id, i.number, i.kind, i.title, i.state, i.created_at, i.closed_at,
232 pr.merged_at,
233 r.name AS repo_name, r.visibility, r.owner_user_id, r.owner_org_id,
234 COALESCE(u.username, o.slug)::text AS owner_slug
235 FROM issues i
236 JOIN repos r ON r.id = i.repo_id
237 LEFT JOIN pull_requests pr ON pr.issue_id = i.id
238 LEFT JOIN users u ON u.id = r.owner_user_id
239 LEFT JOIN orgs o ON o.id = r.owner_org_id
240 WHERE i.author_user_id = $1
241 AND i.created_at >= $2
242 AND i.created_at < $3
243 AND r.deleted_at IS NULL
244 ORDER BY i.created_at DESC, i.id DESC
245 LIMIT $4;
246
247 -- name: InsertIssueReference :exec
248 INSERT INTO issue_references (
249 source_issue_id, target_issue_id, source_kind, source_object_id
250 ) VALUES (
251 sqlc.narg(source_issue_id)::bigint, $1, $2, sqlc.narg(source_object_id)::bigint
252 );
253