willdot.net
forked from
tangled.org/core
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 description,
164 include_bluesky,
165 location,
166 pronouns
167 )
168 values (?, ?, ?, ?, ?)`,
169 profile.Did,
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
353 profile.Did = did
354
355 includeBluesky := 0
356
357 err := e.QueryRow(
358 `select description, include_bluesky, location, pronouns, punchard_setting from profile where did = ?`,
359 did,
360 ).Scan(&profile.Description, &includeBluesky, &profile.Location, &pronouns, &profile.PunchardSetting)
361 if err == sql.ErrNoRows {
362 profile := models.Profile{}
363 profile.Did = did
364 return &profile, nil
365 }
366
367 if err != nil {
368 return nil, err
369 }
370
371 if includeBluesky != 0 {
372 profile.IncludeBluesky = true
373 }
374
375 if pronouns.Valid {
376 profile.Pronouns = pronouns.V
377 }
378
379 rows, err := e.Query(`select link from profile_links where did = ?`, did)
380 if err != nil {
381 return nil, err
382 }
383 defer rows.Close()
384 i := 0
385 for rows.Next() {
386 if err := rows.Scan(&profile.Links[i]); err != nil {
387 return nil, err
388 }
389 i++
390 }
391
392 rows, err = e.Query(`select kind from profile_stats where did = ?`, did)
393 if err != nil {
394 return nil, err
395 }
396 defer rows.Close()
397 i = 0
398 for rows.Next() {
399 if err := rows.Scan(&profile.Stats[i].Kind); err != nil {
400 return nil, err
401 }
402 value, err := GetVanityStat(e, profile.Did, profile.Stats[i].Kind)
403 if err != nil {
404 return nil, err
405 }
406 profile.Stats[i].Value = value
407 i++
408 }
409
410 rows, err = e.Query(`select at_uri from profile_pinned_repositories where did = ?`, did)
411 if err != nil {
412 return nil, err
413 }
414 defer rows.Close()
415 i = 0
416 for rows.Next() {
417 if err := rows.Scan(&profile.PinnedRepos[i]); err != nil {
418 return nil, err
419 }
420 i++
421 }
422
423 return &profile, nil
424}
425
426func GetVanityStat(e Execer, did string, stat models.VanityStatKind) (uint64, error) {
427 query := ""
428 var args []any
429 switch stat {
430 case models.VanityStatMergedPRCount:
431 query = `select count(id) from pulls where owner_did = ? and state = ?`
432 args = append(args, did, models.PullMerged)
433 case models.VanityStatClosedPRCount:
434 query = `select count(id) from pulls where owner_did = ? and state = ?`
435 args = append(args, did, models.PullClosed)
436 case models.VanityStatOpenPRCount:
437 query = `select count(id) from pulls where owner_did = ? and state = ?`
438 args = append(args, did, models.PullOpen)
439 case models.VanityStatOpenIssueCount:
440 query = `select count(id) from issues where did = ? and open = 1`
441 args = append(args, did)
442 case models.VanityStatClosedIssueCount:
443 query = `select count(id) from issues where did = ? and open = 0`
444 args = append(args, did)
445 case models.VanityStatRepositoryCount:
446 query = `select count(id) from repos where did = ?`
447 args = append(args, did)
448 }
449
450 var result uint64
451 err := e.QueryRow(query, args...).Scan(&result)
452 if err != nil {
453 return 0, err
454 }
455
456 return result, nil
457}
458
459func ValidateProfile(e Execer, profile *models.Profile) error {
460 // ensure description is not too long
461 if len(profile.Description) > 256 {
462 return fmt.Errorf("Entered bio is too long.")
463 }
464
465 // ensure description is not too long
466 if len(profile.Location) > 40 {
467 return fmt.Errorf("Entered location is too long.")
468 }
469
470 // ensure pronouns are not too long
471 if len(profile.Pronouns) > 40 {
472 return fmt.Errorf("Entered pronouns are too long.")
473 }
474
475 // ensure links are in order
476 err := validateLinks(profile)
477 if err != nil {
478 return err
479 }
480
481 // ensure all pinned repos are either own repos or collaborating repos
482 repos, err := GetRepos(e, 0, orm.FilterEq("did", profile.Did))
483 if err != nil {
484 log.Printf("getting repos for %s: %s", profile.Did, err)
485 }
486
487 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
488 if err != nil {
489 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
490 }
491
492 var validRepos []syntax.ATURI
493 for _, r := range repos {
494 validRepos = append(validRepos, r.RepoAt())
495 }
496 for _, r := range collaboratingRepos {
497 validRepos = append(validRepos, r.RepoAt())
498 }
499
500 for _, pinned := range profile.PinnedRepos {
501 if pinned == "" {
502 continue
503 }
504 if !slices.Contains(validRepos, pinned) {
505 return fmt.Errorf("Invalid pinned repo: `%s, does not belong to own or collaborating repos", pinned)
506 }
507 }
508
509 return nil
510}
511
512func validateLinks(profile *models.Profile) error {
513 for i, link := range profile.Links {
514 if link == "" {
515 continue
516 }
517
518 parsedURL, err := url.Parse(link)
519 if err != nil {
520 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
521 }
522
523 if parsedURL.Scheme == "" {
524 if strings.HasPrefix(link, "//") {
525 profile.Links[i] = "https:" + link
526 } else {
527 profile.Links[i] = "https://" + link
528 }
529 continue
530 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
531 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
532 }
533
534 // catch relative paths
535 if parsedURL.Host == "" {
536 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
537 }
538 }
539 return nil
540}
541
542func SetProfilePunchcardStatus(e Execer, did string, punchcard models.ProfilePunchcardOption) error {
543 _, err := e.Exec(
544 `update profile set punchard_setting = ? where did = ?`,
545 punchcard, did,
546 )
547 return err
548}