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