dunkirk.sh
rss email digests over ssh because you're a cool kid
herald.dunkirk.sh
go
rss
rss-reader
ssh
charm
1package store
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "time"
9)
10
11type SeenItem struct {
12 ID int64
13 FeedID int64
14 GUID string
15 Title sql.NullString
16 Link sql.NullString
17 SeenAt time.Time
18}
19
20func (db *DB) MarkItemSeen(ctx context.Context, feedID int64, guid, title, link string) error {
21 var titleVal, linkVal sql.NullString
22 if title != "" {
23 titleVal = sql.NullString{String: title, Valid: true}
24 }
25 if link != "" {
26 linkVal = sql.NullString{String: link, Valid: true}
27 }
28
29 _, err := db.stmts.markItemSeen.ExecContext(ctx, feedID, guid, titleVal, linkVal)
30 if err != nil {
31 return fmt.Errorf("mark item seen: %w", err)
32 }
33 return nil
34}
35
36func (db *DB) IsItemSeen(ctx context.Context, feedID int64, guid string) (bool, error) {
37 var id int64
38 err := db.stmts.isItemSeen.QueryRowContext(ctx, feedID, guid).Scan(&id)
39 if err != nil {
40 if errors.Is(err, sql.ErrNoRows) {
41 return false, nil
42 }
43 return false, fmt.Errorf("check item seen: %w", err)
44 }
45 return true, nil
46}
47
48func (db *DB) MarkItemSeenTx(ctx context.Context, tx *sql.Tx, feedID int64, guid, title, link string) error {
49 var titleVal, linkVal sql.NullString
50 if title != "" {
51 titleVal = sql.NullString{String: title, Valid: true}
52 }
53 if link != "" {
54 linkVal = sql.NullString{String: link, Valid: true}
55 }
56
57 _, err := tx.ExecContext(ctx,
58 `INSERT INTO seen_items (feed_id, guid, title, link) VALUES (?, ?, ?, ?)
59 ON CONFLICT(feed_id, guid) DO UPDATE SET title = excluded.title, link = excluded.link`,
60 feedID, guid, titleVal, linkVal,
61 )
62 if err != nil {
63 return fmt.Errorf("mark item seen: %w", err)
64 }
65 return nil
66}
67
68func (db *DB) GetSeenItems(ctx context.Context, feedID int64, limit int) ([]*SeenItem, error) {
69 rows, err := db.stmts.getSeenItems.QueryContext(ctx, feedID, limit)
70 if err != nil {
71 return nil, fmt.Errorf("query seen items: %w", err)
72 }
73 defer func() { _ = rows.Close() }()
74
75 var items []*SeenItem
76 for rows.Next() {
77 var item SeenItem
78 if err := rows.Scan(&item.ID, &item.FeedID, &item.GUID, &item.Title, &item.Link, &item.SeenAt); err != nil {
79 return nil, fmt.Errorf("scan seen item: %w", err)
80 }
81 items = append(items, &item)
82 }
83 return items, rows.Err()
84}
85
86// GetSeenGUIDs returns a set of GUIDs that have been seen for a given feed
87func (db *DB) GetSeenGUIDs(ctx context.Context, feedID int64, guids []string) (map[string]bool, error) {
88 if len(guids) == 0 {
89 return make(map[string]bool), nil
90 }
91
92 // Build the query with the appropriate number of placeholders
93 args := make([]interface{}, 0, len(guids)+1)
94 args = append(args, feedID)
95
96 placeholders := "?"
97 for i := 0; i < len(guids)-1; i++ {
98 placeholders += ",?"
99 }
100 for _, guid := range guids {
101 args = append(args, guid)
102 }
103
104 query := fmt.Sprintf(
105 `SELECT guid FROM seen_items WHERE feed_id = ? AND guid IN (%s)`,
106 placeholders,
107 )
108
109 rows, err := db.QueryContext(ctx, query, args...)
110 if err != nil {
111 return nil, fmt.Errorf("query seen guids: %w", err)
112 }
113 defer func() { _ = rows.Close() }()
114
115 seenSet := make(map[string]bool)
116 for rows.Next() {
117 var guid string
118 if err := rows.Scan(&guid); err != nil {
119 return nil, fmt.Errorf("scan guid: %w", err)
120 }
121 seenSet[guid] = true
122 }
123
124 return seenSet, rows.Err()
125}
126
127// CleanupOldSeenItems deletes seen items older than the specified duration
128func (db *DB) CleanupOldSeenItems(ctx context.Context, olderThan time.Duration) (int64, error) {
129 cutoff := time.Now().Add(-olderThan)
130 result, err := db.stmts.cleanupSeenItems.ExecContext(ctx, cutoff)
131 if err != nil {
132 return 0, fmt.Errorf("cleanup old seen items: %w", err)
133 }
134
135 deleted, err := result.RowsAffected()
136 if err != nil {
137 return 0, fmt.Errorf("get rows affected: %w", err)
138 }
139
140 return deleted, nil
141}