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