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