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