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 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 )
167 values (?, ?, ?, ?, ?, ?)`,
168 profile.Did,
169 profile.Avatar,
170 profile.Description,
171 includeBskyValue,
172 profile.Location,
173 profile.Pronouns,
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, at_uri) 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
232 return tx.Commit()
233}
234
235func GetProfiles(e Execer, filters ...orm.Filter) (map[string]*models.Profile, error) {
236 var conditions []string
237 var args []any
238 for _, filter := range filters {
239 conditions = append(conditions, filter.Condition())
240 args = append(args, filter.Arg()...)
241 }
242
243 whereClause := ""
244 if conditions != nil {
245 whereClause = " where " + strings.Join(conditions, " and ")
246 }
247
248 profilesQuery := fmt.Sprintf(
249 `select
250 id,
251 did,
252 description,
253 include_bluesky,
254 location,
255 pronouns
256 from
257 profile
258 %s`,
259 whereClause,
260 )
261 rows, err := e.Query(profilesQuery, args...)
262 if err != nil {
263 return nil, err
264 }
265 defer rows.Close()
266
267 profileMap := make(map[string]*models.Profile)
268 for rows.Next() {
269 var profile models.Profile
270 var includeBluesky int
271 var pronouns sql.Null[string]
272
273 err = rows.Scan(&profile.ID, &profile.Did, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
274 if err != nil {
275 return nil, err
276 }
277
278 if includeBluesky != 0 {
279 profile.IncludeBluesky = true
280 }
281
282 if pronouns.Valid {
283 profile.Pronouns = pronouns.V
284 }
285
286 profileMap[profile.Did] = &profile
287 }
288 if err = rows.Err(); err != nil {
289 return nil, err
290 }
291
292 // populate profile links
293 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(profileMap)), ", ")
294 args = make([]any, len(profileMap))
295 i := 0
296 for did := range profileMap {
297 args[i] = did
298 i++
299 }
300
301 linksQuery := fmt.Sprintf("select link, did from profile_links where did in (%s)", inClause)
302 rows, err = e.Query(linksQuery, args...)
303 if err != nil {
304 return nil, err
305 }
306 defer rows.Close()
307
308 idxs := make(map[string]int)
309 for did := range profileMap {
310 idxs[did] = 0
311 }
312 for rows.Next() {
313 var link, did string
314 if err = rows.Scan(&link, &did); err != nil {
315 return nil, err
316 }
317
318 idx := idxs[did]
319 profileMap[did].Links[idx] = link
320 idxs[did] = idx + 1
321 }
322
323 pinsQuery := fmt.Sprintf("select at_uri, did from profile_pinned_repositories where did in (%s)", inClause)
324 rows, err = e.Query(pinsQuery, args...)
325 if err != nil {
326 return nil, err
327 }
328 defer rows.Close()
329
330 idxs = make(map[string]int)
331 for did := range profileMap {
332 idxs[did] = 0
333 }
334 for rows.Next() {
335 var link syntax.ATURI
336 var did string
337 if err = rows.Scan(&link, &did); err != nil {
338 return nil, err
339 }
340
341 idx := idxs[did]
342 profileMap[did].PinnedRepos[idx] = link
343 idxs[did] = idx + 1
344 }
345
346 return profileMap, nil
347}
348
349func GetProfile(e Execer, did string) (*models.Profile, error) {
350 var profile models.Profile
351 var pronouns sql.Null[string]
352 var avatar sql.Null[string]
353
354 profile.Did = did
355
356 includeBluesky := 0
357
358 err := e.QueryRow(
359 `select avatar, description, include_bluesky, location, pronouns from profile where did = ?`,
360 did,
361 ).Scan(&avatar, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
362 if err == sql.ErrNoRows {
363 return nil, nil
364 }
365
366 if err != nil {
367 return nil, err
368 }
369
370 if includeBluesky != 0 {
371 profile.IncludeBluesky = true
372 }
373
374 if pronouns.Valid {
375 profile.Pronouns = pronouns.V
376 }
377
378 if avatar.Valid {
379 profile.Avatar = avatar.V
380 }
381
382 rows, err := e.Query(`select link from profile_links where did = ?`, did)
383 if err != nil {
384 return nil, err
385 }
386 defer rows.Close()
387 i := 0
388 for rows.Next() {
389 if err := rows.Scan(&profile.Links[i]); err != nil {
390 return nil, err
391 }
392 i++
393 }
394
395 rows, err = e.Query(`select kind from profile_stats where did = ?`, did)
396 if err != nil {
397 return nil, err
398 }
399 defer rows.Close()
400 i = 0
401 for rows.Next() {
402 if err := rows.Scan(&profile.Stats[i].Kind); err != nil {
403 return nil, err
404 }
405 value, err := GetVanityStat(e, profile.Did, profile.Stats[i].Kind)
406 if err != nil {
407 return nil, err
408 }
409 profile.Stats[i].Value = value
410 i++
411 }
412
413 rows, err = e.Query(`select at_uri from profile_pinned_repositories where did = ?`, did)
414 if err != nil {
415 return nil, err
416 }
417 defer rows.Close()
418 i = 0
419 for rows.Next() {
420 if err := rows.Scan(&profile.PinnedRepos[i]); err != nil {
421 return nil, err
422 }
423 i++
424 }
425
426 return &profile, nil
427}
428
429func GetVanityStat(e Execer, did string, stat models.VanityStatKind) (uint64, error) {
430 query := ""
431 var args []any
432 switch stat {
433 case models.VanityStatMergedPRCount:
434 query = `select count(id) from pulls where owner_did = ? and state = ?`
435 args = append(args, did, models.PullMerged)
436 case models.VanityStatClosedPRCount:
437 query = `select count(id) from pulls where owner_did = ? and state = ?`
438 args = append(args, did, models.PullClosed)
439 case models.VanityStatOpenPRCount:
440 query = `select count(id) from pulls where owner_did = ? and state = ?`
441 args = append(args, did, models.PullOpen)
442 case models.VanityStatOpenIssueCount:
443 query = `select count(id) from issues where did = ? and open = 1`
444 args = append(args, did)
445 case models.VanityStatClosedIssueCount:
446 query = `select count(id) from issues where did = ? and open = 0`
447 args = append(args, did)
448 case models.VanityStatRepositoryCount:
449 query = `select count(id) from repos where did = ?`
450 args = append(args, did)
451 case models.VanityStatStarCount:
452 query = `select count(id) from stars where subject_at like 'at://' || ? || '%'`
453 args = append(args, did)
454 case models.VanityStatNone:
455 return 0, nil
456 default:
457 return 0, fmt.Errorf("invalid vanity stat kind: %s", stat)
458 }
459
460 var result uint64
461 err := e.QueryRow(query, args...).Scan(&result)
462 if err != nil {
463 return 0, err
464 }
465
466 return result, nil
467}
468
469func ValidateProfile(e Execer, profile *models.Profile) error {
470 // ensure description is not too long
471 if len(profile.Description) > 256 {
472 return fmt.Errorf("Entered bio is too long.")
473 }
474
475 // ensure description is not too long
476 if len(profile.Location) > 40 {
477 return fmt.Errorf("Entered location is too long.")
478 }
479
480 // ensure pronouns are not too long
481 if len(profile.Pronouns) > 40 {
482 return fmt.Errorf("Entered pronouns are too long.")
483 }
484
485 // ensure links are in order
486 err := validateLinks(profile)
487 if err != nil {
488 return err
489 }
490
491 // ensure all pinned repos are either own repos or collaborating repos
492 repos, err := GetRepos(e, 0, orm.FilterEq("did", profile.Did))
493 if err != nil {
494 log.Printf("getting repos for %s: %s", profile.Did, err)
495 }
496
497 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
498 if err != nil {
499 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
500 }
501
502 var validRepos []syntax.ATURI
503 for _, r := range repos {
504 validRepos = append(validRepos, r.RepoAt())
505 }
506 for _, r := range collaboratingRepos {
507 validRepos = append(validRepos, r.RepoAt())
508 }
509
510 for _, pinned := range profile.PinnedRepos {
511 if pinned == "" {
512 continue
513 }
514 if !slices.Contains(validRepos, pinned) {
515 return fmt.Errorf("Invalid pinned repo: `%s, does not belong to own or collaborating repos", pinned)
516 }
517 }
518
519 return nil
520}
521
522func validateLinks(profile *models.Profile) error {
523 for i, link := range profile.Links {
524 if link == "" {
525 continue
526 }
527
528 parsedURL, err := url.Parse(link)
529 if err != nil {
530 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
531 }
532
533 if parsedURL.Scheme == "" {
534 if strings.HasPrefix(link, "//") {
535 profile.Links[i] = "https:" + link
536 } else {
537 profile.Links[i] = "https://" + link
538 }
539 continue
540 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
541 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
542 }
543
544 // catch relative paths
545 if parsedURL.Host == "" {
546 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
547 }
548 }
549 return nil
550}