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