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) CreateAnnotation(a *Annotation) error {
8 _, err := db.Exec(db.Rebind(`
9 INSERT INTO annotations (uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid)
10 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
11 ON CONFLICT(uri) DO UPDATE SET
12 motivation = excluded.motivation,
13 body_value = excluded.body_value,
14 body_format = excluded.body_format,
15 body_uri = excluded.body_uri,
16 target_title = excluded.target_title,
17 selector_json = excluded.selector_json,
18 tags_json = excluded.tags_json,
19 indexed_at = excluded.indexed_at,
20 cid = excluded.cid
21 `), a.URI, a.AuthorDID, a.Motivation, a.BodyValue, a.BodyFormat, a.BodyURI, a.TargetSource, a.TargetHash, a.TargetTitle, a.SelectorJSON, a.TagsJSON, a.CreatedAt, a.IndexedAt, a.CID)
22 return err
23}
24
25func (db *DB) GetAnnotationByURI(uri string) (*Annotation, error) {
26 var a Annotation
27 err := db.QueryRow(db.Rebind(`
28 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
29 FROM annotations
30 WHERE uri = ?
31 `), uri).Scan(&a.URI, &a.AuthorDID, &a.Motivation, &a.BodyValue, &a.BodyFormat, &a.BodyURI, &a.TargetSource, &a.TargetHash, &a.TargetTitle, &a.SelectorJSON, &a.TagsJSON, &a.CreatedAt, &a.IndexedAt, &a.CID)
32 if err != nil {
33 return nil, err
34 }
35 return &a, nil
36}
37
38func (db *DB) GetAnnotationsByTargetHash(targetHash string, limit, offset int) ([]Annotation, error) {
39 rows, err := db.Query(db.Rebind(`
40 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
41 FROM annotations
42 WHERE target_hash = ?
43 ORDER BY created_at DESC
44 LIMIT ? OFFSET ?
45 `), targetHash, limit, offset)
46 if err != nil {
47 return nil, err
48 }
49 defer rows.Close()
50
51 return scanAnnotations(rows)
52}
53
54func (db *DB) GetAnnotationsByAuthor(authorDID string, limit, offset int) ([]Annotation, error) {
55 rows, err := db.Query(db.Rebind(`
56 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
57 FROM annotations
58 WHERE author_did = ?
59 ORDER BY created_at DESC
60 LIMIT ? OFFSET ?
61 `), authorDID, limit, offset)
62 if err != nil {
63 return nil, err
64 }
65 defer rows.Close()
66
67 return scanAnnotations(rows)
68}
69
70func (db *DB) GetAnnotationsByMotivation(motivation string, limit, offset int) ([]Annotation, error) {
71 rows, err := db.Query(db.Rebind(`
72 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
73 FROM annotations
74 WHERE motivation = ?
75 ORDER BY created_at DESC
76 LIMIT ? OFFSET ?
77 `), motivation, limit, offset)
78 if err != nil {
79 return nil, err
80 }
81 defer rows.Close()
82
83 return scanAnnotations(rows)
84}
85
86func (db *DB) GetRecentAnnotations(limit, offset int) ([]Annotation, error) {
87 rows, err := db.Query(db.Rebind(`
88 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
89 FROM annotations
90 ORDER BY created_at DESC
91 LIMIT ? OFFSET ?
92 `), limit, offset)
93 if err != nil {
94 return nil, err
95 }
96 defer rows.Close()
97
98 return scanAnnotations(rows)
99}
100
101func (db *DB) GetAnnotationsByTag(tag string, limit, offset int) ([]Annotation, error) {
102 pattern := "%\"" + tag + "\"%"
103 rows, err := db.Query(db.Rebind(`
104 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
105 FROM annotations
106 WHERE tags_json LIKE ?
107 ORDER BY created_at DESC
108 LIMIT ? OFFSET ?
109 `), pattern, limit, offset)
110 if err != nil {
111 return nil, err
112 }
113 defer rows.Close()
114
115 return scanAnnotations(rows)
116}
117
118func (db *DB) DeleteAnnotation(uri string) error {
119 _, err := db.Exec(db.Rebind(`DELETE FROM annotations WHERE uri = ?`), uri)
120 return err
121}
122
123func (db *DB) UpdateAnnotation(uri, bodyValue, tagsJSON, cid string) error {
124 _, err := db.Exec(db.Rebind(`
125 UPDATE annotations
126 SET body_value = ?, tags_json = ?, cid = ?, indexed_at = ?
127 WHERE uri = ?
128 `), bodyValue, tagsJSON, cid, time.Now(), uri)
129 return err
130}
131
132func (db *DB) GetAnnotationsByTagAndAuthor(tag, authorDID string, limit, offset int) ([]Annotation, error) {
133 pattern := "%\"" + tag + "\"%"
134 rows, err := db.Query(db.Rebind(`
135 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
136 FROM annotations
137 WHERE author_did = ? AND tags_json LIKE ?
138 ORDER BY created_at DESC
139 LIMIT ? OFFSET ?
140 `), authorDID, pattern, limit, offset)
141 if err != nil {
142 return nil, err
143 }
144 defer rows.Close()
145
146 return scanAnnotations(rows)
147}
148
149func (db *DB) GetAnnotationsByAuthorAndTargetHash(authorDID, targetHash string, limit, offset int) ([]Annotation, error) {
150 rows, err := db.Query(db.Rebind(`
151 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
152 FROM annotations
153 WHERE author_did = ? AND target_hash = ?
154 ORDER BY created_at DESC
155 LIMIT ? OFFSET ?
156 `), authorDID, targetHash, limit, offset)
157 if err != nil {
158 return nil, err
159 }
160 defer rows.Close()
161
162 return scanAnnotations(rows)
163}
164
165func (db *DB) GetAnnotationsByURIs(uris []string) ([]Annotation, error) {
166 if len(uris) == 0 {
167 return []Annotation{}, nil
168 }
169
170 query := db.Rebind(`
171 SELECT uri, author_did, motivation, body_value, body_format, body_uri, target_source, target_hash, target_title, selector_json, tags_json, created_at, indexed_at, cid
172 FROM annotations
173 WHERE uri IN (` + buildPlaceholders(len(uris)) + `)
174 `)
175
176 args := make([]interface{}, len(uris))
177 for i, uri := range uris {
178 args[i] = uri
179 }
180
181 rows, err := db.Query(query, args...)
182 if err != nil {
183 return nil, err
184 }
185 defer rows.Close()
186
187 return scanAnnotations(rows)
188}
189
190func (db *DB) GetAnnotationURIs(authorDID string) ([]string, error) {
191 rows, err := db.Query(db.Rebind(`
192 SELECT uri FROM annotations WHERE author_did = ?
193 `), authorDID)
194 if err != nil {
195 return nil, err
196 }
197 defer rows.Close()
198
199 var uris []string
200 for rows.Next() {
201 var uri string
202 if err := rows.Scan(&uri); err != nil {
203 return nil, err
204 }
205 uris = append(uris, uri)
206 }
207 return uris, nil
208}