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