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