margin.at
Write on the margins of the internet. Powered by the AT Protocol.
margin.at
extension
web
atproto
comments
scanash.com
1package db
2
3func (db *DB) CreateReply(r *Reply) error {
4 _, err := db.Exec(db.Rebind(`
5 INSERT INTO replies (uri, author_did, parent_uri, root_uri, text, format, created_at, indexed_at, cid)
6 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
7 ON CONFLICT(uri) DO UPDATE SET
8 text = excluded.text,
9 format = excluded.format,
10 indexed_at = excluded.indexed_at,
11 cid = excluded.cid
12 `), r.URI, r.AuthorDID, r.ParentURI, r.RootURI, r.Text, r.Format, r.CreatedAt, r.IndexedAt, r.CID)
13 return err
14}
15
16func (db *DB) GetRepliesByRoot(rootURI string) ([]Reply, error) {
17 rows, err := db.Query(db.Rebind(`
18 SELECT uri, author_did, parent_uri, root_uri, text, format, created_at, indexed_at, cid
19 FROM replies
20 WHERE root_uri = ?
21 ORDER BY created_at ASC
22 `), rootURI)
23 if err != nil {
24 return nil, err
25 }
26 defer rows.Close()
27
28 var replies []Reply
29 for rows.Next() {
30 var r Reply
31 if err := rows.Scan(&r.URI, &r.AuthorDID, &r.ParentURI, &r.RootURI, &r.Text, &r.Format, &r.CreatedAt, &r.IndexedAt, &r.CID); err != nil {
32 return nil, err
33 }
34 replies = append(replies, r)
35 }
36 return replies, nil
37}
38
39func (db *DB) GetReplyByURI(uri string) (*Reply, error) {
40 var r Reply
41 err := db.QueryRow(db.Rebind(`
42 SELECT uri, author_did, parent_uri, root_uri, text, format, created_at, indexed_at, cid
43 FROM replies
44 WHERE uri = ?
45 `), uri).Scan(&r.URI, &r.AuthorDID, &r.ParentURI, &r.RootURI, &r.Text, &r.Format, &r.CreatedAt, &r.IndexedAt, &r.CID)
46 if err != nil {
47 return nil, err
48 }
49 return &r, nil
50}
51
52func (db *DB) DeleteReply(uri string) error {
53 _, err := db.Exec(db.Rebind(`DELETE FROM replies WHERE uri = ?`), uri)
54 return err
55}
56
57func (db *DB) GetRepliesByAuthor(authorDID string) ([]Reply, error) {
58 rows, err := db.Query(db.Rebind(`
59 SELECT uri, author_did, parent_uri, root_uri, text, format, created_at, indexed_at, cid
60 FROM replies
61 WHERE author_did = ?
62 ORDER BY created_at DESC
63 `), authorDID)
64 if err != nil {
65 return nil, err
66 }
67 defer rows.Close()
68
69 var replies []Reply
70 for rows.Next() {
71 var r Reply
72 if err := rows.Scan(&r.URI, &r.AuthorDID, &r.ParentURI, &r.RootURI, &r.Text, &r.Format, &r.CreatedAt, &r.IndexedAt, &r.CID); err != nil {
73 return nil, err
74 }
75 replies = append(replies, r)
76 }
77 return replies, nil
78}
79
80func (db *DB) GetOrphanedRepliesByAuthor(authorDID string) ([]Reply, error) {
81 rows, err := db.Query(db.Rebind(`
82 SELECT r.uri, r.author_did, r.parent_uri, r.root_uri, r.text, r.format, r.created_at, r.indexed_at, r.cid
83 FROM replies r
84 LEFT JOIN annotations a ON r.root_uri = a.uri
85 WHERE r.author_did = ? AND a.uri IS NULL
86 `), authorDID)
87 if err != nil {
88 return nil, err
89 }
90 defer rows.Close()
91
92 var replies []Reply
93 for rows.Next() {
94 var r Reply
95 if err := rows.Scan(&r.URI, &r.AuthorDID, &r.ParentURI, &r.RootURI, &r.Text, &r.Format, &r.CreatedAt, &r.IndexedAt, &r.CID); err != nil {
96 return nil, err
97 }
98 replies = append(replies, r)
99 }
100 return replies, nil
101}
102
103func (db *DB) GetReplyCount(rootURI string) (int, error) {
104 var count int
105 err := db.QueryRow(db.Rebind(`SELECT COUNT(*) FROM replies WHERE root_uri = ?`), rootURI).Scan(&count)
106 return count, err
107}
108
109func (db *DB) GetReplyCounts(rootURIs []string) (map[string]int, error) {
110 if len(rootURIs) == 0 {
111 return map[string]int{}, nil
112 }
113
114 query := db.Rebind(`
115 SELECT root_uri, COUNT(*)
116 FROM replies
117 WHERE root_uri IN (` + buildPlaceholders(len(rootURIs)) + `)
118 GROUP BY root_uri
119 `)
120
121 args := make([]interface{}, len(rootURIs))
122 for i, uri := range rootURIs {
123 args[i] = uri
124 }
125
126 rows, err := db.Query(query, args...)
127 if err != nil {
128 return nil, err
129 }
130 defer rows.Close()
131
132 counts := make(map[string]int)
133 for rows.Next() {
134 var uri string
135 var count int
136 if err := rows.Scan(&uri, &count); err != nil {
137 return nil, err
138 }
139 counts[uri] = count
140 }
141
142 return counts, nil
143}
144
145func (db *DB) GetRepliesByURIs(uris []string) ([]Reply, error) {
146 if len(uris) == 0 {
147 return []Reply{}, nil
148 }
149
150 query := db.Rebind(`
151 SELECT uri, author_did, parent_uri, root_uri, text, format, created_at, indexed_at, cid
152 FROM replies
153 WHERE uri IN (` + buildPlaceholders(len(uris)) + `)
154 `)
155
156 args := make([]interface{}, len(uris))
157 for i, uri := range uris {
158 args[i] = uri
159 }
160
161 rows, err := db.Query(query, args...)
162 if err != nil {
163 return nil, err
164 }
165 defer rows.Close()
166
167 var replies []Reply
168 for rows.Next() {
169 var r Reply
170 if err := rows.Scan(&r.URI, &r.AuthorDID, &r.ParentURI, &r.RootURI, &r.Text, &r.Format, &r.CreatedAt, &r.IndexedAt, &r.CID); err != nil {
171 return nil, err
172 }
173 replies = append(replies, r)
174 }
175 return replies, nil
176}