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