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