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