cli + tui to publish to leaflet (wip) & manage tasks, notes & watch/read lists 馃崈
charm
leaflet
readability
golang
1package repo
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "slices"
8 "strings"
9 "time"
10
11 "github.com/stormlightlabs/noteleaf/internal/models"
12)
13
14func NoteNotFoundError(id int64) error {
15 return fmt.Errorf("note with id %d not found", id)
16}
17
18// NoteRepository provides database operations for notes
19type NoteRepository struct {
20 db *sql.DB
21}
22
23// NewNoteRepository creates a new note repository
24func NewNoteRepository(db *sql.DB) *NoteRepository {
25 return &NoteRepository{db: db}
26}
27
28// NoteListOptions defines filtering options for listing notes
29type NoteListOptions struct {
30 Tags []string
31 Archived *bool
32 Title string
33 Content string
34 Limit int
35 Offset int
36}
37
38func (r *NoteRepository) scanNote(s scanner) (*models.Note, error) {
39 var note models.Note
40 var tags string
41 err := s.Scan(¬e.ID, ¬e.Title, ¬e.Content, &tags, ¬e.Archived,
42 ¬e.Created, ¬e.Modified, ¬e.FilePath, ¬e.LeafletRKey,
43 ¬e.LeafletCID, ¬e.PublishedAt, ¬e.IsDraft)
44 if err != nil {
45 return nil, err
46 }
47
48 if err := note.UnmarshalTags(tags); err != nil {
49 return nil, UnmarshalTagsError(err)
50 }
51
52 return ¬e, nil
53}
54
55func (r *NoteRepository) queryOne(ctx context.Context, query string, args ...any) (*models.Note, error) {
56 row := r.db.QueryRowContext(ctx, query, args...)
57 note, err := r.scanNote(row)
58 if err != nil {
59 if err == sql.ErrNoRows {
60 return nil, fmt.Errorf("note not found")
61 }
62 return nil, fmt.Errorf("failed to scan note: %w", err)
63 }
64 return note, nil
65}
66
67func (r *NoteRepository) queryMany(ctx context.Context, query string, args ...any) ([]*models.Note, error) {
68 rows, err := r.db.QueryContext(ctx, query, args...)
69 if err != nil {
70 return nil, fmt.Errorf("failed to query notes: %w", err)
71 }
72 defer rows.Close()
73
74 var notes []*models.Note
75 for rows.Next() {
76 note, err := r.scanNote(rows)
77 if err != nil {
78 return nil, fmt.Errorf("failed to scan note: %w", err)
79 }
80 notes = append(notes, note)
81 }
82
83 if err := rows.Err(); err != nil {
84 return nil, fmt.Errorf("error iterating over notes: %w", err)
85 }
86
87 return notes, nil
88}
89
90// Create stores a new note and returns its assigned ID
91func (r *NoteRepository) Create(ctx context.Context, note *models.Note) (int64, error) {
92 now := time.Now()
93 note.Created = now
94 note.Modified = now
95
96 tags, err := note.MarshalTags()
97 if err != nil {
98 return 0, fmt.Errorf("failed to marshal tags: %w", err)
99 }
100
101 result, err := r.db.ExecContext(ctx, queryNoteInsert,
102 note.Title, note.Content, tags, note.Archived, note.Created, note.Modified, note.FilePath,
103 note.LeafletRKey, note.LeafletCID, note.PublishedAt, note.IsDraft)
104 if err != nil {
105 return 0, fmt.Errorf("failed to insert note: %w", err)
106 }
107
108 id, err := result.LastInsertId()
109 if err != nil {
110 return 0, fmt.Errorf("failed to get last insert id: %w", err)
111 }
112
113 note.ID = id
114 return id, nil
115}
116
117// Get retrieves a note by its ID
118func (r *NoteRepository) Get(ctx context.Context, id int64) (*models.Note, error) {
119 note, err := r.queryOne(ctx, queryNoteByID, id)
120 if err != nil {
121 return nil, NoteNotFoundError(id)
122 }
123 return note, nil
124}
125
126// Update modifies an existing note
127func (r *NoteRepository) Update(ctx context.Context, note *models.Note) error {
128 note.Modified = time.Now()
129
130 tags, err := note.MarshalTags()
131 if err != nil {
132 return fmt.Errorf("failed to marshal tags: %w", err)
133 }
134
135 result, err := r.db.ExecContext(ctx, queryNoteUpdate,
136 note.Title, note.Content, tags, note.Archived, note.Modified, note.FilePath,
137 note.LeafletRKey, note.LeafletCID, note.PublishedAt, note.IsDraft, note.ID)
138 if err != nil {
139 return fmt.Errorf("failed to update note: %w", err)
140 }
141
142 rowsAffected, err := result.RowsAffected()
143 if err != nil {
144 return fmt.Errorf("failed to get rows affected: %w", err)
145 }
146
147 if rowsAffected == 0 {
148 return NoteNotFoundError(note.ID)
149 }
150
151 return nil
152}
153
154// Delete removes a note by its ID
155func (r *NoteRepository) Delete(ctx context.Context, id int64) error {
156 result, err := r.db.ExecContext(ctx, queryNoteDelete, id)
157 if err != nil {
158 return fmt.Errorf("failed to delete note: %w", err)
159 }
160
161 rowsAffected, err := result.RowsAffected()
162 if err != nil {
163 return fmt.Errorf("failed to get rows affected: %w", err)
164 }
165
166 if rowsAffected == 0 {
167 return NoteNotFoundError(id)
168 }
169
170 return nil
171}
172
173func (r *NoteRepository) buildListQuery(options NoteListOptions) (string, []any) {
174 query := queryNotesList
175 args := []any{}
176 conditions := []string{}
177
178 if options.Archived != nil {
179 conditions = append(conditions, "archived = ?")
180 args = append(args, *options.Archived)
181 }
182
183 if options.Title != "" {
184 conditions = append(conditions, "title LIKE ?")
185 args = append(args, "%"+options.Title+"%")
186 }
187
188 if options.Content != "" {
189 conditions = append(conditions, "content LIKE ?")
190 args = append(args, "%"+options.Content+"%")
191 }
192
193 if len(conditions) > 0 {
194 query += " WHERE " + strings.Join(conditions, " AND ")
195 }
196
197 query += " ORDER BY modified DESC"
198
199 if options.Limit > 0 {
200 query += fmt.Sprintf(" LIMIT %d", options.Limit)
201 if options.Offset > 0 {
202 query += fmt.Sprintf(" OFFSET %d", options.Offset)
203 }
204 }
205
206 return query, args
207}
208
209// List retrieves notes with optional filtering
210func (r *NoteRepository) List(ctx context.Context, options NoteListOptions) ([]*models.Note, error) {
211 query, args := r.buildListQuery(options)
212 return r.queryMany(ctx, query, args...)
213}
214
215// GetByTitle searches for notes by title pattern
216func (r *NoteRepository) GetByTitle(ctx context.Context, title string) ([]*models.Note, error) {
217 return r.List(ctx, NoteListOptions{Title: title})
218}
219
220// GetArchived retrieves all archived notes
221func (r *NoteRepository) GetArchived(ctx context.Context) ([]*models.Note, error) {
222 archived := true
223 return r.List(ctx, NoteListOptions{Archived: &archived})
224}
225
226// GetActive retrieves all non-archived notes
227func (r *NoteRepository) GetActive(ctx context.Context) ([]*models.Note, error) {
228 archived := false
229 return r.List(ctx, NoteListOptions{Archived: &archived})
230}
231
232// Archive marks a note as archived
233func (r *NoteRepository) Archive(ctx context.Context, id int64) error {
234 note, err := r.Get(ctx, id)
235 if err != nil {
236 return err
237 }
238
239 note.Archived = true
240 return r.Update(ctx, note)
241}
242
243// Unarchive marks a note as not archived
244func (r *NoteRepository) Unarchive(ctx context.Context, id int64) error {
245 note, err := r.Get(ctx, id)
246 if err != nil {
247 return err
248 }
249
250 note.Archived = false
251 return r.Update(ctx, note)
252}
253
254// SearchContent searches for notes containing the specified text in content
255func (r *NoteRepository) SearchContent(ctx context.Context, searchText string) ([]*models.Note, error) {
256 return r.List(ctx, NoteListOptions{Content: searchText})
257}
258
259// GetRecent retrieves the most recently modified notes
260func (r *NoteRepository) GetRecent(ctx context.Context, limit int) ([]*models.Note, error) {
261 return r.List(ctx, NoteListOptions{Limit: limit})
262}
263
264// AddTag adds a tag to a note
265func (r *NoteRepository) AddTag(ctx context.Context, id int64, tag string) error {
266 note, err := r.Get(ctx, id)
267 if err != nil {
268 return err
269 }
270
271 if slices.Contains(note.Tags, tag) {
272 return nil
273 }
274
275 note.Tags = append(note.Tags, tag)
276 return r.Update(ctx, note)
277}
278
279// RemoveTag removes a tag from a note
280func (r *NoteRepository) RemoveTag(ctx context.Context, id int64, tag string) error {
281 note, err := r.Get(ctx, id)
282 if err != nil {
283 return err
284 }
285 for i, existingTag := range note.Tags {
286 if existingTag == tag {
287 note.Tags = append(note.Tags[:i], note.Tags[i+1:]...)
288 break
289 }
290 }
291 return r.Update(ctx, note)
292}
293
294func (r *NoteRepository) buildTagsQuery(tags []string) (string, []any) {
295 conditions := make([]string, len(tags))
296 args := make([]any, len(tags))
297 for i, tag := range tags {
298 conditions[i] = "tags LIKE ?"
299 args[i] = "%\"" + tag + "\"%"
300 }
301 return fmt.Sprintf(`SELECT %s FROM notes WHERE %s ORDER BY modified DESC`, noteColumns, strings.Join(conditions, " OR ")), args
302}
303
304// GetByTags retrieves notes that have any of the specified tags
305func (r *NoteRepository) GetByTags(ctx context.Context, tags []string) ([]*models.Note, error) {
306 if len(tags) == 0 {
307 return []*models.Note{}, nil
308 }
309 query, args := r.buildTagsQuery(tags)
310 return r.queryMany(ctx, query, args...)
311}
312
313// GetByLeafletRKey returns a note by its leaflet record key
314func (r *NoteRepository) GetByLeafletRKey(ctx context.Context, rkey string) (*models.Note, error) {
315 query := "SELECT " + noteColumns + " FROM notes WHERE leaflet_rkey = ?"
316 return r.queryOne(ctx, query, rkey)
317}
318
319// ListPublished returns all published leaflet notes (not drafts)
320func (r *NoteRepository) ListPublished(ctx context.Context) ([]*models.Note, error) {
321 query := "SELECT " + noteColumns + " FROM notes WHERE leaflet_rkey IS NOT NULL AND is_draft = false ORDER BY published_at DESC"
322 return r.queryMany(ctx, query)
323}
324
325// ListDrafts returns all draft leaflet notes
326func (r *NoteRepository) ListDrafts(ctx context.Context) ([]*models.Note, error) {
327 query := "SELECT " + noteColumns + " FROM notes WHERE leaflet_rkey IS NOT NULL AND is_draft = true ORDER BY modified DESC"
328 return r.queryMany(ctx, query)
329}
330
331// GetLeafletNotes returns all notes with leaflet association (both published and drafts)
332func (r *NoteRepository) GetLeafletNotes(ctx context.Context) ([]*models.Note, error) {
333 query := "SELECT " + noteColumns + " FROM notes WHERE leaflet_rkey IS NOT NULL ORDER BY modified DESC"
334 return r.queryMany(ctx, query)
335}
336
337// GetNewestPublication returns the most recently published leaflet note
338func (r *NoteRepository) GetNewestPublication(ctx context.Context) (*models.Note, error) {
339 query := "SELECT " + noteColumns + " FROM notes WHERE leaflet_rkey IS NOT NULL ORDER BY published_at DESC LIMIT 1"
340 return r.queryOne(ctx, query)
341}
342
343// DeleteAllLeafletNotes removes all notes with leaflet associations
344func (r *NoteRepository) DeleteAllLeafletNotes(ctx context.Context) error {
345 _, err := r.db.ExecContext(ctx, "DELETE FROM notes WHERE leaflet_rkey IS NOT NULL")
346 if err != nil {
347 return fmt.Errorf("failed to delete leaflet notes: %w", err)
348 }
349 return nil
350}