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