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