kot.pink
1package db
2
3import (
4 "database/sql"
5 "errors"
6 "fmt"
7 "log"
8 "strings"
9 "time"
10
11 "github.com/bluesky-social/indigo/atproto/syntax"
12)
13
14type Star struct {
15 StarredByDid string
16 RepoAt syntax.ATURI
17 Created time.Time
18 Rkey string
19
20 // optionally, populate this when querying for reverse mappings
21 Repo *Repo
22}
23
24func (star *Star) ResolveRepo(e Execer) error {
25 if star.Repo != nil {
26 return nil
27 }
28
29 repo, err := GetRepoByAtUri(e, star.RepoAt.String())
30 if err != nil {
31 return err
32 }
33
34 star.Repo = repo
35 return nil
36}
37
38func AddStar(e Execer, star *Star) error {
39 query := `insert or ignore into stars (starred_by_did, repo_at, rkey) values (?, ?, ?)`
40 _, err := e.Exec(
41 query,
42 star.StarredByDid,
43 star.RepoAt.String(),
44 star.Rkey,
45 )
46 return err
47}
48
49// Get a star record
50func GetStar(e Execer, starredByDid string, repoAt syntax.ATURI) (*Star, error) {
51 query := `
52 select starred_by_did, repo_at, created, rkey
53 from stars
54 where starred_by_did = ? and repo_at = ?`
55 row := e.QueryRow(query, starredByDid, repoAt)
56
57 var star Star
58 var created string
59 err := row.Scan(&star.StarredByDid, &star.RepoAt, &created, &star.Rkey)
60 if err != nil {
61 return nil, err
62 }
63
64 createdAtTime, err := time.Parse(time.RFC3339, created)
65 if err != nil {
66 log.Println("unable to determine followed at time")
67 star.Created = time.Now()
68 } else {
69 star.Created = createdAtTime
70 }
71
72 return &star, nil
73}
74
75// Remove a star
76func DeleteStar(e Execer, starredByDid string, repoAt syntax.ATURI) error {
77 _, err := e.Exec(`delete from stars where starred_by_did = ? and repo_at = ?`, starredByDid, repoAt)
78 return err
79}
80
81// Remove a star
82func DeleteStarByRkey(e Execer, starredByDid string, rkey string) error {
83 _, err := e.Exec(`delete from stars where starred_by_did = ? and rkey = ?`, starredByDid, rkey)
84 return err
85}
86
87func GetStarCount(e Execer, repoAt syntax.ATURI) (int, error) {
88 stars := 0
89 err := e.QueryRow(
90 `select count(starred_by_did) from stars where repo_at = ?`, repoAt).Scan(&stars)
91 if err != nil {
92 return 0, err
93 }
94 return stars, nil
95}
96
97// getStarStatuses returns a map of repo URIs to star status for a given user
98// This is an internal helper function to avoid N+1 queries
99func getStarStatuses(e Execer, userDid string, repoAts []syntax.ATURI) (map[string]bool, error) {
100 if len(repoAts) == 0 || userDid == "" {
101 return make(map[string]bool), nil
102 }
103
104 placeholders := make([]string, len(repoAts))
105 args := make([]any, len(repoAts)+1)
106 args[0] = userDid
107
108 for i, repoAt := range repoAts {
109 placeholders[i] = "?"
110 args[i+1] = repoAt.String()
111 }
112
113 query := fmt.Sprintf(`
114 SELECT repo_at
115 FROM stars
116 WHERE starred_by_did = ? AND repo_at IN (%s)
117 `, strings.Join(placeholders, ","))
118
119 rows, err := e.Query(query, args...)
120 if err != nil {
121 return nil, err
122 }
123 defer rows.Close()
124
125 result := make(map[string]bool)
126 // Initialize all repos as not starred
127 for _, repoAt := range repoAts {
128 result[repoAt.String()] = false
129 }
130
131 // Mark starred repos as true
132 for rows.Next() {
133 var repoAt string
134 if err := rows.Scan(&repoAt); err != nil {
135 return nil, err
136 }
137 result[repoAt] = true
138 }
139
140 return result, nil
141}
142
143func GetStarStatus(e Execer, userDid string, repoAt syntax.ATURI) bool {
144 statuses, err := getStarStatuses(e, userDid, []syntax.ATURI{repoAt})
145 if err != nil {
146 return false
147 }
148 return statuses[repoAt.String()]
149}
150
151// GetStarStatuses returns a map of repo URIs to star status for a given user
152func GetStarStatuses(e Execer, userDid string, repoAts []syntax.ATURI) (map[string]bool, error) {
153 return getStarStatuses(e, userDid, repoAts)
154}
155func GetStars(e Execer, limit int, filters ...filter) ([]Star, error) {
156 var conditions []string
157 var args []any
158 for _, filter := range filters {
159 conditions = append(conditions, filter.Condition())
160 args = append(args, filter.Arg()...)
161 }
162
163 whereClause := ""
164 if conditions != nil {
165 whereClause = " where " + strings.Join(conditions, " and ")
166 }
167
168 limitClause := ""
169 if limit != 0 {
170 limitClause = fmt.Sprintf(" limit %d", limit)
171 }
172
173 repoQuery := fmt.Sprintf(
174 `select starred_by_did, repo_at, created, rkey
175 from stars
176 %s
177 order by created desc
178 %s`,
179 whereClause,
180 limitClause,
181 )
182 rows, err := e.Query(repoQuery, args...)
183 if err != nil {
184 return nil, err
185 }
186
187 starMap := make(map[string][]Star)
188 for rows.Next() {
189 var star Star
190 var created string
191 err := rows.Scan(&star.StarredByDid, &star.RepoAt, &created, &star.Rkey)
192 if err != nil {
193 return nil, err
194 }
195
196 star.Created = time.Now()
197 if t, err := time.Parse(time.RFC3339, created); err == nil {
198 star.Created = t
199 }
200
201 repoAt := string(star.RepoAt)
202 starMap[repoAt] = append(starMap[repoAt], star)
203 }
204
205 // populate *Repo in each star
206 args = make([]any, len(starMap))
207 i := 0
208 for r := range starMap {
209 args[i] = r
210 i++
211 }
212
213 if len(args) == 0 {
214 return nil, nil
215 }
216
217 repos, err := GetRepos(e, 0, FilterIn("at_uri", args))
218 if err != nil {
219 return nil, err
220 }
221
222 for _, r := range repos {
223 if stars, ok := starMap[string(r.RepoAt())]; ok {
224 for i := range stars {
225 stars[i].Repo = &r
226 }
227 }
228 }
229
230 var stars []Star
231 for _, s := range starMap {
232 stars = append(stars, s...)
233 }
234
235 return stars, nil
236}
237
238func CountStars(e Execer, filters ...filter) (int64, error) {
239 var conditions []string
240 var args []any
241 for _, filter := range filters {
242 conditions = append(conditions, filter.Condition())
243 args = append(args, filter.Arg()...)
244 }
245
246 whereClause := ""
247 if conditions != nil {
248 whereClause = " where " + strings.Join(conditions, " and ")
249 }
250
251 repoQuery := fmt.Sprintf(`select count(1) from stars %s`, whereClause)
252 var count int64
253 err := e.QueryRow(repoQuery, args...).Scan(&count)
254
255 if !errors.Is(err, sql.ErrNoRows) && err != nil {
256 return 0, err
257 }
258
259 return count, nil
260}
261
262func GetAllStars(e Execer, limit int) ([]Star, error) {
263 var stars []Star
264
265 rows, err := e.Query(`
266 select
267 s.starred_by_did,
268 s.repo_at,
269 s.rkey,
270 s.created,
271 r.did,
272 r.name,
273 r.knot,
274 r.rkey,
275 r.created
276 from stars s
277 join repos r on s.repo_at = r.at_uri
278 `)
279
280 if err != nil {
281 return nil, err
282 }
283 defer rows.Close()
284
285 for rows.Next() {
286 var star Star
287 var repo Repo
288 var starCreatedAt, repoCreatedAt string
289
290 if err := rows.Scan(
291 &star.StarredByDid,
292 &star.RepoAt,
293 &star.Rkey,
294 &starCreatedAt,
295 &repo.Did,
296 &repo.Name,
297 &repo.Knot,
298 &repo.Rkey,
299 &repoCreatedAt,
300 ); err != nil {
301 return nil, err
302 }
303
304 star.Created, err = time.Parse(time.RFC3339, starCreatedAt)
305 if err != nil {
306 star.Created = time.Now()
307 }
308 repo.Created, err = time.Parse(time.RFC3339, repoCreatedAt)
309 if err != nil {
310 repo.Created = time.Now()
311 }
312 star.Repo = &repo
313
314 stars = append(stars, star)
315 }
316
317 if err := rows.Err(); err != nil {
318 return nil, err
319 }
320
321 return stars, nil
322}
323
324// GetTopStarredReposLastWeek returns the top 8 most starred repositories from the last week
325func GetTopStarredReposLastWeek(e Execer) ([]Repo, error) {
326 // first, get the top repo URIs by star count from the last week
327 query := `
328 with recent_starred_repos as (
329 select distinct repo_at
330 from stars
331 where created >= datetime('now', '-7 days')
332 ),
333 repo_star_counts as (
334 select
335 s.repo_at,
336 count(*) as stars_gained_last_week
337 from stars s
338 join recent_starred_repos rsr on s.repo_at = rsr.repo_at
339 where s.created >= datetime('now', '-7 days')
340 group by s.repo_at
341 )
342 select rsc.repo_at
343 from repo_star_counts rsc
344 order by rsc.stars_gained_last_week desc
345 limit 8
346 `
347
348 rows, err := e.Query(query)
349 if err != nil {
350 return nil, err
351 }
352 defer rows.Close()
353
354 var repoUris []string
355 for rows.Next() {
356 var repoUri string
357 err := rows.Scan(&repoUri)
358 if err != nil {
359 return nil, err
360 }
361 repoUris = append(repoUris, repoUri)
362 }
363
364 if err := rows.Err(); err != nil {
365 return nil, err
366 }
367
368 if len(repoUris) == 0 {
369 return []Repo{}, nil
370 }
371
372 // get full repo data
373 repos, err := GetRepos(e, 0, FilterIn("at_uri", repoUris))
374 if err != nil {
375 return nil, err
376 }
377
378 // sort repos by the original trending order
379 repoMap := make(map[string]Repo)
380 for _, repo := range repos {
381 repoMap[repo.RepoAt().String()] = repo
382 }
383
384 orderedRepos := make([]Repo, 0, len(repoUris))
385 for _, uri := range repoUris {
386 if repo, exists := repoMap[uri]; exists {
387 orderedRepos = append(orderedRepos, repo)
388 }
389 }
390
391 return orderedRepos, nil
392}