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
3import (
4 "time"
5)
6
7func (db *DB) CreateHighlight(h *Highlight) error {
8 _, err := db.Exec(db.Rebind(`
9 INSERT INTO highlights (uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid)
10 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
11 ON CONFLICT(uri) DO UPDATE SET
12 target_title = excluded.target_title,
13 selector_json = excluded.selector_json,
14 color = excluded.color,
15 tags_json = excluded.tags_json,
16 indexed_at = excluded.indexed_at,
17 cid = excluded.cid
18 `), h.URI, h.AuthorDID, h.TargetSource, h.TargetHash, h.TargetTitle, h.SelectorJSON, h.Color, h.TagsJSON, h.CreatedAt, h.IndexedAt, h.CID)
19 return err
20}
21
22func (db *DB) GetHighlightByURI(uri string) (*Highlight, error) {
23 var h Highlight
24 err := db.QueryRow(db.Rebind(`
25 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
26 FROM highlights
27 WHERE uri = ?
28 `), uri).Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID)
29 if err != nil {
30 return nil, err
31 }
32 return &h, nil
33}
34
35func (db *DB) GetRecentHighlights(limit, offset int) ([]Highlight, error) {
36 rows, err := db.Query(db.Rebind(`
37 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
38 FROM highlights
39 ORDER BY created_at DESC
40 LIMIT ? OFFSET ?
41 `), limit, offset)
42 if err != nil {
43 return nil, err
44 }
45 defer rows.Close()
46
47 var highlights []Highlight
48 for rows.Next() {
49 var h Highlight
50 if err := rows.Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID); err != nil {
51 return nil, err
52 }
53 highlights = append(highlights, h)
54 }
55 return highlights, nil
56}
57
58func (db *DB) GetHighlightsByTag(tag string, limit, offset int) ([]Highlight, error) {
59 pattern := "%\"" + tag + "\"%"
60 rows, err := db.Query(db.Rebind(`
61 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
62 FROM highlights
63 WHERE tags_json LIKE ?
64 ORDER BY created_at DESC
65 LIMIT ? OFFSET ?
66 `), pattern, limit, offset)
67 if err != nil {
68 return nil, err
69 }
70 defer rows.Close()
71
72 var highlights []Highlight
73 for rows.Next() {
74 var h Highlight
75 if err := rows.Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID); err != nil {
76 return nil, err
77 }
78 highlights = append(highlights, h)
79 }
80 return highlights, nil
81}
82
83func (db *DB) GetHighlightsByTagAndAuthor(tag, authorDID string, limit, offset int) ([]Highlight, error) {
84 pattern := "%\"" + tag + "\"%"
85 rows, err := db.Query(db.Rebind(`
86 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
87 FROM highlights
88 WHERE author_did = ? AND tags_json LIKE ?
89 ORDER BY created_at DESC
90 LIMIT ? OFFSET ?
91 `), authorDID, pattern, limit, offset)
92 if err != nil {
93 return nil, err
94 }
95 defer rows.Close()
96
97 var highlights []Highlight
98 for rows.Next() {
99 var h Highlight
100 if err := rows.Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID); err != nil {
101 return nil, err
102 }
103 highlights = append(highlights, h)
104 }
105 return highlights, nil
106}
107
108func (db *DB) GetHighlightsByTargetHash(targetHash string, limit, offset int) ([]Highlight, error) {
109 rows, err := db.Query(db.Rebind(`
110 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
111 FROM highlights
112 WHERE target_hash = ?
113 ORDER BY created_at DESC
114 LIMIT ? OFFSET ?
115 `), targetHash, limit, offset)
116 if err != nil {
117 return nil, err
118 }
119 defer rows.Close()
120
121 var highlights []Highlight
122 for rows.Next() {
123 var h Highlight
124 if err := rows.Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID); err != nil {
125 return nil, err
126 }
127 highlights = append(highlights, h)
128 }
129 return highlights, nil
130}
131
132func (db *DB) GetHighlightsByAuthor(authorDID string, limit, offset int) ([]Highlight, error) {
133 rows, err := db.Query(db.Rebind(`
134 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
135 FROM highlights
136 WHERE author_did = ?
137 ORDER BY created_at DESC
138 LIMIT ? OFFSET ?
139 `), authorDID, limit, offset)
140 if err != nil {
141 return nil, err
142 }
143 defer rows.Close()
144
145 var highlights []Highlight
146 for rows.Next() {
147 var h Highlight
148 if err := rows.Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID); err != nil {
149 return nil, err
150 }
151 highlights = append(highlights, h)
152 }
153 return highlights, nil
154}
155
156func (db *DB) GetHighlightsByAuthorAndTargetHash(authorDID, targetHash string, limit, offset int) ([]Highlight, error) {
157 rows, err := db.Query(db.Rebind(`
158 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
159 FROM highlights
160 WHERE author_did = ? AND target_hash = ?
161 ORDER BY created_at DESC
162 LIMIT ? OFFSET ?
163 `), authorDID, targetHash, limit, offset)
164 if err != nil {
165 return nil, err
166 }
167 defer rows.Close()
168
169 var highlights []Highlight
170 for rows.Next() {
171 var h Highlight
172 if err := rows.Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID); err != nil {
173 return nil, err
174 }
175 highlights = append(highlights, h)
176 }
177 return highlights, nil
178}
179
180func (db *DB) DeleteHighlight(uri string) error {
181 _, err := db.Exec(db.Rebind(`DELETE FROM highlights WHERE uri = ?`), uri)
182 return err
183}
184
185func (db *DB) UpdateHighlight(uri, color, tagsJSON, cid string) error {
186 _, err := db.Exec(db.Rebind(`
187 UPDATE highlights
188 SET color = ?, tags_json = ?, cid = ?, indexed_at = ?
189 WHERE uri = ?
190 `), color, tagsJSON, cid, time.Now(), uri)
191 return err
192}
193
194func (db *DB) GetHighlightsByURIs(uris []string) ([]Highlight, error) {
195 if len(uris) == 0 {
196 return []Highlight{}, nil
197 }
198
199 query := db.Rebind(`
200 SELECT uri, author_did, target_source, target_hash, target_title, selector_json, color, tags_json, created_at, indexed_at, cid
201 FROM highlights
202 WHERE uri IN (` + buildPlaceholders(len(uris)) + `)
203 `)
204
205 args := make([]interface{}, len(uris))
206 for i, uri := range uris {
207 args[i] = uri
208 }
209
210 rows, err := db.Query(query, args...)
211 if err != nil {
212 return nil, err
213 }
214 defer rows.Close()
215
216 var highlights []Highlight
217 for rows.Next() {
218 var h Highlight
219 if err := rows.Scan(&h.URI, &h.AuthorDID, &h.TargetSource, &h.TargetHash, &h.TargetTitle, &h.SelectorJSON, &h.Color, &h.TagsJSON, &h.CreatedAt, &h.IndexedAt, &h.CID); err != nil {
220 return nil, err
221 }
222 highlights = append(highlights, h)
223 }
224 return highlights, nil
225}
226
227func (db *DB) GetHighlightURIs(authorDID string) ([]string, error) {
228 rows, err := db.Query(db.Rebind(`
229 SELECT uri FROM highlights WHERE author_did = ?
230 `), authorDID)
231 if err != nil {
232 return nil, err
233 }
234 defer rows.Close()
235
236 var uris []string
237 for rows.Next() {
238 var uri string
239 if err := rows.Scan(&uri); err != nil {
240 return nil, err
241 }
242 uris = append(uris, uri)
243 }
244 return uris, nil
245}