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