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