this repo has no description
tangled.org
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 currentMonth := time.Now().Month()
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 pullMonth := pull.Created.Month()
34
35 if currentMonth-pullMonth >= TimeframeMonths {
36 // shouldn't happen; but times are weird
37 continue
38 }
39
40 idx := currentMonth - pullMonth
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 issueMonth := issue.Created.Month()
57
58 if currentMonth-issueMonth >= TimeframeMonths {
59 // shouldn't happen; but times are weird
60 continue
61 }
62
63 idx := currentMonth - issueMonth
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 return nil, err
81 }
82 }
83
84 repoMonth := repo.Created.Month()
85
86 if currentMonth-repoMonth >= TimeframeMonths {
87 // shouldn't happen; but times are weird
88 continue
89 }
90
91 idx := currentMonth - repoMonth
92
93 items := &timeline.ByMonth[idx].RepoEvents
94 *items = append(*items, models.RepoEvent{
95 Repo: &repo,
96 Source: sourceRepo,
97 })
98 }
99
100 return &timeline, nil
101}
102
103func UpsertProfile(tx *sql.Tx, profile *models.Profile) error {
104 defer tx.Rollback()
105
106 // update links
107 _, err := tx.Exec(`delete from profile_links where did = ?`, profile.Did)
108 if err != nil {
109 return err
110 }
111 // update vanity stats
112 _, err = tx.Exec(`delete from profile_stats where did = ?`, profile.Did)
113 if err != nil {
114 return err
115 }
116
117 // update pinned repos
118 _, err = tx.Exec(`delete from profile_pinned_repositories where did = ?`, profile.Did)
119 if err != nil {
120 return err
121 }
122
123 includeBskyValue := 0
124 if profile.IncludeBluesky {
125 includeBskyValue = 1
126 }
127
128 _, err = tx.Exec(
129 `insert or replace into profile (
130 did,
131 avatar,
132 description,
133 include_bluesky,
134 location,
135 pronouns
136 )
137 values (?, ?, ?, ?, ?, ?)`,
138 profile.Did,
139 profile.Avatar,
140 profile.Description,
141 includeBskyValue,
142 profile.Location,
143 profile.Pronouns,
144 )
145
146 if err != nil {
147 log.Println("profile", "err", err)
148 return err
149 }
150
151 for _, link := range profile.Links {
152 if link == "" {
153 continue
154 }
155
156 _, err := tx.Exec(
157 `insert into profile_links (did, link) values (?, ?)`,
158 profile.Did,
159 link,
160 )
161
162 if err != nil {
163 log.Println("profile_links", "err", err)
164 return err
165 }
166 }
167
168 for _, v := range profile.Stats {
169 if v.Kind == "" {
170 continue
171 }
172
173 _, err := tx.Exec(
174 `insert into profile_stats (did, kind) values (?, ?)`,
175 profile.Did,
176 v.Kind,
177 )
178
179 if err != nil {
180 log.Println("profile_stats", "err", err)
181 return err
182 }
183 }
184
185 for _, pin := range profile.PinnedRepos {
186 if pin == "" {
187 continue
188 }
189
190 _, err := tx.Exec(
191 `insert into profile_pinned_repositories (did, at_uri) values (?, ?)`,
192 profile.Did,
193 pin,
194 )
195
196 if err != nil {
197 log.Println("profile_pinned_repositories", "err", err)
198 return err
199 }
200 }
201
202 return tx.Commit()
203}
204
205func GetProfiles(e Execer, filters ...orm.Filter) (map[string]*models.Profile, error) {
206 var conditions []string
207 var args []any
208 for _, filter := range filters {
209 conditions = append(conditions, filter.Condition())
210 args = append(args, filter.Arg()...)
211 }
212
213 whereClause := ""
214 if conditions != nil {
215 whereClause = " where " + strings.Join(conditions, " and ")
216 }
217
218 profilesQuery := fmt.Sprintf(
219 `select
220 id,
221 did,
222 description,
223 include_bluesky,
224 location,
225 pronouns
226 from
227 profile
228 %s`,
229 whereClause,
230 )
231 rows, err := e.Query(profilesQuery, args...)
232 if err != nil {
233 return nil, err
234 }
235
236 profileMap := make(map[string]*models.Profile)
237 for rows.Next() {
238 var profile models.Profile
239 var includeBluesky int
240 var pronouns sql.Null[string]
241
242 err = rows.Scan(&profile.ID, &profile.Did, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
243 if err != nil {
244 return nil, err
245 }
246
247 if includeBluesky != 0 {
248 profile.IncludeBluesky = true
249 }
250
251 if pronouns.Valid {
252 profile.Pronouns = pronouns.V
253 }
254
255 profileMap[profile.Did] = &profile
256 }
257 if err = rows.Err(); err != nil {
258 return nil, err
259 }
260
261 // populate profile links
262 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(profileMap)), ", ")
263 args = make([]any, len(profileMap))
264 i := 0
265 for did := range profileMap {
266 args[i] = did
267 i++
268 }
269
270 linksQuery := fmt.Sprintf("select link, did from profile_links where did in (%s)", inClause)
271 rows, err = e.Query(linksQuery, args...)
272 if err != nil {
273 return nil, err
274 }
275 idxs := make(map[string]int)
276 for did := range profileMap {
277 idxs[did] = 0
278 }
279 for rows.Next() {
280 var link, did string
281 if err = rows.Scan(&link, &did); err != nil {
282 return nil, err
283 }
284
285 idx := idxs[did]
286 profileMap[did].Links[idx] = link
287 idxs[did] = idx + 1
288 }
289
290 pinsQuery := fmt.Sprintf("select at_uri, did from profile_pinned_repositories where did in (%s)", inClause)
291 rows, err = e.Query(pinsQuery, args...)
292 if err != nil {
293 return nil, err
294 }
295 idxs = make(map[string]int)
296 for did := range profileMap {
297 idxs[did] = 0
298 }
299 for rows.Next() {
300 var link syntax.ATURI
301 var did string
302 if err = rows.Scan(&link, &did); err != nil {
303 return nil, err
304 }
305
306 idx := idxs[did]
307 profileMap[did].PinnedRepos[idx] = link
308 idxs[did] = idx + 1
309 }
310
311 return profileMap, nil
312}
313
314func GetProfile(e Execer, did string) (*models.Profile, error) {
315 var profile models.Profile
316 var pronouns sql.Null[string]
317 var avatar sql.Null[string]
318
319 profile.Did = did
320
321 includeBluesky := 0
322
323 err := e.QueryRow(
324 `select avatar, description, include_bluesky, location, pronouns from profile where did = ?`,
325 did,
326 ).Scan(&avatar, &profile.Description, &includeBluesky, &profile.Location, &pronouns)
327 if err == sql.ErrNoRows {
328 profile := models.Profile{}
329 profile.Did = did
330 return &profile, nil
331 }
332
333 if err != nil {
334 return nil, err
335 }
336
337 if includeBluesky != 0 {
338 profile.IncludeBluesky = true
339 }
340
341 if pronouns.Valid {
342 profile.Pronouns = pronouns.V
343 }
344
345 if avatar.Valid {
346 profile.Avatar = avatar.V
347 }
348
349 rows, err := e.Query(`select link from profile_links where did = ?`, did)
350 if err != nil {
351 return nil, err
352 }
353 defer rows.Close()
354 i := 0
355 for rows.Next() {
356 if err := rows.Scan(&profile.Links[i]); err != nil {
357 return nil, err
358 }
359 i++
360 }
361
362 rows, err = e.Query(`select kind from profile_stats where did = ?`, did)
363 if err != nil {
364 return nil, err
365 }
366 defer rows.Close()
367 i = 0
368 for rows.Next() {
369 if err := rows.Scan(&profile.Stats[i].Kind); err != nil {
370 return nil, err
371 }
372 value, err := GetVanityStat(e, profile.Did, profile.Stats[i].Kind)
373 if err != nil {
374 return nil, err
375 }
376 profile.Stats[i].Value = value
377 i++
378 }
379
380 rows, err = e.Query(`select at_uri from profile_pinned_repositories where did = ?`, did)
381 if err != nil {
382 return nil, err
383 }
384 defer rows.Close()
385 i = 0
386 for rows.Next() {
387 if err := rows.Scan(&profile.PinnedRepos[i]); err != nil {
388 return nil, err
389 }
390 i++
391 }
392
393 return &profile, nil
394}
395
396func GetVanityStat(e Execer, did string, stat models.VanityStatKind) (uint64, error) {
397 query := ""
398 var args []any
399 switch stat {
400 case models.VanityStatMergedPRCount:
401 query = `select count(id) from pulls where owner_did = ? and state = ?`
402 args = append(args, did, models.PullMerged)
403 case models.VanityStatClosedPRCount:
404 query = `select count(id) from pulls where owner_did = ? and state = ?`
405 args = append(args, did, models.PullClosed)
406 case models.VanityStatOpenPRCount:
407 query = `select count(id) from pulls where owner_did = ? and state = ?`
408 args = append(args, did, models.PullOpen)
409 case models.VanityStatOpenIssueCount:
410 query = `select count(id) from issues where did = ? and open = 1`
411 args = append(args, did)
412 case models.VanityStatClosedIssueCount:
413 query = `select count(id) from issues where did = ? and open = 0`
414 args = append(args, did)
415 case models.VanityStatRepositoryCount:
416 query = `select count(id) from repos where did = ?`
417 args = append(args, did)
418 }
419
420 var result uint64
421 err := e.QueryRow(query, args...).Scan(&result)
422 if err != nil {
423 return 0, err
424 }
425
426 return result, nil
427}
428
429func ValidateProfile(e Execer, profile *models.Profile) error {
430 // ensure description is not too long
431 if len(profile.Description) > 256 {
432 return fmt.Errorf("Entered bio is too long.")
433 }
434
435 // ensure description is not too long
436 if len(profile.Location) > 40 {
437 return fmt.Errorf("Entered location is too long.")
438 }
439
440 // ensure pronouns are not too long
441 if len(profile.Pronouns) > 40 {
442 return fmt.Errorf("Entered pronouns are too long.")
443 }
444
445 // ensure links are in order
446 err := validateLinks(profile)
447 if err != nil {
448 return err
449 }
450
451 // ensure all pinned repos are either own repos or collaborating repos
452 repos, err := GetRepos(e, 0, orm.FilterEq("did", profile.Did))
453 if err != nil {
454 log.Printf("getting repos for %s: %s", profile.Did, err)
455 }
456
457 collaboratingRepos, err := CollaboratingIn(e, profile.Did)
458 if err != nil {
459 log.Printf("getting collaborating repos for %s: %s", profile.Did, err)
460 }
461
462 var validRepos []syntax.ATURI
463 for _, r := range repos {
464 validRepos = append(validRepos, r.RepoAt())
465 }
466 for _, r := range collaboratingRepos {
467 validRepos = append(validRepos, r.RepoAt())
468 }
469
470 for _, pinned := range profile.PinnedRepos {
471 if pinned == "" {
472 continue
473 }
474 if !slices.Contains(validRepos, pinned) {
475 return fmt.Errorf("Invalid pinned repo: `%s, does not belong to own or collaborating repos", pinned)
476 }
477 }
478
479 return nil
480}
481
482func validateLinks(profile *models.Profile) error {
483 for i, link := range profile.Links {
484 if link == "" {
485 continue
486 }
487
488 parsedURL, err := url.Parse(link)
489 if err != nil {
490 return fmt.Errorf("Invalid URL '%s': %v\n", link, err)
491 }
492
493 if parsedURL.Scheme == "" {
494 if strings.HasPrefix(link, "//") {
495 profile.Links[i] = "https:" + link
496 } else {
497 profile.Links[i] = "https://" + link
498 }
499 continue
500 } else if parsedURL.Scheme != "http" && parsedURL.Scheme != "https" {
501 return fmt.Errorf("Warning: URL '%s' has unusual scheme: %s\n", link, parsedURL.Scheme)
502 }
503
504 // catch relative paths
505 if parsedURL.Host == "" {
506 return fmt.Errorf("Warning: URL '%s' appears to be a relative path\n", link)
507 }
508 }
509 return nil
510}