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