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