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) CreateBookmark(b *Bookmark) error {
8 _, err := db.Exec(db.Rebind(`
9 INSERT INTO bookmarks (uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid)
10 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
11 ON CONFLICT(uri) DO UPDATE SET
12 title = excluded.title,
13 description = excluded.description,
14 tags_json = excluded.tags_json,
15 indexed_at = excluded.indexed_at,
16 cid = excluded.cid
17 `), b.URI, b.AuthorDID, b.Source, b.SourceHash, b.Title, b.Description, b.TagsJSON, b.CreatedAt, b.IndexedAt, b.CID)
18 return err
19}
20
21func (db *DB) GetBookmarkByURI(uri string) (*Bookmark, error) {
22 var b Bookmark
23 err := db.QueryRow(db.Rebind(`
24 SELECT uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid
25 FROM bookmarks
26 WHERE uri = ?
27 `), uri).Scan(&b.URI, &b.AuthorDID, &b.Source, &b.SourceHash, &b.Title, &b.Description, &b.TagsJSON, &b.CreatedAt, &b.IndexedAt, &b.CID)
28 if err != nil {
29 return nil, err
30 }
31 return &b, nil
32}
33
34func (db *DB) GetRecentBookmarks(limit, offset int) ([]Bookmark, error) {
35 rows, err := db.Query(db.Rebind(`
36 SELECT uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid
37 FROM bookmarks
38 ORDER BY created_at DESC
39 LIMIT ? OFFSET ?
40 `), limit, offset)
41 if err != nil {
42 return nil, err
43 }
44 defer rows.Close()
45
46 var bookmarks []Bookmark
47 for rows.Next() {
48 var b Bookmark
49 if err := rows.Scan(&b.URI, &b.AuthorDID, &b.Source, &b.SourceHash, &b.Title, &b.Description, &b.TagsJSON, &b.CreatedAt, &b.IndexedAt, &b.CID); err != nil {
50 return nil, err
51 }
52 bookmarks = append(bookmarks, b)
53 }
54 return bookmarks, nil
55}
56
57func (db *DB) GetBookmarksByTag(tag string, limit, offset int) ([]Bookmark, error) {
58 pattern := "%\"" + tag + "\"%"
59 rows, err := db.Query(db.Rebind(`
60 SELECT uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid
61 FROM bookmarks
62 WHERE tags_json LIKE ?
63 ORDER BY created_at DESC
64 LIMIT ? OFFSET ?
65 `), pattern, limit, offset)
66 if err != nil {
67 return nil, err
68 }
69 defer rows.Close()
70
71 var bookmarks []Bookmark
72 for rows.Next() {
73 var b Bookmark
74 if err := rows.Scan(&b.URI, &b.AuthorDID, &b.Source, &b.SourceHash, &b.Title, &b.Description, &b.TagsJSON, &b.CreatedAt, &b.IndexedAt, &b.CID); err != nil {
75 return nil, err
76 }
77 bookmarks = append(bookmarks, b)
78 }
79 return bookmarks, nil
80}
81
82func (db *DB) GetBookmarksByTagAndAuthor(tag, authorDID string, limit, offset int) ([]Bookmark, error) {
83 pattern := "%\"" + tag + "\"%"
84 rows, err := db.Query(db.Rebind(`
85 SELECT uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid
86 FROM bookmarks
87 WHERE author_did = ? AND tags_json LIKE ?
88 ORDER BY created_at DESC
89 LIMIT ? OFFSET ?
90 `), authorDID, pattern, limit, offset)
91 if err != nil {
92 return nil, err
93 }
94 defer rows.Close()
95
96 var bookmarks []Bookmark
97 for rows.Next() {
98 var b Bookmark
99 if err := rows.Scan(&b.URI, &b.AuthorDID, &b.Source, &b.SourceHash, &b.Title, &b.Description, &b.TagsJSON, &b.CreatedAt, &b.IndexedAt, &b.CID); err != nil {
100 return nil, err
101 }
102 bookmarks = append(bookmarks, b)
103 }
104 return bookmarks, nil
105}
106
107func (db *DB) GetBookmarksByAuthor(authorDID string, limit, offset int) ([]Bookmark, error) {
108 rows, err := db.Query(db.Rebind(`
109 SELECT uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid
110 FROM bookmarks
111 WHERE author_did = ?
112 ORDER BY created_at DESC
113 LIMIT ? OFFSET ?
114 `), authorDID, limit, offset)
115 if err != nil {
116 return nil, err
117 }
118 defer rows.Close()
119
120 var bookmarks []Bookmark
121 for rows.Next() {
122 var b Bookmark
123 if err := rows.Scan(&b.URI, &b.AuthorDID, &b.Source, &b.SourceHash, &b.Title, &b.Description, &b.TagsJSON, &b.CreatedAt, &b.IndexedAt, &b.CID); err != nil {
124 return nil, err
125 }
126 bookmarks = append(bookmarks, b)
127 }
128 return bookmarks, nil
129}
130
131func (db *DB) DeleteBookmark(uri string) error {
132 _, err := db.Exec(db.Rebind(`DELETE FROM bookmarks WHERE uri = ?`), uri)
133 return err
134}
135
136func (db *DB) UpdateBookmark(uri, title, description, tagsJSON, cid string) error {
137 _, err := db.Exec(db.Rebind(`
138 UPDATE bookmarks
139 SET title = ?, description = ?, tags_json = ?, cid = ?, indexed_at = ?
140 WHERE uri = ?
141 `), title, description, tagsJSON, cid, time.Now(), uri)
142 return err
143}
144
145func (db *DB) GetBookmarksByURIs(uris []string) ([]Bookmark, error) {
146 if len(uris) == 0 {
147 return []Bookmark{}, nil
148 }
149
150 query := db.Rebind(`
151 SELECT uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid
152 FROM bookmarks
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 bookmarks []Bookmark
168 for rows.Next() {
169 var b Bookmark
170 if err := rows.Scan(&b.URI, &b.AuthorDID, &b.Source, &b.SourceHash, &b.Title, &b.Description, &b.TagsJSON, &b.CreatedAt, &b.IndexedAt, &b.CID); err != nil {
171 return nil, err
172 }
173 bookmarks = append(bookmarks, b)
174 }
175 return bookmarks, nil
176}
177
178func (db *DB) GetBookmarkURIs(authorDID string) ([]string, error) {
179 rows, err := db.Query(db.Rebind(`
180 SELECT uri FROM bookmarks WHERE author_did = ?
181 `), authorDID)
182 if err != nil {
183 return nil, err
184 }
185 defer rows.Close()
186
187 var uris []string
188 for rows.Next() {
189 var uri string
190 if err := rows.Scan(&uri); err != nil {
191 return nil, err
192 }
193 uris = append(uris, uri)
194 }
195 return uris, nil
196}
197
198func (db *DB) GetBookmarksByTargetHash(targetHash string, limit, offset int) ([]Bookmark, error) {
199 rows, err := db.Query(db.Rebind(`
200 SELECT uri, author_did, source, source_hash, title, description, tags_json, created_at, indexed_at, cid
201 FROM bookmarks
202 WHERE source_hash = ?
203 ORDER BY created_at DESC
204 LIMIT ? OFFSET ?
205 `), targetHash, limit, offset)
206 if err != nil {
207 return nil, err
208 }
209 defer rows.Close()
210
211 var bookmarks []Bookmark
212 for rows.Next() {
213 var b Bookmark
214 if err := rows.Scan(&b.URI, &b.AuthorDID, &b.Source, &b.SourceHash, &b.Title, &b.Description, &b.TagsJSON, &b.CreatedAt, &b.IndexedAt, &b.CID); err != nil {
215 return nil, err
216 }
217 bookmarks = append(bookmarks, b)
218 }
219 return bookmarks, nil
220}