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