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