nel.pet
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.org/core/api/tangled"
15 "tangled.org/core/appview/models"
16)
17
18type Repo struct {
19 Id int64
20 Did string
21 Name string
22 Knot string
23 Rkey string
24 Created time.Time
25 Description string
26 Spindle string
27
28 // optionally, populate this when querying for reverse mappings
29 RepoStats *models.RepoStats
30
31 // optional
32 Source string
33}
34
35func (r Repo) RepoAt() syntax.ATURI {
36 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", r.Did, tangled.RepoNSID, r.Rkey))
37}
38
39func (r Repo) DidSlashRepo() string {
40 p, _ := securejoin.SecureJoin(r.Did, r.Name)
41 return p
42}
43
44func GetRepos(e Execer, limit int, filters ...filter) ([]models.Repo, error) {
45 repoMap := make(map[syntax.ATURI]*models.Repo)
46
47 var conditions []string
48 var args []any
49 for _, filter := range filters {
50 conditions = append(conditions, filter.Condition())
51 args = append(args, filter.Arg()...)
52 }
53
54 whereClause := ""
55 if conditions != nil {
56 whereClause = " where " + strings.Join(conditions, " and ")
57 }
58
59 limitClause := ""
60 if limit != 0 {
61 limitClause = fmt.Sprintf(" limit %d", limit)
62 }
63
64 repoQuery := fmt.Sprintf(
65 `select
66 id,
67 did,
68 name,
69 knot,
70 rkey,
71 created,
72 description,
73 source,
74 spindle
75 from
76 repos r
77 %s
78 order by created desc
79 %s`,
80 whereClause,
81 limitClause,
82 )
83 rows, err := e.Query(repoQuery, args...)
84
85 if err != nil {
86 return nil, fmt.Errorf("failed to execute repo query: %w ", err)
87 }
88
89 for rows.Next() {
90 var repo models.Repo
91 var createdAt string
92 var description, source, spindle sql.NullString
93
94 err := rows.Scan(
95 &repo.Id,
96 &repo.Did,
97 &repo.Name,
98 &repo.Knot,
99 &repo.Rkey,
100 &createdAt,
101 &description,
102 &source,
103 &spindle,
104 )
105 if err != nil {
106 return nil, fmt.Errorf("failed to execute repo query: %w ", err)
107 }
108
109 if t, err := time.Parse(time.RFC3339, createdAt); err == nil {
110 repo.Created = t
111 }
112 if description.Valid {
113 repo.Description = description.String
114 }
115 if source.Valid {
116 repo.Source = source.String
117 }
118 if spindle.Valid {
119 repo.Spindle = spindle.String
120 }
121
122 repo.RepoStats = &models.RepoStats{}
123 repoMap[repo.RepoAt()] = &repo
124 }
125
126 if err = rows.Err(); err != nil {
127 return nil, fmt.Errorf("failed to execute repo query: %w ", err)
128 }
129
130 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(repoMap)), ", ")
131 args = make([]any, len(repoMap))
132
133 i := 0
134 for _, r := range repoMap {
135 args[i] = r.RepoAt()
136 i++
137 }
138
139 // Get labels for all repos
140 labelsQuery := fmt.Sprintf(
141 `select repo_at, label_at from repo_labels where repo_at in (%s)`,
142 inClause,
143 )
144 rows, err = e.Query(labelsQuery, args...)
145 if err != nil {
146 return nil, fmt.Errorf("failed to execute labels query: %w ", err)
147 }
148 for rows.Next() {
149 var repoat, labelat string
150 if err := rows.Scan(&repoat, &labelat); err != nil {
151 log.Println("err", "err", err)
152 continue
153 }
154 if r, ok := repoMap[syntax.ATURI(repoat)]; ok {
155 r.Labels = append(r.Labels, labelat)
156 }
157 }
158 if err = rows.Err(); err != nil {
159 return nil, fmt.Errorf("failed to execute labels query: %w ", err)
160 }
161
162 languageQuery := fmt.Sprintf(
163 `
164 select repo_at, language
165 from (
166 select
167 repo_at,
168 language,
169 row_number() over (
170 partition by repo_at
171 order by bytes desc
172 ) as rn
173 from repo_languages
174 where repo_at in (%s)
175 and is_default_ref = 1
176 )
177 where rn = 1
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 models.PullOpen,
270 models.PullMerged,
271 models.PullClosed,
272 models.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 []models.Repo
300 for _, r := range repoMap {
301 repos = append(repos, *r)
302 }
303
304 slices.SortFunc(repos, func(a, b models.Repo) int {
305 if a.Created.After(b.Created) {
306 return -1
307 }
308 return 1
309 })
310
311 return repos, nil
312}
313
314// helper to get exactly one repo
315func GetRepo(e Execer, filters ...filter) (*models.Repo, error) {
316 repos, err := GetRepos(e, 0, filters...)
317 if err != nil {
318 return nil, err
319 }
320
321 if repos == nil {
322 return nil, sql.ErrNoRows
323 }
324
325 if len(repos) != 1 {
326 return nil, fmt.Errorf("too many rows returned")
327 }
328
329 return &repos[0], nil
330}
331
332func CountRepos(e Execer, filters ...filter) (int64, error) {
333 var conditions []string
334 var args []any
335 for _, filter := range filters {
336 conditions = append(conditions, filter.Condition())
337 args = append(args, filter.Arg()...)
338 }
339
340 whereClause := ""
341 if conditions != nil {
342 whereClause = " where " + strings.Join(conditions, " and ")
343 }
344
345 repoQuery := fmt.Sprintf(`select count(1) from repos %s`, whereClause)
346 var count int64
347 err := e.QueryRow(repoQuery, args...).Scan(&count)
348
349 if !errors.Is(err, sql.ErrNoRows) && err != nil {
350 return 0, err
351 }
352
353 return count, nil
354}
355
356func GetRepoByAtUri(e Execer, atUri string) (*models.Repo, error) {
357 var repo models.Repo
358 var nullableDescription sql.NullString
359
360 row := e.QueryRow(`select id, did, name, knot, created, rkey, description from repos where at_uri = ?`, atUri)
361
362 var createdAt string
363 if err := row.Scan(&repo.Id, &repo.Did, &repo.Name, &repo.Knot, &createdAt, &repo.Rkey, &nullableDescription); err != nil {
364 return nil, err
365 }
366 createdAtTime, _ := time.Parse(time.RFC3339, createdAt)
367 repo.Created = createdAtTime
368
369 if nullableDescription.Valid {
370 repo.Description = nullableDescription.String
371 } else {
372 repo.Description = ""
373 }
374
375 return &repo, nil
376}
377
378func AddRepo(tx *sql.Tx, repo *models.Repo) error {
379 _, err := tx.Exec(
380 `insert into repos
381 (did, name, knot, rkey, at_uri, description, source)
382 values (?, ?, ?, ?, ?, ?, ?)`,
383 repo.Did, repo.Name, repo.Knot, repo.Rkey, repo.RepoAt().String(), repo.Description, repo.Source,
384 )
385 if err != nil {
386 return fmt.Errorf("failed to insert repo: %w", err)
387 }
388
389 for _, dl := range repo.Labels {
390 if err := SubscribeLabel(tx, &models.RepoLabel{
391 RepoAt: repo.RepoAt(),
392 LabelAt: syntax.ATURI(dl),
393 }); err != nil {
394 return fmt.Errorf("failed to subscribe to label: %w", err)
395 }
396 }
397
398 return nil
399}
400
401func RemoveRepo(e Execer, did, name string) error {
402 _, err := e.Exec(`delete from repos where did = ? and name = ?`, did, name)
403 return err
404}
405
406func GetRepoSource(e Execer, repoAt syntax.ATURI) (string, error) {
407 var nullableSource sql.NullString
408 err := e.QueryRow(`select source from repos where at_uri = ?`, repoAt).Scan(&nullableSource)
409 if err != nil {
410 return "", err
411 }
412 return nullableSource.String, nil
413}
414
415func GetForksByDid(e Execer, did string) ([]models.Repo, error) {
416 var repos []models.Repo
417
418 rows, err := e.Query(
419 `select distinct r.id, r.did, r.name, r.knot, r.rkey, r.description, r.created, r.source
420 from repos r
421 left join collaborators c on r.at_uri = c.repo_at
422 where (r.did = ? or c.subject_did = ?)
423 and r.source is not null
424 and r.source != ''
425 order by r.created desc`,
426 did, did,
427 )
428 if err != nil {
429 return nil, err
430 }
431 defer rows.Close()
432
433 for rows.Next() {
434 var repo models.Repo
435 var createdAt string
436 var nullableDescription sql.NullString
437 var nullableSource sql.NullString
438
439 err := rows.Scan(&repo.Id, &repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &createdAt, &nullableSource)
440 if err != nil {
441 return nil, err
442 }
443
444 if nullableDescription.Valid {
445 repo.Description = nullableDescription.String
446 }
447
448 if nullableSource.Valid {
449 repo.Source = nullableSource.String
450 }
451
452 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
453 if err != nil {
454 repo.Created = time.Now()
455 } else {
456 repo.Created = createdAtTime
457 }
458
459 repos = append(repos, repo)
460 }
461
462 if err := rows.Err(); err != nil {
463 return nil, err
464 }
465
466 return repos, nil
467}
468
469func GetForkByDid(e Execer, did string, name string) (*models.Repo, error) {
470 var repo models.Repo
471 var createdAt string
472 var nullableDescription sql.NullString
473 var nullableSource sql.NullString
474
475 row := e.QueryRow(
476 `select id, did, name, knot, rkey, description, created, source
477 from repos
478 where did = ? and name = ? and source is not null and source != ''`,
479 did, name,
480 )
481
482 err := row.Scan(&repo.Id, &repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &createdAt, &nullableSource)
483 if err != nil {
484 return nil, err
485 }
486
487 if nullableDescription.Valid {
488 repo.Description = nullableDescription.String
489 }
490
491 if nullableSource.Valid {
492 repo.Source = nullableSource.String
493 }
494
495 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
496 if err != nil {
497 repo.Created = time.Now()
498 } else {
499 repo.Created = createdAtTime
500 }
501
502 return &repo, nil
503}
504
505func UpdateDescription(e Execer, repoAt, newDescription string) error {
506 _, err := e.Exec(
507 `update repos set description = ? where at_uri = ?`, newDescription, repoAt)
508 return err
509}
510
511func UpdateSpindle(e Execer, repoAt string, spindle *string) error {
512 _, err := e.Exec(
513 `update repos set spindle = ? where at_uri = ?`, spindle, repoAt)
514 return err
515}
516
517func SubscribeLabel(e Execer, rl *models.RepoLabel) error {
518 query := `insert or ignore into repo_labels (repo_at, label_at) values (?, ?)`
519
520 _, err := e.Exec(query, rl.RepoAt.String(), rl.LabelAt.String())
521 return err
522}
523
524func UnsubscribeLabel(e Execer, filters ...filter) error {
525 var conditions []string
526 var args []any
527 for _, filter := range filters {
528 conditions = append(conditions, filter.Condition())
529 args = append(args, filter.Arg()...)
530 }
531
532 whereClause := ""
533 if conditions != nil {
534 whereClause = " where " + strings.Join(conditions, " and ")
535 }
536
537 query := fmt.Sprintf(`delete from repo_labels %s`, whereClause)
538 _, err := e.Exec(query, args...)
539 return err
540}
541
542func GetRepoLabels(e Execer, filters ...filter) ([]models.RepoLabel, error) {
543 var conditions []string
544 var args []any
545 for _, filter := range filters {
546 conditions = append(conditions, filter.Condition())
547 args = append(args, filter.Arg()...)
548 }
549
550 whereClause := ""
551 if conditions != nil {
552 whereClause = " where " + strings.Join(conditions, " and ")
553 }
554
555 query := fmt.Sprintf(`select id, repo_at, label_at from repo_labels %s`, whereClause)
556
557 rows, err := e.Query(query, args...)
558 if err != nil {
559 return nil, err
560 }
561 defer rows.Close()
562
563 var labels []models.RepoLabel
564 for rows.Next() {
565 var label models.RepoLabel
566
567 err := rows.Scan(&label.Id, &label.RepoAt, &label.LabelAt)
568 if err != nil {
569 return nil, err
570 }
571
572 labels = append(labels, label)
573 }
574
575 if err = rows.Err(); err != nil {
576 return nil, err
577 }
578
579 return labels, nil
580}