this repo has no description
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 GetRepoSourceRepo(e Execer, repoAt syntax.ATURI) (*models.Repo, error) {
415 source, err := GetRepoSource(e, repoAt)
416 if source == "" || errors.Is(err, sql.ErrNoRows) {
417 return nil, nil
418 }
419 if err != nil {
420 return nil, err
421 }
422 return GetRepoByAtUri(e, source)
423}
424
425func GetForksByDid(e Execer, did string) ([]models.Repo, error) {
426 var repos []models.Repo
427
428 rows, err := e.Query(
429 `select distinct r.id, r.did, r.name, r.knot, r.rkey, r.description, r.website, r.created, r.source
430 from repos r
431 left join collaborators c on r.at_uri = c.repo_at
432 where (r.did = ? or c.subject_did = ?)
433 and r.source is not null
434 and r.source != ''
435 order by r.created desc`,
436 did, did,
437 )
438 if err != nil {
439 return nil, err
440 }
441 defer rows.Close()
442
443 for rows.Next() {
444 var repo models.Repo
445 var createdAt string
446 var nullableDescription sql.NullString
447 var nullableWebsite sql.NullString
448 var nullableSource sql.NullString
449
450 err := rows.Scan(&repo.Id, &repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &nullableWebsite, &createdAt, &nullableSource)
451 if err != nil {
452 return nil, err
453 }
454
455 if nullableDescription.Valid {
456 repo.Description = nullableDescription.String
457 }
458
459 if nullableSource.Valid {
460 repo.Source = nullableSource.String
461 }
462
463 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
464 if err != nil {
465 repo.Created = time.Now()
466 } else {
467 repo.Created = createdAtTime
468 }
469
470 repos = append(repos, repo)
471 }
472
473 if err := rows.Err(); err != nil {
474 return nil, err
475 }
476
477 return repos, nil
478}
479
480func GetForkByDid(e Execer, did string, name string) (*models.Repo, error) {
481 var repo models.Repo
482 var createdAt string
483 var nullableDescription sql.NullString
484 var nullableWebsite sql.NullString
485 var nullableTopicStr sql.NullString
486 var nullableSource sql.NullString
487
488 row := e.QueryRow(
489 `select id, did, name, knot, rkey, description, website, topics, created, source
490 from repos
491 where did = ? and name = ? and source is not null and source != ''`,
492 did, name,
493 )
494
495 err := row.Scan(&repo.Id, &repo.Did, &repo.Name, &repo.Knot, &repo.Rkey, &nullableDescription, &nullableWebsite, &nullableTopicStr, &createdAt, &nullableSource)
496 if err != nil {
497 return nil, err
498 }
499
500 if nullableDescription.Valid {
501 repo.Description = nullableDescription.String
502 }
503
504 if nullableWebsite.Valid {
505 repo.Website = nullableWebsite.String
506 }
507
508 if nullableTopicStr.Valid {
509 repo.Topics = strings.Fields(nullableTopicStr.String)
510 }
511
512 if nullableSource.Valid {
513 repo.Source = nullableSource.String
514 }
515
516 createdAtTime, err := time.Parse(time.RFC3339, createdAt)
517 if err != nil {
518 repo.Created = time.Now()
519 } else {
520 repo.Created = createdAtTime
521 }
522
523 return &repo, nil
524}
525
526func UpdateDescription(e Execer, repoAt, newDescription string) error {
527 _, err := e.Exec(
528 `update repos set description = ? where at_uri = ?`, newDescription, repoAt)
529 return err
530}
531
532func UpdateSpindle(e Execer, repoAt string, spindle *string) error {
533 _, err := e.Exec(
534 `update repos set spindle = ? where at_uri = ?`, spindle, repoAt)
535 return err
536}
537
538func SubscribeLabel(e Execer, rl *models.RepoLabel) error {
539 query := `insert or ignore into repo_labels (repo_at, label_at) values (?, ?)`
540
541 _, err := e.Exec(query, rl.RepoAt.String(), rl.LabelAt.String())
542 return err
543}
544
545func UnsubscribeLabel(e Execer, filters ...filter) error {
546 var conditions []string
547 var args []any
548 for _, filter := range filters {
549 conditions = append(conditions, filter.Condition())
550 args = append(args, filter.Arg()...)
551 }
552
553 whereClause := ""
554 if conditions != nil {
555 whereClause = " where " + strings.Join(conditions, " and ")
556 }
557
558 query := fmt.Sprintf(`delete from repo_labels %s`, whereClause)
559 _, err := e.Exec(query, args...)
560 return err
561}
562
563func GetRepoLabels(e Execer, filters ...filter) ([]models.RepoLabel, error) {
564 var conditions []string
565 var args []any
566 for _, filter := range filters {
567 conditions = append(conditions, filter.Condition())
568 args = append(args, filter.Arg()...)
569 }
570
571 whereClause := ""
572 if conditions != nil {
573 whereClause = " where " + strings.Join(conditions, " and ")
574 }
575
576 query := fmt.Sprintf(`select id, repo_at, label_at from repo_labels %s`, whereClause)
577
578 rows, err := e.Query(query, args...)
579 if err != nil {
580 return nil, err
581 }
582 defer rows.Close()
583
584 var labels []models.RepoLabel
585 for rows.Next() {
586 var label models.RepoLabel
587
588 err := rows.Scan(&label.Id, &label.RepoAt, &label.LabelAt)
589 if err != nil {
590 return nil, err
591 }
592
593 labels = append(labels, label)
594 }
595
596 if err = rows.Err(); err != nil {
597 return nil, err
598 }
599
600 return labels, nil
601}