dzming.li
A vibe coded tangled fork which supports pijul.
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 // update links
135 _, err := tx.Exec(`delete from profile_links where did = ?`, profile.Did)
136 if err != nil {
137 return err
138 }
139 // update vanity stats
140 _, err = tx.Exec(`delete from profile_stats where did = ?`, profile.Did)
141 if err != nil {
142 return err
143 }
144
145 // update pinned repos
146 _, err = tx.Exec(`delete from profile_pinned_repositories where did = ?`, profile.Did)
147 if err != nil {
148 return err
149 }
150
151 includeBskyValue := 0
152 if profile.IncludeBluesky {
153 includeBskyValue = 1
154 }
155
156 _, err = tx.Exec(
157 `insert or replace into profile (
158 did,
159 avatar,
160 description,
161 include_bluesky,
162 location,
163 pronouns
164 )
165 values (?, ?, ?, ?, ?, ?)`,
166 profile.Did,
167 profile.Avatar,
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 return nil
230}
231
232func GetProfiles(e Execer, filters ...orm.Filter) (map[string]*models.Profile, error) {
233 var conditions []string
234 var args []any
235 for _, filter := range filters {
236 conditions = append(conditions, filter.Condition())
237 args = append(args, filter.Arg()...)
238 }
239
240 whereClause := ""
241 if conditions != nil {
242 whereClause = " where " + strings.Join(conditions, " and ")
243 }
244
245 profilesQuery := fmt.Sprintf(
246 `select
247 id,
248 did,
249 description,
250 include_bluesky,
251 location,
252 pronouns
253 from
254 profile
255 %s`,
256 whereClause,
257 )
258 rows, err := e.Query(profilesQuery, args...)
259 if err != nil {
260 return nil, err
261 }
262 defer rows.Close()
263
264 profileMap := make(map[string]*models.Profile)
265 for rows.Next() {
266 var profile models.Profile
267 var includeBluesky int
268 var pronouns sql.Null[string]
269
270 err = rows.Scan(&profile.ID, &profile.Did, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
271 if err != nil {
272 return nil, err
273 }
274
275 if includeBluesky != 0 {
276 profile.IncludeBluesky = true
277 }
278
279 if pronouns.Valid {
280 profile.Pronouns = pronouns.V
281 }
282
283 profileMap[profile.Did] = &profile
284 }
285 if err = rows.Err(); err != nil {
286 return nil, err
287 }
288
289 // populate profile links
290 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(profileMap)), ", ")
291 args = make([]any, len(profileMap))
292 i := 0
293 for did := range profileMap {
294 args[i] = did
295 i++
296 }
297
298 linksQuery := fmt.Sprintf("select link, did from profile_links where did in (%s)", inClause)
299 rows, err = e.Query(linksQuery, args...)
300 if err != nil {
301 return nil, err
302 }
303 defer rows.Close()
304
305 idxs := make(map[string]int)
306 for did := range profileMap {
307 idxs[did] = 0
308 }
309 for rows.Next() {
310 var link, did string
311 if err = rows.Scan(&link, &did); err != nil {
312 return nil, err
313 }
314
315 idx := idxs[did]
316 profileMap[did].Links[idx] = link
317 idxs[did] = idx + 1
318 }
319
320 pinsQuery := fmt.Sprintf("select at_uri, did from profile_pinned_repositories where did in (%s)", inClause)
321 rows, err = e.Query(pinsQuery, args...)
322 if err != nil {
323 return nil, err
324 }
325 defer rows.Close()
326
327 idxs = make(map[string]int)
328 for did := range profileMap {
329 idxs[did] = 0
330 }
331 for rows.Next() {
332 var link syntax.ATURI
333 var did string
334 if err = rows.Scan(&link, &did); err != nil {
335 return nil, err
336 }
337
338 idx := idxs[did]
339 profileMap[did].PinnedRepos[idx] = link
340 idxs[did] = idx + 1
341 }
342
343 return profileMap, nil
344}
345
346func GetProfile(e Execer, did string) (*models.Profile, error) {
347 var profile models.Profile
348 var pronouns sql.Null[string]
349 var avatar sql.Null[string]
350
351 profile.Did = did
352
353 includeBluesky := 0
354
355 err := e.QueryRow(
356 `select avatar, description, include_bluesky, location, pronouns from profile where did = ?`,
357 did,
358 ).Scan(&avatar, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
359 if err == sql.ErrNoRows {
360 return nil, nil
361 }
362
363 if err != nil {
364 return nil, err
365 }
366
367 if includeBluesky != 0 {
368 profile.IncludeBluesky = true
369 }
370
371 if pronouns.Valid {
372 profile.Pronouns = pronouns.V
373 }
374
375 if avatar.Valid {
376 profile.Avatar = avatar.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 case models.VanityStatStarCount:
449 query = `select count(id) from stars where subject_at like 'at://' || ? || '%'`
450 args = append(args, did)
451 case models.VanityStatNone:
452 return 0, nil
453 default:
454 return 0, fmt.Errorf("invalid vanity stat kind: %s", stat)
455 }
456
457 var result uint64
458 err := e.QueryRow(query, args...).Scan(&result)
459 if err != nil {
460 return 0, err
461 }
462
463 return result, nil
464}
465
466func ValidateProfile(e Execer, profile *models.Profile) error {
467 // ensure description is not too long
468 if len(profile.Description) > 256 {
469 return fmt.Errorf("Entered bio is too long.")
470 }
471
472 // ensure description is not too long
473 if len(profile.Location) > 40 {
474 return fmt.Errorf("Entered location is too long.")
475 }
476
477 // ensure pronouns are not too long
478 if len(profile.Pronouns) > 40 {
479 return fmt.Errorf("Entered pronouns are too long.")
480 }
481
482 // ensure links are in order
483 err := validateLinks(profile)
484 if err != nil {
485 return err
486 }
487
488 // ensure all pinned repos are either own repos or collaborating repos
489 repos, err := GetRepos(e, 0, orm.FilterEq("did", profile.Did))
490 if err != nil {
491 log.Printf("getting repos for %s: %s", profile.Did, err)
492 }
493
494 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
495 if err != nil {
496 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
497 }
498
499 var validRepos []syntax.ATURI
500 for _, r := range repos {
501 validRepos = append(validRepos, r.RepoAt())
502 }
503 for _, r := range collaboratingRepos {
504 validRepos = append(validRepos, r.RepoAt())
505 }
506
507 for _, pinned := range profile.PinnedRepos {
508 if pinned == "" {
509 continue
510 }
511 if !slices.Contains(validRepos, pinned) {
512 return fmt.Errorf("Invalid pinned repo: `%s, does not belong to own or collaborating repos", pinned)
513 }
514 }
515
516 return nil
517}
518
519func validateLinks(profile *models.Profile) error {
520 for i, link := range profile.Links {
521 if link == "" {
522 continue
523 }
524
525 parsedURL, err := url.Parse(link)
526 if err != nil {
527 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
528 }
529
530 if parsedURL.Scheme == "" {
531 if strings.HasPrefix(link, "//") {
532 profile.Links[i] = "https:" + link
533 } else {
534 profile.Links[i] = "https://" + link
535 }
536 continue
537 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
538 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
539 }
540
541 // catch relative paths
542 if parsedURL.Host == "" {
543 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
544 }
545 }
546 return nil
547}