feed.go 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485
  1. // SPDX-FileCopyrightText: Copyright The Miniflux Authors. All rights reserved.
  2. // SPDX-License-Identifier: Apache-2.0
  3. package storage // import "miniflux.app/v2/internal/storage"
  4. import (
  5. "database/sql"
  6. "errors"
  7. "fmt"
  8. "sort"
  9. "time"
  10. "miniflux.app/v2/internal/config"
  11. "miniflux.app/v2/internal/model"
  12. )
  13. type byStateAndName struct{ f model.Feeds }
  14. func (l byStateAndName) Len() int { return len(l.f) }
  15. func (l byStateAndName) Swap(i, j int) { l.f[i], l.f[j] = l.f[j], l.f[i] }
  16. func (l byStateAndName) Less(i, j int) bool {
  17. // disabled test first, since we don't care about errors if disabled
  18. if l.f[i].Disabled != l.f[j].Disabled {
  19. return l.f[j].Disabled
  20. }
  21. if l.f[i].ParsingErrorCount != l.f[j].ParsingErrorCount {
  22. return l.f[i].ParsingErrorCount > l.f[j].ParsingErrorCount
  23. }
  24. if l.f[i].UnreadCount != l.f[j].UnreadCount {
  25. return l.f[i].UnreadCount > l.f[j].UnreadCount
  26. }
  27. return l.f[i].Title < l.f[j].Title
  28. }
  29. // FeedExists checks if the given feed exists. The returned error is
  30. // non-nil only for a genuine query/connection failure; a feed that doesn't
  31. // exist is reported as (false, nil), matching the underlying sql.ErrNoRows
  32. // case.
  33. func (s *Storage) FeedExists(userID, feedID int64) (bool, error) {
  34. var result bool
  35. query := `SELECT true FROM feeds WHERE user_id=$1 AND id=$2 LIMIT 1`
  36. if err := s.db.QueryRow(query, userID, feedID).Scan(&result); err != nil && !errors.Is(err, sql.ErrNoRows) {
  37. return false, fmt.Errorf(`store: unable to check if feed exists: %w`, err)
  38. }
  39. return result, nil
  40. }
  41. // CheckedAt returns when the feed was last checked.
  42. func (s *Storage) CheckedAt(userID, feedID int64) (time.Time, error) {
  43. var result time.Time
  44. query := `SELECT checked_at FROM feeds WHERE user_id=$1 AND id=$2 LIMIT 1`
  45. err := s.db.QueryRow(query, userID, feedID).Scan(&result)
  46. if err != nil {
  47. return time.Now(), err
  48. }
  49. return result, nil
  50. }
  51. // CategoryFeedExists returns true if the given feed exists and belongs to
  52. // the given category. The returned error is non-nil only for a genuine
  53. // query/connection failure; see FeedExists above for the same convention.
  54. func (s *Storage) CategoryFeedExists(userID, categoryID, feedID int64) (bool, error) {
  55. var result bool
  56. query := `SELECT true FROM feeds WHERE user_id=$1 AND category_id=$2 AND id=$3 LIMIT 1`
  57. if err := s.db.QueryRow(query, userID, categoryID, feedID).Scan(&result); err != nil && !errors.Is(err, sql.ErrNoRows) {
  58. return false, fmt.Errorf(`store: unable to check if feed exists in category: %w`, err)
  59. }
  60. return result, nil
  61. }
  62. // FeedURLExists returns true if the given feed URL already exists for the user.
  63. func (s *Storage) FeedURLExists(userID int64, feedURL string) bool {
  64. var result bool
  65. query := `SELECT true FROM feeds WHERE user_id=$1 AND feed_url=$2 LIMIT 1`
  66. s.db.QueryRow(query, userID, feedURL).Scan(&result)
  67. return result
  68. }
  69. // AnotherFeedURLExists returns true if another feed with the same URL exists for the user.
  70. func (s *Storage) AnotherFeedURLExists(userID, feedID int64, feedURL string) bool {
  71. var result bool
  72. query := `SELECT true FROM feeds WHERE id <> $1 AND user_id=$2 AND feed_url=$3 LIMIT 1`
  73. s.db.QueryRow(query, feedID, userID, feedURL).Scan(&result)
  74. return result
  75. }
  76. // CountAllFeeds returns the number of feeds keyed by enabled, disabled, and total.
  77. func (s *Storage) CountAllFeeds() (map[string]int64, error) {
  78. rows, err := s.db.Query(`SELECT disabled, count(*) FROM feeds GROUP BY disabled`)
  79. if err != nil {
  80. return nil, fmt.Errorf("storage: unable to count feeds: %w", err)
  81. }
  82. defer rows.Close()
  83. results := map[string]int64{
  84. "enabled": 0,
  85. "disabled": 0,
  86. "total": 0,
  87. }
  88. for rows.Next() {
  89. var disabled bool
  90. var count int64
  91. if err := rows.Scan(&disabled, &count); err != nil {
  92. continue
  93. }
  94. if disabled {
  95. results["disabled"] = count
  96. } else {
  97. results["enabled"] = count
  98. }
  99. }
  100. results["total"] = results["disabled"] + results["enabled"]
  101. return results, nil
  102. }
  103. // CountAllFeedsWithErrors returns the number of feeds with parsing errors.
  104. func (s *Storage) CountAllFeedsWithErrors() (int, error) {
  105. pollingParsingErrorLimit := config.Opts.PollingParsingErrorLimit()
  106. if pollingParsingErrorLimit <= 0 {
  107. pollingParsingErrorLimit = 1
  108. }
  109. query := `SELECT count(*) FROM feeds WHERE parsing_error_count >= $1`
  110. var result int
  111. err := s.db.QueryRow(query, pollingParsingErrorLimit).Scan(&result)
  112. if err != nil {
  113. return 0, fmt.Errorf("storage: unable to count feeds with errors: %w", err)
  114. }
  115. return result, nil
  116. }
  117. // Feeds returns all feeds that belong to the given user.
  118. func (s *Storage) Feeds(userID int64) (model.Feeds, error) {
  119. return s.NewFeedQueryBuilder(userID).
  120. WithSorting(model.DefaultFeedSorting, model.DefaultFeedSortingDirection).
  121. GetFeeds()
  122. }
  123. func getFeedsSorted(builder *feedQueryBuilder) (model.Feeds, error) {
  124. result, err := builder.GetFeeds()
  125. if err == nil {
  126. sort.Sort(byStateAndName{result})
  127. return result, nil
  128. }
  129. return result, err
  130. }
  131. // FeedsWithCounters returns all feeds of the given user with read and unread entry counters.
  132. func (s *Storage) FeedsWithCounters(userID int64) (model.Feeds, error) {
  133. return getFeedsSorted(s.NewFeedQueryBuilder(userID).
  134. WithCounters().
  135. WithSorting(model.DefaultFeedSorting, model.DefaultFeedSortingDirection))
  136. }
  137. // FetchCounters returns the per-feed read and unread entry counts for the given user.
  138. func (s *Storage) FetchCounters(userID int64) (model.FeedCounters, error) {
  139. reads, unreads, err := s.NewFeedQueryBuilder(userID).
  140. WithCounters().
  141. fetchFeedCounter()
  142. return model.FeedCounters{ReadCounters: reads, UnreadCounters: unreads}, err
  143. }
  144. // FeedsByCategoryWithCounters returns all feeds in the given category for the given user with read and unread entry counters.
  145. func (s *Storage) FeedsByCategoryWithCounters(userID, categoryID int64) (model.Feeds, error) {
  146. return getFeedsSorted(s.NewFeedQueryBuilder(userID).
  147. WithCategoryID(categoryID).
  148. WithCounters().
  149. WithSorting(model.DefaultFeedSorting, model.DefaultFeedSortingDirection))
  150. }
  151. // WeeklyFeedEntryCount returns the weekly entry count for a feed.
  152. func (s *Storage) WeeklyFeedEntryCount(userID, feedID int64) (int, error) {
  153. // Calculate a virtual weekly count based on the average updating frequency.
  154. // This helps after just adding a high volume feed.
  155. // Return 0 when the 'count(*)' is zero(0) or one(1).
  156. query := `
  157. SELECT
  158. COALESCE(CAST(CEIL(
  159. (EXTRACT(epoch from interval '1 week')) /
  160. NULLIF((EXTRACT(epoch from (max(published_at)-min(published_at))/NULLIF((count(*)-1), 0) )), 0)
  161. ) AS BIGINT), 0)
  162. FROM
  163. entries
  164. WHERE
  165. entries.user_id=$1 AND
  166. entries.feed_id=$2 AND
  167. entries.published_at >= now() - interval '1 week';
  168. `
  169. var weeklyCount int
  170. err := s.db.QueryRow(query, userID, feedID).Scan(&weeklyCount)
  171. switch {
  172. case errors.Is(err, sql.ErrNoRows):
  173. return 0, nil
  174. case err != nil:
  175. return 0, fmt.Errorf(`store: unable to fetch weekly count for feed #%d: %v`, feedID, err)
  176. }
  177. return weeklyCount, nil
  178. }
  179. // FeedByID returns the feed with the given ID.
  180. func (s *Storage) FeedByID(userID, feedID int64) (*model.Feed, error) {
  181. feed, err := s.NewFeedQueryBuilder(userID).
  182. WithFeedID(feedID).
  183. GetFeed()
  184. switch {
  185. case errors.Is(err, sql.ErrNoRows):
  186. return nil, nil
  187. case err != nil:
  188. return nil, fmt.Errorf(`store: unable to fetch feed #%d: %v`, feedID, err)
  189. }
  190. return feed, nil
  191. }
  192. // CreateFeed creates a new feed.
  193. func (s *Storage) CreateFeed(feed *model.Feed) error {
  194. sql := `
  195. INSERT INTO feeds (
  196. feed_url,
  197. site_url,
  198. title,
  199. category_id,
  200. user_id,
  201. etag_header,
  202. last_modified_header,
  203. crawler,
  204. user_agent,
  205. cookie,
  206. username,
  207. password,
  208. disabled,
  209. scraper_rules,
  210. rewrite_rules,
  211. blocklist_rules,
  212. keeplist_rules,
  213. block_filter_entry_rules,
  214. keep_filter_entry_rules,
  215. ignore_http_cache,
  216. allow_self_signed_certificates,
  217. fetch_via_proxy,
  218. hide_globally,
  219. url_rewrite_rules,
  220. no_media_player,
  221. apprise_service_urls,
  222. webhook_url,
  223. disable_http2,
  224. description,
  225. proxy_url,
  226. ignore_entry_updates,
  227. language
  228. )
  229. VALUES
  230. ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32)
  231. RETURNING
  232. id
  233. `
  234. err := s.db.QueryRow(
  235. sql,
  236. feed.FeedURL,
  237. feed.SiteURL,
  238. feed.Title,
  239. feed.Category.ID,
  240. feed.UserID,
  241. feed.EtagHeader,
  242. feed.LastModifiedHeader,
  243. feed.Crawler,
  244. feed.UserAgent,
  245. feed.Cookie,
  246. feed.Username,
  247. feed.Password,
  248. feed.Disabled,
  249. feed.ScraperRules,
  250. feed.RewriteRules,
  251. feed.BlocklistRules,
  252. feed.KeeplistRules,
  253. feed.BlockFilterEntryRules,
  254. feed.KeepFilterEntryRules,
  255. feed.IgnoreHTTPCache,
  256. feed.AllowSelfSignedCertificates,
  257. feed.FetchViaProxy,
  258. feed.HideGlobally,
  259. feed.UrlRewriteRules,
  260. feed.NoMediaPlayer,
  261. feed.AppriseServiceURLs,
  262. feed.WebhookURL,
  263. feed.DisableHTTP2,
  264. feed.Description,
  265. feed.ProxyURL,
  266. feed.IgnoreEntryUpdates,
  267. feed.Language,
  268. ).Scan(&feed.ID)
  269. if err != nil {
  270. return fmt.Errorf(`store: unable to create feed %q: %v`, feed.FeedURL, err)
  271. }
  272. for _, entry := range feed.Entries {
  273. entry.FeedID = feed.ID
  274. entry.UserID = feed.UserID
  275. tx, err := s.db.Begin()
  276. if err != nil {
  277. return fmt.Errorf(`store: unable to start transaction: %v`, err)
  278. }
  279. entryExists, err := s.entryExists(tx, entry)
  280. if err != nil {
  281. if rollbackErr := tx.Rollback(); rollbackErr != nil {
  282. return fmt.Errorf(`store: unable to rollback transaction: %v (rolled back due to: %v)`, rollbackErr, err)
  283. }
  284. return err
  285. }
  286. if !entryExists {
  287. if err := s.createEntry(tx, entry); err != nil {
  288. if rollbackErr := tx.Rollback(); rollbackErr != nil {
  289. return fmt.Errorf(`store: unable to rollback transaction: %v (rolled back due to: %v)`, rollbackErr, err)
  290. }
  291. return err
  292. }
  293. }
  294. if err := tx.Commit(); err != nil {
  295. return fmt.Errorf(`store: unable to commit transaction: %v`, err)
  296. }
  297. }
  298. return nil
  299. }
  300. // UpdateFeed updates an existing feed.
  301. func (s *Storage) UpdateFeed(feed *model.Feed) (err error) {
  302. query := `
  303. UPDATE
  304. feeds
  305. SET
  306. feed_url=$1,
  307. site_url=$2,
  308. title=$3,
  309. category_id=$4,
  310. etag_header=$5,
  311. last_modified_header=$6,
  312. checked_at=$7,
  313. parsing_error_msg=$8,
  314. parsing_error_count=$9,
  315. scraper_rules=$10,
  316. rewrite_rules=$11,
  317. blocklist_rules=$12,
  318. keeplist_rules=$13,
  319. block_filter_entry_rules=$14,
  320. keep_filter_entry_rules=$15,
  321. crawler=$16,
  322. user_agent=$17,
  323. cookie=$18,
  324. username=$19,
  325. password=$20,
  326. disabled=$21,
  327. next_check_at=$22,
  328. ignore_http_cache=$23,
  329. allow_self_signed_certificates=$24,
  330. fetch_via_proxy=$25,
  331. hide_globally=$26,
  332. url_rewrite_rules=$27,
  333. no_media_player=$28,
  334. apprise_service_urls=$29,
  335. webhook_url=$30,
  336. disable_http2=$31,
  337. description=$32,
  338. ntfy_enabled=$33,
  339. ntfy_priority=$34,
  340. ntfy_topic=$35,
  341. pushover_enabled=$36,
  342. pushover_priority=$37,
  343. proxy_url=$38,
  344. ignore_entry_updates=$39,
  345. language=$40
  346. WHERE
  347. id=$41 AND user_id=$42
  348. `
  349. _, err = s.db.Exec(query,
  350. feed.FeedURL,
  351. feed.SiteURL,
  352. feed.Title,
  353. feed.Category.ID,
  354. feed.EtagHeader,
  355. feed.LastModifiedHeader,
  356. feed.CheckedAt,
  357. feed.ParsingErrorMsg,
  358. feed.ParsingErrorCount,
  359. feed.ScraperRules,
  360. feed.RewriteRules,
  361. feed.BlocklistRules,
  362. feed.KeeplistRules,
  363. feed.BlockFilterEntryRules,
  364. feed.KeepFilterEntryRules,
  365. feed.Crawler,
  366. feed.UserAgent,
  367. feed.Cookie,
  368. feed.Username,
  369. feed.Password,
  370. feed.Disabled,
  371. feed.NextCheckAt,
  372. feed.IgnoreHTTPCache,
  373. feed.AllowSelfSignedCertificates,
  374. feed.FetchViaProxy,
  375. feed.HideGlobally,
  376. feed.UrlRewriteRules,
  377. feed.NoMediaPlayer,
  378. feed.AppriseServiceURLs,
  379. feed.WebhookURL,
  380. feed.DisableHTTP2,
  381. feed.Description,
  382. feed.NtfyEnabled,
  383. feed.NtfyPriority,
  384. feed.NtfyTopic,
  385. feed.PushoverEnabled,
  386. feed.PushoverPriority,
  387. feed.ProxyURL,
  388. feed.IgnoreEntryUpdates,
  389. feed.Language,
  390. feed.ID,
  391. feed.UserID,
  392. )
  393. if err != nil {
  394. return fmt.Errorf(`store: unable to update feed #%d (%s): %v`, feed.ID, feed.FeedURL, err)
  395. }
  396. return nil
  397. }
  398. // UpdateFeedError persists the parsing error fields for the given feed.
  399. func (s *Storage) UpdateFeedError(feed *model.Feed) (err error) {
  400. query := `
  401. UPDATE
  402. feeds
  403. SET
  404. parsing_error_msg=$1,
  405. parsing_error_count=$2,
  406. checked_at=$3,
  407. next_check_at=$4
  408. WHERE
  409. id=$5 AND user_id=$6
  410. `
  411. _, err = s.db.Exec(query,
  412. feed.ParsingErrorMsg,
  413. feed.ParsingErrorCount,
  414. feed.CheckedAt,
  415. feed.NextCheckAt,
  416. feed.ID,
  417. feed.UserID,
  418. )
  419. if err != nil {
  420. return fmt.Errorf(`store: unable to update feed error #%d (%s): %v`, feed.ID, feed.FeedURL, err)
  421. }
  422. return nil
  423. }
  424. // RemoveFeed removes the given feed along with its entries and enclosures.
  425. func (s *Storage) RemoveFeed(userID, feedID int64) error {
  426. if _, err := s.db.Exec(`DELETE FROM feeds WHERE id=$1 AND user_id=$2`, feedID, userID); err != nil {
  427. return fmt.Errorf(`store: unable to delete feed #%d: %v`, feedID, err)
  428. }
  429. return nil
  430. }
  431. // ResetFeedErrors clears the parsing error fields for all feeds.
  432. func (s *Storage) ResetFeedErrors() error {
  433. _, err := s.db.Exec(`UPDATE feeds SET parsing_error_count=0, parsing_error_msg=''`)
  434. return err
  435. }
  436. // ResetNextCheckAt schedules all feeds to be checked immediately.
  437. func (s *Storage) ResetNextCheckAt() error {
  438. _, err := s.db.Exec(`UPDATE feeds SET next_check_at=now()`)
  439. return err
  440. }