From d3883126bfca47f1af57a6388ff7b00c997c924f Mon Sep 17 00:00:00 2001 From: Frédéric Guillot Date: Tue, 29 Oct 2019 22:48:07 -0700 Subject: Improve storage module --- storage/feed.go | 185 ++++++++++++++++++++++++++++++++++++-------------------- 1 file changed, 119 insertions(+), 66 deletions(-) (limited to 'storage/feed.go') diff --git a/storage/feed.go b/storage/feed.go index 45ca9be..f6ad021 100644 --- a/storage/feed.go +++ b/storage/feed.go @@ -15,18 +15,18 @@ import ( // FeedExists checks if the given feed exists. func (s *Storage) FeedExists(userID, feedID int64) bool { - var result int - query := `SELECT count(*) as c FROM feeds WHERE user_id=$1 AND id=$2` + var result bool + query := `SELECT true FROM feeds WHERE user_id=$1 AND id=$2` s.db.QueryRow(query, userID, feedID).Scan(&result) - return result >= 1 + return result } // FeedURLExists checks if feed URL already exists. func (s *Storage) FeedURLExists(userID int64, feedURL string) bool { - var result int - query := `SELECT count(*) as c FROM feeds WHERE user_id=$1 AND feed_url=$2` + var result bool + query := `SELECT true FROM feeds WHERE user_id=$1 AND feed_url=$2` s.db.QueryRow(query, userID, feedURL).Scan(&result) - return result >= 1 + return result } // CountFeeds returns the number of feeds that belongs to the given user. @@ -42,8 +42,9 @@ func (s *Storage) CountFeeds(userID int64) int { // CountErrorFeeds returns the number of feeds with parse errors that belong to the given user. func (s *Storage) CountErrorFeeds(userID int64) int { + query := `SELECT count(*) FROM feeds WHERE user_id=$1 AND parsing_error_count>=$2` var result int - err := s.db.QueryRow(`SELECT count(*) FROM feeds WHERE user_id=$1 AND parsing_error_count>=$2`, userID, maxParsingError).Scan(&result) + err := s.db.QueryRow(query, userID, maxParsingError).Scan(&result) if err != nil { return 0 } @@ -54,25 +55,40 @@ func (s *Storage) CountErrorFeeds(userID int64) int { // Feeds returns all feeds of the given user. func (s *Storage) Feeds(userID int64) (model.Feeds, error) { feeds := make(model.Feeds, 0) - query := `SELECT - f.id, f.feed_url, f.site_url, f.title, f.etag_header, f.last_modified_header, - f.user_id, f.checked_at at time zone u.timezone, - f.parsing_error_count, f.parsing_error_msg, - f.scraper_rules, f.rewrite_rules, f.crawler, f.user_agent, - f.username, f.password, f.disabled, - f.category_id, c.title as category_title, - fi.icon_id, - u.timezone + query := ` + SELECT + f.id, + f.feed_url, + f.site_url, + f.title, + f.etag_header, + f.last_modified_header, + f.user_id, + f.checked_at at time zone u.timezone, + f.parsing_error_count, + f.parsing_error_msg, + f.scraper_rules, + f.rewrite_rules, + f.crawler, + f.user_agent, + f.username, + f.password, + f.disabled, + f.category_id, + c.title as category_title, + fi.icon_id, + u.timezone FROM feeds f LEFT JOIN categories c ON c.id=f.category_id LEFT JOIN feed_icons fi ON fi.feed_id=f.id LEFT JOIN users u ON u.id=f.user_id - WHERE f.user_id=$1 - ORDER BY f.parsing_error_count DESC, lower(f.title) ASC` - + WHERE + f.user_id=$1 + ORDER BY f.parsing_error_count DESC, lower(f.title) ASC + ` rows, err := s.db.Query(query, userID) if err != nil { - return nil, fmt.Errorf("unable to fetch feeds: %v", err) + return nil, fmt.Errorf(`store: unable to fetch feeds: %v`, err) } defer rows.Close() @@ -107,7 +123,7 @@ func (s *Storage) Feeds(userID int64) (model.Feeds, error) { ) if err != nil { - return nil, fmt.Errorf("unable to fetch feeds row: %v", err) + return nil, fmt.Errorf(`store: unable to fetch feeds row: %v`, err) } if iconID != nil { @@ -124,27 +140,35 @@ func (s *Storage) Feeds(userID int64) (model.Feeds, error) { // FeedsWithCounters returns all feeds of the given user with counters of read and unread entries. func (s *Storage) FeedsWithCounters(userID int64) (model.Feeds, error) { feeds := make(model.Feeds, 0) - query := `SELECT - f.id, f.feed_url, f.site_url, f.title, f.etag_header, f.last_modified_header, - f.user_id, f.checked_at at time zone u.timezone, - f.parsing_error_count, f.parsing_error_msg, - f.scraper_rules, f.rewrite_rules, f.crawler, f.user_agent, - f.username, f.password, f.disabled, - f.category_id, c.title as category_title, - fi.icon_id, - u.timezone, - (SELECT count(*) FROM entries WHERE entries.feed_id=f.id AND status='unread') as unread_count, - (SELECT count(*) FROM entries WHERE entries.feed_id=f.id AND status='read') as read_count + query := ` + SELECT + f.id, + f.feed_url, + f.site_url, + f.title, + f.etag_header, + f.last_modified_header, + f.user_id, + f.checked_at at time zone u.timezone, + f.parsing_error_count, f.parsing_error_msg, + f.scraper_rules, f.rewrite_rules, f.crawler, f.user_agent, + f.username, f.password, f.disabled, + f.category_id, c.title as category_title, + fi.icon_id, + u.timezone, + (SELECT count(*) FROM entries WHERE entries.feed_id=f.id AND status='unread') as unread_count, + (SELECT count(*) FROM entries WHERE entries.feed_id=f.id AND status='read') as read_count FROM feeds f LEFT JOIN categories c ON c.id=f.category_id LEFT JOIN feed_icons fi ON fi.feed_id=f.id LEFT JOIN users u ON u.id=f.user_id - WHERE f.user_id=$1 - ORDER BY f.parsing_error_count DESC, unread_count DESC, lower(f.title) ASC` - + WHERE + f.user_id=$1 + ORDER BY f.parsing_error_count DESC, unread_count DESC, lower(f.title) ASC + ` rows, err := s.db.Query(query, userID) if err != nil { - return nil, fmt.Errorf("unable to fetch feeds: %v", err) + return nil, fmt.Errorf(`store: unable to fetch feeds: %v`, err) } defer rows.Close() @@ -181,7 +205,7 @@ func (s *Storage) FeedsWithCounters(userID int64) (model.Feeds, error) { ) if err != nil { - return nil, fmt.Errorf("unable to fetch feeds row: %v", err) + return nil, fmt.Errorf(`store: unable to fetch feeds row: %v`, err) } if iconID != nil { @@ -204,19 +228,33 @@ func (s *Storage) FeedByID(userID, feedID int64) (*model.Feed, error) { query := ` SELECT - f.id, f.feed_url, f.site_url, f.title, f.etag_header, f.last_modified_header, - f.user_id, f.checked_at at time zone u.timezone, - f.parsing_error_count, f.parsing_error_msg, - f.scraper_rules, f.rewrite_rules, f.crawler, f.user_agent, - f.username, f.password, f.disabled, - f.category_id, c.title as category_title, - fi.icon_id, - u.timezone + f.id, + f.feed_url, + f.site_url, + f.title, + f.etag_header, + f.last_modified_header, + f.user_id, f.checked_at at time zone u.timezone, + f.parsing_error_count, + f.parsing_error_msg, + f.scraper_rules, + f.rewrite_rules, + f.crawler, + f.user_agent, + f.username, + f.password, + f.disabled, + f.category_id, + c.title as category_title, + fi.icon_id, + u.timezone FROM feeds f LEFT JOIN categories c ON c.id=f.category_id LEFT JOIN feed_icons fi ON fi.feed_id=f.id LEFT JOIN users u ON u.id=f.user_id - WHERE f.user_id=$1 AND f.id=$2` + WHERE + f.user_id=$1 AND f.id=$2 + ` err := s.db.QueryRow(query, userID, feedID).Scan( &feed.ID, @@ -246,7 +284,7 @@ func (s *Storage) FeedByID(userID, feedID int64) (*model.Feed, error) { case err == sql.ErrNoRows: return nil, nil case err != nil: - return nil, fmt.Errorf("unable to fetch feed #%d: %v", feedID, err) + return nil, fmt.Errorf(`store: unable to fetch feed #%d: %v`, feedID, err) } if iconID != nil { @@ -260,14 +298,25 @@ func (s *Storage) FeedByID(userID, feedID int64) (*model.Feed, error) { // CreateFeed creates a new feed. func (s *Storage) CreateFeed(feed *model.Feed) error { sql := ` - INSERT INTO feeds - (feed_url, site_url, title, category_id, user_id, etag_header, - last_modified_header, crawler, user_agent, username, password, - disabled) - VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) - RETURNING id + INSERT INTO feeds ( + feed_url, + site_url, + title, + category_id, + user_id, + etag_header, + last_modified_header, + crawler, + user_agent, + username, + password, + disabled + ) + VALUES + ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) + RETURNING + id ` - err := s.db.QueryRow( sql, feed.FeedURL, @@ -284,7 +333,7 @@ func (s *Storage) CreateFeed(feed *model.Feed) error { feed.Disabled, ).Scan(&feed.ID) if err != nil { - return fmt.Errorf("unable to create feed %q: %v", feed.FeedURL, err) + return fmt.Errorf(`store: unable to create feed %q: %v`, feed.FeedURL, err) } for i := 0; i < len(feed.Entries); i++ { @@ -305,7 +354,9 @@ func (s *Storage) CreateFeed(feed *model.Feed) error { // UpdateFeed updates an existing feed. func (s *Storage) UpdateFeed(feed *model.Feed) (err error) { query := ` - UPDATE feeds SET + UPDATE + feeds + SET feed_url=$1, site_url=$2, title=$3, @@ -325,7 +376,6 @@ func (s *Storage) UpdateFeed(feed *model.Feed) (err error) { WHERE id=$17 AND user_id=$18 ` - _, err = s.db.Exec(query, feed.FeedURL, feed.SiteURL, @@ -348,7 +398,7 @@ func (s *Storage) UpdateFeed(feed *model.Feed) (err error) { ) if err != nil { - return fmt.Errorf("unable to update feed #%d (%s): %v", feed.ID, feed.FeedURL, err) + return fmt.Errorf(`store: unable to update feed #%d (%s): %v`, feed.ID, feed.FeedURL, err) } return nil @@ -357,13 +407,15 @@ func (s *Storage) UpdateFeed(feed *model.Feed) (err error) { // UpdateFeedError updates feed errors. func (s *Storage) UpdateFeedError(feed *model.Feed) (err error) { query := ` - UPDATE feeds + UPDATE + feeds SET parsing_error_msg=$1, parsing_error_count=$2, checked_at=$3 - WHERE id=$4 AND user_id=$5` - + WHERE + id=$4 AND user_id=$5 + ` _, err = s.db.Exec(query, feed.ParsingErrorMsg, feed.ParsingErrorCount, @@ -373,7 +425,7 @@ func (s *Storage) UpdateFeedError(feed *model.Feed) (err error) { ) if err != nil { - return fmt.Errorf("unable to update feed error #%d (%s): %v", feed.ID, feed.FeedURL, err) + return fmt.Errorf(`store: unable to update feed error #%d (%s): %v`, feed.ID, feed.FeedURL, err) } return nil @@ -381,18 +433,19 @@ func (s *Storage) UpdateFeedError(feed *model.Feed) (err error) { // RemoveFeed removes a feed. func (s *Storage) RemoveFeed(userID, feedID int64) error { - result, err := s.db.Exec("DELETE FROM feeds WHERE id = $1 AND user_id = $2", feedID, userID) + query := `DELETE FROM feeds WHERE id = $1 AND user_id = $2` + result, err := s.db.Exec(query, feedID, userID) if err != nil { - return fmt.Errorf("unable to remove feed #%d: %v", feedID, err) + return fmt.Errorf(`store: unable to remove feed #%d: %v`, feedID, err) } count, err := result.RowsAffected() if err != nil { - return fmt.Errorf("unable to remove feed #%d: %v", feedID, err) + return fmt.Errorf(`store: unable to remove feed #%d: %v`, feedID, err) } if count == 0 { - return errors.New("no feed has been removed") + return errors.New(`store: no feed has been removed`) } return nil -- cgit v1.2.3