veryroundbird.house
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "log"
7 "net/url"
8 "slices"
9 "strings"
10 "time"
11
12 "github.com/bluesky-social/indigo/atproto/syntax"
13 "tangled.sh/tangled.sh/core/api/tangled"
14)
15
16type RepoEvent struct {
17 Repo *Repo
18 Source *Repo
19}
20
21type ProfileTimeline struct {
22 ByMonth []ByMonth
23}
24
25func (p *ProfileTimeline) IsEmpty() bool {
26 if p == nil {
27 return true
28 }
29
30 for _, m := range p.ByMonth {
31 if !m.IsEmpty() {
32 return false
33 }
34 }
35
36 return true
37}
38
39type ByMonth struct {
40 RepoEvents []RepoEvent
41 IssueEvents IssueEvents
42 PullEvents PullEvents
43}
44
45func (b ByMonth) IsEmpty() bool {
46 return len(b.RepoEvents) == 0 &&
47 len(b.IssueEvents.Items) == 0 &&
48 len(b.PullEvents.Items) == 0
49}
50
51type IssueEvents struct {
52 Items []*Issue
53}
54
55type IssueEventStats struct {
56 Open int
57 Closed int
58}
59
60func (i IssueEvents) Stats() IssueEventStats {
61 var open, closed int
62 for _, issue := range i.Items {
63 if issue.Open {
64 open += 1
65 } else {
66 closed += 1
67 }
68 }
69
70 return IssueEventStats{
71 Open: open,
72 Closed: closed,
73 }
74}
75
76type PullEvents struct {
77 Items []*Pull
78}
79
80func (p PullEvents) Stats() PullEventStats {
81 var open, merged, closed int
82 for _, pull := range p.Items {
83 switch pull.State {
84 case PullOpen:
85 open += 1
86 case PullMerged:
87 merged += 1
88 case PullClosed:
89 closed += 1
90 }
91 }
92
93 return PullEventStats{
94 Open: open,
95 Merged: merged,
96 Closed: closed,
97 }
98}
99
100type PullEventStats struct {
101 Closed int
102 Open int
103 Merged int
104}
105
106const TimeframeMonths = 7
107
108func MakeProfileTimeline(e Execer, forDid string) (*ProfileTimeline, error) {
109 timeline := ProfileTimeline{
110 ByMonth: make([]ByMonth, TimeframeMonths),
111 }
112 currentMonth := time.Now().Month()
113 timeframe := fmt.Sprintf("-%d months", TimeframeMonths)
114
115 pulls, err := GetPullsByOwnerDid(e, forDid, timeframe)
116 if err != nil {
117 return nil, fmt.Errorf("error getting pulls by owner did: %w", err)
118 }
119
120 // group pulls by month
121 for _, pull := range pulls {
122 pullMonth := pull.Created.Month()
123
124 if currentMonth-pullMonth >= TimeframeMonths {
125 // shouldn't happen; but times are weird
126 continue
127 }
128
129 idx := currentMonth - pullMonth
130 items := &timeline.ByMonth[idx].PullEvents.Items
131
132 *items = append(*items, &pull)
133 }
134
135 issues, err := GetIssuesByOwnerDid(e, forDid, timeframe)
136 if err != nil {
137 return nil, fmt.Errorf("error getting issues by owner did: %w", err)
138 }
139
140 for _, issue := range issues {
141 issueMonth := issue.Created.Month()
142
143 if currentMonth-issueMonth >= TimeframeMonths {
144 // shouldn't happen; but times are weird
145 continue
146 }
147
148 idx := currentMonth - issueMonth
149 items := &timeline.ByMonth[idx].IssueEvents.Items
150
151 *items = append(*items, &issue)
152 }
153
154 repos, err := GetRepos(e, 0, FilterEq("did", forDid))
155 if err != nil {
156 return nil, fmt.Errorf("error getting all repos by did: %w", err)
157 }
158
159 for _, repo := range repos {
160 // TODO: get this in the original query; requires COALESCE because nullable
161 var sourceRepo *Repo
162 if repo.Source != "" {
163 sourceRepo, err = GetRepoByAtUri(e, repo.Source)
164 if err != nil {
165 return nil, err
166 }
167 }
168
169 repoMonth := repo.Created.Month()
170
171 if currentMonth-repoMonth >= TimeframeMonths {
172 // shouldn't happen; but times are weird
173 continue
174 }
175
176 idx := currentMonth - repoMonth
177
178 items := &timeline.ByMonth[idx].RepoEvents
179 *items = append(*items, RepoEvent{
180 Repo: &repo,
181 Source: sourceRepo,
182 })
183 }
184
185 return &timeline, nil
186}
187
188type Profile struct {
189 // ids
190 ID int
191 Did string
192
193 // data
194 Description string
195 IncludeBluesky bool
196 Location string
197 Links [5]string
198 Stats [2]VanityStat
199 PinnedRepos [6]syntax.ATURI
200}
201
202func (p Profile) IsLinksEmpty() bool {
203 for _, l := range p.Links {
204 if l != "" {
205 return false
206 }
207 }
208 return true
209}
210
211func (p Profile) IsStatsEmpty() bool {
212 for _, s := range p.Stats {
213 if s.Kind != "" {
214 return false
215 }
216 }
217 return true
218}
219
220func (p Profile) IsPinnedReposEmpty() bool {
221 for _, r := range p.PinnedRepos {
222 if r != "" {
223 return false
224 }
225 }
226 return true
227}
228
229type VanityStatKind string
230
231const (
232 VanityStatMergedPRCount VanityStatKind = "merged-pull-request-count"
233 VanityStatClosedPRCount VanityStatKind = "closed-pull-request-count"
234 VanityStatOpenPRCount VanityStatKind = "open-pull-request-count"
235 VanityStatOpenIssueCount VanityStatKind = "open-issue-count"
236 VanityStatClosedIssueCount VanityStatKind = "closed-issue-count"
237 VanityStatRepositoryCount VanityStatKind = "repository-count"
238)
239
240func (v VanityStatKind) String() string {
241 switch v {
242 case VanityStatMergedPRCount:
243 return "Merged PRs"
244 case VanityStatClosedPRCount:
245 return "Closed PRs"
246 case VanityStatOpenPRCount:
247 return "Open PRs"
248 case VanityStatOpenIssueCount:
249 return "Open Issues"
250 case VanityStatClosedIssueCount:
251 return "Closed Issues"
252 case VanityStatRepositoryCount:
253 return "Repositories"
254 }
255 return ""
256}
257
258type VanityStat struct {
259 Kind VanityStatKind
260 Value uint64
261}
262
263func (p *Profile) ProfileAt() syntax.ATURI {
264 return syntax.ATURI(fmt.Sprintf("at://%s/%s/%s", p.Did, tangled.ActorProfileNSID, "self"))
265}
266
267func UpsertProfile(tx *sql.Tx, profile *Profile) error {
268 defer tx.Rollback()
269
270 // update links
271 _, err := tx.Exec(`delete from profile_links where did = ?`, profile.Did)
272 if err != nil {
273 return err
274 }
275 // update vanity stats
276 _, err = tx.Exec(`delete from profile_stats where did = ?`, profile.Did)
277 if err != nil {
278 return err
279 }
280
281 // update pinned repos
282 _, err = tx.Exec(`delete from profile_pinned_repositories where did = ?`, profile.Did)
283 if err != nil {
284 return err
285 }
286
287 includeBskyValue := 0
288 if profile.IncludeBluesky {
289 includeBskyValue = 1
290 }
291
292 _, err = tx.Exec(
293 `insert or replace into profile (
294 did,
295 description,
296 include_bluesky,
297 location
298 )
299 values (?, ?, ?, ?)`,
300 profile.Did,
301 profile.Description,
302 includeBskyValue,
303 profile.Location,
304 )
305
306 if err != nil {
307 log.Println("profile", "err", err)
308 return err
309 }
310
311 for _, link := range profile.Links {
312 if link == "" {
313 continue
314 }
315
316 _, err := tx.Exec(
317 `insert into profile_links (did, link) values (?, ?)`,
318 profile.Did,
319 link,
320 )
321
322 if err != nil {
323 log.Println("profile_links", "err", err)
324 return err
325 }
326 }
327
328 for _, v := range profile.Stats {
329 if v.Kind == "" {
330 continue
331 }
332
333 _, err := tx.Exec(
334 `insert into profile_stats (did, kind) values (?, ?)`,
335 profile.Did,
336 v.Kind,
337 )
338
339 if err != nil {
340 log.Println("profile_stats", "err", err)
341 return err
342 }
343 }
344
345 for _, pin := range profile.PinnedRepos {
346 if pin == "" {
347 continue
348 }
349
350 _, err := tx.Exec(
351 `insert into profile_pinned_repositories (did, at_uri) values (?, ?)`,
352 profile.Did,
353 pin,
354 )
355
356 if err != nil {
357 log.Println("profile_pinned_repositories", "err", err)
358 return err
359 }
360 }
361
362 return tx.Commit()
363}
364
365func GetProfiles(e Execer, filters ...filter) (map[string]*Profile, error) {
366 var conditions []string
367 var args []any
368 for _, filter := range filters {
369 conditions = append(conditions, filter.Condition())
370 args = append(args, filter.Arg()...)
371 }
372
373 whereClause := ""
374 if conditions != nil {
375 whereClause = " where " + strings.Join(conditions, " and ")
376 }
377
378 profilesQuery := fmt.Sprintf(
379 `select
380 id,
381 did,
382 description,
383 include_bluesky,
384 location
385 from
386 profile
387 %s`,
388 whereClause,
389 )
390 rows, err := e.Query(profilesQuery, args...)
391 if err != nil {
392 return nil, err
393 }
394
395 profileMap := make(map[string]*Profile)
396 for rows.Next() {
397 var profile Profile
398 var includeBluesky int
399
400 err = rows.Scan(&profile.ID, &profile.Did, &profile.Description, &includeBluesky, &profile.Location)
401 if err != nil {
402 return nil, err
403 }
404
405 if includeBluesky != 0 {
406 profile.IncludeBluesky = true
407 }
408
409 profileMap[profile.Did] = &profile
410 }
411 if err = rows.Err(); err != nil {
412 return nil, err
413 }
414
415 // populate profile links
416 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(profileMap)), ", ")
417 args = make([]any, len(profileMap))
418 i := 0
419 for did := range profileMap {
420 args[i] = did
421 i++
422 }
423
424 linksQuery := fmt.Sprintf("select link, did from profile_links where did in (%s)", inClause)
425 rows, err = e.Query(linksQuery, args...)
426 if err != nil {
427 return nil, err
428 }
429 idxs := make(map[string]int)
430 for did := range profileMap {
431 idxs[did] = 0
432 }
433 for rows.Next() {
434 var link, did string
435 if err = rows.Scan(&link, &did); err != nil {
436 return nil, err
437 }
438
439 idx := idxs[did]
440 profileMap[did].Links[idx] = link
441 idxs[did] = idx + 1
442 }
443
444 pinsQuery := fmt.Sprintf("select at_uri, did from profile_pinned_repositories where did in (%s)", inClause)
445 rows, err = e.Query(pinsQuery, args...)
446 if err != nil {
447 return nil, err
448 }
449 idxs = make(map[string]int)
450 for did := range profileMap {
451 idxs[did] = 0
452 }
453 for rows.Next() {
454 var link syntax.ATURI
455 var did string
456 if err = rows.Scan(&link, &did); err != nil {
457 return nil, err
458 }
459
460 idx := idxs[did]
461 profileMap[did].PinnedRepos[idx] = link
462 idxs[did] = idx + 1
463 }
464
465 return profileMap, nil
466}
467
468func GetProfile(e Execer, did string) (*Profile, error) {
469 var profile Profile
470 profile.Did = did
471
472 includeBluesky := 0
473 err := e.QueryRow(
474 `select description, include_bluesky, location from profile where did = ?`,
475 did,
476 ).Scan(&profile.Description, &includeBluesky, &profile.Location)
477 if err == sql.ErrNoRows {
478 profile := Profile{}
479 profile.Did = did
480 return &profile, nil
481 }
482
483 if err != nil {
484 return nil, err
485 }
486
487 if includeBluesky != 0 {
488 profile.IncludeBluesky = true
489 }
490
491 rows, err := e.Query(`select link from profile_links where did = ?`, did)
492 if err != nil {
493 return nil, err
494 }
495 defer rows.Close()
496 i := 0
497 for rows.Next() {
498 if err := rows.Scan(&profile.Links[i]); err != nil {
499 return nil, err
500 }
501 i++
502 }
503
504 rows, err = e.Query(`select kind from profile_stats where did = ?`, did)
505 if err != nil {
506 return nil, err
507 }
508 defer rows.Close()
509 i = 0
510 for rows.Next() {
511 if err := rows.Scan(&profile.Stats[i].Kind); err != nil {
512 return nil, err
513 }
514 value, err := GetVanityStat(e, profile.Did, profile.Stats[i].Kind)
515 if err != nil {
516 return nil, err
517 }
518 profile.Stats[i].Value = value
519 i++
520 }
521
522 rows, err = e.Query(`select at_uri from profile_pinned_repositories where did = ?`, did)
523 if err != nil {
524 return nil, err
525 }
526 defer rows.Close()
527 i = 0
528 for rows.Next() {
529 if err := rows.Scan(&profile.PinnedRepos[i]); err != nil {
530 return nil, err
531 }
532 i++
533 }
534
535 return &profile, nil
536}
537
538func GetVanityStat(e Execer, did string, stat VanityStatKind) (uint64, error) {
539 query := ""
540 var args []any
541 switch stat {
542 case VanityStatMergedPRCount:
543 query = `select count(id) from pulls where owner_did = ? and state = ?`
544 args = append(args, did, PullMerged)
545 case VanityStatClosedPRCount:
546 query = `select count(id) from pulls where owner_did = ? and state = ?`
547 args = append(args, did, PullClosed)
548 case VanityStatOpenPRCount:
549 query = `select count(id) from pulls where owner_did = ? and state = ?`
550 args = append(args, did, PullOpen)
551 case VanityStatOpenIssueCount:
552 query = `select count(id) from issues where owner_did = ? and open = 1`
553 args = append(args, did)
554 case VanityStatClosedIssueCount:
555 query = `select count(id) from issues where owner_did = ? and open = 0`
556 args = append(args, did)
557 case VanityStatRepositoryCount:
558 query = `select count(id) from repos where did = ?`
559 args = append(args, did)
560 }
561
562 var result uint64
563 err := e.QueryRow(query, args...).Scan(&result)
564 if err != nil {
565 return 0, err
566 }
567
568 return result, nil
569}
570
571func ValidateProfile(e Execer, profile *Profile) error {
572 // ensure description is not too long
573 if len(profile.Description) > 256 {
574 return fmt.Errorf("Entered bio is too long.")
575 }
576
577 // ensure description is not too long
578 if len(profile.Location) > 40 {
579 return fmt.Errorf("Entered location is too long.")
580 }
581
582 // ensure links are in order
583 err := validateLinks(profile)
584 if err != nil {
585 return err
586 }
587
588 // ensure all pinned repos are either own repos or collaborating repos
589 repos, err := GetRepos(e, 0, FilterEq("did", profile.Did))
590 if err != nil {
591 log.Printf("getting repos for %s: %s", profile.Did, err)
592 }
593
594 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
595 if err != nil {
596 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
597 }
598
599 var validRepos []syntax.ATURI
600 for _, r := range repos {
601 validRepos = append(validRepos, r.RepoAt())
602 }
603 for _, r := range collaboratingRepos {
604 validRepos = append(validRepos, r.RepoAt())
605 }
606
607 for _, pinned := range profile.PinnedRepos {
608 if pinned == "" {
609 continue
610 }
611 if !slices.Contains(validRepos, pinned) {
612 return fmt.Errorf("Invalid pinned repo: `%s, does not belong to own or collaborating repos", pinned)
613 }
614 }
615
616 return nil
617}
618
619func validateLinks(profile *Profile) error {
620 for i, link := range profile.Links {
621 if link == "" {
622 continue
623 }
624
625 parsedURL, err := url.Parse(link)
626 if err != nil {
627 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
628 }
629
630 if parsedURL.Scheme == "" {
631 if strings.HasPrefix(link, "//") {
632 profile.Links[i] = "https:" + link
633 } else {
634 profile.Links[i] = "https://" + link
635 }
636 continue
637 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
638 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
639 }
640
641 // catch relative paths
642 if parsedURL.Host == "" {
643 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
644 }
645 }
646 return nil
647}