From 8ffb773f43c8dc54801ca1d111854e7e881c93c9 Mon Sep 17 00:00:00 2001 From: Frédéric Guillot Date: Sun, 19 Nov 2017 21:10:04 -0800 Subject: First commit --- storage/category.go | 178 +++++++++++++++++++++++++++ storage/enclosure.go | 68 +++++++++++ storage/entry.go | 124 +++++++++++++++++++ storage/entry_query_builder.go | 268 +++++++++++++++++++++++++++++++++++++++++ storage/feed.go | 223 ++++++++++++++++++++++++++++++++++ storage/icon.go | 106 ++++++++++++++++ storage/job.go | 44 +++++++ storage/migration.go | 53 ++++++++ storage/session.go | 125 +++++++++++++++++++ storage/storage.go | 32 +++++ storage/timezone.go | 34 ++++++ storage/user.go | 195 ++++++++++++++++++++++++++++++ 12 files changed, 1450 insertions(+) create mode 100644 storage/category.go create mode 100644 storage/enclosure.go create mode 100644 storage/entry.go create mode 100644 storage/entry_query_builder.go create mode 100644 storage/feed.go create mode 100644 storage/icon.go create mode 100644 storage/job.go create mode 100644 storage/migration.go create mode 100644 storage/session.go create mode 100644 storage/storage.go create mode 100644 storage/timezone.go create mode 100644 storage/user.go (limited to 'storage') diff --git a/storage/category.go b/storage/category.go new file mode 100644 index 0000000..3d08c4d --- /dev/null +++ b/storage/category.go @@ -0,0 +1,178 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "database/sql" + "errors" + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" + "time" +) + +func (s *Storage) CategoryExists(userID, categoryID int64) bool { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:CategoryExists] userID=%d, categoryID=%d", userID, categoryID)) + + var result int + query := `SELECT count(*) as c FROM categories WHERE user_id=$1 AND id=$2` + s.db.QueryRow(query, userID, categoryID).Scan(&result) + return result >= 1 +} + +func (s *Storage) GetCategory(userID, categoryID int64) (*model.Category, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetCategory] userID=%d, getCategory=%d", userID, categoryID)) + var category model.Category + + query := `SELECT id, user_id, title FROM categories WHERE user_id=$1 AND id=$2` + err := s.db.QueryRow(query, userID, categoryID).Scan(&category.ID, &category.UserID, &category.Title) + if err == sql.ErrNoRows { + return nil, nil + } else if err != nil { + return nil, fmt.Errorf("Unable to fetch category: %v", err) + } + + return &category, nil +} + +func (s *Storage) GetFirstCategory(userID int64) (*model.Category, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetFirstCategory] userID=%d", userID)) + var category model.Category + + query := `SELECT id, user_id, title FROM categories WHERE user_id=$1 ORDER BY title ASC` + err := s.db.QueryRow(query, userID).Scan(&category.ID, &category.UserID, &category.Title) + if err == sql.ErrNoRows { + return nil, nil + } else if err != nil { + return nil, fmt.Errorf("Unable to fetch category: %v", err) + } + + return &category, nil +} + +func (s *Storage) GetCategoryByTitle(userID int64, title string) (*model.Category, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetCategoryByTitle] userID=%d, title=%s", userID, title)) + var category model.Category + + query := `SELECT id, user_id, title FROM categories WHERE user_id=$1 AND title=$2` + err := s.db.QueryRow(query, userID, title).Scan(&category.ID, &category.UserID, &category.Title) + if err == sql.ErrNoRows { + return nil, nil + } else if err != nil { + return nil, fmt.Errorf("Unable to fetch category: %v", err) + } + + return &category, nil +} + +func (s *Storage) GetCategories(userID int64) (model.Categories, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetCategories] userID=%d", userID)) + + query := `SELECT id, user_id, title FROM categories WHERE user_id=$1` + rows, err := s.db.Query(query, userID) + if err != nil { + return nil, fmt.Errorf("Unable to fetch categories: %v", err) + } + defer rows.Close() + + categories := make(model.Categories, 0) + for rows.Next() { + var category model.Category + if err := rows.Scan(&category.ID, &category.UserID, &category.Title); err != nil { + return nil, fmt.Errorf("Unable to fetch categories row: %v", err) + } + + categories = append(categories, &category) + } + + return categories, nil +} + +func (s *Storage) GetCategoriesWithFeedCount(userID int64) (model.Categories, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetCategoriesWithFeedCount] userID=%d", userID)) + query := `SELECT + c.id, c.user_id, c.title, + (SELECT count(*) FROM feeds WHERE feeds.category_id=c.id) AS count + FROM categories c WHERE user_id=$1` + + rows, err := s.db.Query(query, userID) + if err != nil { + return nil, fmt.Errorf("Unable to fetch categories: %v", err) + } + defer rows.Close() + + categories := make(model.Categories, 0) + for rows.Next() { + var category model.Category + if err := rows.Scan(&category.ID, &category.UserID, &category.Title, &category.FeedCount); err != nil { + return nil, fmt.Errorf("Unable to fetch categories row: %v", err) + } + + categories = append(categories, &category) + } + + return categories, nil +} + +func (s *Storage) CreateCategory(category *model.Category) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:CreateCategory] title=%s", category.Title)) + + query := ` + INSERT INTO categories + (user_id, title) + VALUES + ($1, $2) + RETURNING id + ` + err := s.db.QueryRow( + query, + category.UserID, + category.Title, + ).Scan(&category.ID) + + if err != nil { + return fmt.Errorf("Unable to create category: %v", err) + } + + return nil +} + +func (s *Storage) UpdateCategory(category *model.Category) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UpdateCategory] categoryID=%d", category.ID)) + + query := `UPDATE categories SET title=$1 WHERE id=$2 AND user_id=$3` + _, err := s.db.Exec( + query, + category.Title, + category.ID, + category.UserID, + ) + + if err != nil { + return fmt.Errorf("Unable to update category: %v", err) + } + + return nil +} + +func (s *Storage) RemoveCategory(userID, categoryID int64) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:RemoveCategory] userID=%d, categoryID=%d", userID, categoryID)) + + result, err := s.db.Exec("DELETE FROM categories WHERE id = $1 AND user_id = $2", categoryID, userID) + if err != nil { + return fmt.Errorf("Unable to remove this category: %v", err) + } + + count, err := result.RowsAffected() + if err != nil { + return fmt.Errorf("Unable to remove this category: %v", err) + } + + if count == 0 { + return errors.New("no category has been removed") + } + + return nil +} diff --git a/storage/enclosure.go b/storage/enclosure.go new file mode 100644 index 0000000..ac85cb7 --- /dev/null +++ b/storage/enclosure.go @@ -0,0 +1,68 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "fmt" + "github.com/miniflux/miniflux2/model" +) + +func (s *Storage) GetEnclosures(entryID int64) (model.EnclosureList, error) { + query := `SELECT + id, user_id, entry_id, url, size, mime_type + FROM enclosures + WHERE entry_id = $1 ORDER BY id ASC` + + rows, err := s.db.Query(query, entryID) + if err != nil { + return nil, fmt.Errorf("Unable to get enclosures: %v", err) + } + defer rows.Close() + + enclosures := make(model.EnclosureList, 0) + for rows.Next() { + var enclosure model.Enclosure + err := rows.Scan( + &enclosure.ID, + &enclosure.UserID, + &enclosure.EntryID, + &enclosure.URL, + &enclosure.Size, + &enclosure.MimeType, + ) + + if err != nil { + return nil, fmt.Errorf("Unable to fetch enclosure row: %v", err) + } + + enclosures = append(enclosures, &enclosure) + } + + return enclosures, nil +} + +func (s *Storage) CreateEnclosure(enclosure *model.Enclosure) error { + query := ` + INSERT INTO enclosures + (url, size, mime_type, entry_id, user_id) + VALUES + ($1, $2, $3, $4, $5) + RETURNING id + ` + err := s.db.QueryRow( + query, + enclosure.URL, + enclosure.Size, + enclosure.MimeType, + enclosure.EntryID, + enclosure.UserID, + ).Scan(&enclosure.ID) + + if err != nil { + return fmt.Errorf("Unable to create enclosure: %v", err) + } + + return nil +} diff --git a/storage/entry.go b/storage/entry.go new file mode 100644 index 0000000..84cfb0f --- /dev/null +++ b/storage/entry.go @@ -0,0 +1,124 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "errors" + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" + "time" + + "github.com/lib/pq" +) + +func (s *Storage) GetEntryQueryBuilder(userID int64, timezone string) *EntryQueryBuilder { + return NewEntryQueryBuilder(s, userID, timezone) +} + +func (s *Storage) CreateEntry(entry *model.Entry) error { + query := ` + INSERT INTO entries + (title, hash, url, published_at, content, author, user_id, feed_id) + VALUES + ($1, $2, $3, $4, $5, $6, $7, $8) + RETURNING id + ` + err := s.db.QueryRow( + query, + entry.Title, + entry.Hash, + entry.URL, + entry.Date, + entry.Content, + entry.Author, + entry.UserID, + entry.FeedID, + ).Scan(&entry.ID) + + if err != nil { + return fmt.Errorf("Unable to create entry: %v", err) + } + + entry.Status = "unread" + for i := 0; i < len(entry.Enclosures); i++ { + entry.Enclosures[i].EntryID = entry.ID + entry.Enclosures[i].UserID = entry.UserID + err := s.CreateEnclosure(entry.Enclosures[i]) + if err != nil { + return err + } + } + + return nil +} + +func (s *Storage) UpdateEntry(entry *model.Entry) error { + query := ` + UPDATE entries SET + title=$1, url=$2, published_at=$3, content=$4, author=$5 + WHERE user_id=$6 AND feed_id=$7 AND hash=$8 + ` + _, err := s.db.Exec( + query, + entry.Title, + entry.URL, + entry.Date, + entry.Content, + entry.Author, + entry.UserID, + entry.FeedID, + entry.Hash, + ) + + return err +} + +func (s *Storage) EntryExists(entry *model.Entry) bool { + var result int + query := `SELECT count(*) as c FROM entries WHERE user_id=$1 AND feed_id=$2 AND hash=$3` + s.db.QueryRow(query, entry.UserID, entry.FeedID, entry.Hash).Scan(&result) + return result >= 1 +} + +func (s *Storage) UpdateEntries(userID, feedID int64, entries model.Entries) (err error) { + for _, entry := range entries { + entry.UserID = userID + entry.FeedID = feedID + + if s.EntryExists(entry) { + err = s.UpdateEntry(entry) + } else { + err = s.CreateEntry(entry) + } + + if err != nil { + return err + } + } + + return nil +} + +func (s *Storage) SetEntriesStatus(userID int64, entryIDs []int64, status string) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:SetEntriesStatus] userID=%d, entryIDs=%v, status=%s", userID, entryIDs, status)) + + query := `UPDATE entries SET status=$1 WHERE user_id=$2 AND id=ANY($3)` + result, err := s.db.Exec(query, status, userID, pq.Array(entryIDs)) + if err != nil { + return fmt.Errorf("Unable to update entry status: %v", err) + } + + count, err := result.RowsAffected() + if err != nil { + return fmt.Errorf("Unable to update this entry: %v", err) + } + + if count == 0 { + return errors.New("Nothing has been updated") + } + + return nil +} diff --git a/storage/entry_query_builder.go b/storage/entry_query_builder.go new file mode 100644 index 0000000..0c210c3 --- /dev/null +++ b/storage/entry_query_builder.go @@ -0,0 +1,268 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" + "strings" + "time" +) + +type EntryQueryBuilder struct { + store *Storage + feedID int64 + userID int64 + timezone string + categoryID int64 + status string + order string + direction string + limit int + offset int + entryID int64 + gtEntryID int64 + ltEntryID int64 + conditions []string + args []interface{} +} + +func (e *EntryQueryBuilder) WithCondition(column, operator string, value interface{}) *EntryQueryBuilder { + e.args = append(e.args, value) + e.conditions = append(e.conditions, fmt.Sprintf("%s %s $%d", column, operator, len(e.args)+1)) + return e +} + +func (e *EntryQueryBuilder) WithEntryID(entryID int64) *EntryQueryBuilder { + e.entryID = entryID + return e +} + +func (e *EntryQueryBuilder) WithEntryIDGreaterThan(entryID int64) *EntryQueryBuilder { + e.gtEntryID = entryID + return e +} + +func (e *EntryQueryBuilder) WithEntryIDLowerThan(entryID int64) *EntryQueryBuilder { + e.ltEntryID = entryID + return e +} + +func (e *EntryQueryBuilder) WithFeedID(feedID int64) *EntryQueryBuilder { + e.feedID = feedID + return e +} + +func (e *EntryQueryBuilder) WithCategoryID(categoryID int64) *EntryQueryBuilder { + e.categoryID = categoryID + return e +} + +func (e *EntryQueryBuilder) WithStatus(status string) *EntryQueryBuilder { + e.status = status + return e +} + +func (e *EntryQueryBuilder) WithOrder(order string) *EntryQueryBuilder { + e.order = order + return e +} + +func (e *EntryQueryBuilder) WithDirection(direction string) *EntryQueryBuilder { + e.direction = direction + return e +} + +func (e *EntryQueryBuilder) WithLimit(limit int) *EntryQueryBuilder { + e.limit = limit + return e +} + +func (e *EntryQueryBuilder) WithOffset(offset int) *EntryQueryBuilder { + e.offset = offset + return e +} + +func (e *EntryQueryBuilder) CountEntries() (count int, err error) { + defer helper.ExecutionTime( + time.Now(), + fmt.Sprintf("[EntryQueryBuilder:CountEntries] userID=%d, feedID=%d, status=%s", e.userID, e.feedID, e.status), + ) + + query := `SELECT count(*) FROM entries e LEFT JOIN feeds f ON f.id=e.feed_id WHERE %s` + args, condition := e.buildCondition() + err = e.store.db.QueryRow(fmt.Sprintf(query, condition), args...).Scan(&count) + if err != nil { + return 0, fmt.Errorf("unable to count entries: %v", err) + } + + return count, nil +} + +func (e *EntryQueryBuilder) GetEntry() (*model.Entry, error) { + e.limit = 1 + entries, err := e.GetEntries() + if err != nil { + return nil, err + } + + if len(entries) != 1 { + return nil, nil + } + + entries[0].Enclosures, err = e.store.GetEnclosures(entries[0].ID) + if err != nil { + return nil, err + } + + return entries[0], nil +} + +func (e *EntryQueryBuilder) GetEntries() (model.Entries, error) { + debugStr := "[EntryQueryBuilder:GetEntries] userID=%d, feedID=%d, categoryID=%d, status=%s, order=%s, direction=%s, offset=%d, limit=%d" + defer helper.ExecutionTime(time.Now(), fmt.Sprintf(debugStr, e.userID, e.feedID, e.categoryID, e.status, e.order, e.direction, e.offset, e.limit)) + + query := ` + SELECT + e.id, e.user_id, e.feed_id, e.hash, e.published_at at time zone '%s', e.title, e.url, e.author, e.content, e.status, + f.title as feed_title, f.feed_url, f.site_url, f.checked_at, + f.category_id, c.title as category_title, + fi.icon_id + FROM entries e + LEFT JOIN feeds f ON f.id=e.feed_id + LEFT JOIN categories c ON c.id=f.category_id + LEFT JOIN feed_icons fi ON fi.feed_id=f.id + WHERE %s %s + ` + + args, conditions := e.buildCondition() + query = fmt.Sprintf(query, e.timezone, conditions, e.buildSorting()) + // log.Println(query) + + rows, err := e.store.db.Query(query, args...) + if err != nil { + return nil, fmt.Errorf("unable to get entries: %v", err) + } + defer rows.Close() + + entries := make(model.Entries, 0) + for rows.Next() { + var entry model.Entry + var iconID interface{} + + entry.Feed = &model.Feed{UserID: e.userID} + entry.Feed.Category = &model.Category{UserID: e.userID} + entry.Feed.Icon = &model.FeedIcon{} + + err := rows.Scan( + &entry.ID, + &entry.UserID, + &entry.FeedID, + &entry.Hash, + &entry.Date, + &entry.Title, + &entry.URL, + &entry.Author, + &entry.Content, + &entry.Status, + &entry.Feed.Title, + &entry.Feed.FeedURL, + &entry.Feed.SiteURL, + &entry.Feed.CheckedAt, + &entry.Feed.Category.ID, + &entry.Feed.Category.Title, + &iconID, + ) + + if err != nil { + return nil, fmt.Errorf("Unable to fetch entry row: %v", err) + } + + if iconID == nil { + entry.Feed.Icon.IconID = 0 + } else { + entry.Feed.Icon.IconID = iconID.(int64) + } + + entry.Feed.ID = entry.FeedID + entry.Feed.Icon.FeedID = entry.FeedID + entries = append(entries, &entry) + } + + return entries, nil +} + +func (e *EntryQueryBuilder) buildCondition() ([]interface{}, string) { + args := []interface{}{e.userID} + conditions := []string{"e.user_id = $1"} + + if len(e.conditions) > 0 { + conditions = append(conditions, e.conditions...) + args = append(args, e.args...) + } + + if e.categoryID != 0 { + conditions = append(conditions, fmt.Sprintf("f.category_id=$%d", len(args)+1)) + args = append(args, e.categoryID) + } + + if e.feedID != 0 { + conditions = append(conditions, fmt.Sprintf("e.feed_id=$%d", len(args)+1)) + args = append(args, e.feedID) + } + + if e.entryID != 0 { + conditions = append(conditions, fmt.Sprintf("e.id=$%d", len(args)+1)) + args = append(args, e.entryID) + } + + if e.gtEntryID != 0 { + conditions = append(conditions, fmt.Sprintf("e.id > $%d", len(args)+1)) + args = append(args, e.gtEntryID) + } + + if e.ltEntryID != 0 { + conditions = append(conditions, fmt.Sprintf("e.id < $%d", len(args)+1)) + args = append(args, e.ltEntryID) + } + + if e.status != "" { + conditions = append(conditions, fmt.Sprintf("e.status=$%d", len(args)+1)) + args = append(args, e.status) + } + + return args, strings.Join(conditions, " AND ") +} + +func (e *EntryQueryBuilder) buildSorting() string { + var queries []string + + if e.order != "" { + queries = append(queries, fmt.Sprintf(`ORDER BY "%s"`, e.order)) + } + + if e.direction != "" { + queries = append(queries, fmt.Sprintf(`%s`, e.direction)) + } + + if e.limit != 0 { + queries = append(queries, fmt.Sprintf(`LIMIT %d`, e.limit)) + } + + if e.offset != 0 { + queries = append(queries, fmt.Sprintf(`OFFSET %d`, e.offset)) + } + + return strings.Join(queries, " ") +} + +func NewEntryQueryBuilder(store *Storage, userID int64, timezone string) *EntryQueryBuilder { + return &EntryQueryBuilder{ + store: store, + userID: userID, + timezone: timezone, + } +} diff --git a/storage/feed.go b/storage/feed.go new file mode 100644 index 0000000..ec08580 --- /dev/null +++ b/storage/feed.go @@ -0,0 +1,223 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "database/sql" + "errors" + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" + "time" +) + +func (s *Storage) FeedExists(userID, feedID int64) bool { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:FeedExists] userID=%d, feedID=%d", userID, feedID)) + + var result int + query := `SELECT count(*) as c FROM feeds WHERE user_id=$1 AND id=$2` + s.db.QueryRow(query, userID, feedID).Scan(&result) + return result >= 1 +} + +func (s *Storage) FeedURLExists(userID int64, feedURL string) bool { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:FeedURLExists] userID=%d, feedURL=%s", userID, feedURL)) + + var result int + query := `SELECT count(*) as c FROM feeds WHERE user_id=$1 AND feed_url=$2` + s.db.QueryRow(query, userID, feedURL).Scan(&result) + return result >= 1 +} + +func (s *Storage) GetFeeds(userID int64) (model.Feeds, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetFeeds] userID=%d", userID)) + + 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, f.parsing_error_count, f.parsing_error_msg, + f.category_id, c.title as category_title, + fi.icon_id + FROM feeds f + LEFT JOIN categories c ON c.id=f.category_id + LEFT JOIN feed_icons fi ON fi.feed_id=f.id + WHERE f.user_id=$1 + ORDER BY f.id ASC` + + rows, err := s.db.Query(query, userID) + if err != nil { + return nil, fmt.Errorf("Unable to fetch feeds: %v", err) + } + defer rows.Close() + + for rows.Next() { + var feed model.Feed + var iconID, errorMsg interface{} + feed.Category = &model.Category{UserID: userID} + feed.Icon = &model.FeedIcon{} + + err := rows.Scan( + &feed.ID, + &feed.FeedURL, + &feed.SiteURL, + &feed.Title, + &feed.EtagHeader, + &feed.LastModifiedHeader, + &feed.UserID, + &feed.CheckedAt, + &feed.ParsingErrorCount, + &errorMsg, + &feed.Category.ID, + &feed.Category.Title, + &iconID, + ) + + if err != nil { + return nil, fmt.Errorf("Unable to fetch feeds row: %v", err) + } + + if iconID == nil { + feed.Icon.IconID = 0 + } else { + feed.Icon.IconID = iconID.(int64) + } + + if errorMsg == nil { + feed.ParsingErrorMsg = "" + } else { + feed.ParsingErrorMsg = errorMsg.(string) + } + + feed.Icon.FeedID = feed.ID + feeds = append(feeds, &feed) + } + + return feeds, nil +} + +func (s *Storage) GetFeedById(userID, feedID int64) (*model.Feed, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetFeedById] feedID=%d", feedID)) + + var feed model.Feed + feed.Category = &model.Category{UserID: userID} + + 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, f.parsing_error_count, f.parsing_error_msg, + f.category_id, c.title as category_title + FROM feeds f + LEFT JOIN categories c ON c.id=f.category_id + WHERE f.user_id=$1 AND f.id=$2` + + err := s.db.QueryRow(query, userID, feedID).Scan( + &feed.ID, + &feed.FeedURL, + &feed.SiteURL, + &feed.Title, + &feed.EtagHeader, + &feed.LastModifiedHeader, + &feed.UserID, + &feed.CheckedAt, + &feed.ParsingErrorCount, + &feed.ParsingErrorMsg, + &feed.Category.ID, + &feed.Category.Title, + ) + + switch { + case err == sql.ErrNoRows: + return nil, nil + case err != nil: + return nil, fmt.Errorf("Unable to fetch feed: %v", err) + } + + return &feed, nil +} + +func (s *Storage) CreateFeed(feed *model.Feed) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:CreateFeed] feedURL=%s", feed.FeedURL)) + sql := ` + INSERT INTO feeds + (feed_url, site_url, title, category_id, user_id, etag_header, last_modified_header) + VALUES ($1, $2, $3, $4, $5, $6, $7) + RETURNING id + ` + + err := s.db.QueryRow( + sql, + feed.FeedURL, + feed.SiteURL, + feed.Title, + feed.Category.ID, + feed.UserID, + feed.EtagHeader, + feed.LastModifiedHeader, + ).Scan(&feed.ID) + + if err != nil { + return fmt.Errorf("Unable to create feed: %v", err) + } + + for i := 0; i < len(feed.Entries); i++ { + feed.Entries[i].FeedID = feed.ID + feed.Entries[i].UserID = feed.UserID + err := s.CreateEntry(feed.Entries[i]) + if err != nil { + return err + } + } + + return nil +} + +func (s *Storage) UpdateFeed(feed *model.Feed) (err error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UpdateFeed] feedURL=%s", feed.FeedURL)) + + query := `UPDATE feeds SET + feed_url=$1, site_url=$2, title=$3, category_id=$4, etag_header=$5, last_modified_header=$6, checked_at=$7, + parsing_error_msg=$8, parsing_error_count=$9 + WHERE id=$10 AND user_id=$11` + + _, err = s.db.Exec(query, + feed.FeedURL, + feed.SiteURL, + feed.Title, + feed.Category.ID, + feed.EtagHeader, + feed.LastModifiedHeader, + feed.CheckedAt, + feed.ParsingErrorMsg, + feed.ParsingErrorCount, + feed.ID, + feed.UserID, + ) + + if err != nil { + return fmt.Errorf("Unable to update feed: %v", err) + } + + return nil +} + +func (s *Storage) RemoveFeed(userID, feedID int64) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:RemoveFeed] userID=%d, feedID=%d", userID, feedID)) + + result, err := s.db.Exec("DELETE FROM feeds WHERE id = $1 AND user_id = $2", feedID, userID) + if err != nil { + return fmt.Errorf("Unable to remove this feed: %v", err) + } + + count, err := result.RowsAffected() + if err != nil { + return fmt.Errorf("Unable to remove this feed: %v", err) + } + + if count == 0 { + return errors.New("no feed has been removed") + } + + return nil +} diff --git a/storage/icon.go b/storage/icon.go new file mode 100644 index 0000000..993e4a7 --- /dev/null +++ b/storage/icon.go @@ -0,0 +1,106 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "database/sql" + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" + "strings" + "time" +) + +func (s *Storage) HasIcon(feedID int64) bool { + var result int + query := `SELECT count(*) as c FROM feed_icons WHERE feed_id=$1` + s.db.QueryRow(query, feedID).Scan(&result) + return result == 1 +} + +func (s *Storage) GetIconByID(iconID int64) (*model.Icon, error) { + defer helper.ExecutionTime(time.Now(), "[Storage:GetIconByID]") + + var icon model.Icon + query := `SELECT id, hash, mime_type, content FROM icons WHERE id=$1` + err := s.db.QueryRow(query, iconID).Scan(&icon.ID, &icon.Hash, &icon.MimeType, &icon.Content) + if err == sql.ErrNoRows { + return nil, nil + } else if err != nil { + return nil, fmt.Errorf("Unable to fetch icon by hash: %v", err) + } + + return &icon, nil +} + +func (s *Storage) GetIconByHash(icon *model.Icon) error { + defer helper.ExecutionTime(time.Now(), "[Storage:GetIconByHash]") + + err := s.db.QueryRow(`SELECT id FROM icons WHERE hash=$1`, icon.Hash).Scan(&icon.ID) + if err == sql.ErrNoRows { + return nil + } else if err != nil { + return fmt.Errorf("Unable to fetch icon by hash: %v", err) + } + + return nil +} + +func (s *Storage) CreateIcon(icon *model.Icon) error { + defer helper.ExecutionTime(time.Now(), "[Storage:CreateIcon]") + + query := ` + INSERT INTO icons + (hash, mime_type, content) + VALUES + ($1, $2, $3) + RETURNING id + ` + err := s.db.QueryRow( + query, + icon.Hash, + normalizeMimeType(icon.MimeType), + icon.Content, + ).Scan(&icon.ID) + + if err != nil { + return fmt.Errorf("Unable to create icon: %v", err) + } + + return nil +} + +func (s *Storage) CreateFeedIcon(feed *model.Feed, icon *model.Icon) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:CreateFeedIcon] feedID=%d", feed.ID)) + + err := s.GetIconByHash(icon) + if err != nil { + return err + } + + if icon.ID == 0 { + err := s.CreateIcon(icon) + if err != nil { + return err + } + } + + _, err = s.db.Exec(`INSERT INTO feed_icons (feed_id, icon_id) VALUES ($1, $2)`, feed.ID, icon.ID) + if err != nil { + return fmt.Errorf("Unable to create feed icon: %v", err) + } + + return nil +} + +func normalizeMimeType(mimeType string) string { + mimeType = strings.ToLower(mimeType) + switch mimeType { + case "image/png", "image/jpeg", "image/jpg", "image/webp", "image/svg+xml", "image/x-icon", "image/gif": + return mimeType + default: + return "image/x-icon" + } +} diff --git a/storage/job.go b/storage/job.go new file mode 100644 index 0000000..5383a5b --- /dev/null +++ b/storage/job.go @@ -0,0 +1,44 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" + "log" + "time" +) + +const maxParsingError = 3 + +func (s *Storage) GetJobs(batchSize int) []model.Job { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("storage.GetJobs[%d]", batchSize)) + + var jobs []model.Job + query := `SELECT + id, user_id + FROM feeds + WHERE parsing_error_count < $1 + ORDER BY checked_at ASC LIMIT %d` + + rows, err := s.db.Query(fmt.Sprintf(query, batchSize), maxParsingError) + if err != nil { + log.Println("Unable to fetch feed jobs:", err) + } + defer rows.Close() + + for rows.Next() { + var job model.Job + if err := rows.Scan(&job.FeedID, &job.UserID); err != nil { + log.Println("Unable to fetch feed job:", err) + break + } + + jobs = append(jobs, job) + } + + return jobs +} diff --git a/storage/migration.go b/storage/migration.go new file mode 100644 index 0000000..a41e812 --- /dev/null +++ b/storage/migration.go @@ -0,0 +1,53 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "fmt" + "github.com/miniflux/miniflux2/sql" + "log" + "strconv" +) + +const schemaVersion = 1 + +func (s *Storage) Migrate() { + var currentVersion int + s.db.QueryRow(`select version from schema_version`).Scan(¤tVersion) + + fmt.Println("Current schema version:", currentVersion) + fmt.Println("Latest schema version:", schemaVersion) + + for version := currentVersion + 1; version <= schemaVersion; version++ { + fmt.Println("Migrating to version:", version) + + tx, err := s.db.Begin() + if err != nil { + log.Fatalln(err) + } + + rawSQL := sql.SqlMap["schema_version_"+strconv.Itoa(version)] + // fmt.Println(rawSQL) + _, err = tx.Exec(rawSQL) + if err != nil { + tx.Rollback() + log.Fatalln(err) + } + + if _, err := tx.Exec(`delete from schema_version`); err != nil { + tx.Rollback() + log.Fatalln(err) + } + + if _, err := tx.Exec(`insert into schema_version (version) values($1)`, version); err != nil { + tx.Rollback() + log.Fatalln(err) + } + + if err := tx.Commit(); err != nil { + log.Fatalln(err) + } + } +} diff --git a/storage/session.go b/storage/session.go new file mode 100644 index 0000000..296711d --- /dev/null +++ b/storage/session.go @@ -0,0 +1,125 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "database/sql" + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" +) + +func (s *Storage) GetSessions(userID int64) (model.Sessions, error) { + query := `SELECT id, user_id, token, created_at, user_agent, ip FROM sessions WHERE user_id=$1 ORDER BY id DESC` + rows, err := s.db.Query(query, userID) + if err != nil { + return nil, fmt.Errorf("unable to fetch sessions: %v", err) + } + defer rows.Close() + + var sessions model.Sessions + for rows.Next() { + var session model.Session + err := rows.Scan( + &session.ID, + &session.UserID, + &session.Token, + &session.CreatedAt, + &session.UserAgent, + &session.IP, + ) + + if err != nil { + return nil, fmt.Errorf("unable to fetch session row: %v", err) + } + + sessions = append(sessions, &session) + } + + return sessions, nil +} + +func (s *Storage) CreateSession(username, userAgent, ip string) (sessionID string, err error) { + var userID int64 + + err = s.db.QueryRow("SELECT id FROM users WHERE username = $1", username).Scan(&userID) + if err != nil { + return "", fmt.Errorf("unable to fetch UserID: %v", err) + } + + token := helper.GenerateRandomString(64) + query := "INSERT INTO sessions (token, user_id, user_agent, ip) VALUES ($1, $2, $3, $4)" + _, err = s.db.Exec(query, token, userID, userAgent, ip) + if err != nil { + return "", fmt.Errorf("unable to create session: %v", err) + } + + s.SetLastLogin(userID) + + return token, nil +} + +func (s *Storage) GetSessionByToken(token string) (*model.Session, error) { + var session model.Session + + query := "SELECT id, user_id, token, created_at, user_agent, ip FROM sessions WHERE token = $1" + err := s.db.QueryRow(query, token).Scan( + &session.ID, + &session.UserID, + &session.Token, + &session.CreatedAt, + &session.UserAgent, + &session.IP, + ) + + if err == sql.ErrNoRows { + return nil, fmt.Errorf("session not found: %s", token) + } else if err != nil { + return nil, fmt.Errorf("unable to fetch session: %v", err) + } + + return &session, nil +} + +func (s *Storage) RemoveSessionByToken(userID int64, token string) error { + result, err := s.db.Exec(`DELETE FROM sessions WHERE user_id=$1 AND token=$2`, userID, token) + if err != nil { + return fmt.Errorf("unable to remove this session: %v", err) + } + + count, err := result.RowsAffected() + if err != nil { + return fmt.Errorf("unable to remove this session: %v", err) + } + + if count != 1 { + return fmt.Errorf("nothing has been removed") + } + + return nil +} + +func (s *Storage) RemoveSessionByID(userID, sessionID int64) error { + result, err := s.db.Exec(`DELETE FROM sessions WHERE user_id=$1 AND id=$2`, userID, sessionID) + if err != nil { + return fmt.Errorf("unable to remove this session: %v", err) + } + + count, err := result.RowsAffected() + if err != nil { + return fmt.Errorf("unable to remove this session: %v", err) + } + + if count != 1 { + return fmt.Errorf("nothing has been removed") + } + + return nil +} + +func (s *Storage) FlushAllSessions() (err error) { + _, err = s.db.Exec(`delete from sessions`) + return +} diff --git a/storage/storage.go b/storage/storage.go new file mode 100644 index 0000000..ebefe91 --- /dev/null +++ b/storage/storage.go @@ -0,0 +1,32 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "database/sql" + "log" + + _ "github.com/lib/pq" +) + +type Storage struct { + db *sql.DB +} + +func (s *Storage) Close() { + s.db.Close() +} + +func NewStorage(databaseUrl string, maxOpenConns int) *Storage { + db, err := sql.Open("postgres", databaseUrl) + if err != nil { + log.Fatalf("Unable to connect to the database: %v", err) + } + + db.SetMaxOpenConns(maxOpenConns) + db.SetMaxIdleConns(2) + + return &Storage{db: db} +} diff --git a/storage/timezone.go b/storage/timezone.go new file mode 100644 index 0000000..8edfc1c --- /dev/null +++ b/storage/timezone.go @@ -0,0 +1,34 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "fmt" + "github.com/miniflux/miniflux2/helper" + "time" +) + +func (s *Storage) GetTimezones() (map[string]string, error) { + defer helper.ExecutionTime(time.Now(), "[Storage:GetTimezones]") + + timezones := make(map[string]string) + query := `select name from pg_timezone_names() order by name asc` + rows, err := s.db.Query(query) + if err != nil { + return nil, fmt.Errorf("unable to fetch timezones: %v", err) + } + defer rows.Close() + + for rows.Next() { + var timezone string + if err := rows.Scan(&timezone); err != nil { + return nil, fmt.Errorf("unable to fetch timezones row: %v", err) + } + + timezones[timezone] = timezone + } + + return timezones, nil +} diff --git a/storage/user.go b/storage/user.go new file mode 100644 index 0000000..736f7ac --- /dev/null +++ b/storage/user.go @@ -0,0 +1,195 @@ +// Copyright 2017 Frédéric Guillot. All rights reserved. +// Use of this source code is governed by the Apache 2.0 +// license that can be found in the LICENSE file. + +package storage + +import ( + "database/sql" + "errors" + "fmt" + "github.com/miniflux/miniflux2/helper" + "github.com/miniflux/miniflux2/model" + "strings" + "time" + + "golang.org/x/crypto/bcrypt" +) + +func (s *Storage) SetLastLogin(userID int64) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:SetLastLogin] userID=%d", userID)) + query := "UPDATE users SET last_login_at=now() WHERE id=$1" + _, err := s.db.Exec(query, userID) + if err != nil { + return fmt.Errorf("unable to update last login date: %v", err) + } + + return nil +} + +func (s *Storage) UserExists(username string) bool { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UserExists] username=%s", username)) + + var result int + s.db.QueryRow(`SELECT count(*) as c FROM users WHERE username=$1`, username).Scan(&result) + return result >= 1 +} + +func (s *Storage) AnotherUserExists(userID int64, username string) bool { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:AnotherUserExists] userID=%d, username=%s", userID, username)) + + var result int + s.db.QueryRow(`SELECT count(*) as c FROM users WHERE id != $1 AND username=$2`, userID, username).Scan(&result) + return result >= 1 +} + +func (s *Storage) CreateUser(user *model.User) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:CreateUser] username=%s", user.Username)) + + password, err := hashPassword(user.Password) + if err != nil { + return err + } + + query := "INSERT INTO users (username, password, is_admin) VALUES ($1, $2, $3) RETURNING id" + err = s.db.QueryRow(query, strings.ToLower(user.Username), password, user.IsAdmin).Scan(&user.ID) + if err != nil { + return fmt.Errorf("unable to create user: %v", err) + } + + s.CreateCategory(&model.Category{Title: "All", UserID: user.ID}) + return nil +} + +func (s *Storage) UpdateUser(user *model.User) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UpdateUser] username=%s", user.Username)) + user.Username = strings.ToLower(user.Username) + + if user.Password != "" { + hashedPassword, err := hashPassword(user.Password) + if err != nil { + return err + } + + query := "UPDATE users SET username=$1, password=$2, is_admin=$3, theme=$4, language=$5, timezone=$6 WHERE id=$7" + _, err = s.db.Exec(query, user.Username, hashedPassword, user.IsAdmin, user.Theme, user.Language, user.Timezone, user.ID) + if err != nil { + return fmt.Errorf("unable to update user: %v", err) + } + } else { + query := "UPDATE users SET username=$1, is_admin=$2, theme=$3, language=$4, timezone=$5 WHERE id=$6" + _, err := s.db.Exec(query, user.Username, user.IsAdmin, user.Theme, user.Language, user.Timezone, user.ID) + if err != nil { + return fmt.Errorf("unable to update user: %v", err) + } + } + + return nil +} + +func (s *Storage) GetUserById(userID int64) (*model.User, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetUserById] userID=%d", userID)) + + var user model.User + row := s.db.QueryRow("SELECT id, username, is_admin, theme, language, timezone FROM users WHERE id = $1", userID) + err := row.Scan(&user.ID, &user.Username, &user.IsAdmin, &user.Theme, &user.Language, &user.Timezone) + if err == sql.ErrNoRows { + return nil, nil + } else if err != nil { + return nil, fmt.Errorf("unable to fetch user: %v", err) + } + + return &user, nil +} + +func (s *Storage) GetUserByUsername(username string) (*model.User, error) { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:GetUserByUsername] username=%s", username)) + + var user model.User + row := s.db.QueryRow("SELECT id, username, is_admin, theme, language, timezone FROM users WHERE username=$1", username) + err := row.Scan(&user.ID, &user.Username, &user.IsAdmin, &user.Theme, &user.Language, &user.Timezone) + if err == sql.ErrNoRows { + return nil, nil + } else if err != nil { + return nil, fmt.Errorf("unable to fetch user: %v", err) + } + + return &user, nil +} + +func (s *Storage) RemoveUser(userID int64) error { + defer helper.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:RemoveUser] userID=%d", userID)) + + result, err := s.db.Exec("DELETE FROM users WHERE id = $1", userID) + if err != nil { + return fmt.Errorf("unable to remove this user: %v", err) + } + + count, err := result.RowsAffected() + if err != nil { + return fmt.Errorf("unable to remove this user: %v", err) + } + + if count == 0 { + return errors.New("nothing has been removed.") + } + + return nil +} + +func (s *Storage) GetUsers() (model.Users, error) { + defer helper.ExecutionTime(time.Now(), "[Storage:GetUsers]") + + var users model.Users + rows, err := s.db.Query("SELECT id, username, is_admin, theme, language, timezone, last_login_at FROM users ORDER BY username ASC") + if err != nil { + return nil, fmt.Errorf("unable to fetch users: %v", err) + } + defer rows.Close() + + for rows.Next() { + var user model.User + err := rows.Scan( + &user.ID, + &user.Username, + &user.IsAdmin, + &user.Theme, + &user.Language, + &user.Timezone, + &user.LastLoginAt, + ) + + if err != nil { + return nil, fmt.Errorf("unable to fetch users row: %v", err) + } + + users = append(users, &user) + } + + return users, nil +} + +func (s *Storage) CheckPassword(username, password string) error { + defer helper.ExecutionTime(time.Now(), "[Storage:CheckPassword]") + + var hash string + username = strings.ToLower(username) + + err := s.db.QueryRow("SELECT password FROM users WHERE username=$1", username).Scan(&hash) + if err == sql.ErrNoRows { + return fmt.Errorf("Unable to find this user: %s\n", username) + } else if err != nil { + return fmt.Errorf("Unable to fetch user: %v\n", err) + } + + if err := bcrypt.CompareHashAndPassword([]byte(hash), []byte(password)); err != nil { + return fmt.Errorf("Invalid password for %s\n", username) + } + + return nil +} + +func hashPassword(password string) (string, error) { + bytes, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost) + return string(bytes), err +} -- cgit v1.2.3