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