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