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