Monorepo for Tangled
tangled.org
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 lexutil "github.com/bluesky-social/indigo/lex/util"
13 "github.com/ipfs/go-cid"
14 "tangled.org/core/appview/models"
15 "tangled.org/core/orm"
16)
17
18func AddString(d *DB, s models.String) error {
19 tx, err := d.Begin()
20 if err != nil {
21 return fmt.Errorf("starting transaction: %w", err)
22 }
23 defer tx.Rollback()
24 res, err := tx.Exec(
25 `insert into strings (
26 did,
27 rkey,
28 cid,
29 title,
30 description,
31 file_name,
32 file_content,
33 created
34 )
35 values (?, ?, ?, ?, ?, ?, ?, ?)
36 on conflict(did, rkey) do update set
37 cid = excluded.cid,
38 title = excluded.title,
39 description = excluded.description,
40 file_name = excluded.file_name,
41 file_content= excluded.file_content,
42 created = excluded.created,
43 edited = case when strings.cid is not null then ? else strings.edited end
44 where strings.cid is not excluded.cid`,
45 s.Did,
46 s.Rkey,
47 s.Cid,
48 s.Title,
49 s.Description,
50 s.FileName,
51 s.FileContent,
52 s.Created.Format(time.RFC3339),
53 time.Now().Format(time.RFC3339),
54 )
55 if err != nil {
56 return fmt.Errorf("inserting string: %w", err)
57 }
58
59 num, err := res.RowsAffected()
60 if err != nil {
61 return fmt.Errorf("calculating affected rows: %w", err)
62 }
63 if num == 0 {
64 return nil
65 }
66
67 _, err = tx.Exec(`delete from string_files where at_uri = ?`, s.AtUri())
68 if err != nil {
69 return fmt.Errorf("deleting old files: %w", err)
70 }
71
72 // legacy single-file strings carry their content in file_name/file_content
73 // and have no rows in string_files; nothing more to insert.
74 if len(s.Files) == 0 {
75 if err := tx.Commit(); err != nil {
76 return fmt.Errorf("commiting transaction: %w", err)
77 }
78 return nil
79 }
80
81 vals := make([]string, len(s.Files))
82 args := make([]any, 0, len(s.Files)*8)
83 for i, file := range s.Files {
84 vals[i] = "(?, ?, ?, ?, ?)"
85 args = append(args,
86 s.AtUri(),
87 file.Name,
88 file.Content.Ref.String(),
89 file.Content.Size,
90 file.Content.MimeType,
91 )
92 }
93 _, err = tx.Exec(
94 fmt.Sprintf(
95 `insert into string_files (
96 at_uri,
97 name,
98 content_ref,
99 content_size,
100 content_mimetype
101 )
102 values %s`,
103 strings.Join(vals, ","),
104 ),
105 args...,
106 )
107 if err != nil {
108 return fmt.Errorf("inserting files: %w", err)
109 }
110
111 if err := tx.Commit(); err != nil {
112 return fmt.Errorf("commiting transaction: %w", err)
113 }
114 return nil
115}
116
117func GetString(e Execer, filters ...orm.Filter) (models.String, error) {
118 strings, err := GetStrings(e, 0, filters...)
119 if err != nil {
120 return models.String{}, err
121 }
122 if len(strings) != 1 {
123 return models.String{}, sql.ErrNoRows
124 }
125 return strings[0], nil
126}
127
128func GetStrings(e Execer, limit int, filters ...orm.Filter) ([]models.String, error) {
129 stringMap := make(map[syntax.ATURI]*models.String)
130
131 var conditions []string
132 var args []any
133 for _, filter := range filters {
134 conditions = append(conditions, filter.Condition())
135 args = append(args, filter.Arg()...)
136 }
137
138 whereClause := ""
139 if conditions != nil {
140 whereClause = " where " + strings.Join(conditions, " and ")
141 }
142
143 limitClause := ""
144 if limit != 0 {
145 limitClause = fmt.Sprintf(" limit %d ", limit)
146 }
147
148 query := fmt.Sprintf(
149 `select
150 did,
151 rkey,
152 cid,
153 title,
154 description,
155 file_name,
156 file_content,
157 created,
158 edited
159 from strings
160 %s
161 order by created desc
162 %s`,
163 whereClause,
164 limitClause,
165 )
166
167 rows, err := e.Query(query, args...)
168
169 if err != nil {
170 return nil, err
171 }
172 defer rows.Close()
173
174 for rows.Next() {
175 var s models.String
176 var createdAt string
177 var cid, title, description, editedAt sql.Null[string]
178
179 if err := rows.Scan(
180 &s.Did,
181 &s.Rkey,
182 &cid,
183 &title,
184 &description,
185 &s.FileName,
186 &s.FileContent,
187 &createdAt,
188 &editedAt,
189 ); err != nil {
190 return nil, err
191 }
192
193 if cid.Valid {
194 s.Cid = new(syntax.CID)
195 *s.Cid = syntax.CID(cid.V)
196 }
197
198 if title.Valid {
199 s.Title = new(string)
200 *s.Title = title.V
201 }
202
203 if description.Valid {
204 s.Description = new(string)
205 *s.Description = description.V
206 }
207
208 s.Created, err = time.Parse(time.RFC3339, createdAt)
209 if err != nil {
210 s.Created = time.Now()
211 }
212
213 if editedAt.Valid {
214 e, err := time.Parse(time.RFC3339, editedAt.V)
215 if err != nil {
216 e = time.Now()
217 }
218 s.Edited = &e
219 }
220
221 s.Stats = &models.StringStats{}
222 stringMap[s.AtUri()] = &s
223 }
224
225 if err := rows.Err(); err != nil {
226 return nil, err
227 }
228
229 // if no strings, return early
230 if len(stringMap) == 0 {
231 return nil, nil
232 }
233
234 // build IN clause for related queries
235 inClause := strings.TrimSuffix(strings.Repeat("?, ", len(stringMap)), ", ")
236 args = make([]any, len(stringMap))
237 i := 0
238 for _, s := range stringMap {
239 args[i] = s.AtUri()
240 i++
241 }
242
243 // get files
244 {
245 rows, err := e.Query(
246 fmt.Sprintf(
247 `select
248 at_uri,
249 name,
250 content_ref,
251 content_size,
252 content_mimetype
253 from string_files
254 where at_uri in (%s) order by at_uri, id`,
255 inClause,
256 ),
257 args...,
258 )
259 if err != nil {
260 return nil, fmt.Errorf("failed to execute string_files query: %w", err)
261 }
262 defer rows.Close()
263
264 for rows.Next() {
265 var stringAt syntax.ATURI
266 var file models.String_File
267
268 var contentRef string
269 if err := rows.Scan(
270 &stringAt,
271 &file.Name,
272 &contentRef,
273 &file.Content.Size,
274 &file.Content.MimeType,
275 ); err != nil {
276 return nil, fmt.Errorf("failed to execute string_files query: %w", err)
277 }
278
279 file.Content.Ref = lexutil.LexLink(cid.MustParse(contentRef))
280
281 if s, ok := stringMap[stringAt]; ok {
282 s.Files = append(s.Files, file)
283 }
284 }
285 if err = rows.Err(); err != nil {
286 return nil, fmt.Errorf("failed to execute string_files query: %w", err)
287 }
288 }
289
290 // get star counts
291 {
292 rows, err := e.Query(
293 fmt.Sprintf(
294 `select subject, count(1) from stars where subject_type = 'string' and subject in (%s) group by subject`,
295 inClause,
296 ),
297 args...,
298 )
299 if err != nil {
300 return nil, fmt.Errorf("failed to execute star-count query: %w", err)
301 }
302 defer rows.Close()
303
304 for rows.Next() {
305 var stringAt syntax.ATURI
306 var count int
307 if err := rows.Scan(&stringAt, &count); err != nil {
308 continue
309 }
310 if s, ok := stringMap[stringAt]; ok {
311 s.Stats.StarCount = count
312 }
313 }
314 if err = rows.Err(); err != nil {
315 return nil, fmt.Errorf("failed to execute star-count query: %w", err)
316 }
317 }
318
319 var all []models.String
320 for _, s := range stringMap {
321 all = append(all, *s)
322 }
323
324 // sort by created timestamp (desc)
325 slices.SortFunc(all, func(a, b models.String) int {
326 if a.Created.After(b.Created) {
327 return -1
328 }
329 return 1
330 })
331
332 return all, nil
333}
334
335func CountStrings(e Execer, filters ...orm.Filter) (int64, error) {
336 var conditions []string
337 var args []any
338 for _, filter := range filters {
339 conditions = append(conditions, filter.Condition())
340 args = append(args, filter.Arg()...)
341 }
342
343 whereClause := ""
344 if conditions != nil {
345 whereClause = " where " + strings.Join(conditions, " and ")
346 }
347
348 repoQuery := fmt.Sprintf(`select count(1) from strings %s`, whereClause)
349 var count int64
350 err := e.QueryRow(repoQuery, args...).Scan(&count)
351
352 if !errors.Is(err, sql.ErrNoRows) && err != nil {
353 return 0, err
354 }
355
356 return count, nil
357}
358
359func DeleteString(e Execer, filters ...orm.Filter) error {
360 var conditions []string
361 var args []any
362 for _, filter := range filters {
363 conditions = append(conditions, filter.Condition())
364 args = append(args, filter.Arg()...)
365 }
366
367 whereClause := ""
368 if conditions != nil {
369 whereClause = " where " + strings.Join(conditions, " and ")
370 }
371
372 query := fmt.Sprintf(`delete from strings %s`, whereClause)
373
374 _, err := e.Exec(query, args...)
375 return err
376}