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