veryroundbird.house
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "slices"
8 "strings"
9 "time"
10
11 "github.com/bluesky-social/indigo/atproto/syntax"
12 securejoin "github.com/cyphar/filepath-securejoin"
13 "tangled.sh/tangled.sh/core/api/tangled"
14)
15
16type Repo struct {
17 Did string
18 Name string
19 Knot string
20 Rkey string
21 Created time.Time
22 Description string
23 Spindle string
24
25 // optionally, populate this when querying for reverse mappings
26 RepoStats *RepoStats
27
28 // optional
29 Source string
30}
31
32func (r Repo) RepoAt() syntax.ATURI {
33 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", r.Did, tangled.RepoNSID, r.Rkey))
34}
35
36func (r Repo) DidSlashRepo() string {
37 p, _ := securejoin.SecureJoin(r.Did, r.Name)
38 return p
39}
40
41func GetAllRepos(e Execer, limit int) ([]Repo, error) {
42 var repos []Repo
43
44 rows, err := e.Query(
45 `select did, name, knot, rkey, description, created, source
46 from repos
47 order by created desc
48 limit ?
49 `,
50 limit,
51 )
52 if err != nil {
53 return nil, err
54 }
55 defer rows.Close()
56
57 for rows.Next() {
58 var repo Repo
59 err := scanRepo(
60 rows, &repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &repo.Description, &repo.Created, &repo.Source,
61 )
62 if err != nil {
63 return nil, err
64 }
65 repos = append(repos, repo)
66 }
67
68 if err := rows.Err(); err != nil {
69 return nil, err
70 }
71
72 return repos, nil
73}
74
75func GetRepos(e Execer, limit int, filters ...filter) ([]Repo, error) {
76 repoMap := make(map[syntax.ATURI]*Repo)
77
78 var conditions []string
79 var args []any
80 for _, filter := range filters {
81 conditions = append(conditions, filter.Condition())
82 args = append(args, filter.Arg()...)
83 }
84
85 whereClause := ""
86 if conditions != nil {
87 whereClause = " where " + strings.Join(conditions, " and ")
88 }
89
90 limitClause := ""
91 if limit != 0 {
92 limitClause = fmt.Sprintf(" limit %d", limit)
93 }
94
95 repoQuery := fmt.Sprintf(
96 `select
97 did,
98 name,
99 knot,
100 rkey,
101 created,
102 description,
103 source,
104 spindle
105 from
106 repos r
107 %s
108 order by created desc
109 %s`,
110 whereClause,
111 limitClause,
112 )
113 rows, err := e.Query(repoQuery, args...)
114
115 if err != nil {
116 return nil, fmt.Errorf("failed to execute repo query: %w ", err)
117 }
118
119 for rows.Next() {
120 var repo Repo
121 var createdAt string
122 var description, source, spindle sql.NullString
123
124 err := rows.Scan(
125 &repo.Did,
126 &repo.Name,
127 &repo.Knot,
128 &repo.Rkey,
129 &createdAt,
130 &description,
131 &source,
132 &spindle,
133 )
134 if err != nil {
135 return nil, fmt.Errorf("failed to execute repo query: %w ", err)
136 }
137
138 if t, err := time.Parse(time.RFC3339, createdAt); err == nil {
139 repo.Created = t
140 }
141 if description.Valid {
142 repo.Description = description.String
143 }
144 if source.Valid {
145 repo.Source = source.String
146 }
147 if spindle.Valid {
148 repo.Spindle = spindle.String
149 }
150
151 repo.RepoStats = &RepoStats{}
152 repoMap[repo.RepoAt()] = &repo
153 }
154
155 if err = rows.Err(); err != nil {
156 return nil, fmt.Errorf("failed to execute repo query: %w ", err)
157 }
158
159 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(repoMap)), ", ")
160 args = make([]any, len(repoMap))
161
162 i := 0
163 for _, r := range repoMap {
164 args[i] = r.RepoAt()
165 i++
166 }
167
168 languageQuery := fmt.Sprintf(
169 `
170 select
171 repo_at, language
172 from
173 repo_languages r1
174 where
175 repo_at IN (%s)
176 and is_default_ref = 1
177 and id = (
178 select id
179 from repo_languages r2
180 where r2.repo_at = r1.repo_at
181 and r2.is_default_ref = 1
182 order by bytes desc
183 limit 1
184 );
185 `,
186 inClause,
187 )
188 rows, err = e.Query(languageQuery, args...)
189 if err != nil {
190 return nil, fmt.Errorf("failed to execute lang query: %w ", err)
191 }
192 for rows.Next() {
193 var repoat, lang string
194 if err := rows.Scan(&repoat, &lang); err != nil {
195 log.Println("err", "err", err)
196 continue
197 }
198 if r, ok := repoMap[syntax.ATURI(repoat)]; ok {
199 r.RepoStats.Language = lang
200 }
201 }
202 if err = rows.Err(); err != nil {
203 return nil, fmt.Errorf("failed to execute lang query: %w ", err)
204 }
205
206 starCountQuery := fmt.Sprintf(
207 `select
208 repo_at, count(1)
209 from stars
210 where repo_at in (%s)
211 group by repo_at`,
212 inClause,
213 )
214 rows, err = e.Query(starCountQuery, args...)
215 if err != nil {
216 return nil, fmt.Errorf("failed to execute star-count query: %w ", err)
217 }
218 for rows.Next() {
219 var repoat string
220 var count int
221 if err := rows.Scan(&repoat, &count); err != nil {
222 log.Println("err", "err", err)
223 continue
224 }
225 if r, ok := repoMap[syntax.ATURI(repoat)]; ok {
226 r.RepoStats.StarCount = count
227 }
228 }
229 if err = rows.Err(); err != nil {
230 return nil, fmt.Errorf("failed to execute star-count query: %w ", err)
231 }
232
233 issueCountQuery := fmt.Sprintf(
234 `select
235 repo_at,
236 count(case when open = 1 then 1 end) as open_count,
237 count(case when open = 0 then 1 end) as closed_count
238 from issues
239 where repo_at in (%s)
240 group by repo_at`,
241 inClause,
242 )
243 rows, err = e.Query(issueCountQuery, args...)
244 if err != nil {
245 return nil, fmt.Errorf("failed to execute issue-count query: %w ", err)
246 }
247 for rows.Next() {
248 var repoat string
249 var open, closed int
250 if err := rows.Scan(&repoat, &open, &closed); err != nil {
251 log.Println("err", "err", err)
252 continue
253 }
254 if r, ok := repoMap[syntax.ATURI(repoat)]; ok {
255 r.RepoStats.IssueCount.Open = open
256 r.RepoStats.IssueCount.Closed = closed
257 }
258 }
259 if err = rows.Err(); err != nil {
260 return nil, fmt.Errorf("failed to execute issue-count query: %w ", err)
261 }
262
263 pullCountQuery := fmt.Sprintf(
264 `select
265 repo_at,
266 count(case when state = ? then 1 end) as open_count,
267 count(case when state = ? then 1 end) as merged_count,
268 count(case when state = ? then 1 end) as closed_count,
269 count(case when state = ? then 1 end) as deleted_count
270 from pulls
271 where repo_at in (%s)
272 group by repo_at`,
273 inClause,
274 )
275 args = append([]any{
276 PullOpen,
277 PullMerged,
278 PullClosed,
279 PullDeleted,
280 }, args...)
281 rows, err = e.Query(
282 pullCountQuery,
283 args...,
284 )
285 if err != nil {
286 return nil, fmt.Errorf("failed to execute pulls-count query: %w ", err)
287 }
288 for rows.Next() {
289 var repoat string
290 var open, merged, closed, deleted int
291 if err := rows.Scan(&repoat, &open, &merged, &closed, &deleted); err != nil {
292 log.Println("err", "err", err)
293 continue
294 }
295 if r, ok := repoMap[syntax.ATURI(repoat)]; ok {
296 r.RepoStats.PullCount.Open = open
297 r.RepoStats.PullCount.Merged = merged
298 r.RepoStats.PullCount.Closed = closed
299 r.RepoStats.PullCount.Deleted = deleted
300 }
301 }
302 if err = rows.Err(); err != nil {
303 return nil, fmt.Errorf("failed to execute pulls-count query: %w ", err)
304 }
305
306 var repos []Repo
307 for _, r := range repoMap {
308 repos = append(repos, *r)
309 }
310
311 slices.SortFunc(repos, func(a, b Repo) int {
312 if a.Created.After(b.Created) {
313 return 1
314 }
315 return -1
316 })
317
318 return repos, nil
319}
320
321func GetAllReposByDid(e Execer, did string) ([]Repo, error) {
322 var repos []Repo
323
324 rows, err := e.Query(
325 `select
326 r.did,
327 r.name,
328 r.knot,
329 r.rkey,
330 r.description,
331 r.created,
332 count(s.id) as star_count,
333 r.source
334 from
335 repos r
336 left join
337 stars s on r.at_uri = s.repo_at
338 where
339 r.did = ?
340 group by
341 r.at_uri
342 order by r.created desc`,
343 did)
344 if err != nil {
345 return nil, err
346 }
347 defer rows.Close()
348
349 for rows.Next() {
350 var repo Repo
351 var repoStats RepoStats
352 var createdAt string
353 var nullableDescription sql.NullString
354 var nullableSource sql.NullString
355
356 err := rows.Scan(&repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &createdAt, &repoStats.StarCount, &nullableSource)
357 if err != nil {
358 return nil, err
359 }
360
361 if nullableDescription.Valid {
362 repo.Description = nullableDescription.String
363 }
364
365 if nullableSource.Valid {
366 repo.Source = nullableSource.String
367 }
368
369 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
370 if err != nil {
371 repo.Created = time.Now()
372 } else {
373 repo.Created = createdAtTime
374 }
375
376 repo.RepoStats = &repoStats
377
378 repos = append(repos, repo)
379 }
380
381 if err := rows.Err(); err != nil {
382 return nil, err
383 }
384
385 return repos, nil
386}
387
388func GetRepo(e Execer, did, name string) (*Repo, error) {
389 var repo Repo
390 var description, spindle sql.NullString
391
392 row := e.QueryRow(`
393 select did, name, knot, created, description, spindle, rkey
394 from repos
395 where did = ? and name = ?
396 `,
397 did,
398 name,
399 )
400
401 var createdAt string
402 if err := row.Scan(&repo.Did, &repo.Name, &repo.Knot, &createdAt, &description, &spindle, &repo.Rkey); err != nil {
403 return nil, err
404 }
405 createdAtTime, _ := time.Parse(time.RFC3339, createdAt)
406 repo.Created = createdAtTime
407
408 if description.Valid {
409 repo.Description = description.String
410 }
411
412 if spindle.Valid {
413 repo.Spindle = spindle.String
414 }
415
416 return &repo, nil
417}
418
419func GetRepoByAtUri(e Execer, atUri string) (*Repo, error) {
420 var repo Repo
421 var nullableDescription sql.NullString
422
423 row := e.QueryRow(`select did, name, knot, created, rkey, description from repos where at_uri = ?`, atUri)
424
425 var createdAt string
426 if err := row.Scan(&repo.Did, &repo.Name, &repo.Knot, &createdAt, &repo.Rkey, &nullableDescription); err != nil {
427 return nil, err
428 }
429 createdAtTime, _ := time.Parse(time.RFC3339, createdAt)
430 repo.Created = createdAtTime
431
432 if nullableDescription.Valid {
433 repo.Description = nullableDescription.String
434 } else {
435 repo.Description = ""
436 }
437
438 return &repo, nil
439}
440
441func AddRepo(e Execer, repo *Repo) error {
442 _, err := e.Exec(
443 `insert into repos
444 (did, name, knot, rkey, at_uri, description, source)
445 values (?, ?, ?, ?, ?, ?, ?)`,
446 repo.Did, repo.Name, repo.Knot, repo.Rkey, repo.RepoAt().String(), repo.Description, repo.Source,
447 )
448 return err
449}
450
451func RemoveRepo(e Execer, did, name string) error {
452 _, err := e.Exec(`delete from repos where did = ? and name = ?`, did, name)
453 return err
454}
455
456func GetRepoSource(e Execer, repoAt syntax.ATURI) (string, error) {
457 var nullableSource sql.NullString
458 err := e.QueryRow(`select source from repos where at_uri = ?`, repoAt).Scan(&nullableSource)
459 if err != nil {
460 return "", err
461 }
462 return nullableSource.String, nil
463}
464
465func GetForksByDid(e Execer, did string) ([]Repo, error) {
466 var repos []Repo
467
468 rows, err := e.Query(
469 `select distinct r.did, r.name, r.knot, r.rkey, r.description, r.created, r.source
470 from repos r
471 left join collaborators c on r.at_uri = c.repo_at
472 where (r.did = ? or c.subject_did = ?)
473 and r.source is not null
474 and r.source != ''
475 order by r.created desc`,
476 did, did,
477 )
478 if err != nil {
479 return nil, err
480 }
481 defer rows.Close()
482
483 for rows.Next() {
484 var repo Repo
485 var createdAt string
486 var nullableDescription sql.NullString
487 var nullableSource sql.NullString
488
489 err := rows.Scan(&repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &createdAt, &nullableSource)
490 if err != nil {
491 return nil, err
492 }
493
494 if nullableDescription.Valid {
495 repo.Description = nullableDescription.String
496 }
497
498 if nullableSource.Valid {
499 repo.Source = nullableSource.String
500 }
501
502 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
503 if err != nil {
504 repo.Created = time.Now()
505 } else {
506 repo.Created = createdAtTime
507 }
508
509 repos = append(repos, repo)
510 }
511
512 if err := rows.Err(); err != nil {
513 return nil, err
514 }
515
516 return repos, nil
517}
518
519func GetForkByDid(e Execer, did string, name string) (*Repo, error) {
520 var repo Repo
521 var createdAt string
522 var nullableDescription sql.NullString
523 var nullableSource sql.NullString
524
525 row := e.QueryRow(
526 `select did, name, knot, rkey, description, created, source
527 from repos
528 where did = ? and name = ? and source is not null and source != ''`,
529 did, name,
530 )
531
532 err := row.Scan(&repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &createdAt, &nullableSource)
533 if err != nil {
534 return nil, err
535 }
536
537 if nullableDescription.Valid {
538 repo.Description = nullableDescription.String
539 }
540
541 if nullableSource.Valid {
542 repo.Source = nullableSource.String
543 }
544
545 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
546 if err != nil {
547 repo.Created = time.Now()
548 } else {
549 repo.Created = createdAtTime
550 }
551
552 return &repo, nil
553}
554
555func UpdateDescription(e Execer, repoAt, newDescription string) error {
556 _, err := e.Exec(
557 `update repos set description = ? where at_uri = ?`, newDescription, repoAt)
558 return err
559}
560
561func UpdateSpindle(e Execer, repoAt string, spindle *string) error {
562 _, err := e.Exec(
563 `update repos set spindle = ? where at_uri = ?`, spindle, repoAt)
564 return err
565}
566
567type RepoStats struct {
568 Language string
569 StarCount int
570 IssueCount IssueCount
571 PullCount PullCount
572}
573
574func scanRepo(rows *sql.Rows, did, name, knot, rkey, description *string, created *time.Time, source *string) error {
575 var createdAt string
576 var nullableDescription sql.NullString
577 var nullableSource sql.NullString
578 if err := rows.Scan(did, name, knot, rkey, &nullableDescription, &createdAt, &nullableSource); err != nil {
579 return err
580 }
581
582 if nullableDescription.Valid {
583 *description = nullableDescription.String
584 } else {
585 *description = ""
586 }
587
588 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
589 if err != nil {
590 *created = time.Now()
591 } else {
592 *created = createdAtTime
593 }
594
595 if nullableSource.Valid {
596 *source = nullableSource.String
597 } else {
598 *source = ""
599 }
600
601 return nil
602}