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