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