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