alice.pds.demo.boltless.dev
This repository has no description
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "slices"
7 "strings"
8 "time"
9
10 "github.com/bluesky-social/indigo/atproto/syntax"
11 "tangled.org/core/appview/models"
12 "tangled.org/core/appview/pagination"
13 "tangled.org/core/orm"
14)
15
16func UpsertStar(e Execer, star models.Star) error {
17 _, err := e.Exec(
18 `insert into stars (did, rkey, subject_type, subject, created)
19 values (?, ?, ?, ?, ?)
20 on conflict(did, rkey) do update set
21 subject_type = excluded.subject_type,
22 subject = excluded.subject,
23 created = excluded.created`,
24 star.Did,
25 star.Rkey,
26 string(star.SubjectType),
27 star.Subject,
28 star.Created.Format(time.RFC3339),
29 )
30 return err
31}
32
33func GetStars(e Execer, subject string, page pagination.Page) ([]models.Star, error) {
34 query := `
35 select did, subject_type, subject, created, rkey
36 from stars
37 where subject = ?
38 order by created desc
39 limit ? offset ?
40 `
41 rows, err := e.Query(query, subject, page.Limit, page.Offset)
42 if err != nil {
43 return nil, err
44 }
45 defer rows.Close()
46
47 var stars []models.Star
48 for rows.Next() {
49 var star models.Star
50 var created string
51 if err := rows.Scan(&star.Did, &star.SubjectType, &star.Subject, &created, &star.Rkey); err != nil {
52 return nil, err
53 }
54
55 star.Created = time.Now()
56 if t, err := time.Parse(time.RFC3339, created); err == nil {
57 star.Created = t
58 }
59 stars = append(stars, star)
60 }
61
62 return stars, rows.Err()
63}
64
65// Remove all stars from given user to subject
66func DeleteStars(tx *sql.Tx, did syntax.DID, subject string) ([]syntax.ATURI, error) {
67 var deleted []syntax.ATURI
68 rows, err := tx.Query(
69 `delete from stars
70 where did = ? and subject = ?
71 returning at_uri`,
72 did,
73 subject,
74 )
75 if err != nil {
76 return nil, fmt.Errorf("deleting stars: %w", err)
77 }
78 defer rows.Close()
79
80 for rows.Next() {
81 var aturi syntax.ATURI
82 if err := rows.Scan(&aturi); err != nil {
83 return nil, fmt.Errorf("scanning at_uri: %w", err)
84 }
85 deleted = append(deleted, aturi)
86 }
87
88 return deleted, nil
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, subjectType models.StarSubjectType, subject string) (int, error) {
98 stars := 0
99 err := e.QueryRow(
100 `select count(did) from stars where subject_type = ? and subject = ?`,
101 string(subjectType), subject,
102 ).Scan(&stars)
103 if err != nil {
104 return 0, err
105 }
106 return stars, nil
107}
108
109// getStarStatuses returns a map of subjects 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, subjects []string) (map[string]bool, error) {
112 if len(subjects) == 0 || userDid == "" {
113 return make(map[string]bool), nil
114 }
115
116 placeholders := make([]string, len(subjects))
117 args := make([]any, len(subjects)+1)
118 args[0] = userDid
119
120 for i, subj := range subjects {
121 placeholders[i] = "?"
122 args[i+1] = subj
123 }
124
125 query := fmt.Sprintf(`
126 SELECT subject
127 FROM stars
128 WHERE did = ? AND subject 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 subjects as not starred
139 for _, subj := range subjects {
140 result[subj] = false
141 }
142
143 // Mark starred subjects as true
144 for rows.Next() {
145 var subj string
146 if err := rows.Scan(&subj); err != nil {
147 return nil, err
148 }
149 result[subj] = true
150 }
151
152 return result, nil
153}
154
155func GetStarStatus(e Execer, userDid string, subject string) bool {
156 statuses, err := getStarStatuses(e, userDid, []string{subject})
157 if err != nil {
158 return false
159 }
160 return statuses[subject]
161}
162
163// GetStarStatuses returns a map of subjects to star status for a given user
164func GetStarStatuses(e Execer, userDid string, subjects []string) (map[string]bool, error) {
165 return getStarStatuses(e, userDid, subjects)
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 conditions = append(conditions, "subject_type = 'repo'")
179
180 whereClause := " where " + strings.Join(conditions, " and ")
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_type, subject, 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.SubjectType, &star.Subject, &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 starMap[star.Subject] = append(starMap[star.Subject], 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("repo_did", 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[r.RepoDid]; 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 if err := e.QueryRow(repoQuery, args...).Scan(&count); err != nil {
277 return 0, err
278 }
279
280 return count, nil
281}
282
283// GetTopStarredReposLastWeek returns the top 8 most starred repositories from the last week
284func GetTopStarredReposLastWeek(e Execer) ([]models.Repo, error) {
285 // first, get the top repo DIDs by star count from the last week
286 query := `
287 with recent_starred_repos as (
288 select distinct subject
289 from stars
290 where created >= datetime('now', '-7 days')
291 and subject_type = 'repo'
292 ),
293 repo_star_counts as (
294 select
295 s.subject,
296 count(*) as stars_gained_last_week
297 from stars s
298 join recent_starred_repos rsr on s.subject = rsr.subject
299 where s.created >= datetime('now', '-7 days')
300 and s.subject_type = 'repo'
301 group by s.subject
302 )
303 select rsc.subject
304 from repo_star_counts rsc
305 order by rsc.stars_gained_last_week desc
306 limit 5
307 `
308
309 rows, err := e.Query(query)
310 if err != nil {
311 return nil, err
312 }
313 defer rows.Close()
314
315 var repoDids []string
316 for rows.Next() {
317 var repoDid string
318 err := rows.Scan(&repoDid)
319 if err != nil {
320 return nil, err
321 }
322 repoDids = append(repoDids, repoDid)
323 }
324
325 if err := rows.Err(); err != nil {
326 return nil, err
327 }
328
329 if len(repoDids) == 0 {
330 return []models.Repo{}, nil
331 }
332
333 // get full repo data
334 repos, err := GetRepos(e, orm.FilterIn("repo_did", repoDids))
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.RepoDid] = repo
343 }
344
345 orderedRepos := make([]models.Repo, 0, len(repoDids))
346 for _, did := range repoDids {
347 if repo, exists := repoMap[did]; exists {
348 orderedRepos = append(orderedRepos, repo)
349 }
350 }
351
352 return orderedRepos, nil
353}