apps/feeds/db_items.go 3.6 K raw
1
package main
2
3
import (
4
	"database/sql"
5
	"strings"
6
)
7
8
type ItemWithFeed struct {
9
	ID             int64   `json:"id"`
10
	SubscriptionID int64   `json:"subscription_id"`
11
	GUID           string  `json:"guid"`
12
	Title          string  `json:"title"`
13
	Link           string  `json:"link"`
14
	Author         *string `json:"author,omitempty"`
15
	PublishedAt    int64   `json:"published_at"`
16
	IsRead         bool    `json:"is_read"`
17
	FetchedAt      string  `json:"fetched_at"`
18
	FeedTitle      string  `json:"feed_title"`
19
	FeedURL        string  `json:"feed_url"`
20
	CategoryID     *int64  `json:"category_id,omitempty"`
21
	CategoryName   *string `json:"category_name,omitempty"`
22
}
23
24
type ListItemsFilter struct {
25
	Limit          int
26
	UnreadOnly     bool
27
	CategoryID     *int64
28
	SubscriptionID *int64
29
}
30
31
type NewItem struct {
32
	SubscriptionID int64
33
	GUID           string
34
	Title          string
35
	Link           string
36
	Author         string
37
	PublishedAt    int64
38
}
39
40
func listItems(db *sql.DB, filter ListItemsFilter) ([]ItemWithFeed, error) {
41
	limit := filter.Limit
42
	if limit <= 0 {
43
		limit = 100
44
	}
45
	if limit > 1000 {
46
		limit = 1000
47
	}
48
	var b strings.Builder
49
	b.WriteString(`SELECT i.id, i.subscription_id, i.guid, i.title, i.link, i.author, i.published_at,
50
		i.is_read, i.fetched_at, s.title, s.feed_url, s.category_id, c.name
51
		FROM items i
52
		JOIN subscriptions s ON s.id = i.subscription_id
53
		LEFT JOIN categories c ON c.id = s.category_id
54
		WHERE 1=1`)
55
	args := []any{}
56
	if filter.UnreadOnly {
57
		b.WriteString(" AND i.is_read = 0")
58
	}
59
	if filter.CategoryID != nil {
60
		b.WriteString(" AND s.category_id = ?")
61
		args = append(args, *filter.CategoryID)
62
	}
63
	if filter.SubscriptionID != nil {
64
		b.WriteString(" AND i.subscription_id = ?")
65
		args = append(args, *filter.SubscriptionID)
66
	}
67
	b.WriteString(" ORDER BY i.published_at DESC, i.id DESC LIMIT ?")
68
	args = append(args, limit)
69
	rows, err := db.Query(b.String(), args...)
70
	if err != nil {
71
		return nil, err
72
	}
73
	defer rows.Close()
74
	var items []ItemWithFeed
75
	for rows.Next() {
76
		var it ItemWithFeed
77
		var author sql.NullString
78
		var categoryID sql.NullInt64
79
		var categoryName sql.NullString
80
		var isRead int
81
		if err := rows.Scan(&it.ID, &it.SubscriptionID, &it.GUID, &it.Title, &it.Link, &author, &it.PublishedAt, &isRead, &it.FetchedAt, &it.FeedTitle, &it.FeedURL, &categoryID, &categoryName); err != nil {
82
			return nil, err
83
		}
84
		if author.Valid {
85
			it.Author = &author.String
86
		}
87
		if categoryID.Valid {
88
			v := categoryID.Int64
89
			it.CategoryID = &v
90
		}
91
		if categoryName.Valid {
92
			v := categoryName.String
93
			it.CategoryName = &v
94
		}
95
		it.IsRead = isRead != 0
96
		items = append(items, it)
97
	}
98
	return items, rows.Err()
99
}
100
101
func insertItemIgnoreDup(db *sql.DB, item NewItem) (bool, error) {
102
	res, err := db.Exec(`INSERT OR IGNORE INTO items (subscription_id, guid, title, link, author, published_at) VALUES (?, ?, ?, ?, ?, ?)`,
103
		item.SubscriptionID, item.GUID, item.Title, item.Link, nullableString(stringPtr(strings.TrimSpace(item.Author))), item.PublishedAt)
104
	if err != nil {
105
		return false, err
106
	}
107
	n, _ := res.RowsAffected()
108
	return n > 0, nil
109
}
110
111
func pruneSubscription(db *sql.DB, subscriptionID int64, keepN int) error {
112
	_, err := db.Exec(`DELETE FROM items
113
		WHERE subscription_id = ?
114
		  AND id NOT IN (
115
			SELECT id FROM items WHERE subscription_id = ? ORDER BY published_at DESC, id DESC LIMIT ?
116
		)`, subscriptionID, subscriptionID, keepN)
117
	return err
118
}
119
120
func markItemRead(db *sql.DB, id int64, isRead bool) (bool, error) {
121
	val := 0
122
	if isRead {
123
		val = 1
124
	}
125
	res, err := db.Exec(`UPDATE items SET is_read = ? WHERE id = ?`, val, id)
126
	if err != nil {
127
		return false, err
128
	}
129
	n, _ := res.RowsAffected()
130
	return n > 0, nil
131
}