veryroundbird.house
1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "strings"
7 "time"
8
9 "github.com/bluesky-social/indigo/atproto/syntax"
10)
11
12type Spindle struct {
13 Id int
14 Owner syntax.DID
15 Instance string
16 Verified *time.Time
17 Created time.Time
18 NeedsUpgrade bool
19}
20
21type SpindleMember struct {
22 Id int
23 Did syntax.DID // owner of the record
24 Rkey string // rkey of the record
25 Instance string
26 Subject syntax.DID // the member being added
27 Created time.Time
28}
29
30func GetSpindles(e Execer, filters ...filter) ([]Spindle, error) {
31 var spindles []Spindle
32
33 var conditions []string
34 var args []any
35 for _, filter := range filters {
36 conditions = append(conditions, filter.Condition())
37 args = append(args, filter.Arg()...)
38 }
39
40 whereClause := ""
41 if conditions != nil {
42 whereClause = " where " + strings.Join(conditions, " and ")
43 }
44
45 query := fmt.Sprintf(
46 `select id, owner, instance, verified, created, needs_upgrade
47 from spindles
48 %s
49 order by created
50 `,
51 whereClause,
52 )
53
54 rows, err := e.Query(query, args...)
55
56 if err != nil {
57 return nil, err
58 }
59 defer rows.Close()
60
61 for rows.Next() {
62 var spindle Spindle
63 var createdAt string
64 var verified sql.NullString
65 var needsUpgrade int
66
67 if err := rows.Scan(
68 &spindle.Id,
69 &spindle.Owner,
70 &spindle.Instance,
71 &verified,
72 &createdAt,
73 &needsUpgrade,
74 ); err != nil {
75 return nil, err
76 }
77
78 spindle.Created, err = time.Parse(time.RFC3339, createdAt)
79 if err != nil {
80 spindle.Created = time.Now()
81 }
82
83 if verified.Valid {
84 t, err := time.Parse(time.RFC3339, verified.String)
85 if err != nil {
86 now := time.Now()
87 spindle.Verified = &now
88 }
89 spindle.Verified = &t
90 }
91
92 if needsUpgrade != 0 {
93 spindle.NeedsUpgrade = true
94 }
95
96 spindles = append(spindles, spindle)
97 }
98
99 return spindles, nil
100}
101
102// if there is an existing spindle with the same instance, this returns an error
103func AddSpindle(e Execer, spindle Spindle) error {
104 _, err := e.Exec(
105 `insert into spindles (owner, instance) values (?, ?)`,
106 spindle.Owner,
107 spindle.Instance,
108 )
109 return err
110}
111
112func VerifySpindle(e Execer, filters ...filter) (int64, error) {
113 var conditions []string
114 var args []any
115 for _, filter := range filters {
116 conditions = append(conditions, filter.Condition())
117 args = append(args, filter.Arg()...)
118 }
119
120 whereClause := ""
121 if conditions != nil {
122 whereClause = " where " + strings.Join(conditions, " and ")
123 }
124
125 query := fmt.Sprintf(`update spindles set verified = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now'), needs_upgrade = 0 %s`, whereClause)
126
127 res, err := e.Exec(query, args...)
128 if err != nil {
129 return 0, err
130 }
131
132 return res.RowsAffected()
133}
134
135func DeleteSpindle(e Execer, filters ...filter) error {
136 var conditions []string
137 var args []any
138 for _, filter := range filters {
139 conditions = append(conditions, filter.Condition())
140 args = append(args, filter.Arg()...)
141 }
142
143 whereClause := ""
144 if conditions != nil {
145 whereClause = " where " + strings.Join(conditions, " and ")
146 }
147
148 query := fmt.Sprintf(`delete from spindles %s`, whereClause)
149
150 _, err := e.Exec(query, args...)
151 return err
152}
153
154func AddSpindleMember(e Execer, member SpindleMember) error {
155 _, err := e.Exec(
156 `insert or ignore into spindle_members (did, rkey, instance, subject) values (?, ?, ?, ?)`,
157 member.Did,
158 member.Rkey,
159 member.Instance,
160 member.Subject,
161 )
162 return err
163}
164
165func RemoveSpindleMember(e Execer, filters ...filter) error {
166 var conditions []string
167 var args []any
168 for _, filter := range filters {
169 conditions = append(conditions, filter.Condition())
170 args = append(args, filter.Arg()...)
171 }
172
173 whereClause := ""
174 if conditions != nil {
175 whereClause = " where " + strings.Join(conditions, " and ")
176 }
177
178 query := fmt.Sprintf(`delete from spindle_members %s`, whereClause)
179
180 _, err := e.Exec(query, args...)
181 return err
182}
183
184func GetSpindleMembers(e Execer, filters ...filter) ([]SpindleMember, error) {
185 var members []SpindleMember
186
187 var conditions []string
188 var args []any
189 for _, filter := range filters {
190 conditions = append(conditions, filter.Condition())
191 args = append(args, filter.Arg()...)
192 }
193
194 whereClause := ""
195 if conditions != nil {
196 whereClause = " where " + strings.Join(conditions, " and ")
197 }
198
199 query := fmt.Sprintf(
200 `select id, did, rkey, instance, subject, created
201 from spindle_members
202 %s
203 order by created
204 `,
205 whereClause,
206 )
207
208 rows, err := e.Query(query, args...)
209
210 if err != nil {
211 return nil, err
212 }
213 defer rows.Close()
214
215 for rows.Next() {
216 var member SpindleMember
217 var createdAt string
218
219 if err := rows.Scan(
220 &member.Id,
221 &member.Did,
222 &member.Rkey,
223 &member.Instance,
224 &member.Subject,
225 &createdAt,
226 ); err != nil {
227 return nil, err
228 }
229
230 member.Created, err = time.Parse(time.RFC3339, createdAt)
231 if err != nil {
232 member.Created = time.Now()
233 }
234
235 members = append(members, member)
236 }
237
238 return members, nil
239}