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