apps/feeds/db_subscriptions.go 3.6 K raw
1
package main
2
3
import (
4
	"database/sql"
5
	"errors"
6
	"time"
7
)
8
9
const subscriptionSelectColumns = `id, feed_url, title, site_url, favicon_url, category_id, etag, last_modified, last_fetched_at, last_error, added_at`
10
11
type Subscription struct {
12
	ID            int64
13
	FeedURL       string
14
	Title         string
15
	SiteURL       sql.NullString
16
	FaviconURL    sql.NullString
17
	CategoryID    sql.NullInt64
18
	ETag          sql.NullString
19
	LastModified  sql.NullString
20
	LastFetchedAt sql.NullString
21
	LastError     sql.NullString
22
	AddedAt       string
23
}
24
25
func listSubscriptions(db *sql.DB) ([]Subscription, error) {
26
	rows, err := db.Query(`SELECT ` + subscriptionSelectColumns + `
27
		FROM subscriptions ORDER BY title COLLATE NOCASE ASC`)
28
	if err != nil {
29
		return nil, err
30
	}
31
	defer rows.Close()
32
	var subs []Subscription
33
	for rows.Next() {
34
		s, err := scanSubscription(rows)
35
		if err != nil {
36
			return nil, err
37
		}
38
		subs = append(subs, *s)
39
	}
40
	return subs, rows.Err()
41
}
42
43
func getSubscriptionByURL(db *sql.DB, feedURL string) (*Subscription, error) {
44
	return querySubscription(db, `SELECT `+subscriptionSelectColumns+` FROM subscriptions WHERE feed_url = ?`, feedURL)
45
}
46
47
func insertSubscription(db *sql.DB, feedURL, title string, siteURL *string, categoryID *int64) (*Subscription, error) {
48
	res, err := db.Exec(`INSERT INTO subscriptions (feed_url, title, site_url, category_id) VALUES (?, ?, ?, ?)`, feedURL, title, siteURL, categoryID)
49
	if err != nil {
50
		return nil, err
51
	}
52
	id, err := res.LastInsertId()
53
	if err != nil {
54
		return nil, err
55
	}
56
	return getSubscription(db, id)
57
}
58
59
func getSubscription(db *sql.DB, id int64) (*Subscription, error) {
60
	return querySubscription(db, `SELECT `+subscriptionSelectColumns+` FROM subscriptions WHERE id = ?`, id)
61
}
62
63
func updateSubscriptionMeta(db *sql.DB, id int64, etag, lastModified *string, lastError *string) error {
64
	_, err := db.Exec(`UPDATE subscriptions SET etag = ?, last_modified = ?, last_fetched_at = ?, last_error = ? WHERE id = ?`,
65
		nullableString(etag), nullableString(lastModified), time.Now().UTC().Format("2006-01-02 15:04:05"), nullableString(lastError), id)
66
	return err
67
}
68
69
func updateSubscriptionTitle(db *sql.DB, id int64, title string) error {
70
	_, err := db.Exec(`UPDATE subscriptions SET title = ? WHERE id = ?`, title, id)
71
	return err
72
}
73
74
func updateSubscriptionSiteURL(db *sql.DB, id int64, siteURL *string) error {
75
	_, err := db.Exec(`UPDATE subscriptions SET site_url = ? WHERE id = ?`, nullableString(siteURL), id)
76
	return err
77
}
78
79
func updateSubscriptionFavicon(db *sql.DB, id int64, favicon *string) error {
80
	_, err := db.Exec(`UPDATE subscriptions SET favicon_url = ? WHERE id = ?`, nullableString(favicon), id)
81
	return err
82
}
83
84
func updateSubscriptionCategory(db *sql.DB, id int64, categoryID *int64) error {
85
	_, err := db.Exec(`UPDATE subscriptions SET category_id = ? WHERE id = ?`, nullableInt64(categoryID), id)
86
	return err
87
}
88
89
func deleteSubscription(db *sql.DB, id int64) (bool, error) {
90
	res, err := db.Exec(`DELETE FROM subscriptions WHERE id = ?`, id)
91
	if err != nil {
92
		return false, err
93
	}
94
	n, _ := res.RowsAffected()
95
	return n > 0, nil
96
}
97
98
func querySubscription(db *sql.DB, query string, args ...any) (*Subscription, error) {
99
	return scanSubscription(db.QueryRow(query, args...))
100
}
101
102
func scanSubscription(scanner interface{ Scan(dest ...any) error }) (*Subscription, error) {
103
	var s Subscription
104
	err := scanner.Scan(&s.ID, &s.FeedURL, &s.Title, &s.SiteURL, &s.FaviconURL, &s.CategoryID, &s.ETag, &s.LastModified, &s.LastFetchedAt, &s.LastError, &s.AddedAt)
105
	if errors.Is(err, sql.ErrNoRows) {
106
		return nil, nil
107
	}
108
	if err != nil {
109
		return nil, err
110
	}
111
	return &s, nil
112
}