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