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
20func GetSpindles(e Execer, filters ...filter) ([]Spindle, error) {
21 var spindles []Spindle
22
23 var conditions []string
24 var args []any
25 for _, filter := range filters {
26 conditions = append(conditions, filter.Condition())
27 args = append(args, filter.arg)
28 }
29
30 whereClause := ""
31 if conditions != nil {
32 whereClause = " where " + strings.Join(conditions, " and ")
33 }
34
35 query := fmt.Sprintf(
36 `select id, owner, instance, verified, created
37 from spindles
38 %s
39 order by created
40 `,
41 whereClause,
42 )
43
44 rows, err := e.Query(query, args...)
45
46 if err != nil {
47 return nil, err
48 }
49 defer rows.Close()
50
51 for rows.Next() {
52 var spindle Spindle
53 var createdAt string
54 var verified sql.NullString
55
56 if err := rows.Scan(
57 &spindle.Id,
58 &spindle.Owner,
59 &spindle.Instance,
60 &verified,
61 &createdAt,
62 ); err != nil {
63 return nil, err
64 }
65
66 spindle.Created, err = time.Parse(time.RFC3339, createdAt)
67 if err != nil {
68 spindle.Created = time.Now()
69 }
70
71 if verified.Valid {
72 t, err := time.Parse(time.RFC3339, verified.String)
73 if err != nil {
74 now := time.Now()
75 spindle.Verified = &now
76 }
77 spindle.Verified = &t
78 }
79
80 spindles = append(spindles, spindle)
81 }
82
83 return spindles, nil
84}
85
86// if there is an existing spindle with the same instance, this returns an error
87func AddSpindle(e Execer, spindle Spindle) error {
88 _, err := e.Exec(
89 `insert into spindles (owner, instance) values (?, ?)`,
90 spindle.Owner,
91 spindle.Instance,
92 )
93 return err
94}
95
96func VerifySpindle(e Execer, filters ...filter) (int64, error) {
97 var conditions []string
98 var args []any
99 for _, filter := range filters {
100 conditions = append(conditions, filter.Condition())
101 args = append(args, filter.arg)
102 }
103
104 whereClause := ""
105 if conditions != nil {
106 whereClause = " where " + strings.Join(conditions, " and ")
107 }
108
109 query := fmt.Sprintf(`update spindles set verified = strftime('%%Y-%%m-%%dT%%H:%%M:%%SZ', 'now') %s`, whereClause)
110
111 res, err := e.Exec(query, args...)
112 if err != nil {
113 return 0, err
114 }
115
116 return res.RowsAffected()
117}
118
119func DeleteSpindle(e Execer, filters ...filter) error {
120 var conditions []string
121 var args []any
122 for _, filter := range filters {
123 conditions = append(conditions, filter.Condition())
124 args = append(args, filter.arg)
125 }
126
127 whereClause := ""
128 if conditions != nil {
129 whereClause = " where " + strings.Join(conditions, " and ")
130 }
131
132 query := fmt.Sprintf(`delete from spindles %s`, whereClause)
133
134 _, err := e.Exec(query, args...)
135 return err
136}