Openhub
Sign in Sign up
samjeston / openhub Public
main
Switch branches/tags
Branches
main
Sam Jeston Working vertical 9aa3c45 · History
768 lines· 20.9 KB ·Rust
Raw
1//! SQLite persistence: connection setup, row types and queries.
2
3use std::path::Path;
4use std::str::FromStr;
5use std::time::Duration;
6
7use sqlx::sqlite::{SqliteConnectOptions, SqliteJournalMode, SqlitePoolOptions, SqliteSynchronous};
8use sqlx::{FromRow, SqlitePool};
9
10pub type Db = SqlitePool;
11type Result<T> = std::result::Result<T, sqlx::Error>;
12
13pub fn now() -> i64 {
14 std::time::SystemTime::now()
15 .duration_since(std::time::UNIX_EPOCH)
16 .map(|d| d.as_secs() as i64)
17 .unwrap_or(0)
18}
19
20pub async fn connect(path: &Path) -> anyhow::Result<Db> {
21 let opts = SqliteConnectOptions::from_str(&format!("sqlite://{}", path.display()))?
22 .create_if_missing(true)
23 .journal_mode(SqliteJournalMode::Wal)
24 .synchronous(SqliteSynchronous::Normal)
25 .foreign_keys(true)
26 .busy_timeout(Duration::from_secs(5));
27 let pool = SqlitePoolOptions::new()
28 .max_connections(8)
29 .connect_with(opts)
30 .await?;
31 sqlx::migrate!("./migrations").run(&pool).await?;
32 Ok(pool)
33}
34
35// ---------------------------------------------------------------------------
36// Users & sessions
37// ---------------------------------------------------------------------------
38
39#[derive(Debug, Clone, FromRow)]
40pub struct User {
41 pub id: i64,
42 pub username: String,
43 pub email: String,
44 pub display_name: String,
45 pub bio: String,
46 pub password_hash: String,
47 pub is_admin: bool,
48 pub created_at: i64,
49}
50
51impl User {
52 /// Display name if set, otherwise the username.
53 pub fn name(&self) -> &str {
54 if self.display_name.trim().is_empty() {
55 &self.username
56 } else {
57 &self.display_name
58 }
59 }
60}
61
62const USER_COLS: &str =
63 "id, username, email, display_name, bio, password_hash, is_admin, created_at";
64
65pub async fn count_users(db: &Db) -> Result<i64> {
66 sqlx::query_scalar("SELECT COUNT(*) FROM users")
67 .fetch_one(db)
68 .await
69}
70
71pub async fn create_user(
72 db: &Db,
73 username: &str,
74 email: &str,
75 password_hash: &str,
76 is_admin: bool,
77) -> Result<i64> {
78 let res = sqlx::query(
79 "INSERT INTO users (username, email, password_hash, is_admin, created_at) VALUES (?, ?, ?, ?, ?)",
80 )
81 .bind(username)
82 .bind(email)
83 .bind(password_hash)
84 .bind(is_admin)
85 .bind(now())
86 .execute(db)
87 .await?;
88 Ok(res.last_insert_rowid())
89}
90
91pub async fn user_by_id(db: &Db, id: i64) -> Result<Option<User>> {
92 sqlx::query_as(&format!("SELECT {USER_COLS} FROM users WHERE id = ?"))
93 .bind(id)
94 .fetch_optional(db)
95 .await
96}
97
98pub async fn user_by_username(db: &Db, username: &str) -> Result<Option<User>> {
99 sqlx::query_as(&format!("SELECT {USER_COLS} FROM users WHERE username = ?"))
100 .bind(username)
101 .fetch_optional(db)
102 .await
103}
104
105pub async fn user_by_login(db: &Db, login: &str) -> Result<Option<User>> {
106 sqlx::query_as(&format!(
107 "SELECT {USER_COLS} FROM users WHERE username = ?1 OR email = ?1"
108 ))
109 .bind(login)
110 .fetch_optional(db)
111 .await
112}
113
114pub async fn email_taken(db: &Db, email: &str, except_user: i64) -> Result<bool> {
115 let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM users WHERE email = ? AND id != ?")
116 .bind(email)
117 .bind(except_user)
118 .fetch_one(db)
119 .await?;
120 Ok(n > 0)
121}
122
123pub async fn update_profile(
124 db: &Db,
125 id: i64,
126 display_name: &str,
127 email: &str,
128 bio: &str,
129) -> Result<()> {
130 sqlx::query("UPDATE users SET display_name = ?, email = ?, bio = ? WHERE id = ?")
131 .bind(display_name)
132 .bind(email)
133 .bind(bio)
134 .bind(id)
135 .execute(db)
136 .await?;
137 Ok(())
138}
139
140pub async fn update_password(db: &Db, id: i64, password_hash: &str) -> Result<()> {
141 sqlx::query("UPDATE users SET password_hash = ? WHERE id = ?")
142 .bind(password_hash)
143 .bind(id)
144 .execute(db)
145 .await?;
146 Ok(())
147}
148
149pub async fn create_session(db: &Db, token_hash: &str, user_id: i64, ttl_secs: i64) -> Result<()> {
150 let now = now();
151 sqlx::query(
152 "INSERT INTO sessions (token_hash, user_id, created_at, expires_at) VALUES (?, ?, ?, ?)",
153 )
154 .bind(token_hash)
155 .bind(user_id)
156 .bind(now)
157 .bind(now + ttl_secs)
158 .execute(db)
159 .await?;
160 Ok(())
161}
162
163pub async fn user_by_session(db: &Db, token_hash: &str) -> Result<Option<User>> {
164 sqlx::query_as(&format!(
165 "SELECT {} FROM users u JOIN sessions s ON s.user_id = u.id \
166 WHERE s.token_hash = ? AND s.expires_at > ?",
167 USER_COLS
168 .split(", ")
169 .map(|c| format!("u.{c}"))
170 .collect::<Vec<_>>()
171 .join(", ")
172 ))
173 .bind(token_hash)
174 .bind(now())
175 .fetch_optional(db)
176 .await
177}
178
179pub async fn delete_session(db: &Db, token_hash: &str) -> Result<()> {
180 sqlx::query("DELETE FROM sessions WHERE token_hash = ?")
181 .bind(token_hash)
182 .execute(db)
183 .await?;
184 Ok(())
185}
186
187pub async fn delete_other_sessions(db: &Db, user_id: i64, keep_hash: &str) -> Result<()> {
188 sqlx::query("DELETE FROM sessions WHERE user_id = ? AND token_hash != ?")
189 .bind(user_id)
190 .bind(keep_hash)
191 .execute(db)
192 .await?;
193 Ok(())
194}
195
196pub async fn purge_expired_sessions(db: &Db) -> Result<()> {
197 sqlx::query("DELETE FROM sessions WHERE expires_at <= ?")
198 .bind(now())
199 .execute(db)
200 .await?;
201 Ok(())
202}
203
204// ---------------------------------------------------------------------------
205// SSH keys
206// ---------------------------------------------------------------------------
207
208#[derive(Debug, Clone, FromRow)]
209pub struct SshKey {
210 pub id: i64,
211 pub title: String,
212 pub fingerprint: String,
213 pub key_type: String,
214 pub created_at: i64,
215 pub last_used_at: Option<i64>,
216}
217
218pub async fn list_keys(db: &Db, user_id: i64) -> Result<Vec<SshKey>> {
219 sqlx::query_as(
220 "SELECT id, title, fingerprint, key_type, created_at, last_used_at FROM ssh_keys \
221 WHERE user_id = ? ORDER BY created_at",
222 )
223 .bind(user_id)
224 .fetch_all(db)
225 .await
226}
227
228pub async fn fingerprint_exists(db: &Db, fingerprint: &str) -> Result<bool> {
229 let n: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM ssh_keys WHERE fingerprint = ?")
230 .bind(fingerprint)
231 .fetch_one(db)
232 .await?;
233 Ok(n > 0)
234}
235
236pub async fn add_key(
237 db: &Db,
238 user_id: i64,
239 title: &str,
240 fingerprint: &str,
241 public_key: &str,
242 key_type: &str,
243) -> Result<()> {
244 sqlx::query(
245 "INSERT INTO ssh_keys (user_id, title, fingerprint, public_key, key_type, created_at) \
246 VALUES (?, ?, ?, ?, ?, ?)",
247 )
248 .bind(user_id)
249 .bind(title)
250 .bind(fingerprint)
251 .bind(public_key)
252 .bind(key_type)
253 .bind(now())
254 .execute(db)
255 .await?;
256 Ok(())
257}
258
259pub async fn delete_key(db: &Db, id: i64, user_id: i64) -> Result<bool> {
260 let res = sqlx::query("DELETE FROM ssh_keys WHERE id = ? AND user_id = ?")
261 .bind(id)
262 .bind(user_id)
263 .execute(db)
264 .await?;
265 Ok(res.rows_affected() > 0)
266}
267
268/// Finds the owner of a public key by its SHA256 fingerprint.
269pub async fn user_by_key_fingerprint(db: &Db, fingerprint: &str) -> Result<Option<(User, i64)>> {
270 let Some((user_id, key_id)): Option<(i64, i64)> =
271 sqlx::query_as("SELECT user_id, id FROM ssh_keys WHERE fingerprint = ?")
272 .bind(fingerprint)
273 .fetch_optional(db)
274 .await?
275 else {
276 return Ok(None);
277 };
278 Ok(user_by_id(db, user_id).await?.map(|u| (u, key_id)))
279}
280
281pub async fn touch_key(db: &Db, key_id: i64) -> Result<()> {
282 sqlx::query("UPDATE ssh_keys SET last_used_at = ? WHERE id = ?")
283 .bind(now())
284 .bind(key_id)
285 .execute(db)
286 .await?;
287 Ok(())
288}
289
290// ---------------------------------------------------------------------------
291// Repositories
292// ---------------------------------------------------------------------------
293
294#[derive(Debug, Clone, FromRow)]
295pub struct Repo {
296 pub id: i64,
297 pub owner_id: i64,
298 pub owner: String,
299 pub name: String,
300 pub description: String,
301 pub is_private: bool,
302 pub default_branch: String,
303 pub created_at: i64,
304 pub pushed_at: Option<i64>,
305}
306
307const REPO_SELECT: &str = "SELECT r.id, r.owner_id, u.username AS owner, r.name, r.description, \
308 r.is_private, r.default_branch, r.created_at, r.pushed_at \
309 FROM repositories r JOIN users u ON u.id = r.owner_id";
310
311pub async fn create_repo(
312 db: &Db,
313 owner_id: i64,
314 name: &str,
315 description: &str,
316 is_private: bool,
317 default_branch: &str,
318) -> Result<i64> {
319 let now = now();
320 let res = sqlx::query(
321 "INSERT INTO repositories (owner_id, name, description, is_private, default_branch, created_at, updated_at) \
322 VALUES (?, ?, ?, ?, ?, ?, ?)",
323 )
324 .bind(owner_id)
325 .bind(name)
326 .bind(description)
327 .bind(is_private)
328 .bind(default_branch)
329 .bind(now)
330 .bind(now)
331 .execute(db)
332 .await?;
333 Ok(res.last_insert_rowid())
334}
335
336pub async fn repo_by_name(db: &Db, owner: &str, name: &str) -> Result<Option<Repo>> {
337 sqlx::query_as(&format!(
338 "{REPO_SELECT} WHERE u.username = ? AND r.name = ?"
339 ))
340 .bind(owner)
341 .bind(name)
342 .fetch_optional(db)
343 .await
344}
345
346pub async fn repo_name_taken(db: &Db, owner_id: i64, name: &str, except: i64) -> Result<bool> {
347 let n: i64 = sqlx::query_scalar(
348 "SELECT COUNT(*) FROM repositories WHERE owner_id = ? AND name = ? AND id != ?",
349 )
350 .bind(owner_id)
351 .bind(name)
352 .bind(except)
353 .fetch_one(db)
354 .await?;
355 Ok(n > 0)
356}
357
358pub async fn list_public_repos(db: &Db, limit: i64) -> Result<Vec<Repo>> {
359 sqlx::query_as(&format!(
360 "{REPO_SELECT} WHERE r.is_private = 0 ORDER BY COALESCE(r.pushed_at, r.updated_at) DESC LIMIT ?"
361 ))
362 .bind(limit)
363 .fetch_all(db)
364 .await
365}
366
367/// Repositories owned by `owner_id` that `viewer` is allowed to see.
368pub async fn list_owner_repos(db: &Db, owner_id: i64, viewer: Option<&User>) -> Result<Vec<Repo>> {
369 let (viewer_id, admin) = viewer.map(|v| (v.id, v.is_admin)).unwrap_or((-1, false));
370 sqlx::query_as(&format!(
371 "{REPO_SELECT} WHERE r.owner_id = ?1 AND (r.is_private = 0 OR ?2 OR r.owner_id = ?3 \
372 OR EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = r.id AND c.user_id = ?3)) \
373 ORDER BY COALESCE(r.pushed_at, r.updated_at) DESC"
374 ))
375 .bind(owner_id)
376 .bind(admin)
377 .bind(viewer_id)
378 .fetch_all(db)
379 .await
380}
381
382/// Repositories a user owns or collaborates on.
383pub async fn list_member_repos(db: &Db, user_id: i64) -> Result<Vec<Repo>> {
384 sqlx::query_as(&format!(
385 "{REPO_SELECT} WHERE r.owner_id = ?1 \
386 OR EXISTS (SELECT 1 FROM collaborators c WHERE c.repo_id = r.id AND c.user_id = ?1) \
387 ORDER BY COALESCE(r.pushed_at, r.updated_at) DESC"
388 ))
389 .bind(user_id)
390 .fetch_all(db)
391 .await
392}
393
394pub async fn update_repo(
395 db: &Db,
396 id: i64,
397 name: &str,
398 description: &str,
399 is_private: bool,
400 default_branch: &str,
401) -> Result<()> {
402 sqlx::query(
403 "UPDATE repositories SET name = ?, description = ?, is_private = ?, default_branch = ?, updated_at = ? WHERE id = ?",
404 )
405 .bind(name)
406 .bind(description)
407 .bind(is_private)
408 .bind(default_branch)
409 .bind(now())
410 .bind(id)
411 .execute(db)
412 .await?;
413 Ok(())
414}
415
416pub async fn set_default_branch(db: &Db, id: i64, branch: &str) -> Result<()> {
417 sqlx::query("UPDATE repositories SET default_branch = ? WHERE id = ?")
418 .bind(branch)
419 .bind(id)
420 .execute(db)
421 .await?;
422 Ok(())
423}
424
425pub async fn touch_pushed(db: &Db, id: i64) -> Result<()> {
426 let now = now();
427 sqlx::query("UPDATE repositories SET pushed_at = ?, updated_at = ? WHERE id = ?")
428 .bind(now)
429 .bind(now)
430 .bind(id)
431 .execute(db)
432 .await?;
433 Ok(())
434}
435
436pub async fn delete_repo(db: &Db, id: i64) -> Result<()> {
437 sqlx::query("DELETE FROM repositories WHERE id = ?")
438 .bind(id)
439 .execute(db)
440 .await?;
441 Ok(())
442}
443
444pub async fn is_collaborator(db: &Db, repo_id: i64, user_id: i64) -> Result<bool> {
445 let n: i64 =
446 sqlx::query_scalar("SELECT COUNT(*) FROM collaborators WHERE repo_id = ? AND user_id = ?")
447 .bind(repo_id)
448 .bind(user_id)
449 .fetch_one(db)
450 .await?;
451 Ok(n > 0)
452}
453
454pub async fn list_collaborators(db: &Db, repo_id: i64) -> Result<Vec<User>> {
455 sqlx::query_as(
456 "SELECT u.id, u.username, u.email, u.display_name, u.bio, u.password_hash, u.is_admin, u.created_at \
457 FROM users u JOIN collaborators c ON c.user_id = u.id WHERE c.repo_id = ? ORDER BY u.username",
458 )
459 .bind(repo_id)
460 .fetch_all(db)
461 .await
462}
463
464pub async fn add_collaborator(db: &Db, repo_id: i64, user_id: i64) -> Result<()> {
465 sqlx::query(
466 "INSERT OR IGNORE INTO collaborators (repo_id, user_id, created_at) VALUES (?, ?, ?)",
467 )
468 .bind(repo_id)
469 .bind(user_id)
470 .bind(now())
471 .execute(db)
472 .await?;
473 Ok(())
474}
475
476pub async fn remove_collaborator(db: &Db, repo_id: i64, user_id: i64) -> Result<()> {
477 sqlx::query("DELETE FROM collaborators WHERE repo_id = ? AND user_id = ?")
478 .bind(repo_id)
479 .bind(user_id)
480 .execute(db)
481 .await?;
482 Ok(())
483}
484
485// ---------------------------------------------------------------------------
486// Pull requests
487// ---------------------------------------------------------------------------
488
489#[derive(Debug, Clone, FromRow)]
490pub struct PullRequest {
491 pub id: i64,
492 pub repo_id: i64,
493 pub number: i64,
494 pub title: String,
495 pub body: String,
496 pub author_id: i64,
497 pub author: String,
498 pub base_branch: String,
499 pub head_branch: String,
500 pub state: String,
501 pub head_sha: Option<String>,
502 pub base_sha: Option<String>,
503 pub merge_commit_sha: Option<String>,
504 pub merged_by: Option<String>,
505 pub merged_at: Option<i64>,
506 pub closed_at: Option<i64>,
507 pub created_at: i64,
508 pub comments: i64,
509}
510
511impl PullRequest {
512 pub fn is_open(&self) -> bool {
513 self.state == "open"
514 }
515}
516
517const PULL_SELECT: &str = "SELECT p.id, p.repo_id, p.number, p.title, p.body, p.author_id, \
518 a.username AS author, p.base_branch, p.head_branch, p.state, p.head_sha, p.base_sha, \
519 p.merge_commit_sha, m.username AS merged_by, p.merged_at, p.closed_at, p.created_at, \
520 (SELECT COUNT(*) FROM pr_events e WHERE e.pr_id = p.id AND e.kind = 'comment') AS comments \
521 FROM pull_requests p JOIN users a ON a.id = p.author_id LEFT JOIN users m ON m.id = p.merged_by";
522
523#[allow(clippy::too_many_arguments)]
524pub async fn create_pull(
525 db: &Db,
526 repo_id: i64,
527 author_id: i64,
528 title: &str,
529 body: &str,
530 base: &str,
531 head: &str,
532 head_sha: &str,
533 base_sha: &str,
534) -> Result<i64> {
535 let mut tx = db.begin().await?;
536 let number: i64 = sqlx::query_scalar(
537 "SELECT COALESCE(MAX(number), 0) + 1 FROM pull_requests WHERE repo_id = ?",
538 )
539 .bind(repo_id)
540 .fetch_one(&mut *tx)
541 .await?;
542 let now = now();
543 sqlx::query(
544 "INSERT INTO pull_requests (repo_id, number, title, body, author_id, base_branch, head_branch, head_sha, base_sha, created_at, updated_at) \
545 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
546 )
547 .bind(repo_id)
548 .bind(number)
549 .bind(title)
550 .bind(body)
551 .bind(author_id)
552 .bind(base)
553 .bind(head)
554 .bind(head_sha)
555 .bind(base_sha)
556 .bind(now)
557 .bind(now)
558 .execute(&mut *tx)
559 .await?;
560 tx.commit().await?;
561 Ok(number)
562}
563
564pub async fn pull_by_number(db: &Db, repo_id: i64, number: i64) -> Result<Option<PullRequest>> {
565 sqlx::query_as(&format!(
566 "{PULL_SELECT} WHERE p.repo_id = ? AND p.number = ?"
567 ))
568 .bind(repo_id)
569 .bind(number)
570 .fetch_optional(db)
571 .await
572}
573
574/// `open = true` lists open pull requests, otherwise closed and merged ones.
575pub async fn list_pulls(db: &Db, repo_id: i64, open: bool) -> Result<Vec<PullRequest>> {
576 let cond = if open {
577 "p.state = 'open'"
578 } else {
579 "p.state != 'open'"
580 };
581 sqlx::query_as(&format!(
582 "{PULL_SELECT} WHERE p.repo_id = ? AND {cond} ORDER BY p.number DESC"
583 ))
584 .bind(repo_id)
585 .fetch_all(db)
586 .await
587}
588
589pub async fn count_pulls(db: &Db, repo_id: i64) -> Result<(i64, i64)> {
590 sqlx::query_as(
591 "SELECT COALESCE(SUM(state = 'open'), 0), COALESCE(SUM(state != 'open'), 0) \
592 FROM pull_requests WHERE repo_id = ?",
593 )
594 .bind(repo_id)
595 .fetch_one(db)
596 .await
597}
598
599pub async fn find_open_pull(db: &Db, repo_id: i64, base: &str, head: &str) -> Result<Option<i64>> {
600 sqlx::query_scalar(
601 "SELECT number FROM pull_requests WHERE repo_id = ? AND base_branch = ? AND head_branch = ? AND state = 'open'",
602 )
603 .bind(repo_id)
604 .bind(base)
605 .bind(head)
606 .fetch_optional(db)
607 .await
608}
609
610pub async fn find_open_pull_by_head(db: &Db, repo_id: i64, head: &str) -> Result<Option<i64>> {
611 sqlx::query_scalar(
612 "SELECT number FROM pull_requests WHERE repo_id = ? AND head_branch = ? AND state = 'open'",
613 )
614 .bind(repo_id)
615 .bind(head)
616 .fetch_optional(db)
617 .await
618}
619
620/// Open pull requests in repositories the user can push to, or that they authored.
621pub async fn dashboard_pulls(db: &Db, user_id: i64) -> Result<Vec<(PullRequest, String, String)>> {
622 let pulls: Vec<PullRequest> = sqlx::query_as(&format!(
623 "{PULL_SELECT} JOIN repositories r ON r.id = p.repo_id WHERE p.state = 'open' AND \
624 (p.author_id = ?1 OR r.owner_id = ?1 OR EXISTS \
625 (SELECT 1 FROM collaborators c WHERE c.repo_id = r.id AND c.user_id = ?1)) \
626 ORDER BY p.updated_at DESC LIMIT 20"
627 ))
628 .bind(user_id)
629 .fetch_all(db)
630 .await?;
631 let mut out = Vec::with_capacity(pulls.len());
632 for p in pulls {
633 let (owner, name): (String, String) = sqlx::query_as(
634 "SELECT u.username, r.name FROM repositories r JOIN users u ON u.id = r.owner_id WHERE r.id = ?",
635 )
636 .bind(p.repo_id)
637 .fetch_one(db)
638 .await?;
639 out.push((p, owner, name));
640 }
641 Ok(out)
642}
643
644pub async fn update_pull_head(
645 db: &Db,
646 id: i64,
647 head_sha: &str,
648 base_sha: Option<&str>,
649) -> Result<()> {
650 sqlx::query("UPDATE pull_requests SET head_sha = ?, base_sha = ?, updated_at = ? WHERE id = ?")
651 .bind(head_sha)
652 .bind(base_sha)
653 .bind(now())
654 .bind(id)
655 .execute(db)
656 .await?;
657 Ok(())
658}
659
660pub async fn update_pull_text(db: &Db, id: i64, title: &str, body: &str) -> Result<()> {
661 sqlx::query("UPDATE pull_requests SET title = ?, body = ?, updated_at = ? WHERE id = ?")
662 .bind(title)
663 .bind(body)
664 .bind(now())
665 .bind(id)
666 .execute(db)
667 .await?;
668 Ok(())
669}
670
671pub async fn mark_merged(
672 db: &Db,
673 id: i64,
674 merged_by: Option<i64>,
675 merge_commit_sha: Option<&str>,
676 head_sha: &str,
677 base_sha: &str,
678) -> Result<()> {
679 let now = now();
680 sqlx::query(
681 "UPDATE pull_requests SET state = 'merged', merged_by = ?, merged_at = ?, closed_at = ?, \
682 merge_commit_sha = ?, head_sha = ?, base_sha = ?, updated_at = ? WHERE id = ?",
683 )
684 .bind(merged_by)
685 .bind(now)
686 .bind(now)
687 .bind(merge_commit_sha)
688 .bind(head_sha)
689 .bind(base_sha)
690 .bind(now)
691 .bind(id)
692 .execute(db)
693 .await?;
694 Ok(())
695}
696
697pub async fn mark_closed(db: &Db, id: i64, head_sha: &str, base_sha: &str) -> Result<()> {
698 let now = now();
699 sqlx::query(
700 "UPDATE pull_requests SET state = 'closed', closed_at = ?, head_sha = ?, base_sha = ?, updated_at = ? WHERE id = ?",
701 )
702 .bind(now)
703 .bind(head_sha)
704 .bind(base_sha)
705 .bind(now)
706 .bind(id)
707 .execute(db)
708 .await?;
709 Ok(())
710}
711
712pub async fn mark_reopened(db: &Db, id: i64, head_sha: &str, base_sha: &str) -> Result<()> {
713 sqlx::query(
714 "UPDATE pull_requests SET state = 'open', closed_at = NULL, head_sha = ?, base_sha = ?, updated_at = ? WHERE id = ?",
715 )
716 .bind(head_sha)
717 .bind(base_sha)
718 .bind(now())
719 .bind(id)
720 .execute(db)
721 .await?;
722 Ok(())
723}
724
725#[derive(Debug, Clone, FromRow)]
726pub struct PrEvent {
727 pub id: i64,
728 pub author: String,
729 pub kind: String,
730 pub body: String,
731 pub created_at: i64,
732}
733
734pub async fn add_event(
735 db: &Db,
736 pr_id: i64,
737 author_id: Option<i64>,
738 kind: &str,
739 body: &str,
740) -> Result<()> {
741 let now = now();
742 sqlx::query(
743 "INSERT INTO pr_events (pr_id, author_id, kind, body, created_at) VALUES (?, ?, ?, ?, ?)",
744 )
745 .bind(pr_id)
746 .bind(author_id)
747 .bind(kind)
748 .bind(body)
749 .bind(now)
750 .execute(db)
751 .await?;
752 sqlx::query("UPDATE pull_requests SET updated_at = ? WHERE id = ?")
753 .bind(now)
754 .bind(pr_id)
755 .execute(db)
756 .await?;
757 Ok(())
758}
759
760pub async fn list_events(db: &Db, pr_id: i64) -> Result<Vec<PrEvent>> {
761 sqlx::query_as(
762 "SELECT e.id, COALESCE(u.username, '') AS author, e.kind, e.body, e.created_at FROM pr_events e \
763 LEFT JOIN users u ON u.id = e.author_id WHERE e.pr_id = ? ORDER BY e.created_at, e.id",
764 )
765 .bind(pr_id)
766 .fetch_all(db)
767 .await
768}