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 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 did, name, knot, rkey, description, created, source
470 from repos
471 where did = ? and source is not null and source != ''
472 order by created desc`,
473 did,
474 )
475 if err != nil {
476 return nil, err
477 }
478 defer rows.Close()
479
480 for rows.Next() {
481 var repo Repo
482 var createdAt string
483 var nullableDescription sql.NullString
484 var nullableSource sql.NullString
485
486 err := rows.Scan(&repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &createdAt, &nullableSource)
487 if err != nil {
488 return nil, err
489 }
490
491 if nullableDescription.Valid {
492 repo.Description = nullableDescription.String
493 }
494
495 if nullableSource.Valid {
496 repo.Source = nullableSource.String
497 }
498
499 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
500 if err != nil {
501 repo.Created = time.Now()
502 } else {
503 repo.Created = createdAtTime
504 }
505
506 repos = append(repos, repo)
507 }
508
509 if err := rows.Err(); err != nil {
510 return nil, err
511 }
512
513 return repos, nil
514}
515
516func GetForkByDid(e Execer, did string, name string) (*Repo, error) {
517 var repo Repo
518 var createdAt string
519 var nullableDescription sql.NullString
520 var nullableSource sql.NullString
521
522 row := e.QueryRow(
523 `select did, name, knot, rkey, description, created, source
524 from repos
525 where did = ? and name = ? and source is not null and source != ''`,
526 did, name,
527 )
528
529 err := row.Scan(&repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &createdAt, &nullableSource)
530 if err != nil {
531 return nil, err
532 }
533
534 if nullableDescription.Valid {
535 repo.Description = nullableDescription.String
536 }
537
538 if nullableSource.Valid {
539 repo.Source = nullableSource.String
540 }
541
542 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
543 if err != nil {
544 repo.Created = time.Now()
545 } else {
546 repo.Created = createdAtTime
547 }
548
549 return &repo, nil
550}
551
552func UpdateDescription(e Execer, repoAt, newDescription string) error {
553 _, err := e.Exec(
554 `update repos set description = ? where at_uri = ?`, newDescription, repoAt)
555 return err
556}
557
558func UpdateSpindle(e Execer, repoAt string, spindle *string) error {
559 _, err := e.Exec(
560 `update repos set spindle = ? where at_uri = ?`, spindle, repoAt)
561 return err
562}
563
564type RepoStats struct {
565 Language string
566 StarCount int
567 IssueCount IssueCount
568 PullCount PullCount
569}
570
571func scanRepo(rows *sql.Rows, did, name, knot, rkey, description *string, created *time.Time, source *string) error {
572 var createdAt string
573 var nullableDescription sql.NullString
574 var nullableSource sql.NullString
575 if err := rows.Scan(did, name, knot, rkey, &nullableDescription, &createdAt, &nullableSource); err != nil {
576 return err
577 }
578
579 if nullableDescription.Valid {
580 *description = nullableDescription.String
581 } else {
582 *description = ""
583 }
584
585 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
586 if err != nil {
587 *created = time.Now()
588 } else {
589 *created = createdAtTime
590 }
591
592 if nullableSource.Valid {
593 *source = nullableSource.String
594 } else {
595 *source = ""
596 }
597
598 return nil
599}