aboutsummaryrefslogtreecommitdiffhomepage
path: root/backend/db
diff options
context:
space:
mode:
Diffstat (limited to 'backend/db')
-rw-r--r--backend/db/articles.sql.go192
-rw-r--r--backend/db/feeds.sql.go36
-rw-r--r--backend/db/migrations/006_add_composite_index.sql1
-rw-r--r--backend/db/queries/articles.sql32
-rw-r--r--backend/db/queries/feeds.sql7
-rw-r--r--backend/db/schema.sql2
6 files changed, 248 insertions, 22 deletions
diff --git a/backend/db/articles.sql.go b/backend/db/articles.sql.go
index 7f6400b..329cb18 100644
--- a/backend/db/articles.sql.go
+++ b/backend/db/articles.sql.go
@@ -192,18 +192,96 @@ func (q *Queries) GetArticlesByFeed(ctx context.Context, feedID int64) ([]Articl
return items, nil
}
-const getReadArticles = `-- name: GetReadArticles :many
+const getArticlesByFeedPaginated = `-- name: GetArticlesByFeedPaginated :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 1 AND f.is_subscribed = 1 AND f.user_id = ?
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ? AND a.feed_id = ?
ORDER BY a.id DESC
-LIMIT 100
+LIMIT ?
`
-type GetReadArticlesRow struct {
+type GetArticlesByFeedPaginatedParams struct {
+ IsRead int64
+ UserID int64
+ FeedID int64
+ Limit int64
+}
+
+type GetArticlesByFeedPaginatedRow struct {
+ ID int64
+ FeedID int64
+ Guid string
+ Title string
+ Url string
+ IsRead int64
+ FeedID2 int64
+ FeedUrl string
+ FeedTitle string
+ FeedIsSubscribed int64
+}
+
+func (q *Queries) GetArticlesByFeedPaginated(ctx context.Context, arg GetArticlesByFeedPaginatedParams) ([]GetArticlesByFeedPaginatedRow, error) {
+ rows, err := q.db.QueryContext(ctx, getArticlesByFeedPaginated,
+ arg.IsRead,
+ arg.UserID,
+ arg.FeedID,
+ arg.Limit,
+ )
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+ items := []GetArticlesByFeedPaginatedRow{}
+ for rows.Next() {
+ var i GetArticlesByFeedPaginatedRow
+ if err := rows.Scan(
+ &i.ID,
+ &i.FeedID,
+ &i.Guid,
+ &i.Title,
+ &i.Url,
+ &i.IsRead,
+ &i.FeedID2,
+ &i.FeedUrl,
+ &i.FeedTitle,
+ &i.FeedIsSubscribed,
+ ); err != nil {
+ return nil, err
+ }
+ items = append(items, i)
+ }
+ if err := rows.Close(); err != nil {
+ return nil, err
+ }
+ if err := rows.Err(); err != nil {
+ return nil, err
+ }
+ return items, nil
+}
+
+const getArticlesByFeedPaginatedAfter = `-- name: GetArticlesByFeedPaginatedAfter :many
+SELECT
+ a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
+FROM articles AS a
+INNER JOIN feeds AS f ON a.feed_id = f.id
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ? AND a.feed_id = ? AND a.id < ?
+ORDER BY a.id DESC
+LIMIT ?
+`
+
+type GetArticlesByFeedPaginatedAfterParams struct {
+ IsRead int64
+ UserID int64
+ FeedID int64
+ ID int64
+ Limit int64
+}
+
+type GetArticlesByFeedPaginatedAfterRow struct {
ID int64
FeedID int64
Guid string
@@ -216,15 +294,85 @@ type GetReadArticlesRow struct {
FeedIsSubscribed int64
}
-func (q *Queries) GetReadArticles(ctx context.Context, userID int64) ([]GetReadArticlesRow, error) {
- rows, err := q.db.QueryContext(ctx, getReadArticles, userID)
+func (q *Queries) GetArticlesByFeedPaginatedAfter(ctx context.Context, arg GetArticlesByFeedPaginatedAfterParams) ([]GetArticlesByFeedPaginatedAfterRow, error) {
+ rows, err := q.db.QueryContext(ctx, getArticlesByFeedPaginatedAfter,
+ arg.IsRead,
+ arg.UserID,
+ arg.FeedID,
+ arg.ID,
+ arg.Limit,
+ )
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+ items := []GetArticlesByFeedPaginatedAfterRow{}
+ for rows.Next() {
+ var i GetArticlesByFeedPaginatedAfterRow
+ if err := rows.Scan(
+ &i.ID,
+ &i.FeedID,
+ &i.Guid,
+ &i.Title,
+ &i.Url,
+ &i.IsRead,
+ &i.FeedID2,
+ &i.FeedUrl,
+ &i.FeedTitle,
+ &i.FeedIsSubscribed,
+ ); err != nil {
+ return nil, err
+ }
+ items = append(items, i)
+ }
+ if err := rows.Close(); err != nil {
+ return nil, err
+ }
+ if err := rows.Err(); err != nil {
+ return nil, err
+ }
+ return items, nil
+}
+
+const getArticlesPaginated = `-- name: GetArticlesPaginated :many
+SELECT
+ a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
+FROM articles AS a
+INNER JOIN feeds AS f ON a.feed_id = f.id
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ?
+ORDER BY a.id DESC
+LIMIT ?
+`
+
+type GetArticlesPaginatedParams struct {
+ IsRead int64
+ UserID int64
+ Limit int64
+}
+
+type GetArticlesPaginatedRow struct {
+ ID int64
+ FeedID int64
+ Guid string
+ Title string
+ Url string
+ IsRead int64
+ FeedID2 int64
+ FeedUrl string
+ FeedTitle string
+ FeedIsSubscribed int64
+}
+
+func (q *Queries) GetArticlesPaginated(ctx context.Context, arg GetArticlesPaginatedParams) ([]GetArticlesPaginatedRow, error) {
+ rows, err := q.db.QueryContext(ctx, getArticlesPaginated, arg.IsRead, arg.UserID, arg.Limit)
if err != nil {
return nil, err
}
defer rows.Close()
- items := []GetReadArticlesRow{}
+ items := []GetArticlesPaginatedRow{}
for rows.Next() {
- var i GetReadArticlesRow
+ var i GetArticlesPaginatedRow
if err := rows.Scan(
&i.ID,
&i.FeedID,
@@ -250,18 +398,25 @@ func (q *Queries) GetReadArticles(ctx context.Context, userID int64) ([]GetReadA
return items, nil
}
-const getUnreadArticles = `-- name: GetUnreadArticles :many
+const getArticlesPaginatedAfter = `-- name: GetArticlesPaginatedAfter :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 0 AND f.is_subscribed = 1 AND f.user_id = ?
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ? AND a.id < ?
ORDER BY a.id DESC
-LIMIT 100
+LIMIT ?
`
-type GetUnreadArticlesRow struct {
+type GetArticlesPaginatedAfterParams struct {
+ IsRead int64
+ UserID int64
+ ID int64
+ Limit int64
+}
+
+type GetArticlesPaginatedAfterRow struct {
ID int64
FeedID int64
Guid string
@@ -274,15 +429,20 @@ type GetUnreadArticlesRow struct {
FeedIsSubscribed int64
}
-func (q *Queries) GetUnreadArticles(ctx context.Context, userID int64) ([]GetUnreadArticlesRow, error) {
- rows, err := q.db.QueryContext(ctx, getUnreadArticles, userID)
+func (q *Queries) GetArticlesPaginatedAfter(ctx context.Context, arg GetArticlesPaginatedAfterParams) ([]GetArticlesPaginatedAfterRow, error) {
+ rows, err := q.db.QueryContext(ctx, getArticlesPaginatedAfter,
+ arg.IsRead,
+ arg.UserID,
+ arg.ID,
+ arg.Limit,
+ )
if err != nil {
return nil, err
}
defer rows.Close()
- items := []GetUnreadArticlesRow{}
+ items := []GetArticlesPaginatedAfterRow{}
for rows.Next() {
- var i GetUnreadArticlesRow
+ var i GetArticlesPaginatedAfterRow
if err := rows.Scan(
&i.ID,
&i.FeedID,
diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
index cec228a..0226a7d 100644
--- a/backend/db/feeds.sql.go
+++ b/backend/db/feeds.sql.go
@@ -96,6 +96,42 @@ func (q *Queries) GetFeedByURL(ctx context.Context, arg GetFeedByURLParams) (Fee
return i, err
}
+const getFeedUnreadCounts = `-- name: GetFeedUnreadCounts :many
+SELECT f.id as feed_id, COUNT(a.id) as unread_count
+FROM feeds AS f
+LEFT JOIN articles AS a ON f.id = a.feed_id AND a.is_read = 0
+WHERE f.is_subscribed = 1 AND f.user_id = ?
+GROUP BY f.id
+`
+
+type GetFeedUnreadCountsRow struct {
+ FeedID int64
+ UnreadCount int64
+}
+
+func (q *Queries) GetFeedUnreadCounts(ctx context.Context, userID int64) ([]GetFeedUnreadCountsRow, error) {
+ rows, err := q.db.QueryContext(ctx, getFeedUnreadCounts, userID)
+ if err != nil {
+ return nil, err
+ }
+ defer rows.Close()
+ items := []GetFeedUnreadCountsRow{}
+ for rows.Next() {
+ var i GetFeedUnreadCountsRow
+ if err := rows.Scan(&i.FeedID, &i.UnreadCount); err != nil {
+ return nil, err
+ }
+ items = append(items, i)
+ }
+ if err := rows.Close(); err != nil {
+ return nil, err
+ }
+ if err := rows.Err(); err != nil {
+ return nil, err
+ }
+ return items, nil
+}
+
const getFeeds = `-- name: GetFeeds :many
SELECT id, url, title, fetched_at, is_subscribed, user_id
FROM feeds
diff --git a/backend/db/migrations/006_add_composite_index.sql b/backend/db/migrations/006_add_composite_index.sql
new file mode 100644
index 0000000..d3d378b
--- /dev/null
+++ b/backend/db/migrations/006_add_composite_index.sql
@@ -0,0 +1 @@
+CREATE INDEX IF NOT EXISTS idx_articles_feed_read_id ON articles(feed_id, is_read, id DESC);
diff --git a/backend/db/queries/articles.sql b/backend/db/queries/articles.sql
index 2c00678..1554530 100644
--- a/backend/db/queries/articles.sql
+++ b/backend/db/queries/articles.sql
@@ -6,25 +6,45 @@ FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
WHERE a.id = ?;
--- name: GetUnreadArticles :many
+-- name: GetArticlesPaginated :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 0 AND f.is_subscribed = 1 AND f.user_id = ?
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ?
ORDER BY a.id DESC
-LIMIT 100;
+LIMIT ?;
--- name: GetReadArticles :many
+-- name: GetArticlesPaginatedAfter :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 1 AND f.is_subscribed = 1 AND f.user_id = ?
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ? AND a.id < ?
ORDER BY a.id DESC
-LIMIT 100;
+LIMIT ?;
+
+-- name: GetArticlesByFeedPaginated :many
+SELECT
+ a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
+FROM articles AS a
+INNER JOIN feeds AS f ON a.feed_id = f.id
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ? AND a.feed_id = ?
+ORDER BY a.id DESC
+LIMIT ?;
+
+-- name: GetArticlesByFeedPaginatedAfter :many
+SELECT
+ a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
+FROM articles AS a
+INNER JOIN feeds AS f ON a.feed_id = f.id
+WHERE a.is_read = ? AND f.is_subscribed = 1 AND f.user_id = ? AND a.feed_id = ? AND a.id < ?
+ORDER BY a.id DESC
+LIMIT ?;
-- name: GetArticlesByFeed :many
SELECT id, feed_id, guid, title, url, is_read
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
index acf36d2..094a0f8 100644
--- a/backend/db/queries/feeds.sql
+++ b/backend/db/queries/feeds.sql
@@ -37,3 +37,10 @@ WHERE is_subscribed = 1;
UPDATE feeds
SET is_subscribed = 0
WHERE id = ?;
+
+-- name: GetFeedUnreadCounts :many
+SELECT f.id as feed_id, COUNT(a.id) as unread_count
+FROM feeds AS f
+LEFT JOIN articles AS a ON f.id = a.feed_id AND a.is_read = 0
+WHERE f.is_subscribed = 1 AND f.user_id = ?
+GROUP BY f.id;
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index 07ac72d..2596c84 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -37,3 +37,5 @@ CREATE INDEX IF NOT EXISTS idx_articles_is_read ON articles(is_read);
CREATE INDEX IF NOT EXISTS idx_articles_guid ON articles(guid);
CREATE INDEX IF NOT EXISTS idx_feeds_user_id ON feeds(user_id);
+
+CREATE INDEX IF NOT EXISTS idx_articles_feed_read_id ON articles(feed_id, is_read, id DESC);
generated by cgit v1.3-1-g0d28 (git 2.53.0) at 2026-03-02 04:04:27 +0000