From c5373ff2bffb3a3e3d37b3eb6bd1cdc733d9c590 Mon Sep 17 00:00:00 2001 From: Frédéric Guillot Date: Sat, 9 Jun 2018 13:40:20 -0700 Subject: Rewrite individual entry pagination SQL queries --- storage/entry_pagination_builder.go | 158 ++++++++++++++++++++++++++++++++++++ storage/entry_query_builder.go | 42 +++++++--- 2 files changed, 187 insertions(+), 13 deletions(-) create mode 100644 storage/entry_pagination_builder.go (limited to 'storage') diff --git a/storage/entry_pagination_builder.go b/storage/entry_pagination_builder.go new file mode 100644 index 0000000..72fae8d --- /dev/null +++ b/storage/entry_pagination_builder.go @@ -0,0 +1,158 @@ +// Copyright 2018 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" + "strings" + "time" + + "github.com/miniflux/miniflux/model" + "github.com/miniflux/miniflux/timer" +) + +// EntryPaginationBuilder is a builder for entry prev/next queries. +type EntryPaginationBuilder struct { + store *Storage + conditions []string + args []interface{} + entryID int64 + direction string +} + +// WithStarred adds starred to the condition. +func (e *EntryPaginationBuilder) WithStarred() { + e.conditions = append(e.conditions, "e.starred is true") +} + +// WithFeedID adds feed_id to the condition. +func (e *EntryPaginationBuilder) WithFeedID(feedID int64) { + if feedID != 0 { + e.conditions = append(e.conditions, fmt.Sprintf("e.feed_id = $%d", len(e.args)+1)) + e.args = append(e.args, feedID) + } +} + +// WithCategoryID adds category_id to the condition. +func (e *EntryPaginationBuilder) WithCategoryID(categoryID int64) { + if categoryID != 0 { + e.conditions = append(e.conditions, fmt.Sprintf("f.category_id = $%d", len(e.args)+1)) + e.args = append(e.args, categoryID) + } +} + +// WithStatus adds status to the condition. +func (e *EntryPaginationBuilder) WithStatus(status string) { + if status != "" { + e.conditions = append(e.conditions, fmt.Sprintf("e.status = $%d", len(e.args)+1)) + e.args = append(e.args, status) + } +} + +// Entries returns previous and next entries. +func (e *EntryPaginationBuilder) Entries() (*model.Entry, *model.Entry, error) { + tx, err := e.store.db.Begin() + if err != nil { + return nil, nil, fmt.Errorf("begin transaction for entry pagination: %v", err) + } + + prevID, nextID, err := e.getPrevNextID(tx) + if err != nil { + tx.Rollback() + return nil, nil, err + } + + prevEntry, err := e.getEntry(tx, prevID) + if err != nil { + tx.Rollback() + return nil, nil, err + } + + nextEntry, err := e.getEntry(tx, nextID) + if err != nil { + tx.Rollback() + return nil, nil, err + } + + tx.Commit() + + if e.direction == "desc" { + return nextEntry, prevEntry, nil + } + + return prevEntry, nextEntry, nil +} + +func (e *EntryPaginationBuilder) getPrevNextID(tx *sql.Tx) (prevID int64, nextID int64, err error) { + defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[EntryPaginationBuilder] %v, %v", e.conditions, e.args)) + + cte := ` + WITH entry_pagination AS ( + SELECT + e.id, + lag(e.id) over (order by e.published_at asc, e.id desc) as prev_id, + lead(e.id) over (order by e.published_at asc, e.id desc) as next_id + FROM entries AS e + LEFT JOIN feeds AS f ON f.id=e.feed_id + WHERE %s + ORDER BY e.published_at asc, e.id desc + ) + SELECT prev_id, next_id FROM entry_pagination AS ep WHERE %s; + ` + + subCondition := strings.Join(e.conditions, " AND ") + finalCondition := fmt.Sprintf("ep.id = $%d", len(e.args)+1) + query := fmt.Sprintf(cte, subCondition, finalCondition) + e.args = append(e.args, e.entryID) + + var pID, nID sql.NullInt64 + err = tx.QueryRow(query, e.args...).Scan(&pID, &nID) + switch { + case err == sql.ErrNoRows: + return 0, 0, nil + case err != nil: + return 0, 0, fmt.Errorf("entry pagination: %v", err) + } + + if pID.Valid { + prevID = pID.Int64 + } + + if nID.Valid { + nextID = nID.Int64 + } + + return prevID, nextID, nil +} + +func (e *EntryPaginationBuilder) getEntry(tx *sql.Tx, entryID int64) (*model.Entry, error) { + var entry model.Entry + + err := tx.QueryRow(`SELECT id, title FROM entries WHERE id = $1`, entryID).Scan( + &entry.ID, + &entry.Title, + ) + + switch { + case err == sql.ErrNoRows: + return nil, nil + case err != nil: + return nil, fmt.Errorf("fetching sibling entry: %v", err) + } + + return &entry, nil +} + +// NewEntryPaginationBuilder returns a new EntryPaginationBuilder. +func NewEntryPaginationBuilder(store *Storage, userID, entryID int64, direction string) *EntryPaginationBuilder { + return &EntryPaginationBuilder{ + store: store, + args: []interface{}{userID, "removed"}, + conditions: []string{"e.user_id = $1", "e.status <> $2"}, + entryID: entryID, + direction: direction, + } +} diff --git a/storage/entry_query_builder.go b/storage/entry_query_builder.go index d596526..0577192 100644 --- a/storage/entry_query_builder.go +++ b/storage/entry_query_builder.go @@ -33,15 +33,31 @@ func (e *EntryQueryBuilder) WithStarred() *EntryQueryBuilder { return e } -// Before add condition based on the entry date. -func (e *EntryQueryBuilder) Before(date *time.Time) *EntryQueryBuilder { +// BeforeDate adds a condition < published_at +func (e *EntryQueryBuilder) BeforeDate(date time.Time) *EntryQueryBuilder { e.conditions = append(e.conditions, fmt.Sprintf("e.published_at < $%d", len(e.args)+1)) e.args = append(e.args, date) return e } -// WithGreaterThanEntryID adds a condition > entryID. -func (e *EntryQueryBuilder) WithGreaterThanEntryID(entryID int64) *EntryQueryBuilder { +// AfterDate adds a condition > published_at +func (e *EntryQueryBuilder) AfterDate(date time.Time) *EntryQueryBuilder { + e.conditions = append(e.conditions, fmt.Sprintf("e.published_at > $%d", len(e.args)+1)) + e.args = append(e.args, date) + return e +} + +// BeforeEntryID adds a condition < entryID. +func (e *EntryQueryBuilder) BeforeEntryID(entryID int64) *EntryQueryBuilder { + if entryID != 0 { + e.conditions = append(e.conditions, fmt.Sprintf("e.id < $%d", len(e.args)+1)) + e.args = append(e.args, entryID) + } + return e +} + +// AfterEntryID adds a condition > entryID. +func (e *EntryQueryBuilder) AfterEntryID(entryID int64) *EntryQueryBuilder { if entryID != 0 { e.conditions = append(e.conditions, fmt.Sprintf("e.id > $%d", len(e.args)+1)) e.args = append(e.args, entryID) @@ -179,10 +195,10 @@ func (e *EntryQueryBuilder) GetEntries() (model.Entries, error) { ` condition := e.buildCondition() - query = fmt.Sprintf(query, condition, e.buildSorting()) - // log.Println(query) + sorting := e.buildSorting() + query = fmt.Sprintf(query, condition, sorting) - defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[EntryQueryBuilder:GetEntries] condition=%s, args=%v", condition, e.args)) + defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[EntryQueryBuilder:GetEntries] %s, args=%v, sorting=%s", condition, e.args, sorting)) rows, err := e.store.db.Query(query, e.args...) if err != nil { @@ -286,25 +302,25 @@ func (e *EntryQueryBuilder) buildCondition() string { } func (e *EntryQueryBuilder) buildSorting() string { - var queries []string + var parts []string if e.order != "" { - queries = append(queries, fmt.Sprintf(`ORDER BY "%s"`, e.order)) + parts = append(parts, fmt.Sprintf(`ORDER BY "%s"`, e.order)) } if e.direction != "" { - queries = append(queries, fmt.Sprintf(`%s`, e.direction)) + parts = append(parts, fmt.Sprintf(`%s`, e.direction)) } if e.limit != 0 { - queries = append(queries, fmt.Sprintf(`LIMIT %d`, e.limit)) + parts = append(parts, fmt.Sprintf(`LIMIT %d`, e.limit)) } if e.offset != 0 { - queries = append(queries, fmt.Sprintf(`OFFSET %d`, e.offset)) + parts = append(parts, fmt.Sprintf(`OFFSET %d`, e.offset)) } - return strings.Join(queries, " ") + return strings.Join(parts, " ") } // NewEntryQueryBuilder returns a new EntryQueryBuilder. -- cgit v1.2.3