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