shithub Public

Watch 1 Fork 0 Star 0
MySQL · 18831 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2
3 -- name: CreateRepo :one
4 INSERT INTO repos (
5 owner_user_id, owner_org_id, name, description, visibility,
6 default_branch, license_key, primary_language
7 ) VALUES (
8 $1, $2, $3, $4, $5, $6, $7, $8
9 )
10 RETURNING id, owner_user_id, owner_org_id, name, description, visibility,
11 default_branch, is_archived, archived_at, deleted_at,
12 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
13 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
14 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
15 star_count, watcher_count, fork_count, init_status,
16 last_indexed_oid;
17
18 -- name: LockRepoOwnerName :exec
19 -- Serializes DB + filesystem operations for one logical owner/name
20 -- pair. Create, soft-delete, restore, and hard-delete all touch the
21 -- canonical bare path; a transaction-scoped advisory lock keeps those
22 -- cross-resource moves from racing.
23 SELECT pg_advisory_xact_lock(hashtextextended($1, 0));
24
25 -- name: GetRepoByID :one
26 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
27 default_branch, is_archived, archived_at, deleted_at,
28 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
29 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
30 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
31 star_count, watcher_count, fork_count, init_status,
32 last_indexed_oid
33 FROM repos
34 WHERE id = $1;
35
36 -- name: GetRepoOwnerUsernameByID :one
37 -- Returns the owner slug for a repo. Used by size-recalc, indexing, and
38 -- other jobs that need the bare-repo on-disk path. Org-owned repos use the
39 -- org slug in the same path position as user-owned repos.
40 SELECT COALESCE(u.username::varchar, o.slug::varchar) AS owner_username, r.name AS repo_name
41 FROM repos r
42 LEFT JOIN users u ON u.id = r.owner_user_id
43 LEFT JOIN orgs o ON o.id = r.owner_org_id
44 WHERE r.id = $1;
45
46 -- name: GetRepoByOwnerUserAndName :one
47 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
48 default_branch, is_archived, archived_at, deleted_at,
49 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
50 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
51 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
52 star_count, watcher_count, fork_count, init_status,
53 last_indexed_oid
54 FROM repos
55 WHERE owner_user_id = $1 AND name = $2 AND deleted_at IS NULL;
56
57 -- name: GetSoftDeletedRepoByOwnerUserAndName :one
58 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
59 default_branch, is_archived, archived_at, deleted_at,
60 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
61 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
62 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
63 star_count, watcher_count, fork_count, init_status,
64 last_indexed_oid
65 FROM repos
66 WHERE owner_user_id = $1 AND name = $2 AND deleted_at IS NOT NULL
67 ORDER BY deleted_at DESC, id DESC
68 LIMIT 1;
69
70 -- name: ExistsRepoForOwnerUser :one
71 SELECT EXISTS(
72 SELECT 1 FROM repos
73 WHERE owner_user_id = $1 AND name = $2 AND deleted_at IS NULL
74 );
75
76 -- name: ListReposForOwnerUser :many
77 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
78 default_branch, is_archived, archived_at, deleted_at,
79 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
80 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
81 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
82 star_count, watcher_count, fork_count, init_status,
83 last_indexed_oid
84 FROM repos
85 WHERE owner_user_id = $1 AND deleted_at IS NULL
86 ORDER BY updated_at DESC;
87
88 -- name: CountReposForOwnerUser :one
89 SELECT count(*) FROM repos
90 WHERE owner_user_id = $1 AND deleted_at IS NULL;
91
92 -- name: ListReposForOwnerUserPaged :many
93 -- Paginated mirror of ListReposForOwnerUser. Used by the REST surface
94 -- where the caller is the owner (or a site admin) and is allowed to see
95 -- private repos. Order matches the un-paginated form so callers can
96 -- swap between them without observing a reshuffle.
97 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
98 default_branch, is_archived, archived_at, deleted_at,
99 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
100 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
101 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
102 star_count, watcher_count, fork_count, init_status,
103 last_indexed_oid
104 FROM repos
105 WHERE owner_user_id = $1 AND deleted_at IS NULL
106 ORDER BY updated_at DESC
107 LIMIT $2 OFFSET $3;
108
109 -- name: ListPublicReposForOwnerUser :many
110 -- Public-only view of a user's repos for the "list another user's repos"
111 -- REST endpoint. Hidden behind the same updated_at ordering.
112 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
113 default_branch, is_archived, archived_at, deleted_at,
114 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
115 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
116 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
117 star_count, watcher_count, fork_count, init_status,
118 last_indexed_oid
119 FROM repos
120 WHERE owner_user_id = $1
121 AND visibility = 'public'
122 AND deleted_at IS NULL
123 ORDER BY updated_at DESC
124 LIMIT $2 OFFSET $3;
125
126 -- name: CountPublicReposForOwnerUser :one
127 SELECT count(*) FROM repos
128 WHERE owner_user_id = $1
129 AND visibility = 'public'
130 AND deleted_at IS NULL;
131
132 -- name: GetRepoByOwnerOrgAndName :one
133 -- S30: org-owner mirror of GetRepoByOwnerUserAndName. The (owner_org_id,
134 -- name) partial unique index from 0017 backs this lookup with the same
135 -- O(1) cost the user-side path enjoys.
136 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
137 default_branch, is_archived, archived_at, deleted_at,
138 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
139 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
140 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
141 star_count, watcher_count, fork_count, init_status,
142 last_indexed_oid
143 FROM repos
144 WHERE owner_org_id = $1 AND name = $2 AND deleted_at IS NULL;
145
146 -- name: GetSoftDeletedRepoByOwnerOrgAndName :one
147 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
148 default_branch, is_archived, archived_at, deleted_at,
149 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
150 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
151 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
152 star_count, watcher_count, fork_count, init_status,
153 last_indexed_oid
154 FROM repos
155 WHERE owner_org_id = $1 AND name = $2 AND deleted_at IS NOT NULL
156 ORDER BY deleted_at DESC, id DESC
157 LIMIT 1;
158
159 -- name: ExistsRepoForOwnerOrg :one
160 SELECT EXISTS(
161 SELECT 1 FROM repos
162 WHERE owner_org_id = $1 AND name = $2 AND deleted_at IS NULL
163 );
164
165 -- name: ListReposForOwnerOrg :many
166 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
167 default_branch, is_archived, archived_at, deleted_at,
168 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
169 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
170 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
171 star_count, watcher_count, fork_count, init_status,
172 last_indexed_oid
173 FROM repos
174 WHERE owner_org_id = $1 AND deleted_at IS NULL
175 ORDER BY updated_at DESC;
176
177 -- name: ListReposForOwnerOrgPaged :many
178 -- Paginated mirror of ListReposForOwnerOrg for the REST list endpoint
179 -- when the viewer is an org member and may see private repos.
180 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
181 default_branch, is_archived, archived_at, deleted_at,
182 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
183 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
184 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
185 star_count, watcher_count, fork_count, init_status,
186 last_indexed_oid
187 FROM repos
188 WHERE owner_org_id = $1 AND deleted_at IS NULL
189 ORDER BY updated_at DESC
190 LIMIT $2 OFFSET $3;
191
192 -- name: CountReposForOwnerOrg :one
193 SELECT count(*) FROM repos
194 WHERE owner_org_id = $1 AND deleted_at IS NULL;
195
196 -- name: ListPublicReposForOwnerOrg :many
197 -- Public-only org repo listing for non-member callers (and the no-auth
198 -- public-discovery REST view).
199 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
200 default_branch, is_archived, archived_at, deleted_at,
201 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
202 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
203 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
204 star_count, watcher_count, fork_count, init_status,
205 last_indexed_oid
206 FROM repos
207 WHERE owner_org_id = $1
208 AND visibility = 'public'
209 AND deleted_at IS NULL
210 ORDER BY updated_at DESC
211 LIMIT $2 OFFSET $3;
212
213 -- name: CountPublicReposForOwnerOrg :one
214 SELECT count(*) FROM repos
215 WHERE owner_org_id = $1
216 AND visibility = 'public'
217 AND deleted_at IS NULL;
218
219 -- name: ListProfilePinCandidateReposForUser :many
220 SELECT sqlc.embed(r), COALESCE(owner_user.username, owner_org.slug)::text AS owner_slug
221 FROM repos r
222 LEFT JOIN users owner_user ON owner_user.id = r.owner_user_id
223 LEFT JOIN orgs owner_org ON owner_org.id = r.owner_org_id
224 WHERE r.deleted_at IS NULL
225 AND r.visibility = 'public'
226 AND (
227 (r.owner_user_id IS NOT NULL AND owner_user.deleted_at IS NULL AND owner_user.suspended_at IS NULL)
228 OR (r.owner_org_id IS NOT NULL AND owner_org.deleted_at IS NULL)
229 )
230 AND (
231 r.owner_user_id = $1
232 OR EXISTS (
233 SELECT 1
234 FROM org_members m
235 WHERE m.org_id = r.owner_org_id
236 AND m.user_id = $1
237 )
238 OR EXISTS (
239 SELECT 1
240 FROM repo_collaborators c
241 WHERE c.repo_id = r.id
242 AND c.user_id = $1
243 )
244 )
245 ORDER BY lower(COALESCE(owner_user.username::text, owner_org.slug::text, '')), lower(r.name::text), r.id;
246
247 -- name: ListPublicContributionRepos :many
248 SELECT sqlc.embed(r), COALESCE(u.username, o.slug)::text AS owner_slug
249 FROM repos r
250 LEFT JOIN users u ON u.id = r.owner_user_id
251 LEFT JOIN orgs o ON o.id = r.owner_org_id
252 WHERE r.deleted_at IS NULL
253 AND r.visibility = 'public'
254 AND (
255 (r.owner_user_id IS NOT NULL AND u.deleted_at IS NULL AND u.suspended_at IS NULL)
256 OR (r.owner_org_id IS NOT NULL AND o.deleted_at IS NULL)
257 )
258 ORDER BY r.updated_at DESC, r.id DESC
259 LIMIT $1;
260
261 -- name: UpdateRepoGeneralSettings :exec
262 -- S32: General-tab settings persist via this single query so each
263 -- form post is one round-trip. The merge-method toggles are kept
264 -- separate from the repo create flow because they're admin-only.
265 UPDATE repos
266 SET description = $2,
267 has_issues = $3,
268 has_pulls = $4,
269 updated_at = now()
270 WHERE id = $1;
271
272 -- name: UpdateRepoMergeSettings :exec
273 UPDATE repos
274 SET allow_merge_commit = $2,
275 allow_squash_merge = $3,
276 allow_rebase_merge = $4,
277 default_merge_method = $5,
278 updated_at = now()
279 WHERE id = $1;
280
281 -- ─── repo_topics (S32) ─────────────────────────────────────────────
282
283 -- name: ListRepoTopics :many
284 SELECT topic FROM repo_topics WHERE repo_id = $1 ORDER BY topic ASC;
285
286 -- ─── profile/org pinned repositories ───────────────────────────────
287
288 -- name: GetProfilePinSetForUser :one
289 SELECT id FROM profile_pin_sets WHERE owner_user_id = $1;
290
291 -- name: GetProfilePinSetForOrg :one
292 SELECT id FROM profile_pin_sets WHERE owner_org_id = $1;
293
294 -- name: UpsertProfilePinSetForUser :one
295 INSERT INTO profile_pin_sets (owner_user_id)
296 VALUES ($1)
297 ON CONFLICT (owner_user_id) WHERE owner_user_id IS NOT NULL
298 DO UPDATE SET updated_at = now()
299 RETURNING id;
300
301 -- name: UpsertProfilePinSetForOrg :one
302 INSERT INTO profile_pin_sets (owner_org_id)
303 VALUES ($1)
304 ON CONFLICT (owner_org_id) WHERE owner_org_id IS NOT NULL
305 DO UPDATE SET updated_at = now()
306 RETURNING id;
307
308 -- name: ListProfilePinsForSet :many
309 SELECT repo_id, position
310 FROM profile_pins
311 WHERE set_id = $1
312 ORDER BY position ASC;
313
314 -- name: DeleteProfilePinsForSet :exec
315 DELETE FROM profile_pins WHERE set_id = $1;
316
317 -- name: InsertProfilePin :exec
318 INSERT INTO profile_pins (set_id, repo_id, position)
319 VALUES ($1, $2, $3);
320
321 -- name: ReplaceRepoTopics :exec
322 -- Atomic full-replace: callers compose the new topic set in Go,
323 -- then replace the existing rows in one tx (DELETE + INSERT). The
324 -- caller's tx wraps both calls for atomicity.
325 DELETE FROM repo_topics WHERE repo_id = $1;
326
327 -- name: InsertRepoTopic :exec
328 INSERT INTO repo_topics (repo_id, topic)
329 VALUES ($1, $2)
330 ON CONFLICT DO NOTHING;
331
332 -- name: SoftDeleteRepo :exec
333 UPDATE repos SET deleted_at = now() WHERE id = $1;
334
335 -- name: UpdateRepoDiskUsed :exec
336 UPDATE repos SET disk_used_bytes = $2 WHERE id = $1;
337
338 -- name: UpdateRepoDefaultBranchOID :exec
339 -- Set when push:process detects a commit on the repo's default branch.
340 -- Pass NULL to clear (e.g. when the branch is force-deleted in a future
341 -- sprint). The repo home view reads this to decide between empty and
342 -- populated layouts.
343 UPDATE repos SET default_branch_oid = sqlc.narg(default_branch_oid)::text WHERE id = $1;
344
345 -- name: ListAllRepoFullNames :many
346 -- Used by `shithubd hooks reinstall --all` to enumerate every active
347 -- bare repo on disk and re-link its hooks.
348 SELECT
349 r.id,
350 r.name,
351 u.username AS owner_username
352 FROM repos r
353 JOIN users u ON u.id = r.owner_user_id
354 WHERE r.deleted_at IS NULL
355 ORDER BY r.id;
356
357 -- name: ListAllActiveReposWithOwner :many
358 -- Used by the GPG verification backfill (S51) to enumerate every
359 -- active repo system-wide. Unlike ListAllRepoFullNames this query
360 -- handles BOTH user-owned and org-owned repos via a COALESCE between
361 -- users.username and orgs.slug; the owner string is whatever
362 -- RepoFS.RepoPath expects.
363 SELECT
364 r.id,
365 r.name,
366 r.default_branch,
367 COALESCE(u.username, o.slug) AS owner
368 FROM repos r
369 LEFT JOIN users u ON u.id = r.owner_user_id
370 LEFT JOIN orgs o ON o.id = r.owner_org_id
371 WHERE r.deleted_at IS NULL
372 ORDER BY r.id;
373
374 -- name: GetRepoForBackfill :one
375 -- Lookup the per-repo backfill metadata. Mirrors the row shape of
376 -- ListAllActiveReposWithOwner so the per-repo job handler can run
377 -- the same code path the bulk handler uses.
378 SELECT
379 r.id,
380 r.name,
381 r.default_branch,
382 COALESCE(u.username, o.slug) AS owner
383 FROM repos r
384 LEFT JOIN users u ON u.id = r.owner_user_id
385 LEFT JOIN orgs o ON o.id = r.owner_org_id
386 WHERE r.id = $1 AND r.deleted_at IS NULL;
387
388 -- ─── S27 forks ─────────────────────────────────────────────────────
389
390 -- name: CreateForkRepo :one
391 -- Insert a fork shell. Distinct from CreateRepo because forks set
392 -- `fork_of_repo_id` (which fires the fork_count trigger) and start
393 -- at init_status='init_pending' so the worker job can flip them to
394 -- 'initialized' once `git clone --bare --shared` finishes.
395 INSERT INTO repos (
396 owner_user_id, owner_org_id, name, description, visibility,
397 default_branch, fork_of_repo_id, init_status
398 ) VALUES (
399 $1, $2, $3, $4, $5, $6, $7, 'init_pending'
400 )
401 RETURNING id, owner_user_id, owner_org_id, name, description, visibility,
402 default_branch, is_archived, archived_at, deleted_at,
403 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
404 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
405 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
406 star_count, watcher_count, fork_count, init_status,
407 last_indexed_oid;
408
409 -- name: SetLastIndexedOID :exec
410 -- S28 code-search: the worker writes the OID it finished indexing
411 -- so the reconciler can detect drift (default_branch_oid moved but
412 -- last_indexed_oid lagged).
413 UPDATE repos SET last_indexed_oid = sqlc.narg(last_indexed_oid)::text WHERE id = $1;
414
415 -- name: ListReposNeedingReindex :many
416 -- S28 code-search reconciler: returns repos whose default_branch_oid
417 -- has advanced past last_indexed_oid (or last_indexed_oid is NULL
418 -- and a default exists). Limited so a single tick of the cron
419 -- doesn't try to re-index the whole world.
420 SELECT id, name, default_branch, default_branch_oid
421 FROM repos
422 WHERE deleted_at IS NULL
423 AND default_branch_oid IS NOT NULL
424 AND (last_indexed_oid IS NULL OR last_indexed_oid <> default_branch_oid)
425 ORDER BY id
426 LIMIT $1;
427
428 -- name: SetRepoInitStatus :exec
429 -- Promotes a fork from init_pending to initialized (or init_failed).
430 -- The DB row is created up-front so the URL resolves immediately and
431 -- the user sees a "preparing your fork" placeholder while the worker
432 -- runs `git clone --bare --shared`.
433 UPDATE repos SET init_status = $2 WHERE id = $1;
434
435 -- name: ListForksOfRepo :many
436 -- Forks of a given source repo, paginated, recency-sorted. Joined
437 -- with users for the owner display name. Excludes soft-deleted.
438 SELECT r.id, r.name, r.description, r.visibility, r.created_at,
439 r.star_count, r.fork_count, r.init_status,
440 u.username AS owner_username, u.display_name AS owner_display_name
441 FROM repos r
442 JOIN users u ON u.id = r.owner_user_id
443 WHERE r.fork_of_repo_id = $1
444 AND r.deleted_at IS NULL
445 ORDER BY r.created_at DESC
446 LIMIT $2 OFFSET $3;
447
448 -- name: CountForksOfRepo :one
449 SELECT count(*) FROM repos
450 WHERE fork_of_repo_id = $1 AND deleted_at IS NULL;
451
452 -- name: ListForksOfRepoForRepack :many
453 -- Used by S16's hard-delete cascade (S27 amendment): before deleting
454 -- a source repo, every fork must `git repack -a -d --no-shared` so
455 -- it has its own copy of the objects. Returns just enough to locate
456 -- the bare repo on disk.
457 SELECT r.id, r.name, u.username AS owner_username
458 FROM repos r
459 JOIN users u ON u.id = r.owner_user_id
460 WHERE r.fork_of_repo_id = $1
461 AND r.deleted_at IS NULL;
462
463
464 -- name: AdminForceDeleteRepo :exec
465 -- Bypasses the soft-delete grace window (admin only — S34): set
466 -- deleted_at to a year ago so the next lifecycle sweep hard-deletes
467 -- without waiting. Replaces the inline UPDATE in admin/repos.go
468 -- (SR2 M2).
469 UPDATE repos SET deleted_at = now() - interval '1 year' WHERE id = $1;
470