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(tx *sql.Tx, profile *models.Profile) error {
134 // update links
135 _, err := tx.Exec(`delete from profile_links where did = ?`, profile.Did)
136 if err != nil {
137 return err
138 }
139 // update vanity stats
140 _, err = tx.Exec(`delete from profile_stats where did = ?`, profile.Did)
141 if err != nil {
142 return err
143 }
144
145 // update pinned repos
146 _, err = tx.Exec(`delete from profile_pinned_repositories where did = ?`, profile.Did)
147 if err != nil {
148 return err
149 }
150
151 includeBskyValue := 0
152 if profile.IncludeBluesky {
153 includeBskyValue = 1
154 }
155
156 _, err = tx.Exec(
157 `insert or replace into profile (
158 did,
159 avatar,
160 description,
161 include_bluesky,
162 location,
163 pronouns,
164 preferred_handle
165 )
166 values (?, ?, ?, ?, ?, ?, ?)`,
167 profile.Did,
168 profile.Avatar,
169 profile.Description,
170 includeBskyValue,
171 profile.Location,
172 profile.Pronouns,
173 string(profile.PreferredHandle),
174 )
175
176 if err != nil {
177 log.Println("profile", "err", err)
178 return err
179 }
180
181 for _, link := range profile.Links {
182 if link == "" {
183 continue
184 }
185
186 _, err := tx.Exec(
187 `insert into profile_links (did, link) values (?, ?)`,
188 profile.Did,
189 link,
190 )
191
192 if err != nil {
193 log.Println("profile_links", "err", err)
194 return err
195 }
196 }
197
198 for _, v := range profile.Stats {
199 if v.Kind == "" {
200 continue
201 }
202
203 _, err := tx.Exec(
204 `insert into profile_stats (did, kind) values (?, ?)`,
205 profile.Did,
206 v.Kind,
207 )
208
209 if err != nil {
210 log.Println("profile_stats", "err", err)
211 return err
212 }
213 }
214
215 for _, pin := range profile.PinnedRepos {
216 if pin == "" {
217 continue
218 }
219
220 _, err := tx.Exec(
221 `insert into profile_pinned_repositories (did, pin) values (?, ?)`,
222 profile.Did,
223 pin,
224 )
225
226 if err != nil {
227 log.Println("profile_pinned_repositories", "err", err)
228 return err
229 }
230 }
231 return nil
232}
233
234func DeleteProfile(tx *sql.Tx, did string) error {
235 defer tx.Rollback()
236
237 if _, err := tx.Exec(`delete from profile where did = ?`, did); err != nil {
238 return err
239 }
240
241 return tx.Commit()
242}
243
244func GetProfiles(e Execer, filters ...orm.Filter) (map[string]*models.Profile, error) {
245 var conditions []string
246 var args []any
247 for _, filter := range filters {
248 conditions = append(conditions, filter.Condition())
249 args = append(args, filter.Arg()...)
250 }
251
252 whereClause := ""
253 if conditions != nil {
254 whereClause = " where " + strings.Join(conditions, " and ")
255 }
256
257 profilesQuery := fmt.Sprintf(
258 `select
259 id,
260 did,
261 description,
262 include_bluesky,
263 location,
264 pronouns,
265 preferred_handle
266 from
267 profile
268 %s`,
269 whereClause,
270 )
271 rows, err := e.Query(profilesQuery, args...)
272 if err != nil {
273 return nil, err
274 }
275 defer rows.Close()
276
277 profileMap := make(map[string]*models.Profile)
278 for rows.Next() {
279 var profile models.Profile
280 var includeBluesky int
281 var pronouns sql.Null[string]
282 var preferredHandle sql.Null[string]
283
284 err = rows.Scan(&profile.ID, &profile.Did, &profile.Description, &includeBluesky, &profile.Location, &pronouns, &preferredHandle)
285 if err != nil {
286 return nil, err
287 }
288
289 if includeBluesky != 0 {
290 profile.IncludeBluesky = true
291 }
292
293 if pronouns.Valid {
294 profile.Pronouns = pronouns.V
295 }
296
297 if preferredHandle.Valid {
298 profile.PreferredHandle = syntax.Handle(preferredHandle.V)
299 }
300
301 profileMap[profile.Did] = &profile
302 }
303 if err = rows.Err(); err != nil {
304 return nil, err
305 }
306
307 // populate profile links
308 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(profileMap)), ", ")
309 args = make([]any, len(profileMap))
310 i := 0
311 for did := range profileMap {
312 args[i] = did
313 i++
314 }
315
316 linksQuery := fmt.Sprintf("select link, did from profile_links where did in (%s)", inClause)
317 rows, err = e.Query(linksQuery, args...)
318 if err != nil {
319 return nil, err
320 }
321 defer rows.Close()
322
323 idxs := make(map[string]int)
324 for did := range profileMap {
325 idxs[did] = 0
326 }
327 for rows.Next() {
328 var link, did string
329 if err = rows.Scan(&link, &did); err != nil {
330 return nil, err
331 }
332
333 idx := idxs[did]
334 profileMap[did].Links[idx] = link
335 idxs[did] = idx + 1
336 }
337
338 pinsQuery := fmt.Sprintf("select pin, did from profile_pinned_repositories where did in (%s)", inClause)
339 rows, err = e.Query(pinsQuery, args...)
340 if err != nil {
341 return nil, err
342 }
343 defer rows.Close()
344
345 idxs = make(map[string]int)
346 for did := range profileMap {
347 idxs[did] = 0
348 }
349 for rows.Next() {
350 var pin string
351 var did string
352 if err = rows.Scan(&pin, &did); err != nil {
353 return nil, err
354 }
355
356 idx := idxs[did]
357 profileMap[did].PinnedRepos[idx] = pin
358 idxs[did] = idx + 1
359 }
360
361 return profileMap, nil
362}
363
364func GetPreferredHandle(e Execer, did string) (syntax.Handle, error) {
365 var h sql.Null[string]
366 err := e.QueryRow(
367 `select preferred_handle from profile where did = ?`,
368 did,
369 ).Scan(&h)
370 if err != nil {
371 return "", err
372 }
373 if !h.Valid || h.V == "" {
374 return "", sql.ErrNoRows
375 }
376 return syntax.Handle(h.V), nil
377}
378
379func GetDidByPreferredHandle(e Execer, handle syntax.Handle) (syntax.DID, error) {
380 var did string
381 err := e.QueryRow(
382 `select did from profile where preferred_handle = ?`,
383 string(handle),
384 ).Scan(&did)
385 if err != nil {
386 return "", err
387 }
388 return syntax.DID(did), nil
389}
390
391func GetProfile(e Execer, did string) (*models.Profile, error) {
392 var profile models.Profile
393 var pronouns sql.Null[string]
394 var avatar sql.Null[string]
395 var preferredHandle sql.Null[string]
396
397 profile.Did = did
398
399 includeBluesky := 0
400
401 err := e.QueryRow(
402 `select avatar, description, include_bluesky, location, pronouns, preferred_handle from profile where did = ?`,
403 did,
404 ).Scan(&avatar, &profile.Description, &includeBluesky, &profile.Location, &pronouns, &preferredHandle)
405 if err == sql.ErrNoRows {
406 return nil, nil
407 }
408
409 if err != nil {
410 return nil, err
411 }
412
413 if includeBluesky != 0 {
414 profile.IncludeBluesky = true
415 }
416
417 if pronouns.Valid {
418 profile.Pronouns = pronouns.V
419 }
420
421 if avatar.Valid {
422 profile.Avatar = avatar.V
423 }
424
425 if preferredHandle.Valid {
426 profile.PreferredHandle = syntax.Handle(preferredHandle.V)
427 }
428
429 rows, err := e.Query(`select link from profile_links where did = ?`, did)
430 if err != nil {
431 return nil, err
432 }
433 defer rows.Close()
434 i := 0
435 for rows.Next() {
436 if err := rows.Scan(&profile.Links[i]); err != nil {
437 return nil, err
438 }
439 i++
440 }
441
442 rows, err = e.Query(`select kind from profile_stats where did = ?`, did)
443 if err != nil {
444 return nil, err
445 }
446 defer rows.Close()
447 i = 0
448 for rows.Next() {
449 if err := rows.Scan(&profile.Stats[i].Kind); err != nil {
450 return nil, err
451 }
452 value, err := GetVanityStat(e, profile.Did, profile.Stats[i].Kind)
453 if err != nil {
454 return nil, err
455 }
456 profile.Stats[i].Value = value
457 i++
458 }
459
460 rows, err = e.Query(`select pin from profile_pinned_repositories where did = ?`, did)
461 if err != nil {
462 return nil, err
463 }
464 defer rows.Close()
465 i = 0
466 for rows.Next() {
467 if err := rows.Scan(&profile.PinnedRepos[i]); err != nil {
468 return nil, err
469 }
470 i++
471 }
472
473 return &profile, nil
474}
475
476func GetVanityStat(e Execer, did string, stat models.VanityStatKind) (uint64, error) {
477 query := ""
478 var args []any
479 switch stat {
480 case models.VanityStatMergedPRCount:
481 query = `select count(id) from pulls where owner_did = ? and state = ?`
482 args = append(args, did, models.PullMerged)
483 case models.VanityStatClosedPRCount:
484 query = `select count(id) from pulls where owner_did = ? and state = ?`
485 args = append(args, did, models.PullClosed)
486 case models.VanityStatOpenPRCount:
487 query = `select count(id) from pulls where owner_did = ? and state = ?`
488 args = append(args, did, models.PullOpen)
489 case models.VanityStatOpenIssueCount:
490 query = `select count(id) from issues where did = ? and open = 1`
491 args = append(args, did)
492 case models.VanityStatClosedIssueCount:
493 query = `select count(id) from issues where did = ? and open = 0`
494 args = append(args, did)
495 case models.VanityStatRepositoryCount:
496 query = `select count(id) from repos where did = ?`
497 args = append(args, did)
498 case models.VanityStatStarCount:
499 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 = ?`
500 args = append(args, did)
501 case models.VanityStatNone:
502 return 0, nil
503 default:
504 return 0, fmt.Errorf("invalid vanity stat kind: %s", stat)
505 }
506
507 var result uint64
508 err := e.QueryRow(query, args...).Scan(&result)
509 if err != nil {
510 return 0, err
511 }
512
513 return result, nil
514}
515
516func ValidateProfile(e Execer, profile *models.Profile) error {
517 // ensure description is not too long
518 if len(profile.Description) > 256 {
519 return fmt.Errorf("Entered bio is too long.")
520 }
521
522 // ensure description is not too long
523 if len(profile.Location) > 40 {
524 return fmt.Errorf("Entered location is too long.")
525 }
526
527 // ensure pronouns are not too long
528 if len(profile.Pronouns) > 40 {
529 return fmt.Errorf("Entered pronouns are too long.")
530 }
531
532 if profile.PreferredHandle != "" {
533 if _, err := syntax.ParseHandle(string(profile.PreferredHandle)); err != nil {
534 return fmt.Errorf("Invalid preferred handle format.")
535 }
536
537 claimant, err := GetDidByPreferredHandle(e, profile.PreferredHandle)
538 if err == nil && string(claimant) != profile.Did {
539 return fmt.Errorf("Preferred handle is already claimed by another user.")
540 }
541 }
542
543 // ensure links are in order
544 err := validateLinks(profile)
545 if err != nil {
546 return err
547 }
548
549 repos, err := GetRepos(e, orm.FilterEq("did", profile.Did))
550 if err != nil {
551 log.Printf("getting repos for %s: %s", profile.Did, err)
552 }
553
554 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
555 if err != nil {
556 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
557 }
558
559 // ensure all pinned repos are either own repos or collaborating repos
560 allRepos := append(repos, collaboratingRepos...)
561
562 for _, pinned := range profile.PinnedRepos {
563 if pinned == "" {
564 continue
565 }
566 matched := slices.ContainsFunc(allRepos, func(r models.Repo) bool {
567 if strings.HasPrefix(pinned, "did:") {
568 return pinned == r.RepoDid
569 }
570 return pinned == string(r.RepoAt())
571 })
572 if !matched {
573 return fmt.Errorf("Invalid pinned repo: `%s`, does not belong to own or collaborating repos", pinned)
574 }
575 }
576
577 return nil
578}
579
580func validateLinks(profile *models.Profile) error {
581 for i, link := range profile.Links {
582 if link == "" {
583 continue
584 }
585
586 parsedURL, err := url.Parse(link)
587 if err != nil {
588 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
589 }
590
591 if parsedURL.Scheme == "" {
592 if strings.HasPrefix(link, "//") {
593 profile.Links[i] = "https:" + link
594 } else {
595 profile.Links[i] = "https://" + link
596 }
597 continue
598 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
599 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
600 }
601
602 // catch relative paths
603 if parsedURL.Host == "" {
604 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
605 }
606 }
607 return nil
608}