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