shithub Public

Watch 1 Fork 0 Star 0
MySQL · 14316 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: GetRepoByOwnerOrgAndName :one
93 -- S30: org-owner mirror of GetRepoByOwnerUserAndName. The (owner_org_id,
94 -- name) partial unique index from 0017 backs this lookup with the same
95 -- O(1) cost the user-side path enjoys.
96 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
97 default_branch, is_archived, archived_at, deleted_at,
98 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
99 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
100 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
101 star_count, watcher_count, fork_count, init_status,
102 last_indexed_oid
103 FROM repos
104 WHERE owner_org_id = $1 AND name = $2 AND deleted_at IS NULL;
105
106 -- name: GetSoftDeletedRepoByOwnerOrgAndName :one
107 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
108 default_branch, is_archived, archived_at, deleted_at,
109 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
110 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
111 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
112 star_count, watcher_count, fork_count, init_status,
113 last_indexed_oid
114 FROM repos
115 WHERE owner_org_id = $1 AND name = $2 AND deleted_at IS NOT NULL
116 ORDER BY deleted_at DESC, id DESC
117 LIMIT 1;
118
119 -- name: ExistsRepoForOwnerOrg :one
120 SELECT EXISTS(
121 SELECT 1 FROM repos
122 WHERE owner_org_id = $1 AND name = $2 AND deleted_at IS NULL
123 );
124
125 -- name: ListReposForOwnerOrg :many
126 SELECT id, owner_user_id, owner_org_id, name, description, visibility,
127 default_branch, is_archived, archived_at, deleted_at,
128 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
129 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
130 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
131 star_count, watcher_count, fork_count, init_status,
132 last_indexed_oid
133 FROM repos
134 WHERE owner_org_id = $1 AND deleted_at IS NULL
135 ORDER BY updated_at DESC;
136
137 -- name: ListProfilePinCandidateReposForUser :many
138 SELECT sqlc.embed(r), COALESCE(owner_user.username, owner_org.slug)::text AS owner_slug
139 FROM repos r
140 LEFT JOIN users owner_user ON owner_user.id = r.owner_user_id
141 LEFT JOIN orgs owner_org ON owner_org.id = r.owner_org_id
142 WHERE r.deleted_at IS NULL
143 AND r.visibility = 'public'
144 AND (
145 (r.owner_user_id IS NOT NULL AND owner_user.deleted_at IS NULL AND owner_user.suspended_at IS NULL)
146 OR (r.owner_org_id IS NOT NULL AND owner_org.deleted_at IS NULL)
147 )
148 AND (
149 r.owner_user_id = $1
150 OR EXISTS (
151 SELECT 1
152 FROM org_members m
153 WHERE m.org_id = r.owner_org_id
154 AND m.user_id = $1
155 )
156 OR EXISTS (
157 SELECT 1
158 FROM repo_collaborators c
159 WHERE c.repo_id = r.id
160 AND c.user_id = $1
161 )
162 )
163 ORDER BY lower(COALESCE(owner_user.username::text, owner_org.slug::text, '')), lower(r.name::text), r.id;
164
165 -- name: ListPublicContributionRepos :many
166 SELECT sqlc.embed(r), COALESCE(u.username, o.slug)::text AS owner_slug
167 FROM repos r
168 LEFT JOIN users u ON u.id = r.owner_user_id
169 LEFT JOIN orgs o ON o.id = r.owner_org_id
170 WHERE r.deleted_at IS NULL
171 AND r.visibility = 'public'
172 AND (
173 (r.owner_user_id IS NOT NULL AND u.deleted_at IS NULL AND u.suspended_at IS NULL)
174 OR (r.owner_org_id IS NOT NULL AND o.deleted_at IS NULL)
175 )
176 ORDER BY r.updated_at DESC, r.id DESC
177 LIMIT $1;
178
179 -- name: UpdateRepoGeneralSettings :exec
180 -- S32: General-tab settings persist via this single query so each
181 -- form post is one round-trip. The merge-method toggles are kept
182 -- separate from the repo create flow because they're admin-only.
183 UPDATE repos
184 SET description = $2,
185 has_issues = $3,
186 has_pulls = $4,
187 updated_at = now()
188 WHERE id = $1;
189
190 -- name: UpdateRepoMergeSettings :exec
191 UPDATE repos
192 SET allow_merge_commit = $2,
193 allow_squash_merge = $3,
194 allow_rebase_merge = $4,
195 default_merge_method = $5,
196 updated_at = now()
197 WHERE id = $1;
198
199 -- ─── repo_topics (S32) ─────────────────────────────────────────────
200
201 -- name: ListRepoTopics :many
202 SELECT topic FROM repo_topics WHERE repo_id = $1 ORDER BY topic ASC;
203
204 -- ─── profile/org pinned repositories ───────────────────────────────
205
206 -- name: GetProfilePinSetForUser :one
207 SELECT id FROM profile_pin_sets WHERE owner_user_id = $1;
208
209 -- name: GetProfilePinSetForOrg :one
210 SELECT id FROM profile_pin_sets WHERE owner_org_id = $1;
211
212 -- name: UpsertProfilePinSetForUser :one
213 INSERT INTO profile_pin_sets (owner_user_id)
214 VALUES ($1)
215 ON CONFLICT (owner_user_id) WHERE owner_user_id IS NOT NULL
216 DO UPDATE SET updated_at = now()
217 RETURNING id;
218
219 -- name: UpsertProfilePinSetForOrg :one
220 INSERT INTO profile_pin_sets (owner_org_id)
221 VALUES ($1)
222 ON CONFLICT (owner_org_id) WHERE owner_org_id IS NOT NULL
223 DO UPDATE SET updated_at = now()
224 RETURNING id;
225
226 -- name: ListProfilePinsForSet :many
227 SELECT repo_id, position
228 FROM profile_pins
229 WHERE set_id = $1
230 ORDER BY position ASC;
231
232 -- name: DeleteProfilePinsForSet :exec
233 DELETE FROM profile_pins WHERE set_id = $1;
234
235 -- name: InsertProfilePin :exec
236 INSERT INTO profile_pins (set_id, repo_id, position)
237 VALUES ($1, $2, $3);
238
239 -- name: ReplaceRepoTopics :exec
240 -- Atomic full-replace: callers compose the new topic set in Go,
241 -- then replace the existing rows in one tx (DELETE + INSERT). The
242 -- caller's tx wraps both calls for atomicity.
243 DELETE FROM repo_topics WHERE repo_id = $1;
244
245 -- name: InsertRepoTopic :exec
246 INSERT INTO repo_topics (repo_id, topic)
247 VALUES ($1, $2)
248 ON CONFLICT DO NOTHING;
249
250 -- name: SoftDeleteRepo :exec
251 UPDATE repos SET deleted_at = now() WHERE id = $1;
252
253 -- name: UpdateRepoDiskUsed :exec
254 UPDATE repos SET disk_used_bytes = $2 WHERE id = $1;
255
256 -- name: UpdateRepoDefaultBranchOID :exec
257 -- Set when push:process detects a commit on the repo's default branch.
258 -- Pass NULL to clear (e.g. when the branch is force-deleted in a future
259 -- sprint). The repo home view reads this to decide between empty and
260 -- populated layouts.
261 UPDATE repos SET default_branch_oid = sqlc.narg(default_branch_oid)::text WHERE id = $1;
262
263 -- name: ListAllRepoFullNames :many
264 -- Used by `shithubd hooks reinstall --all` to enumerate every active
265 -- bare repo on disk and re-link its hooks.
266 SELECT
267 r.id,
268 r.name,
269 u.username AS owner_username
270 FROM repos r
271 JOIN users u ON u.id = r.owner_user_id
272 WHERE r.deleted_at IS NULL
273 ORDER BY r.id;
274
275 -- ─── S27 forks ─────────────────────────────────────────────────────
276
277 -- name: CreateForkRepo :one
278 -- Insert a fork shell. Distinct from CreateRepo because forks set
279 -- `fork_of_repo_id` (which fires the fork_count trigger) and start
280 -- at init_status='init_pending' so the worker job can flip them to
281 -- 'initialized' once `git clone --bare --shared` finishes.
282 INSERT INTO repos (
283 owner_user_id, owner_org_id, name, description, visibility,
284 default_branch, fork_of_repo_id, init_status
285 ) VALUES (
286 $1, $2, $3, $4, $5, $6, $7, 'init_pending'
287 )
288 RETURNING id, owner_user_id, owner_org_id, name, description, visibility,
289 default_branch, is_archived, archived_at, deleted_at,
290 disk_used_bytes, fork_of_repo_id, license_key, primary_language,
291 has_issues, has_pulls, created_at, updated_at, default_branch_oid,
292 allow_squash_merge, allow_rebase_merge, allow_merge_commit, default_merge_method,
293 star_count, watcher_count, fork_count, init_status,
294 last_indexed_oid;
295
296 -- name: SetLastIndexedOID :exec
297 -- S28 code-search: the worker writes the OID it finished indexing
298 -- so the reconciler can detect drift (default_branch_oid moved but
299 -- last_indexed_oid lagged).
300 UPDATE repos SET last_indexed_oid = sqlc.narg(last_indexed_oid)::text WHERE id = $1;
301
302 -- name: ListReposNeedingReindex :many
303 -- S28 code-search reconciler: returns repos whose default_branch_oid
304 -- has advanced past last_indexed_oid (or last_indexed_oid is NULL
305 -- and a default exists). Limited so a single tick of the cron
306 -- doesn't try to re-index the whole world.
307 SELECT id, name, default_branch, default_branch_oid
308 FROM repos
309 WHERE deleted_at IS NULL
310 AND default_branch_oid IS NOT NULL
311 AND (last_indexed_oid IS NULL OR last_indexed_oid <> default_branch_oid)
312 ORDER BY id
313 LIMIT $1;
314
315 -- name: SetRepoInitStatus :exec
316 -- Promotes a fork from init_pending to initialized (or init_failed).
317 -- The DB row is created up-front so the URL resolves immediately and
318 -- the user sees a "preparing your fork" placeholder while the worker
319 -- runs `git clone --bare --shared`.
320 UPDATE repos SET init_status = $2 WHERE id = $1;
321
322 -- name: ListForksOfRepo :many
323 -- Forks of a given source repo, paginated, recency-sorted. Joined
324 -- with users for the owner display name. Excludes soft-deleted.
325 SELECT r.id, r.name, r.description, r.visibility, r.created_at,
326 r.star_count, r.fork_count, r.init_status,
327 u.username AS owner_username, u.display_name AS owner_display_name
328 FROM repos r
329 JOIN users u ON u.id = r.owner_user_id
330 WHERE r.fork_of_repo_id = $1
331 AND r.deleted_at IS NULL
332 ORDER BY r.created_at DESC
333 LIMIT $2 OFFSET $3;
334
335 -- name: CountForksOfRepo :one
336 SELECT count(*) FROM repos
337 WHERE fork_of_repo_id = $1 AND deleted_at IS NULL;
338
339 -- name: ListForksOfRepoForRepack :many
340 -- Used by S16's hard-delete cascade (S27 amendment): before deleting
341 -- a source repo, every fork must `git repack -a -d --no-shared` so
342 -- it has its own copy of the objects. Returns just enough to locate
343 -- the bare repo on disk.
344 SELECT r.id, r.name, u.username AS owner_username
345 FROM repos r
346 JOIN users u ON u.id = r.owner_user_id
347 WHERE r.fork_of_repo_id = $1
348 AND r.deleted_at IS NULL;
349
350
351 -- name: AdminForceDeleteRepo :exec
352 -- Bypasses the soft-delete grace window (admin only — S34): set
353 -- deleted_at to a year ago so the next lifecycle sweep hard-deletes
354 -- without waiting. Replaces the inline UPDATE in admin/repos.go
355 -- (SR2 M2).
356 UPDATE repos SET deleted_at = now() - interval '1 year' WHERE id = $1;
357