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