lewis.moe
Monorepo for Tangled
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, 0, 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 if strings.HasPrefix(repo.Source, "did:") {
79 sourceRepo, err = GetRepoByDid(e, repo.Source)
80 } else {
81 sourceRepo, err = GetRepoByAtUri(e, repo.Source)
82 }
83 if err != nil {
84 log.Println("profile", "err", err)
85 }
86 }
87
88 monthsAgo := monthsBetween(repo.Created, now)
89
90 if monthsAgo >= TimeframeMonths {
91 // shouldn't happen; but times are weird
92 continue
93 }
94
95 idx := monthsAgo
96
97 items := &timeline.ByMonth[idx].RepoEvents
98 *items = append(*items, models.RepoEvent{
99 Repo: &repo,
100 Source: sourceRepo,
101 })
102 }
103
104 punchcard, err := MakePunchcard(
105 e,
106 orm.FilterEq("did", forDid),
107 orm.FilterGte("date", time.Now().AddDate(0, -TimeframeMonths, 0)),
108 )
109 if err != nil {
110 return nil, fmt.Errorf("error getting commits by did: %w", err)
111 }
112 for _, punch := range punchcard.Punches {
113 if punch.Date.After(now) {
114 continue
115 }
116
117 monthsAgo := monthsBetween(punch.Date, now)
118 if monthsAgo >= TimeframeMonths {
119 // shouldn't happen; but times are weird
120 continue
121 }
122
123 idx := monthsAgo
124 timeline.ByMonth[idx].Commits += punch.Count
125 }
126
127 return &timeline, nil
128}
129
130func monthsBetween(from, to time.Time) int {
131 years := to.Year() - from.Year()
132 months := int(to.Month() - from.Month())
133 return years*12 + months
134}
135
136func UpsertProfile(tx *sql.Tx, profile *models.Profile) error {
137 defer tx.Rollback()
138
139 // update links
140 _, err := tx.Exec(`delete from profile_links where did = ?`, profile.Did)
141 if err != nil {
142 return err
143 }
144 // update vanity stats
145 _, err = tx.Exec(`delete from profile_stats where did = ?`, profile.Did)
146 if err != nil {
147 return err
148 }
149
150 // update pinned repos
151 _, err = tx.Exec(`delete from profile_pinned_repositories where did = ?`, profile.Did)
152 if err != nil {
153 return err
154 }
155
156 includeBskyValue := 0
157 if profile.IncludeBluesky {
158 includeBskyValue = 1
159 }
160
161 _, err = tx.Exec(
162 `insert or replace into profile (
163 did,
164 avatar,
165 description,
166 include_bluesky,
167 location,
168 pronouns
169 )
170 values (?, ?, ?, ?, ?, ?)`,
171 profile.Did,
172 profile.Avatar,
173 profile.Description,
174 includeBskyValue,
175 profile.Location,
176 profile.Pronouns,
177 )
178
179 if err != nil {
180 log.Println("profile", "err", err)
181 return err
182 }
183
184 for _, link := range profile.Links {
185 if link == "" {
186 continue
187 }
188
189 _, err := tx.Exec(
190 `insert into profile_links (did, link) values (?, ?)`,
191 profile.Did,
192 link,
193 )
194
195 if err != nil {
196 log.Println("profile_links", "err", err)
197 return err
198 }
199 }
200
201 for _, v := range profile.Stats {
202 if v.Kind == "" {
203 continue
204 }
205
206 _, err := tx.Exec(
207 `insert into profile_stats (did, kind) values (?, ?)`,
208 profile.Did,
209 v.Kind,
210 )
211
212 if err != nil {
213 log.Println("profile_stats", "err", err)
214 return err
215 }
216 }
217
218 for _, pin := range profile.PinnedRepos {
219 if pin == "" {
220 continue
221 }
222
223 _, err := tx.Exec(
224 `insert into profile_pinned_repositories (did, at_uri) values (?, ?)`,
225 profile.Did,
226 pin,
227 )
228
229 if err != nil {
230 log.Println("profile_pinned_repositories", "err", err)
231 return err
232 }
233 }
234
235 return tx.Commit()
236}
237
238func GetProfiles(e Execer, filters ...orm.Filter) (map[string]*models.Profile, error) {
239 var conditions []string
240 var args []any
241 for _, filter := range filters {
242 conditions = append(conditions, filter.Condition())
243 args = append(args, filter.Arg()...)
244 }
245
246 whereClause := ""
247 if conditions != nil {
248 whereClause = " where " + strings.Join(conditions, " and ")
249 }
250
251 profilesQuery := fmt.Sprintf(
252 `select
253 id,
254 did,
255 description,
256 include_bluesky,
257 location,
258 pronouns
259 from
260 profile
261 %s`,
262 whereClause,
263 )
264 rows, err := e.Query(profilesQuery, args...)
265 if err != nil {
266 return nil, err
267 }
268 defer rows.Close()
269
270 profileMap := make(map[string]*models.Profile)
271 for rows.Next() {
272 var profile models.Profile
273 var includeBluesky int
274 var pronouns sql.Null[string]
275
276 err = rows.Scan(&profile.ID, &profile.Did, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
277 if err != nil {
278 return nil, err
279 }
280
281 if includeBluesky != 0 {
282 profile.IncludeBluesky = true
283 }
284
285 if pronouns.Valid {
286 profile.Pronouns = pronouns.V
287 }
288
289 profileMap[profile.Did] = &profile
290 }
291 if err = rows.Err(); err != nil {
292 return nil, err
293 }
294
295 // populate profile links
296 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(profileMap)), ", ")
297 args = make([]any, len(profileMap))
298 i := 0
299 for did := range profileMap {
300 args[i] = did
301 i++
302 }
303
304 linksQuery := fmt.Sprintf("select link, did from profile_links where did in (%s)", inClause)
305 rows, err = e.Query(linksQuery, args...)
306 if err != nil {
307 return nil, err
308 }
309 defer rows.Close()
310
311 idxs := make(map[string]int)
312 for did := range profileMap {
313 idxs[did] = 0
314 }
315 for rows.Next() {
316 var link, did string
317 if err = rows.Scan(&link, &did); err != nil {
318 return nil, err
319 }
320
321 idx := idxs[did]
322 profileMap[did].Links[idx] = link
323 idxs[did] = idx + 1
324 }
325
326 pinsQuery := fmt.Sprintf("select at_uri, did from profile_pinned_repositories where did in (%s)", inClause)
327 rows, err = e.Query(pinsQuery, 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 syntax.ATURI
339 var did string
340 if err = rows.Scan(&link, &did); err != nil {
341 return nil, err
342 }
343
344 idx := idxs[did]
345 profileMap[did].PinnedRepos[idx] = link
346 idxs[did] = idx + 1
347 }
348
349 return profileMap, nil
350}
351
352func GetProfile(e Execer, did string) (*models.Profile, error) {
353 var profile models.Profile
354 var pronouns sql.Null[string]
355 var avatar sql.Null[string]
356
357 profile.Did = did
358
359 includeBluesky := 0
360
361 err := e.QueryRow(
362 `select avatar, description, include_bluesky, location, pronouns from profile where did = ?`,
363 did,
364 ).Scan(&avatar, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
365 if err == sql.ErrNoRows {
366 return nil, nil
367 }
368
369 if err != nil {
370 return nil, err
371 }
372
373 if includeBluesky != 0 {
374 profile.IncludeBluesky = true
375 }
376
377 if pronouns.Valid {
378 profile.Pronouns = pronouns.V
379 }
380
381 if avatar.Valid {
382 profile.Avatar = avatar.V
383 }
384
385 rows, err := e.Query(`select link from profile_links where did = ?`, did)
386 if err != nil {
387 return nil, err
388 }
389 defer rows.Close()
390 i := 0
391 for rows.Next() {
392 if err := rows.Scan(&profile.Links[i]); err != nil {
393 return nil, err
394 }
395 i++
396 }
397
398 rows, err = e.Query(`select kind from profile_stats where did = ?`, did)
399 if err != nil {
400 return nil, err
401 }
402 defer rows.Close()
403 i = 0
404 for rows.Next() {
405 if err := rows.Scan(&profile.Stats[i].Kind); err != nil {
406 return nil, err
407 }
408 value, err := GetVanityStat(e, profile.Did, profile.Stats[i].Kind)
409 if err != nil {
410 return nil, err
411 }
412 profile.Stats[i].Value = value
413 i++
414 }
415
416 rows, err = e.Query(`select at_uri from profile_pinned_repositories where did = ?`, did)
417 if err != nil {
418 return nil, err
419 }
420 defer rows.Close()
421 i = 0
422 for rows.Next() {
423 if err := rows.Scan(&profile.PinnedRepos[i]); err != nil {
424 return nil, err
425 }
426 i++
427 }
428
429 return &profile, nil
430}
431
432func GetVanityStat(e Execer, did string, stat models.VanityStatKind) (uint64, error) {
433 query := ""
434 var args []any
435 switch stat {
436 case models.VanityStatMergedPRCount:
437 query = `select count(id) from pulls where owner_did = ? and state = ?`
438 args = append(args, did, models.PullMerged)
439 case models.VanityStatClosedPRCount:
440 query = `select count(id) from pulls where owner_did = ? and state = ?`
441 args = append(args, did, models.PullClosed)
442 case models.VanityStatOpenPRCount:
443 query = `select count(id) from pulls where owner_did = ? and state = ?`
444 args = append(args, did, models.PullOpen)
445 case models.VanityStatOpenIssueCount:
446 query = `select count(id) from issues where did = ? and open = 1`
447 args = append(args, did)
448 case models.VanityStatClosedIssueCount:
449 query = `select count(id) from issues where did = ? and open = 0`
450 args = append(args, did)
451 case models.VanityStatRepositoryCount:
452 query = `select count(id) from repos where did = ?`
453 args = append(args, did)
454 case models.VanityStatStarCount:
455 query = `select count(s.id) from stars s join repos r on (s.subject_at = r.at_uri or (s.subject_did is not null and s.subject_did = r.repo_did)) where r.did = ?`
456 args = append(args, did)
457 case models.VanityStatNone:
458 return 0, nil
459 default:
460 return 0, fmt.Errorf("invalid vanity stat kind: %s", stat)
461 }
462
463 var result uint64
464 err := e.QueryRow(query, args...).Scan(&result)
465 if err != nil {
466 return 0, err
467 }
468
469 return result, nil
470}
471
472func ValidateProfile(e Execer, profile *models.Profile) error {
473 // ensure description is not too long
474 if len(profile.Description) > 256 {
475 return fmt.Errorf("Entered bio is too long.")
476 }
477
478 // ensure description is not too long
479 if len(profile.Location) > 40 {
480 return fmt.Errorf("Entered location is too long.")
481 }
482
483 // ensure pronouns are not too long
484 if len(profile.Pronouns) > 40 {
485 return fmt.Errorf("Entered pronouns are too long.")
486 }
487
488 // ensure links are in order
489 err := validateLinks(profile)
490 if err != nil {
491 return err
492 }
493
494 // ensure all pinned repos are either own repos or collaborating repos
495 repos, err := GetRepos(e, 0, orm.FilterEq("did", profile.Did))
496 if err != nil {
497 log.Printf("getting repos for %s: %s", profile.Did, err)
498 }
499
500 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
501 if err != nil {
502 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
503 }
504
505 var validRepos []syntax.ATURI
506 for _, r := range repos {
507 validRepos = append(validRepos, r.RepoAt())
508 }
509 for _, r := range collaboratingRepos {
510 validRepos = append(validRepos, r.RepoAt())
511 }
512
513 for _, pinned := range profile.PinnedRepos {
514 if pinned == "" {
515 continue
516 }
517 if !slices.Contains(validRepos, pinned) {
518 return fmt.Errorf("Invalid pinned repo: `%s, does not belong to own or collaborating repos", pinned)
519 }
520 }
521
522 return nil
523}
524
525func validateLinks(profile *models.Profile) error {
526 for i, link := range profile.Links {
527 if link == "" {
528 continue
529 }
530
531 parsedURL, err := url.Parse(link)
532 if err != nil {
533 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
534 }
535
536 if parsedURL.Scheme == "" {
537 if strings.HasPrefix(link, "//") {
538 profile.Links[i] = "https:" + link
539 } else {
540 profile.Links[i] = "https://" + link
541 }
542 continue
543 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
544 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
545 }
546
547 // catch relative paths
548 if parsedURL.Host == "" {
549 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
550 }
551 }
552 return nil
553}