shithub Public

Watch 1 Fork 0 Star 0
MySQL · 6934 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- S16 lifecycle queries. Kept in a separate file from repos.sql so the
4 -- mainline CRUD stays easy to read.
5
6 -- ─── repo mutations ────────────────────────────────────────────────────
7
8 -- name: RenameRepo :exec
9 -- Same-owner rename. The handler validates the new name shape and the
10 -- redirect row is INSERTed in the same tx.
11 UPDATE repos SET name = $2, updated_at = now() WHERE id = $1;
12
13 -- name: TransferRepoOwner :exec
14 -- Sets owner_user_id (or owner_org_id post-S31) on accept. The xor
15 -- check on the table enforces the shape. Also clears the other side
16 -- so a user→org transfer flips both columns atomically.
17 UPDATE repos
18 SET owner_user_id = sqlc.narg(owner_user_id)::bigint,
19 owner_org_id = sqlc.narg(owner_org_id)::bigint,
20 name = $2,
21 updated_at = now()
22 WHERE id = $1;
23
24 -- name: ArchiveRepo :exec
25 UPDATE repos SET is_archived = true, archived_at = now(), updated_at = now() WHERE id = $1;
26
27 -- name: UnarchiveRepo :exec
28 UPDATE repos SET is_archived = false, archived_at = NULL, updated_at = now() WHERE id = $1;
29
30 -- name: SetRepoVisibility :exec
31 UPDATE repos SET visibility = $2, updated_at = now() WHERE id = $1;
32
33 -- name: SoftDeleteRepoLifecycle :exec
34 -- Distinct name from S11's SoftDeleteRepo so future code that wants to
35 -- preserve the lifecycle audit-emission shape can find this one.
36 UPDATE repos SET deleted_at = now(), updated_at = now() WHERE id = $1;
37
38 -- name: RestoreRepo :exec
39 UPDATE repos SET deleted_at = NULL, updated_at = now() WHERE id = $1;
40
41 -- name: HardDeleteRepo :exec
42 DELETE FROM repos WHERE id = $1;
43
44
45 -- ─── redirects ─────────────────────────────────────────────────────────
46
47 -- name: InsertRepoRedirect :exec
48 -- Both old-owner FKs are nullable; pass exactly one. The CHECK
49 -- constraint on the table enforces the xor shape.
50 INSERT INTO repo_redirects (old_owner_user_id, old_owner_org_id, old_name, repo_id)
51 VALUES (sqlc.narg(old_owner_user_id)::bigint, sqlc.narg(old_owner_org_id)::bigint, $1, $2)
52 ON CONFLICT DO NOTHING;
53
54 -- name: LookupRedirectByUserOwner :one
55 -- Returns the current repo_id when (old_owner_user_id, old_name) hits
56 -- a redirect row.
57 SELECT repo_id FROM repo_redirects
58 WHERE old_owner_user_id = $1 AND old_name = $2;
59
60 -- name: DeleteRedirectsForRepo :exec
61 -- Used by hard-delete: drop the redirect rows pointing at this repo
62 -- (they would dangle once the repos row is gone; the FK ON DELETE
63 -- CASCADE would handle it, but explicit is auditable).
64 DELETE FROM repo_redirects WHERE repo_id = $1;
65
66 -- name: DeleteRedirectByUserOwnerOldName :exec
67 -- Used by the rename compensator: drop a single redirect row when
68 -- the rename has to be rolled back due to a filesystem failure. We
69 -- avoided raw SQL here at the audit's request (S00-S25, M).
70 DELETE FROM repo_redirects
71 WHERE repo_id = $1 AND old_owner_user_id = $2 AND old_name = $3;
72
73
74 -- ─── transfer requests ─────────────────────────────────────────────────
75
76 -- name: InsertTransferRequest :one
77 INSERT INTO repo_transfer_requests (
78 repo_id, from_user_id, to_principal_kind, to_principal_id,
79 created_by, expires_at
80 ) VALUES (
81 $1, $2, $3, $4, $5, $6
82 )
83 RETURNING id, repo_id, from_user_id, to_principal_kind, to_principal_id,
84 created_by, created_at, expires_at, status,
85 accepted_at, declined_at, canceled_at;
86
87 -- name: GetTransferRequest :one
88 SELECT id, repo_id, from_user_id, to_principal_kind, to_principal_id,
89 created_by, created_at, expires_at, status,
90 accepted_at, declined_at, canceled_at
91 FROM repo_transfer_requests
92 WHERE id = $1;
93
94 -- name: ListPendingTransfersForUser :many
95 -- Inbox view: pending offers a user can act on.
96 SELECT id, repo_id, from_user_id, to_principal_kind, to_principal_id,
97 created_by, created_at, expires_at, status,
98 accepted_at, declined_at, canceled_at
99 FROM repo_transfer_requests
100 WHERE to_principal_kind = 'user' AND to_principal_id = $1 AND status = 'pending'
101 ORDER BY created_at DESC;
102
103 -- name: ListTransfersForRepo :many
104 -- Sender / repo-settings view.
105 SELECT id, repo_id, from_user_id, to_principal_kind, to_principal_id,
106 created_by, created_at, expires_at, status,
107 accepted_at, declined_at, canceled_at
108 FROM repo_transfer_requests
109 WHERE repo_id = $1
110 ORDER BY created_at DESC;
111
112 -- name: AcceptTransferRequest :exec
113 UPDATE repo_transfer_requests
114 SET status = 'accepted', accepted_at = now()
115 WHERE id = $1 AND status = 'pending';
116
117 -- name: DeclineTransferRequest :exec
118 UPDATE repo_transfer_requests
119 SET status = 'declined', declined_at = now()
120 WHERE id = $1 AND status = 'pending';
121
122 -- name: CancelTransferRequest :exec
123 UPDATE repo_transfer_requests
124 SET status = 'canceled', canceled_at = now()
125 WHERE id = $1 AND status = 'pending';
126
127 -- name: ExpirePendingTransfers :execrows
128 -- Called by the periodic worker (transfers:expire) — flips pending
129 -- offers past their expires_at to the expired terminal state.
130 UPDATE repo_transfer_requests
131 SET status = 'expired'
132 WHERE status = 'pending' AND expires_at < now();
133
134
135 -- ─── soft-delete sweep query ───────────────────────────────────────────
136
137 -- name: ListRepoIDsPastSoftDeleteGrace :many
138 -- The repo:hard_delete enqueuer queries this to find rows ready for
139 -- destruction. The 7-day grace is hard-coded here; if we add a config
140 -- knob later, change this to a parameter.
141 SELECT id FROM repos
142 WHERE deleted_at IS NOT NULL AND deleted_at < now() - interval '7 days'
143 ORDER BY deleted_at ASC;
144
145 -- name: ListSoftDeletedReposForOwner :many
146 -- /settings/repositories/restore page lists these.
147 SELECT id, owner_user_id, name, deleted_at
148 FROM repos
149 WHERE owner_user_id = $1 AND deleted_at IS NOT NULL
150 ORDER BY deleted_at DESC;
151
152
153 -- ─── rename rate limit support ─────────────────────────────────────────
154
155 -- name: CountRecentRedirectsForRepo :one
156 -- Used to enforce the 5-per-30-days rename rate limit. The redirect
157 -- row is the audit trail for renames; counting them per repo gives a
158 -- reliable cap.
159 SELECT count(*)::int AS recent_count
160 FROM repo_redirects
161 WHERE repo_id = $1 AND redirected_at > now() - interval '30 days';
162
163
164 -- ─── fork-anchor cleanup on hard delete ────────────────────────────────
165
166 -- name: OrphanForksOf :execrows
167 -- Children pointing at this repo lose their fork-of pointer. Mirrors
168 -- GitHub's behavior when an upstream is deleted.
169 UPDATE repos SET fork_of_repo_id = NULL WHERE fork_of_repo_id = $1;
170