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