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