alice.pds.demo.boltless.dev
This repository has no description
1package db
2
3import (
4 "fmt"
5 "log"
6 "strings"
7 "time"
8
9 "github.com/bluesky-social/indigo/atproto/syntax"
10 "tangled.org/core/appview/models"
11 "tangled.org/core/orm"
12)
13
14func UpsertFollow(e Execer, follow models.Follow) error {
15 _, err := e.Exec(
16 `insert into follows (did, rkey, subject_did, created)
17 values (?, ?, ?, ?)
18 on conflict(did, rkey) do update set
19 subject_did = excluded.subject_did,
20 created = excluded.created`,
21 follow.UserDid,
22 follow.Rkey,
23 follow.SubjectDid,
24 follow.FollowedAt.Format(time.RFC3339),
25 )
26 return err
27}
28
29// Remove a follow
30func DeleteFollow(e Execer, did, subjectDid syntax.DID) ([]syntax.ATURI, error) {
31 var deleted []syntax.ATURI
32 rows, err := e.Query(
33 `delete from follows
34 where did = ? and subject_did = ?
35 returning at_uri`,
36 did,
37 subjectDid,
38 )
39 if err != nil {
40 return nil, fmt.Errorf("deleting follows: %w", err)
41 }
42 defer rows.Close()
43
44 for rows.Next() {
45 var aturi syntax.ATURI
46 if err := rows.Scan(&aturi); err != nil {
47 return nil, fmt.Errorf("scanning at_uri: %w", err)
48 }
49 deleted = append(deleted, aturi)
50 }
51 return deleted, nil
52}
53
54// Remove a follow
55func DeleteFollowByRkey(e Execer, userDid, rkey string) error {
56 _, err := e.Exec(`delete from follows where did = ? and rkey = ?`, userDid, rkey)
57 return err
58}
59
60// GetMostFollowed returns the DIDs with the most followers, most-followed first.
61func GetMostFollowed(e Execer, limit int) ([]string, error) {
62 query := `
63 select subject_did, count(distinct did) as followers
64 from follows
65 group by subject_did
66 order by followers desc
67 limit ?`
68
69 rows, err := e.Query(query, limit)
70 if err != nil {
71 return nil, err
72 }
73 defer rows.Close()
74
75 var dids []string
76 for rows.Next() {
77 var did string
78 var followers int64
79 if err := rows.Scan(&did, &followers); err != nil {
80 return nil, err
81 }
82 dids = append(dids, did)
83 }
84 return dids, rows.Err()
85}
86
87func GetFollowerFollowingCount(e Execer, did string) (models.FollowStats, error) {
88 var followers, following int64
89 err := e.QueryRow(
90 `SELECT
91 COUNT(DISTINCT CASE WHEN subject_did = ? THEN did END) AS followers,
92 COUNT(DISTINCT CASE WHEN did = ? THEN subject_did END) AS following
93 FROM follows;`, did, did).Scan(&followers, &following)
94 if err != nil {
95 return models.FollowStats{}, err
96 }
97 return models.FollowStats{
98 Followers: followers,
99 Following: following,
100 }, nil
101}
102
103func GetFollowerFollowingCounts(e Execer, dids []string) (map[string]models.FollowStats, error) {
104 if len(dids) == 0 {
105 return nil, nil
106 }
107
108 placeholders := make([]string, len(dids))
109 for i := range placeholders {
110 placeholders[i] = "?"
111 }
112 placeholderStr := strings.Join(placeholders, ",")
113
114 args := make([]any, len(dids)*2)
115 for i, did := range dids {
116 args[i] = did
117 args[i+len(dids)] = did
118 }
119
120 query := fmt.Sprintf(`
121 select
122 coalesce(f.did, g.did) as did,
123 coalesce(f.followers, 0) as followers,
124 coalesce(g.following, 0) as following
125 from (
126 select subject_did as did, count(distinct did) as followers
127 from follows
128 where subject_did in (%s)
129 group by subject_did
130 ) f
131 full outer join (
132 select did as did, count(distinct subject_did) as following
133 from follows
134 where did in (%s)
135 group by did
136 ) g on f.did = g.did`,
137 placeholderStr, placeholderStr)
138
139 result := make(map[string]models.FollowStats)
140
141 rows, err := e.Query(query, args...)
142 if err != nil {
143 return nil, err
144 }
145 defer rows.Close()
146
147 for rows.Next() {
148 var did string
149 var followers, following int64
150 if err := rows.Scan(&did, &followers, &following); err != nil {
151 return nil, err
152 }
153 result[did] = models.FollowStats{
154 Followers: followers,
155 Following: following,
156 }
157 }
158
159 for _, did := range dids {
160 if _, exists := result[did]; !exists {
161 result[did] = models.FollowStats{
162 Followers: 0,
163 Following: 0,
164 }
165 }
166 }
167
168 return result, nil
169}
170
171func GetFollows(e Execer, limit int, filters ...orm.Filter) ([]models.Follow, error) {
172 var follows []models.Follow
173
174 var conditions []string
175 var args []any
176 for _, filter := range filters {
177 conditions = append(conditions, filter.Condition())
178 args = append(args, filter.Arg()...)
179 }
180
181 whereClause := ""
182 if conditions != nil {
183 whereClause = " where " + strings.Join(conditions, " and ")
184 }
185 limitClause := ""
186 if limit > 0 {
187 limitClause = " limit ?"
188 args = append(args, limit)
189 }
190
191 query := fmt.Sprintf(
192 `select did, subject_did, created, rkey
193 from follows
194 %s
195 order by created desc
196 %s
197 `, whereClause, limitClause)
198
199 rows, err := e.Query(query, args...)
200 if err != nil {
201 return nil, err
202 }
203 defer rows.Close()
204
205 for rows.Next() {
206 var follow models.Follow
207 var followedAt string
208 err := rows.Scan(
209 &follow.UserDid,
210 &follow.SubjectDid,
211 &followedAt,
212 &follow.Rkey,
213 )
214 if err != nil {
215 return nil, err
216 }
217 followedAtTime, err := time.Parse(time.RFC3339, followedAt)
218 if err != nil {
219 log.Println("unable to determine followed at time")
220 follow.FollowedAt = time.Now()
221 } else {
222 follow.FollowedAt = followedAtTime
223 }
224 follows = append(follows, follow)
225 }
226 return follows, nil
227}
228
229func GetFollowers(e Execer, did string) ([]models.Follow, error) {
230 return GetFollows(e, 0, orm.FilterEq("subject_did", did))
231}
232
233func GetFollowing(e Execer, did string) ([]models.Follow, error) {
234 return GetFollows(e, 0, orm.FilterEq("did", did))
235}
236
237func getFollowStatuses(e Execer, userDid string, subjectDids []string) (map[string]models.FollowStatus, error) {
238 if len(subjectDids) == 0 || userDid == "" {
239 return make(map[string]models.FollowStatus), nil
240 }
241
242 result := make(map[string]models.FollowStatus)
243
244 for _, subjectDid := range subjectDids {
245 if userDid == subjectDid {
246 result[subjectDid] = models.IsSelf
247 } else {
248 result[subjectDid] = models.IsNotFollowing
249 }
250 }
251
252 var querySubjects []string
253 for _, subjectDid := range subjectDids {
254 if userDid != subjectDid {
255 querySubjects = append(querySubjects, subjectDid)
256 }
257 }
258
259 if len(querySubjects) == 0 {
260 return result, nil
261 }
262
263 placeholders := make([]string, len(querySubjects))
264 args := make([]any, len(querySubjects)+1)
265 args[0] = userDid
266
267 for i, subjectDid := range querySubjects {
268 placeholders[i] = "?"
269 args[i+1] = subjectDid
270 }
271
272 query := fmt.Sprintf(`
273 SELECT subject_did
274 FROM follows
275 WHERE did = ? AND subject_did IN (%s)
276 `, strings.Join(placeholders, ","))
277
278 rows, err := e.Query(query, args...)
279 if err != nil {
280 return nil, err
281 }
282 defer rows.Close()
283
284 for rows.Next() {
285 var subjectDid string
286 if err := rows.Scan(&subjectDid); err != nil {
287 return nil, err
288 }
289 result[subjectDid] = models.IsFollowing
290 }
291
292 return result, nil
293}
294
295func GetFollowStatus(e Execer, userDid, subjectDid string) models.FollowStatus {
296 statuses, err := getFollowStatuses(e, userDid, []string{subjectDid})
297 if err != nil {
298 return models.IsNotFollowing
299 }
300 return statuses[subjectDid]
301}
302
303func GetFollowStatuses(e Execer, userDid string, subjectDids []string) (map[string]models.FollowStatus, error) {
304 return getFollowStatuses(e, userDid, subjectDids)
305}