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