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