From af15412954b9b36365f3fd723bf91b3c7c2f88bd Mon Sep 17 00:00:00 2001 From: Frédéric Guillot Date: Wed, 4 Jul 2018 17:40:03 -0700 Subject: Add full-text search for entries and add search parameter to the API --- storage/entry.go | 84 +++++++++++++++++++++++------------------- storage/entry_query_builder.go | 11 +++++- storage/migration.go | 2 +- 3 files changed, 58 insertions(+), 39 deletions(-) (limited to 'storage') diff --git a/storage/entry.go b/storage/entry.go index 65c0220..b703a4c 100644 --- a/storage/entry.go +++ b/storage/entry.go @@ -35,14 +35,41 @@ func (s *Storage) NewEntryQueryBuilder(userID int64) *EntryQueryBuilder { return NewEntryQueryBuilder(s, userID) } +// UpdateEntryContent updates entry content. +func (s *Storage) UpdateEntryContent(entry *model.Entry) error { + tx, err := s.db.Begin() + if err != nil { + return err + } + + _, err = tx.Exec(`UPDATE entries SET content=$1 WHERE id=$2 AND user_id=$3`, entry.Content, entry.ID, entry.UserID) + if err != nil { + tx.Rollback() + return err + } + + query := ` + UPDATE entries + SET document_vectors = to_tsvector(title || ' ' || coalesce(content, '')) + WHERE id=$1 AND user_id=$2 + ` + _, err = tx.Exec(query, entry.ID, entry.UserID) + if err != nil { + tx.Rollback() + return err + } + + return tx.Commit() +} + // createEntry add a new entry. func (s *Storage) createEntry(entry *model.Entry) error { query := ` INSERT INTO entries - (title, hash, url, comments_url, published_at, content, author, user_id, feed_id) + (title, hash, url, comments_url, published_at, content, author, user_id, feed_id, document_vectors) VALUES - ($1, $2, $3, $4, $5, $6, $7, $8, $9) - RETURNING id + ($1, $2, $3, $4, $5, $6, $7, $8, $9, to_tsvector($1 || ' ' || coalesce($6, ''))) + RETURNING id, status ` err := s.db.QueryRow( query, @@ -55,13 +82,12 @@ func (s *Storage) createEntry(entry *model.Entry) error { entry.Author, entry.UserID, entry.FeedID, - ).Scan(&entry.ID) + ).Scan(&entry.ID, &entry.Status) 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 @@ -74,30 +100,14 @@ func (s *Storage) createEntry(entry *model.Entry) error { return nil } -// UpdateEntryContent updates entry content. -func (s *Storage) UpdateEntryContent(entry *model.Entry) error { - query := ` - UPDATE entries SET - content=$1 - WHERE user_id=$2 AND id=$3 - ` - - _, err := s.db.Exec( - query, - entry.Content, - entry.UserID, - entry.ID, - ) - return err -} - // updateEntry updates an entry when a feed is refreshed. // Note: we do not update the published date because some feeds do not contains any date, // it default to time.Now() which could change the order of items on the history page. func (s *Storage) updateEntry(entry *model.Entry) error { query := ` UPDATE entries SET - title=$1, url=$2, comments_url=$3, content=$4, author=$5 + title=$1, url=$2, comments_url=$3, content=$4, author=$5, + document_vectors=to_tsvector($1 || ' ' || coalesce($4, '')) WHERE user_id=$6 AND feed_id=$7 AND hash=$8 RETURNING id ` @@ -133,6 +143,20 @@ func (s *Storage) entryExists(entry *model.Entry) bool { return result >= 1 } +// cleanupEntries deletes from the database entries marked as "removed" and not visible anymore in the feed. +func (s *Storage) cleanupEntries(feedID int64, entryHashes []string) error { + query := ` + DELETE FROM entries + WHERE feed_id=$1 AND + id IN (SELECT id FROM entries WHERE feed_id=$2 AND status=$3 AND NOT (hash=ANY($4))) + ` + if _, err := s.db.Exec(query, feedID, feedID, model.EntryStatusRemoved, pq.Array(entryHashes)); err != nil { + return fmt.Errorf("unable to cleanup entries: %v", err) + } + + return nil +} + // UpdateEntries updates a list of entries while refreshing a feed. func (s *Storage) UpdateEntries(userID, feedID int64, entries model.Entries, updateExistingEntries bool) (err error) { var entryHashes []string @@ -162,20 +186,6 @@ func (s *Storage) UpdateEntries(userID, feedID int64, entries model.Entries, upd return nil } -// cleanupEntries deletes from the database entries marked as "removed" and not visible anymore in the feed. -func (s *Storage) cleanupEntries(feedID int64, entryHashes []string) error { - query := ` - DELETE FROM entries - WHERE feed_id=$1 AND - id IN (SELECT id FROM entries WHERE feed_id=$2 AND status=$3 AND NOT (hash=ANY($4))) - ` - if _, err := s.db.Exec(query, feedID, feedID, model.EntryStatusRemoved, pq.Array(entryHashes)); err != nil { - return fmt.Errorf("unable to cleanup entries: %v", err) - } - - return nil -} - // ArchiveEntries changes the status of read items to "removed" after 60 days. func (s *Storage) ArchiveEntries() error { query := ` diff --git a/storage/entry_query_builder.go b/storage/entry_query_builder.go index 0577192..8c0d706 100644 --- a/storage/entry_query_builder.go +++ b/storage/entry_query_builder.go @@ -27,6 +27,15 @@ type EntryQueryBuilder struct { offset int } +// WithSearchQuery adds full-text search query to the condition. +func (e *EntryQueryBuilder) WithSearchQuery(query string) *EntryQueryBuilder { + if query != "" { + e.conditions = append(e.conditions, fmt.Sprintf("e.document_vectors @@ plainto_tsquery($%d)", len(e.args)+1)) + e.args = append(e.args, query) + } + return e +} + // WithStarred adds starred filter. func (e *EntryQueryBuilder) WithStarred() *EntryQueryBuilder { e.conditions = append(e.conditions, "e.starred is true") @@ -146,7 +155,7 @@ func (e *EntryQueryBuilder) CountEntries() (count int, err error) { query := `SELECT count(*) FROM entries e LEFT JOIN feeds f ON f.id=e.feed_id WHERE %s` condition := e.buildCondition() - defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[EntryQueryBuilder:CountEntries] condition=%s, args=%v", condition, e.args)) + defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[EntryQueryBuilder:CountEntries] %s, args=%v", condition, e.args)) err = e.store.db.QueryRow(fmt.Sprintf(query, condition), e.args...).Scan(&count) if err != nil { diff --git a/storage/migration.go b/storage/migration.go index 56b7bfb..e78f1d5 100644 --- a/storage/migration.go +++ b/storage/migration.go @@ -12,7 +12,7 @@ import ( "github.com/miniflux/miniflux/sql" ) -const schemaVersion = 19 +const schemaVersion = 20 // Migrate run database migrations. func (s *Storage) Migrate() { -- cgit v1.2.3