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