shithub Public

Watch 1 Fork 0 Star 0
MySQL · 6597 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
67 -- ─── transfer requests ─────────────────────────────────────────────────
68
69 -- name: InsertTransferRequest :one
70 INSERT INTO repo_transfer_requests (
71 repo_id, from_user_id, to_principal_kind, to_principal_id,
72 created_by, expires_at
73 ) VALUES (
74 $1, $2, $3, $4, $5, $6
75 )
76 RETURNING id, repo_id, from_user_id, to_principal_kind, to_principal_id,
77 created_by, created_at, expires_at, status,
78 accepted_at, declined_at, canceled_at;
79
80 -- name: GetTransferRequest :one
81 SELECT id, repo_id, from_user_id, to_principal_kind, to_principal_id,
82 created_by, created_at, expires_at, status,
83 accepted_at, declined_at, canceled_at
84 FROM repo_transfer_requests
85 WHERE id = $1;
86
87 -- name: ListPendingTransfersForUser :many
88 -- Inbox view: pending offers a user can act on.
89 SELECT id, repo_id, from_user_id, to_principal_kind, to_principal_id,
90 created_by, created_at, expires_at, status,
91 accepted_at, declined_at, canceled_at
92 FROM repo_transfer_requests
93 WHERE to_principal_kind = 'user' AND to_principal_id = $1 AND status = 'pending'
94 ORDER BY created_at DESC;
95
96 -- name: ListTransfersForRepo :many
97 -- Sender / repo-settings view.
98 SELECT id, repo_id, from_user_id, to_principal_kind, to_principal_id,
99 created_by, created_at, expires_at, status,
100 accepted_at, declined_at, canceled_at
101 FROM repo_transfer_requests
102 WHERE repo_id = $1
103 ORDER BY created_at DESC;
104
105 -- name: AcceptTransferRequest :exec
106 UPDATE repo_transfer_requests
107 SET status = 'accepted', accepted_at = now()
108 WHERE id = $1 AND status = 'pending';
109
110 -- name: DeclineTransferRequest :exec
111 UPDATE repo_transfer_requests
112 SET status = 'declined', declined_at = now()
113 WHERE id = $1 AND status = 'pending';
114
115 -- name: CancelTransferRequest :exec
116 UPDATE repo_transfer_requests
117 SET status = 'canceled', canceled_at = now()
118 WHERE id = $1 AND status = 'pending';
119
120 -- name: ExpirePendingTransfers :execrows
121 -- Called by the periodic worker (transfers:expire) — flips pending
122 -- offers past their expires_at to the expired terminal state.
123 UPDATE repo_transfer_requests
124 SET status = 'expired'
125 WHERE status = 'pending' AND expires_at < now();
126
127
128 -- ─── soft-delete sweep query ───────────────────────────────────────────
129
130 -- name: ListRepoIDsPastSoftDeleteGrace :many
131 -- The repo:hard_delete enqueuer queries this to find rows ready for
132 -- destruction. The 7-day grace is hard-coded here; if we add a config
133 -- knob later, change this to a parameter.
134 SELECT id FROM repos
135 WHERE deleted_at IS NOT NULL AND deleted_at < now() - interval '7 days'
136 ORDER BY deleted_at ASC;
137
138 -- name: ListSoftDeletedReposForOwner :many
139 -- /settings/repositories/restore page lists these.
140 SELECT id, owner_user_id, name, deleted_at
141 FROM repos
142 WHERE owner_user_id = $1 AND deleted_at IS NOT NULL
143 ORDER BY deleted_at DESC;
144
145
146 -- ─── rename rate limit support ─────────────────────────────────────────
147
148 -- name: CountRecentRedirectsForRepo :one
149 -- Used to enforce the 5-per-30-days rename rate limit. The redirect
150 -- row is the audit trail for renames; counting them per repo gives a
151 -- reliable cap.
152 SELECT count(*)::int AS recent_count
153 FROM repo_redirects
154 WHERE repo_id = $1 AND redirected_at > now() - interval '30 days';
155
156
157 -- ─── fork-anchor cleanup on hard delete ────────────────────────────────
158
159 -- name: OrphanForksOf :execrows
160 -- Children pointing at this repo lose their fork-of pointer. Mirrors
161 -- GitHub's behavior when an upstream is deleted.
162 UPDATE repos SET fork_of_repo_id = NULL WHERE fork_of_repo_id = $1;
163