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