4
0

category.go 8.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300
  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. "github.com/lib/pq"
  9. "miniflux.app/v2/internal/model"
  10. )
  11. // ErrNoCategory is returned when a user does not have any category.
  12. var ErrNoCategory = errors.New("store: no category found")
  13. // AnotherCategoryExists checks if another category exists with the same title.
  14. func (s *Storage) AnotherCategoryExists(userID, categoryID int64, title string) bool {
  15. var result bool
  16. query := `SELECT true FROM categories WHERE user_id=$1 AND id != $2 AND lower(title)=lower($3) LIMIT 1`
  17. s.db.QueryRow(query, userID, categoryID, title).Scan(&result)
  18. return result
  19. }
  20. // CategoryTitleExists checks if the given category exists into the database.
  21. func (s *Storage) CategoryTitleExists(userID int64, title string) bool {
  22. var result bool
  23. query := `SELECT true FROM categories WHERE user_id=$1 AND lower(title)=lower($2) LIMIT 1`
  24. s.db.QueryRow(query, userID, title).Scan(&result)
  25. return result
  26. }
  27. // CategoryIDExists checks if the given category exists into the database.
  28. // The returned error is non-nil only for a genuine query/connection failure;
  29. // a category that doesn't exist is reported as (false, nil), matching the
  30. // underlying sql.ErrNoRows case.
  31. func (s *Storage) CategoryIDExists(userID, categoryID int64) (bool, error) {
  32. var result bool
  33. query := `SELECT true FROM categories WHERE user_id=$1 AND id=$2 LIMIT 1`
  34. if err := s.db.QueryRow(query, userID, categoryID).Scan(&result); err != nil && !errors.Is(err, sql.ErrNoRows) {
  35. return false, fmt.Errorf(`store: unable to check if category exists: %w`, err)
  36. }
  37. return result, nil
  38. }
  39. // Category returns a category from the database.
  40. func (s *Storage) Category(userID, categoryID int64) (*model.Category, error) {
  41. var category model.Category
  42. query := `SELECT id, user_id, title, hide_globally FROM categories WHERE user_id=$1 AND id=$2`
  43. err := s.db.QueryRow(query, userID, categoryID).Scan(&category.ID, &category.UserID, &category.Title, &category.HideGlobally)
  44. switch {
  45. case errors.Is(err, sql.ErrNoRows):
  46. return nil, nil
  47. case err != nil:
  48. return nil, fmt.Errorf(`store: unable to fetch category: %v`, err)
  49. default:
  50. return &category, nil
  51. }
  52. }
  53. // FirstCategory returns the first category for the given user.
  54. // It returns ErrNoCategory if the user does not have any category.
  55. func (s *Storage) FirstCategory(userID int64) (*model.Category, error) {
  56. query := `SELECT id, user_id, title, hide_globally FROM categories WHERE user_id=$1 ORDER BY title ASC LIMIT 1`
  57. var category model.Category
  58. err := s.db.QueryRow(query, userID).Scan(&category.ID, &category.UserID, &category.Title, &category.HideGlobally)
  59. switch {
  60. case errors.Is(err, sql.ErrNoRows):
  61. return nil, ErrNoCategory
  62. case err != nil:
  63. return nil, fmt.Errorf(`store: unable to fetch category: %v`, err)
  64. default:
  65. return &category, nil
  66. }
  67. }
  68. // CategoryByTitle finds a category by its case-insensitive title, preferring an exact match.
  69. func (s *Storage) CategoryByTitle(userID int64, title string) (*model.Category, error) {
  70. var category model.Category
  71. query := `SELECT id, user_id, title, hide_globally FROM categories WHERE user_id=$1 AND lower(title)=lower($2) ORDER BY title=$2 DESC, id ASC LIMIT 1`
  72. err := s.db.QueryRow(query, userID, title).Scan(&category.ID, &category.UserID, &category.Title, &category.HideGlobally)
  73. switch {
  74. case errors.Is(err, sql.ErrNoRows):
  75. return nil, nil
  76. case err != nil:
  77. return nil, fmt.Errorf(`store: unable to fetch category: %v`, err)
  78. default:
  79. return &category, nil
  80. }
  81. }
  82. // Categories returns all categories that belongs to the given user.
  83. func (s *Storage) Categories(userID int64) (model.Categories, error) {
  84. query := `SELECT id, user_id, title, hide_globally FROM categories WHERE user_id=$1 ORDER BY title ASC`
  85. rows, err := s.db.Query(query, userID)
  86. if err != nil {
  87. return nil, fmt.Errorf(`store: unable to fetch categories: %v`, err)
  88. }
  89. defer rows.Close()
  90. categories := make(model.Categories, 0)
  91. for rows.Next() {
  92. var category model.Category
  93. if err := rows.Scan(&category.ID, &category.UserID, &category.Title, &category.HideGlobally); err != nil {
  94. return nil, fmt.Errorf(`store: unable to fetch category row: %v`, err)
  95. }
  96. categories = append(categories, category)
  97. }
  98. return categories, nil
  99. }
  100. // CategoriesWithFeedCount returns all categories with the number of feeds, sorted according to sortOrder.
  101. func (s *Storage) CategoriesWithFeedCount(userID int64, sortOrder string) (model.Categories, error) {
  102. query := `
  103. SELECT
  104. c.id,
  105. c.user_id,
  106. c.title,
  107. c.hide_globally,
  108. coalesce(fc.feed_count, 0),
  109. coalesce(uc.unread_count, 0)
  110. FROM categories c
  111. LEFT JOIN (
  112. SELECT category_id, count(*) AS feed_count
  113. FROM feeds
  114. WHERE user_id = $2
  115. GROUP BY category_id
  116. ) fc ON fc.category_id = c.id
  117. LEFT JOIN (
  118. SELECT f.category_id, count(*) AS unread_count
  119. FROM entries e
  120. INNER JOIN feeds f ON f.id = e.feed_id
  121. WHERE e.user_id = $2 AND e.status = $1
  122. GROUP BY f.category_id
  123. ) uc ON uc.category_id = c.id
  124. WHERE
  125. c.user_id=$2
  126. `
  127. if sortOrder == "alphabetical" {
  128. query += `
  129. ORDER BY
  130. c.title ASC
  131. `
  132. } else {
  133. query += `
  134. ORDER BY
  135. coalesce(uc.unread_count, 0) DESC,
  136. c.title ASC
  137. `
  138. }
  139. rows, err := s.db.Query(query, model.EntryStatusUnread, userID)
  140. if err != nil {
  141. return nil, fmt.Errorf(`store: unable to fetch categories: %v`, err)
  142. }
  143. defer rows.Close()
  144. categories := make(model.Categories, 0)
  145. for rows.Next() {
  146. var category model.Category
  147. if err := rows.Scan(&category.ID, &category.UserID, &category.Title, &category.HideGlobally, &category.FeedCount, &category.TotalUnread); err != nil {
  148. return nil, fmt.Errorf(`store: unable to fetch category row: %v`, err)
  149. }
  150. categories = append(categories, category)
  151. }
  152. return categories, nil
  153. }
  154. // CreateCategory creates a new category.
  155. func (s *Storage) CreateCategory(userID int64, request *model.CategoryCreationRequest) (*model.Category, error) {
  156. var category model.Category
  157. query := `
  158. INSERT INTO categories
  159. (user_id, title, hide_globally)
  160. VALUES
  161. ($1, $2, $3)
  162. RETURNING
  163. id,
  164. user_id,
  165. title,
  166. hide_globally
  167. `
  168. err := s.db.QueryRow(
  169. query,
  170. userID,
  171. request.Title,
  172. request.HideGlobally,
  173. ).Scan(
  174. &category.ID,
  175. &category.UserID,
  176. &category.Title,
  177. &category.HideGlobally,
  178. )
  179. if err != nil {
  180. return nil, fmt.Errorf(`store: unable to create category %q for user ID %d: %v`, request.Title, userID, err)
  181. }
  182. return &category, nil
  183. }
  184. // UpdateCategory updates an existing category.
  185. func (s *Storage) UpdateCategory(category *model.Category) error {
  186. query := `UPDATE categories SET title=$1, hide_globally=$2 WHERE id=$3 AND user_id=$4`
  187. _, err := s.db.Exec(
  188. query,
  189. category.Title,
  190. category.HideGlobally,
  191. category.ID,
  192. category.UserID,
  193. )
  194. if err != nil {
  195. return fmt.Errorf(`store: unable to update category: %v`, err)
  196. }
  197. return nil
  198. }
  199. // RemoveCategory deletes a category.
  200. func (s *Storage) RemoveCategory(userID, categoryID int64) error {
  201. query := `DELETE FROM categories WHERE id = $1 AND user_id = $2`
  202. result, err := s.db.Exec(query, categoryID, userID)
  203. if err != nil {
  204. return fmt.Errorf(`store: unable to remove this category: %v`, err)
  205. }
  206. count, err := result.RowsAffected()
  207. if err != nil {
  208. return fmt.Errorf(`store: unable to remove this category: %v`, err)
  209. }
  210. if count == 0 {
  211. return errors.New(`store: no category has been removed`)
  212. }
  213. return nil
  214. }
  215. // RemoveAndReplaceCategoriesByName deletes categories with the given titles and
  216. // reassigns affected feeds to the user's first remaining category. It returns
  217. // an error if the deletion would leave the user without any categories.
  218. func (s *Storage) RemoveAndReplaceCategoriesByName(userid int64, titles []string) error {
  219. tx, err := s.db.Begin()
  220. if err != nil {
  221. return errors.New("store: unable to begin transaction")
  222. }
  223. titleParam := pq.Array(titles)
  224. var count int
  225. query := "SELECT count(*) FROM categories WHERE user_id = $1 AND title <> ALL($2)"
  226. err = tx.QueryRow(query, userid, titleParam).Scan(&count)
  227. if err != nil {
  228. tx.Rollback()
  229. return errors.New("store: unable to retrieve category count")
  230. }
  231. if count < 1 {
  232. tx.Rollback()
  233. return errors.New("store: at least 1 category must remain after deletion")
  234. }
  235. query = `
  236. WITH d_cats AS (SELECT id FROM categories WHERE user_id = $1 AND title = ANY($2))
  237. UPDATE feeds
  238. SET category_id =
  239. (SELECT id
  240. FROM categories
  241. WHERE user_id = $1 AND id NOT IN (SELECT id FROM d_cats)
  242. ORDER BY title ASC
  243. LIMIT 1)
  244. WHERE user_id = $1 AND category_id IN (SELECT id FROM d_cats)
  245. `
  246. _, err = tx.Exec(query, userid, titleParam)
  247. if err != nil {
  248. tx.Rollback()
  249. return fmt.Errorf("store: unable to replace categories: %v", err)
  250. }
  251. query = "DELETE FROM categories WHERE user_id = $1 AND title = ANY($2)"
  252. _, err = tx.Exec(query, userid, titleParam)
  253. if err != nil {
  254. tx.Rollback()
  255. return fmt.Errorf("store: unable to delete categories: %v", err)
  256. }
  257. tx.Commit()
  258. return nil
  259. }